CODEWITHSUNDEEP
pythonpandasweek-number
Ollie
Ollie

Reputation: 209

Separate out weeks in a year and calculate averages in a dataframe

I have a data frame for hourly intervals of prices for a whole year that looks like this:

                      DE     FR     NL     CH     BE     AT  peak  offpeak
2015-12-14 00:00:00  30.93  36.56  32.44  45.53  32.44  28.50     0        1
2015-12-14 01:00:00  31.49  31.49  31.49  42.12  31.49  26.65     0        1
2015-12-14 02:00:00  29.50  29.50  29.50  41.24  29.50  27.00     0        1
2015-12-14 03:00:00  27.84  27.84  27.84  40.29  27.84  26.75     0        1
2015-12-14 04:00:00  27.45  27.45  27.45  37.72  27.45  26.00     0        1
2015-12-14 05:00:00  29.65  29.65  29.65  43.44  29.65  29.37     0        1
2015-12-14 06:00:00  41.26  41.26  41.26  54.99  41.26  41.30     0        1
2015-12-14 07:00:00  53.46  53.46  53.46  66.89  53.46  53.43     0        1
2015-12-14 08:00:00  56.70  56.70  56.70  68.90  56.70  57.30     1        0
2015-12-14 09:00:00  51.50  60.47  51.39  68.95  59.03  54.21     1        0

...                    ...    ...    ...    ...    ...    ...   ...      ...
2016-12-14 20:00:00  67.54  71.20  67.47  65.99  70.38  53.88     1        0
2016-12-14 21:00:00  47.94  64.47  47.92  60.69  60.77  49.14     0        1
2016-12-14 22:00:00  41.71  63.65  39.57  59.97  59.49  40.00     0        1
2016-12-14 23:00:00  35.57  62.45  33.08  57.38  57.53  35.66     0        1

I want to be able to separate out each week in the year and sum together the average for each week for each of the columns DE,FR,NL,CH,BE,AT.

If anyone could help with this. It would be great!

Upvotes: 1

Views: 84

Answers (1)

Julien Marrec
Julien Marrec

Reputation: 11895

Given this hourly fake dataset for a full year:

cols = ['DE', 'FR', 'NL', 'CH', 'BE', 'AT']
df = pd.DataFrame(np.random.random((8760,len(cols))),
                  index=pd.date_range('2015-01-01', freq='H',periods=8760),
                  columns=cols)

You can use DataFrame.resample and apply the mean function to it:

df.resample('W').mean().sum()

If you want to be able to specify the start day of the week, look at Anchored Offsets below. W is the same as W-SUN, for mondays you can use W-MON, etc.

Documentation:

Upvotes: 1

Related Questions