1

I have a DataFrame which has a datetimeindex ranging from 2004-01-01 01:00 to 2014-12-31- 00:00

2004-01-01 00:00:00+01:00          
2004-01-01 01:00:00+01:00          
2004-01-01 02:00:00+01:00         
2004-01-01 03:00:00+01:00
.
.
.
2014-12-30 22:00:00+01:00      
2014-12-30 23:00:00+01:00       
2014-12-31 00:00:00+01:00

Id like to find all days and months and hours that are equal independent of year and take the mean of those values.

E.g. If I ask for "YYYY-01-01 01:00" I'd be taking the mean of those values stored in the indexes

"2004-01-01 01:00", "2005-01-01 01:00", "2006-01-01 01:00", "2007-01-01 01:00", "2008-01-01 01:00", "2009-01-01 01:00", "2010-01-01 01:00", "2011-01-01 01:00", 
"2012-01-01 01:00", "2013-01-01 01:00", "2014-01-01 01:00"

I tried creating a new year with hourly frequency and loop over all dates but those take forever. df_inflow is the DataFrame with the index from 2004 to 2014 which is given by the function inflow() 

date_range = pd.date_range(start=pd.to_datetime('2019-01-01'), 
end=pd.to_datetime('2019-12-31'), tz='Europe/Oslo', freq='H')
df_inflow = inflow() 

for date in date_range:
    temp_inflow = df_inflow.loc[(df_inflow.index.strftime('%m-%d %H') == 
    date)].mean()[0]
    # Save mean
nitin3685
  • 825
  • 1
  • 9
  • 20
Olba12
  • 305
  • 2
  • 14

1 Answers1

1

I believe you need DataFrame.groupby with mean:

df = df_inflow.groupby(df_inflow.index.strftime('%m-%d %H')).mean()

Or if need mean of column 0:

df = df_inflow.groupby(df_inflow.index.strftime('%m-%d %H'))[0].mean()
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • df = df_inflow.groupby(df_inflow.index.strftime('%m-%d %H'))[0].mean() returned 'Column not found: 0' so I tried to change 0 to the real column name, which returned 'No numeric types to aggregate' – Olba12 Nov 08 '19 at 08:01
  • @Olba12 - so column should be numeric? then use `df['col'] = pd.to_numeric(df['col'], error='coerce')` - check also another solutions [here](https://stackoverflow.com/questions/15891038/change-data-type-of-columns-in-pandas) – jezrael Nov 08 '19 at 08:03
  • 1
    Got a result now, seem promising. Need to some sanity checks – Olba12 Nov 08 '19 at 08:12