How can I transform dataframe using pd.melt

Question

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 dataframe 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

Answer 1

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

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

The result is a pivot-table:

       Cost
Meal dinner lunch
Name
Bob     160    70
Sara    220   150

Not exactly what you want but maybe better.

b) Alternative: wide-form table

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

...which you can get with code 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']

Answer 2

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