4、数据清洗与准备：为精算建模打造干净可靠的数据源
精算建模的成败，往往在数据进入模型之前就已注定。无论是寿险准备金评估、非寿险费率厘定，还是资产负债匹配分析，模型输出的可信度直接取决于输入数据的质量。实践中，精算师花费在数据清洗与准备上的时间通常占整个建模周期的 50% 以上，这并非效率低下，而是对模型稳健性的必要投资。本章将系统讲解从数据导入到数据验证的完整链路，帮助您建立一套可复用、可审计的数据预处理框架。
4.1 数据导入：从源头控制质量
核心原理：数据导入是数据清洗的第一道关口。精算建模常用的数据来源包括保险公司核心系统的导出文件（CSV、TXT）、再保人提供的报表（Excel）、以及数据库查询结果（SQL Server、Oracle）。导入阶段的核心任务不仅是读取数据，更是识别数据类型、检查编码格式、确认字段含义，为后续清洗建立基础。
操作流程：
- 文件格式检查：确认文件编码（UTF-8、GBK）、分隔符（逗号、制表符、分号）、日期格式（YYYY-MM-DD vs MM/DD/YYYY）。
- 字段类型预判：在 Excel 中使用 Power Query（数据 > 获取数据 > 自文件）导入时，提前指定每列的数据类型（文本、整数、小数、日期、布尔值）。
- 首行检查：确认首行是否为字段名，是否存在合并单元格、标题行、备注行等干扰信息。
- 数据量核对：导入后立即对比源文件行数与导入行数，发现差异需排查原因。
| 数据源类型 | 常见问题 | 推荐导入方式 |
|---|---|---|
| CSV 文件 | 编码混乱、字段中包含逗号 | Power Query 指定分隔符与编码 |
| Excel 报表 | 多 Sheet、合并单元格、公式残留 | 仅选择数据区域，关闭“启用加载” |
| SQL 查询结果 | 字段类型隐式转换、NULL 值 | 使用 ODBC 连接，保留原生类型 |
4.2 去重：识别并消除重复记录
核心原理：重复数据会导致精算模型中的计数指标虚高、聚合结果失真。去重并非简单地删除重复行，而是需要区分“完全重复”和“部分重复”。完全重复指所有字段完全一致；部分重复则指业务主键相同但其他字段存在差异，例如同一保单号下有多条不同状态的记录。
判断方法：
- 完全重复：使用 Excel 的“数据 > 删除重复值”功能，选择所有列进行去重。
- 部分重复：需先确定业务主键（如保单号、客户ID、交易流水号），再检查主键相同的记录中哪些字段存在差异。
- 时间序列重复：对于包含生效日期的数据，同一主键可能在不同时间点有合法记录，需结合业务逻辑判断。
// 在 Power Query 中按业务主键去重（保留最新记录）
= Table.Group(源, {"保单号"}, {{"最新记录", each Table.Max(_, "统计日期"), type table}})
4.3 缺失值处理：策略选择与实施
核心原理：缺失值处理是数据清洗中最需要判断力的环节。精算数据中的缺失可能源于系统迁移遗漏、人工录入不完整或业务上确实不适用（如未出险保单的理赔金额为空）。处理策略需基于缺失机制：完全随机缺失（MCAR）、随机缺失（MAR）或非随机缺失（MNAR）。
处理策略对比：
| 策略 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 删除记录 | 缺失比例 < 5%，且缺失机制为 MCAR | 简单、不引入偏差 | 损失样本量 |
| 均值/中位数插补 | 连续变量，缺失比例较低 | 计算简单 | 低估方差，扭曲分布 |
| 回归插补 | 存在强相关预测变量 | 利用变量间关系 | 可能过度拟合 |
| 多重插补 | 缺失比例较高，模型较复杂 | 考虑不确定性 | 实现复杂，需专业软件 |
| 单独标记 | 缺失本身具有业务含义 | 保留信息 | 增加维度 |
案例拆解：某寿险公司准备评估终身寿险产品的退保率，数据集中“退保原因”字段有 30% 缺失。经分析，缺失记录主要集中在早期保单（2005-2010年），当时系统未强制录入该字段。由于缺失机制与时间强相关（MNAR），简单删除会导致早期保单完全丢失，影响退保率的时间趋势分析。最终方案是：将“退保原因”分为“缺失-早期系统”和“缺失-其他”两个标记，并在建模时作为独立类别处理，同时使用多重插补对“退保时年龄”等其他字段进行填补。
4.4 异常值识别：区分错误与极端风险
核心原理：异常值在精算中具有双重含义：一是数据录入或传输错误导致的“脏数据”，二是真实存在的极端风险事件（如巨额理赔、极端死亡率）。识别异常值的目的是区分这两者，而非简单删除。
识别方法：
- 统计方法：Z-score（|Z|>3）、四分位距法（超出 Q1-1.5×IQR 或 Q3+1.5×IQR）、箱线图可视化。
- 业务规则：年龄 > 120 岁、理赔金额为负数、保费 < 0 等明显逻辑错误。
- 模型诊断：在回归模型中，通过标准化残差（|残差|>3）识别影响点。
案例拆解：在非寿险费率厘定中，某财产险公司发现车险理赔数据中有一笔 500 万元的理赔记录，而该保单的保额上限仅为 100 万元。经核查，这是系统录入时将“50,000”误录为“5,000,000”。该异常值若直接进入模型，将导致纯保费被严重高估。处理流程：首先通过箱线图识别该极端值，然后与业务部门核实原始理赔单，确认录入错误后修正为 5 万元，并在数据验证报告中记录该修正。
4.5 数据透视与验证：确保数据可用性
核心原理：数据透视是验证数据质量的有效手段，通过多维度的交叉汇总，可以发现单变量检查无法察觉的问题。数据验证则是建立一套规则，确保数据满足建模要求，包括完整性、准确性、一致性和时效性。
操作流程：
- 创建数据透视表：按关键维度（如险种、地区、年度）汇总保费、赔款、保单数等核心指标。
- 交叉验证：将透视结果与业务报表、精算假设进行对比，差异超过阈值需追溯原因。
- 建立验证规则：使用 Excel 的“数据验证”功能设置字段约束（如年龄在 0-120 之间、保费 > 0）。
- 生成质量报告：记录数据量、缺失率、异常值数量、修正记录等，形成可审计的文档。
| 验证维度 | 检查内容 | 示例规则 |
|---|---|---|
| 完整性 | 必填字段是否为空 | 保单号不能为空 |
| 准确性 | 数值是否在合理范围 | 赔付金额 ≤ 保额 |
| 一致性 | 关联字段是否匹配 | 险种代码必须在代码表中存在 |
| 时效性 | 日期是否合理 | 生效日期 ≤ 当前日期 |
4.6 总结复盘：构建数据清洗的标准流程
数据清洗不是一次性任务，而是需要持续优化的流程。精算建模团队应建立标准操作程序（SOP），明确每一步的责任人、操作方法和验收标准。以下是一份推荐的流程清单：
- 步骤1：数据导入后立即生成数据概览（行数、列数、字段类型、缺失率）。
- 步骤2：按业务主键去重，记录重复数量和处理规则。
- 步骤3：对缺失值进行分类，制定插补或标记策略。
- 步骤4：使用统计方法和业务规则双重识别异常值，区分错误与极端风险。
- 步骤5：通过数据透视验证关键指标，与业务报表交叉核对。
- 步骤6：应用数据验证规则，确保数据符合建模要求。
- 步骤7：输出数据质量报告，存档清洗日志，确保可追溯性。