2

I have a small Data Frame where the row index is date-time. I want to sum the weeks into months, but am struggling. Any suggestions? Can't seem to figure out how to group by the index.

               A       B       C       D
Date                                      
2023-01-08  0.0000  0.7075  0.0000   0.000
2023-01-15  0.0000  1.0000  0.0000   0.000
2023-01-22  0.0000  1.0000  0.0000   0.000
2023-01-29  0.0325  0.9300  0.0000   0.025
2023-02-05  0.0250  0.9750  0.0000   0.000
2023-02-12  0.0050  0.5450  0.0000   0.000
2023-02-19  0.0000  0.1800  0.0000   0.000
2023-02-26  0.0000  0.0000  0.0000   0.000
2023-03-05  0.0000  0.0000  0.0000   0.000
2023-03-12  0.0000  0.0000  0.0000   0.000
2023-03-19  0.0000  0.0000  0.0000   0.000
2023-03-26  0.0000  0.4375  0.1875   0.000
Jim Rutter
  • 45
  • 4

1 Answers1

0

You should convert to_period:

df.groupby(pd.to_datetime(df.index).to_period('M')).sum()

Output:

              A       B       C      D
Date                                  
2023-01  0.0325  3.6375  0.0000  0.025
2023-02  0.0300  1.7000  0.0000  0.000
2023-03  0.0000  0.4375  0.1875  0.000

alternative

resample on month start (MS):

df.index = pd.to_datetime(df.index)

out = df.resample('MS').sum()

Output:

                 A       B       C      D
Date                                     
2023-01-01  0.0325  3.6375  0.0000  0.025
2023-02-01  0.0300  1.7000  0.0000  0.000
2023-03-01  0.0000  0.4375  0.1875  0.000
mozway
  • 194,879
  • 13
  • 39
  • 75
  • @Barmar I added an alternative with `resample`, this way it's possible to use any frequency ;) – mozway Apr 04 '23 at 20:51
  • The resample version worked great. Thanks! I want to divide the sum by the number of weeks that make up the sum.. any ideas? Since some months will have 5 weeks. – Jim Rutter Apr 04 '23 at 21:01
  • @Jim you can calculate the week number of the last day of the month (see [here](https://stackoverflow.com/questions/3806473/week-number-of-the-month)). If you use `M` in place of `MS` in `resample` you'll get the last day of the month – mozway Apr 04 '23 at 22:08