How can I transform dataframe using pd.melt-CodePudding

This is my dataframe for example

df = pd.DataFrame([['Bob', 'lunch', 70], 
                   ['Bob', 'dinner', 160], 
                   ['Sara', 'lunch', 150], 
                   ['Sara', 'dinner', 220]], 
columns=['Name', 'Meal', 'Cost'])

   Name    Meal  Cost
0   Bob   lunch    70
1   Bob  dinner   160
2  Sara   lunch   150
3  Sara  dinner   220

How can I transform the df into the following using pd.melt or any other way?

   Name   Meal_1  Cost_1   Meal_2  Cost_2
0   Bob   lunch       70   dinner     160
1  Sara   lunch      150   dinner     220

CodePudding user response：

This looks more like a job for pivot() not melt().

df.pivot(index=['Name'], columns=['Meal'], values=['Cost'])

The result is

       Cost
Meal dinner lunch
Name
Bob     160    70
Sara    220   150

Not exactly what you want but maybe better.

Alternative

Or do you look for something like this

df = df.set_index('Name')
lunch = df.loc[df.Meal.eq('lunch')]
dinner = df.loc[df.Meal.eq('dinner')]
df = pd.concat([lunch, dinner], axis=1)
df.columns = ['Meal_1', 'Cost_1', 'Meal_2', 'Cost_2']

Will result in

     Meal_1  Cost_1  Meal_2  Cost_2
Name
Bob   lunch      70  dinner     160
Sara  lunch     150  dinner     220

CodePudding user response：

Use Groupby.agg and pd.concat:

In [1293]: x = df.groupby('Name').agg(list)

In [1333]: splits = [pd.DataFrame(x[col].tolist(), index=x.index).add_prefix(col   '_') for col in x.columns]

In [1338]: res = pd.concat(splits, axis=1).reset_index()

In [1339]: res
Out[1339]: 
   Name Meal_0  Meal_1  Cost_0  Cost_1
0   Bob  lunch  dinner      70     160
1  Sara  lunch  dinner     150     220