Milage Milage - 3 months ago 8
Python Question

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.

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   -1.290477  0.719895  1.053028 -1.241249  0.413831  0.222256
three  0.758194  0.130636 -0.075926 -0.112860 -1.020070  0.400713
two    1.313731  0.251464 -0.028479 -1.314031 -0.527045  0.652248