Converting Multi-level columns to single level in Pandas

Question

I have a multi level columns:

Item	CBP		SAC
	Qty	Date	Qty	Date
Item A	20	2/10/2021	32	3/12/2022

How do use pandas to change the above table to:

Item	Branch	Qty	Date
Item A	CBP	20	2/10/2021
Item A	SAC	30	3/12/2022

I usually use the melt in pandas (Convert columns into rows with Pandas)

I dont know how to process this since there is two level. Need help Thank you very much.

it'll help if you can paste the first dataframe such that we can copy and run it. Use `df.head().to_dict()` and paste the output for that — Shubham Periwal, Jan 11 '22 at 01:54

score 0 · Answer 1 · answered Jan 11 '22 at 02:20

Assuming all are columns:

idx = pd.MultiIndex.from_arrays([['Item', 'CBP', 'CBP', 'SAC', 'SAC'], [None, 'Qty', 'Date', 'Qty', 'Date']])
df = pd.DataFrame([['Item A', 20, '2/10/2021', 32, '3/12/2022']],
                  columns=idx)

You can set the Item columns aside as index and stack. explode Items after reshaping to get rid of the extra level.

(df.set_index('Item')
   .rename_axis(columns=('Branch',None))
   .stack(0)
   .reset_index()
   .explode('Item')
 )

Output:

     Item Branch       Date  Qty
0  Item A    CBP  2/10/2021   20
1  Item A    SAC  3/12/2022   32

Converting Multi-level columns to single level in Pandas

1 Answers1