2.3 数据清洗：pandas 处理 Excel 与 CSV

场景：每周一合并六个部门的销售表

采集来的数据是一摊，内部表格又是一摊。每周一早上，六个部门的销售负责人会往群里丢 Excel，格式全凭个人喜好：有的表头在第一行，有的在第三行；有的金额带人民币符号，有的用千分位逗号；最过分的一位把汇总行混在明细中间。我的任务是把这六张表合并成一张标准表，出周报。

这活就是为 pandas 准备的，但很多人在 read_excel 这一步就开始翻车。下面把我这两年的标准套路拆开讲，这套流程和我们AI 工具箱里的采集模块是无缝衔接的，采集入库的数据走的是同一套清洗规则。

方案：读、修、合、写四步流水线

第一步：读。 核心是别让 pandas 猜类型。数字列被读成浮点、身份证号变科学计数法，都是猜出来的祸：

import pandas as pd

def load_orders(path, sheet="销售明细"):
    df = pd.read_excel(
        path, sheet_name=sheet,
        header=2,                  # 表头在第 3 行
        dtype={"订单号": str, "身份证号": str, "金额": float},
        parse_dates=["下单日期"],
    )
    return df

dtype 显式声明是保命符。订单号 100230 读成数字，导出后再补 0 的事故我排查过两次——订单号一旦变 float 就有精度风险，超过 16 位直接丢位，所以凡是编号类字段一律 str。

第二步：修。 修的是那些格式各异的单元格：

def clean(df):
    df = df.copy()
    df["金额"] = (
        df["金额"].astype(str)
        .str.replace("￥", "", regex=False)
        .str.replace(",", "", regex=False)
        .str.strip()
        .astype(float)
    )
    df = df[df["订单号"].notna()]               # 干掉汇总行
    df["下单日期"] = pd.to_datetime(
        df["下单日期"], format="mixed", errors="coerce"
    )
    df = df.drop_duplicates(subset=["订单号"], keep="last")
    return df

汇总行怎么干掉的？它没有订单号，dropna 一滤就掉，比写正则匹配表头稳得多。errors="coerce" 让坏日期变成 NaT 而不是抛异常，配合空值计数就能在日志里报告这周哪几张表脏得最厉害。

字段清洗规则对照

把清洗规则沉淀成表格，是让团队协作不出幺蛾子的关键，每周谁值班谁照着执行：

表里最后一列是给新人看的震慑，不写后果他们永远觉得清洗规则是繁文缛节。

第三步：合。 多表合并主战场是 concat 加 merge，键名不一致是永远的痛：

frames = [clean(load_orders(p)) for p in paths]
total = pd.concat(frames, ignore_index=True)
# 与客户维度表拼接
total = total.merge(
    customers, left_on="客户名", right_on="客户简称", how="left"
)

merge 之前一定先核对两边的键分布：左表在右表找不到的行有多少条、反过来多少条。体检就两行，但能救周报的命：

unmatched = total[~total["客户简称"].isin(customers["客户简称"])]
print(f"未匹配: {len(unmatched)} 条, 金额合计 {unmatched['金额'].sum():.2f}")

how="left" 会默默吞掉这些差异，周报整体金额和明细对不上时，排查能查到怀疑人生。我们团队的规矩是 merge 结果先打印 shape 和未匹配数量，超过阈值（我们定 2%）就拦截报警，宁可晚出半小时报告，也不带疑点交差。

第四步：写。 导出 Excel 用 openpyxl 引擎，注意 to_excel 默认覆盖整个文件而非单个 sheet，追加要写 mode="a" 加 if_sheet_exists="replace"，参数组合错一个就报错。被过滤掉的行也别扔，单独导出一张 reject 表：

with pd.ExcelWriter("周报.xlsx") as w:
    total.to_excel(w, sheet_name="汇总", index=False)
    rejected.to_excel(w, sheet_name="被剔除行", index=False)

这张 reject 表是清洗流程最受欢迎的设计。之前有一周某部门金额少了一截，复盘半天才发现是备用单号被规则滤掉了；有了 reject 表，值班的人只需要扫一眼被剔除行就能确认有没有误杀，审计也多了一份留痕。

踩坑与排查

坑一：notna 误杀。 有一次清洗规则定严了，把一个部门临时用的备用单号全滤掉，金额差了一截。教训是过滤条件上线前先看影响行数，或者把被过滤的行单独存到 reject 表里备查，而不是直接丢。

坑二：chain assignment 警告。 高版本 pandas 里 df["a"]["b"] = 1 这种链式赋值会报警告甚至报错，必须用 df.loc。代码审查时这行也是重点，AI 生成的代码经常这么写，代码审查那篇里列了几个高频翻车点，链式赋值就是其中之一。

坑三：大文件读取。 周报数据量上来后 read_excel 会慢到分钟级，CSV 换成 pd.read_csv 分块读取加指定 dtype，实测能省三分之二时间。Excel 实在没招就先转 CSV。

坑四：openpyxl 日期。 用 to_excel 写日期列，对面用 WPS 打开有时显示成一串数字。稳妥做法是写格式化字符串列，或者保留原始值后用 ExcelWriter 的 datetime 格式，两边测试一遍再发布。

坑五：合并版本灾难。 有一次周一抢着出报告，两位同事各自清洗了一遍又都往总表 merge，等发现重复已经是周三。后来周报合并统一收口到一个人执行，版本只认云端对象存储里的最新归档，本地中转文件一律不作为下游输入。数据合并这事儿，流程上的规矩比代码里的校验更救命，我们定的完整流程在腾讯云实践里也有类似的 Single Source of Truth 案例。

小结

数据清洗的本质是给混乱的数据定标准、留痕迹。pandas 的 API 都在明面上，真正难的是把每一步处理变成可复核的规则，让任何人接手都能复现同样的结果。清洗完的数据才够格进知识库，RAG 检索那节会验证这一点：脏数据进库，检索质量直接崩盘。整套周报流程后来搬到了云函数上定时跑，详情见Serverless 部署入门。

延伸阅读

• Python AI 工具箱全书目录
• RAG 检索：本地知识库问答
• 腾讯云实践案例合集