Pandas reshaping to sub-columns

June 16, 2023

I have a dataframe which has columns = [‘personName’, ‘Date’, ‘groceries’, ‘entertainment’]

with groceries and entertainment being how much that person spent in each category that day.
I am trying to reshape the data frame so that ‘Date’ becomes the top level column with ‘groceries’ and ‘entertainment’ subcolumns under each Date. Making it easier to compare spends across each category across different persons, How can I do that pls? The code below does not create the subcolumns but rather Date just repeats itself for both values.

pivoted = df(index='personName', columns=['Date'], values=['groceries', 'entertainment'])

>Solution :

It would be better if you provide an MRE but you can still try this :

pivoted = df.pivot(index='personName', columns=['Date'], values=['groceries', 'entertainment'])

out = pivoted.swaplevel(axis=1).sort_index(axis=1) # <-- add this line to level-up/sort the dates

Or, in its chained version :

out = (
    df.pivot(
        index='personName',
        columns=['Date'],
        values=['groceries', 'entertainment'])
    .swaplevel(axis=1).sort_index(axis=1)
)