--- name: csv-data-cleaning-engine description: CSV数据清洗与格式化专家决策引擎。提供数据质量诊断、去重策略(精确/模糊/规则)、格式标准化(日期/金额/编码)、异常值处理、批量处理方案、质量门控清单、常见错误模式。当用户需要CSV数据清洗、去重、格式化、数据整理、Excel数据处理、Pandas数据清洗时使用此技能。 --- # CSV数据清洗专家决策引擎 ## 触发条件 当用户提到以下关键词时触发：CSV数据清洗、去重、格式化、数据整理、Excel数据处理、Pandas数据清洗、数据质量诊断、异常值处理、批量数据清洗 ## 1. 数据质量诊断（5维度评估） | 维度 | 定义 | 诊断指标 | 工具 | |------|------|----------|------| | 完整性 | 数据是否有缺失 | 缺失率/字段空值率 | `df.isnull().sum()` / `df.info()` | | 一致性 | 同一实体在不同记录中是否一致 | 矛盾记录数/格式不统一数 | `df.duplicated()` / 正则校验 | | 准确性 | 数据是否反映真实值 | 异常值数量/逻辑矛盾数 | IQR/Z-score/业务规则校验 | | 时效性 | 数据是否过时 | 超期记录占比 | 日期字段与当前时间比对 | | 唯一性 | 是否存在重复记录 | 重复率/近似重复率 | 精确去重/模糊匹配 | **快速诊断脚本模板：** ## 2. 去重策略决策树 **模糊去重示例：** ## 3. 格式标准化 ### 3.1 日期标准化 ### 3.2 金额标准化 ### 3.3 编码处理 ### 3.4 电话号码标准化 ## 4. 异常值处理 | 方法 | 适用场景 | 实现方式 | 注意事项 | |------|----------|----------|----------| | IQR法 | 数值型、近似正态分布 | Q1-1.5*IQR ~ Q3+1.5*IQR | 对偏态分布不友好 | | Z-score | 正态分布数值 | \|z\| > 3 判为异常 | 样本量需≥30 | | 业务规则 | 有明确业务逻辑 | 价格<0、年龄>150等 | 需要领域知识 | ## 5. 工具选型决策 | 场景 | Pandas | Polars | DuckDB | |------|--------|--------|--------| | 数据量 <1GB | ✅ 首选 | ✅ 更快 | — | | 数据量 1-10GB | ⚠️ 需分块 | ✅ 首选 | ✅ 首选 | | 数据量 >10GB | ❌ OOM风险 | ✅ 可处理 | ✅ 首选 | | SQL技能强 | — | — | ✅ 首选 | | 需要复杂转换 | ✅ 生态最全 | ✅ 表达式强 | ⚠️ 有限 | | 并行处理 | ❌ 单线程 | ✅ 自动并行 | ✅ 向量化 | **Python 3.14注意**：PEP 779正式支持自由线程(去除GIL)，Pandas/Polars可直接受益于多线程，但单线程性能损耗约10-15%，内存增加约20%。 ## 6. 批量处理方案 ### 6.1 分块读取 ### 6.2 内存优化 ## 7. 质量门控 ### P0 阻塞级（必须修复） - 数据缺失率 >50% 的关键字段 - 去重后行数变化 >30%（可能有系统性重复） - 编码错误导致乱码 - 金额/日期字段无法解析 ### P1 警告级（建议修复） - 个别列缺失率 10-50% - 异常值占比 >5% - 格式不完全统一（如日期混用/- - 数据类型推断不一致 ### P2 提示级（可选优化） - 内存使用 >500MB（建议优化dtypes） - 列名含空格或特殊字符 - 存在完全为空的行/列 - 数值精度过高（>6位小数） ## 8. 常见错误（Top 10） | # | 错误 | 症状 | 解决方案 | |---|------|------|----------| | 1 | BOM头导致首列解析失败 | 列名带\ufeff前缀 | `encoding='utf-8-sig'`读取 | | 2 | 编码混用 | 部分行乱码 | chardet逐块检测+统一转UTF-8 | | 3 | 日期格式不一致 | 2024/1/1和2024-01-01混存 | `pd.to_datetime(errors='coerce')` | | 4 | 金额科学计数法 | 1.23E+05 | 先转字符串再转数值 | | 5 | 隐形空白字符 | 去重失败/匹配不上 | `str.strip()` + `str.replace('\xa0','')` | | 6 | 数字被读为字符串 | 列含"1,234"或"N/A" | `pd.to_numeric(errors='coerce')` | | 7 | 换行符在字段内 | CSV解析错位 | `quoting=csv.QUOTE_ALL` | | 8 | 重复表头 | 列名出现两次 | `df[df[col] != col]`过滤 | | 9 | NULL vs 空字符串混淆 | 缺失值统计不准 | 统一为`np.nan`再处理 | | 10 | 大文件内存溢出 | OOM | 分块读取+Polars/DuckDB | ## 9. 输出格式 清洗完成后输出： 1. **清洗报告**：原始行数→清洗后行数，各步骤处理行数，异常值统计 2. **处理后的CSV文件**：UTF-8编码（无BOM），标准日期格式，数值精度合理 3. **问题记录**：无法自动处理的行单独列出，供人工审核 ## 10. 2026平台实战洞察 ### UUMit任务实战要点 - CSV整理任务(51UT)是高频需求，用户通常需要：去重+格式标准化+空值处理一站式完成 - Word模板任务常与CSV配套，清洗后的数据需可直接导入Word邮件合并 - 交付物必须包含清洗报告，让用户理解每步处理了什么 ### 自媒体数据清洗场景 - 抖音/小红书导出的评论数据：emoji编码、@用户名、HTML实体需清理 - 电商订单数据：地址标准化、手机号去重、金额汇总 - 问卷数据：开放题清洗、量表题反向计分 ## 六、Pandas数据清洗实战代码库 ### 6.1 数据质量一键诊断 ### 6.2 智能去重（精确+模糊+规则） ### 6.3 日期格式标准化 ### 6.4 金额格式标准化 ### 6.5 批量处理大数据文件 ## 七、常见数据清洗场景速查 | 场景 | 问题 | 解决方案 | 关键代码 | |------|------|---------|---------| | 手机号清洗 | 格式不统一(含+86/空格/-) | 正则统一为11位 | `re.sub(r'[^\d]', '', s)[-11:]` | | 身份证校验 | 15位/18位/含X | 长度+校验位验证 | `len(s) in [15,18] and check_digit(s)` | | 地址标准化 | 省/市/区/详细地址混乱 | 分列+字典映射 | `df['地址'].str.extract(r'(.+省)(.+市)(.+区)')` | | 编码统一 | UTF-8/GBK/GB2312混用 | chardet检测+统一转码 | `pd.read_csv(f, encoding=chardet.detect(raw)['encoding'])` | | 合并单元格 | Excel合并导致NaN | ffill填充+重新对齐 | `df.fillna(method='ffill')` | | 多Sheet合并 | 多个Sheet需纵向拼接 | 循环读取+标识来源 | `pd.concat([pd.read_excel(f, sheet_name=s) for s in sheets])` | | JSON字段展开 | 列中含JSON字符串 | json_normalize展开 | `pd.json_normalize(df['json_col'].apply(json.loads))` | | 重复列名 | CSV有重复列名 | rename+序号标记 | `df.columns = pd.io.parsers.base_parser.ParserBase._maybe_dedup_names(df.columns)` | ## 八、2026数据清洗洞察 - **AI辅助清洗**：GPT-4/Claude可直接理解清洗意图，生成Pandas代码，效率提升5-10倍 - **自动化数据质量监控**：Great Expectations + Airflow实现数据管道质量门控 - **Polars替代Pandas**：大数据场景(>1GB)Polars性能比Pandas快5-20倍 - **DuckDB直接查询CSV**：无需加载到内存，直接SQL查询本地CSV文件 - **实时数据清洗**：流式处理(Flink/Kafka)替代批量ETL，延迟降至秒级 --- ## 九、数据清洗高级技巧 ### 9.1 正则表达式深度应用 ### 9.2 数据类型自动推断 ### 9.3 条件筛选与过滤 --- ## 十、数据验证与质量报告 ### 10.1 Great Expectations 集成 ### 10.2 业务规则验证 --- ## 十一、大数据处理方案 ### 11.1 Polars 高性能处理 ### 11.2 DuckDB SQL查询 ### 11.3 分区处理策略 --- ## 十二、数据清洗SOP ### SOP-1: 通用数据清洗流程 | 阶段 | 步骤 | 操作 | 工具/代码 | |------|------|------|----------| | 1.加载数据 | 1.1 检测编码 | 自动检测文件编码 | chardet | | 1.加载数据 | 1.2 读取文件 | 读取CSV/Excel | pd.read_csv/read_excel | | 1.加载数据 | 1.3 数据预览 | 查看前几行和数据类型 | df.head(), df.info() | | 2.数据诊断 | 2.1 缺失值分析 | 统计各列缺失情况 | df.isnull().sum() | | 2.数据诊断 | 2.2 重复值分析 | 检测重复行 | df.duplicated().sum() | | 2.数据诊断 | 2.3 数据类型检查 | 验证列类型 | df.dtypes | | 2.数据诊断 | 2.4 异常值检测 | IQR/Z-score分析 | 自定义函数 | | 3.清洗处理 | 3.1 缺失值处理 | 删除/填充/插值 | df.fillna()/dropna() | | 3.清洗处理 | 3.2 去重处理 | 删除重复行 | df.drop_duplicates() | | 3.清洗处理 | 3.3 格式标准化 | 日期/金额/编码 | 自定义函数 | | 3.清洗处理 | 3.4 异常值处理 | 替换/删除/标记 | 自定义函数 | | 4.数据转换 | 4.1 类型转换 | 转为合适的数据类型 | astype() | | 4.数据转换 | 4.2 特征工程 | 派生新列 | df.assign() | | 4.数据转换 | 4.3 数据归一化 | MinMax/Standard | sklearn | | 5.质量验证 | 5.1 完整性检查 | 确保无空值 | df.isnull().sum() | | 5.质量验证 | 5.2 一致性检查 | 确保格式统一 | 自定义检查 | | 5.质量验证 | 5.3 业务规则验证 | 逻辑正确性 | validator | | 6.导出数据 | 6.1 格式选择 | CSV/Parquet/Excel | df.to_csv/to_parquet | | 6.导出数据 | 6.2 编码设置 | UTF-8无BOM | encoding参数 | | 6.导出数据 | 6.3 索引处理 | 是否保留索引 | index参数 | ### SOP-2: 电商订单数据清洗流程 | 阶段 | 步骤 | 操作 | 验证标准 | |------|------|------|----------| | 1.数据导入 | 1.1 读取订单数据 | UTF-8编码读取 | 行列数正确 | | 1.数据导入 | 1.2 解析嵌套数据 | JSON展开 | 字段完整 | | 2.字段清洗 | 2.1 手机号清洗 | 正则提取+标准化 | 11位有效手机号 | | 2.字段清洗 | 2.2 地址清洗 | 省市区分离 | 标准行政区划 | | 2.字段清洗 | 2.3 金额清洗 | 去除符号，保留小数 | 两位小数 | | 2.字段清洗 | 2.4 时间清洗 | 标准化为datetime | YYYY-MM-DD HH:MM:SS | | 3.数据校验 | 3.1 订单号唯一性 | 去重检查 | 无重复订单号 | | 3.数据校验 | 3.2 金额逻辑 | 数量×单价=总价 | 误差<0.01 | | 3.数据校验 | 3.3 收货地址完整性 | 非空检查 | 省市区完整 | | 4.数据统计 | 4.1 异常订单标记 | 价格异常/数量异常 | flag字段 | | 4.数据统计 | 4.2 订单状态标准化 | 状态归一化 | 标准状态值 | | 5.数据导出 | 5.1 分区存储 | 按日期分区 | Parquet格式 | | 5.数据导出 | 5.2 生成报告 | 清洗统计报告 | 包含处理数量 | ### SOP-3: 用户数据清洗流程 | 阶段 | 步骤 | 操作 | 质量标准 | |------|------|------|----------| | 1.数据获取 | 1.1 读取用户表 | 多源数据合并 | 字段对齐 | | 1.数据获取 | 1.2 去重合并 | 同用户多账号合并 | 保留最新 | | 2.身份信息 | 2.1 姓名清洗 | 去除特殊字符 | 纯中文/英文 | | 2.身份信息 | 2.2 手机号清洗 | 去重+标准化 | 11位+86前缀 | | 2.身份信息 | 2.3 邮箱清洗 | 格式验证+小写 | 有效邮箱格式 | | 2.身份信息 | 2.4 身份证验证 | 15/18位+校验位 | 通过校验算法 | | 3.地址信息 | 3.1 地址标准化 | 省市区街道分离 | 标准行政区划 | | 3.地址信息 | 3.2 坐标补充 | 地址转经纬度 | 高德/百度API | | 4.活跃分析 | 4.1 注册时间标准化 | 统一时区 | UTC+8 | | 4.活跃分析 | 4.2 最后活跃计算 | 计算时间差 | 精确到天 | | 5.标签体系 | 5.1 基础标签 | 性别/年龄/地区 | 标准枚举值 | | 5.标签体系 | 5.2 行为标签 | RFM分析 | R/F/M分数 | | 5.标签体系 | 5.3 偏好标签 | 兴趣分析 | 标签权重 | --- ## 十三、30+常见错误模式 | # | 错误类型 | 症状表现 | 原因分析 | 解决方案 | |---|----------|----------|----------|----------| | 1 | BOM头乱码 | 首列名带\ufeff | UTF-8 with BOM | encoding='utf-8-sig' | | 2 | 编码检测失败 | 读取后全是None | chardet置信度低 | 手动指定编码 | | 3 | 日期格式不一致 | 2024/1/1与2024-01-01 | 多源数据混用 | pd.to_datetime统一 | | 4 | 科学计数法 | 1.23E+05显示 | Excel自动转换 | 读取时指定dtype | | 5 | 隐形空格 | 去重失效/匹配不上 | 字符串首尾空格 | strip()清理 | | 6 | 数字字符串 | "1,234"被当作文本 | 含逗号的数字 | str.replace(',','') | | 7 | 空字符串陷阱 | isna()检测不到 | 空串≠NaN | replace('',np.nan) | | 8 | 换行符在字段内 | CSV解析错位 | 字段内含\n | quoting参数 | | 9 | 重复表头行 | 列名重复出现 | 首行非表头 | 跳过首行 | | 10 | 大文件OOM | MemoryError | 一次性加载 | 分块读取 | | 11 | 时区混乱 | 时间差8小时 | UTC与本地时差 | 统一时区 | | 12 | 精度丢失 | 浮点数比较失败 | float精度问题 | Decimal类型 | | 13 | 索引混乱 | iloc与loc混用出错 | 索引被修改 | 重置索引 | | 14 | 列类型不一致 | merge后类型改变 | object+int | 转换统一类型 | | 15 | 中文编码问题 | 写入后乱码 | 系统默认编码 | utf-8编码 | | 16 | 缺失值填充陷阱 | 填充后均值变小 | 填充值影响统计 | 先分析再填充 | | 17 | 字符串大小写 | 匹配失败 | 大小写不一致 | str.lower()统一 | | 18 | 特殊字符未转义 | CSV格式错误 | 逗号/引号未转义 | quoting | | 19 | 日期边界错误 | 月末日期计算错 | 月份天数不同 | 使用dateutil | | 20 | JSON嵌套解析 | 展开后数据丢失 | 嵌套层级过深 | jsonpath提取 | | 21 | Excel日期转换 | 数字变成日期 | Excel日期序列 | 转换为天数 | | 22 | 合并冲突 | merge后行数暴增 | 一对多关系 | 检查重复键 | | 23 | 列名空格 | df[' 列']找不到 | 列名含空格 | strip列名 | | 24 | NaN参与运算 | sum结果偏小 | NaN+number=NaN | skipna=True | | 25 | 布尔值混淆 | True变成1 | dtype=bool | 保持object类型 | | 26 | 分组聚合丢失 | 某组数据消失 | 分组键含NaN | fillna处理 | | 27 | 时间戳误解 | 秒当毫秒用 | 单位不一致 | 统一单位 | | 28 | 浮点比较 | 0.1+0.2≠0.3 | 浮点精度问题 | math.isclose() | | 29 | 类型推断失败 | 整列变NaN | 含有异常值 | errors='coerce' | | 30 | 内存泄漏 | df越来越大 | 循环中未释放 | del+gc.collect | | 31 | 分块处理丢失 | 部分数据丢失 | offset计算错 | 验证总行数 | | 32 | 正则性能问题 | 处理超时 | 贪婪匹配/回溯 | 优化正则表达式 | --- ## 十四、质量门控体系（三级清单） ### P0 级：阻塞性问题（必须修复） | 检查项 | 验收标准 | 检测方法 | 修复方案 | |--------|----------|----------|----------| | 关键字段缺失 | 缺失率 < 5% | df[col].isnull().mean() | 删除或填充 | | 重复行比例 | < 1% | df.duplicated().mean() | drop_duplicates | | 编码错误 | 无乱码 | chardet检测 | 重新指定编码 | | 数值解析失败 | 全部可解析 | pd.to_numeric(errors='coerce') | 清理格式 | | 日期解析失败 | > 90%可解析 | pd.to_datetime校验 | 标准化格式 | ### P1 级：功能性问题（建议修复） | 检查项 | 验收标准 | 检测方法 | 修复方案 | |--------|----------|----------|----------| | 非关键字段缺失 | 缺失率 < 30% | 缺失率统计 | 填充或标记 | | 异常值比例 | < 5% | IQR/Z-score检测 | 标记或调整 | | 格式一致性 | > 90%统一 | 正则校验 | 标准化处理 | | 数据类型正确性 | 类型推断准确 | dtype验证 | 类型转换 | | 唯一性约束 | ID/手机等唯一 | duplicated检测 | 去重或合并 | ### P2 级：体验优化项（可选修复） | 检查项 | 验收标准 | 检测方法 | 修复方案 | |--------|----------|----------|----------| | 内存占用 | < 500MB | memory_usage | dtype优化 | | 处理速度 | < 10秒/GB | time.time() | 并行处理 | | 列名规范性 | 无空格/特殊字符 | 列名校验 | rename | | 数据完整性 | > 95%有效 | 业务规则验证 | 补充数据 | | 可追溯性 | 保留清洗日志 | 记录处理步骤 | 生成报告 | --- ## 十五、2026年数据清洗趋势 ### 15.1 AI辅助数据清洗 | 技术 | 说明 | 工具 | |------|------|------| | 自然语言清洗 | 用自然语言描述清洗规则 | GPT-4/Claude | | 自动模式识别 | AI识别数据模式并清洗 | cleanlab | | 智能填充 | 基于历史数据智能填充空值 | miceforest | | 异常检测 | 无监督异常检测 | PyOD | ### 15.2 实时数据处理 | 技术 | 说明 | 场景 | |------|------|------| | 流处理 | Flink/Kafka实时清洗 | 日志/传感器 | | CDC | 变更数据捕获 | 数据库同步 | | 事件驱动 | 事件触发清洗任务 | 实时报表 | ### 15.3 新兴数据格式 | 格式 | 优势 | 适用场景 | |------|------|----------| | Parquet | 列式存储/压缩率高 | 大数据分析 | | Arrow | 零拷贝读取 | Python生态 | | Delta Lake | ACID事务/版本控制 | 数据湖 | | Iceberg | 大表支持/时间旅行 | 大数据湖 | ### 15.4 自动化工具链 - **Apache DolphinScheduler**: 工作流调度 - **Airflow**: 数据管道编排 - **Great Expectations**: 数据质量监控 - **dbt**: 数据转换工具 - **Datafold**: 数据差异检测 --- ## 十六、高级数据转换技术 ### 16.1 数据透视与逆透视 ### 16.2 时间序列处理 ### 16.3 文本数据处理 --- ## 十七、数据库交互与SQL ### 17.1 SQL查询构建器 ### 17.2 数据迁移工具 --- ## 十八、性能优化与最佳实践 ### 18.1 内存优化技巧 ### 18.2 并行处理 --- ## 十九、完整项目模板 ### 19.1 数据清洗项目结构 ### 19.2 清洗配置文件模板 --- ## 二十、实战案例集 ### 案例1：电商订单数据清洗 | 步骤 | 操作 | 验证 | |------|------|------| | 1 | 读取订单CSV (100万行) | 行列数正确 | | 2 | 展开JSON字段 (items) | 订单商品明细完整 | | 3 | 清洗手机号 (正则+标准化) | 11位有效手机 | | 4 | 分离收货地址 (省市区) | 标准行政区划 | | 5 | 清洗金额 (去符号+小数位) | 两位小数 | | 6 | 标准化时间 (统一时区) | UTC+8 | | 7 | 订单号去重 | 无重复订单 | | 8 | 金额逻辑校验 | 数量×单价=总价 | | 9 | 导出Parquet | 文件正常 | ### 案例2：用户画像数据整合 | 步骤 | 操作 | 工具 | |------|------|------| | 1 | 读取多源数据 (4个表) | pandas | | 2 | 姓名清洗 (去除特殊字符) | 正则 | | 3 | 手机号去重+标准化 | 自定义函数 | | 4 | 邮箱格式验证 | 正则 | | 5 | 身份证校验 | 校验算法 | | 6 | 地址结构化 | 省市区分离 | | 7 | 用户合并 (同一人多账号) | merge | | 8 | RFM分析 | 分箱统计 | | 9 | 导出用户画像表 | parquet | ### 案例3：日志数据处理 | 步骤 | 操作 | 说明 | |------|------|------| | 1 | 按行读取日志文件 | 分块读取 | | 2 | 解析JSON格式日志 | json.loads | | 3 | 提取关键字段 | 正则提取 | | 4 | 时间戳标准化 | UTC转本地 | | 5 | IP地址解析 | geoip2 | | 6 | 异常请求标记 | 规则匹配 | | 7 | 聚合统计 | groupby | | 8 | 导出统计报表 | Excel |