Pandas批量处理Excel：从文件名提取RTO、所属州、年份为数据列

适配文件名规则的正则匹配逻辑 #

针对统一命名的文件结构，直接使用以下正则表达式即可稳定提取三类字段，无需手动拆分字符串：

filename_pattern = re.compile(
    r'^Maker Month Wise Data\s+of\s+(?P<rto>[^,]+),\s+(?P<state>[^(]+)\((?P<year>\d{4})\)\.xlsx$'
)

各匹配段的对应关系：

• ^Maker Month Wise Data\s+of\s+：匹配文件名开头固定的前缀内容，\s+可自动适配原文中出现的多空格情况（比如样例里“Data”和“of”之间的两个空格）
• (?P<rto>[^,]+)：命名捕获组，匹配第一个逗号前的全部内容，正好对应需要提取的RTO字段
• ,\s+(?P<state>[^(]+)：匹配逗号之后、左括号之前的全部内容，对应当前文件所属的州
• \((?P<year>\d{4})\)\.xlsx$：匹配文件名末尾括号内的4位数字年份，以及固定的.xlsx后缀

单文件调试阶段的完整代码 #

将提取逻辑嵌入现有清洗流程，可直接运行：

import pandas as pd
import os
import re

# 初始化正则匹配规则
filename_pattern = re.compile(
    r'^Maker Month Wise Data\s+of\s+(?P<rto>[^,]+),\s+(?P<state>[^(]+)\((?P<year>\d{4})\)\.xlsx$'
)

userhome = os.path.expanduser('~/')
path_to_file = userhome + 'Downloads/arunachal-pradesh/'
file_name = 'Maker Month Wise Data  of WEST KAMENG - AR4 , Arunachal Pradesh (2020).xlsx'

# 从文件名提取三类字段，加容错逻辑避免格式异常中断
match_result = filename_pattern.match(file_name)
if not match_result:
    raise ValueError(f"文件名格式不匹配，无法提取字段：{file_name}")
# 提取字段并自动去除多余空格
rto = match_result.group('rto').strip()
state = match_result.group('state').strip()
year = match_result.group('year').strip()

# 原有单文件清洗逻辑
df = pd.read_excel(path_to_file + file_name)
df.columns = df.iloc[2]
df = df.drop(df.index[0:3])
df = df.iloc[:,1:14]
df.rename(columns={df.columns[0]: "Maker"}, inplace=True)

# 将提取的字段作为独立列加入DataFrame
df['RTO'] = rto
df['State'] = state
df['Year'] = year

用样例文件测试时，提取到的字段值分别为：

• RTO: WEST KAMENG - AR4
• State: Arunachal Pradesh
• Year: 2020
  完全符合预期。

批量处理阶段的适配参考 #

等单文件逻辑验证通过后，可直接扩展为批量遍历处理全量文件的流程，最后合并导出成可直接导入可视化工具的总表：

all_processed_dfs = []
# 遍历目标目录下所有xlsx文件
for cur_file in os.listdir(path_to_file):
    if not cur_file.endswith('.xlsx'):
        continue
    # 复用匹配逻辑
    match_result = filename_pattern.match(cur_file)
    if not match_result:
        print(f"跳过格式异常的文件：{cur_file}")
        continue
    rto = match_result.group('rto').strip()
    state = match_result.group('state').strip()
    year = match_result.group('year').strip()

    # 复用原有清洗逻辑
    cur_df = pd.read_excel(os.path.join(path_to_file, cur_file))
    cur_df.columns = cur_df.iloc[2]
    cur_df = cur_df.drop(cur_df.index[0:3])
    cur_df = cur_df.iloc[:,1:14]
    cur_df.rename(columns={cur_df.columns[0]: "Maker"}, inplace=True)

    # 追加三个字段列
    cur_df['RTO'] = rto
    cur_df['State'] = state
    cur_df['Year'] = year

    all_processed_dfs.append(cur_df)

# 合并所有处理后的数据，导出兼容可视化工具的CSV文件
final_df = pd.concat(all_processed_dfs, ignore_index=True)
# 用utf-8-sig编码避免可视化工具打开时出现中文乱码
final_df.to_csv('processed_maker_monthly_data.csv', index=False, encoding='utf-8-sig')

注意事项 #

• 正则采用命名捕获组的写法，无需记忆捕获组顺序，后续调整规则时代码可读性更高
• 所有提取的字段都加了strip()处理，可自动清除文件名中残留的首尾多余空格，避免后续统计时出现同字段值不匹配的问题
• 流程中加入了格式校验的容错逻辑，不会因为个别文件名格式不规范就中断整个批量任务，可自动跳过异常文件并输出提示

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