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