Merge columns into one Pandas

Question

I would like to transform the following DataFrame:

   Cat A  Cat B  Total points
0     20     30            50
1     25     35            60

Into:

  Cat  Points  Total points
0   A      20            50
1   A      25            60
2   B      30            50
3   B      35            60

I've tried to reply to my own post but i seems like it didn't work. So i'll modify my original post instead. In the future how should I reply to a comment?

To answer your question: Yes it was 35 instead of 45

This is a screen shot of my DataFrame: https://i.stack.imgur.com/OQ79p.png

Hello friend it seens like you are new here, i will help you out. Question like this big nono. Please try to display your data. And please try to show us what you tried — INGl0R1AM0R1, Aug 18 '22 at 12:18
Please don't post pictures of your data, but provide the actual data in code blocks (between triple backticks (```)). I've corrected the vals for `B` in the desired output as well. At least, I assume that you mean `30, 35` there, not `35, 45`. If you *do* mean those values, please clarify where they are supposed to come from. — ouroboros1, Aug 18 '22 at 12:30

score 1 · Accepted Answer · answered Aug 18 '22 at 12:35

You could use pd.melt to achieve this. E.g. as follows:

import pandas as pd

data = {'Cat A': [20, 25], 'Cat B': [30, 35], 'Total points': [50, 60]}
df = pd.DataFrame(data)

df_res = pd.melt(df, id_vars='Total points', value_vars=['Cat A','Cat B'], value_name='Points', var_name='Cat')
print(df_res)

   Total points    Cat  Points
0            50  Cat A      20
1            60  Cat A      25
2            50  Cat B      30
3            60  Cat B      35

To get this into the specific form you've requested (your desired df), you could do:

df_res['Cat'] = df_res['Cat'].str.extract('\s(.*$)')
col_order = ['Cat', 'Points', 'Total points']
df_res = df_res.loc[:, col_order]

print(df_res)

  Cat  Points  Total points
0   A      20            50
1   A      25            60
2   B      30            50
3   B      35            60

Merge columns into one Pandas

1 Answers1