Skip to content
Advertisement

Stack columns above value labels in pandas pivot table

Given a dataframe that looks like:

import numpy as np
import pandas as pd

df = pd.DataFrame({
               'Key1': ['one', 'one', 'two', 'three'] * 3,
               'Key2': ['A', 'B', 'C'] * 4,
               'Value1': np.random.randn(12),   
               'Value2': np.random.randn(12)
               })
print df
     Key1 Key2    Value1    Value2
0     one    A  1.405817  1.307511
1     one    B -0.037627 -0.215800
2     two    C -0.116591 -1.195066
3   three    A  2.044775 -1.207433
4     one    B -1.109636  0.031521
5     one    C -1.529597  1.761366
6     two    A -1.349865  0.321454
7   three    B  0.814374  2.285579
8     one    C  0.178702  0.479210
9     one    A  0.718921  0.504311
10    two    B -0.375898 -0.379315
11  three    C -0.822250  0.703811

I can pivot it so that I get the first key as rows and the second key as columns

pt = df.pivot_table(
    index=['Key1'],
    columns=['Key2'],
    values=['Value1','Value2']
    )

print pt
         Value1                        Value2                    
Key2          A         B         C         A         B         C
Key1                                                             
one   -0.076303 -0.899175  0.631831 -1.196249  0.339583  0.583173
three  0.105773  0.460911 -0.387941  0.697660  1.091828  1.447365
two    1.391854  0.499841 -0.422887 -0.366169 -0.230001  2.417211

How can flip it such that the values and columns are stacked by the column first and then the values, e.g.

       A                    B                     C    
       Value1     Value2    Value1     Value2     Value1     Value2
one    -0.0763    -1.19625  -0.89918    0.339583   0.631831  0.583173
three   0.105773   0.69766   0.460911   1.091828  -0.38794   1.447365
two     1.391854  -0.36617   0.499841  -0.23      -0.42289   2.417211

I’ve looked at MultiIndexes but I can’t see how that would affect the layout in this way.

Advertisement

Answer

You can use MultiIndex.swaplevel and sort_index:

pt.columns = pt.columns.swaplevel(0,1)
pt = pt.sort_index(axis=1)
#pt = pt.sort_index(axis=1, level=0)
print (pt)
Key2          A                   B                   C          
         Value1    Value2    Value1    Value2    Value1    Value2
Key1                                                             
one    0.439076 -0.492287 -0.841044  0.435300 -0.490016  0.045178
three -0.975650  0.276097  0.617394 -0.553229  0.213254 -0.044848
two    0.291563  2.730831 -2.405110 -0.878826 -0.801219  0.908600

Another solution with DataFrame.swaplevel:

pt = pt.swaplevel(0,1, axis=1).sort_index(axis=1)
print (pt)
Key2          A                   B                   C          
         Value1    Value2    Value1    Value2    Value1    Value2
Key1                                                             
one    0.439076 -0.492287 -0.841044  0.435300 -0.490016  0.045178
three -0.975650  0.276097  0.617394 -0.553229  0.213254 -0.044848
two    0.291563  2.730831 -2.405110 -0.878826 -0.801219  0.908600
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement