I have a dataframe, df, with index as date column and the columns with integers.
c = {'Date': ['2020-12-08','2020-12-09','2020-12-10','2020-12-11','2020-12-12','2020-12-13','2020-12-14'], 'Name1': [11,5,85,128,4,3,233], 'Name2':[18,1,39,63,2,3,330],'Name3':[15,32,80,89,54,70,89], 'Name4' :[1,2,5,75,4,1,25], 'Name5':[394,937,840,788,259,241,718], 'Name6':[66,96,118,43,31,27,50], 'Name7':[34,67,72,76,0,0,151]}
df = pd.DataFrame(data=c).set_index('Date')
df
Date Name1 Name2 Name3 .... Name7
...
2020-12-08 11 18 15
2020-12-09 5 1 32
2020-12-10 85 39 80
2020-12-11 128 ...
2020-12-12 4 ...
2020-12-13 3 ...
2020-12-14 233 ...
and I want to get moving averages on each column.
I have tried it on 1 column which is fine - it gives the first 6 nans following by the 7-day averages.
df_a = (df['Name1'].rolling(7)).mean()
df_a.tail(10)
2020-12-08 NaN
2020-12-09 NaN
2020-12-10 NaN
2020-12-11 NaN
2020-12-12 NaN
2020-12-13 NaN
2020-12-14 67.000000
2020-12-15 132.857143
....
Now, I'm trying to do the same for all of the columns
cols= ['Name1','Name2','Name3','Name4','Name5','Name6','Name7']
df_a = df.apply(lambda x: (x[cols].rolling(7)).mean(), axis=1)
and it gives me something like this - nan for the first 6 columns.
Date Name1 Name2 Name3 Name4 Name Name6 Name7
2020-12-08 NaN NaN NaN NaN NaN NaN 77
2020-12-09 NaN NaN NaN NaN NaN NaN 163
2020-12-10 NaN NaN NaN NaN NaN NaN 177
2020-12-11 NaN NaN NaN NaN NaN NaN 180
2020-12-12 NaN NaN NaN NaN NaN NaN 51
How can I amend the above so it gives me the rolling averages row-wise?
Seems like your Date column is already the row index. As such, you can simply use:
df.rolling(7).mean()
If your Date column is not already the row index, you can try:
df.set_index('Date').rolling(7).mean()