artDeco artDeco - 11 months ago 40
Python Question

Resampling a Time Series in a MultiIndex DataFrame

I have a hierarchical table with time series data in a MultiIndex DataFrame - see sample data below - note that Level 1 index is unevenly populated.

How do I resample the DataFrame to result in a weekly time series that resamples weekly backwards from the current date.

I have tried this but failed:

df.index.levels[0].name = 'date'
df.reset_index().groupby(pandas.Grouper(key='date', freq='W'))


Sample data:

Price Sector
2016-08-08 Equity(24 [AAPL]) 107.47 311
Equity(4151 [JNJ]) 124.19 206
Equity(5061 [MSFT]) 57.95 311
Equity(8347 [XOM]) 87.52 309
Equity(11100 [BRK_B]) 145.52 103
Equity(16841 [AMZN]) 765.87 102
Equity(26578 [GOOG_L]) 806.93 311
Equity(42950 [FB]) 125.11 311
2016-08-09 Equity(24 [AAPL]) 108.37 311
Equity(4151 [JNJ]) 123.69 206
Equity(5061 [MSFT]) 58.06 311
Equity(8347 [XOM]) 88.55 309
Equity(11100 [BRK_B]) 145.45 103
Equity(16841 [AMZN]) 766.56 102
Equity(26578 [GOOG_L]) 805.39 311
Equity(42950 [FB]) 125.26 311
2016-08-10 Equity(24 [AAPL]) 108.81 311
Equity(4151 [JNJ]) 123.44 206
Equity(5061 [MSFT]) 58.20 311
Equity(8347 [XOM]) 87.98 309
Equity(11100 [BRK_B]) 147.30 103
Equity(16841 [AMZN]) 768.32 102
Equity(26578 [GOOG_L]) 807.47 311
Equity(42950 [FB]) 125.06 311
2016-08-11 Equity(24 [AAPL]) 108.00 311
Equity(4151 [JNJ]) 123.43 206
Equity(5061 [MSFT]) 58.02 311
Equity(8347 [XOM]) 86.41 309
Equity(11100 [BRK_B]) 146.73 103
Equity(16841 [AMZN]) 768.44 102
Equity(26578 [GOOG_L]) 808.66 311
Equity(42950 [FB]) 124.87 311
2016-08-12 Equity(24 [AAPL]) 107.93 311
Equity(4151 [JNJ]) 123.76 206
Equity(5061 [MSFT]) 58.31 311
Equity(8347 [XOM]) 86.73 309
Equity(11100 [BRK_B]) 147.72 103
Equity(16841 [AMZN]) 771.30 102
Equity(26578 [GOOG_L]) 808.20 311
Equity(42950 [FB]) 124.91 311
2016-08-15 Equity(24 [AAPL]) 108.16 311
Equity(4151 [JNJ]) 123.21 206
Equity(5061 [MSFT]) 57.93 311
Equity(8347 [XOM]) 87.80 309
Equity(11100 [BRK_B]) 147.64 103
Equity(16841 [AMZN]) 772.56 102
Equity(26578 [GOOG_L]) 807.05 311
Equity(42950 [FB]) 124.87 311
2016-08-16 Equity(24 [AAPL]) 109.50 311
Equity(4151 [JNJ]) 122.33 206
Equity(5061 [MSFT]) 57.76 311
Equity(8347 [XOM]) 87.81 309
Equity(11100 [BRK_B]) 147.78 103
Equity(16841 [AMZN]) 768.49 102
Equity(26578 [GOOG_L]) 806.00 311
Equity(42950 [FB]) 123.90 311
2016-08-17 Equity(24 [AAPL]) 109.38 311
Equity(4151 [JNJ]) 120.34 206
Equity(5061 [MSFT]) 57.44 311
Equity(8347 [XOM]) 87.94 309
…… …… …… ……
2017-07-31 Equity(8347 [XOM]) 79.60 309
Equity(11100 [BRK_B]) 173.99 103
Equity(16841 [AMZN]) 1020.01 102
Equity(25006 [JPM]) 91.27 103
Equity(26578 [GOOG_L]) 958.20 311
Equity(42950 [FB]) 172.46 311
2017-08-01 Equity(24 [AAPL]) 148.77 311
Equity(4151 [JNJ]) 132.72 206
Equity(5061 [MSFT]) 72.72 311
Equity(8347 [XOM]) 80.04 309
Equity(11100 [BRK_B]) 175.07 103
Equity(16841 [AMZN]) 987.50 102
Equity(25006 [JPM]) 91.81 103
Equity(26578 [GOOG_L]) 945.83 311
Equity(42950 [FB]) 169.28 311
2017-08-02 Equity(24 [AAPL]) 150.05 311
Equity(4151 [JNJ]) 132.55 206
Equity(5061 [MSFT]) 72.55 311
Equity(8347 [XOM]) 80.15 309
Equity(11100 [BRK_B]) 176.29 103
Equity(16841 [AMZN]) 996.19 102
Equity(25006 [JPM]) 93.00 103
Equity(26578 [GOOG_L]) 946.56 311
Equity(42950 [FB]) 169.85 311
2017-08-03 Equity(24 [AAPL]) 157.15 311
Equity(4151 [JNJ]) 132.14 206
Equity(5061 [MSFT]) 72.25 311
Equity(8347 [XOM]) 80.59 309
Equity(11100 [BRK_B]) 177.78 103
Equity(16841 [AMZN]) 995.32 102
Equity(25006 [JPM]) 93.08 103
Equity(26578 [GOOG_L]) 947.18 311
Equity(42950 [FB]) 169.25 311
2017-08-04 Equity(24 [AAPL]) 155.53 311
Equity(4151 [JNJ]) 133.35 206
Equity(5061 [MSFT]) 72.14 311
Equity(8347 [XOM]) 80.46 309
Equity(11100 [BRK_B]) 178.94 103
Equity(16841 [AMZN]) 987.14 102
Equity(25006 [JPM]) 92.46 103
Equity(26578 [GOOG_L]) 940.63 311
Equity(42950 [FB]) 168.58 311
2017-08-07 Equity(24 [AAPL]) 156.34 311
Equity(4151 [JNJ]) 133.11 206
Equity(5061 [MSFT]) 72.66 311
Equity(8347 [XOM]) 80.17 309
Equity(11100 [BRK_B]) 179.79 103
Equity(16841 [AMZN]) 987.41 102
Equity(25006 [JPM]) 93.68 103
Equity(26578 [GOOG_L]) 945.34 311
Equity(42950 [FB]) 169.59 311
2017-08-08 Equity(24 [AAPL]) 158.79 311
Equity(4151 [JNJ]) 132.84 206
Equity(5061 [MSFT]) 72.40 311
Equity(8347 [XOM]) 80.15 309
Equity(11100 [BRK_B]) 178.00 103
Equity(16841 [AMZN]) 991.80 102
Equity(25006 [JPM]) 94.02 103
Equity(26578 [GOOG_L]) 945.31 311
Equity(42950 [FB]) 171.96 311

Answer Source

Depending on how you want to aggregate the prices, resample gives you many options. Here's a sample dataframe for 2 stocks covering parts of 2 business weeks:

np.random.seed(123)
df1 = pd.DataFrame({ 'price':abs(np.random.randn(5)).cumsum(), 'ticker':'GOOG_L' }, 
                   index=pd.date_range('1-4-2017', periods=5, freq='B') )
df2 = pd.DataFrame({ 'price':abs(np.random.randn(5)).cumsum(), 'ticker':'AAPL' }, 
                   index=pd.date_range('1-4-2017', periods=5, freq='B') )
df = df1.append(df2).sort_index()

               price ticker
2017-01-04  1.085631   GOOG_L
2017-01-04  1.651437   AAPL
2017-01-05  2.082976   GOOG_L
2017-01-05  4.078116   AAPL
2017-01-06  2.365955   GOOG_L
2017-01-06  4.507028   AAPL
2017-01-09  3.872249   GOOG_L
2017-01-09  5.772965   AAPL
2017-01-10  4.450850   GOOG_L
2017-01-10  6.639705   AAPL

From there you can resample in a variety of ways (mean, max, first, etc.):

df.groupby('ticker')['price'].resample('W').mean()

ticker            
AAPL    2017-01-08    3.412194
        2017-01-15    6.206335
GOOG_L  2017-01-08    1.844854
        2017-01-15    4.161549
Name: price, dtype: float64

df.groupby('ticker')['price'].resample('W').max()

ticker            
AAPL    2017-01-08    4.507028
        2017-01-15    6.639705
GOOG_L  2017-01-08    2.365955
        2017-01-15    4.450850
Name: price, dtype: float64

df.groupby('ticker')['price'].resample('W').first()

ticker            
AAPL    2017-01-08    1.651437
        2017-01-15    5.772965
GOOG_L  2017-01-08    1.085631
        2017-01-15    3.872249
Name: price, dtype: float64

edit to add: You can adjust the day of the week with the "loffset" optional argument. For example, the above dates are all Sundays. To make it Monday, use:

df.groupby('ticker')['price'].resample('W',loffset='1D').first()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download