CODEWITHSUNDEEP
pythonpandasdataframemulti-index
Chau362
Chau362

Reputation: 37

Multilevel column header as new column

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.

Upvotes: 1

Views: 166

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71707

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

Upvotes: 1

Related Questions