Retaining time of max with Pandas GroupBy and resample

Question

I have a Pandas DataFrame with multiple stations and predicted hourly wind gusts ("gust_speed") for each station. I need to find the max wind gust for each station for each day, along with the time at which that gust occurs.

pd_df
Out[24]: 
                    station  gust_speed
valid_dt                               
2020-05-21 15:00:00     AMO           6
2020-05-21 16:00:00     AMO           8
2020-05-21 17:00:00     AMO          11
2020-05-21 18:00:00     AMO          17
2020-05-21 19:00:00     AMO          19
2020-05-21 20:00:00     AMO          20
2020-05-21 21:00:00     AMO          21
2020-05-21 22:00:00     AMO          22
2020-05-21 23:00:00     AMO          24
2020-05-22 00:00:00     AMO          22
2020-05-22 01:00:00     AMO          18
2020-05-22 02:00:00     AMO          13
2020-05-22 03:00:00     AMO           8
2020-05-22 04:00:00     AMO           5
2020-05-22 05:00:00     AMO           2
2020-05-22 06:00:00     AMO           1
2020-05-22 07:00:00     AMO           2
2020-05-22 08:00:00     AMO           2
2020-05-22 09:00:00     AMO           3
2020-05-22 10:00:00     AMO           2
2020-05-22 11:00:00     AMO           3
2020-05-22 12:00:00     AMO           4
2020-05-22 13:00:00     AMO           3
2020-05-22 14:00:00     AMO           2
2020-05-22 15:00:00     AMO           7
2020-05-22 16:00:00     AMO           7
2020-05-22 17:00:00     AMO          10
2020-05-22 18:00:00     AMO          13
2020-05-22 19:00:00     AMO          19
2020-05-22 20:00:00     AMO          21
                    ...         ...
2020-05-23 19:00:00     YSA          21
2020-05-23 20:00:00     YSA          22
2020-05-23 21:00:00     YSA          22
2020-05-23 22:00:00     YSA          23
2020-05-23 23:00:00     YSA          22
2020-05-24 00:00:00     YSA          20
2020-05-24 01:00:00     YSA          17
2020-05-24 02:00:00     YSA          14
2020-05-24 03:00:00     YSA           4
2020-05-24 04:00:00     YSA           0
2020-05-24 05:00:00     YSA           1
2020-05-24 06:00:00     YSA           1
2020-05-24 07:00:00     YSA           3
2020-05-24 08:00:00     YSA           3
2020-05-24 09:00:00     YSA           4
2020-05-24 10:00:00     YSA           5
2020-05-24 11:00:00     YSA           0
2020-05-24 12:00:00     YSA           7
2020-05-24 13:00:00     YSA           3
2020-05-24 14:00:00     YSA           5
2020-05-24 15:00:00     YSA           8
2020-05-24 16:00:00     YSA          11
2020-05-24 17:00:00     YSA          14
2020-05-24 18:00:00     YSA          13
2020-05-24 19:00:00     YSA          19
2020-05-24 20:00:00     YSA          20
2020-05-24 21:00:00     YSA          21
2020-05-24 22:00:00     YSA          23
2020-05-24 23:00:00     YSA          22
2020-05-25 00:00:00     YSA          19

[15826 rows x 2 columns]

Using GroupBy, I grouped the data by station, then resampled to get the daily max:

daily_max=pd_df.groupby(['station']).resample('D').max()

daily_max
Out[28]: 
                   station  gust_speed
station valid_dt                      
AMO     2020-05-21     AMO          24
        2020-05-22     AMO          24
        2020-05-23     AMO          23
        2020-05-24     AMO          24
        2020-05-25     AMO          22
ANE     2020-05-21     ANE          55
        2020-05-22     ANE          21
        2020-05-23     ANE          20
        2020-05-24     ANE          23
        2020-05-25     ANE          20
ARH     2020-05-21     ARH          20
        2020-05-22     ARH          21
        2020-05-23     ARH          19
        2020-05-24     ARH          21
        2020-05-25     ARH          21
AVO     2020-05-21     AVO          26
        2020-05-22     AVO          29
        2020-05-23     AVO          28
        2020-05-24     AVO          27
        2020-05-25     AVO          24
AVY     2020-05-21     AVY          21
        2020-05-22     AVY          21
        2020-05-23     AVY          20
        2020-05-24     AVY          22
        2020-05-25     AVY          21
BAJC1   2020-05-21   BAJC1          24
        2020-05-22   BAJC1          22
        2020-05-23   BAJC1          22
        2020-05-24   BAJC1          25
        2020-05-25   BAJC1          22
                   ...         ...

I also need the time at which that max wind speed occurs. Using idxmax() with the same code instead of max() resturns this error:

AttributeError: 'DatetimeIndexResamplerGroupby' object has no attribute 'idxmax'

So I tried using Grouper, but am not sure how to first group by station:

daily_max=pd_df.groupby(pd.Grouper(freq='D')).gust_speed.idxmax()

daily_max
Out[35]: 
valid_dt
2020-05-21   2020-05-21 22:00:00
2020-05-22   2020-05-22 23:00:00
2020-05-23   2020-05-23 08:00:00
2020-05-24   2020-05-24 05:00:00
2020-05-25   2020-05-25 00:00:00
Freq: D, Name: gust_speed, dtype: datetime64[ns]

This, of course, returns the time of the max wind gust out of all stations for each day, which is not helpful.

The desired outcome would show the date and time of the max speed, the station, and the value of the max speed. So the only thing missing is the time at which the daily max occurred for each station.

Thank you in advance!

user_2901002 · Answer

You can add station to groupby, so possible use GroupBy.agg with max and also idxmax:

df = pd_df.groupby(['station', pd.Grouper(freq='D')]).gust_speed.agg(['max','idxmax'])
print (df)
                    max              idxmax
station valid_dt                           
AMO     2020-05-21   24 2020-05-21 23:00:00
        2020-05-22   22 2020-05-22 00:00:00
YSA     2020-05-23   23 2020-05-23 22:00:00
        2020-05-24   23 2020-05-24 22:00:00
        2020-05-25   19 2020-05-25 00:00:00

user_13107804 · Answer

Processing flow:.

Adding a Date Column
find the maximum value by grouping them by day and station to create a DF
combine the original DF with the created DF.

df['valid_date'] = pd.to_datetime(df['date'].dt.date)
df2 = df.groupby(['valid_date','station'])['gust_speed'].max().reset_index()
df2['valid_date'] = pd.to_datetime(df2['valid_date'])
pd.merge(df2, df, on=['valid_date','station','gust_speed'], how='left')

df
    valid_date  station     gust_speed  date
0   2020-05-21  AMO     29  2020-05-21 00:00:00
1   2020-05-21  AMO     29  2020-05-21 10:00:00
2   2020-05-21  ANE     29  2020-05-21 14:00:00
3   2020-05-21  ARH     29  2020-05-21 12:00:00
4   2020-05-21  ARH     29  2020-05-21 14:00:00
...     ...     ...     ...     ...
82  2020-05-26  ANE     24  2020-05-26 00:00:00
83  2020-05-26  ARH     26  2020-05-26 00:00:00
84  2020-05-26  AVO     29  2020-05-26 00:00:00
85  2020-05-26  AVY     26  2020-05-26 00:00:00
86  2020-05-26  BAJC1   27  2020-05-26 00:00:00

Retaining time of max with Pandas GroupBy and resample

2 Answers