CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
venkatesh subramaniam
venkatesh subramaniam

Reputation: 7

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

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.

Upvotes: 0

Views: 158

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150745

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

Upvotes: 3

Sina Kamalii
Sina Kamalii

Reputation: 46

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.

Upvotes: 1

Related Questions