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 :

check `which gives me :` it contains output in a serial manner for each column — Himanshu Poddar, Jul 21 '22 at 09:38
Still can you be explicit? The format of your output is unclear. If just looks like you need preprocessing of the columns. — mozway, Jul 21 '22 at 09:39
@mozway updated my questiom, check expected and current ouput — Himanshu Poddar, Jul 21 '22 at 09:42
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. — Himanshu Poddar, Jul 21 '22 at 09:45
So the order is an issue? Not the wrongly concatenated column? — mozway, Jul 21 '22 at 09:45
wdym by wrongly concatenated, the output that I expect is in correct manner — Himanshu Poddar, Jul 21 '22 at 09:46
the fact that you seemingly have separate 'distance' and 'km' columns in the expected output, but if this is order, this question is already answered — mozway, Jul 21 '22 at 09:47
Yes, it's already answered, please read the duplicate. What is wrong with closing a duplicate? — mozway, Jul 21 '22 at 09:48
Over here I have a number column, on which we sorted but what if there was no number column — Himanshu Poddar, Jul 21 '22 at 09:52
then you add it with `groupby` on `mw_link`, or read the duplicate, there is a solution that doesn't require sorting — mozway, Jul 21 '22 at 09:58
`there is a solution that doesn't require sorting` : that solution is deprecated `pivot_longer` — Himanshu Poddar, Jul 21 '22 at 10:08

René · Answer 1 · 2022-07-21T10:23:52.530

2

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'])

edited Jul 21 '22 at 10:23

answered Jul 21 '22 at 09:47

René

4,594
5
23
52

what if I don't have a Number column? I am saying this because this column was added manually to take care of other issue – Himanshu Poddar Jul 21 '22 at 09:56
Based on the provided data you can use `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'])` to get the same result. I added those to my answer. – René Jul 21 '22 at 10:22
1

okay, will wait for some more answers, ll accept it in a day – Himanshu Poddar Jul 21 '22 at 10:29

Pandas : Produce alternative rows with melt on multiple column

1 Answers1