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:
JavaScript
x
5
1
df = pd.DataFrame({"X":[9,3,4,5,1,2,8,7,6,10,11]})
2
window_size = 5
3
indexer = pd.api.indexers.FixedForwardWindowIndexer(window_size=window_size)
4
df.rolling(window=indexer).rank(ascending=False)
5
results:
JavaScript
1
13
13
1
X
2
0 5.0
3
1 4.0
4
2 1.0
5
3 2.0
6
4 3.0
7
5 1.0
8
6 1.0
9
7 NaN
10
8 NaN
11
9 NaN
12
10 NaN
13
By my reckoning, it should look like:
JavaScript
1
13
13
1
X
2
0 1.0 # based on the window [9,3,4,5,1], 9 is ranked 1st w/ascending = False
3
1 3.0 # based on the window [3,4,5,1,2], 3 is ranked 3rd
4
2 3.0 # based on the window [4,5,1,2,8], 4 is ranked 3rd
5
3 3.0 # etc
6
4 5.0
7
5 5.0
8
6 3.0
9
7 NaN
10
8 NaN
11
9 NaN
12
10 NaN
13
I am basing this on a backward-looking window, which works fine:
JavaScript
1
14
14
1
>>> df.rolling(window_size).rank(ascending=False)
2
X
3
0 NaN
4
1 NaN
5
2 NaN
6
3 NaN
7
4 5.0
8
5 4.0
9
6 1.0
10
7 2.0
11
8 3.0
12
9 1.0
13
10 1.0
14
Any assistance is most welcome.
Advertisement
Answer
Here is another way to do it:
JavaScript
1
6
1
df["rank"] = [
2
x.rank(ascending=False).iloc[0].values[0]
3
for x in df.rolling(window_size)
4
if len(x) == window_size
5
] + [pd.NA] * (window_size - 1)
6
Then:
JavaScript
1
15
15
1
print(df)
2
# Output
3
X rank
4
0 9 1.0
5
1 3 3.0
6
2 4 3.0
7
3 5 3.0
8
4 1 5.0
9
5 2 5.0
10
6 8 3.0
11
7 7 <NA>
12
8 6 <NA>
13
9 10 <NA>
14
10 11 <NA>
15