Rolling Average in Pandas

Question

I have a dataframe with 2 columns - Date and Price. The data is sorted with newest date first (23 Jan in first row, 22 Jan in second row and so on).

Date   Price
23 Jan 100
22 Jan 95
21 Jan 90
.
.
.

I want to calculate 2 days rolling average price for this time series data. I am using this:

df.rolling(2).mean()

What this does is, it assigns NaN to the first row (23 Jan) and then for the second row gives the output as the mean of prices on 23 Jan and 22 Jan. This is not useful as 22 Jan average is using forward data (price of 23 Jan). What I need is that the moving average value for 23 Jan is the average of 23 Jan & 22 Jan. This way the last value of MA would be NaN instead of first value.

What I do not want to do is sort this data with oldest first, compute and then resort.

I had the same issue with pct_change(). However, pct_change(-1) solved that issue. But rolling does not accept negative value as an input. Please suggest a workaround this issue. Thanks.

Why don't you want your data to be sorted chronologically (in ascending order)? Is there a particular reason? — Joe Patten
I have been developing these systems in excel and now moving them to python for easier development. I am used to seeing values sorted as newest date first. Irrespective, I would assume that python will have some way to take care of this issue. Just want to check on that. Changing the sorting would be the last resort anyways. — Manish Jain

Joe Patten Joe Patten · Accepted Answer · 2019-01-23T07:10:03

Since you don't want to sort, here is one workaround. You could reverse your dataframe, take the rolling mean, then reverse it again.

df[::-1].rolling(window=2).mean()[::-1]

Output:

        Price
23 Jan  97.5
22 Jan  92.5
21 Jan  NaN

Rolling Average in Pandas

1 Answers