Q:

pandas: rolling mean not working

I have a pandas data frame as follows:
         Dates  Var_1   Var_2
0   2018-01-09  612.0  368.0  
1   2018-01-10  348.0  348.0  
2   2018-01-11  350.0  337.0  
3   2018-01-12  335.0  337.0  
4   2018-01-13  334.0 900.0  
5   2018-01-14  325.0  325.0 
6   2018-01-15  580.0  317.0 
7   2018-01-16  334.0 900.0  
8   2018-01-17  325.0  325.0 

I tried to calculate mean of each 3 rolling elements for each column.
For example:
mean of 2018-01-09, 2018-01-10, 2018-01-11 >> 2018-01-10
mean of 612.0, 348.0, 350.0 >> 436.66

Then, output 3 rows by 2 columns pandas dataframe.
I tried as:
result = df.set_index('Dates').rolling(3).mean().reset_index()

But unsuccessful.
Please help me, guys

A:

You can groupby by floor divide np.arange by 3 and if want also mean of datetimes need convert to np.int64 for native unix datetimes in nanoseconds and last convert back:
print (np.arange(len(df)))
[0 1 2 3 4 5 6 7 8]

print (np.arange(len(df)) // 3)
[0 0 0 1 1 1 2 2 2]

df['Dates'] = pd.to_datetime(df['Dates']).astype(np.int64)
print (df)
                 Dates  Var_1  Var_2
0  1515456000000000000  612.0  368.0
1  1515542400000000000  348.0  348.0
2  1515628800000000000  350.0  337.0
3  1515715200000000000  335.0  337.0
4  1515801600000000000  334.0  900.0
5  1515888000000000000  325.0  325.0
6  1515974400000000000  580.0  317.0
7  1516060800000000000  334.0  900.0
8  1516147200000000000  325.0  325.0

df = df.groupby(np.arange(len(df)) // 3).mean()
df['Dates'] = pd.to_datetime(df['Dates'])
print (df)
       Dates       Var_1       Var_2
0 2018-01-10  436.666667  351.000000
1 2018-01-13  331.333333  520.666667
2 2018-01-16  413.000000  514.000000

If omit converting, Dates column is silently removed, because automatic exclusion of nuisance columns.
df = df.groupby(np.arange(len(df)) // 3).mean()
print (df)
        Var_1       Var_2
0  436.666667  351.000000
1  331.333333  520.666667
2  413.000000  514.000000

