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