Pandas get rank on rolling with FixedForwardWindow…

I am using Pandas 1.51 and I’m trying to get the rank of each row in a dataframe in a rolling window that looks ahead by employing FixedForwardWindowIndexer. But I can’t make sense of the results. My code:

df = pd.DataFrame({"X":[9,3,4,5,1,2,8,7,6,10,11]})
window_size = 5
indexer = pd.api.indexers.FixedForwardWindowIndexer(window_size=window_size)
df.rolling(window=indexer).rank(ascending=False)

results:

      X
0   5.0
1   4.0
2   1.0
3   2.0
4   3.0
5   1.0
6   1.0
7   NaN
8   NaN
9   NaN
10  NaN

By my reckoning, it should look like:

      X
0   1.0 # based on the window [9,3,4,5,1], 9 is ranked 1st w/ascending = False
1   3.0 # based on the window [3,4,5,1,2], 3 is ranked 3rd
2   3.0 # based on the window [4,5,1,2,8], 4 is ranked 3rd
3   3.0 # etc
4   5.0
5   5.0
6   3.0
7   NaN
8   NaN
9   NaN
10  NaN

I am basing this on a backward-looking window, which works fine:

>>> df.rolling(window_size).rank(ascending=False)
      X
0   NaN
1   NaN
2   NaN
3   NaN
4   5.0
5   4.0
6   1.0
7   2.0
8   3.0
9   1.0
10  1.0

Any assistance is most welcome.

Answer

Here is another way to do it:

df["rank"] = [
    x.rank(ascending=False).iloc[0].values[0]
    for x in df.rolling(window_size)
    if len(x) == window_size
] + [pd.NA] * (window_size - 1)

Then:

print(df)
# Output
     X  rank
0    9   1.0
1    3   3.0
2    4   3.0
3    5   3.0
4    1   5.0
5    2   5.0
6    8   3.0
7    7  <NA>
8    6  <NA>
9   10  <NA>
10  11  <NA>

Pandas get rank on rolling with FixedForwardWindowIndexer

Advertisement

Answer