Get two columns with same name side by side using Pivot function in Python

October 19, 2022

I have create the table using following code I cannot reshuffle columns evertime manually because, the number of Ctypes may change. How can I do this?

from collections import OrderedDict
from pandas import DataFrame
import pandas as pd
import numpy as np

table = OrderedDict((
    ("Item", ['Item0', 'Item0', 'Item1', 'Item1']),
    ('CType',['Gold', 'Bronze', 'Gold', 'Silver']),
    ('USD',  ['1$', '2$', '3$', '4$']),
    ('EU',   ['1€', '2€', '3€', '4€'])
))
d = DataFrame(table)```

    Item    CType   USD EU
0   Item0   Gold    1$  1€
1   Item0   Bronze  2$  2€
2   Item1   Gold    3$  3€
3   Item1   Silver  4$  4€

I am using following code in to rearrange df

p = d.pivot(index='Item', columns='CType')
p.columns = p.columns.map('.'.join)
p = p.reset_index()

        Item    USD.Bronze  USD.Gold    USD.Silver  EU.Bronze   EU.Gold EU.Silver
0       Item0       2$          1$          NaN         2€      1€      NaN
1       Item1       NaN         3$          4$          NaN     3€      4€

But I want USD and EU side by side for each CType like below

        Item    USD.Bronze EU.Bronze USD.Gold EU.Gold USD.Silver EU.Silver
0       Item0       2$          2€    1$        1€    NaN          NaN
1       Item1       NaN         NaN   3$        3€    4$           4€

I cannot reshuffle columns evertime manually because, the number of Ctypes may change. How can I do this?

>Solution :

Add DataFrame.sort_index by MultiIndex:

p = (d.pivot(index='Item', columns='CType')
       .sort_index(axis=1, level=[1, 0], ascending=[True, False])
       .reindex(d['Item'].unique()))
p.columns = p.columns.map('.'.join)
print (p)
      USD.Bronze EU.Bronze USD.Gold EU.Gold USD.Silver EU.Silver
Item                                                            
Item0         2$        2€       1$      1€        NaN       NaN
Item1        NaN       NaN       3$      3€         4$        4€