Pandas centred rolling window rank returns wrong value

user_4960908 0
Viewed 66

I'm trying to calculate the rank of a column value within a rolling window in Pandas like this:

df = pd.DataFrame( [[1, 10],
                    [2, 20],
                    [3, 50],
                    [4, 30],
                    [5, 40]], 
                   columns=['order_col', 'rank_col'])
df['rank'] = df.rolling(3, center=True, min_periods=1, on='order_col')['rank_col'].rank()

The result of rank() though gives the rank of the last row in the window not the one in the centre, as expected:

image of DataFrame

Any ideas how I can get the rank of the correct row? I.e. I expect the ranks to be 1, 2, 3, 1, 2

EDIT: I chose a small example to illustrate the problem but in actuality my dataframe has thousands of rows and the rolling window is of size 100+ rows.

rolling-computation pandas python
1 Answers

The following is a workaround, you'd use rank in apply and explicitly take the center value.

The code inspects the index of the series to recognize that it's the first window and not the last.

def series_rank_center(series):
    if 1 in series.index and len(series) < 3:
        return series.rank().iat[0]  # center value for first window
    else:
        return series.rank().iat[1]  # center value

df.rolling(3, center=True, min_periods=1, on='order_col').apply(series_rank_center)

   order_col  rank_col
0          1       1.0
1          2       2.0
2          3       3.0
3          4       1.0
4          5       2.0
user_79675880
Related