Pandas change order of columns in pivot table

Question

The representation of pivot tabel not looks like something I looking for, to be more specific the order of the resulting rows. I can`t figure out how to change it in proper way.

Example df:

test_df = pd.DataFrame({'name':['name_1','name_1','name_1','name_2','name_2','name_2','name_3','name_3','name_3'], 
                        'month':[1,2,3,1,2,3,1,2,3], 
                        'salary':[100,100,100,110,110,110,120,120,120], 
                        'status':[1,1,2,1,1,3,2,2,1]})

code for make pivot:

test_df.pivot_table(index='name', columns=['month'], 
             values=['salary', 'status'])

Actual output:

       salary           status      
month       1    2    3      1  2  3
name                                
name_1    100  100  100      1  1  2
name_2    110  110  110      1  1  3
name_3    120  120  120      2  2  1

The output I want to see:

       salary status salary status salary status             
month       1     1      2      2      3      3
name
name_1    100     1     100     1     100     2
name_2    110     1     110     1     110     3
name_3    120     2     120     2     120     1

Jondiedoop · Accepted Answer · 2019-03-29 11:14:41Z

17

You would use sort_index, indicating the axis and the level:

piv = test_df.pivot_table(index='name', columns=['month'], 
             values=['salary', 'status'])
piv.sort_index(axis='columns', level='month')
#       salary status salary status salary status
#month       1      1      2      2      3      3
#name                                            
#name_1    100      1    100      1    100      2
#name_2    110      1    110      1    110      3
#name_3    120      2    120      2    120      1

answered Mar 29, 2019 at 11:14

Jondiedoop

3,38311 silver badges24 bronze badges

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

Comments

Chris Adams · Accepted Answer · 2019-03-29 11:14:23Z

6

Use DataFrame.sort_index with axis=1, level=1 arguments

(test_df.pivot_table(index='name', columns=['month'], 
             values=['salary', 'status'])
 .sort_index(axis=1, level=1))

[out]

       salary status salary status salary status
month       1      1      2      2      3      3
name                                            
name_1    100      1    100      1    100      2
name_2    110      1    110      1    110      3
name_3    120      2    120      2    120      1

answered Mar 29, 2019 at 11:14

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

Comments

Rajat Kansal · Accepted Answer · 2019-03-29 11:23:09Z

1

import pandas as pd

df = pd.DataFrame({'name': 
['name_1','name_1','name_1','name_2','name_2','name_2','name_3','name_3','name_3'], 
                        'month':[1,2,3,1,2,3,1,2,3], 
                        'salary':[100,100,100,110,110,110,120,120,120], 
                        'status':[1,1,2,1,1,3,2,2,1]})



df = df.pivot_table(index='name', columns=['month'], 
             values=['salary', 'status']).sort_index(axis='columns', level='month')
print(df)

answered Mar 29, 2019 at 11:23

Rajat Kansal

966 bronze badges

1 Comment

Martijn Pieters Over a year ago

This just repeats the two answers already posted 10 minutes prior, but without any explanation as to what is being done.

Collectives™ on Stack Overflow

Pandas change order of columns in pivot table

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related