I have the following dataframe:
df = pd.DataFrame({'Col1':[10,20,30,40,50], 'Col2':[60,70,80,90,100]}, index=pd.MultiIndex.from_arrays([['A','A','A','B','B'], [1,2,3,4,5]]))
I would like to obtain the following:
'A' 'B'
'Col1' 'Col2' 'Col1' 'Col2'
10 60 NaN NaN
20 70 NaN NaN
30 80 NaN NaN
NaN NaN 40 90
NaN NaN 50 100
I tried using df.unstack(0).swaplevel(0,1,axis=1) but this results in:
'A' 'B' 'A' 'B'
'Col1' 'Col1' 'Col2' 'Col2'
10 NaN 60 NaN
20 NaN 70 NaN
30 NaN 80 NaN
NaN 40 NaN 90
NaN 50 NaN 100
Can someone point me in the right direction?
sort the index of level=0 on axis=1 and you would have your desired view:
df.unstack(0).swaplevel(0,1,axis=1).sort_index(axis=1)
A B
Col1 Col2 Col1 Col2
1 10.0 60.0 NaN NaN
2 20.0 70.0 NaN NaN
3 30.0 80.0 NaN NaN
4 NaN NaN 40.0 90.0
5 NaN NaN 50.0 100.0