如何用Python Pandas高效比较DataFrame并提取员工数据变更信息

因为Id可能重复，必须用Name + Id作为联合主键匹配记录，用Pandas内置的合并与矢量化操作替代循环，能大幅提升效率：

步骤1：准备数据并合并两个DataFrame #

先修正索引设置（不能单独用Id当索引，避免重复键冲突），再通过联合主键合并表，将新旧数据放在同一条记录中方便对比：

import pandas as pd

columns = ['Name', 'Id', 'Salary', 'Work_hours', 'Location', 'Date_updated']

old_data = [
    ['John', 1, 1000, 100, 'York', '2013-01-01'],
    ['Tim', 2, 1500, 210, 'Chicago', '2013-04-03'],
    ['Rob', 3, 900, 180, 'San Francisco', '2013-02-07'],
    ['Tom', 4, 2000, 220, 'New York', '2013-01-01'],
    ['Steve', 5, 1200, 190, 'Chicago', '2013-06-01'],
    ['Jim', 6, 1000, 170, 'New York', '2013-01-01'],
    ['Mark', 7, 1500, 210, 'Chicago', '2013-04-03'],
    ['John', 8, 900, 180, 'San Francisco', '2013-02-07'],
]

new_data = [
    ['John', 1, 2000, 200, 'New York', '2014-01-01'],
    ['Tim', 2, 1000, 300, 'Miami', '2014-04-03'],
    ['Rob', 9, 900, 180, 'San Francisco', '2016-02-07'],  # 新员工，无需处理
]

# 用Name+Id作为联合匹配键，不单独设Id为索引
df_old = pd.DataFrame(data=old_data, columns=columns)
df_new = pd.DataFrame(data=new_data, columns=columns)

# 合并两个表，只保留两边都有的记录（自动排除新员工）
merged_df = pd.merge(df_old, df_new, on=['Name', 'Id'], suffixes=('_old', '_new'), how='inner')

步骤2：筛选符合条件的变更记录 #

先筛选Date_updated不同的记录，再从中找出Salary/Work_hours/Location至少有一个变更的记录：

# 筛选更新时间不同的记录
date_changed = merged_df['Date_updated_old'] != merged_df['Date_updated_new']

# 定义需要检查变更的字段
check_cols = ['Salary', 'Work_hours', 'Location']
# 检查字段是否存在差异
has_changes = merged_df[[f'{col}_old' for col in check_cols]] != merged_df[[f'{col}_new' for col in check_cols]]
# 筛选至少有一个字段变更的记录
any_change = has_changes.any(axis=1)

# 最终得到符合要求的变更记录
filtered_df = merged_df[date_changed & any_change]

步骤3：提取变更信息并格式化输出 #

遍历筛选后的记录，提取新旧数据和变更字段：

print(">> Following employee's data was updated")
print("#Ignoring the Time_updated")

for _, row in filtered_df.iterrows():
    # 提取完整的旧数据和新数据记录
    old_record = row[['Name', 'Id', 'Salary_old', 'Work_hours_old', 'Location_old', 'Date_updated_old']].tolist()
    new_record = row[['Name', 'Id', 'Salary_new', 'Work_hours_new', 'Location_new', 'Date_updated_new']].tolist()
    
    # 找出变更的字段，匹配示例输出的字段名格式
    changed_cols = [col for col in check_cols if row[f'{col}_old'] != row[f'{col}_new']]
    changed_cols = [col.replace('Work_hours', 'Working_hours') for col in changed_cols]
    
    print(f">> Old data : {old_record}")
    print(f"   New data : {new_record}")
    print(f"   Parameters updated : {', '.join(changed_cols)}\n")

运行结果 #

>> Following employee's data was updated
#Ignoring the Time_updated
>> Old data : ['John', 1, 1000, 100, 'York', '2013-01-01']
   New data : ['John', 1, 2000, 200, 'New York', '2014-01-01']
   Parameters updated : Salary, Working_hours, Location

>> Old data : ['Tim', 2, 1500, 210, 'Chicago', '2013-04-03']
   New data : ['Tim', 2, 1000, 300, 'Miami', '2014-04-03']
   Parameters updated : Salary, Working_hours, Location

这种方法用Pandas矢量化操作替代逐行循环，数据量越大，效率优势越明显。

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