客户 Excel 一团乱,pandas 为何悄悄少算营收
pandas 求和时把缺失值当 0,要清点类型转换丢掉的每个单元格,并在演示前给客户发错误报告。
要点速览
- 按原样读取客户文件:ID 列用 dtype=str 或 object,按列设置 na_values,越南式数字用 thousands 和 decimal 参数处理。
- pandas 求和时把 NA 当 0,所以在报出任何数字之前,一定要清点有多少单元格被强制转成了 NaN。
- 最有价值的产出不是干净的文件,而是一份客户能自己去修正的错误清单。
一千笔订单,其中四十笔的金额列写着“联系我们”。你把这一列转成数字,四十个单元格变成 NaN,然后调用 .sum()。返回的数字看起来整整齐齐,却恰好少算了四十笔订单,而且没有任何警告。
这个场景是假设的,但机制是真实的。pandas 文档写得很明白:求和时,NA 值或空数据按 0 处理。对前线部署工程师(FDE)来说,这是驻场第一周最危险的陷阱,因为错误的数字最终会出现在演示里。
本文介绍一套今晚就能在笔记本上跑通的流程:按原样读取文件,声明缺失值,可控地转换类型,清点损失,再把错误汇总成报告交给客户。代码片段为教学做了删减,凡是简化之处都会标明。
要做什么,需要准备什么
假设一家分销商发来两个文件:orders.csv(订单)和 report.xlsx(报表)。CSV 里有一列 customer_id(客户编号),值形如“00123”;一列 amount(金额)按越南习惯书写,例如“1.250.000,5”(外面套着双引号);还有几个单元格写着“-”或“暂无”。Excel 文件顶部有三行装饰性的表头,每个分支机构一个工作表。
你需要 Python 3、pandas、运行环境自带的 Excel 读取引擎,最后一步还需要 Pandera。自己动手造这两个样例文件,每个约 20 行,故意把各类脏数据都埋进去。亲手弄脏数据,是理解每个参数作用的最快方式。
第 1 步:读取 CSV,别让 pandas 去猜
最常见的错误是直接写 pd.read_csv("orders.csv")。pandas 会推断类型,“00123”变成数字 123,你还没来得及看一眼,客户编号就已经彻底毁了。
import pandas as pd
df = pd.read_csv(
"orders.csv",
dtype={"customer_id": str},
na_values={"amount": ["-", "pending", "contact us"]},
thousands=".",
decimal=",",
on_bad_lines="warn",
)
read_csv 文档建议使用 str 或 object,并配合合适的 na_values,以保留原始数据,而不是让 pandas 解释类型。na_values 可以接收字典,因此能为每一列单独声明缺失值,作为 pandas 默认识别为 NaN 的列表之外的补充。
thousands 和 decimal 参数用来处理特定地区的数字格式;文档中的示例针对欧洲数据,把逗号作为小数分隔符,这正是越南数字的写法。on_bad_lines="warn" 遇到字段过多的行时会发出警告并跳过,既不会让整个读取崩溃,也不会悄无声息地丢行。
本步检查:打印 df.dtypes 和 df.head()。customer_id 列必须保留开头的 0,amount 必须是浮点数 1250000.5。仔细阅读每一条坏行警告,记下行号,它们是给客户的报告里的第一项。
第 2 步:合并之前,逐个打开 Excel 工作表
客户的 Excel 文件很少从第 1 行开始。前三行通常是公司名称、报表标题和导出日期。
sheets = pd.read_excel(
"report.xlsx",
sheet_name=None,
skiprows=3,
dtype=object,
)
for name, table in sheets.items():
print(name, table.shape)
设置 sheet_name=None 后,read_excel 返回一个字典,每个工作表对应一个 DataFrame,各分支机构有多少行一目了然。dtype=object 让数据保持 Excel 中的原样,不做类型推断;代价是所有列都变成 object,需要你在下一步自己转换类型。
如果各工作表的垃圾行数不同,skiprows 也可以接收一个函数:它会对每个行索引调用一次,返回 True 即跳过该行。例如 skiprows=lambda i: i in (0, 1, 2)。本步检查:所有工作表的列数必须相同;列数不同的工作表,就是客户手动插过列的那个。
第 3 步:先规范化数字字符串,再转换类型
第 2 步之后,Excel 里的金额列是混杂的:客户以数字形式录入的单元格是数字,以文本形式录入的则是“1.250.000,5”这样的字符串。直接把这一列交给 pd.to_numeric,越南式写法的字符串一个也解析不了;加上 errors="coerce",它们全部变成 NaN。完全有效的单元格和垃圾值一起丢了。
所以需要一个规范化步骤:去掉千位分隔的点号,把小数逗号换成点号,而且只对字符串单元格这样处理。已经是数字的单元格不要动,因为去掉 1250000.5 里的点号会把它变成另一个数。
table = sheets["Branch_HN"]
def normalize(x):
if isinstance(x, str):
return x.strip().replace(".", "").replace(",", ".")
return x
amount = table["amount"].map(normalize)
already_missing = amount.isna().sum()
table["amount"] = pd.to_numeric(amount, errors="coerce")
lost_in_coercion = table["amount"].isna().sum() - already_missing
print("原本为空:", already_missing, "| 无法读取:", lost_in_coercion)
(输出分别显示原本为空的单元格和无法读取的单元格。)
默认情况下,to_numeric 遇到第一个无法解析的值就会报错。设置 errors="coerce" 后,该值变成 NaN,代码继续运行。很方便,但开头那四十笔“联系我们”的订单,恰恰就是在这里从合计中消失的。
上面的 normalize(规范化)函数是简化版:它假定整列都按越南习惯书写。如果客户还混用了美式的“1,250,000.5”,就需要额外的识别规则,与其去猜,不如先问客户。
注意清点的方式:对列本身调用 isna().sum(),把一开始就为空的单元格与转换后新坏掉的单元格区分开。这两个数字对客户的意义不同。如果 1,000 行中只有 960 行还有金额,你报出的每一个合计都必须附上一句“不含 40 笔金额未知的订单”。
第 4 步:缺失单元格怎么处理,由客户决定
pandas 提供了两个工具:dropna() 删除含缺失数据的行或列,fillna() 用其他值替换 NA。两者都只需一行代码,但两者都是业务决策。
把金额列填成 0,等于断言这些订单是免费的;删掉这些行,等于断言这些订单从未存在。建议:只在客户确认过规则的列上使用 fillna(例如备注为空时填成空字符串),金额列保留 NaN,并写进错误报告。
还要注意,pandas 会根据数据类型使用不同的哨兵值来表示 NA。执行 fillna 或类型转换之后,再打印一次 dtypes,确认没有哪一列意外改变了类型。
去重也属于这一步。KDnuggets 上的一篇教程给出的理由很简单:同一条记录被重复计算,会扭曲分析结果。合并多个工作表时,在两个分支机构之间转移的订单很容易出现两次;要按客户确认的关键列去重,而不是按整行去重。
第 5 步:把错误变成客户看得懂的报告
到这里,你已经有了一个基本干净的文件,但真正的价值在错误清单里。Pandera 是 Union.ai 的开源项目,可以为 DataFrame 声明 schema 并据此校验。分支机构文件的三列 schema 大致如下。
# 简化示意;API 名称请以已安装的 Pandera 版本为准
import pandera as pa
schema = pa.DataFrameSchema({
"customer_id": pa.Column(str, pa.Check.str_matches(r"^\d{5}$")),
"amount": pa.Column(float, pa.Check.ge(0), nullable=False),
"date": pa.Column("datetime64[ns]"),
})
try:
schema.validate(table, lazy=True)
except pa.errors.SchemaErrors as err:
failures = err.failure_cases
print(failures[["column", "check", "index", "failure_case"]])
failures.to_csv("errors_for_client.csv", index=False)
(顶部注释说明这是简化示意,API 名称需以已安装的 Pandera 版本为准。)
关键在于 lazy=True:Pandera 不会在第一个错误处停下,而是把所有错误汇总成一份报告。failure_cases 表列出每次失败对应的列、规则、行和值。你把它导出成一个文件,一次性发给客户,而不是发十封“又发现一个错误”的邮件。
本步检查:在样例文件里故意把一个客户编号改成“123”,再删掉一个金额单元格。两个错误必须出现在同一份报告里,而不是每次运行只报一个。
最常见的错误
第一个是先直接读取文件、之后再修:开头的 0 一旦丢失,就无法从 DataFrame 中恢复。其次是为了减少干扰,把 on_bad_lines 设成静默跳过,结果没人知道丢了多少行。
更隐蔽的一个,是第 3 步提到的:对未经规范化的 Excel 列用 coerce 模式调用 to_numeric,代码运行顺畅,半列数据却不见了。最危险的是为了让图表看起来完整,对金额列执行 fillna(0)。再加上 pandas 求和时把 NA 当 0,你会得到一个必错无疑、却看起来完全可信的合计。
在客户现场,什么样的回答能赢得信任?
设想项目第一天:你通过邮件收到一个装满 Excel 文件的文件夹,第二天开会时客户问:“这和我们的账对得上吗?”
弱的回答是“清洗已经做完了”。好的回答是:“我们读取了 1,000 行,其中 40 行缺少金额,3 行格式有误。这是清单,请从源头修正。”这样的回答让客户看到,你对他们的数据了解到了每一行。
简历上不要写“精通 pandas”。要描述一条具体的数据管道:用固定 dtype 读取多工作表文件,处理越南格式的数字,用 Pandera 报告错误。
阅读岗位描述时,留意有没有提到接入客户数据的内容;如果有,面试时就讲这个故事。
第一次开会时,你能带去的最干净的数字,是附有一份“尚未计入哪些行”清单的数字。
这篇文章对你有帮助吗?
感谢你的反馈!