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

中文脏数据清洗

专治中文场景脏数据：地址(省市区拆分/统一)、手机号(11 位校验/号段识别)、姓名(姓氏分离/复姓处理)、日期(中文/数字混排)、身份证(校验/省份/年龄)。提供 Excel 公式、Python pandas、SQL 三种实现。

person作者: user_36c7efa2hubcommunity
download下载资源包

中文脏数据清洗

这个 skill 解决什么

通用 AI / 通用清洗工具在英文场景跑得欢，到中文场景就翻车——因为：

  • 中文地址不像英文有逗号分隔（"北京市朝阳区建国路 88 号 SOHO 现代城" 一字不分隔）
  • 中文姓名有复姓（欧阳、司马、上官 …… 60+ 种）
  • 中国手机号有 100+ 个号段、3 大运营商、虚商号段、靓号段
  • 中国身份证编码内含省份 / 出生日期 / 性别 / 校验位（外国人查不到）
  • 中国日期可能写成"2024 年 3 月"、"24/3"、"二零二四年三月"……

这个 skill 提供 5 大场景的中文脏数据清洗规则、工具实现、边界处理。

不做的事：不做反洗钱真实身份核验（这是公安系统的事）；不做手机号实名认证（这是运营商的事）；这里只做格式校验和字段提取。

5 大脏数据场景

场景 1：地址拆分（省 / 市 / 区 / 街道 / 详细）

典型脏数据：

- "北京朝阳建国路 88 号 SOHO 现代城 A 座 2008 室"
- "北京市朝阳区建国路 88 号"
- "北京 朝阳区 建国路"
- "北京市朝阳区 建国路 88"
- "上海浦东新区世纪大道 100 号"
- "广州天河区珠江新城猎德大道 1 号 G 座"

标准化目标：

省 = "北京市"
市 = "北京市" / "上海市" / "广州市"
区 = "朝阳区" / "浦东新区" / "天河区"
街道/镇 = "建国路" / "世纪大道" / "猎德大道"
详细 = "88 号 SOHO 现代城 A 座 2008 室"

实现思路：

  1. 维护省/市/区清单（民政部行政区划代码，约 3000 条）
  2. 从长到短匹配（先匹配 4 字"内蒙古自治区"，再匹配 3 字"内蒙古"）
  3. 直辖市处理（北京 / 上海 / 天津 / 重庆 的"省 = 市"）
  4. 港澳台处理（特别行政区 / 不分到区）

Python 实现（推荐）：

# pip install cpca
import cpca
df = cpca.transform(["北京朝阳建国路 88 号"])
# 返回 DataFrame：省、市、区、地址（剩余）

cpca 是中国地址解析的最佳开源库，准确率 95%+，覆盖港澳台。

Excel 实现：通过 VLOOKUP + 行政区划清单（30 行公式可解决省 / 市 / 区拆分），但维护成本高，建议导入 Python 处理。

场景 2：手机号校验与运营商识别

典型问题：

  • 11 位但不合法（12345678901 不是真号段）
  • 带 +86 / 86 / 0086 前缀
  • 带空格 / 横线 / 括号（186-1234-5678）
  • 写成全角数字
  • 7 位 / 8 位的座机号混在手机号列里

校验规则（2024 现行）：

1[3-9]\d{9}    # 13X 14X 15X 16X 17X 18X 19X 开头，共 11 位

号段 → 运营商映射（按段，不是按 3 位）：

中国移动：134-139, 147, 148, 150-152, 157-159, 165, 172, 178, 182-184, 187, 188, 195, 197, 198
中国联通：130-132, 145, 146, 155, 156, 166, 167, 171, 175, 176, 185, 186, 196
中国电信：133, 149, 153, 173, 174, 177, 180, 181, 189, 190, 191, 193, 199
中国广电：192
虚拟运营商：162, 165, 167, 170, 171

Python 实现：

# pip install phonenumbers
import phonenumbers
from phonenumbers import carrier
n = phonenumbers.parse("18612345678", "CN")
phonenumbers.is_valid_number(n)        # True/False
carrier.name_for_number(n, "zh")       # "中国移动"

Excel 实现：

校验：=IF(AND(LEN(A2)=11, LEFT(A2,1)="1", VALUE(MID(A2,2,1))>=3), "合法", "不合法")
清洗：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"+86","")

场景 3：中文姓名拆分（处理复姓）

典型问题：

  • 单姓 vs 复姓判断（"诸葛亮" 不是 "诸 / 葛亮"）
  • 4 字姓名（可能复姓 + 双名 OR 单姓 + 三字名）
  • 少数民族姓名（"古丽尼莎汗·吐尔逊" 用·分隔）
  • 港澳台姓名（"陈奕迅" / "周杰倫" 简繁混排）

复姓清单（60 个常见，需维护）：

欧阳 司马 上官 司徒 诸葛 东方 端木 慕容 皇甫 尉迟
公孙 长孙 宇文 单于 钟离 闾丘 太史 申屠 公冶 仲孙
轩辕 令狐 钟离 宗政 濮阳 淳于 第五 太叔 闻人 …

实现规则：

1. 名字含 ·（中点）→ 少数民族 → 整名不拆
2. 长度 == 2 → 姓 = 第 1 字，名 = 第 2 字
3. 长度 >= 3 → 取前 2 字查复姓表
   - 命中 → 姓 = 前 2 字，名 = 剩余
   - 未命中 → 姓 = 第 1 字，名 = 剩余

Python 实现：

COMPOUND = {"欧阳", "司马", "诸葛", ...}  # 60 个

def split_name(name: str):
    if "·" in name:
        return ("", name)  # 少数民族整名
    if len(name) == 2:
        return (name[0], name[1])
    if name[:2] in COMPOUND:
        return (name[:2], name[2:])
    return (name[0], name[1:])

场景 4：日期统一（多种中英文混排）

典型脏数据：

"2024年3月15日"
"2024-3-15"
"2024.3.15"
"24/3/15"
"3/15/24"        # 美式
"15/3/2024"      # 欧式
"二零二四年三月十五日"   # 中文数字
"2024 年 3 月"     # 缺日
"2024 春节后"      # 模糊
"昨天"           # 相对时间

Python pandas 实现：

import pandas as pd

# 90% 场景能搞定
df["日期"] = pd.to_datetime(df["日期"], errors="coerce")

# 中文数字 → 阿拉伯数字
import cn2an
"二零二四年三月" → cn2an.cn2an("二零二四") + "年3月"

# 模糊日期映射（"昨天"、"上周一" → 具体日期）
import dateparser
dateparser.parse("昨天", languages=["zh"])

Excel 实现：

  • 用 DATEVALUE 转字符串日期
  • 中文格式："2024 年 3 月 15 日" 用 DATE(YEAR + MONTH + DAY 拆出来的数字) 重建
  • 中文数字字符串 → 没有内置函数，需 VBA

场景 5：身份证号校验与字段提取

18 位身份证编码：

1-6 位：行政区划代码（省/市/区）
7-14 位：出生年月日
15-17 位：顺序码（第 17 位偶数为女、奇数为男）
18 位：校验码（按前 17 位算的 ISO 7064 模 11-2 校验）

校验码算法：

WEIGHTS = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2]
CHECK_CODES = "10X98765432"

def check_id(idnum: str) -> bool:
    if len(idnum) != 18:
        return False
    s = sum(int(c) * w for c, w in zip(idnum[:17], WEIGHTS))
    return CHECK_CODES[s % 11] == idnum[17].upper()

字段提取：

def parse_id(idnum: str) -> dict:
    return {
        "省份": REGION_MAP[idnum[:2]],   # 11=北京 31=上海 ...
        "出生日期": f"{idnum[6:10]}-{idnum[10:12]}-{idnum[12:14]}",
        "性别": "男" if int(idnum[16]) % 2 == 1 else "女",
        "年龄": today.year - int(idnum[6:10]),
    }

重要警告：

  • ⚠️ 身份证号是敏感个人信息，处理时遵守《个人信息保护法》
  • ⚠️ 不要在日志 / 数据库明文存储完整身份证号，必须脱敏（中间 8 位用 *）
  • ⚠️ 不向用户输出"用户的真实姓名 + 完整身份证号"组合

AI 执行流程

第一步：摸场景

  1. 你的数据有多少行？（>10 万行优先 Python，<1 万行 Excel 够用）
  2. 你想清洗的字段是什么？（地址 / 手机 / 姓名 / 日期 / 身份证 / 其他）
  3. 你用什么工具？（Excel 公式 / Python pandas / SQL）
  4. 数据示例发 5-10 条来（看真实脏度）

第二步：诊断脏度

扫一遍样本，列出"脏的形态"：

  • 空值占比
  • 格式不统一的有几种
  • 异常值占比（不合法的、明显错的）
  • 重复值

第三步：给清洗方案

按用户用的工具给：

  • Excel：公式 + 操作步骤（截图描述）
  • Python：pip install + 代码 + 输出格式
  • SQL：原生函数 + 正则 + UPDATE 语句

第四步：自检 checklist

  • [ ] 边界 case 覆盖了吗？（空值、超长、特殊字符、外文）
  • [ ] 性能可接受？（10 万行 Excel 公式可能跑 30 分钟，建议 Python）
  • [ ] 涉及敏感信息（手机号、身份证）有没有提示脱敏？
  • [ ] 不可逆操作有没有"先备份"提醒？

输出格式

## 你的需求
（复述）

## 脏度诊断
- 总行数：X
- 字段：A / B / C
- 主要脏点：
  - X% 行有空值
  - X% 行格式不统一
  - X% 行明显异常

## 清洗方案（你用 Python pandas）

### Step 1：读取
```python
df = pd.read_excel("data.xlsx")

Step 2：地址拆分

import cpca
result = cpca.transform(df["地址"])
df[["省", "市", "区", "详细"]] = result[["省", "市", "区", "地址"]]

Step 3：手机号清洗

df["手机号_清洗"] = df["手机号"].str.replace(r"\D", "", regex=True).str[-11:]
df["手机号_合法"] = df["手机号_清洗"].str.match(r"1[3-9]\d{9}")

Step 4：保存

df.to_excel("data_clean.xlsx", index=False)

注意事项

  • 处理前请先备份 data.xlsx → data.xlsx.bak
  • 身份证字段已要求脱敏，不会保留完整号
  • cpca 库准确率约 95%，5% 异常地址会进"省/市为空"，需人工复核

---

## 终止条件

- 用户拿到了可执行的清洗代码 / 公式
- 边界 case 提示完整
- 用户没追问"那 X% 怎么办"

不主动做数据可视化、入库、分析（不是清洗的范畴）。