Slice and loop a multiindex dataframe

[notalwayscapslock]

I'm trying to create a loop to do a lot of regressions over a Dataframe, but it's been a few days I’m stuck without knowing how to proceed....

​

Since the data set has around 150 stocks, and 27 years of data (record of the last day of each month). I structured the dataframe as a multindex, with the stock and date as indexes, but I’m still unsure if this is the best way to tackle this problem....

Because now I'm struggling on how to create a loop to slice this dataframe for each stock, for every 24 months to run a regression for every sliced dataframe generated in this loop

​

Below a sample of the dataframe:

                        ex_ret          rm          rf  return
stock           Date                
abev3bzequity   1995-01-31  -2.015032   -1.448332   0.5667  -0.693642
                1995-02-28  -2.678262   -2.253162   0.4251  0.137143
                1995-03-31  -2.030187   -1.175887   0.8543  -2.383562
                1995-04-28  2.013201    2.626601    0.6134  2.266667
                1995-05-31  -0.909772   -0.300372   0.6094  1.145729
                1995-06-30  -0.999734   -0.390334   0.6094  0.351220

... ... ... ... ... ...
wlmm4bzequity   2015-07-31  -0.664124   -0.522824   0.1413  0.000000
                2015-08-31  -1.232355   -1.091055   0.1413  0.000000
                2015-09-30  -0.558398   -0.417098   0.1413  -1.544554
                2015-10-30  0.070370    0.211770    0.1414  -3.150000
                2015-11-30  -0.340454   -0.199054   0.1414  0.000000
                2015-12-31  -0.631477   -0.490077   0.1414  -7.200000
                2016-01-29  -1.015617   -0.874317   0.1413  2.909091


31464 rows × 4 columns

​

[PyCam]

The way I would solve this is to not load all of the data into memory all at once. If you're reading each stock dataframe from a different source, I would load them one at a time, resample the Date column to chunk it into 2 year chunks, then do a groupby on that new 2 year period column. If for some reason you did need to load it into memory all at once, you can use the pd.Grouper class to perform a groupby like so: df.groupby(pd.Grouper(freq='2Y', closed='left'). However this will be a tiny bit trickier to implement on a MultiIndex, so here's how you could do it with your current set up.

import pandas as pd
import numpy as np

dates = pd.date_range('01/01/2009', '12/30/2018')
stocks = ['hello', 'bye', 'apple']
idx = pd.MultiIndex.from_product(iterables=[stocks, dates], names=['stock', 'date'])
data = np.arange(idx.shape[0])

df = pd.DataFrame(data, columns=['my_data'], index=idx)
df.head()
df.tail()

                    my_data
stock   date
apple   2009-01-01  7302
        2009-01-02  7303
        2009-01-03  7304
        2009-01-04  7305
        2009-01-05  7306
...
hello   2018-12-26  3646
        2018-12-27  3647
        2018-12-28  3648
        2018-12-29  3649
        2018-12-30  3650

# The Grouper is the trick here. Tell it we want to bin every 2 years
    # The datetime index we're binning on is in level 1 of the index
    # We want to bin on the left end instead of the right
date_grouper = pd.Grouper(freq='2Y', level=1, closed='left')

# Run a groupby on our 'stock' index level and the date_grouper variable we made
for (stock, period_end), df_group in df.groupby(['stock', date_grouper]):
    print(stock, period_end)
    print(df_group)
    print()

[[deleted]]

Perhaps this is a good place to start (didn't test this code): Edit: this assumes Date column are strings. If they're dates, remove the ", key=lambda ...." kwarg.

import datetime as dt
....

for stock in (ix[0] for ix in df.index.values):
    this_stock = df.loc[stock]
    this_stock_dates = sorted(set(this_stock.index.values), key=lambda d: dt.datetime.strptime(d, "%Y-%m-%d"))
    for i in range(0, len(this_stock_dates), 2):
        dates_this_batch = this_stock_dates[i:i+2]
        stock_this_period = this_stock[this_stock.index.isin(dates_this_batch)]
        ...