Groupby with forward looking rolling maximum-CodePudding

I have data with date, time, and values and want to calculate a forward looking rolling maximum for each date:

Date        Time    Value   Output
01/01/2022  01:00      1.3   1.4
01/01/2022  02:00      1.4   1.2
01/01/2022  03:00      0.9   1.2
01/01/2022  04:00      1.2   NaN
01/02/2022  01:00      5     4
01/02/2022  02:00      4     3 
01/02/2022  03:00      2     3
01/02/2022  04:00      3     NaN

I have tried this:

df = df.sort_values(by=['Date','Time'], ascending=True)
df['rollingmax'] = df.groupby(['Date'])['Value'].rolling(window=4,min_periods=0).max()
df = df.sort_values(by=['Date','Time'], ascending=False)

but that doesn't seem to work...

CodePudding user response：

It looks like you want a shifted reverse rolling max:

n = 4
df['Output'] = (df[::-1]
 .groupby('Date')['Value']
 .apply(lambda g: g.rolling(n-1, min_periods=1).max().shift())
 )

Output:

         Date   Time  Value  Output
0  01/01/2022  01:00    1.3     1.4
1  01/01/2022  02:00    1.4     1.2
2  01/01/2022  03:00    0.9     1.2
3  01/01/2022  04:00    1.2     NaN
4  01/02/2022  01:00    5.0     4.0
5  01/02/2022  02:00    4.0     3.0
6  01/02/2022  03:00    2.0     3.0
7  01/02/2022  04:00    3.0     NaN