Pandas convert multi index header

Question

I have a data frame with the following format:

        price                cost              
   id   mean   max    min    mean   max    min  
    0     1     1      1       1     2      3
    1     2     2      2       1     2      3
    2     3     3      3       1     2      3

I would like to change it to the following:

   id      mean   max    min    type
    0       1     1      1     price    
    1       2     2      2     price  
    2       3     3      3     price
    0       1     1      1     cost       
    1       2     2      2     cost   
    2       3     3      3     cost

Please accept one of our answers or describe how they do not answer your specific question. Thanks :) — Noah May
– Noah May, Commented May 21, 2021 at 6:00

Anurag Dabas · Accepted Answer · 2021-05-18 02:01:45Z

3

try with stack() method,drop() method, reset_index() method and rename() method:

df=(df.stack(0)
 .drop('id',1)
 .reset_index()
 .rename(columns={'level_0':'id','level_1':'type'}))

Output of df:

    id  type    max     mean    min
0   0   cost    2       1       1
1   0   price   1       1       3
2   1   cost    2       1       2
3   1   price   2       2       3
4   2   cost    2       1       3
5   2   price   3       3       3

edited May 18, 2021 at 2:01

answered May 18, 2021 at 1:56

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BENY · Accepted Answer · 2021-05-18 02:16:58Z

3

Let us try rename_axis + stack and follow with sort_values

out = df.rename_axis(['type',None],axis=1).stack(0).reset_index().sort_values('type')
Out[294]: 
   id   type  max  mean  min
0   0   cost    2     1    3
2   1   cost    2     1    3
4   2   cost    2     1    3
1   0  price    1     1    1
3   1  price    2     2    2
5   2  price    3     3    3

answered May 18, 2021 at 2:16

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Noah May · Accepted Answer · 2021-05-18 02:13:15Z

That is the difference between the long- and short-form of a table.

You can use .stack(), .reset_index(), and .rename() to convert your desired format.

df.stack(0).reset_index(1).rename(columns={'level_1': 'type'})

Here's an awesome article with nice visualizations describing how stack and unstack works.

And here's the result step by step:

>>> df
  price         cost        
   mean max min mean max min
0     1   1   1    1   2   3
1     2   2   2    1   2   3
2     3   3   3    1   2   3

>>> df.stack(level=0)
         max  mean  min
0 cost     2     1    3
  price    1     1    1
1 cost     2     1    3
  price    2     2    2
2 cost     2     1    3
  price    3     3    3

>>> df.stack(level=0).reset_index(level=1)
  level_1  max  mean  min
0    cost    2     1    3
0   price    1     1    1
1    cost    2     1    3
1   price    2     2    2
2    cost    2     1    3
2   price    3     3    3

>>> df.stack(level=0).reset_index(level=1).rename(columns={'level_1': 'type'})
     type max  mean  min
0   cost    2     1    3
0  price    1     1    1
1   cost    2     1    3
1  price    2     2    2
2   cost    2     1    3
2  price    3     3    3

If you need to maintain the id column (it is not a useless auto-incrementing column), you can add an extra set_index and reset_index to keep that column:

>>> df.set_index('id').stack(0).reset_index(1) \
    .rename(columns={'level_1': 'type'}).reset_index()
   id   type  max  mean  min
0   0   cost    2     1    3
1   0  price    1     1    1
2   1   cost    2     1    3
3   1  price    2     2    2
4   2   cost    2     1    3
5   2  price    3     3    3

Collectives™ on Stack Overflow

Pandas convert multi index header

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related