How to calculate the difference and percentage change between rows in Pandas

When working with Pandas dataframes, it’s a very common task to calculate the difference between two rows. For example, you might want to calculate the difference in the number of visitors to your website between two days, or the difference in the price of a stock between two days.

There are actually a number of different ways to calculate the difference between two rows in Pandas and calculate their percentage change. In this post, we’ll look at two of the most common methods: diff() and pct_change(), which are designed specifically for this task, and doing the same thing across column values.

Import the data

To get started, open a new Jupyter notebook and import the data. We’ll use the pandas library to read the data from a CSV file into a dataframe using the read_csv() function. Pandas supports importing data from a number of different file formats, including CSV, Excel, JSON, and SQL.

import pandas as pd

df = pd.read_csv('https://raw.githubusercontent.com/flyandlure/datasets/master/causal_impact_dataset.csv')
df.head()

Calculate the difference in between each row using diff()

Crucially, you need to ensure your Pandas dataframe has been sorted into a logical order before you calculate the differences between rows or their percentage change. You can do this by appending .sort_values(by='column_name_here') to the end of your dataframe, and passing in the column name you want to sort by.

To calculate the difference between selected values in each row of our dataframe we’ll simply append .diff() to the end of our column name and then assign the value to a new column in our dataframe. We don’t need to do it here, but the axis parameter can be used to calculate the difference between columns instead of rows, and the periods parameter can be used to calculate the difference between rows that are further apart than the next row by using shift().

```python
df['clicks_diff'] = df['clicks'].diff()
df['impressions_diff'] = df['impressions'].diff()
df['ctr_diff'] = df['ctr'].diff()
df['position_diff'] = df['position'].diff()
df.head(10)

Calculate the percentage change between each row with pct_change()

The same kind of approach can be used to calculate the percentage change between selected values in each row of our dataframe. As with diff(), we simply append .pct_change() to the end of the column name and then assign the value to a new column.

The pct_change() function will calculate the percentage change between each row and the previous row. This means that the first row will always be NaN as there is no previous row to compare it to. As with diff(), the pct_change() function has some other arguments that can be used to change the behaviour of the function.

For example, we can use the periods argument to specify the number of rows to compare to. This is useful if we want to compare the current row to a row that is not the previous row. For example, if we wanted to compare the current row to the row that was 3 rows ago, we could use periods=3.

df['clicks_pct_change'] = df['clicks'].pct_change()
df['impressions_pct_change'] = df['impressions'].pct_change()
df['ctr_pct_change'] = df['ctr'].pct_change()
df['position_pct_change'] = df['position'].pct_change()
df.head(10)

Calculate the percentage change versus the same day last week

To calculate the percentage change in a metric versus the same day last week we can pass in a value to the periods argument of the pct_change() function. This will calculate the percentage change in the metric versus the same day last week.

# Calculate the percentage change between each row and the previous week
df['clicks_pct_change_week'] = df['clicks'].pct_change(periods=7)
df['impressions_pct_change_week'] = df['impressions'].pct_change(periods=7)
df['ctr_pct_change_week'] = df['ctr'].pct_change(periods=7)
df['position_pct_change_week'] = df['position'].pct_change(periods=7)

# Show the original data and the weekly percentage changes
df[['clicks', 'clicks_pct_change_week', 'impressions', 'impressions_pct_change_week', 'ctr', 'ctr_pct_change_week', 'position', 'position_pct_change_week']].tail(10)

Matt Clarke, Saturday, September 10, 2022