How to sum up multiple pandas dataframes with a rolling average window?

user_11028689 0
Viewed 112

I am using a rolling window and a shift on my dataframe, df.

import pandas as pd
from functools import reduce

df = pd.DataFrame({
   'date': ['2021-01-01','2021-01-02', '2021-01-03', '2021-01-04', '2021-01-05','2021-01-06','2021-01-07','2021-01-08','2021-01-09','2021-01-10'],
   'value': [10,20,20,30,20,10,30,20,10,20]
})
df.date = pd.to_datetime(df.loc[:, 'date'], format='%Y-%m-%d')
df.set_index('date',inplace=True)
df
value
date    
2021-01-01  10
2021-01-02  20
2021-01-03  20
2021-01-04  30
2021-01-05  20
2021-01-06  10
2021-01-07  30
2021-01-08  20
2021-01-09  10
2021-01-10  20

I want to get a dataframe, df_all, which looks like this. the value for 01-04 is (10+20+20)/3 = 17, the value for 01-05 is 17+ (20+20+30)/3 = 40, the value for 01-06 is 17+(20+20+30)/3+(20+30+20)/3=63 and so on.

Note that the shift(time interval) is repeated for 5 days, so 01-09 is 23+23+20+20+20 =106, and 01-10 =23+20+20+20+20 = 103.

   value
date    
2021-01-01  0.0
2021-01-02  0.0
2021-01-03  0.0
2021-01-04  17.0
2021-01-05  40.0
2021-01-06  63.0
2021-01-07  83.0
2021-01-08  103.0
2021-01-09  106.0
2021-01-10  103.0

I have achieved this by this code:

df1 = (((df.rolling(3)).mean())).shift(1).rolling(1).mean().fillna(0).round()
df2 = (((df.rolling(3)).mean())).shift(2).rolling(1).mean().fillna(0).round()
df3 = (((df.rolling(3)).mean())).shift(3).rolling(1).mean().fillna(0).round()
df4 = (((df.rolling(3)).mean())).shift(4).rolling(1).mean().fillna(0).round()
df5 = (((df.rolling(3)).mean())).shift(5).rolling(1).mean().fillna(0).round()

df_all = reduce(lambda i, j: i.add(j, fill_value=0), [df1, df2, df3, df4, df5])
df_all

However, my real dataset is very big with 70 of these shifts (it is a time interval in medical data). so I will have df1, df2,.... df70 and it is very laborious.

Can somebody help me with a more compact code for my problem? I imagine it would be a loop of some kind. thank you.

P.S. 
to aid the understanding of this problem, imagine these numbers horizontally as one line. then you shift this line 1 column to the right, and then again and again up to 5 times.

10,20,20,30,20,10,30,20,10,20
   10,20,20,30,20,10,30,20,10,20
      10,20,20,30,20,10,30,20,10,20
         10,20,20,30,20,10,30,20,10,20
            10,20,20,30,20,10,30,20,10,20
First 10 column totals:
10,30,50,80,100,100,110,110,80,90. 
(in my example, these are taken as 3-day averages).
pandas dataframe for-loop python-3.x
2 Answers

IIUC, here's one way:

df['value'] = df.rolling(3).mean().shift().cumsum().fillna(0).round()
print(df)
OUTPUT:
            value
date             
2021-01-01    0.0
2021-01-02    0.0
2021-01-03    0.0
2021-01-04   17.0
2021-01-05   40.0
2021-01-06   63.0
2021-01-07   83.0
2021-01-08  103.0
2021-01-09  123.0
2021-01-10  143.0
user_154380330

Since you edited your post, the answer of @Nk03 is incomplete.

Use np.cumsum to avoid multiple rolling:

df['value'] = df.rolling(3).mean().shift().cumsum().fillna(0).round()
df['value'] = df['value'].sub(df['value'].shift(5, fill_value=0))

>>> df
            value
date
2021-01-01    0.0
2021-01-02    0.0
2021-01-03    0.0
2021-01-04   17.0
2021-01-05   40.0
2021-01-06   63.0
2021-01-07   83.0
2021-01-08  103.0
2021-01-09  106.0
2021-01-10  103.0
user_152399510
Related