[SOLVED] Rolling max excluding current observation in Pandas 1.0

Rolling max excluding current observation in Pandas 1.0

Using Pandas 1.0 I need to generate a rolling max with a window of the previous 3 observations, excluding the current observation. In R this is achieved by

library(tidyverse)

test_df = data.frame(a = 1:5, b = c(40, 37, 60, 45, 40))

test_df <- test_df %>% mutate(
    rolling_max=rollapply(b, width = list(-1:-3), max, na.rm = TRUE, partial = 0, align = "right")
)
> test_df
  a  b rolling_max
1 1 40        -Inf
2 2 37          40
3 3 60          40
4 4 45          60
5 5 40          60

In Python the pandas.rolling.apply() function does not seem to have a way of excluding the current observation, hence this yields the unexpected result:

import pandas as pd
test_df = pd.DataFrame({'a': [1,2,3,4,5], 'b': [40,37,60,45,40]})
test_df['rolling_max'] = test_df['b'].rolling(3).apply(max)
test_df
   a   b  rolling_max
0  1  40          NaN
1  2  37          NaN
2  3  60         60.0
3  4  45         60.0
4  5  40         60.0

This outputs the expected result, but it looks like a clanky and non scalable solution:

test_df['rolling_max'] = np.fmax(
    test_df['b'].shift(periods=1).to_numpy(), 
    test_df['b'].shift(periods=2).to_numpy(), 
    test_df['b'].shift(periods=3).to_numpy()
)
test_df
   a   b  rolling_max
0  1  40          NaN
1  2  37         40.0
2  3  60         40.0
3  4  45         60.0
4  5  40         60.0

Can someone recommend a better approach?

Solution

First of all, you are using max while you said you need mean. Suppose what you need is max, with Python, you can do something like below:

test_df.b.rolling(4, min_periods=2).apply(lambda x: np.max(x[:-1]))

0     NaN
1    40.0
2    40.0
3    60.0
4    60.0
Name: b, dtype: float64