Finding an hour of daily high using Pandas in Python

Question

I try to find the hour of maximum demand every day in my time series of demand.

I created a DataFrame that looks like.

power 2011-01-01 00:00:00 1015.70 2011-01-01 01:00:00 1015.70 2011-01-01 02:00:00 1010.30 2011-01-01 03:00:00 1010.90 2011-01-01 04:00:00 1021.10 2011-01-01 05:00:00 1046.00 2011-01-01 06:00:00 1054.60 ... 

and grouped series to find the maximum value from each day using .max ()

 grouped = df.groupby(pd.TimeGrouper('D')) grouped['power'].max() 

OUTPUT

 2011-01-01 1367.30 2011-01-02 1381.90 2011-01-03 1289.00 2011-01-04 1323.50 2011-01-05 1372.70 2011-01-06 1314.40 2011-01-07 1310.60 ... 

However, I also need an hour of maximum value. So something like:

 2011-01-01 18 1367.30 2011-01-02 5 1381.90 2011-01-03 22 1289.00 2011-01-04 10 1323.50 ... 

I tried using idxmax (), but I keep getting ValueError

Answer 1

try the following:

 In [376]: df.loc[df.groupby(pd.TimeGrouper('D')).idxmax().iloc[:, 0]] Out[376]: power 2011-01-01 06:00:00 1054.6 2011-01-02 06:00:00 2054.6 

Data:

 In [377]: df Out[377]: power 2011-01-01 00:00:00 1015.7 2011-01-01 01:00:00 1015.7 2011-01-01 02:00:00 1010.3 2011-01-01 03:00:00 1010.9 2011-01-01 04:00:00 1021.1 2011-01-01 05:00:00 1046.0 2011-01-01 06:00:00 1054.6 2011-01-02 00:00:00 2015.7 2011-01-02 01:00:00 2015.7 2011-01-02 02:00:00 2010.3 2011-01-02 03:00:00 2010.9 2011-01-02 04:00:00 2021.1 2011-01-02 05:00:00 2046.0 2011-01-02 06:00:00 2054.6