Pandas: Average over the same hour

Question

I have a csv which looks like this:

YYYY-MO-DD HH-MI-SS_SSS             ATMOSPHERIC PRESSURE (hPa) mean
2/24/2016 13:00                            1011.937618
2/24/2016 14:00                            1011.721583
2/24/2016 15:00                            1011.348064
2/24/2016 16:00                            1011.30785
2/24/2016 17:00                            1011.3198
2/24/2016 18:00                            1011.403372
2/24/2016 19:00                            1011.485108
2/24/2016 20:00                            1011.270083
2/24/2016 21:00                            1010.936331
2/24/2016 22:00                            1010.920958
2/24/2016 23:00                            1010.816478
2/25/2016 00:00                            1010.899142
2/25/2016 01:00                            1010.209392
2/25/2016 02:00                            1009.700625
2/25/2016 03:00                            1009.457683
2/25/2016 04:00                            1009.268081
2/25/2016 05:00                            1009.718639
2/25/2016 06:00                            1010.745444
2/25/2016 07:00                            1011.062028
2/25/2016 08:00                            1011.168117
2/25/2016 09:00                            1010.771281
2/25/2016 10:00                            1010.138053
2/25/2016 11:00                            1009.509119
2/25/2016 12:00                            1008.703811
2/25/2016 13:00                            1008.021547
2/25/2016 14:00                            1007.774825
   .....                                     .....

I want to create a new dataframe with averages over the same hour of every day:

Hour                     Average
13:00                   1009.9795825
14:00                   1009.748204
15:00                    ....

Any easy ways to do it ?

Thanks !

unutbu · Accepted Answer · 2016-04-09T16:45:29.523

Once you parse the dates into a Pandas datetime-like Series, then you can access the hour of the time series using the dt accessor:

df['YYYY-MO-DD HH-MI-SS_SSS'] = pd.to_datetime(df['YYYY-MO-DD HH-MI-SS_SSS'])
hour = pd.to_timedelta(df['YYYY-MO-DD HH-MI-SS_SSS'].dt.hour, unit='H')

Then you can group by hour and compute the mean for each group:

df.groupby(hour).mean()

import pandas as pd
df = pd.DataFrame(
    {'ATMOSPHERIC PRESSURE (hPa) mean': 
     [1011.937618, 1011.721583, 1011.348064, 1011.30785, 1011.3198, 1011.403372, 
      1011.485108, 1011.270083, 1010.936331, 1010.920958, 1010.816478, 1010.899142, 
      1010.209392, 1009.700625, 1009.457683, 1009.268081, 1009.718639, 1010.745444, 
      1011.062028, 1011.168117, 1010.771281, 1010.138053, 1009.509119, 1008.703811, 
      1008.021547, 1007.774825],
     'YYYY-MO-DD HH-MI-SS_SSS': 
     ['2/24/2016 13:00', '2/24/2016 14:00', '2/24/2016 15:00', '2/24/2016 16:00', 
      '2/24/2016 17:00', '2/24/2016 18:00', '2/24/2016 19:00', '2/24/2016 20:00', 
      '2/24/2016 21:00', '2/24/2016 22:00', '2/24/2016 23:00', '2/25/2016 00:00', 
      '2/25/2016 01:00', '2/25/2016 02:00', '2/25/2016 03:00', '2/25/2016 04:00', 
      '2/25/2016 05:00', '2/25/2016 06:00', '2/25/2016 07:00', '2/25/2016 08:00', 
      '2/25/2016 09:00', '2/25/2016 10:00', '2/25/2016 11:00', '2/25/2016 12:00', 
      '2/25/2016 13:00', '2/25/2016 14:00']})
df['YYYY-MO-DD HH-MI-SS_SSS'] = pd.to_datetime(df['YYYY-MO-DD HH-MI-SS_SSS'])

hour = pd.to_timedelta(df['YYYY-MO-DD HH-MI-SS_SSS'].dt.hour, unit='H')
hour.name = 'Hour'
result = df.groupby(hour).mean()

yields

                         ATMOSPHERIC PRESSURE (hPa) mean
YYYY-MO-DD HH-MI-SS_SSS                                 
00:00:00                                     1010.899142
01:00:00                                     1010.209392
02:00:00                                     1009.700625
03:00:00                                     1009.457683
04:00:00                                     1009.268081
05:00:00                                     1009.718639
...

Pandas: Average over the same hour

1 Answers1