如何按多条件合并两个DataFrame：姓名匹配、账号不匹配且电话/邮箱匹配

问题背景 #

现有两个DataFrame：

incoming_table #

main_table #

需要筛选出满足以下条件的记录：

• first_name和last_name完全匹配
• account_number不匹配
• phone 或 email至少有一个匹配

期望最终结果：

尝试过基础内连接代码，但无法实现多条件筛选：

final_df = main_table.merge(incoming_table, on=['first_name', 'last_name'], how = 'inner')

解决方案 #

通过分步合并、筛选、整理来实现需求：

完整代码 #

import pandas as pd

# 构造示例数据（实际使用中可直接读取你的数据）
incoming_data = [
    ["John", "Smith", 12344557, 123456, "hello@hello.co.uk"],
    ["Jane", "Smith", 12335887, 891011, "you@mint.co.uk"],
    ["Sarah", "Apple", 25246555, 789101, "someone@yellow.co.uk"],
    ["George", "Bush", 45425445, 112131, "anyone@green.com"],
    ["Jacob", "May", 45543634, 415161, "everyone@grey.com"]
]
incoming_table = pd.DataFrame(incoming_data, columns=["first_name", "last_name", "account_number", "phone", "email"])

main_data = [
    ["John", "Smith", 97634437, 123456, "hello@hello.co.uk"],
    ["Jane", "Smith", 76735423, 111111, "you@mint.co.uk"],
    ["Simon", "Wilkinson", 87875564, 159263, "all@red.co.uk"],
    ["Ben", "Google", 85345757, 189147, "only@pink.com"],
    ["Jacob", "May", 45543634, 415161, "everyone@grey.com"]
]
main_table = pd.DataFrame(main_data, columns=["first_name", "last_name", "account_number", "phone", "email"])

# 1. 基于姓名做内连接，添加后缀区分两个表的字段
merged = main_table.merge(
    incoming_table,
    on=["first_name", "last_name"],
    suffixes=("_main", "_incoming"),
    how="inner"
)

# 2. 筛选符合条件的行：账号不匹配 + 电话/邮箱至少一个匹配
filtered = merged[
    (merged["account_number_main"] != merged["account_number_incoming"]) &
    ((merged["phone_main"] == merged["phone_incoming"]) | (merged["email_main"] == merged["email_incoming"]))
]

# 3. 提取并整理两个表中的符合条件记录
main_matches = filtered[["first_name", "last_name", "account_number_main", "phone_main", "email_main"]].rename(
    columns={col: col.replace("_main", "") for col in filtered.columns if "_main" in col}
)

incoming_matches = filtered[["first_name", "last_name", "account_number_incoming", "phone_incoming", "email_incoming"]].rename(
    columns={col: col.replace("_incoming", "") for col in filtered.columns if "_incoming" in col}
)

# 4. 合并去重，得到最终结果
final_df = pd.concat([main_matches, incoming_matches]).drop_duplicates().reset_index(drop=True)

print(final_df)

代码逻辑说明 #

1. 内连接配对：用merge基于姓名做内连接，通过suffixes给两个表的同名字段加后缀，方便后续字段对比。
2. 多条件筛选：用布尔索引组合三个条件，精准筛选出符合要求的配对记录。
3. 整理格式：分别提取两个表中的有效记录，重命名字段恢复原始格式。
4. 去重合并：合并两个来源的记录并去重，避免重复展示同一用户的多条符合条件记录。

内容的提问来源于stack exchange，提问作者S.Toor