How to resample data inside multiindex dataframe
If want grouping with resample first is necessary DatetimeIndex
only, so added DataFrame.reset_index
by all levels without first, then grouping and resample with custom function, because pct_change
for resample
is not implemented:
def percent_change(x): return pd.Series(x).pct_change()
Another idea is use numpy solution for pct_change
:
def percent_change(x): return x / np.concatenate(([np.nan], x[:-1])) - 1
df1 = (df.reset_index(level=[1,2,3]) .groupby(['month', 'week'])['Adj Close'] .resample('W') .apply(percent_change))
this way every month would yield 4 values for weekly change
So it seems there is no groupby
, only necessary downsample like sum
and chain Series.pct_change
:
df2 = (df.reset_index(level=[1,2,3]) .resample('W')['Adj Close'] .sum() .pct_change())
Drop unwanted indexes. The datetime index is enough for re-sampling / grouping
df.index = df.index.droplevel(['month', 'week', 'day'])
Re-sample by week, select the column needed, add a aggregation function and then calculate percentage change.
df.resample('W')['Adj Close'].mean().pct_change()