Merge columns into one Pandas-CodePudding

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

CodePudding user response：

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