Python/Pandas合并月度薪资报表并生成差异列及差异报告需求

1. 预处理：统一列名，解决月度列头不一致问题 #

先处理月度报表列名大小写、空格、位置不一致的问题，通过标准化列名确保合并匹配准确：

import pandas as pd
from openpyxl import load_workbook

# 列名标准化函数：统一转小写、替换空格为下划线
def standardize_columns(df):
    df.columns = df.columns.str.lower().str.replace(' ', '_')
    return df

# 读取并标准化两份薪资报表
dfnew = standardize_columns(pd.read_csv("sep22.csv"))
dfold = standardize_columns(pd.read_csv("Aug22.csv"))

# 以标准化后的员工编码为键合并，保留当月所有员工数据
mergedfiles = pd.merge(dfnew, dfold, on='employee_code', how='left')

# 设置员工编码为索引，按列名排序方便查看（可选）
mergedfiles = mergedfiles.set_index('employee_code').sort_index(axis=1)

2. 自动生成差异列（硬编码数值/Excel公式二选一） #

选项A：硬编码差异数值（直接计算结果） #

遍历所有配对列，自动生成_diff列并插入到对应_x/_y列之后：

# 获取所有配对列的原始名称（去除_x/_y后缀）
original_cols = list(set(col.split('_')[0] for col in mergedfiles.columns if '_' in col))

for col in original_cols:
    # 检查当月和上月列是否同时存在
    if f"{col}_x" in mergedfiles.columns and f"{col}_y" in mergedfiles.columns:
        # 计算差异：当月值 - 上月值
        mergedfiles[f"{col}_diff"] = mergedfiles[f"{col}_x"] - mergedfiles[f"{col}_y"]
        # 将差异列插入到_y列的下一个位置
        col_index = mergedfiles.columns.get_loc(f"{col}_y") + 1
        mergedfiles = mergedfiles.reindex(
            columns=list(mergedfiles.columns[:col_index]) + [f"{col}_diff"] + list(mergedfiles.columns[col_index:-1])
        )

# 保存含差异列的文件
mergedfiles.to_excel("new_v_old_with_diff.xlsx")

选项B：插入Excel公式（保留动态计算能力） #

使用openpyxl引擎写入Excel公式，后续修改原始值时差异会自动更新：

# 先保存基础合并文件
mergedfiles.to_excel("new_v_old_with_formula.xlsx", engine='openpyxl')

# 加载工作簿并写入公式
wb = load_workbook("new_v_old_with_formula.xlsx")
ws = wb.active

# 建立列名与Excel列号的映射（如A、B、C...）
col_map = {cell.value: chr(65 + idx) for idx, cell in enumerate(ws[1])}

for col in original_cols:
    if f"{col}_x" in mergedfiles.columns and f"{col}_y" in mergedfiles.columns:
        # 获取_x和_y列对应的Excel列字母
        x_col = col_map[f"{col}_x"]
        y_col = col_map[f"{col}_y"]
        # 计算差异列的位置（在_y列之后）
        diff_col_idx = list(col_map.values()).index(y_col) + 1
        diff_col_letter = chr(65 + diff_col_idx)
        
        # 写入差异列标题
        ws[f"{diff_col_letter}1"] = f"{col}_diff"
        
        # 逐行写入差异公式
        for row in range(2, ws.max_row + 1):
            ws[f"{diff_col_letter}{row}"] = f"={x_col}{row}-{y_col}{row}"

# 保存带公式的文件
wb.save("new_v_old_with_formula.xlsx")

3. 生成仅含差异数据的报告 #

筛选出有差异的记录，按薪资项分组整理成指定格式：

# 生成差异标记矩阵：标记差异不为0且非空的单元格
diff_mask = mergedfiles.filter(like='_diff').ne(0) & mergedfiles.filter(like='_diff').notna()

# 整理差异报告内容
report_content = []
for col in original_cols:
    diff_col = f"{col}_diff"
    if diff_col in diff_mask.columns:
        # 获取该薪资项有差异的员工
        diff_emp_ids = diff_mask[diff_col][diff_mask[diff_col]].index
        if len(diff_emp_ids) > 0:
            # 转换回中文列名格式（如salary -> 薪资）
            item_name = col.replace('_', ' ')
            report_content.append(f"> {item_name}:")
            for emp_id in diff_emp_ids:
                x_val = mergedfiles.loc[emp_id, f"{col}_x"]
                y_val = mergedfiles.loc[emp_id, f"{col}_y"]
                diff_val = mergedfiles.loc[emp_id, diff_col]
                # 获取员工姓氏（假设列名为last_name，可根据实际调整）
                last_name = mergedfiles.loc[emp_id, 'last_name']
                report_content.append(f"> {last_name} {x_val}; {y_val}; {diff_val}")
            report_content.append("> ")

# 在Jupyter中输出报告
print('\n'.join(report_content))

# 保存报告到文本文件
with open("payroll_diff_report.txt", 'w') as f:
    f.write('\n'.join(report_content))

关键说明 #

• 列名标准化：通过统一格式消除月度报表列名的格式差异，避免合并时匹配错误。
• 差异列排版：自动将差异列插入对应_x/_y列之后，保持数据逻辑连贯。
• 差异报告：仅提取有变化的薪资项和员工，减少冗余信息，提升审核效率。

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