基于多列（Name+ID）对比两个DataFrame并识别数据变更

旧数据 #

import pandas as pd

old = pd.DataFrame({
    'Name': ['Ronaldo', 'Messi', 'Shevchenko', 'Maradona'],
    'Id': [12414, 4344134, 1234435, 37346],
    'Club': ['Al-Nassr', 'Miami', 'Milan', 'Retired'],
    'Number': [7, 30, 7, None]
})

新数据 #

new = pd.DataFrame({
    'Name': ['Ronaldo', 'Messi', 'Shevchenko', 'Neymar'],
    'Id': [12414, 4344134, 1234435, 423552],
    'Club': ['Al-Nassr', 'Miami', 'Retired', 'Al Hilal'],
    'Number': [7, 10, None, 10]
})

需求 #

基于Name和Id列对比新旧DataFrame，新增Changes列标识数据状态：

• 无变更：标记为No change
• 字段变更：列出所有变更的字段名，多字段用逗号分隔
• 新条目：标记为New entry
• 可选需求：包含旧数据中已删除的条目，标记为Removed

方案1：仅识别新增与变更条目 #

# 左连接新旧数据，以Name和Id为匹配键
merged = pd.merge(new, old, on=['Name', 'Id'], how='left', suffixes=('_new', '_old'))

def get_changes(row):
    changes = []
    # 检查Club字段差异
    if row['Club_new'] != row['Club_old']:
        changes.append('Club')
    # 检查Number字段差异（兼容None值比较）
    num_new_null = pd.isna(row['Number_new'])
    num_old_null = pd.isna(row['Number_old'])
    if num_new_null != num_old_null or row['Number_new'] != row['Number_old']:
        changes.append('Number')
    # 判断新条目
    if pd.isna(row['Club_old']):
        return 'New entry'
    # 判断无变更
    elif not changes:
        return 'No change'
    # 返回变更字段
    return ', '.join(changes)

# 生成Changes列并整理输出格式
merged['Changes'] = merged.apply(get_changes, axis=1)
result = merged[['Name', 'Id', 'Club_new', 'Number_new', 'Changes']].rename(columns={
    'Club_new': 'Club',
    'Number_new': 'Number'
})

print(result)

输出结果：

Name       Id      Club Number       Changes
0     Ronaldo    12414   Al-Nassr      7     No change
1       Messi   4344134     Miami     10        Number
2  Shevchenko   1234435  Retired   None  Club, Number
3      Neymar    423552  Al Hilal     10     New entry

方案2：包含删除条目 #

# 全外连接新旧数据，保留所有条目
merged_full = pd.merge(new, old, on=['Name', 'Id'], how='outer', suffixes=('_new', '_old'))

def get_changes_full(row):
    # 判断删除条目
    if pd.isna(row['Club_new']):
        return 'Removed'
    # 判断新条目
    elif pd.isna(row['Club_old']):
        return 'New entry'
    # 检查字段变更
    changes = []
    if row['Club_new'] != row['Club_old']:
        changes.append('Club')
    num_new_null = pd.isna(row['Number_new'])
    num_old_null = pd.isna(row['Number_old'])
    if num_new_null != num_old_null or row['Number_new'] != row['Number_old']:
        changes.append('Number')
    # 判断无变更
    if not changes:
        return 'No change'
    return ', '.join(changes)

# 生成Changes列并整理输出格式
merged_full['Changes'] = merged_full.apply(get_changes_full, axis=1)
result_full = merged_full.copy()
result_full['Club'] = result_full['Club_new'].fillna(result_full['Club_old'])
result_full['Number'] = result_full['Number_new'].fillna(result_full['Number_old'])
result_full = result_full[['Name', 'Id', 'Club', 'Number', 'Changes']]

print(result_full)

输出结果：

Name       Id      Club Number       Changes
0     Ronaldo    12414   Al-Nassr      7     No change
1       Messi   4344134     Miami     10        Number
2  Shevchenko   1234435  Retired   None  Club, Number
3      Neymar    423552  Al Hilal     10     New entry
4   Maradona     37346  Retired   None        Removed

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