Rolling mean returns over DataFrame

Question

I want to add columns to the following Dataframe for each stock of 5 year (60 month) rolling returns. The following code is used to obtain the financial data over the period 1995 to 2010.

quandl.ApiConfig.api_key = 'Enter Key'
stocks = ['MSFT', 'AAPL', 'WMT', 'GE', 'KO']
stockdata = quandl.get_table('WIKI/PRICES', ticker = stocks, paginate=True,
                    qopts = { 'columns': ['date', 'ticker', 'adj_close'] },
                    date = { 'gte': '1995-1-1', 'lte': '2010-12-31' })

# Setting date as index with columns of tickers and adjusted closing price

df = stockdata.pivot(index = 'date',columns='ticker')
df.index = pd.to_datetime(df.index)
df.resample('1M').mean()
df = df.pct_change()

df.head()
Out[1]: 
              rets                                                    
ticker          AAPL        BA         F        GE       JNJ        KO   
 date                                                                     
1995-01-03       NaN       NaN       NaN       NaN       NaN       NaN   
1995-01-04  0.026055 -0.002567  0.026911  0.000000  0.006972 -0.019369   
1995-01-05 -0.012697  0.002573 -0.008735  0.002549 -0.002369 -0.004938   
1995-01-06  0.080247  0.018824  0.000000 -0.004889 -0.006758  0.000000   
1995-01-09 -0.019048  0.000000  0.017624 -0.009827 -0.011585 -0.014887 

df.tail()
Out[2]: 
              rets
ticker          AAPL        BA         F        GE       JNJ        KO   
 date                                                                     
2010-12-27  0.003337 -0.004765  0.005364  0.008315 -0.005141 -0.007777   
2010-12-28  0.002433  0.001699 -0.008299  0.007147  0.001938  0.004457   
2010-12-29 -0.000553  0.002929  0.000598 -0.002729  0.001289  0.001377   
2010-12-30 -0.005011 -0.000615 -0.002987 -0.004379 -0.003058  0.000764   
2010-12-31 -0.003399  0.003846  0.005992  0.005498 -0.001453  0.004122

Any assistance of how to do this would be awesome!

What have you tried and where has it gone wrong? have you tried pandas rolling? https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rolling.html — Sven Harris, Oct 15 '18 at 08:25
I have tried the following: df['rolling'] = df['rets'].rolling(60).mean() However, it returns this error: ValueError: Wrong number of items passed 6, placement implies 1 — oceanbeach96, Oct 15 '18 at 08:30

score 1 · Accepted Answer · answered Oct 15 '18 at 22:38

1

The problem is in the multi-level index in the columns. We can start by selecting the second level index, and after that the rolling mean works:

means = df['rets'].rolling(60).mean() 
means.tail()

Gives:

answered Oct 15 '18 at 22:38

rje

6,388
1
21
40

score 0 · Answer 2 · answered Oct 15 '18 at 17:35

0

The error you are receiving is due to you passing the entire dataframe into the rolling function since your frame uses a multi index. You cant pass a multi index frame to a rolling function since rolling only accepts numpy arrays of 1 column. You’ll have to probably create a for loop and return the values individually per ticker

answered Oct 15 '18 at 17:35

IDontKnowCode

48
1
6

A loop is not necessary as you can simply select the inner level of the index as I've shown in the other answer. – rje Oct 15 '18 at 22:41

Rolling mean returns over DataFrame

2 Answers2