基于Pandas优化Excel单元格值迁移代码以提升性能

需求回顾 #

现有Excel表格需完成以下操作：

• 将第14行及以后的单元格内容，根据末尾匹配的STA ATT、TEC、D C P、A SPV标识，迁移至第10-13行的对应列位置
• 原OpenPyXL实现处理482列、8000行数据耗时15分钟，性能亟待优化
• @mozway提供的方法有性能改进方向，但需调整逻辑以正常运行

Pandas高效实现方案 #

Pandas基于矢量化运算，可避免OpenPyXL逐单元格循环的性能瓶颈，以下是修正后的优化代码：

1. 核心代码

import pandas as pd

# 读取Excel文件，header=None保留原始行结构（避免自动识别表头）
df = pd.read_excel("your_file.xlsx", header=None)

# 定义标识与目标行的映射（Excel行号10-13对应DataFrame 0基索引9-12）
id_to_target_row = {
    "STA ATT": 9,
    "TEC": 10,
    "D C P": 11,
    "A SPV": 12
}

# 提取第14行及以后的行（Excel行号14对应DataFrame索引13）
source_rows = df.loc[13:]

# 遍历标识，批量迁移数据
for identifier, target_row_idx in id_to_target_row.items():
    # 生成掩码：标记单元格内容以当前标识结尾的位置（处理NaN值）
    match_mask = source_rows.astype(str).apply(lambda col: col.str.endswith(identifier))
    # 提取每列最后一个匹配到的有效值（可根据需求改为ffill取第一个，或合并值）
    matched_values = source_rows.where(match_mask).bfill().iloc[-1]
    # 将匹配值写入目标行的对应列
    df.loc[target_row_idx, matched_values.index] = matched_values

# 保存处理后的Excel
df.to_excel("processed_file.xlsx", index=False, header=False)

2. 关键优化点

• 矢量化匹配：利用Pandas的str.endswith批量判断单元格内容，替代逐单元格循环
• 批量赋值：直接对目标行整列赋值，避免单个单元格写入的开销
• 灵活取值逻辑：示例中用bfill().iloc[-1]取每列最后一个匹配值，若需取第一个匹配值可改为ffill().iloc[0]，按需调整

3. 注意事项

• 若Excel包含多个工作表，需在read_excel中指定sheet_name参数
• 若单元格内容为数字与标识混合，astype(str)确保字符串匹配逻辑正常运行
• 若同一列存在多个匹配项，可根据业务需求调整取值规则（如合并、取最值等）

内容的提问来源于stack exchange，提问作者Davide C.