How to merge 3 columns into 1 whilst keeping values column separate

Question

I have the following Pivot table:

Subclass	Subclass2	Layer	Amount
A	B	C	5
E	F	G	100

I want to merge the 3 columns together and have Amount stay separate to form this:

Col1	Amount
A	NaN
B	NaN
C	5
E	NaN
F	NaN
G	100

So Far I've turned it into a regular DataFrame and did this:

df.melt(id_vars = ['SubClass', 'SubClass2'], value_name = 'CQ')

But that didn't arrange it right at all. It messed up all the columns.

I've thought once I get the melt right, I could just change the NaN values to 0 or blanks.

EDIT

I need to keep Subclass & Subclass2 in the final column as they're the higher level mapping of Layer, hence why I want the output Col1 to include them before listing Layer with Amount next to it.

Thanks!

Given your example input/output... why not just `df[['Layer', 'Amount']]` ? (if you're planning on dropping the NaNs anyway?) — Jon Clements, Jul 08 '22 at 20:19
Ah sorry that's my mistake. I've edited the question I will not drop NaN rows, I will just change them to 0. — user18233539, Jul 08 '22 at 20:24

Naveed · Accepted Answer · 2022-07-08T20:36:33.307

1

here is one way to do it

pd.concat([df,
           df[['Subclass','Subclass2']].stack().reset_index()[0].to_frame().rename(columns={0:'Layer'})
          ]
         )[['Layer','Amount']].sort_values('Layer')

    Layer   Amount
0      A    NaN
1      B    NaN
0      C    5.0
2      E    NaN
3      F    NaN
1      G    100.0

edited Jul 08 '22 at 20:36

answered Jul 08 '22 at 20:18

Naveed

11,495
2
14
21

Hi Naveed, thanks but that gives the value at the highest level. I want the Amount to be next to C and G as stated in question. – user18233539 Jul 08 '22 at 20:19
it hasn't worked I get a syntax error around the rename – user18233539 Jul 08 '22 at 20:31
@user18233539 would you mind elaborating... it's gotten a little unclear as to why it's not working as it matches what you want from your sample output? – Jon Clements Jul 08 '22 at 20:33
hi John, sure. I'm using it but it's just saying: df[['Subclass','Subclass2']]stack().reset_index()[0].to_frame().rename(columns={0:'Layer'}) ^ SyntaxError: invalid syntax. Perhaps you forgot a comma? – user18233539 Jul 08 '22 at 20:36
@user18233539 you're missing a period before the **stack**, copy paste the line perhaps and then try it out – Naveed Jul 08 '22 at 20:43
ah right! Sorry that did work! Thank you so much! I will look more into stacking with pd.concat it's much more porwerful. – user18233539 Jul 08 '22 at 20:50

mozway · Answer 2 · 2022-07-08T20:39:46.387

Here is my interpretation. Using a stack instead of melt to preserve the order.

out = (df
   .set_index('Amount')
   .stack().reset_index(name='Col1')
   .assign(Amount=lambda d: d['Amount'].where(d['level_1'].eq('Layer'), 0))
   .drop(columns='level_1')
 )

NB. with melt the syntax would be df.melt(id_vars='Amount', value_name='Col1'), and using variable in place of level_1

Output:

   Amount Col1
0       0    A
1       0    B
2       5    C
3       0    E
4       0    F
5     100    G

How to merge 3 columns into 1 whilst keeping values column separate

2 Answers2