Rolling Mean of Rolling Correlation dataframe in Python?

Question

Python beginner here.

What I've done so far:

• Imported price data from Yahoo Finance from a list of stocks.

• Between the stocks (every combination), computed the 20 day rolling correlation into a dataframe.

I would like to:

1) Calculate the 200 day simple moving average for each of the 20 day rolling correlations.

2) Report the 200 day moving average results in a matrix.

How to do this in python/pandas? Thanks, this would help me out a ton!

---

Here is what I have so far...

import pandas as pd
from pandas import DataFrame
import datetime
import pandas.io.data as web
from pandas.io.data import DataReader

stocks = ['spy', 'gld', 'uso']
start = datetime.datetime(2014,1,1)
end = datetime.datetime(2015,1,1)

f = web.DataReader(stocks, 'yahoo', start, end)
adj_close_df = f['Adj Close']

correls = pd.rolling_corr(adj_close_df, 20)

means = pd.rolling_mean(correls, 200) #<---- I get an error message here!

Answer 1

This is a start which answers questions 1-3 (you should only have one question per post).

import pandas.io.data as web
import datetime as dt
import pandas as pd

end_date = dt.datetime.now().date()
start_date = end_date - pd.DateOffset(years=5)

symbols = ['AAPL', 'IBM', 'GM']
prices = web.get_data_yahoo(symbols=symbols, start=start_date, end=end_date)['Adj Close']
returns = prices.pct_change()
rolling_corr = pd.rolling_corr_pairwise(returns, window=20)

Getting the rolling mean of the rolling correlation is relatively simple for a single stock against all others. For example:

pd.rolling_mean(rolling_corr.major_xs('AAPL').T, 200).tail()
Out[34]: 
            AAPL        GM       IBM
Date                                
2015-05-08     1  0.313391  0.324728
2015-05-11     1  0.315561  0.327537
2015-05-12     1  0.317844  0.330375
2015-05-13     1  0.320137  0.333189
2015-05-14     1  0.322119  0.335659

To view the correlation matrix for the most recent 200 day window:

>>> rolling_corr.iloc[-200:].mean(axis=0)
          AAPL        GM       IBM
AAPL  1.000000  0.322119  0.335659
GM    0.322119  1.000000  0.383672
IBM   0.335659  0.383672  1.000000