Transpose data into multiple column using pandas dataframe

Question

I have a file which has two columns Phone and Type. Now I need to transpose these two columns into three columns based on the Type value(Type will have three values only i.e. Home,Office and Personal)

Source data example:

ID|Phone|Type
1|123#345#567|Home#Office#Personal
2|890|Office
3|431#676|Personal#Home

Output req:

ID|Home|Office|Personal
1|123|345|567
2||890|
3|676|431|

I am able to map the Phone with their types, but struggling to split the values in separate columns, here is snippet of my code:

zipped = zip(df['Phone'],df['Type'])

df['PhoneData'] = ['|'.join(','.join(y).rstrip('|')
                                   for y in zip(a.split('#'), b.split('#')))
                          for a, b in zipped]

Raymond Kwok · Accepted Answer · 2022-02-22 05:16:40Z

1

You can build a DataFrame from a list of dictionaries where each dictionary's keys and values are column names and values respectively, so this will do the job:

text = '''Phone|Type
123#345#567|Home#Office#Personal
890|Office
431#676|Personal#Home'''

pd.DataFrame([
    dict(zip(type.split('#'), phone.split('#'))) 
        for phone, type in map(lambda t: t.split('|'), text.splitlines()[1:])
])

If your data isn't in text but as a dataframe,

df['Phone'] = df['Phone'].str.split('#')
df['Type'] = df['Type'].str.split('#')

df.explode(['Phone', 'Type']).set_index('Type', append=True).unstack().droplevel(0, axis=1)

edited Feb 22, 2022 at 5:16

answered Feb 22, 2022 at 5:06

Raymond Kwok

2,5512 gold badges11 silver badges11 bronze badges

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

4 Comments

Mayank Porwal Over a year ago

Nice answer .. +1. Good use of append=True in set_index.

Mayank Porwal Over a year ago

@kunalsharma If you see error ValueError: column must be a scalar, your pandas version maybe older. Either upgrade it or use command : df.apply(pd.Series.explode).set_index('Type', append=True).unstack().droplevel(0, axis=1) .

Raymond Kwok Over a year ago

Thanks for sharing. It's good to know about that!

Kunal Sharma Over a year ago

This command is giving me the same output as I was doing in zip : Phone Type 0 [123, 345, 567] [Home, Office, Personal] 1 [890] [Office] 2 [431, 676] [Personal, Home] I was working on to get output like: ID|Home|Office|Personal 1|123|345|567 2||890| 3|676|431|

Collectives™ on Stack Overflow

Transpose data into multiple column using pandas dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related