Pandas : Produce alternative rows with melt on multiple column

Question

Say I have a dataframe

data_dict = {'Number': {0: 1, 1: 2, 2: 3}, 'mw link': {0: 'SAM3703_2SAM3944 2', 1: 'SAM3720_2SAM4115 2', 2: 'SAM3729_2SAM4121_ 2'}, 'site_a': {0: 'SAM3703', 1: 'SAM3720', 2: 'SAM3729'}, 'name_a': {0: 'Chelak', 1: 'KattakurganATC', 2: 'Payariq'}, 'site_b': {0: 'SAM3944', 1: 'SAM4115', 2: 'SAM4121'}, 'name_b': {0: 'Turkibolo', 1: 'Kattagurgon Sement Zavod', 2: 'Payariq Dehgonobod'}, 'distance km': {0: 3.618, 1: 7.507, 2: 9.478}, 'manufacture': {0: 'ZTE NR 8150/8250', 1: 'ZTE NR 8150/8250', 2: 'ZTE NR 8150/8250'}}
df = pd.DataFrame(data_dict)

Expected Output :

There are these two columns site_a and site_b which I want to melt into rows but applying a simple melt gives output in series, I want them to be in an alternate fashion.

  Number    mw link         distance km  manufacture       variable value
0   1   SAM3703_2SAM3944 2    3.618     ZTE NR 8150/8250    site_a  SAM3703
1   1   SAM3703_2SAM3944 2    3.618     ZTE NR 8150/8250    site_b  SAM3944
2   2   SAM3720_2SAM4115 2    7.507     ZTE NR 8150/8250    site_a  SAM3720
3   2   SAM3720_2SAM4115 2    7.507     ZTE NR 8150/8250    site_b  SAM4115
4   3   SAM3729_2SAM4121_ 2   9.478     ZTE NR 8150/8250    site_a  SAM3729
5   3   SAM3729_2SAM4121_ 2   9.478     ZTE NR 8150/8250    site_b  SAM4121

My Solution :

This is what I have tried

df1 = pd.melt(df, id_vars=['Number', 'mw link', 'distance km', 'manufacture'], value_vars=['site_a', 'site_b'])

which gives me :

Answer 1

You just add sort_values(['Number', 'variable']):

pd.melt(df, id_vars=['Number', 'mw link', 'distance km', 'manufacture'], value_vars=['site_a', 'site_b']).sort_values(['Number', 'variable'])

Alternatives:

pd.melt(df, id_vars=['Number', 'mw link', 'distance km', 'manufacture'], value_vars=['site_a', 'site_b']).sort_values(['mw link', 'variable'])

Or:

pd.melt(df, id_vars=['Number', 'mw link', 'distance km', 'manufacture'], value_vars=['site_a', 'site_b']).sort_values(['distance km', 'variable'])