Python : Transposing a column with multiple values in each row into column headers

Question

I have the following data

Date            Name       Salary        Models
01/01/2019      Jack       50,000        AC, GM, MC
01/01/2019      Sam        30,000        AC, KL
01/01/2019      Los        45,000        GM

I need to transpose the column Models but each model(separated by comma) should be a column. Expected output is

Date            Name       Salary     AC     GM     MC      KL     GM
01/01/2019      Jack       50,000     1      1      1       0      0   
01/01/2019      Sam        30,000     1      0      0       1      0   
01/01/2019      Los        45,000     0      0      0       0      1

I tried the pandas.dummies. df1 = pandas.get_dummies(df, columns=['Models'])

But the problem is I don't know how to split the models in each row based on the comma and then do the pandas.dummies. Your help is much appreciated.

Your expected output looks incorrect. There are two GM columns, and the last row doesn't have any 1's. — Quang Hoang
– Quang Hoang, Commented Nov 3, 2020 at 20:03

Quang Hoang · Accepted Answer · 2020-11-03 20:02:31Z

3

You can use str.get_dummies:

df.drop('Models',axis=1).join(df.Models.str.get_dummies(', '))

Output:

         Date  Name  Salary  AC  GM  KL  MC
0  01/01/2019  Jack  50,000   1   1   0   1
1  01/01/2019   Sam  30,000   1   0   1   0
2  01/01/2019   Los  45,000   0   1   0   0

answered Nov 3, 2020 at 20:02

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sina Kamalii · Accepted Answer · 2020-11-03 19:56:25Z

1

You can use

temp_df = pd.concat([df['Models'].str.split(',', expand=True)], axis=1)

to separate the values in the Models column, and then by using

df1 = pd.get_dummies(temp_df, columns=temp_df.columns)

getting the desired result.

answered Nov 3, 2020 at 19:56

Sina Kamalii

463 bronze badges

Collectives™ on Stack Overflow

Python : Transposing a column with multiple values in each row into column headers

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related