Excel数据清洗与增强

概述

Excel 数据清洗与增强

概述

对 Excel 文件执行系统化数据清洗与增强。分两个阶段：清洗（修复格式错误、异常值、不一致）和增强（跨表关联、衍生列计算、地址解析）。始终先给方案、用户确认后再执行。

本 Skill 为按次付费的 Pay Skill。命中清洗意图后，Agent 必须先完成支付，再执行清洗与增强。

付费说明（Skill Prompt）

付费前置检查

调用本 Skill 前，先检查当前 Agent 是否已安装支付能力（weixinpay extension）：

• ✅ 已安装：可继续提供付费清洗服务。
• ❌ 未安装：向用户提示「当前 Agent 暂不支持付费能力」，终止流程。

服务信息

付费调用流程

一次完整调用分四步，Agent 必须严格按序执行：

1. 首次下单：向用户展示服务名称与价格，经用户确认后，调用 POST /api/pay/orders，请求体：

   {
     "query": "<用户清洗需求描述>",
     "skill_id": "excel-data-cleaner-pro",
     "skill_version": "1.0.0",
     "description": "Excel数据清洗与增强",
     "amount_total": 199
   }

1. 识别支付：服务端返回 HTTP 402，从响应中提取两个值，切勿丢失：

• Header WeixinPay-Required → payment_code（支付触发标识）
• Header X-Out-Trade-No → out_trade_no（订单号，重试时原样带回）

1. 完成支付：调用支付插件 weixinpay_pay(paymentCode=<payment_code>)，向用户申请支付授权。
2. 重试取内容：支付成功后，用完全相同 的 JSON body 重试 POST /api/pay/orders，并在 Header 中带上订单号：

   POST /api/pay/orders
   Content-Type: application/json
   X-Out-Trade-No: <out_trade_no>
   WeixinPay-Required: <payment_code>

   {"query": "...", "skill_id": "excel-data-cleaner-handle", "skill_version": "1.0.0", "description": "...", "amount_total": 199}

返回 HTTP 200 且 code=SUCCESS 即为履约成功，Agent 可继续执行下方清洗流程。

重试与错误处理

铁律：

• 原样重试：重试时 JSON body 必须与首次完全一致，订单参数只能通过 Header 传递，禁止改动 query 等业务字段来传订单号。
• 幂等：同一订单重复重试返回缓存结果（already_fulfilled=true），不要重复向用户收费。
• 有效期：payment_code 有效期 15 分钟，过期后需重新下单获取新 payment_code。

工作流程

Phase 1：扫描诊断

读取所有 Sheet，逐列检查数据质量。检查维度：

输出：问题清单表格（行号 + 当前值 + 问题描述 + 建议方案），按优先级分级（P0必须/P1重要/P2规范/P3待定）。

Phase 2：方案确认

将问题清单呈给用户，等待确认每项处理方式。对于不确定项（如虚构数据、模糊地址），明确询问保留还是修复。禁止跳过此阶段直接清洗。

Phase 3：执行清洗

按确认后的规则执行。核心原则：

1. 修复类（直接改值）：
   • 编号格式统一：数字 → 前缀+零填充（如 6 → EMP0006）
   • 文本清洗：去除空格、数字、标点，只保留目标字符集（如中文姓名只留 u4e00-u9fa5）
   • 数值修复：负值取绝对值（如月薪 -29460 → 29460）
   • 枚举映射：拼音→中文（如 boshi → 博士，shuoshi → 硕士）
   • 地址错别字：直接替换（如 程度市 → 成都市，大通市 → 大同市）
   • 列名统一：跨表同义不同名列名对齐（如 学号 → 员工编号）
   • 排序：按主键升序重排全表
   • 日期格式：统一 yyyy/mm/dd
   • 空 Sheet：删除
2. 标注类（不改值，标颜色）：
   • 主键重复 → 🟡 黄色标注（#FFFFFF00）
   • 跨表日期矛盾（如离职早于入职）→ 🟠 橙色标注（#FF8C00）
   • 枚举异常值（如性别非男/女）→ 🟡 黄色标注
3. 保留类（教学/业务需要，不动）：
   • 虚构路名等教学数据
   • 粒度不一致但有意保留的地址

Phase 4：数据增强

清洗完成后，自动执行增强操作：

4.1 跨表关联（VLOOKUP）

扫描所有 Sheet，找出可通过主键关联的数据列，自动 VLOOKUP 到主表。

• 按主键（如员工编号）匹配
• 重复主键取较晚日期（或按业务逻辑确认）
• 无匹配行留空

4.2 衍生列计算

当存在多个有逻辑关系的日期列时，自动计算间隔：

衍生列为负值时（日期冲突），橙色标注。

4.3 地址解析

当地址列存在时，自动拆分为省份和城市：

• 直辖市（北京市/天津市/上海市/重庆市）：省份 = 城市 = 市名
• 自治区：匹配 XX维吾尔自治区、XX壮族自治区、XX回族自治区、XX自治区
• 省：匹配 XX省，后接 XX市 提取城市
• 无法解析的地址留空

新增列插入位置：省份、城市紧跟地址列之后；离职日期、在职天数紧跟入职日期之后。

Phase 5：验证交付

逐项验证清洗结果：

• 修复类：检查目标值是否全部替换（如负值数=0、非中文姓名数=0）
• 标注类：检查颜色标注数量是否匹配
• 增强类：检查 VLOOKUP 匹配数、衍生列计算正确性、地址解析覆盖率
• 日期格式：检查 number_format 是否为 yyyy/mm/dd
• 表头样式：深色背景 + 白色加粗字 + 居中 + 边框

验证通过后输出文件，附清洗摘要表。

表格样式规范

技术要求

• 使用 openpyxl 读写 Excel（保留格式、颜色、样式）
• 使用 pandas 做数据分析和验证
• Python 脚本中用正则 re 模块做文本清洗和地址解析
• 输出文件命名：原文件名-已清洗.xlsx（清洗阶段）、原文件名-已增强.xlsx（增强阶段）
• 也可合并为一步：原文件名-已增强.xlsx

执行铁律

1. 先方案后执行：Phase 1 扫描 → Phase 2 确认 → Phase 3 执行，禁止跳步
2. 不改标注类数据：重复、日期矛盾只标颜色，不修改原值
3. 保留教学数据：虚构地址等有意保留的数据不动
4. 跨表必须关联：发现可关联的列自动 VLOOKUP，不遗漏
5. 衍生列自动算：有逻辑关系的日期列自动计算间隔天数
6. 地址自动拆：有地址列就拆省份+城市
7. 验证不能省：每项修复都要用 pandas 验证结果