How can I set a frequency for the shift method in Pandas with a multi-index?

user_14384914 0
Viewed 279

I have the following data which I index by both date and ticker:

Date,Ticker,SharePrice,Vol
2014-12-31,MSFT,10.79,16.24
2015-03-31,MSFT,19.44,14.94
2015-06-30,MSFT,3.73,19.79
2015-09-30,MSFT,3.76,6.52
2015-12-31,MSFT,10.56,17.91
2016-03-31,MSFT,13.56,11.96
2016-06-30,MSFT,16.27,19.79
2014-12-31,GM,16.51,12.91
2015-03-31,GM,18.22,9.92
2015-06-30,GM,17.16,18.69

Per the code below I want to add an additional column where I use the shift method but rather than specifying the number of rows I want to specify a calendar period (in this example 3 months). However when I do this in a multi-index dataframe I get the error "Not supported for type Multiindex" - why is this and how can I get around this restriction?

Code:

import pandas as pd

stocks = pd.read_csv("C:\\Users\\stocks.csv", index_col=["Date", "Ticker"])
stocks['OldSharePrice'] = stocks['SharePrice'].groupby(level=1).shift(periods=3, freq='M')
print(stocks)

Note - if I remove the "freq='M'" part then it works fine but as detailed above I need the ability to specify calendar frequencies

pandas python
1 Answers

One idea is reshape by Series.unstack and DataFrame.stack and also convert Date to DatetimeIndex in read_csv by parse_dates parameter:

stocks = pd.read_csv("C:\\Users\\stocks.csv", 
                     index_col=["Date", "Ticker"], 
                     parse_dates=["Date"])

stocks['OldSharePrice'] = stocks['SharePrice'].unstack().shift(periods=3, freq='M').stack()
print(stocks)
                   SharePrice    Vol  OldSharePrice
Date       Ticker                                  
2014-12-31 MSFT         10.79  16.24            NaN
2015-03-31 MSFT         19.44  14.94          10.79
2015-06-30 MSFT          3.73  19.79          19.44
2015-09-30 MSFT          3.76   6.52           3.73
2015-12-31 MSFT         10.56  17.91           3.76
2016-03-31 MSFT         13.56  11.96          10.56
2016-06-30 MSFT         16.27  19.79          13.56
2014-12-31 GM           16.51  12.91            NaN
2015-03-31 GM           18.22   9.92          16.51
2015-06-30 GM           17.16  18.69          18.22
user_29010020
Related