For Loop to average datetime data

Question

I'm trying to average various columns of data based on months. eg. find the average value of B for April 2019.

Date        A       B       C       D
2012-04-01  173.53  10.81   133.96  28.75
2012-04-02  197.99  10.92   147.91  39.17
2012-04-03  211.35  10.97   164.73  35.64
2012-04-04  249.39  11.02   200.62  37.75
2012-04-05  233.59  11.39   187.57  34.64

I have figured out how to average each column based on the month and year;

index = pd.date_range('2012 04 01', '2019, 12, 31', freq='1MS')

apr12 = (demdf.index.year==2012) & (demdf.index.month==4)
apr12ave = demdf.loc[apr12].mean(axis=0)

But I can't figure out how to create a loop which would loop through and get the average of each month from each year (dataset goes from 2014 to 2019).

Any help much appreciated!

ansev · Accepted Answer · 2020-01-09T18:18:59.960

Use DataFrame.resample:

#df['Date'] = pd.to_datetime(df['Date'])
df.resample('M',on='Date').mean()

or pd.Grouper with DataFrame.groupby

#df['Date'] = pd.to_datetime(df['Date'])
df.groupby(pd.Grouper(freq = 'M',key='Date')).mean()

Output

                 A       B        C      D
Date                                      
2012-04-30  213.17  11.022  166.958  35.19

If you want a MultiIndex (year,month) DataFrame.

df.groupby([df.Date.dt.year,df.Date.dt.month]).mean()

For Loop to average datetime data

1 Answers1