0

I have a dataframe containing ~3 years of data looking something like this:

enter image description here

I can groupby the whole dataframe by weekdays, to get the average value for each of them, by doing

df_weekday = df.groupby(df.index.weekday).mean()

But I want to have not just the average value for each weekday, but have this for every season within my dataframe of ~3 years - meaning, average value for Mon to Sun for winter, average value for Mon to Sun for spring and so on.

How can I do this? Tnx

NeStack
  • 1,739
  • 1
  • 20
  • 40
  • https://stackoverflow.com/questions/17679089/pandas-dataframe-groupby-two-columns-and-get-counts – Chana Drori Aug 06 '23 at 14:29
  • 1
    Hi! Why opting to post the input data as picture? https://stackoverflow.com/q/20109391/12846804 – OCa Aug 06 '23 at 17:13
  • Does this answer your question? [Python: Datetime to season](https://stackoverflow.com/questions/44124436/python-datetime-to-season) – OCa Aug 06 '23 at 17:22
  • 1
    @OCa Thanks for the hint, I have taken note to follow this guideline in the future! – NeStack Aug 07 '23 at 21:06

2 Answers2

1

You can group by more than 1 columns:

# Use whatever logic to determine your season. Here, I simply assign each
# quarter (92 days) to a season
season = (df.index.day_of_year // 92).map({
    0: "Winter",
    1: "Spring",
    2: "Summer",
    3: "Fall"
})
df.groupby([season, df.index.weekday]).mean()
Code Different
  • 90,614
  • 16
  • 144
  • 163
0

Using the answer by Code Different and the code found in this answer here I created my own answer. It allows for custom ranges for the seasons. This is how it looks like this:

def season_of_date(date):
    year = str(date.year)
    seasons = {'spring': pd.date_range(start='03/01/'+year, end='05/31/'+year),
               'summer': pd.date_range(start='06/01/'+year, end='08/31/'+year),
               'autumn': pd.date_range(start='09/01/'+year, end='11/30/'+year)}
    if date in seasons['spring']:
        return 'spring'
    if date in seasons['summer']:
        return 'summer'
    if date in seasons['autumn']:
        return 'autumn'
    else:
        return 'winter'

# Assuming df has a date column of type `datetime`
df['season'] = df.index.map(season_of_date)
df_season_dow = df.groupby([df.season, df.index.weekday]).mean()
NeStack
  • 1,739
  • 1
  • 20
  • 40