Expanding window functions with pandas

Manipulating Time Series Data in Python

Stefan Jansen

Founder & Lead Data Scientist at Applied Artificial Intelligence

Expanding windows in pandas

From rolling to expanding windows
Calculate metrics for periods up to current date
New time series reflects all historical values
Useful for running rate of return, running min/max
Two options with pandas:
- .expanding() - just like .rolling()
- .cumsum(), .cumprod(), cummin()/max()

The basic idea

df = pd.DataFrame({'data': range(5)})

df['expanding sum'] = df.data.expanding().sum()

df['cumulative sum'] = df.data.cumsum()

df

   data  expanding sum  cumulative sum
0     0            0.0               0
1     1            1.0               1
2     2            3.0               3
3     3            6.0               6
4     4           10.0              10

Get data for the S&P 500

data = pd.read_csv('sp500.csv', parse_dates=['date'], index_col='date')

DatetimeIndex: 2519 entries, 2007-05-24 to 2017-05-24
Data columns (total 1 columns):
SP500    2519 non-null float64

ch3_2_v2 - Expanding Window Functions with Pandas.013.png

How to calculate a running return

Single period return $r_t$: current price over last price minus 1:

$$r_t = \frac{P_t}{P_{t-1}} - 1$$
- Multi-period return: product of $(1 + r_t)$ for all periods, minus 1:
$$R_T = (1 + r_1)(1 + r_2)...(1 + r_T) - 1$$
- For the period return: .pct_change()
- For basic math .add(), .sub(), .mul(), .div()
- For cumulative product: .cumprod()

Running rate of return in practice

pr = data.SP500.pct_change() # period return

pr_plus_one = pr.add(1)

cumulative_return = pr_plus_one.cumprod().sub(1)

cumulative_return.mul(100).plot()

ch3_2_v2 - Expanding Window Functions with Pandas.021.png

Getting the running min & max

data['running_min'] = data.SP500.expanding().min()

data['running_max'] = data.SP500.expanding().max()

data.plot()

ch3_2_v2 - Expanding Window Functions with Pandas.023.png

Rolling annual rate of return

def multi_period_return(period_returns):
    return np.prod(period_returns + 1) - 1

pr = data.SP500.pct_change() # period return

r = pr.rolling('360D').apply(multi_period_return)

data['Rolling 1yr Return'] = r.mul(100)

data.plot(subplots=True)

Rolling annual rate of return

data['Rolling 1yr Return'] = r.mul(100)

data.plot(subplots=True)

ch3_2_v2 - Expanding Window Functions with Pandas.027.png

Let's practice!

Manipulating Time Series Data in Python