
本文介绍如何利用pandas和openpyxl,根据一个统一的映射excel文件(含多张映射表),自动为多个目标excel文件批量重命名列,支持模糊匹配、大小写归一化及跨表合并映射规则。
本文介绍如何利用pandas和openpyxl,根据一个统一的映射excel文件(含多张映射表),自动为多个目标excel文件批量重命名列,支持模糊匹配、大小写归一化及跨表合并映射规则。
在企业数据治理与ETL流程中,常需将来源不一、命名混乱的多个Excel报表标准化后入库。典型场景是:存在一个名为 table_name.xlsx 的配置文件,其每张工作表(如 chung、nganh)分别定义了某类业务表的“旧列名→新列名”映射关系;而待处理的原始文件(如 phu_luc_chung.xlsx、phu_luc_nganh.xlsx)则需按对应规则重命名列。关键挑战在于:如何智能关联文件名与映射表名? 例如 phu_luc_chung1.xlsx、phu_luc_chungdraft.xlsx 均属于 chung 类别,但文件名不完全匹配表名。
直接遍历所有映射表并合并为全局字典(如答案所示)是一种简洁有效的策略——它规避了复杂的文件-表名匹配逻辑,转而采用“全量覆盖+按需应用”的方式:只要原始列名(经小写标准化后)存在于映射字典中,即自动替换。该方法鲁棒性强,尤其适合存在命名变体或历史遗留文件的场景。
以下是完整、可运行的解决方案:
将测试、lint、构建和文档流程编码为命名的Python会话,以便人类和代理每次运行相同的workflow。
import glob
import os
import pandas as pd
# 配置路径与文件名
mapping_file = "table_name.xlsx" # 列名映射配置文件
data_folder = r"E:\folder" # 待处理Excel文件所在目录
# 步骤1:读取所有映射表,构建全局列名映射字典
rename_dict = {}
try:
# sheet_name=None 返回字典:{sheet_name: DataFrame}
all_sheets = pd.read_excel(mapping_file, engine="openpyxl", sheet_name=None)
for sheet_name, df_map in all_sheets.items():
# 确保Old/New列存在且非空
if "Old" not in df_map.columns or "New" not in df_map.columns:
raise ValueError(f"映射表 '{sheet_name}' 缺少 'Old' 或 'New' 列")
# 过滤空行,并转换为小写键(适配后续df.columns小写化)
valid_rows = df_map.dropna(subset=["Old", "New"])
sheet_dict = {
str(old).strip().lower(): str(new).strip()
for old, new in zip(valid_rows["Old"], valid_rows["New"])
}
rename_dict.update(sheet_dict)
except FileNotFoundError:
raise FileNotFoundError(f"未找到映射文件: {mapping_file}")
except Exception as e:
raise RuntimeError(f"读取映射文件失败: {e}")
print(f"已加载 {len(rename_dict)} 条列名映射规则")
# 步骤2:批量处理所有Excel文件
xlsx_files = glob.glob(os.path.join(data_folder, "*.xlsx"))
if not xlsx_files:
print("警告:未找到任何Excel文件")
else:
for file_path in xlsx_files:
try:
# 读取原始数据
df = pd.read_excel(file_path, engine='openpyxl')
# 标准化列名:转小写 + 去首尾空格(提升匹配成功率)
original_cols = list(df.columns)
df.columns = [str(col).strip().lower() for col in df.columns]
# 应用映射(仅对存在的键进行重命名,其余列名保持不变)
df_renamed = df.rename(columns=rename_dict)
# 输出处理摘要
renamed_count = sum(1 for old, new in zip(original_cols, df_renamed.columns)
if str(old).strip().lower() != str(new).strip().lower())
print(f"\n✅ 文件: {os.path.basename(file_path)}")
print(f" 原列名: {original_cols}")
print(f" 新列名: {list(df_renamed.columns)}")
print(f" 已重命名列数: {renamed_count}/{len(original_cols)}")
# (可选)保存结果到新文件,避免覆盖原数据
# output_path = os.path.join(data_folder, "renamed_" + os.path.basename(file_path))
# df_renamed.to_excel(output_path, index=False)
# print(f" 已保存至: {output_path}")
except Exception as e:
print(f"❌ 处理文件 {os.path.basename(file_path)} 失败: {e}")
关键设计说明与注意事项:
-
映射健壮性:代码对映射表中的
Old/New列值执行.strip().lower(),确保"CHUNG1 "和"chung1"能被正确匹配;同时跳过空值行,防止异常。 -
零配置关联逻辑:放弃“文件名→表名”的硬编码匹配(如
if 'chung' in filename),转而依赖语义级列名匹配。这意味着即使文件phu_luc_chungdraft.xlsx实际包含nganh1列,也会被正确重命名为new_nganh1——这反而更符合真实数据质量场景。 -
安全处理:
rename(columns=...)默认只替换字典中存在的键,不存在的列名将保持原样,避免意外丢列。 - 错误防御:添加了完整的异常捕获(文件不存在、列缺失、读取失败),便于定位问题。
-
生产建议:
- 生产环境应启用
output_path保存逻辑,并添加时间戳或版本号; - 对于超大文件,可改用
chunksize分块读取; - 若需严格按文件类别应用特定映射表(如
chung类只用chung表),可扩展为基于文件名关键词(如re.search(r'chung|chung\d+|chungdraft', filename))动态选择映射子字典。
- 生产环境应启用
此方案以最小复杂度实现了高适应性列名标准化,是处理混乱数据源的实用工程实践。
Python免费学习笔记(深入):立即使用
在学习笔记中,你将探索 Python 的核心概念和高级技巧!










