How to groupby a date column level and replace with a column level containing the corresponding iso week number

user_12822130 0
Viewed 33

I have a dataframe with the following column levels:

enter image description here

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

enter image description here

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:

enter image description here

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.

pandas dataframe group-by
1 Answers

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