How to calculate a rolling average or rolling mean in Pandas

The Pandas rolling() method can be used to calculate a rolling mean or rolling average (also known as a moving average), which is simply the mean of a specific time series data column value over a specified number of periods.

Rolling means and rolling averages are commonly used in finance and retail sectors, where they are used to smooth out short-term fluctuations in data and highlight longer-term trends or cycles.

Import a Pandas dataframe

To get started, open a Jupyter notebook, import the Pandas library, find some time series data and load it into a Pandas dataframe. I’m using a dataset of ecommerce sales data I created for test purposes. You can access the file via my GitHub repository. It contains 19 months of ecommerce sales data and was exported from Google Analytics.

import pandas as pd

df = pd.read_csv('https://raw.githubusercontent.com/flyandlure/datasets/master/monthly-ecommerce-data.csv')
df

Prepare the data

In order to use the Pandas rolling() function, the data obviously needs to be numeric. When data are exported from Google Analytics, it often adds commas to the numbers to improve their readability. However, this results in what was a numeric value, becoming a categorical value with an object dtype. You can identify the dtypes of your columns by using the Pandas info() function. This shows we have two object columns to fix.

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 19 entries, 0 to 18
Data columns (total 6 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   year_month       19 non-null     int64  
 1   sessions         19 non-null     object 
 2   transactions     19 non-null     int64  
 3   conversion_rate  19 non-null     float64
 4   revenue          19 non-null     object 
 5   aov              19 non-null     float64
dtypes: float64(2), int64(2), object(2)
memory usage: 1.0+ KB

To convert the non-numeric object data in the sessions and revenue columns to float and int dtypes, we can first use the str.replace() method to remove the comma and replace it with an empty string, then we can use the astype() method to cast the column data to the correct dtype. If you run this code, then re-run df.info(), you should find that the columns are now numeric, and we can use the rolling() function without issues.

df['sessions'] = df['sessions'].str.replace(',', '').astype(int)
df['revenue'] = df['revenue'].str.replace(',', '').astype(float)

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 19 entries, 0 to 18
Data columns (total 6 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   year_month       19 non-null     int64  
 1   sessions         19 non-null     int64  
 2   transactions     19 non-null     int64  
 3   conversion_rate  19 non-null     float64
 4   revenue          19 non-null     float64
 5   aov              19 non-null     float64
dtypes: float64(3), int64(3)
memory usage: 1.0 KB

Use rolling() to calculate the rolling mean of a column

Now we’ve ensured our data are in the numeric format required, we can calculate the rolling mean or rolling average of a column using the Pandas rolling() function. The default argument for the rolling() is called window, and is an int specifying the number of rows to use.

We’ll use rolling() to calculate rolling means or rolling averages for the conversion_rate column. We’ll calculate a three-month, six-month, nine-month, and 12-month rolling average, and assign the values back to the original dataframe.

df['mean_conversion_rate_3m'] = df['conversion_rate'].rolling(3).mean()
df['mean_conversion_rate_6m'] = df['conversion_rate'].rolling(6).mean()
df['mean_conversion_rate_9m'] = df['conversion_rate'].rolling(9).mean()
df['mean_conversion_rate_12m'] = df['conversion_rate'].rolling(12).mean()

If you print the dataframe you’ll see that the time series data are sorted with the oldest values at the top of the dataframe and the latest values at the bottom. For each of the rolling() calculations, Pandas will look at each row and then calculate the rolling mean based on the previous n rows.

For example, for the year_month 202002, when calculating a three-month rolling average with rolling(3).mean(), Pandas will look back at the values of 202002, 202001, and 201912, to calculate the rolling mean based on the current value and the previous two. When there are insufficient prior values to use for the calculation, NaN values are added.

df

Matt Clarke, Tuesday, November 29, 2022