这是《学习记录》系列的第二篇。上一篇聊的是本地开发环境怎么搭这篇直接把成果摆出来——我用Python写了一个小脚本把散落在十几个子文件夹里的Excel表格自动合并、清洗、汇总成一张总表顺带产出一份数据质量报告。整个过程没花一分钱买工具只靠Python的pandas和openpyxl两个库。如果你也经常被“月底汇总门店数据”这种活儿折磨这篇文章应该能让你少走不少弯路。我会从需求梳理、代码实现写到踩坑记录适合有Python基础但还没实际写过完整项目的人参考。1. 学习目标与场景拆解1.1 这次想解决什么问题事情起源于我上一个项目积累的数据整理需求。业务方每月会收到十几个门店发来的Excel每张表记录着当月销售明细字段基本一致但又各有各的“小脾气”有的店在表头多留了两行有的日期列是文本有的金额列里混着“元”字还有的干脆把汇总行放在中间。手工操作的话我至少要花半天时间复制粘贴中途还要反复核对有没有串行。这其实是很典型的数据整理场景多源异构表格的汇聚。我当时给自己定的学习目标很明确写一个脚本能够扫描指定文件夹下的所有Excel自动识别每个表的字段过滤无关行标准化日期和金额去重后合并成一张总表。更重要的是它必须能告诉我“处理过程中哪些数据有问题”而不是闷头出结果。1.2 为什么选择Python而不是其他工具在动手之前我认真对比过Power Query、VBA和Python。Power Query适合交互式操作点几下鼠标就能合并但操作步骤保存在文件里换一批文件就得重新设置脚本化程度不够。VBA能实现完全自动化但调试体验实在糟糕而且只能处理Windows环境下的Excel换台电脑就可能跑不起来。Python的优势在于逻辑是显式的代码版本控制一目了然跑完还能顺手生成日志。pandas处理表格数据的生态非常成熟read_excel一句就能把Excel读成DataFrame后续的筛选、去重、聚合都是现成方法。另外它跨平台我在公司Windows上写的代码回家用Mac改一改也能跑。所以我选了Python这条路虽然前期写代码花了两天但之后每个月只需双击运行一次。2. 环境准备与工具选型解析2.1 用到的库和版本这个项目核心依赖并不多我列一下实际用到的版本方便你复现环境库名版本用途pandas2.1.4数据读取、清洗、合并openpyxl3.1.2读写.xlsx格式的Excel文件pathlibPython内置跨平台处理文件路径python-dateutil2.8.2日期解析pandas会自动依赖安装命令就是常规的pip install但我强烈建议先在项目目录下建一个虚拟环境。原因很简单pandas这种库依赖很多底层包如果直接装到全局后续其他项目用到不同版本的pandas会冲突得让人头大。我用的命令是python -m venv .venv .venv\Scripts\activate # Windows source .venv/bin/activate # macOS/Linux pip install pandas openpyxl这里有个细节值得说明openpyxl只是Excel的读写引擎之一pandas读取.xlsx时会自动识别。如果你想处理老的.xls文件通常还要装xlrd但xlrd 2.0以后只支持读取xls不支持xlsx别装错了。我这次的项目里既有xlsx也有xls所以环境里额外装了一份xlrd。2.2 项目目录结构设计代码还没开始写先把目录结构想好。这一步看似多余实际能省掉后期很多麻烦。我的项目目录长这样excel_merge/ ├── .venv/ # 虚拟环境 ├── data/ │ ├── raw/ # 放原始Excel │ │ ├── 门店A.xlsx │ │ ├── 门店B.xls │ │ └── ... │ ├── output/ # 输出汇总表 │ └── reports/ # 数据质量报告 ├── scripts/ │ └── merge_excel.py # 主脚本 └── logs/ └── run_log.txt # 运行日志把原始数据、输出结果、代码分开好处是哪怕脚本跑挂了原始文件不会被破坏。同时日志单独放一个目录出了问题可以追溯最后一次运行的情况。我在项目初期犯过“直接在raw文件夹里写结果”的错误有一次读取循环和输出循环搞混结果把汇总表写进了原始数据文件夹第二次运行脚本时汇总表也被当成输入读了一遍数据立刻翻倍。从此以后输入输出目录严格隔离这是花钱买不来的教训。3. 核心实操从脚本需求到第一版可用程序3.1 读取与合并多个Excel的逻辑第一步是遍历文件夹下的所有Excel文件逐个读取。这里的核心难点不是读取本身而是“每个表的结构不完全一致”。有的表字段名叫“销售日期”有的叫“日期”。为了让后续合并顺利我在代码里维护了一个字段映射字典把同义不同名的列统一起来。import pandas as pd from pathlib import Path raw_dir Path(data/raw) output_dir Path(data/output) reports_dir Path(data/reports) # 确保输出目录存在 output_dir.mkdir(parentsTrue, exist_okTrue) reports_dir.mkdir(parentsTrue, exist_okTrue) # 字段映射把不同门店的列名统一 field_alias { 销售日期: 日期, 交易日期: 日期, 日期时间: 日期, 商品名称: 商品, 产品名称: 商品, 销售数量: 数量, 成交数量: 数量, 销售金额: 金额, 成交金额: 金额, 门店编号: 门店, 店号: 门店, } all_frames [] file_list list(raw_dir.glob(*.xlsx)) list(raw_dir.glob(*.xls)) if not file_list: raise FileNotFoundError(f在 {raw_dir} 下没有找到Excel文件请检查文件是否放对位置。) for file in file_list: df pd.read_excel(file) # 统一列名 df.columns [field_alias.get(str(col), str(col)) for col in df.columns] df[来源文件] file.name all_frames.append(df)这段代码里嵌套了一个常见技巧读取前先明确“按列名映射字段”而不是假设所有表字段完全一样。因为现实中的Excel很少有100%统一的稍微残留一列不同名等到合并后再处理就会非常被动。接下来是合并。这里需要想清楚用concat还是merge。我刚开始总是混淆这两个方法后来总结出一个记忆方式concat是“竖着拼”或“横着拼”把多个表直接叠在一起适合行数变多的情况merge是“按关键列匹配”类似SQL的join适合表之间有关联字段的时候。我这次做的事情是汇总各门店的销售明细每行都是一个独立订单不需要关联其他表所以用concatresult pd.concat(all_frames, ignore_indexTrue)ignore_indexTrue的目的是让最终表的行号重新从0开始编号否则每个分表的原始索引保留下来打印结果时会出现一列突兀的旧索引看着难受后续筛选也容易出错。3.2 数据清洗的核心步骤合并完成后真正的脏活才开始。绝大多数Excel数据表不会干干净净等着你我这次遇到的典型问题有表头下方多出空行、金额列里带单位、日期被识别成文本、商品名称前后有空格、重复行。我用了几条代码逐个处理每一步都有明确目的。先去掉完全空白的行。有些门店的表在中间会留一个空行当作“美观”的间隔但这种空行对数据分析毫无意义result result.dropna(howall)接着处理日期列。如果Excel里日期是以“2024/11/3”这样的文本形式存在pandas默认读成字符串直接排序会出现“2024/11/3”排在“2024/10/15”前面的可笑结果。统一成datetime类型是最稳妥的方案result[日期] pd.to_datetime(result[日期], errorscoerce)errorscoerce表示遇到解析不了的日期就变成NaN而不是让整个程序崩溃。之后检查这些NaN要么手动修正原表要么在报告里标记出来这一步我会放到后面的校验报告中处理。金额列的处理则需要小心。有的门店填的是“1,280元”有的直接填“1280”还有的填“1280.00”。如果直接做类型转换带“元”的字符串会让转换报错。我的处理方式是先用字符串替换把非数字字符清掉再转成数值类型# 先去掉逗号和“元”再转成float result[金额] ( result[金额] .astype(str) .str.replace(,, ) .str.replace(元, ) .astype(float) )这里为什么用astype(str)先转字符串因为金额列里可能既有数字又有字符串直接调用str.replace在非字符串列上会报错。先统一转字符串再用字符串方法处理保证不会因为某一行是数字而卡住。去重也是个高频操作。同一个门店可能因为重复提交导致同一条记录出现两次。我的去重逻辑是基于所有业务字段判断而不是单单看“来源文件”列result result.drop_duplicates(subset[日期, 门店, 商品, 数量, 金额])如果连“金额”都一样那基本可以断定是重复数据了。但有的时候两个订单金额恰好相同且数量也相同这种误删情况很难完全避免所以我不会直接覆盖原始数据而是把去重结果输出到新表并且在报告里记录每个门店删除了多少行方便业务方人工抽查。3.3 输出汇总表与校验报告数据清洗完最终目标是把结果写回Excel。这里我不建议直接把DataFrame用to_excel导出因为实际业务中往往需要“一个工作簿里包含多个Sheet”比如汇总表一张、门店明细一张、数据质量报告一张。pandas的ExcelWriter可以一次性写入多个Sheet但要注意openpyxl引擎会覆盖整个文件如果多次打开同一个路径写入容易把前一个Sheet冲掉。我一般这样写from openpyxl import load_workbook output_path output_dir / 汇总销售明细.xlsx with pd.ExcelWriter(output_path, engineopenpyxl) as writer: result.to_excel(writer, sheet_name汇总数据, indexFalse) result[result[金额].isna()].to_excel( writer, sheet_name金额缺失记录, indexFalse )关键点是with上下文管理器。它会保证writer正确关闭文件落盘。我之前图省事写了两行单独的to_excel第一行写完文件已经存在第二行再打开时openpyxl默认会认为“追加模式”结果第二个Sheet莫名其妙写到别的文件里了。校验报告我选择用最简单直观的方式——直接统计关键指标写成文本文件方便打开即看report_lines [] report_lines.append(f原始文件数量{len(file_list)}) report_lines.append(f合并后总行数{len(result)}) report_lines.append(f缺失日期行数{result[日期].isna().sum()}) report_lines.append(f缺失金额行数{result[金额].isna().sum()}) report_lines.append(f重复行数去重前{len(result) - len(result.drop_duplicates(subset[日期, 门店, 商品, 数量, 金额])}) report_path reports_dir / check_report.txt report_path.write_text(\n.join(report_lines), encodingutf-8)这份报告不需要多复杂但非常有用。它让我不用打开Excel就能判断脚本运行后数据质量大概是什么水平比如缺失金额的行是否有异常增长如果某个月突然多出几百条缺失记录很可能说明门店的新模板又改了字段名这时就该去查原表了。4. 实战中的坑与排查方法4.1 路径与编码新手最容易翻车的地方第一个坑来自Windows系统。因为门店同事发来的文件名里带着中文比如“华东区-11月-新.xlsx”Python字符串本身没问题但我在某些老旧代码里习惯性地用data\\raw\\ filename手动拼接路径一旦脚本在Mac上运行反斜杠路径就会直接报错。后来我全改成pathlib的Path对象写法统一为raw_dir / file_name跨平台瞬间省心。编码问题则更隐蔽。我在写校验报告时直接把中文字符串写入txtWindows下用记事本打开是乱码原因在于Python写入时默认用的是UTF-8而Windows记事本默认以GBK读取。解决办法是写文本时显式声明编码report_path.write_text(\n.join(report_lines), encodingutf-8-sig)utf-8-sig会在文件开头加一个BOM头记事本就能正确识别UTF-8编码。这个小细节如果不处理报告生成之后同事反馈“全是乱码”排查半天也找不到原因。4.2 pandas版本与Excel引擎兼容pandas不同版本的行为差异真是让人防不胜防。我最早写这个脚本时用的是pandas 1.5当时pd.read_excel读取xlsx默认用的是xlrd引擎xls和xlsx都读。后来升级到pandas 2.xxlrd不再支持xlsx必须显式指定openpyxl。如果你遇到报错ValueError: Excel file format cannot be determined, you must specify an engine manually多半就是引擎匹配问题。我的处理方式是在read_excel里显式传引擎if file.suffix .xlsx: df pd.read_excel(file, engineopenpyxl) else: df pd.read_excel(file, enginexlrd)这样做虽然多写了两个分支但避免了pandas自动猜测时出错的可能。另外openpyxl读大文件几万行数据时内存占用很高如果你发现脚本运行到一半机器卡死可以先试试只读不加载公式df pd.read_excel(file, engineopenpyxl, data_onlyTrue)data_onlyTrue会跳过Excel里的公式计算直接读取缓存值速度能快不少。但它有个副作用如果单元格里只有公式而没保存过结果读出来会是空值所以要在报告里留意缺失情况。4.3 数据本身带来的暗坑数据问题是最让人头疼的最典型的是Excel表格里有合并单元格。pandas读取合并单元格时通常会把合并区域里除左上角外的所有单元格读成NaN。比如一个门店表里“门店名称”一列一行合并了十行那么后面九行都是空值。这种问题在报表类Excel里极其常见。我的应对思路是不要试图在pandas里自动填充分合并单元格那太绕了。最实际的办法是在数据读取后做一次前向填充result[门店] result[门店].fillna(methodffill)ffill的意思是“用上一个非空值填充当前空值”前提是空值所在的列按顺序排列且前面的确存在可以填充的值。这个操作必须在其他清洗步骤之前做否则一旦你先把空值行删掉了合并单元格的信息也就全丢了。另一个暗坑是日期列以数字形式存储。Excel里如果单元格格式不是“日期”而是“常规”你看到的可能是45000这样的数字实际它是1900年以来的天数。pandas会把看起来像日期的数值自动识别成datetime类型但也有识别不了的时候。遇到这种问题我会先用result.dtypes打印所有列的类型再用pd.to_datetime(result[日期], unitD, origin1899-12-30)做显式转换Excel与pandas的日期起点差异就在这里用错了日期会莫名其妙差几十年。最后列名里藏着空格的问题也值得单独记录。某个门店的表标题叫“商品名称”但后面多了一个空格成了“商品名称 ”肉眼看不出来。字段映射匹配时因为多了空格没对齐这一列就会变成独立的未映射字段后续合并结果里就会出现“商品”和“商品名称”两列并存的情况。我排查了一小时才发现是多个空格在捣乱。后来我在读取后统一对列名做strip处理df.columns [str(col).strip() for col in df.columns]这个防御性操作建议放在任何真实项目的read_excel之后成本极低收益却很实在。5. 学习复盘与后续计划这第二篇学习记录写到这里最想强调的其实不是语法或函数本身而是一种处理表格数据的思维习惯先看清楚数据结构再动代码。我在这个项目里最大的收获是把日常重复的“打开Excel、查找、复制、粘贴”操作抽象成了“读取—清洗—校验—输出”四个环节。这个流程不仅适用于Excel任何表格类数据都通用掌握之后你会发现很多手工活儿都有了自动化的可能性。具体到工具层面pandas的DataFrame就像一个工作台你不需要太长时间就能把十几张表摆在一起处理。openpyxl则更像“翻译官”负责把Excel格式的细节处理好。我可以负责任地说花两天时间学这两个工具的常用操作远比每个月月底花半天手工汇总来得划算。基于这次经验我接下来的学习计划很明确一是给脚本加上简单的GUI让不熟悉命令行的同事也能双击选择文件夹使用二是把运行日志从纯文本改成结构化格式比如JSON方便后续统计脚本执行情况三是研究一下怎么把这份逻辑部署成定时任务每月自动运行并发送结果邮件。到时候应该会有新的坑可以记录等第三篇学习记录出来再和大家聊。