日终对账：多DataFrame基于ID列找无匹配异常值问题排查

需求说明 #

• 构建日终对账流程，每日营业结束后校验数据，找出ID列无匹配的异常值（非重复项）
• 需完成双向比对：df1 ↔ df2、df1 ↔ df3
• 三个DataFrame均包含ID和Source列，ID格式示例：20_777666333_bob或-1000_932143097_john
• 异常值定义：某ID仅出现在单个DataFrame中，需提取至独立DataFrame用于后续邮件通知

当前问题与代码分析 #

现有代码逻辑缺陷 #

现有代码仅对单个DataFrame去重，未实现跨DataFrame的ID匹配校验，无法提取非重复异常值：

# List of dataframes
dataframes = [df1, df2, df3]

# Iterate through dataframes and drop duplicates
for i, df in enumerate(dataframes, start=1):
    dataframes[i-1] = df.drop_duplicates(subset='ID')

# Display the updated dataframes
for i, df in enumerate(dataframes, start=1):
    print(f"DataFrame {i} after dropping duplicates:")
    print(df)
    print()

示例代码的隐藏问题 #

用户提供的示例代码存在语法错误（ID值未用字符串包裹），且因ID列数据类型不统一导致匹配失效：

修正后的示例代码

import pandas as pd

df1 = pd.DataFrame({"ID": ["20_777666333_bob", "-1000_932143097_john", "-5_987443211_jane"], "Source": ["A"]*3})
df2 = pd.DataFrame({"ID": ["440_098742123_jim", "680_715355572_bill", "20_777666333_bob"], "Source": ["B"]*3})
df3 = pd.DataFrame({"ID": ["680_715355572_bill", "-1000_932143097_john", "-900_976165534_sean"], "Source": ["C"]*3})

# 原错误代码
x = pd.concat([df1, df2, df3]).drop_duplicates(subset='ID', keep=False)

预期异常值：

1. ID = -5_987443211_jane, Source A
2. ID = 440_098742123_jim, Source B
3. ID = -900_976165534_sean, Source C

问题核心：ID列数据类型不统一——df1为object类型，df2、df3为string类型，合并后类型兼容但字符串匹配逻辑失效，导致无法正确识别非重复项。

解决方案 #

步骤1：统一ID列数据类型 #

先将所有DataFrame的ID列强制转换为字符串类型，确保跨表匹配的一致性：

# 统一所有ID列为string类型
df1['ID'] = df1['ID'].astype(str)
df2['ID'] = df2['ID'].astype(str)
df3['ID'] = df3['ID'].astype(str)

步骤2：提取非重复异常值 #

提供两种可靠实现方案：

方案1：基于全局计数筛选

合并所有数据后，统计每个ID的出现次数，筛选仅出现1次的记录：

# 合并三个DataFrame
combined_df = pd.concat([df1, df2, df3])
# 统计每个ID的出现频次
id_frequency = combined_df['ID'].value_counts()
# 筛选仅出现1次的ID（异常值）
exception_df = combined_df[combined_df['ID'].isin(id_frequency[id_frequency == 1].index)]
# 重置索引（可选）
exception_df = exception_df.reset_index(drop=True)

方案2：基于差集的双向校验

分别计算每个DataFrame与另外两个的ID差集，再合并结果，更贴合"双向比对"的需求：

# df1中不在df2和df3的ID
df1_except = df1[~df1['ID'].isin(pd.concat([df2, df3])['ID'])]
# df2中不在df1和df3的ID
df2_except = df2[~df2['ID'].isin(pd.concat([df1, df3])['ID'])]
# df3中不在df1和df2的ID
df3_except = df3[~df3['ID'].isin(pd.concat([df1, df2])['ID'])]
# 合并所有异常记录
exception_df = pd.concat([df1_except, df2_except, df3_except]).reset_index(drop=True)

结果验证 #

运行上述代码后，exception_df将完全匹配预期的3条异常记录，可直接用于后续邮件内容填充。

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