17 份异构销售数据的合并清洗方案30 万行处理耗时 9.64 秒月底汇总时各分公司提交的 17 份销售表格式各异文件类型涵盖.xls、.xlsx、.csv三种同一字段存在 9 种命名方式日期格式亦不统一。采用人工方式合并单次耗时约两小时且难以核验输出结果的完整性。采用脚本处理后全流程耗时 9.64 秒且每一行数据的取舍均可追溯。本文记录该方案的实现过程以及开发阶段遇到的 6 个问题。一、问题背景与处理结果需求将 17 份销售数据合并为一张可直接用于透视分析的标准总表。人工处理方式通常为逐文件复制粘贴、人工核对字段名、手动调整日期格式。面对 30 万行规模的数据不仅耗时较长且输出结果的完整性缺乏有效核验手段。程序化处理结果如下读入 301,564 行输出 263,454 行处理耗时 9.64 秒字段命名自动统一、日期格式自动归一、缺失数据与重复数据自动剔除全流程打印处理明细各项数字均可追溯最终执行数字自洽校验263454 37947 163 301564校验不通过则程序终止并拒绝输出结果运行环境Python 3.14 / pandas 3.0.6 / Windows兼容范围Python 3.9 及以上、pandas 2.2 及以上已在两套环境实测通过Python 3.14 pandas 3.0.6、Python 3.9 pandas 2.2依赖清单pandas2.2 charset-normalizer3.0 # CSV 编码识别 xlrd2.0 # 读取老式 .xls xlsxwriter3.0 # 输出 Excel python-calamine0.2 # 读取 .xlsx / .xls包实际用途引入方式pandas数据处理主体直接 importcharset-normalizerCSV 编码识别直接 importpython-calamine读取.xlsx/.xls通过engine指定xlrd读取老式.xls通过engine指定xlsxwriter输出 Excel通过engine指定说明一为什么后面三个包必须手写进清单。enginecalamine这类参数是字符串而非 import 语句pipreqs等自动工具扫描不到生成的清单会漏掉全部引擎包。漏包的后果不会在本地暴露——本机依赖已装全——只会在他人环境运行时报错。因此 Excel 读写相关的引擎依赖必须手工补入。说明二读取环节的选型。python-calamine基于 Rust 的 calamine 库读取速度明显优于纯 Python 实现且一种引擎通吃.xlsx/.xls两种格式无需分别维护。这是 30 万行数据能在 9.64 秒内处理完成的原因之一。xlrd保留用于老式.xls的兼容场景。说明三依赖清单建议用pipreqs --mode no-pin生成后再手工补全引擎包避免写入与本机不符的版本号。二、数据质量问题该批数据存在以下五类问题异常数据规模如下。1. 文件命名与类型不统一10月份销售.xls sales_05.xlsx 销售数据_01月.xlsx 11月份销售 - 副本.xlsx 该文件为空文件 销售数据_补充_BOM.csv 销售数据_补充_GBK.csv 两个 CSV 文件编码不一致2. 同一字段存在多种命名方式。以金额字段为例17 个文件中共出现 9 种写法销售额、销售金额、金额、金额(元)、营业额、amount、sales、revenue、gmv日期字段存在「日期 / 销售日期 / 订单日期 / date」等写法地区字段存在「地区 / 大区 / 区域 / 省份」等写法各有八九种变体。3. 日期格式不统一2024年12月13日、2024.12.13、2024/12/13等多种格式并存。4. 数值列夹杂文本内容金额列中包含「暂无」「-」「N/A」等非数值内容。5. 地区名称带有注记「华东大区」与「华东」指向同一地区若不作处理将被拆分为两个分组。异常数据规模统计问题类型行数金额无法解析24,071数量无法解析15,054关键字段缺失去重后合计37,947完全重复记录163说明一金额异常与数量异常存在重叠其中 1,178 行两个字段同时异常去重后仅计一次故关键字段缺失合计为 37,947 行不等于 24,071 与 15,054 之和39,125。说明二本批数据的缺失集中于金额与数量两个字段日期、地区、产品三个字段无缺失记录。关于数据分布的说明本批数据的行数分布并不均衡构成行数占比10 月份文件300,00099.48%其余 15 份合计1,5640.52%其中 10 月份的单份数据量为其余各份平均值约 104 行的 2,877 倍。该构成是为验证脚本在大数据量下的处理表现而设定的并非真实业务分布。此处引出一个值得注意的结论**数字自洽校验只能保证账目平衡无法识别数据本身的业务异常。**在本例中10 月份数据量异常这一问题并非由校验逻辑发现——因为300000 1564 301564完全成立账目是平的这一问题是在观察透视表分布时才暴露的。因此数据质量报告需要与可视化结果交叉验证前者回答「有没有丢数据」后者回答「数据是否合理」二者缺一不可。三、处理方案整体流程分为六步核心思路为先统一、再合并、后判重每一步保留处理明细。1. 遍历目录并按扩展名分派读取遍历时过滤两类文件Excel 打开过程中生成的~$临时文件以及扩展名不属于.xlsx/.xls/.csv的文件。每个文件的读取操作单独以try/except包裹——单个文件损坏不应中断整批处理失败文件名记入清单最后统一提示。forfile_pathinsorted(input_dir.iterdir()):iffile_path.name.startswith(~$):continue# Excel 临时文件iffile_path.suffix.lower()notin(.xlsx,.xls,.csv):continuetry:dfread_any(file_path)# 按扩展名分派读取exceptExceptionase:failed.append((file_path.name,str(e)))continueframes.append(df)2. 字段名标准化与别名映射各文件列名先经norm()函数标准化去除首尾空格、空格 / 下划线 / 短横线、括号及括号内内容并统一转为小写随后通过别名表将金额字段的多种写法映射至标准名Amount。defnorm(s):ss.strip()sre.sub(r[\s_-],,s)# 去除空格/下划线/短横线sre.sub(r[(].*?[)],,s)# 「金额(元)」-「金额」ss.lower()returns alias{# 标准名 - 别名列表节选Amount:[销售额,销售金额,金额,营业额,amount,sales,revenue,gmv],...}由于norm()已剥离括号内容「金额(元)」会被规范化为「金额」故别名表中无需单独列出该写法。需注意两点norm()会移除下划线若别名表中使用order_date这类带下划线的英文名将无法与实际列名orderdate匹配。别名表中的英文写法应统一为无下划线形式。比对时须对两侧同时施加norm()——只规范化实际列名、而不处理别名表同样会导致匹配失败。3. CSV 编码自动检测GBK 与 UTF-8-BOM 两种编码并存采用charset_normalizer自动检测编码而非人工指定encodingfrom_path(file_path).best().encoding dfpd.read_csv(file_path,encodingencoding)4. 日期格式归一先将「年 / 月 / . / /」统一替换为-、去除「日」字符再按固定格式解析解析失败的值置为 NaT 并计数本批数据解析失败 0 行merge_df[Date]pd.to_datetime(merge_df[Date].astype(str).str.strip().str.replace(r[年月./],-,regexTrue).str.replace(r日,,regexTrue),errorscoerce,format%Y-%m-%d,)一个易被忽略的陷阱上述写法假设日期列是字符串。若某个文件的日期列已是 datetime 类型Excel 中设为日期格式时常如此.astype(str)会得到2024-12-13 00:00:00与format%Y-%m-%d严格不匹配整列会被静默置为 NaT。规避方式有两种读取时对日期列统一指定dtypestr或在转换前截断时分秒部分.str.slice(0,10)# 仅保留前 10 位兼容带时分秒的情形由于本方案对解析失败数做了计数并打印一旦发生上述整列失效计数会立刻暴露异常不会无声通过。5. 清洗顺序的设计该环节的执行顺序不可随意调整地区名归一化「华东大区」并入「华东」须置于去重之前——顺序颠倒将导致此类记录无法被识别为重复数值列须先经pd.to_numeric(errorscoerce)将文本转为 NaN缺失剔除环节才能完整覆盖这些记录上述两项完成后再依次执行dropna剔除关键字段缺失行与drop_duplicates剔除完全重复行每一步均记录剔除行数6. 数字自洽硬校验iftotal_rows!rows_after_drop_dupdropna_rowsrows_drop_dup:sys.exit(合并后数据行数与原始数据行数不一致终止处理)校验逻辑为读入行数 输出行数 缺失剔除行数 重复剔除行数。校验不通过则终止程序不输出未经核验的结果。四、开发阶段遇到的 6 个问题问题 1数量列输出为17.0。列中一旦出现 NaNpandas 会将整列推断为 float 类型整数显示为 17.0。处理方式是使用可空整数类型注意首字母大写merge_df[Quantity]merge_df[Quantity].astype(Int64)问题 2金额列求和结果异常。「暂无」「-」属于文本而非缺失值直接调用df[Amount].sum()将得到错误结果。须先执行pd.to_numeric(errorscoerce)将文本转为 NaN 后再计算。经验总结isna()无法识别此类伪缺失值必须先转换再统计。问题 3Excel 临时文件被误读。Excel 打开文件时会在同目录生成~$开头的临时文件读取该文件既无意义也可能报错。处理方式为在遍历时增加过滤iffile_path.name.startswith(~$):continue问题 4空文件混于其中。「11月份销售 - 副本.xlsx」为空文件。处理方式为明确打印「为空跳过」并计入失败清单既不静默忽略也不中断整批处理。问题 5输出文件被占用。上一轮生成的输出文件若正在 Excel 中打开xlsxwriter写入时将触发PermissionError。处理方式为单独捕获该异常并给出明确提示请用户关闭 Excel 后重新运行。问题 6如何证明 30 万行数据剔除 3.8 万行后不存在误删。这是整个方案中最关键的一环。删除数据本身不难难的是向数据使用方证明被剔除的每一行都有明确原因留下的每一行都可追溯来源。仅依赖人工抽查无法完成这一核验——30 万行的体量下抽查既覆盖不全也无法复现。本方案的处理方式是每一步剔除操作均记录行数与原因最终通过自洽校验串联全部数字。读入 301,564 行输出 263,454 行中间差额 38,110 行的去向逐项列明关键字段缺失 37,947 行、完全重复 163 行两者相加恰为 38,110无一行下落不明。校验不通过则程序终止绝不输出账目不平的结果。该机制并非理论设计。在后续项目的开发中它实际拦截过一次数据丢失——某次清洗逻辑调整后自洽校验立即报错经排查发现是新引入的过滤条件误删了有效记录。若没有这道校验错误结果会直接交付出去。**但自洽校验并非万能需要明确它的边界。**它保证的是「账目平衡」而非「数据合理」。本例中 10 月份单份数据 30 万行、占总量 99.48%这一问题校验逻辑无法发现——因为300000 1564 301564完全成立。业务口径层面的异常只能通过与可视化结果的交叉验证来识别详见第二节末尾的说明。因此一道自洽校验加上一张分布图二者缺一不可校验回答「有没有丢数据」图表回答「数据是否合理」。五、运行结果执行python clean_sales_data.py9.64 秒完成 16 个文件17 份中 1 份为空文件已跳过、301,564 行数据的合并清洗输出cleaned_sales_data.xlsx包含明细数据 263,454 行与「月份 × 地区」销售额透视表两个 sheet日期统一为YYYY-MM-DD格式可直接排序与筛选同步落盘docs/数据质量报告.txt记录读入行数、各原因剔除行数、输出行数全部数字满足自洽关系图 1运行结果控制台输出的处理明细包含各文件读取行数、9.64 秒耗时与三项自洽数字。图 2透视表输出已排除 2024-1010 月份单份数据 30 万行、占总量 99.48%若纳入则其余月份在图上不可见。故此处筛除该月以呈现其余 11 个月的真实分布完整数据见仓库中的输出文件。六、总结复盘该项目技术实现本身难度不高真正具备价值的是三点方法论层面的结论先统一、再合并、后判重—— 处理顺序错误时不规范数据会在错误的环节被遗漏且事后难以察觉每一行数据的取舍都必须可追溯—— 交付数据的前提是账目能够自洽这既是技术要求也是对数据使用方的责任自洽校验有边界须与可视化交叉验证—— 校验能证明「没有丢数据」但无法证明「数据合理」业务口径层面的异常只有分布图能暴露后续一篇将基于国家统计局 31 个省份的 GDP 数据展开趋势分析聚焦地区排位变化与增速差异的背离现象具体分析将在后续文章中介绍。完整代码与示例数据GitHub 仓库https://github.com/mokong-J/sales-data-cleaner文中脚本、脏数据生成器与数据质量报告模板均已在上述仓库开源MIT License可自行取用。如果在实践中遇到本文未覆盖的场景欢迎在评论区留言讨论。