如何基于ProductID列值合并Pandas数据集中的重复行？

我合并多个CSV文件后得到了如下DataFrame：

import pandas as pd
reality = pd.DataFrame({
    "ProductID": ["016193625","016193625","016215677","016215677","016259859","016259859"],
    "CountofProduct": [130.0, 130.0, 16,16, 38,38],
    "InDF1": [0,0,0,0,0,0],
    "InDF2": [0,0,0,0,0,0],
    "InDF3": [0,0,0,0,0,0],
    "InDF4": [2,2,0,0,0,0],
    "InDF5": [4,4,0,0,0,0],
    "InFile1":[0,"Y","Y","Y",0,"Y"],
    "InFile2":[0,0,0,0,0,0],
    "InFile3":[0,0,0,0,0,0],
    "InFile4":[0,0,0,0,0,0],
    "InFile5":[0,0,0,0,0,0],
    "InFile6":["Y",0,0,0,"Y",0]
})

现在的问题是相同ProductID的行未合并，我需要将其处理成如下预期格式：

expectation = pd.DataFrame({
    "ProductID": ["016193625","016215677","016259859"], 
    "CountofProduct": [130.0, 16, 38],
    "InDF1": [0,0,0],
    "InDF2": [0,0,0],
    "InDF3": [0,0,0],
    "InDF4": [2,2,0],
    "InDF5": [4,0,0],
    "InFile1":["Y","Y",0], 
    "InFile2":[0,0,0],
    "InFile3":[0,0,0],
    "InFile4":[0,0,0],
    "InFile5":[0,0,0],
    "InFile6":["Y",0,"Y"]
})

我知道原因是源文件中同一ProductID对应的行数据存在差异，导致合并后出现重复行，请问能否通过Pandas实现该合并效果？

完全可以通过Pandas实现，核心思路是按ProductID分组后，针对不同类型的列采用对应聚合规则：

• 对于CountofProduct这类同一ID下值完全一致的列，直接取第一个值即可
• 对于InDF系列数值列，用最大值聚合（有效数值大于0，能自动保留有效数据）
• 对于InFile系列混合0和"Y"的列，优先保留"Y"，无"Y"则保留0

具体代码如下：

# 定义各列的聚合规则
agg_rules = {
    "CountofProduct": "first",
    "InDF1": "max",
    "InDF2": "max",
    "InDF3": "max",
    "InDF4": "max",
    "InDF5": "max",
    # 自定义规则：替换0为缺失值，取第一个非空的"Y"，无有效值则返回0
    "InFile1": lambda x: x.replace(0, pd.NA).dropna().iloc[0] if not x.replace(0, pd.NA).dropna().empty else 0,
    "InFile2": lambda x: x.replace(0, pd.NA).dropna().iloc[0] if not x.replace(0, pd.NA).dropna().empty else 0,
    "InFile3": lambda x: x.replace(0, pd.NA).dropna().iloc[0] if not x.replace(0, pd.NA).dropna().empty else 0,
    "InFile4": lambda x: x.replace(0, pd.NA).dropna().iloc[0] if not x.replace(0, pd.NA).dropna().empty else 0,
    "InFile5": lambda x: x.replace(0, pd.NA).dropna().iloc[0] if not x.replace(0, pd.NA).dropna().empty else 0,
    "InFile6": lambda x: x.replace(0, pd.NA).dropna().iloc[0] if not x.replace(0, pd.NA).dropna().empty else 0,
}

# 分组聚合得到结果
result = reality.groupby("ProductID", as_index=False).agg(agg_rules)

运行后得到的result与你预期的expectation完全一致。

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