How to groupby a date column level and replace with a column level containing the corresponding iso-CodePudding

I have a dataframe with the following column levels:

I would like to groupby the DATE level to the corresponding iso week number, and have the output equal to:

I tried the following:

df = df.groupby(
            lambda d: d.isocalendar().week,
            level='DATE', axis='columns'
     ).sum()
df.columns.rename('WK', inplace=True)

results in:

which is part of what I want, but I would like to keep the other levels of the column MultiIndex (YEAR and CAT in this case). Where Year is an integer, and CAT could be string, or categorical.

CodePudding user response：

You can first rename level DATE for weeks and then aggregate sum per all 4 levels of MultiIndex in columns:

df = (df.rename(columns=lambda d: d.isocalendar().week, level='DATE')
        .groupby(level=[0,1,2,3], axis=1)
        .sum())