Resampling a Time Series in a MultiIndex DataFrame

Question

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

score 6 · Accepted Answer · edited Aug 17 '17 at 10:01

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()

Is it possible to specify the current date 2017-08-08 for resampling ie. all preceding weeks to be based on that date? Also, I need weekly dates to be the first index, not the ticker. Thanks — artDeco, Aug 12 '17 at 15:39
@artDeco I don't understand the first question. You mean if current date is a monday, all sampled dates should be mondays? As far as the second question you can just set or reset the index however you like. The results are all dataframes themselves — JohnE, Aug 12 '17 at 15:54
Yes that's right if the current date is Monday, all sampled dates would be Mondays. — artDeco, Aug 12 '17 at 15:57
I cannot change the current date on as-received basis, and all data as received are daily. What I need to produce is precisely: take the current date, resample by weeks backwards. Appreciate your input with my sincere thanks. — artDeco, Aug 12 '17 at 16:13
@artDeco just parameterize it. Get the day of week as they do here: https://stackoverflow.com/questions/9847213/which-day-of-week-given-a-date-python then plug that into `loffset` (I think subtract one from it tho) — JohnE, Aug 12 '17 at 17:58

Resampling a Time Series in a MultiIndex DataFrame

1 Answers1

Linked