How to calculate rolling cumulative product on Pandas DataFrame

Question

I have a time series of returns, rolling beta, and rolling alpha in a pandas DataFrame. How can I calculate a rolling annualized alpha for the alpha column of the DataFrame? (I want to do the equivalent to =PRODUCT(1+[trailing 12 months])-1 in excel)

            SPX Index BBOEGEUS Index    Beta      Alpha
2006-07-31   0.005086    0.001910    1.177977   -0.004081
2006-08-31   0.021274    0.028854    1.167670    0.004012
2006-09-30   0.024566    0.009769    1.101618   -0.017293
2006-10-31   0.031508    0.030692    1.060355   -0.002717
2006-11-30   0.016467    0.031720    1.127585    0.013153

I was surprised to see that there was no "rolling" function built into pandas for this, but I was hoping somebody could help with a function that I can then apply to the df['Alpha'] column using pd.rolling_apply.

Thanks in advance for any help you have to offer.

score 30 · Answer 1 · edited Jul 15 '19 at 08:47

30

rolling_apply has been dropped in pandas and replaced by more versatile window methods (e.g. rolling() etc.)

# Both agg and apply will give you the same answer
(1+df).rolling(window=12).agg(np.prod) - 1
# BUT apply(raw=True) will be much FASTER!
(1+df).rolling(window=12).apply(np.prod, raw=True) - 1

edited Jul 15 '19 at 08:47

jamesoh

372
1
10

answered Jul 11 '19 at 09:00

YaOzI

16,128
9
76
72

is there any faster way? maybe using `cumprod()` and dividing the last element in the window by the first element in the window? – Jayen Nov 22 '21 at 07:31

score 22 · Accepted Answer · answered Mar 08 '13 at 14:34

22

will this do?

import pandas as pd
import numpy as np

# your DataFrame; df = ...

pd.rolling_apply(df, 12, lambda x: np.prod(1 + x) - 1)

answered Mar 08 '13 at 14:34

herrfz

4,814
4
26
37

2

This function seems to be deprecated. There are more recent solutions here: https://stackoverflow.com/questions/35365545/calculating-cumulative-returns-with-pandas-dataframe – nocibambi May 27 '19 at 18:58

score 7 · Answer 3 · edited Mar 25 '20 at 08:32

7

It will be a bit faster, if you move those +/-1 out of df, like this:

cumprod = (1.+df).rolling(window=12).agg(lambda x : x.prod()) -1

edited Mar 25 '20 at 08:32

Jaroslav Bezděk

6,967
6
29
46

answered Jun 25 '18 at 21:06

Zellint

99
1
6

Emerson Uehara · Answer 4 · 2022-11-08T13:51:43.163

0

You can do this by acumulating it all, and then getting the difference between the periods that you want:

(df+1).cumprod().pct_change(periods=12)

edited Nov 08 '22 at 13:51

answered Nov 08 '22 at 13:51

Emerson Uehara

1
1

score -2 · Answer 5 · answered Jul 13 '20 at 02:10

-2

rolling_apply is deprecated, so this works best:

(1 + df).cumprod() - 1

answered Jul 13 '20 at 02:10

develarist

1,224
1
13
34

This answer does not take into account the size of the rolling window - it assumes it to be 1. – Doggie52 Apr 07 '21 at 09:49

How to calculate rolling cumulative product on Pandas DataFrame

5 Answers5

Linked