Grouped timeseries operations fail on multi-indexed DataFrames

[neiseman]

This might be a potential bug: doing grouped timeseries operations fails silently on a multi-indexed DataFrame.

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

    # Get some market data
    df = web.DataReader(['AAPL', 'GOOG'], 'yahoo', pd.Timestamp('2013'), pd.Timestamp('2014')).to_frame()
    df.index.names = ('dt', 'symbol')

    In [21]: df.head()
    Out[21]: 
                            Open       High        Low      Close     Volume  \
    dt         symbol                                                          
    2013-01-02 AAPL    553.82001  555.00000  541.62994  549.03003  140129500   
    2013-01-03 AAPL    547.88000  549.67004  541.00000  542.10004   88241300   
    2013-01-04 AAPL    536.96997  538.63000  525.82996  527.00000  148583400   
    2013-01-07 AAPL    522.00000  529.30005  515.20001  523.90002  121039100   
    2013-01-08 AAPL    529.21002  531.89001  521.25000  525.31000  114676800   

                       Adj Close  
    dt         symbol             
    2013-01-02 AAPL     74.63931  
    2013-01-03 AAPL     73.69719  
    2013-01-04 AAPL     71.64438  
    2013-01-07 AAPL     71.22294  
    2013-01-08 AAPL     71.41463 

Let's say we want to resample this to monthly data. This fails and returns an empty DataFrame:

    df_M = df.groupby(level='symbol').resample('M', how='mean')
    In [23]: df_M
    Out[23]: 
    Empty DataFrame
    Columns: []
    Index: []

This, however, works, but requires a seemingly-unnecessary re-indexing:

    df_M = df.reset_index().set_index('dt').groupby('symbol').resample('M', how='mean')
    In [26]: df_M.head()
    Out[26]: 
                       Adj Close       Close        High         Low        Open  \
    symbol dt                                                                      
    AAPL   2013-01-31  67.677750  497.822382  504.407623  492.969997  500.083329   
           2013-02-28  62.388477  456.808942  463.231056  452.106325  458.503692   
           2013-03-31  60.417287  441.841000  446.803495  437.337996  442.011512   
           2013-04-30  57.398619  419.765001  425.553183  414.722271  419.766820   
           2013-05-31  61.340151  446.452734  451.658190  441.495455  446.400919   

                             Volume  
    symbol dt                        
    AAPL   2013-01-31  1.562312e+08  
           2013-02-28  1.229478e+08  
           2013-03-31  1.147110e+08  
           2013-04-30  1.245851e+08  
           2013-05-31  1.073583e+08  

The fact that you need to do the reset_index().set_index('dt') and then groupby('symbol') instead of groupby(level='symbol') seems to defeat the purpose of a multi-index! What gives?

I also realize that data like this is perhaps more well-suited to a Panel than a DataFrame, but when dealing with very large amounts of (often sparse) data, the 3D Panel structure presents performance and memory issues compared to the flat DataFrame.

[jreback]

Here is how to do this; use pd.Grouper (note that there WAS a bug in doing this, which is NotImplemented in 0.16.0, and wasn't working on master). So this is fixed fo 0.16.1 (releasing on friday)
here are the docs on this relatively new feature

In [6]:     df = web.DataReader(['AAPL', 'ORCL'], 'yahoo', pd.Timestamp('2013'), pd.Timestamp('2014')).to_frame()

In [7]:     df.index.names = ('dt', 'symbol')

In [8]: pd.set_option('max_rows',10)

In [9]: df
Out[9]: 
                        Open       High        Low      Close     Volume  Adj Close
dt         symbol                                                                  
2013-01-02 AAPL    553.82001  555.00000  541.62994  549.03003  140129500   74.63931
           ORCL     34.08000   34.74000   33.93000   34.69000   33758400   33.80673
2013-01-03 AAPL    547.88000  549.67004  541.00000  542.10004   88241300   73.69719
           ORCL     34.63000   34.75000   34.14000   34.31000   21819500   33.43641
2013-01-04 AAPL    536.96997  538.63000  525.82996  527.00000  148583400   71.64438
...                      ...        ...        ...        ...        ...        ...
2013-12-27 ORCL     37.89000   38.22000   37.78000   37.98000   15373100   37.28903
2013-12-30 AAPL    557.45996  560.09003  552.32007  554.51996   63407400   77.27941
           ORCL     37.95000   38.11000   37.81000   37.99000   11683000   37.29885
2013-12-31 AAPL    554.16998  561.28003  554.00000  561.02002   55771100   78.18527
           ORCL     37.94000   38.34000   37.88000   38.26000   11746400   37.56393

[504 rows x 6 columns]

In [10]: df.groupby([pd.Grouper(level='symbol'),pd.Grouper(level='dt',freq='M')]).mean()
Out[10]: 
                         Open        High         Low       Close        Volume  Adj Close
symbol dt                                                                                 
AAPL   2013-01-31  500.083329  504.407623  492.969997  497.822382  1.562312e+08  67.677750
       2013-02-28  458.503692  463.231056  452.106325  456.808942  1.229478e+08  62.388477
       2013-03-31  442.011512  446.803495  437.337996  441.841000  1.147110e+08  60.417287
       2013-04-30  419.766820  425.553183  414.722271  419.765001  1.245851e+08  57.398619
       2013-05-31  446.400919  451.658190  441.495455  446.452734  1.073583e+08  61.340151
...                       ...         ...         ...         ...           ...        ...
ORCL   2013-08-31   32.474091   32.778182   32.295455   32.506364  1.575545e+07  31.799757
       2013-09-30   33.090000   33.380500   32.883000   33.146500  2.274582e+07  32.425978
       2013-10-31   33.055652   33.326522   32.843478   33.076087  1.833730e+07  32.458856
       2013-11-30   34.437500   34.722500   34.295500   34.551500  1.617405e+07  33.922902
       2013-12-31   35.552857   35.869048   35.338571   35.630952  2.231692e+07  34.982716

[24 rows x 6 columns]

[jreback]

@neiseman this is fixed now in master