How to pivot dataframe and transpose 1 row

Question

I want to pivot this dataframe and convert the columns to a second level multiindex or column.

Original dataframe:

    Type        VC  C   B   Security
0   Standard    2   2   2       A
1   Standard    16  13  0       B
2   Standard    52  35  2       C
3   RI          10  10  0       A
4   RI          10  15  31      B
5   RI          10  15  31      C

Desired dataframe:

    Type            A   B  C  
0   Standard VC     2   16 52
1   Standard C      2   13 35
2   Standard B      2   0  2
3   RI       VC     10  10 10
11  RI       C      10  15 15
12  RI       B      0   31 31

Please provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). Also see [How to make pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — MagnusO_O, Nov 02 '22 at 19:56

score 0 · Answer 1 · answered Nov 02 '22 at 20:39

You could try as follows:

Use df.pivot and then transpose using df.T.
Next, chain df.sort_index to rearrange the entries, and apply df.swaplevel to change the order of the MultiIndex.
Lastly, consider getting rid of the Security as columns.name, and adding an index.name for the unnamed variable, e.g. Subtype here.
If you want the MultiIndex as columns, you can of course simply use df.reset_index at this stage.

res = (df.pivot(index='Security', columns='Type').T
       .sort_index(level=[1,0], ascending=[False, False])
       .swaplevel(0))

res.columns.name = None
res.index.names = ['Type','Subtype']
print(res)

                   A   B   C
Type     Subtype            
Standard VC        2  16  52
         C         2  13  35
         B         2   0   2
RI       VC       10  10  10
         C        10  15  15
         B         0  31  31

How to pivot dataframe and transpose 1 row

1 Answers1