Panda rolling window percentile rank

Question

I am trying to calculate the percentile rank of data by column within a rolling window.

test=pd.DataFrame(np.random.randn(20,3),pd.date_range('1/1/2000',periods=20),['A','B','C'])

test
Out[111]: 
                   A         B         C
2000-01-01 -0.566992 -1.494799  0.462330
2000-01-02 -0.550769 -0.699104  0.767778
2000-01-03 -0.270597  0.060836  0.057195
2000-01-04 -0.583784 -0.546418 -0.557850
2000-01-05  0.294073 -2.326211  0.262098
2000-01-06 -1.122543 -0.116279 -0.003088
2000-01-07  0.121387  0.763100  3.503757
2000-01-08  0.335564  0.076304  2.021757
2000-01-09  0.403170  0.108256  0.680739
2000-01-10 -0.254558 -0.497909 -0.454181
2000-01-11  0.167347  0.459264 -1.247459
2000-01-12 -1.243778  0.858444  0.338056
2000-01-13 -1.070655  0.924808  0.080867
2000-01-14 -1.175651 -0.559712 -0.372584
2000-01-15 -0.216708 -0.116188  0.511223
2000-01-16  0.597171  0.205529 -0.728783
2000-01-17 -0.624469  0.592436  0.832100
2000-01-18  0.259269  0.665585  0.126534
2000-01-19  1.150804  0.575759 -1.335835
2000-01-20 -0.909525  0.500366  2.120933

I tried to use .rolling with .apply but I am missing something.

pctrank = lambda x: x.rank(pct=True)
rollingrank=test.rolling(window=10,centre=False).apply(pctrank)

For column A the final value would be the percentile rank of -0.909525 within the length=10 window from 2000-01-11 to 2000-01-20. Any ideas?

score 7 · Accepted Answer · edited May 23 '17 at 12:16

7

Your lambda receives a numpy array, which does not have a .rank method — it is pandas's Series and DataFrame that have it. You can thus change it to

pctrank = lambda x: pd.Series(x).rank(pct=True).iloc[-1]

Or you could use pure numpy along the lines of this SO answer:

def pctrank(x):
    n = len(x)
    temp = x.argsort()
    ranks = np.empty(n)
    ranks[temp] = (np.arange(n) + 1) / n
    return ranks[-1]

edited May 23 '17 at 12:16

Community

1
1

answered Aug 09 '16 at 17:20

Alicia Garcia-Raboso

13,193
1
43
48

That's perfect, just what I was looking for. Thank you. So I missed defining x as a panda Series before applying rank(). Can you explain why my lambda receives a numpy array? .rolling() and .apply() are both panda functions acting on a panda dataframe 'test'? – user6435943 Aug 10 '16 at 08:37
*why my lambda receives a numpy array?* I don't know why; I just know that the error said something like "`numpy.ndarray` has no method `rank`". `rolling` is a method of pandas `Series` and `DataFrame`. `apply` has several different incarnations. Have a look at the [split-apply-combine](http://pandas.pydata.org/pandas-docs/stable/groupby.html) documentation. – Alicia Garcia-Raboso Aug 11 '16 at 13:52
`x.argsort()` takes O(nlogn), but I think percent rank for specific one value in array should be `O(n)` – PaleNeutron Mar 30 '23 at 02:09

DataYoda · Answer 2 · 2022-02-10T09:36:12.860

3

The easiest option would be to do something like this:

from scipy import stats
# 200 is the window size

dataset[name] =  dataset[name].rolling(200).apply(lambda x: stats.percentileofscore(x, x[-1]))

edited Feb 10 '22 at 09:36

answered Aug 24 '20 at 12:20

DataYoda

771
5
18

1

This works well if `x` within the scipy.stats is changed to `x.to_numpy()` or `x.values` – GoneAsync May 19 '23 at 23:43

score 1 · Answer 3 · answered Sep 18 '20 at 23:29

1

In case you need the rank of the last observation only, as the case with rolling apply you can use:

 def pctrank(x):
    i = x.argsort().argmax() + 1
    n = len(x)
    return i/n

Time is about twice as fast

answered Sep 18 '20 at 23:29

flT

121
1
2
10

Panda rolling window percentile rank

3 Answers3

Linked