使用Python实现层级数据对比并在Excel中高亮差异的技术问询

需求：用Python对比历史与当前层级数据集，识别变化并在Excel中用颜色编码高亮差异。当前使用pandas遇到数据未排序、输出Excel缺失部分列的问题。

修正后的完整实现代码 #

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

# 定义当前与历史数据集
current_data_str = """L1,L2,L3
manager1,e1,e5
manager1,e1,e5
manager2,e2,e6
manager2,e2,e7
manager3,e3,e8
manager3,e4,e9
manager3,e4,e10"""

previous_data_str = """L1,L2,L3
manager1,e1,e5
manager1,e1,e5
e6,e13,
e6,e14,
e8,e15,
e9,e16,
e17,,"""

# 加载数据并处理空值，避免对比时因空值出错
current_df = pd.read_csv(pd.io.common.StringIO(current_data_str)).fillna("")
previous_df = pd.read_csv(pd.io.common.StringIO(previous_data_str)).fillna("")

# 添加数据来源标识，便于区分
current_df["来源"] = "当前数据"
previous_df["来源"] = "历史数据"

# 合并数据集并按层级列排序，解决数据未排序问题
combined_df = pd.concat([current_df, previous_df]).sort_values(by=["L1", "L2", "L3"]).reset_index(drop=True)

# 识别新增和删除的层级数据
current_unique = current_df[["L1", "L2", "L3"]].drop_duplicates()
previous_unique = previous_df[["L1", "L2", "L3"]].drop_duplicates()

added = current_unique.merge(previous_unique, on=["L1", "L2", "L3"], how="left", indicator=True).query("_merge == 'left_only'").drop("_merge", axis=1)
removed = previous_unique.merge(current_unique, on=["L1", "L2", "L3"], how="left", indicator=True).query("_merge == 'left_only'").drop("_merge", axis=1)

# 标记差异类型
combined_df["差异类型"] = ""
combined_df.loc[combined_df[["L1", "L2", "L3"]].isin(added.to_dict("list")).all(axis=1), "差异类型"] = "新增"
combined_df.loc[combined_df[["L1", "L2", "L3"]].isin(removed.to_dict("list")).all(axis=1), "差异类型"] = "删除"

# 导出到Excel并设置高亮样式
wb = Workbook()
ws = wb.active

# 写入所有数据（包含所有列，解决缺失列问题）
for r in dataframe_to_rows(combined_df, index=False, header=True):
    ws.append(r)

# 定义高亮填充色：新增行绿色，删除行红色
green_fill = PatternFill(start_color="90EE90", end_color="90EE90", fill_type="solid")
red_fill = PatternFill(start_color="FFCCCB", end_color="FFCCCB", fill_type="solid")

# 遍历行设置高亮
for row in ws.iter_rows(min_row=2):
    diff_type = row[-1].value
    if diff_type == "新增":
        for cell in row:
            cell.fill = green_fill
    elif diff_type == "删除":
        for cell in row:
            cell.fill = red_fill

# 保存结果文件
wb.save("层级数据对比结果.xlsx")

关键解决点 #

1. 数据排序：通过sort_values(by=["L1", "L2", "L3"])按层级列排序，确保数据按层级逻辑展示
2. 列缺失问题：合并时保留所有原始列，新增「来源」「差异类型」辅助列，导出时完整写入Excel
3. 差异高亮：用openpyxl设置单元格填充色，新增行标绿色、删除行标红色，直观识别变化

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