电子表格 AI 清洗：人类审批防止静默数据损坏

一个电子表格清理工具，读取文件、请模型修复、再写回结果——这本质上是一起数据丢失事故，只不过加了个进度条。安全可运行的版本有三处关键不同：列类型通过投票而非猜测来确定；解析是确定性的，只对解析失败的单元格调用模型；输出是一份 diff，等待人工批准——而不是直接写回文件。

核心原则：绝不原地写回

每一次转换都是提案，直到人类批准为止。这不是过度谨慎：在 4 万行的文件中，一条正确率 99.5% 的规则会悄悄损坏 200 行数据，而且直到三个月后报告出错才会被发现。

因此，每次运行的输出是三份产物：

提案文件——每行一条变更：行号、列名、旧值、新值、产生该变更的规则、置信度。

摘要——按列、按规则统计数量，审查者打开文件之前就知道该重点看哪里。

清理后的文件，仅在批准后才写入，且绝不覆盖输入文件。这是一种"人在环中"的设计，而非自动化——提前说明这一点，能和需求方建立正确的预期。

列类型通过投票确定

不要问模型某列是什么类型。尝试用所有解析器逐个单元格跑一遍，然后统计。这更快、更免费，而且产生的是一个可以理性分析的数字。

# clean.py
import csv, re
from datetime import datetime
from decimal import Decimal, InvalidOperation

def try_int(s):
    s = s.strip().replace(",", "")
    return int(s) if re.fullmatch(r"-?\d+", s) else None

def try_decimal(s):
    t = s.strip().replace(",", "").replace("£", "").replace("$", "").replace("%", "")
    try:
        return Decimal(t)
    except (InvalidOperation, ValueError):
        return None

DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%m/%d/%Y", "%d-%b-%Y",
                "%Y/%m/%d", "%d.%m.%Y"]

def try_date(s):
    s = s.strip()
    for f in DATE_FORMATS:
        try:
            return datetime.strptime(s, f).date(), f
        except ValueError:
            pass
    return None

BOOLS = {"yes": True, "no": False, "true": True, "false": False,
         "y": True, "n": False, "1": True, "0": False}

PARSERS = {"int": try_int, "decimal": try_decimal,
           "date": try_date, "bool": lambda s: BOOLS.get(s.strip().lower())}

def type_column(values, blank=("", "-", "n/a", "na", "null", "none", "#n/a")):
    real = [v for v in values if v.strip().lower() not in blank]
    if not real:
        return "empty", 1.0, len(values)
    scores = {name: sum(1 for v in real if p(v) is not None)
              for name, p in PARSERS.items()}
    best = max(scores, key=scores.get)
    ratio = scores[best] / len(real)
    return (best if ratio >= 0.90 else "text"), ratio, len(values) - len(real)

0.90 这个阈值是核心决策点。高于它，该列有明确类型，解析失败的单元格是需要修复的错误；低于它，该列本质上是混合类型，强行指定类型会破坏信息——一个 60% 是数字、40% 是"见备注"的"数量"列，实际上是一个有数据录入问题的文本列，诚实的报告应当如实说明。

确定性地先行，模型地后行

将单元格分为三个桶，分别处理。这就是整体的成本逻辑：模型只看到第三个桶。

将第二个桶的规则写成具名函数，并记录每个单元格触发了哪条规则。当审查者看到 800 条变更来自 strip_currency_symbol 时，他们可以批准这条规则而不是逐个单元格审批——这是 15 分钟审查和无法使用的审查之间的差别。

模型真正擅长什么

不是解析——正则表达式解析得更好而且免费。模型擅长的是规则无法表达的判断：

实体解析。"Acme Ltd"、"ACME Limited"、"acme ltd." 是同一家公司。先通过字符串相似度对候选进行聚类，再让模型确认每个聚类——不要让它扫描整列。

单位歧义。一个包含"2.5kg"、"900g"和"1 lb"的重量列，需要对每个单元格判断数字的含义。要求模型分别返回数值和单位两个字段，然后在代码中做规范化。

自由文本分类。将一个混乱的"原因"列映射到固定词表，是一个分类任务，当词表在 prompt 中时模型表现良好——只要输出被约束在词表范围内，而不仅仅是"请尽量使用词表"。

解释问题所在。有时候最有用的输出不是修复，而是一句话："该列混入了两种不同顺序的日期，无法消歧"。

REPAIR = """A CSV column is typed as {coltype}. Here are cells that failed
to parse, with the row number. For each, return JSON:

{"row": n, "value": "the normalised value, or null if it cannot be
 recovered", "confidence": 0.0-1.0, "note": "under 15 words"}

Rules:
- Never invent a value. Missing data stays null.
- Only reformat what is present. Do not infer from other rows.
- If a cell means "no value" (n/a, -, unknown), return null with note
  "explicit blank"."""

"不要从其他行推断"这一指令阻止了最恶劣的行为。面对一列日期中有一条空白，模型会愉快地进行插值。那不是清洗，是捏造，而且产生的文件其错误无法被检测，因为它们看起来很合理。

Diff 就是产品

row,column,old,new,rule,confidence
14,amount,"£1,204.00",1204.00,strip_currency_symbol,1.0
14,date,"14/03/2024",2024-03-14,date_dmy,1.0
27,amount,"(85.50)",-85.50,parenthesised_negative,1.0
27,supplier,"ACME Limited","Acme Ltd",entity_resolution,0.86
39,amount,"1.204,00",1204.00,decimal_comma_locale,0.72
41,weight,"2 stone",null,model_repair,0.31

第 39 行是值得注意的。欧洲格式的数字和美国格式的数字在孤立情况下是无法区分的——1.204,00 和 1,204.00 是同一笔钱，但单独的 1.204 既可以是 1000 也可以是 1.2。正确的处理方式是根据多数模式一次性为整列做出决定，用同一条规则标记所有受影响的单元格，并在报告中承认这种歧义，而不是逐个单元格去消解。

按规则排序，再按置信度升序排序。审查者先看最差的结果，看腻了就可以停止。

将相同的转换分组。"312 个单元格：去除货币符号"是一条决策。

允许审查者否决整条规则，而不只是逐个单元格否决。这是使大型 diff 变得可处理的控制手段。

仅应用已批准的规则，写入新文件，并将批准记录保存在旁边。一份清洗后数据集的溯源信息是数据集的一部分。

测试输入总是新的工具

每个文件都不同，所以无法用预期输出来测试。但可以测试那些无论输入是什么都必须满足的属性，这是一种更强的测试形式，建立起来大约需要一个小时。

行数保持不变。 清洗不会添加或删除行。如果行数变了，那是 bug，不是判断问题。

每个变更都出现在 diff 中。 重新读取输出文件，逐单元格与输入比较，并断言有差异的单元格集合恰好等于已应用的 diff 行集合。这一条测试能捕获所有静默变更，包括库在替你写文件时偷偷做的那些。

幂等性。 对已清洗的文件再次清洗不会产生进一步变更。一个持续找到工作的清洗器是在振荡，通常是在两条相互矛盾的正则化规则之间来回切换。

未改动单元格的往返一致性。 清洗器未触碰的单元格必须逐字节一致地输出——包括前导零、引号内的尾随空格，以及原始换行符。

数值的价值保留。 对于每个被修改的数值单元格，断言新值按照所应用的规则解析出的数量与旧字符串表示的数量相同。这是一个比"正确"更弱的声明，但可以验证。

然后维护一个真实文件语料库，记录每个文件暴露的 bug，在每次变更后对所有文件运行完整的属性测试套件。每当有人报告问题时它就增加一个文件，不需要标注，六个月后它就成为项目最有价值的产物。

电子表格特有的陷阱

表头不在第一行。 可能有标题行、空行、合并单元格。将大多数单元格是短文本、非数字且唯一的行检测为表头，并说明你选择了哪一行。

数字以文本形式存储，文本以数字形式存储。 带前导零的产品代码被电子表格善意地转成整数后，你无法恢复丢失的信息。检测它——某列的值全是不同长度的整数——并报告它，而不是尝试清洗它。

Excel 序列日期。 包含 45383 的日期列是一个序列号，两种历史约定的纪元不同。检测范围并询问，而不是猜测纪元。

编码。 打开后出现乱码的文件不是清洗问题，而是编码错误。尝试 UTF-8，然后是带 BOM 的 UTF-8，然后是常见的单字节编码，按替换字符计数来选择。永远不要在乱码上运行模型。

末尾总计行。 最后一行通常是合计，不是记录。将其纳入平均值是经典的静默损坏。检测某数值列等于其上方列之和的最后一行。

持续运行的数据质量检查是下一步：一次性的清洗不如从同一来源再次收到坏数据时让管道失败的检查有价值。

---