Open rolling window backwards in pandas

2024-01-302024-02-22

I can open rolling window forward.
But how can I open it backwards ?

With using pandas, sometimes you may want to open window not forward but backwards.

But how can we Open rolling window backwards.

Today I will introduce about "How to open rolling window backwards in pandas".

What is window direction ?

If you calculate moving average with below csv, initial some records show NaN because they don't have enough width for window.
Window is opened forwards. So the window function result of 2019/01/07 is (7+8+9+10)/4 = 8.5.

date,value
2019/01/10,10
2019/01/09,9
2019/01/08,8
2019/01/07,7
2019/01/06,6
2019/01/05,5
2019/01/04,4
2019/01/03,3
2019/01/02,2
2019/01/01,1

import pandas as pd

# read CSV
df_csv = pd.read_csv("rolling_forward.csv", encoding='shift-jis')

# get average in latest 4 records
df_csv["MA"] = df_csv["value"].rolling(window=4).mean()
print(df_csv)

         date  value   MA
0  2019/01/10     10  NaN
1  2019/01/09      9  NaN
2  2019/01/08      8  NaN
3  2019/01/07      7  8.5
4  2019/01/06      6  7.5
5  2019/01/05      5  6.5
6  2019/01/04      4  5.5
7  2019/01/03      3  4.5
8  2019/01/02      2  3.5
9  2019/01/01      1  2.5

So how can we open window backwards to get a result (7+6+5+4)/4 = 5.5 for 2019/01/07 ?

Change direction of window

Even in cocument of DataFrame, nothing is written to open window backwards.
There is how to open window from center position.

I didn't get any information for a long time.
Then I found a article in stackoverflow.

Rolling backwards is the same as rolling forward and then shifting the result:
x.rolling(window=3).sum().shift(-2)
python – How to Reverse Rolling Sum? – Stack Overflow

It says "you can get same result with rolling forwards and shift."
I tried it.

# shift
df_csv["MA2"] = df_csv["value"].rolling(window=4).mean().shift(-3)
print(df_csv)

         date  value   MA  MA2
0  2019/01/10     10  NaN  8.5
1  2019/01/09      9  NaN  7.5
2  2019/01/08      8  NaN  6.5
3  2019/01/07      7  8.5  5.5
4  2019/01/06      6  7.5  4.5
5  2019/01/05      5  6.5  3.5
6  2019/01/04      4  5.5  2.5
7  2019/01/03      3  4.5  NaN
8  2019/01/02      2  3.5  NaN
9  2019/01/01      1  2.5  NaN