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]

Answer 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)