如何使用pd.melt转换数据

Question

例如，这是我的数据

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

如何使用pd.melt或任何其他方式将数据转换为以下内容？

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

Answer 1

( a)这看起来更像是pivot()而不是melt()的工作。

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

其结果是一个支点表：

       Cost
Meal dinner lunch
Name
Bob     160    70
Sara    220   150

不完全是你想要的，也许更好。

b)备选方案:宽表格

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

您可以使用这样的代码获得...which：

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

使用Groupby.agg和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