Averaging the last 7 days in a dataframe based on a specific label against the adjoining column

user_7605946 0
Viewed 32

I'm working with a time series dataframe which shows cumulative positions of a given entity for each hour of the day from 01/06/2022 - 22/08/2022. I'm looking to take the average of the last 7 days which have a specific label against it, which may not necessarily fall on the previous 7 days leading up to the 22/08/2022, see example below:

Example 1

The labels against the entries can include:

  • 5f
  • 5i
  • 5j
  • 5x
  • 5h

Each day will have one label against it, with it repeating for each hour in the day. To put it simply, I want to average the most recent 7 days in the 2 month dataframe which has one of these specific labels against it, i.e The average cumulativeVol for each hour for the past 7 days where we have had a 5f strategy, or 5i etc.

Expected output of the script should have a DF dimension of [24x1].

I'm wondering can this be achieved solely through Pandas? Or would a tailored method need developed?

Any help greatly appreciated.

pandas average python
1 Answers

IIUC this will give the average cumulativeVols per labels:

last_week = df[df.StartDateTime >= (df.StartDateTime.iloc[-1] - pd.Timedelta(weeks=1))]
last_week.set_index('StartDateTime', inplace=True)
last_week.groupby([pd.Grouper(freq='H'), 'Strategy']).cumulativeVols.mean()
user_192557490
Related