Group by month and year and sum all columns in Python

Question

I have a dataframe with 10 columns of daily observations and then I have a date column. I'd like to sum the daily observations and group them by month and year.

The data looks similar to this:

    ds          c1         c2          c3          c4          c5          c6 
2019-05-27  285.029066  56.891208   404.848509  172.780268  391.853462  -47.865271  
2019-05-28  284.742624  83.432062   419.062742  172.039440  391.919534  -38.753380  
2019-05-29  284.456182  79.556789   413.650187  171.003154  391.985605  -36.871281  
2019-05-30  284.169740  63.251651   406.679183  170.160845  392.174533  -38.606698          
2019-05-31  283.883298  99.122362   441.525001  169.359221  392.463681  -7.067061   

The output I expect is

   ds    c1  c2  c3  c4  c5  c6
2019-05 xx1 xx2 xx3 xx4 xx5 xx6

Where xx1 through xx6 are the sums for observations between 2019-05-27 and 2019-05-31.

Thanks in advance.

Answer 1

Use DataFrame.resample with MS for start of month:

#datetimeindex
df['ds'] = pd.to_datetime(df['ds'])
df = df.set_index('ds')

df = df.resample('MS').sum()
print (df)
                    c1          c2           c3          c4           c5  \
ds                                                                         
2019-05-01  1422.28091  382.254072  2085.765622  855.342928  1960.396815   

                    c6  
ds                      
2019-05-01 -169.163691  

Or use month periods:

df['ds'] = pd.to_datetime(df['ds'])

df2 = df.groupby(df['ds'].dt.to_period('m')).sum()
print (df2)
                 c1          c2           c3          c4           c5  \
ds                                                                      
2019-05  1422.28091  382.254072  2085.765622  855.342928  1960.396815   

                 c6  
ds                   
2019-05 -169.163691