Multilevel column header as new column

Question

I have a dataframe in the following form with a multilevel column header. In this case each Category has a date and a price.

 Person Category1 Category2 index Name Date Price Date Price 1 Bob 1999 1 1994 2 2 Alice 1992 8 1992 3 3 Alice 1994 19 1990 6

I would like to transform the first column header i.e. "Person", "Category1", "Category2", etc. into a new column with the name as the values. In addition I want to concatenate the columns with the same header names, to get something like this:

index Name Date Price Category 1 Bob 1999 1 Category1 2 Alice 1992 8 Category1 3 Alice 1994 19 Category1 4 Bob 1994 2 Category2 5 Alice 1992 3 Category2 6 Alice 1990 6 Category2

Tried reset_index() to remove the multi-index but nothing changed.

Shubham Sharma · Accepted Answer · 2021-04-24 16:35:48Z

We can set the index of dataframe to the Name column then stack the dataframe on level=0 to reshape followed by rename_axis and reset_index

df.set_index(('Person', 'Name')).stack(0)\ .rename_axis(['Name', 'Category']).reset_index()

 Name Category Date Price 0 Bob Category1 1999 1 1 Bob Category2 1994 2 2 Alice Category1 1992 8 3 Alice Category2 1992 3 4 Alice Category1 1994 19 5 Alice Category2 1990 6

Collectives™ on Stack Overflow

Multilevel column header as new column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related