CODEWITHSUNDEEP
pythonpandasrolling-average
Tony
Tony

Reputation: 13

Python Pandas: Custom rolling window calculation

I'm Looking to take the most recent value in a rolling window and divide it by the mean of all numbers in said window.

What I tried: 

df.a.rolling(window=7).mean()/df.a[-1]

This doesn't work because df.a[-1] is always the most recent of the entire dataset. I need the last value of the window.

I've done a ton of searching today. I may be searching the wrong terms, or not understanding the results, because I have not gotten anything useful.

Any pointers would be appreciated.

Upvotes: 1

Views: 2629

Answers (1)

James
James

Reputation: 36756

Aggregation (use the mean()) on a rolling windows returns a pandas Series object with the same indexing as the original column. You can simply aggregate the rolling window and then divide the original column by the aggregated values.

import numpy as np
import pandas as pd

df = pd.DataFrame(np.arange(30), columns=['A'])

df
# returns:
     A
0    0
1    1
2    2
...
27  27
28  28
29  29

You can use a rolling mean to get a series with the same index.

df.A.rolling(window=7).mean()
# returns:
0      NaN
1      NaN
2      NaN
3      NaN
4      NaN
5      NaN
6      3.0
7      4.0
...
26    23.0
27    24.0
28    25.0
29    26.0

Because it is indexed, you can simple divide by df.A to get your desired results.

df.A.rolling(window=7).mean() / df.A
# returns:
0          NaN
1          NaN
2          NaN
3          NaN
4          NaN
5          NaN
6     0.500000
7     0.571429
8     0.625000
9     0.666667
10    0.700000
11    0.727273
12    0.750000
13    0.769231
14    0.785714
15    0.800000
16    0.812500
17    0.823529
18    0.833333
19    0.842105
20    0.850000
21    0.857143
22    0.863636
23    0.869565
24    0.875000
25    0.880000
26    0.884615
27    0.888889
28    0.892857
29    0.896552

Upvotes: 3

Related Questions