CODEWITHSUNDEEP
python-3.xpandasdataframedatetimeresampling
Slartibartfast
Slartibartfast

Reputation: 1190

How to resample data inside multiindex dataframe

I have the following dataframe:enter image description here

I need to resample the data to calculate the weekly pct_change(). How can i get the weekly change ?

Something like data['pct_week'] = data['Adj Close'].resample('W').ffill().pct_change() but the data need to groupby data.groupby(['month', 'week'])

This way every month would yield 4 values for weekly change.Which i can graph then

What i did was df['pct_week'] = data['Adj Close'].groupby(['week', 'day']).pct_change() but i got this error TypeError: 'type' object does not support item assignment

Upvotes: 5

Views: 888

Answers (2)

Vishnudev Krishnadas
Vishnudev Krishnadas

Reputation: 10960

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

Upvotes: 4

jezrael
jezrael

Reputation: 862481

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

Upvotes: 5

Related Questions