Get Rolling Window estimates in Pandas

Rolling window estimates can be very useful when working with time-series data. They are quite frequently used in finance, for example, to smooth out a value over a rolling window using a rolling mean. In this tutorial, we will look at how to calculate rolling estimates like the rolling mean in a pandas dataframe.

The pandas rolling() function

You can use the pandas rolling() function to get a rolling window for computing the rolling estimates. The following is the syntax:

# get rolling mean
df.rolling(n).mean()

Here, n is the size of the moving window you want to use, that is, the number of observations you want to use to compute a rolling statistic. Also, note that the above will result in a rolling mean for all the numerical columns of the dataframe df. If you want to compute the rolling mean of a specific column, use the following syntax:

# get rolling mean for Col1
df['Col1'].rolling(n).mean()

Examples

Let’s look at some examples of using the pandas rolling() function to compute rolling window estimates. First, we’ll create a sample dataframe with just one column.

import pandas as pd

# create dataframe
df = pd.DataFrame({'PageViews': [100, 120, 180, 200, 240, 160, 130]},
                  index = ['2020-03-01', '2020-03-02', '2020-03-03', '2020-03-04',\
                           '2020-03-05', '2020-03-06', '2020-03-07'])
# print the dataframe
print(df)

Output:

            PageViews
2020-03-01        100
2020-03-02        120
2020-03-03        180
2020-03-04        200
2020-03-05        240
2020-03-06        160
2020-03-07        130

The dataframe df stores the daily pageviews of a blogging website.

Rolling mean on pandas dataframe

Let’s calculate the 3 day rolling mean of the number of page views the website gets.

# rolling mean of pageviews
print(df.rolling(3).mean())

Output:

             PageViews
2020-03-01         NaN
2020-03-02         NaN
2020-03-03  133.333333
2020-03-04  166.666667
2020-03-05  206.666667
2020-03-06  200.000000
2020-03-07  176.666667

You can see that we get NaN in the first two rows because we cannot calculate the rolling mean as there are no preceding values to make the three-day window complete. The third row is 133.33 which is the average pageviews over the previous three days.

You can similarly use the rolling() function to calculate other rolling window estimates like the rolling median, rolling sum, etc. Let’s calculate the rolling the sum of the 3 day window on the above dataframe.

# rolling sum of pageviews
print(df.rolling(3).sum())

Output:

            PageViews
2020-03-01        NaN
2020-03-02        NaN
2020-03-03      400.0
2020-03-04      500.0
2020-03-05      620.0
2020-03-06      600.0
2020-03-07      530.0

Similar to the rolling mean example above, we get NaN for the first two rows. From the third row, the value in each row is a rolling sum of the previous three values.

Rolling mean on dataframe with multiple columns

What happens if we apply a rolling window function to a dataframe with multiple columns? Let’s find out. First, we’ll add an additional column representing the Revenue (in dollars) generated by the website on the corresponding date.

# add column for revenue
df['Revenue'] = [10, 15, 12, 20, 30, 22, 14]
# print the dataframe
print(df)

Output:

            PageViews  Revenue
2020-03-01        100       10
2020-03-02        120       15
2020-03-03        180       12
2020-03-04        200       20
2020-03-05        240       30
2020-03-06        160       22
2020-03-07        130       14

Now, let’s apply a rolling mean function on the dataframe for a three-day window.

# rolling mean 
print(df.rolling(3).mean())

Output:

             PageViews    Revenue
2020-03-01         NaN        NaN
2020-03-02         NaN        NaN
2020-03-03  133.333333  12.333333
2020-03-04  166.666667  15.666667
2020-03-05  206.666667  20.666667
2020-03-06  200.000000  24.000000
2020-03-07  176.666667  22.000000

Applying the pandas rolling() function on the entire dataframe results in the rolling window estimate of all the columns for which it can be calculated (that is, all the numerical columns for rolling mean).

If you want to calculate the rolling estimate for a specific column you can apply the rolling function on the column itself instead of the entire dataframe.

# rolling mean of Revenue
print(df['Revenue'].rolling(3).mean())

Output:

2020-03-01          NaN
2020-03-02          NaN
2020-03-03    12.333333
2020-03-04    15.666667
2020-03-05    20.666667
2020-03-06    24.000000
2020-03-07    22.000000
Name: Revenue, dtype: float64

For more on the pandas rolling() function, refer to its documentation.

With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having pandas version 1.0.5