Unstack to top column level

Question

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?

Answer 1

You can stack your existing columns, and then unstack in any order you want.

>>> df = pd.DataFrame({'Col2':[10,20,30,40,50], 'Col1':[60,70,80,90,100]}, index=pd.MultiIndex.from_arrays([['A','A','A','B','B'], [1,2,3,4,5]]))
>>> df.stack().unstack(level=[0, -1])
      A           B       
   Col2  Col1  Col2   Col1
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

The benefit of this approach is that you original column order is preserved (in the example above I purposefully put Col2 before Col1 to demonstrate this).

Answer 2

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