Pandas drop_duplicates未按预期生效，如何合并重复行？

问题场景 #

• 通过含大量左连接的SQL查询构建DataFrame，产生大量重复行
• 使用pd.drop_duplicates()未达预期效果：原查询返回791行，预期按['client_name', 'Evaluation_Date']去重后得到190行，但实际仅降至301行；导出Excel手动按相同子集去重可得到预期结果，已确认日期字段为datetime类型
• 当前DataFrame结构：

• 期望合并后的结构：

尝试过的代码 #

df = pd.read_sql("query")
index = []
for i in range(len(df)):
    index.append(i)

df['index'] = index
df.set_index([df['index']])

df2 = df.groupby(['SSN', 'client_name', 'Evaluation_Date']).substance_use_name.agg(' | '.join).reset_index()
# df2.shape 为 (182,4)

df3 = pd.concat([df, df2], axis=1, join='outer').drop_duplicates(keep=False)
df3.drop_duplicates(subset=['client_name', 'Evaluation_Date'], keep='first', inplace=True)

问题分析 #

1. 手动添加索引的代码无效：df.set_index([df['index']])未赋值回df，且该操作对去重无帮助
2. 横向拼接+drop_duplicates(keep=False)逻辑错误：concat后列数翻倍，keep=False仅删除完全重复的整行，但你的数据中几乎没有完全相同的整行，因此这步未起到去重作用
3. 核心需求理解偏差：你需要的是按分组键将多行非空值合并为一行，而非简单删除重复行

解决方案 #

方案1：针对单非空值场景（每个分组内每个列仅一个非空值）

利用groupby+max（或first）直接聚合非空值，pandas的max会自动忽略NaN：

# 按ID分组，合并同一ID的多行数据
result_df = df.groupby('ID').max().reset_index()

# 若需按['client_name', 'Evaluation_Date']分组（对应你之前的去重子集）
result_df = df.groupby(['client_name', 'Evaluation_Date']).max().reset_index()

方案2：针对多非空值场景（同一列有多个值需合并）

自定义聚合函数，合并同一分组内的所有非空值：

def combine_non_null_values(x):
    # 提取非空唯一值，用' | '分隔
    non_nulls = x.dropna().unique()
    return ' | '.join(non_nulls) if len(non_nulls) > 0 else None

# 按目标分组键聚合
result_df = df.groupby(['client_name', 'Evaluation_Date']).agg(combine_non_null_values).reset_index()

效果验证 #

上述方案会直接按分组键生成唯一行，行数与你Excel手动去重后的190行一致，同时完成多行数据的合并需求。

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