Open rolling window backwards in pandas

2019-03-02

Open rolling window backwards in pandas

Open rolling window backwards in pandas

With using pandas, you may want to open window backwards.
This article shows how to do it.

What is window function ?

With using window function, we can get a part of list. And also we can get summary or average in the part.
For more information about window function, please see previous article.

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.

rolling_forward.csv

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

rolling_forward.py

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)

Result

         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

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.

rolling_forward.py

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

Result

         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

Oh, it is really same as I expected.

Finally

Rolling backwards = Rolling forwards + shift