I have two DataFrames. One has the returns for a range of dates for some stocks. The other has the price and volume by stock and date. In the second DataFrame the price and volume are combined in a MultiIndex with the stocks:
| date | ('adjClose', 'AAPL') | ('volume', 'AAPL') | ('adjClose', 'AMZN') | ('volume', 'AMZN') |
|---|---|---|---|---|
| 2022-04-12 00:00:00 | 167.66 | 7.92652e+07 | 3015.75 | 2.75887e+06 |
| 2022-04-13 00:00:00 | 170.4 | 7.06189e+07 | 3110.82 | 2.66954e+06 |
| 2022-04-14 00:00:00 | 165.29 | 7.53294e+07 | 3034.13 | 2.57991e+06 |
The returns DataFrame has dates as indexes and non-MultiIndex stock columns. I'm trying to slice the dates and stocks of the returns DataFrame in the prices and volumes DataFrame. With IndexSlice i have the following:
idx = pd.IndexSlice
df_prices = df_prices.loc[df_return.index, idx[:, df_return.columns]]
df_return has a shape df_return.shape=(22, 11224) and df_priceshas df_prices.shape=(821, 22488)
With said formula it takes 4.94 seconds to complete the slicing. I've tried constructing a MultiIndex from product with the columns in df_return and a list ['adjClose','volume'] and the results are instantaneous:
cols = pd.MultiIndex.from_product([["adjClose", "volume"], df_return.columns])
df_prices = df_prices.loc[df_return.index, cols]
What could be causing this delay with IndexSlice? it's not that intuitive that creating a new MultiIndex is faster than using IndexSlice
Thanks!