4. 检测数据读取与清洗实战

食品检测实验室每天产生的原始记录，往往以三种形态存在：仪器工作站导出的CSV、检验员手工填写的Excel台账、以及LIMS系统里的数据库表。这三种来源的字段命名、单位习惯、时间写法各不相同，直接拿来做可视化，图表会立刻"说谎"——同一批样品的铅含量，一半按mg/kg记录，一半按μg/kg记录，平均值就会被放大一千倍。因此，数据读取与清洗是可视化开发中投入产出比最高的一步，本节围绕"导入—诊断—清洗—校验"四个动作展开。

一、三类数据源的读取方式与常见陷阱

CSV文件看似最简单，实际问题最多。仪器导出的CSV常见编码为GBK，用默认UTF-8读取会出现中文列名乱码；部分文件带BOM头，首列名会变成"\ufeff样品编号"。Python中可用pd.read_csv(path, encoding='gbk')显式指定编码，若不确定可先读取前几行字节判断。另一个陷阱是分隔符，有些导出文件用分号或制表符分隔，需通过sep参数指定。

Excel台账的问题在于"人"的痕迹：合并单元格导致表头错位、同一列混入备注文字、日期被存成文本。读取时应先用header参数定位真实表头行，再用dtype=str把所有列按字符串读入，避免pandas自动把"2024-03-01"转成时间戳、把"0.05"转成浮点数后丢失原始精度，后续再逐列做类型转换，这样每一步转换都可控、可追溯。

数据库导入相对规范，但要注意字段类型与业务语义的差异。例如检测结果字段在库中可能是DECIMAL(10,4)，而检出限以下的值被存为NULL或-1，这两种写法含义完全不同：前者是"未检出"，后者可能是"未检测"。读取时应通过SQL显式筛选字段并保留原始标记，不要急于用fillna统一填充。

二、缺失值：先分清"没有"和"未检出"

检测数据中的空值至少有三种语义：一是记录缺失，比如某批次漏填了水分含量；二是未检出，即结果低于方法检出限（LOD），实验室通常记为"ND"或"<0.01"；三是未检测，该指标本批次不在检测范围内。三者的处理方式完全不同：记录缺失应回溯原始单据补录，无法补录时在可视化中标注为"数据缺失"而非填0；未检出应保留为特殊标记，统计时按1/2 LOD参与计算或单独计数；未检测则应在图表中直接排除该指标，避免拉低整体均值。

实操中建议新增一列result_flag，取值如detected、not_detected、missing、not_tested，把语义显式化。这样后续无论是算检出率还是画箱线图，都能按标记分流，而不是靠猜测空值的含义。

三、异常值：区分"超标"与"录入错误"

异常值处理最忌讳一刀切地删除。食品检测中的异常值有两类：一类是真实的超标值，比如某批酱腌菜的亚硝酸盐检出25 mg/kg，而GB 2762限量是20 mg/kg，这是有价值的业务信号，必须保留并打上超标标签；另一类是录入错误，比如铅含量录成"5000 mg/kg"，明显超出方法量程，应标记为可疑并回溯原始记录。

判断方法可以组合使用：先按标准限量做规则判断，超出限量的标记over_limit；再用统计方法辅助识别，对同指标同基质的历史数据计算四分位距，超出Q3 + 1.5×IQR的值标记为suspect，但suspect不等于删除，而是进入人工复核队列。示例代码如下：

import pandas as pd
import numpy as np

df = pd.read_csv("inspection.csv", encoding="gbk", dtype=str)
df["result"] = pd.to_numeric(df["result"], errors="coerce")

# 规则一：按限量标准标记超标
limits = {"铅": 0.5, "亚硝酸盐": 20.0, "菌落总数": 100000}
df["over_limit"] = df.apply(
    lambda r: r["result"] > limits.get(r["item"], np.inf), axis=1)

# 规则二：IQR 识别可疑值（仅对已检出记录）
detected = df[df["result_flag"] == "detected"]
q1, q3 = detected["result"].quantile([0.25, 0.75])
iqr = q3 - q1
df["suspect"] = (df["result"] > q3 + 1.5 * iqr) & (~df["over_limit"])

四、单位统一与时间格式转换

单位不统一是食品检测数据最常见的"隐形错误"。重金属常以mg/kg和μg/kg混用，两者相差1000倍；农药残留有时用mg/kg，有时用ppm，而1 ppm在固体样品中恰等于1 mg/kg，在液体样品中则需结合密度换算。统一策略是：为每个指标设定一个基准单位（如重金属统一为mg/kg），在数据字典中记录原始单位，清洗时按换算系数转换并新增unit_original列留痕。换算系数表应作为配置维护，而不是硬编码在脚本里。

时间格式同样混乱：Excel里可能是"2024/3/1"、"2024-03-01"、"2024年3月1日"，数据库里可能是时间戳。统一用pd.to_datetime(df["test_date"], format="mixed")解析，再格式化为ISO 8601的YYYY-MM-DD。需要特别注意时区：如果采样时间与检测时间跨天，应分别保留两个字段，不要合并成一个时间点，否则按日聚合时会出现归属错误。

五、清洗后的校验清单

清洗完成不等于可以交付，必须做一轮校验。核心检查项包括：行数是否与原始记录一致（防止去重误删）、每个指标的单位是否唯一、时间字段是否全部可解析、超标标记数量是否与业务预期吻合、缺失率是否在可接受范围。建议把校验结果输出为一张简表，随数据一起归档，这样后续可视化出现异常时，能快速定位是数据问题还是图表问题。