如何在Pandas中按特定值合并行并生成新列？

我希望合并包含特定值（这里是Day列的相同日期）的行，合并后生成新列来存放原多行的数据。示例如下：

原始代码 #

import pandas as pd

df = pd.DataFrame([{'Day': "Monday", 'Item_1':   "Shirt", 'Item_2': "Mug",   'Item_3': "Pen"},
                   {'Day': "Monday", 'Item_1':   "Shoes", 'Item_2': "Tea",   'Item_3': "Book"},
                   {'Day': "Tuesday", 'Item_1':"Charger", 'Item_2': "Router",'Item_3': "Phone"},
                   {'Day': "Tuesday", 'Item_1':"Monitor", 'Item_2': "Toy",   'Item_3': "Chair"},
                   {'Day': "Friday", 'Item_1':   "Shirt", 'Item_2': "TV",    'Item_3': "Desk"}])

原始数据 #

Day     Item_1  Item_2  Item_3
0   Monday  Shirt   Mug     Pen
1   Monday  Shoes   Tea     Book
2   Tuesday Charger Router  Phone
3   Tuesday Monitor Toy     Chair
4   Friday  Shirt   TV      Desk

期望结果 #

Day     Item_1     Item_2     Item_3     Item_1_1     Item_2_1     Item_3_1
Monday   Shirt      Mug        Pen        Shoes        Tea          Book
Tuesday  Charger    Router     Phone      Monitor      Toy          Chair
Friday   Shirt      TV         Desk       NaN          NaN          NaN

请问能否实现这样的合并操作？

解决方案 #

完全可以实现，用Pandas的分组+透视表方法就能搞定，具体步骤如下：

• 按Day列分组，给每组内的行添加序号后缀（从0开始）
• 将每组的行转成宽表，把序号作为列名的后缀
• 整理列名，让格式符合预期

实现代码 #

import pandas as pd

# 原始数据
df = pd.DataFrame([{'Day': "Monday", 'Item_1':   "Shirt", 'Item_2': "Mug",   'Item_3': "Pen"},
                   {'Day': "Monday", 'Item_1':   "Shoes", 'Item_2': "Tea",   'Item_3': "Book"},
                   {'Day': "Tuesday", 'Item_1':"Charger", 'Item_2': "Router",'Item_3': "Phone"},
                   {'Day': "Tuesday", 'Item_1':"Monitor", 'Item_2': "Toy",   'Item_3': "Chair"},
                   {'Day': "Friday", 'Item_1':   "Shirt", 'Item_2': "TV",    'Item_3': "Desk"}])

# 分组并添加组内行号
df['group_id'] = df.groupby('Day').cumcount()

# 转成宽表，重置索引后整理列名
result = df.pivot(index='Day', columns='group_id').reset_index()
result.columns = [f'{col[0]}_{col[1]}' if col[1] != 0 else col[0] for col in result.columns]

# 调整行顺序为期望的顺序
result = result.reindex(['Monday', 'Tuesday', 'Friday']).reset_index(drop=True)

# 输出结果
print(result)

最终输出 #

Day  Item_1 Item_2 Item_3 Item_1_1 Item_2_1 Item_3_1
0   Monday   Shirt    Mug    Pen    Shoes      Tea     Book
1  Tuesday Charger Router  Phone  Monitor      Toy    Chair
2   Friday   Shirt     TV    Desk      NaN      NaN      NaN

内容的提问来源于stack exchange，提问作者Josuke