使用Pandas对比两个DataFrame导出标记差异与缺失值的Excel文件

这个需求完全可以通过Pandas结合XlsxWriter实现，以下是具体实现逻辑和可运行代码：

原代码问题 #

• 直接用pd.concat([df1, df2], axis='columns')按行索引拼接，两个数据源行数、匹配逻辑都不对应，导出数据完全错位
• 没有做数据匹配对齐、差异判断以及Excel格式配置

实现步骤 #

1. 数据预处理与匹配 #

先处理df2的ArtNr字段，去除前缀CC-得到和df1一致的编码，再以Customer、ArtNr、Product为联合主键做外连接，确保两边的缺失数据都能完整保留。

2. 差异标记 #

将两个数据源的价格、数量字段重命名区分后，判断每一行的价格、数量是否存在差异或者缺失，用于后续配置格式规则。

3. 导出Excel并配置条件格式 #

通过XlsxWriter的条件格式规则，将差异值、缺失值设置为红色字体，同时支持按客户排序/分组。

完整可运行代码 #

import pandas as pd
import xlsxwriter

# 原始数据
data1 = [('CUS001', 'Apple', '778899', '01.09.2021 - 01.10.2021', 15.50, 9), ('CUS001', 'Banana', '554466', '01.09.2021 - 14.09.2021', 12.99, 12), ('CUS001', 'Banana', '554466', '15.09.2021 - 01.10.2021', 15.50, 9), ('CUS001', 'Orange', '112233', '01.09.2021 - 01.10.2021', 20.00, 10), ('CUS002', 'Apple', '778899', '01.09.2021 - 01.10.2021', 15.50, 20), ('CUS002', 'Banana', '554466', '01.09.2021 - 01.10.2021', 12.99, 19)]
data2 = [('CUS001', 'CC-778899', 'Apple', 15.50, 10), ('CUS001', 'CC-554466', 'Banana', 12.99, 12), ('CUS002', 'CC-778899', 'Apple', 15.50, 10), ('CUS002', 'CC-554466', 'Banana', 10.50, 9)]

df1 = pd.DataFrame(data=data1, columns = ['Customer', 'Product', 'ArtNr', 'Charge Interval', 'Price', 'Qty'])
df2 = pd.DataFrame(data=data2, columns = ['Customer', 'ArtNr', 'Product', 'Price', 'Qty'])

# 处理df2的ArtNr前缀，生成匹配键
df2['ArtNr'] = df2['ArtNr'].str.replace('CC-', '')
# 重名字段区分两个数据源
df1 = df1.rename(columns={'Price': 'Price_数据源1', 'Qty': 'Qty_数据源1'})
df2 = df2.rename(columns={'Price': 'Price_数据源2', 'Qty': 'Qty_数据源2'})

# 外连接合并，保留所有行
merge_df = pd.merge(
    df1, df2,
    on=['Customer', 'ArtNr', 'Product'],
    how='outer'
).sort_values('Customer').reset_index(drop=True) # 按客户排序实现分组效果

# 导出Excel并配置格式
writer = pd.ExcelWriter('对比结果.xlsx', engine='xlsxwriter')
merge_df.to_excel(writer, sheet_name='对比结果', index=False)
workbook = writer.book
worksheet = writer.sheets['对比结果']

# 定义红色字体格式
red_format = workbook.add_format({'font_color': 'red'})

# 获取价格、数量列的位置（列索引）
price1_col = merge_df.columns.get_loc('Price_数据源1')
qty1_col = merge_df.columns.get_loc('Qty_数据源1')
price2_col = merge_df.columns.get_loc('Price_数据源2')
qty2_col = merge_df.columns.get_loc('Qty_数据源2')
max_row = merge_df.shape[0]

# 配置条件格式：价格不等/为空标红
worksheet.conditional_format(1, price1_col, max_row, price1_col,
    {'type': 'formula',
     'criteria': f'=OR(ISBLANK(${chr(ord("A")+price1_col)}2), ${chr(ord("A")+price1_col)}2<>${chr(ord("A")+price2_col)}2)',
     'format': red_format})
worksheet.conditional_format(1, price2_col, max_row, price2_col,
    {'type': 'formula',
     'criteria': f'=OR(ISBLANK(${chr(ord("A")+price2_col)}2), ${chr(ord("A")+price1_col)}2<>${chr(ord("A")+price2_col)}2)',
     'format': red_format})

# 配置条件格式：数量不等/为空标红
worksheet.conditional_format(1, qty1_col, max_row, qty1_col,
    {'type': 'formula',
     'criteria': f'=OR(ISBLANK(${chr(ord("A")+qty1_col)}2), ${chr(ord("A")+qty1_col)}2<>${chr(ord("A")+qty2_col)}2)',
     'format': red_format})
worksheet.conditional_format(1, qty2_col, max_row, qty2_col,
    {'type': 'formula',
     'criteria': f'=OR(ISBLANK(${chr(ord("A")+qty2_col)}2), ${chr(ord("A")+qty1_col)}2<>${chr(ord("A")+qty2_col)}2)',
     'format': red_format})

# 调整列宽方便查看
for col in merge_df.columns:
    width = max(merge_df[col].astype(str).map(len).max(), len(col)) + 2
    worksheet.set_column(f'{chr(ord("A")+merge_df.columns.get_loc(col))}:{chr(ord("A")+merge_df.columns.get_loc(col))}', width)

writer.save()

效果说明 #

• 两个数据源的所有数据都完整展示，仅在单个数据源存在的数据对应的缺失字段留空
• 价格、数量存在差异或者为空的单元格自动标红
• 默认按客户排序实现分组效果，如果需要折叠分组可额外调用XlsxWriter的set_row方法配置group属性

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