My data looks like this:

Date                 Value
2011-01-01 09:00     1
2011-01-01 10:00     2
2011-02-18 09:00     3
...
2017-01-28 07:00     4

What I need is the average for each month (January, February.. etc) over the years so output should be:

Month        Avg
January      ...
February     ...
...

What I did what doing df.resample("M").mean(), which kind of works but it is not over the years, it's an averge of each month for a specific year:

Month              Avg
January 2011       ...
February 2011      ...
...
January 2017       ...

Which is not what I'm looking for. I tried to use groupby without success. Any idea how I can solve this matter?

CodePudding user response：

Try:

df.groupby(df.Date.dt.month).Value.mean()

You can then use calendar to convert month numbers to names

import calendar
df.index = df.index.map(lambda x: calendar.month_name[x])