shield_lock
金大哥
← 返回 Skill 列表
extension
分类: 数据与分析无需 API Key

表格数据清洗器

表格数据清洗器 —— 把脏乱的 Excel 导出 CSV / TSV 一键整理成干净、规范、可分析的数据：表头标准化、全角半角归一、货币/千分位/百分比还原为数值、缺失值与占位符检测、重复行去重、字段类型推断，并输出可追溯的质检报告。 触发词：清洗数据、整理表格、CSV 太脏、去重、标准化、数据整理、表格规范化、脏数据、Excel 导出乱码、批量整理、数据预处理、字段对齐。 适用：用户手里有一份从系统/网页/别人那里导出的表格，需要快速变成能直接进图表、数据库、模型训练的干净数据。 不适用：生成或伪造数据、篡改原始数据以掩盖事实、处理含密级/隐私且未脱敏的数据。纯本地处理，不联网、不上传表格内容。

person作者: user_8ca2df90hubcommunity
download下载资源包

表格数据清洗器（Data Cleaner）

从系统导出的 CSV、网页复制的表格、别人发来的 Excel，十个里有九个不能直接用：表头带空格、金额混着「￥12,000」、日期有 2023/01/15 也有 2023.1.5、空值用「-」和「NULL」乱填、还有一堆整行重复的废行。

这个 skill 把「整理表格」变成一套可复用的方法论：规则 + 可执行脚本 + 质检报告。你不用懂 Python，把文件丢给它，它按固定流程把脏数据收拾干净，还告诉你每一处改了什么、为什么改、还有哪些地方要你人工拍板。

它不是魔法——复杂业务逻辑（比如「这条该算谁的」）它判断不了，但它能处理 80% 的纯格式与一致性脏乱，让你把精力花在真正要动脑子的事上。

何时使用

当用户出现以下任意信号时激活本 skill：

  • 说「这表太乱了」「帮我整理下数据」「CSV 导出一堆乱码」「去个重」「把金额转成数字」。
  • 上传/粘贴了 CSV、TSV、从网页或 Excel 复制的表格文本。
  • 提到「表头不规范」「日期格式不统一」「有重复行」「空值太多」「字段对不齐」。
  • 需要把一份表先洗干净再拿去做图表、透视、入库、训练或对外报送。
  • 要做批量初筛：多份同结构表格先机洗、再人工复核。

不适用：要求生成假数据、把缺失值编造成合理值、篡改原始数据以掩盖错误。这些一律走护栏（见 hooks/guardrail.md）。

核心理念：先备份，再清洗，全留痕

  1. 原始文件永不动：清洗结果一律写到新文件（<原名>.cleaned.csv），源文件原样保留。这是铁律——任何一步错了都能回到原点。
  2. 每步可解释：脚本会生成质检报告，逐条列出「表头改了什么 / 哪列有多少缺失 / 去重删了几行 / 哪些类型被推断为数值」。不是黑箱。
  3. 机器做脏活，人做判断：脚本能确定的（去空格、全角转半角、货币还原、整行去重）全自动；需要业务判断的（某列该不该去重、缺失该怎么补）只报告不擅自改，交回给你拍板。

执行流程

第 1 步：识别输入与结构

先搞清楚用户给的是什么：

  • 文件类型：.csv / .tsv / .xlsx（xlsx 需先转 csv，见下）/ 纯文本表格。
  • 分隔符：逗号、制表符、分号还是竖线？脚本用 csv.Sniffer 自动嗅探，嗅探失败按逗号/制表符出现频次兜底。
  • 编码：优先 utf-8-sig（Excel 导出的 UTF-8 常带 BOM），失败再试 gbk / gb18030。中文环境大量脏数据来自编码错配，必须先对齐。
  • 规模：多少行多少列？决定是一次性跑还是分块。

如果用户给的是 .xlsx，先转成 csv 再洗：

  • 优先用环境内已有工具（如 python -c "import pandas" 若有 pandas）；若无 pandas，提示用户「在 Excel 里另存为 CSV（UTF-8）」再交回来。本 skill 核心脚本零依赖纯标准库，不强制要求 pandas。

第 2 步：跑核心清洗脚本

核心脚本 scripts/clean_tab.py 是一条流水线，覆盖绝大多数格式类脏乱：

# 基础：清洗并自动写出 <原名>.cleaned.csv，同时打印质检报告
python scripts/clean_tab.py 原始表.csv

# 指定输出路径
python scripts/clean_tab.py 原始表.csv --out 干净表.csv

# 报告单独写文件（Markdown）
python scripts/clean_tab.py 原始表.csv --report 清洗报告.md

# 报告以 JSON 输出（便于程序化核对 / 接入下游）
python scripts/clean_tab.py 原始表.csv --json

# 本地自检（验证脚本本身没坏）
python scripts/clean_tab.py --selftest

脚本自动完成以下动作（每条都进报告）：

| 动作 | 做了什么 | 典型场景 | | --- | --- | --- | | 表头标准化 | 去首尾空格、全角→半角、连续空白转下划线、重复列名加 _2 后缀 | 姓名 → 姓名；两列都叫「金额」→ 金额 / 金额_2 | | 单元格清洗 | 去首尾空白、行内多空格合并、全角数字字母标点转半角 | ２８ → 28；ＮＡ → NA | | 货币/千分位/百分比还原 | 去掉 ￥ $ € £、千分位逗号，括号负数转负号，百分号剥离 | ￥12,000 → 12000；(500) → -500；3.5% → 3.5 | | 缺失值归一 | - / NA / N/A / null / 无 / 空 / 暂无 / — 统一视作空 | 让「假非空」现出原形，缺失统计才准 | | 字段类型推断 | 按列采样判断 int / float / date / bool / string | 后续进图表、入库不会因「数字存成文本」翻车 | | 整行去重 | 完全一致的行只保留首条 | 导出重复、复制粘贴带来的废行 | | 主键列重复提示 | 首列重复值只报告不擅删，交人工 | 「订单号」重复可能是真问题，不能一键清 |

第 3 步：读报告，做人工判断

报告出来后，重点看三块：

  1. 缺失值汇总——哪列缺失多？如果是关键列（如「客户ID」大片空白），这份数据本身就有问题，清洗救不了，要回头找数据源。
  2. 类型推断——有没有本该是数字的列被推成了 string？常见原因：混了「待定」「洽谈中」这类文本。这种要和用户确认「非数字怎么处理」。
  3. 主键重复提示——首列重复往往意味着业务异常（同一订单两条记录），必须人工核对，绝不能脚本自动删。

关键原则：脚本只动「确定无害」的格式问题；涉及业务含义的（缺失怎么补、重复留哪条、某列要不要拆）一律在报告里标出来，等用户拍板，绝不替用户下业务结论。

第 4 步：进阶清洗（按需，参考 references/）

基础脚本解决格式层。更深的清洗看 references/：

  • 想要单位统一（「1.8米」和「180cm」归一）、枚举规范化（「男/男性/M」统一为「男」）、分列/合并，用 references/03-清洗规则与标准化清单.md 里的方法，可让 AI 生成针对该表的补充脚本。
  • 想要异常值检测（年龄 999、价格为负）、跨表关联校验，参考 references/05-清洗后质检SOP.md。
  • 想要日期统一格式化（2023/1/5 → 2023-01-05），参考 references/04-字段类型推断与转换.md 的日期节。

第 5 步：交付与留痕

  • 交付：干净表（新文件）+ 质检报告（md 或 json）。
  • 提示用户：原始文件未改动；若后续要复跑，建议把本次用的参数记下来。
  • 若数据含敏感字段（手机号、身份证、金额），提示用户清洗完成后及时删除临时文件，必要时做脱敏（见 guardrail）。

输出格式规范

质检报告（Markdown）结构

# 数据清洗质检报告

- 输入行数（不含表头）：1200
- 输出行数：1185
- 单元格清洗变更数：342
- 缺失值总数（空/占位符）：87

## 字段概览

| 字段 | 推断类型 | 缺失数 | 缺失率 |
| --- | --- | --- | --- |
| 姓名 | string | 0 | 0.0% |
| 年龄 | int | 12 | 1.0% |
| 工资(元) | float | 3 | 0.3% |
| 入职日期 | date | 5 | 0.4% |

## 清洗动作明细

- **表头标准化**：去空格/全角/重复列名加后缀（影响 6）
- **缺失值检测**：共 87/4740 个空/占位符单元格（影响 87）
- **整行去重**：完全一致的重复行仅保留首条（影响 15）

干净表格式

  • 仍是 CSV，UTF-8（带 BOM 方便 Excel 直接打开）。
  • 表头为标准化后的英文名/中文名（无空格、无全角）。
  • 数值列纯数字，无货币符号、无千分位。
  • 日期统一 YYYY-MM-DD（若用户要求其他格式，按需求调整）。
  • 空值留空（不填占位符）。

异常处理

| 现象 | 原因 | 处置 | | --- | --- | --- | | 打开是乱码（方块/问号） | 编码错配，多为 GBK 当 UTF-8 读 | 用 encoding='gb18030' 重新读；或用 iconv/记事本另存为 UTF-8 | | 一行被拆成多行 | 某单元格内含换行符 | 用 csv 模块读取（会自动处理引号内换行），勿用简单按行 split | | 列数对不齐 | 表头与数据列数不一致，或某格多出了分隔符 | 报告标出异常行，让用户核对源文件 | | 数字列被推成 string | 混入了非数字文本（「待定」「-」未归一） | 人工确认非数字项处理方式，必要时先替换再重跑 | | 去重删太多 | 首列本就不是唯一主键 | 改用「全行完全重复」才去重，主键重复仅提示 | | 文件超大（>50万行） | 内存吃紧 | 分块读取 / 用数据库（sqlite）做中转，不在单脚本里硬扛 |

隐私与数据安全

  • 表格可能含手机号、身份证、银行账号、薪资等敏感信息。处理完成后提示用户及时删除临时文件。
  • 默认不在任何输出中回显完整敏感字段（如账号、证件号），必要时做脱敏（保留前几位+掩码）。
  • 不将表格内容用于训练或对外传输；纯本地 stdout / 本地文件。
  • 含密级或未经授权个人信息的表格，按 hooks/guardrail.md 的软边界提示，必要时拒绝处理。

与其他 skill 的衔接

  • 洗完的干净表，可直接喂给「通用图表」类 skill 出图。
  • 多份同结构表格洗完，可参考「深度研究报告」类 skill 做汇总分析。
  • 需要入库（如云数据库）时，清洗后的规范字段是建表 schema 的最佳依据。

能力边界（说清楚不做什么）

  • 不做业务层面的正确与否判断：「这条记录该归哪个部门」它不知道，只负责格式与一致性。
  • 不编造缺失值：缺失就是缺失，报告出来让你决定补还是留空，绝不替你编一个「看起来合理」的数。
  • 不处理需要专业知识的语义清洗（如「把这段自由文本归类到行业」），那要另写专门流程。
  • 超大表（百万级）不建议单脚本硬跑，走分块或数据库中转。

实战案例：从一团乱到干净表

下面用一个真实感的例子走完全流程，让你看清每步产出。

原始表 订单原始.csv（从某后台导出，UTF-8 带 BOM）：

订单号, 客户名称 ,金额(元), 下单日期 , 状态 , 备注
A001, 张三, ￥1,200, 2023/01/15, 已付款, -
A002, 李四 , 1500.5, 2023-1-5, 未付款, 催过
A001, 张三, ￥1,200, 2023/01/15, 已付款, -
A003, 王五, NULL, 2024.03.20, 已付款, 暂无
A004, 赵六 , (300), 2021/12/31, 退款, 测试

第 1 步：跑脚本

python scripts/clean_tab.py 订单原始.csv --report 清洗报告.md

第 2 步：看报告（节选）

输入行数（不含表头）：5
输出行数：4            # A001 重复行被删 1 行
单元格清洗变更数：约 18
缺失值总数：2          # 王五金额为 NULL、赵六备注合同步归一

字段概览
| 字段 | 类型 | 缺失 |
| 订单号 | string | 0 |
| 客户名称 | string | 0 |
| 金额(元) | float | 1 |
| 下单日期 | date | 0 |
| 状态 | string | 0 |

清洗动作
- 表头标准化：去空格/全角/重复列名（影响 5）
- 缺失值检测：2 个空/占位符
- 整行去重：1 行

第 3 步：人工判断

  • 金额列 1 个缺失（王五）→ 问用户：填 0？留空？回源查？本例留空。
  • 金额 (300) 自动变 -300（退款，合理）。
  • ￥1,200 变 1200、1500.5 保留小数 → 数值列干净。
  • 日期三种写法被识别为 date 类型；若要统一 YYYY-MM-DD 需参考 references/04 补一步格式化。
  • 主键「订单号」去重后无重复，放行。

第 4 步：交付

订单原始.cleaned.csv + 清洗报告.md。源文件未动。

场景化命令模板（直接复制改路径）

场景 A：电商订单批量整理

# 多份同结构表，循环洗（bash）
for f in 订单*.csv; do
  python scripts/clean_tab.py "$f" --out "clean/${f%.csv}.cleaned.csv" --report "report/${f%.csv}.md"
done

场景 B：数据库入库前预处理

# 出 JSON 报告，便于程序读字段类型建表
python scripts/clean_tab.py 会员.csv --json --report 会员质检.json
# 报告里的 per_column 直接当建表 schema 草稿

场景 C：只要报告不要改文件

# 先 dry-run 看问题严重程度，再决定要不要洗
python scripts/clean_tab.py 疑似脏表.csv --report 预览.md

场景 D：超大文件分块

单脚本一次读全表。超过 50 万行建议：先 split -l 200000 切块，逐块洗，再 cat 合并；或导入 sqlite 用 SQL 做去重/类型转换，避免内存爆。

进阶清洗片段（按需让 AI 生成）

以下片段基于标准库，与本 skill 脚本风格一致，可拼到工作流里：

片段 1：日期统一为 ISO

import re, datetime
def to_iso(s):
    s = s.strip()
    for fmt in ('%Y/%m/%d', '%Y-%m-%d', '%Y.%m.%d', '%Y年%m月%d日'):
        try:
            return datetime.datetime.strptime(s, fmt).strftime('%Y-%m-%d')
        except ValueError:
            continue
    return s  # 解析不了原样返回，交人工

片段 2：枚举映射

GENDER = {'男':'男','男性':'男','M':'男','女':'女','女性':'女','F':'女'}
def norm_gender(v):
    return GENDER.get(v.strip(), v)  # 未命中原样返回并报告

片段 3：异常值（3σ）标记

import statistics
def flag_outliers(nums):
    if len(nums) < 3: return set()
    mu = statistics.mean(nums); sd = statistics.pstdev(nums)
    return {x for x in nums if abs(x-mu) > 3*sd}

常见问题 FAQ

Q：洗完数值列还是文本，图表拉不出来？ A：检查是不是混了非数字（「待定」「洽谈中」）。报告会标该列被推成 string。先把非数字项替换为空或标准值，重跑即可。

Q：去重把该留的也删了？ A：脚本只删「整行完全相等」的行。若你遇到误删，说明是「主键重复」而非整行重复——那是 L1 级，脚本只报告不删，需人工。确认你用的是最新脚本，且没加 --aggressive 之类会扩大去重范围的参数。

Q：中文乱码怎么破？ A：99% 是编码。用 encoding='gb18030' 重读，或先 iconv -f gbk -t utf-8 原.csv > 新.csv。源头存 UTF-8 最省事。

Q：金额带「万」单位（1.2万）怎么办？ A：基础脚本只剥离符号不分单位。需加单位归一（见 references/03），把「万」乘 10000 后删单位词，再转数值。

Q：能直接处理 xlsx 吗？ A：核心脚本读 CSV/TSV。xlsx 请先在 Excel「另存为 CSV（UTF-8）」。若环境有 pandas 可让其先转；本 skill 不强制依赖 pandas，保持零依赖可移植。

Q：会不会把我原始文件改坏？ A：不会。输出永远是新文件（<名>.cleaned.csv），源文件只读。这是铁律，改不了。

性能与规模建议

脚本为纯标准库、单进程、全表读内存，定位是「日常几万行以内的快速清洗」。规模上来后要换打法：

| 规模 | 建议 | | --- | --- | | < 5 万行 | 直接脚本跑，秒级完成 | | 5 万 – 50 万行 | 脚本可跑，但注意内存；确保机器有对应余量 | | 50 万 – 500 万行 | 分块：split -l 200000 切块逐洗再合并；或导入 sqlite 用 SQL 去重/类型转换 | | > 500 万行 | 直接上数据库/Spark，不在单脚本里硬扛；本 skill 的方法论仍可复用（规则一致） |

内存之外，还有两点：① 超大表先抽样（头 1 万行）跑一遍确认规则对，再全量，避免全量跑完才发现映射表漏了词；② 写入用流式（逐行 write），别全攒内存再一次性落盘。

字段级清洗决策树

遇到「这一列该怎么洗」拿不准时，按下面顺序过：

1. 这列是主键吗？
   ├─ 是 → 检查唯一性；重复只报告不删；缺失=致命，回源。
   └─ 否 → 往下
2. 这列是数值吗？
   ├─ 是，且带货币/千分位/括号负 → 先剥离符号转数值（脚本已做）。
   ├─ 是，带单位（万/kg/cm）→ 分离单位再转数值（references/03）。
   ├─ 是，但混了文本（待定/洽谈中）→ 文本项置空或标异常，重跑。
   └─ 否 → 往下
3. 这列是日期吗？
   ├─ 是，多格式混 → 统一 ISO YYYY-MM-DD（references/04 片段）。
   └─ 否 → 往下
4. 这列是枚举/分类吗？
   ├─ 是 → 建映射表归一；未命中报告交人工，别猜。
   └─ 否 → 当作文本，只做去空格/全角/占位符归一。
5. 这列是电话/身份证/邮编/账号吗？
   └─ 是 → 强制 string，禁止当数值（前导 0 会丢）。

这套决策树的本质：先判类型，再定动作；能自动的自动，要业务的业务拍板。

交付物规范与版本管理

一份清洗任务交付时，建议按如下结构归档，方便复跑与审计：

项目名_清洗/
├── 原始表.csv            # 源文件（只读，不改动）
├── 原始表.cleaned.csv    # 清洗结果
├── 清洗报告.md           # 脚本报告（含字段概览+动作明细）
├── 质检报告.md           # 进阶 SOP 质检（可选）
└── 清洗参数.txt          # 本次用的占位符清单/单位换算/缺失策略，备查
  • 参数留痕：把本次的占位符集合、单位换算规则、缺失处理策略写进 清洗参数.txt。下次同结构表复跑，直接复用，避免因「上次怎么处理的」失忆又洗一遍。
  • 溯源哈希：若数据进生产系统，建议对原始表算一次 SHA256 存底，作为「这份干净表来自哪份源」的凭证。
  • 版本号：若清洗规则迭代（如新增枚举映射），在参数文件和报告里标版本（v1/v2），避免新旧结果混淆。
  • 回滚：任何时候对结果不满意，源文件还在，删掉产物重跑即可，零损失。

质量门禁（交付红线）

以下任一条不满足，不算「清洗完成」，必须返工或明确标注风险后交用户：

  1. 源文件零改动——产物是独立新文件。
  2. 报告可追溯——每步动作、影响行数都进了报告，无黑箱。
  3. 关键列无未知缺失——ID/金额/日期/主键的缺失已逐一起因说明。
  4. 数值列确为数值——无货币符、无千分位、无隐藏文本。
  5. 日期统一——目标格式明确且全列一致。
  6. 类型无陷阱——电话/身份证/邮编等被保为 string，未被当数字。
  7. 敏感字段已提示——含个人信息时提示脱敏与删除临时文件。
  8. 异常已报告——异常值、主键重复、跨列冲突都列出待人工，未擅自处理。

满足这八条，这份干净表才敢拿去出图、入库、训练、对外报。

快速自检清单（交付前过一遍）

  • [ ] 原始文件未被改动（新文件独立存在）
  • [ ] 报告列出了每步动作与影响行数
  • [ ] 关键列缺失率已提示用户
  • [ ] 数字列确为数值类型（非文本）
  • [ ] 日期格式统一
  • [ ] 重复行已处理或已提示
  • [ ] 敏感字段已提示脱敏/删除