不同形状DataFrame合并后高亮差异行与变更单元格并导出Excel

场景与需求 #

现有两个结构不同的DataFrame：

• dfcurr（757行26列）：当前版本数据
• dfprev（688行39列）：历史版本数据，已过滤出Subject包含"M"或"S"的行，且前26列列名与dfcurr完全一致

需要实现：

1. 合并两个DataFrame，保留dfcurr的全部内容
2. 按以下规则高亮导出Excel：
   • 整行高亮#DAEEF3：当某行的Subject、Visit、Visit Date组合在dfprev中不存在时
   • 单元格高亮#E4DFEC：当行标识（Subject/Visit/Visit Date）在dfprev中存在，但该单元格内容与dfprev对应位置不一致时

之前的尝试问题 #

尝试1：未成功应用样式 #

最初通过openpyxl手动写入数据，但样式完全失效，核心问题是调用.style.apply()后直接取.data获取原始数据，丢失了样式信息：

import pandas as pd
import numpy as np
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook
import pandas.io.formats.style as style

dfcurr=pd.read_excel(r'IPH4201P2 - DRATracker_Current.xlsx')
dfprev=pd.read_excel(r'IPH4201P2 - DRATracker_Previous.xlsx')

dfprev=dfprev.loc[(dfprev['Subject'].str.contains('M'))|(dfprev['Subject'].str.contains('S'))]
dfprev=dfprev.reset_index(drop=True)

df_diff=pd.merge(dfcurr,dfprev,how='left',indicator=True)

common_columns = df_diff.columns.intersection(dfprev.columns)
compare_df = df_diff[common_columns].eq(dfprev[common_columns])

# 转换为字符串进一步破坏了样式匹配逻辑
df_diff = df_diff.astype(str)

def highlight_diff(data, compare):
    if type(data) != pd.DataFrame:
        data = pd.DataFrame(data)
    if type(compare) != pd.DataFrame:
        compare = pd.DataFrame(compare)
    result = []
    for col in data.columns:
        if col in compare.columns and (data[col] != compare[col]).any():
            result.append('background-color: #DAEEF3')
        elif col not in compare.columns:
            result.append('background-color: #E4DFEC')
        else:
            result.append('background-color: white')
    return result

wb = Workbook()
ws = wb.active
# 错误：仅写入原始数据，丢失样式
for r in dataframe_to_rows(df_diff.style.apply(highlight_diff, compare=compare_df).data, index=False, header=True):
    ws.append(r)

wb.save('Merged_style.xlsx')

尝试2：样式导出成功但不符合需求 #

参考外部方法实现了样式导出，但仅能标记单元格差异，未处理整行高亮需求，且颜色规则不匹配：

import pandas as pd
import numpy as np
import openpyxl
import pandas.io.formats.style as style

dfcurr=pd.read_excel(r'IPH4201P2 - DRATracker_Current.xlsx')
dfprev=pd.read_excel(r'IPH4201P2 - DRATracker_Previous.xlsx')

dfprev=dfprev.loc[(dfprev['Subject'].str.contains('M'))|(dfprev['Subject'].str.contains('S'))]
dfprev=dfprev.reset_index(drop=True)

new='background-color: #DAEEF3'
change='background-color: #E4DFEC'

df_diff=pd.merge(dfcurr,dfprev,on=['Subject','Visit','Visit Date','Site\nID','Cohort','Pathology','Clinical\nStage At\nScreening','TNMBA at\nScreening'],how='left',indicator=True)

for col in df_diff.columns:
    if '_y' in col:
        del df_diff[col]
    elif 'Unnamed: 1' in col:
        del df_diff[col]
    elif '_x' in col:
        df_diff.columns=df_diff.columns.str.rstrip('_x')

def highlight_diff(data, other, color='#DAEEF3'):
    attr = 'background-color: {}'.format(color)
    return pd.DataFrame(np.where(data.ne(other), attr, ''),
                        index=data.index, columns=data.columns)

df_diff=df_diff.style.apply(highlight_diff, axis=None, other=dfprev)
df_diff.to_excel('Diff.xlsx',engine='openpyxl',index=0)

修改后的解决方案 #

通过两次样式叠加实现需求：先标记新行的整行高亮，再标记匹配行的差异单元格：

import pandas as pd
import numpy as np

# 读取并预处理数据
dfcurr = pd.read_excel(r'IPH4201P2 - DRATracker_Current.xlsx')
dfprev = pd.read_excel(r'IPH4201P2 - DRATracker_Previous.xlsx')
dfprev = dfprev.loc[(dfprev['Subject'].str.contains('M')) | (dfprev['Subject'].str.contains('S'))]
dfprev = dfprev.reset_index(drop=True)

# 以核心标识为键合并数据，保留dfcurr全部内容
merge_keys = ['Subject', 'Visit', 'Visit Date']
df_diff = pd.merge(dfcurr, dfprev, on=merge_keys, how='left', suffixes=('_curr', '_prev'), indicator=True)

# 清理合并后冗余列：保留dfcurr原始列，删除dfprev对应列
for col in df_diff.columns:
    if '_prev' in col:
        df_diff.drop(col, axis=1, inplace=True)
    elif '_curr' in col:
        df_diff.rename(columns={col: col.rstrip('_curr')}, inplace=True)

# 标记新行：判断当前行标识是否在dfprev中存在
df_prev_keys = dfprev[merge_keys].drop_duplicates()
is_new_row = ~df_diff[merge_keys].apply(tuple, axis=1).isin(df_prev_keys.apply(tuple, axis=1))

# 标记差异单元格：仅对非新行，对比与dfprev的内容差异
common_cols = dfcurr.columns.intersection(dfprev.columns)
df_compare = pd.merge(df_diff[merge_keys + list(common_cols)], 
                      dfprev[merge_keys + list(common_cols)], 
                      on=merge_keys, how='left', suffixes=('_curr', '_prev'))

# 生成差异掩码
diff_mask = pd.DataFrame()
for col in common_cols:
    diff_mask[col] = df_compare[f'{col}_curr'] != df_compare[f'{col}_prev']
diff_mask = diff_mask & ~is_new_row.values.reshape(-1, 1)

def highlight_rules(row):
    # 新行整行高亮
    if is_new_row[row.name]:
        return ['background-color: #DAEEF3'] * len(row)
    # 非新行仅高亮差异单元格
    styles = []
    for col in row.index:
        if col in diff_mask.columns and diff_mask.loc[row.name, col]:
            styles.append('background-color: #E4DFEC')
        else:
            styles.append('')
    return styles

# 应用样式并导出
styled_df = df_diff.style.apply(highlight_rules, axis=1)
styled_df.to_excel('Merged_Highlighted.xlsx', engine='openpyxl', index=False)

代码说明 #

1. 合并与列清理：以Subject、Visit、Visit Date为唯一标识左连接，清理合并产生的冗余列，保留dfcurr原始结构
2. 新行标记：通过对比行标识组合，精准识别dfcurr独有的行
3. 差异单元格标记：仅对已匹配的行，逐列对比与dfprev的内容差异，生成差异掩码
4. 样式叠加：通过apply(axis=1)逐行处理，先判断新行应用整行高亮，再对非新行的差异单元格应用高亮

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