How can i unstack a pandas dataframe to have the level 0 index above the columns-CodePudding

I have this dataframe 'info' with a multilevel index ['symbol','date']

Symbol   date            Sector      Industry
a        2022-01-01       2             6
         2022-01-02       3             7
b        2022-01-02       4             8
         2022-01-03       5             9

and I would like to have

                   a                      b 
                sector  industry    sector  industry
2022-01-01         2       6          NaN     NaN
2022-01-02         3       7           4       8
2022-01-03         NaN     NaN         5       9

I have been told to unstack them but if I do

info.unstack(level=0)

i get

                 sector                  industry   
date            a      b               a          b
2022-01-01      2      NaN             6        NaN
2022-01-02      3      4               7          8
2022-01-03      NaN    5               NaN        9

How can i get the a and b on top of sector and industry?

CodePudding user response：

you can use reorder_levels:

In [21]: df
Out[21]:
                   Sector  Industry
Symbol date
a      2022-01-01       2         6
       2022-01-02       3         7
b      2022-01-02       4         8
       2022-01-03       5         9

In [22]: df = df.unstack(level = 0)

In [23]: df
Out[23]:
           Sector      Industry
Symbol          a    b        a    b
date
2022-01-01    2.0  NaN      6.0  NaN
2022-01-02    3.0  4.0      7.0  8.0
2022-01-03    NaN  5.0      NaN  9.0

In [24]: df.columns.names = ['C', 'Symbol'] # name both levels in columns

In [25]: df
Out[25]:
C          Sector      Industry
Symbol          a    b        a    b
date
2022-01-01    2.0  NaN      6.0  NaN
2022-01-02    3.0  4.0      7.0  8.0
2022-01-03    NaN  5.0      NaN  9.0

In [26]: df.reorder_levels(['Symbol', 'C'], axis = 1)
Out[26]:
Symbol          a      b        a        b
C          Sector Sector Industry Industry
date
2022-01-01    2.0    NaN      6.0      NaN
2022-01-02    3.0    4.0      7.0      8.0
2022-01-03    NaN    5.0      NaN      9.0