Python pandas批量处理多个Excel指定工作表并分列

脚本专家

Excel 数据清洗中，把多个工作簿里指定工作表的一列拆成多列，是很典型的批量自动化任务。原始列常见格式如 张三*13800000000*销售，需要拆成 姓名、手机号、部门 三列。手工在 Excel 里点分列，文件一多就不现实。用 Python pandas 可以把 split 拆列、drop 删除原混合列、concat 拼回新列串成稳定流程。

一、核心逻辑：split、drop、concat
核心代码只有四句：

2. split_df = df[split_col].astype(str).str.split(sep, expand=True)
3. split_df.columns = new_cols
4. df = df.drop(columns=[split_col])
5. df = pd.concat([df, split_df], axis=1)

复制代码

split 负责把目标列拆成多列；drop 删除原来的混合列，避免重复信息；concat 用 axis=1 按列拼接，把新字段横向拼回原表。astype(str) 是为了统一类型，expand=True 让拆分结果直接变成 DataFrame；如果 expand=False，每个单元格得到的是列表，不适合直接写出 Excel。

二、完整批量脚本
实际使用时只要改输入目录、工作表名、目标列、分隔符、新列名和输出目录。脚本会跳过 Excel 临时文件，只处理常见 Excel 扩展名，并在缺少目标列时跳过。

2. import os
3. import pandas as pd
5. folder_path = r'e:\\file\\target'
6. sheet_name = 'Sheet1'
7. split_col = '信息'
8. sep = '*'
9. new_cols = ['姓名', '手机号', '部门']
10. out_folder = r'e:\\file\\split_out'
12. os.makedirs(out_folder, exist_ok=True)
14. for file in os.listdir(folder_path):
15.     if file.startswith('~$'):
16.         continue
17.     if not file.lower().endswith(('.xlsx', '.xls', '.xlsm')):
18.         continue
20.     full_path = os.path.join(folder_path, file)
21.     df = pd.read_excel(full_path, sheet_name=sheet_name)
23.     if split_col not in df.columns:
24.         print(f'跳过：{file}，缺少列：{split_col}')
25.         continue
27.     split_df = df[split_col].astype(str).str.split(sep, expand=True, regex=False)
28.     split_df.columns = new_cols
30.     df = df.drop(columns=[split_col])
31.     df = pd.concat([df, split_df], axis=1)
33.     out_path = os.path.join(out_folder, file)
34.     df.to_excel(out_path, index=False)
35.     print(f'分列完成：{file} -> {out_path}')

复制代码

这里 regex=False 是针对 *、|、. 这类正则特殊字符的注意点。原文提到，在较新的 pandas 版本中，str.split() 参数涉及正则解释；如果分隔符只是普通字符，建议明确设置 regex=False，避免被当作正则符号处理。

三、代码参数与设计细节
folder_path 是原始 Excel 文件夹，sheet_name 指定要处理的工作表，split_col 是要拆分的列，sep 是分隔符，new_cols 是拆分后的新列名，out_folder 是输出目录。
文件遍历阶段做两层过滤：file.startswith('~$') 跳过 Excel 临时文件；file.lower().endswith(('.xlsx', '.xls', '.xlsm')) 只处理 Excel 工作簿。读取后用 if split_col not in df.columns 检查目标列，缺少列时打印跳过原因并 continue，避免整批任务因单个文件中断。
df.drop(columns=[split_col]) 删除原列，pd.concat([df, split_df], axis=1) 按列拼接新字段。axis=1 表示横向扩展；如果写成 axis=0，就变成按行拼接，不符合分列需求。
输出建议写到 split_out 新目录，不直接覆盖原文件。批量数据清洗一旦逻辑出错，覆盖原文件会让回退变麻烦。原文件只读、结果另存，是批量脚本基本的安全边界。

四、常见报错与排查
1. 拆分后的列数和新列名数量不一致
最常见。设置了 ['姓名', '手机号', '部门'] 三个新列名，但某些单元格只能拆出两段或四段，就会列数不匹配。这不是 split 语法问题，而是源数据不规范。可以先查看实际拆出来多少列：

2. split_df = df[split_col].astype(str).str.split(sep, expand=True, regex=False)
3. print(split_df.shape)

复制代码

数据源不稳定时，不要假设每一行都符合格式，应在脚本里增加列数校验。
2. 空值被转成字符串 nan
astype(str) 后，空白单元格可能变成字符串 'nan'。如果业务上希望空值保持为空，可以在拆分前填充：

2. df[split_col] = df[split_col].fillna('')
3. split_df = df[split_col].astype(str).str.split(sep, expand=True, regex=False)

复制代码

如果空值代表异常数据，则应单独记录并提示，而不是直接填空。
3. 分隔符是正则特殊字符
*、|、. 等字符在正则里有特殊含义。明确传入 regex=False 能减少歧义。
4. 不建议覆盖原文件
分列、删除原列、重新写出的操作会改变数据结构，直接覆盖风险高。推荐输出到新目录后再核对。

五、效果验证与日志
脚本不报错不代表结果正确。至少验证三件事：输出文件数量、目标列是否已经拆开、拆分后的字段是否和原始信息对应。
检查输出目录文件数量：

2. import os
4. out_folder = r'e:\\file\\split_out'
5. files = [f for f in os.listdir(out_folder) if f.lower().endswith(('.xlsx', '.xls', '.xlsm'))]
6. print(f'输出文件数量：{len(files)}')

复制代码

读取一个输出文件检查字段：

2. import pandas as pd
4. test_file = r'e:\\file\\split_out\\示例.xlsx'
5. df = pd.read_excel(test_file)
6. print(df.columns.tolist())
7. print(df.head())

复制代码

批量处理多个来源文件时，最好抽查 2 到 3 个输出文件，因为不同文件的数据格式可能有细微差异。还可以在循环中记录处理日志：

2. log_list = []
3. log_list.append({
4.     '文件名': file,
5.     '处理结果': '成功',
6.     '拆分列': split_col,
7.     '输出路径': out_path
8. })

复制代码

对办公自动化脚本来说，日志就是证据链，能说明哪些文件处理过、哪些跳过、为什么跳过。

六、进阶：一列拆成多行
前面是把一列横向拆成多列。如果想把一列里的多个值纵向展开成明细行，可以用 split 后接 stack。示例：

2. import pandas as pd
4. df = pd.DataFrame({
5.     '信息': [
6.         '张三*13800000000*销售',
7.         '李四*13900000000*财务',
8.         '王五*13700000000*市场'
9.     ]
10. })
12. split_df = df['信息'].astype(str).str.split('*', expand=True, regex=False)
13. long_df = split_df.stack().reset_index()
14. long_df.columns = ['原行号', '拆分序号', '拆分值']
15. print(long_df)

复制代码

横向拆列会增加列数，纵向拆行会增加行数。要做明细化统计、标签展开、关键词拆分时，一列拆多行更合适。

总结
可复用的 pandas Excel 分列模板，重点不是只记 str.split()，而是把读取指定工作表、定位目标列、split 拆列、drop 删除原列、concat 拼回新列、输出新文件、验证结果串成完整流程。脚本升级时优先增加异常日志、列数校验和结果汇总表。处理正式文件前，先复制样本测试，确认分列结果、字段顺序和输出文件都正确，再扩大范围。