如何对比两个DataFrame的差异、标注归属并导出Excel？

实现思路 #

• 不依赖DataFrame原始索引顺序，通过统计每行内容的出现次数处理重复行的差异场景
• 外连接合并两个DataFrame的行计数结果，通过计数差值判断行归属：差值为正表示该行是df1独有/多出的，差值为负表示是df2独有/多出的
• 最终将差异结果结构化后直接导出为Excel文件

完整可运行代码 #

import pandas as pd

# 初始化示例DataFrame，可替换为你的实际数据
df1 = pd.DataFrame([['tom', 10],['nick',15], ['juli',14]], columns=['name', 'age'])
df2 = pd.DataFrame([['juli', 14],['daniel',15], ['tom',10], ['tom',10]], columns=['name', 'age'])

# 统计每行的出现次数
count1 = df1.value_counts().reset_index(name='count_df1')
count2 = df2.value_counts().reset_index(name='count_df2')

# 外连接合并统计结果，空值填充为0
merged = pd.merge(count1, count2, on=['name', 'age'], how='outer').fillna(0)

diff_list = []
for _, row in merged.iterrows():
    diff = int(row['count_df1'] - row['count_df2'])
    if diff > 0:
        # df1多出的行
        for _ in range(diff):
            diff_list.append([row['name'], row['age'], 'df1'])
    elif diff < 0:
        # df2多出的行
        for _ in range(abs(diff)):
            diff_list.append([row['name'], row['age'], 'df2'])

# 转换为DataFrame导出到Excel
diff_df = pd.DataFrame(diff_list, columns=['name', 'age', '来源DataFrame'])
diff_df.to_excel('df差异结果.xlsx', index=False)

结果说明 #

生成的Excel文件和你给出的预期完全匹配：

1. ['nick',15] 标注来源为df1
2. ['daniel',15] 标注来源为df2
3. ['tom',10] 标注来源为df2

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