CODEWITHSUNDEEP
pythonpandasdataframe
JSolomonCulp
JSolomonCulp

Reputation: 1574

Group Daily Data by Week for Python Dataframe

So I have a Python dataframe that is sorted by month and then by day, 

In [4]: result_GB_daily_average
Out[4]:
                NREL      Avert
Month Day
1     1    14.718417  37.250000
      2    40.381167  45.250000
      3    42.512646  40.666667
      4    12.166896  31.583333
      5    14.583208  50.416667
      6    34.238000  45.333333
      7    45.581229  29.125000
      8    60.548479  27.916667
      9    48.061583  34.041667
      10   20.606958  37.583333
      11    5.418833  70.833333
      12   51.261375  43.208333
      13   21.796771  42.541667
      14   27.118979  41.958333
      15    8.230542  43.625000
      16   14.233958  48.708333
      17   28.345875  51.125000
      18   43.896375  55.500000
      19   95.800542  44.500000
      20   53.763104  39.958333
      21   26.171437  50.958333
      22   20.372688  66.916667
      23   20.594042  42.541667
      24   16.889083  48.083333
      25   16.416479  42.125000
      26   28.459625  40.125000
      27    1.055229  49.833333
      28   36.798792  42.791667
      29   27.260083  47.041667
      30   23.584917  55.750000

This continues on for every month of the year and I would like to be able to sort it by week instead of day, so that it looks something like this:

In [4]: result_GB_week_average
Out[4]:
                NREL      Avert
Month Week
1     1    Average values from first 7 days
      2    Average values from next 7 days
      3    Average values from next 7 days
      4    Average values from next 7 days

And so forth. What would the easiest way to do this be?

Upvotes: 0

Views: 219

Answers (1)

Shahram
Shahram

Reputation: 814

I assume by weeks you don't mean actual calendar week!!! Here is my proposed solution:

#First add a dummy column
result_GB_daily_average['count'] = 1

#Then calculate a cumulative sum and divide it by 7
result_GB_daily_average['Week'] = result_GB_daily_average['count'].cumsum() / 7.0

#Then Round the weeks
result_GB_daily_average['Week']=result_GB_daily_average['Week'].round()

#Then do the group by and calculate average
result_GB_week_average = result_GB_daily_average.groupby('Week')['NREL','AVERT'].mean()

Upvotes: 1

Related Questions