Pandas：DataFrame按组对比行并生成标记差异的汇总行

方案1：分组末尾新增X标记汇总行 #

核心逻辑是按ID、Serial分组后，逐列判断组内是否存在多个不同值，生成对应汇总行追加到分组末尾。

import pandas as pd
import numpy as np

# 示例数据构造，可替换为你自己的DataFrame
data = [
    ["M001", 2, 52, "37", 1, 1, 1, 1],
    ["M001", 2, 55, "37", 2, 1, 0, 1],
    ["M001", 3, 51, "36,5", 1, 1, 1, 0],
    ["M001", 3, 51, "46,5", 1, 0, 1, 1],
    ["M041", 2, 52, "36,1", 1, 1, 0, 0],
    ["M041", 2, 51, "36,1", 2, 1, 2, 4],
    ["M041", 2, 52, "36,1", 1, 1, 0, np.nan],
    ["M041", 2, 52, "36,1", 1, 1, 1, np.nan],
    ["M010", 5, 58, "37,4", 0, 1, 1, 3],
    ["M010", 5, 55, "39,4", 1, 2, 1, 1],
    ["M010", 5, 58, "37,4", 1, 1, 1, 1],
]
df = pd.DataFrame(data, columns=["ID", "Serial", "Age", "Grade", "Chem", "Bio", "Math", "Phy"])
# 转object类型避免插入字符串X时报类型错误
df = df.astype(object)

group_keys = ["ID", "Serial"]
result_list = []

for (id_val, serial_val), group in df.groupby(group_keys, sort=False):
    # 初始化汇总行，分组字段保留原值，其余字段默认空
    summary = {col: id_val if col == "ID" else serial_val if col == "Serial" else "" for col in df.columns}
    # 逐列判断是否存在差异
    for col in df.columns:
        if col in group_keys:
            continue
        # 统计非空唯一值，如需将空值作为差异判断条件可删除.dropna()
        unique_cnt = len(group[col].dropna().unique())
        if unique_cnt > 1:
            summary[col] = "X"
    # 追加原分组和汇总行
    result_list.append(group)
    result_list.append(pd.DataFrame([summary]))

# 拼接得到最终结果
final_df = pd.concat(result_list, ignore_index=True)
# 输出查看效果
print(final_df.fillna("").to_string(index=False))

方案2：差异单元格高亮为红色 #

适合在Jupyter中展示或者导出带格式的Excel，无需修改原数据内容：

group_keys = ["ID", "Serial"]
def mark_diff(group):
    style_df = pd.DataFrame("", index=group.index, columns=group.columns)
    for col in group.columns:
        if col in group_keys:
            continue
        if len(group[col].dropna().unique()) > 1:
            style_df[col] = "color: red"
    return style_df

# 生成带样式的DataFrame
styled_df = df.style.apply(mark_diff, groupby=group_keys, axis=None)
# 如需导出到Excel可执行下面的代码
# styled_df.to_excel("diff_highlight_result.xlsx", index=False)

注意事项 #

• 分组参数sort=False保证输出顺序和原数据分组顺序一致
• 若需要将空值也纳入差异判断逻辑，删除代码中的.dropna()即可
• 方案1中如果你的原数据有纯数值列，转object类型的步骤不能省略

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