课题：垃圾进，垃圾出——分析前的最后一道防线
一、引言：最好的模型救不了最烂的数据
L099-L102 我们学习了数据类型、参数/统计量、频数分布和数据可视化。但在这一切之前，有一个更基础、更关键的步骤——数据整理与清洗（Data Wrangling & Cleaning）。
一个真实场景： 你从 Bloomberg 导出了一组标普 500 成分股的财务数据，准备做因子分析。打开一看：有的 PE 是负数、有的 ROE 缺了、有的市值多了一个零、有的代码重复了两遍。
不经过整理和清洗的数据 = 给模型喂毒药。CFA 一级的考查重点：识别数据问题 + 选择正确的处理方法。
二、数据整理（Data Wrangling）的核心流程
2.1 什么是数据整理？
数据整理（Data Wrangling / Data Munging）是将原始数据转换为适合分析的格式的过程。它位于数据获取和数据分析之间。
原始数据 → 整理(Wrangling) → 清洗(Cleaning) → 转换(Transformation) → 分析(Analysis)
2.2 数据整理的七大任务
| 任务 | 说明 | 例子 |
|---|---|---|
| 合并（Merging） | 将多个数据源按共同字段合并 | 将股价数据与财报数据按 ticker 合并 |
| 重塑（Reshaping） | 改变数据结构（宽表 ↔ 长表） | 季度数据从列转行（pivot/melt） |
| 过滤（Filtering） | 筛选符合条件的观测值 | 只保留市值 > 10 亿的公司 |
| 聚合（Aggregation） | 将数据按组汇总 | 按行业计算平均 PE |
| 排序（Sorting） | 按某字段对数据排序 | 按回报率从高到低排列 |
| 去重（Deduplication） | 删除重复的观测值 | 同一交易日同一股票出现两条记录 |
| 类型转换（Type Conversion） | 修改变量数据类型 | 将文本格式的日期转为日期类型 |
三、数据清洗（Data Cleaning）：四大常见问题
3.1 缺失值（Missing Values）⭐
定义： 数据集中某些观测值没有记录，是金融数据中最常见的问题。
缺失值的来源： - 公司未披露某些财务指标 - 数据采集故障（API 调用失败） - 数据合并时某些字段无匹配 - 调查问卷中的「不适用」或拒绝回答
处理方法一览
| 方法 | 操作 | 适用场景 | 风险 |
|---|---|---|---|
| 列表删除（Listwise Deletion） | 删除含缺失值的整行 | 缺失比例很小（<5%） | 丢失信息，样本量减少 |
| 成对删除（Pairwise Deletion） | 只在用到该变量时排除 | 多变量分析中变量缺失不重叠 | 不同分析使用不同样本量（不一致） |
| 均值/中位数填补 | 用该变量的均值或中位数填补 | 数值型变量，缺失随机 | 低估方差，扭曲分布 |
| 众数填补 | 用最常见的值填补 | 分类变量 | 可能引入偏差 |
| 前向/后向填充 | 用相邻观测值填补 | 时间序列数据 | 不适用于非连续缺失 |
| 回归填补 | 用其他变量预测缺失值 | 缺失与其它变量相关 | 可能过拟合 |
| 多重插补（Multiple Imputation） | 生成多个填补值取平均 | 高要求的学术/专业分析 | 计算复杂，实施难度大 |
| 指示变量法 | 创建一个缺失指示变量 | 缺失本身有信息价值 | 增加变量数量 |
⚠️ CFA 重点： 选择缺失值处理方法时，首先要判断缺失机制——
| 缺失类型 | 英文 | 含义 | 影响 |
|---|---|---|---|
| 完全随机缺失 | MCAR | 缺失与任何变量都无关 | 任何方法都 OK（最理想） |
| 随机缺失 | MAR | 缺失与其它观测变量有关 | 可用插补，需谨慎 |
| 非随机缺失 | MNAR | 缺失与缺失值本身有关 | 最危险，任何简单方法都会引入偏差 |
案例： 分析基金回报率数据，发现表现差的基金更倾向于不报告——这是 MNAR。如果直接用均值填补，会系统性地高估行业平均回报。
3.2 异常值（Outliers）⭐
定义： 与数据集中大多数观测值显著不同的值。
识别方法：
| 方法 | 判定标准 | 适用场景 |
|---|---|---|
| Z-score 法 | |z| > 3 | 近似正态分布的数据 |
| IQR 法 | < Q1-1.5×IQR 或 > Q3+1.5×IQR | 偏态分布、稳健 |
| 百分位法 | 超出第 1 或第 99 百分位 | 保守处理 |
| 业务逻辑法 | 不符合现实的值 | 如负的股价、PE > 1000 |
处理方法：
| 处理方式 | 操作 | 适用场景 | 风险 |
|---|---|---|---|
| 保留 | 不做处理 | 异常值是真实信息（如金融危机期间的波动率） | 可能扭曲回归结果 |
| 删除（Trimming） | 直接删除异常值所在行 | 明显的录入错误（如多打了一个零） | 丢失合法信息 |
| 缩尾（Winsorizing） | 将极端值替换为某百分位的值 | 保留排序信息，降低影响力 | 改变了实际数据 |
| 对数变换 | 对变量取对数 | 右偏分布的数据 | 不适用于零值或负值 |
| 标准化 | 将数据转为 Z-score | 消除量纲影响 | 不改变分布形状 |
⚠️ CFA 陷阱： - 异常值 ≠ 错误值。金融危机时 -50% 的月回报是真实的，不应该删。 - 判断异常值必须结合业务背景。CFA 考题喜欢让你区分「数据录入错误」vs「真实的极端事件」。
3.3 不一致数据（Inconsistency）
| 问题类型 | 例子 | 处理方式 |
|---|---|---|
| 格式不一致 | 日期：2024/1/1 vs 01-01-2024 vs Jan 1, 2024 | 统一格式 |
| 单位不一致 | 市值：有的用元、有的用万元、有的用亿元 | 标准化单位 |
| 编码不一致 | 行业分类：有的用 GICS、有的用 ICB | 建立映射表 |
| 拼写/大小写 | "Apple Inc." vs "APPLE INC" vs "Apple" | 标准化字符串 |
3.4 重复数据（Duplication）
常见原因： - 数据多次导入 - 合并时匹配条件过宽 - 同一实体有多个标识符
处理方式： 按主键去重，保留第一次或最后一次出现的记录。
四、数据转换（Data Transformation）
4.1 常见转换方法
| 转换方法 | 公式 | 用途 | 注意事项 |
|---|---|---|---|
| 对数变换 | ln(x) | 处理右偏分布，稳定方差 | x > 0 |
| 平方根变换 | √x | 中等偏度 | x ≥ 0 |
| Box-Cox 变换 | (x^λ - 1)/λ | 更灵活的幂变换 | 需选择 λ |
| 标准化（Z-score） | (x - μ)/σ | 均值为 0，标准差为 1 | 保留分布形状 |
| 归一化（Min-Max） | (x - min)/(max - min) | 缩放到 [0, 1] 区间 | 对异常值敏感 |
| 排名变换 | rank(x) | 消除极端值影响 | 丢失量度信息 |
4.2 何时需要转换？
| 情况 | 推荐转换 |
|---|---|
| 数据严重右偏（如收入、市值） | 对数变换 |
| 需要满足回归的「同方差」假设 | 对数或 Box-Cox |
| 不同变量量纲差异大（如身高 vs 收入） | 标准化 |
| 需要将数据输入神经网络 | 归一化到 [0,1] 或 [-1,1] |
| 非线性关系转线性 | 对数-对数模型 |
五、数据质量检查清单
在实际分析之前，分析师应完成以下检查：
| 序号 | 检查项 | 方法 |
|---|---|---|
| 1 | 是否有缺失值？ | isnull().sum() / 描述性统计 |
| 2 | 缺失比例多大？ | 缺失数 / 总观测数 |
| 3 | 缺失是否有规律？ | 分组统计缺失比例 |
| 4 | 是否有明显的异常值？ | 箱线图、Z-score、IQR |
| 5 | 异常值是错误还是真实信息？ | 业务判断 |
| 6 | 数据类型是否正确？ | 检查各字段 dtype |
| 7 | 是否有重复记录？ | 按主键检查 |
| 8 | 分类变量的类别是否合理？ | 频数统计 |
| 9 | 时间序列是否有断点？ | 日期序列检查 |
| 10 | 合并后的数据行数是否正确？ | 预期行数 vs 实际行数 |
六、实战案例：清洗一份股票基本面数据
假设你拿到以下数据：
| Ticker | PE | ROE(%) | Market_Cap | Sector |
|---|---|---|---|---|
| AAPL | 28.5 | 145.2 | 2.8E+12 | Tech |
| GOOGL | 25.3 | 28.1 | 1.9E+12 | tech |
| MSFT | -15.2 | 32.5 | 2.5E+12 | Tech |
| AMZN | 55.0 | NA | 3e12 | 消费 |
| TSLA | 999.0 | 18.3 | 6.5E+11 | 汽车 |
| META | 30.1 | 28.7 | 8.5E+11 | Tech |
| AAPL | 28.5 | 145.2 | 2.8E+12 | Tech |
清洗步骤：
- ROE 异常值： AAPL 的 ROE 145.2% 明显异常，可能是录入错误 → 标记或交叉验证
- PE 负值： MSFT PE = -15.2，表示公司亏损（EPS 为负）→ 不删！这是真实信息
- 缺失值： AMZN 的 ROE 缺失 → 检查原因，可能该季度未披露
- 单位不一致： Market_Cap 有的用科学计数法，有的用字母 e → 统一为数字格式
- 行业拼写： "Tech" vs "tech" → 统一大写首字母
- 重复值： AAPL 出现了两次 → 去重，保留第一次
- PE 极端值： TSLA PE = 999 → 可能真实（盈利极低导致 PE 畸高），使用缩尾处理或标记
七、CFA 一级考点速记
| 考点 | 关键记住 |
|---|---|
| 缺失值处理 | MCAR/MAR/MNAR 三种机制的区别；列表删除 vs 成对删除 vs 插补 |
| 异常值 | 异常值 ≠ 错误值；缩尾 vs 删除 vs 保留的应用场景 |
| 对数变换 | 处理右偏数据、稳定方差的第一选择 |
| 标准化 vs 归一化 | 标准化保留异常值信息，归一化对异常值敏感 |
| 数据整理 vs 数据清洗 | Wrangling = 结构调整（合并/重塑/聚合）；Cleaning = 质量修正（缺失/异常/不一致） |
| 缩尾（Winsorizing） | 将极值替换为某百分位的值，而非删除——保留观测数量 |
八、随堂测试（5 题）
题 1
某分析师发现数据集中某变量的缺失完全随机，缺失比例为 3%。最合适的处理方法是？
A. 多重插补
B. 均值填补
C. 列表删除
D. 回归填补
题 2
以下哪项关于异常值的说法是正确的？
A. 所有异常值都应该被删除
B. Z-score > 2 的值一定是异常值
C. 异常值可能是真实的极端事件，需结合业务判断
D. 缩尾处理会减少样本量
题 3
一家公司某季度亏损导致 PE 为负值。分析师应如何处理？
A. 删除该条记录
B. 用行业中位数 PE 填补
C. 保留并标记，因为这是真实信息
D. 将该 PE 设为 0
题 4
以下哪项是「数据整理（Wrangling）」的典型操作？
A. 删除缺失值
B. 合并两个数据集
C. 用中位数填补异常值
D. 对变量取对数
题 5
分析师在处理右偏的基金规模数据时，最合适的转换方法是？
A. Min-Max 归一化
B. Z-score 标准化
C. 对数变换
D. 排名变换
九、答案与解析
题 1：C — 缺失比例仅 3% 且为 MCAR（完全随机），列表删除最简单且不会引入偏差。A 和 D 在处理 MCAR 时过于复杂；B 会低估方差。
题 2：C — 核心考点：异常值 ≠ 错误值，必须结合业务背景判断。A 错误（真实极值应保留）；B 错误（Z-score 阈值通常是 3，且需结合分布）；D 错误（缩尾不改变样本量）。
题 3：C — PE 为负因公司亏损，这是真实的信息，不应篡改。删除（A）会丢失信息，填补（B）会歪曲事实，设为零（D）没有意义。
题 4：B — 数据整理关注数据结构层面：合并、重塑、聚合、过滤。A/C/D 都是数据清洗操作。
题 5：C — 对数变换是处理右偏数据的标准方法。归一化（A）和标准化（B）不改变分布形状；排名变换（D）会丢失量度信息。
下节预告：L104 · 数据组织综合练习——把所有工具串起来
Topic: Garbage In, Garbage Out — The Last Line of Defense Before Analysis
1. Introduction: The Best Model Cannot Save the Worst Data
In L099–L102, we covered data types, parameters vs. statistics, frequency distributions, and data visualization. But before all of that, there is a more fundamental and critical step — Data Wrangling & Cleaning.
A real-world scenario: You export financial data for S&P 500 constituents from Bloomberg, ready to run a factor analysis. You open the file and find: some P/E ratios are negative, some ROEs are missing, one market cap has an extra zero, and one ticker appears twice.
Data that hasn't been wrangled and cleaned is poison for your model. The CFA Level 1 exam focuses on: identifying data problems + selecting the correct treatment method.
2. Core Data Wrangling Workflow
2.1 What Is Data Wrangling?
Data Wrangling (or Data Munging) is the process of transforming raw data into a format suitable for analysis. It sits between data acquisition and data analysis.
Raw Data → Wrangling → Cleaning → Transformation → Analysis
2.2 Seven Key Wrangling Tasks
| Task | Description | Example |
|---|---|---|
| Merging | Combine multiple data sources on a common field | Joining price data with financial statements by ticker |
| Reshaping | Change data structure (wide ↔ long format) | Pivoting quarterly data from columns to rows |
| Filtering | Select observations meeting criteria | Keep only companies with market cap > $1B |
| Aggregation | Summarize data by group | Compute average P/E by sector |
| Sorting | Order data by a field | Rank by return from highest to lowest |
| Deduplication | Remove duplicate observations | Same stock appearing twice on the same trading day |
| Type Conversion | Change variable data types | Convert text dates to date type |
3. Data Cleaning: Four Common Problems
3.1 Missing Values ⭐
Definition: Observations where no value is recorded — the single most common issue in financial data.
Sources of missing values: - Company did not disclose certain financial metrics - Data collection failure (API call error) - No match found during data merging - Survey responses marked "not applicable" or refused
Treatment Methods Overview
| Method | Action | When to Use | Risk |
|---|---|---|---|
| Listwise Deletion | Drop entire rows with any missing value | Missing proportion is very small (<5%) | Loss of information, reduced sample size |
| Pairwise Deletion | Exclude only when that variable is used | Missingness patterns don't overlap across variables | Inconsistent sample sizes across analyses |
| Mean/Median Imputation | Fill with the variable's mean or median | Numeric variables, missing at random | Underestimates variance, distorts distribution |
| Mode Imputation | Fill with the most frequent value | Categorical variables | May introduce bias |
| Forward/Backward Fill | Use adjacent observation values | Time series data | Not suitable for non-consecutive gaps |
| Regression Imputation | Predict missing values from other variables | Missingness correlates with other variables | Risk of overfitting |
| Multiple Imputation | Generate multiple imputed values and average | High-stakes academic/professional analysis | Complex to implement |
| Indicator Variable Method | Create a dummy variable flagging missingness | The fact of being missing carries information | Increases variable count |
⚠️ CFA Key Point: Before choosing a method, first determine the missingness mechanism —
| Missingness Type | Acronym | Meaning | Impact |
|---|---|---|---|
| Missing Completely At Random | MCAR | Missingness unrelated to any variable | Any method works (ideal case) |
| Missing At Random | MAR | Missingness related to other observed variables | Imputation feasible, but with caution |
| Missing Not At Random | MNAR | Missingness related to the missing value itself | Most dangerous; simple methods introduce bias |
Case Study: Analyzing fund return data, you find that underperforming funds are less likely to report — this is MNAR. If you impute with the mean, you will systematically overestimate the industry average return.
3.2 Outliers ⭐
Definition: Values that differ significantly from the majority of observations.
Detection Methods:
| Method | Criterion | Best For |
|---|---|---|
| Z-score Method | |z| > 3 | Approximately normal distributions |
| IQR Method | < Q1 − 1.5×IQR or > Q3 + 1.5×IQR | Skewed distributions; robust |
| Percentile Method | Beyond 1st or 99th percentile | Conservative approach |
| Business Logic | Values that defy reality | Negative stock prices, P/E > 1000 |
Treatment Methods:
| Treatment | Action | When to Use | Risk |
|---|---|---|---|
| Retain | Do nothing | Outlier is genuine information (e.g., volatility during a financial crisis) | May distort regression results |
| Delete (Trimming) | Remove outlier rows entirely | Obvious data entry errors (e.g., an extra zero) | Loss of legitimate information |
| Winsorizing | Replace extreme values with a specified percentile value | Preserve rank information while reducing influence | Alters actual data |
| Log Transformation | Take the natural log of the variable | Right-skewed data | Not applicable to zero or negative values |
| Standardization | Convert to Z-scores | Remove scale effects | Does not change distribution shape |
⚠️ CFA Pitfall: - Outlier ≠ Error. A −50% monthly return during a financial crisis is real and should not be deleted. - Outlier assessment must incorporate business context. CFA exam questions love to test your ability to distinguish "data entry error" from "genuine extreme event."
3.3 Inconsistent Data
| Problem Type | Example | Treatment |
|---|---|---|
| Format Inconsistency | Dates: 2024/1/1 vs 01-01-2024 vs Jan 1, 2024 | Standardize format |
| Unit Inconsistency | Market cap in units, ten-thousands, or hundred-millions | Standardize units |
| Encoding Inconsistency | Industry classification: GICS vs ICB | Build mapping table |
| Spelling/Case | "Apple Inc." vs "APPLE INC" vs "Apple" | Standardize strings |
3.4 Duplicate Data
Common Causes: - Data imported multiple times - Overly broad matching criteria during merges - Same entity with multiple identifiers
Treatment: Deduplicate by primary key, retaining the first or last occurrence.
4. Data Transformation
4.1 Common Transformation Methods
| Transformation | Formula | Purpose | Notes |
|---|---|---|---|
| Log Transform | ln(x) | Handle right-skewed data, stabilize variance | x > 0 |
| Square Root | √x | Moderate skewness | x ≥ 0 |
| Box-Cox | (x^λ − 1)/λ | Flexible power transformation | Requires λ selection |
| Standardization (Z-score) | (x − μ)/σ | Mean = 0, SD = 1 | Preserves distribution shape |
| Normalization (Min-Max) | (x − min)/(max − min) | Scale to [0, 1] | Sensitive to outliers |
| Rank Transformation | rank(x) | Eliminate extreme value influence | Loses magnitude information |
4.2 When to Transform?
| Situation | Recommended Transformation |
|---|---|
| Severely right-skewed data (income, market cap) | Log transform |
| Need to satisfy homoscedasticity for regression | Log or Box-Cox |
| Variables on vastly different scales (height vs. income) | Standardization |
| Input for neural networks | Min-Max normalization to [0,1] or [−1,1] |
| Converting non-linear to linear relationships | Log-log model |
5. Data Quality Checklist
Before starting analysis, every analyst should complete the following checks:
| # | Item | Method |
|---|---|---|
| 1 | Are there missing values? | Descriptive statistics / null count |
| 2 | What proportion is missing? | Missing count ÷ total observations |
| 3 | Is missingness patterned? | Grouped missing proportion analysis |
| 4 | Are there obvious outliers? | Box plot, Z-score, IQR |
| 5 | Are outliers errors or genuine information? | Business judgment |
| 6 | Are data types correct? | Check field dtypes |
| 7 | Are there duplicate records? | Check by primary key |
| 8 | Are categorical variable levels reasonable? | Frequency count |
| 9 | Are there gaps in time series? | Date sequence check |
| 10 | Is row count after merging correct? | Expected vs. actual row count |
6. Case Study: Cleaning a Stock Fundamentals Dataset
Suppose you receive the following data:
| Ticker | P/E | ROE(%) | Market_Cap | Sector |
|---|---|---|---|---|
| AAPL | 28.5 | 145.2 | 2.8E+12 | Tech |
| GOOGL | 25.3 | 28.1 | 1.9E+12 | tech |
| MSFT | −15.2 | 32.5 | 2.5E+12 | Tech |
| AMZN | 55.0 | NA | 3e12 | Consumer |
| TSLA | 999.0 | 18.3 | 6.5E+11 | Auto |
| META | 30.1 | 28.7 | 8.5E+11 | Tech |
| AAPL | 28.5 | 145.2 | 2.8E+12 | Tech |
Cleaning Steps:
- ROE outlier: AAPL's ROE of 145.2% is clearly extreme — likely a data entry error → flag or cross-validate
- Negative P/E: MSFT P/E = −15.2 indicates the company posted a loss (negative EPS) → Do NOT delete! This is genuine information
- Missing value: AMZN ROE is missing → investigate the reason; may not have been disclosed that quarter
- Unit inconsistency: Market_Cap uses mixed scientific notation → standardize to a uniform numeric format
- Sector spelling: "Tech" vs "tech" → standardize to title case
- Duplicate: AAPL appears twice → deduplicate, retain first occurrence
- Extreme P/E: TSLA P/E = 999 → could be genuine (very low earnings inflating P/E); use winsorization or flag
7. CFA Level 1 Key Takeaways
| Topic | Remember |
|---|---|
| Missing Value Treatment | MCAR vs. MAR vs. MNAR; listwise deletion vs. pairwise deletion vs. imputation |
| Outliers | Outlier ≠ error; winsorizing vs. deletion vs. retention — know when to use each |
| Log Transformation | The go-to method for right-skewed data and variance stabilization |
| Standardization vs. Normalization | Standardization preserves outlier information; normalization is sensitive to outliers |
| Wrangling vs. Cleaning | Wrangling = structural adjustments (merge/reshape/aggregate); Cleaning = quality fixes (missing/outlier/inconsistency) |
| Winsorizing | Replaces extreme values with percentile values rather than deleting — preserves observation count |
8. Practice Questions (5 Questions)
Question 1
An analyst finds that a variable in the dataset is missing completely at random, with a missing proportion of 3%. What is the most appropriate treatment?
A. Multiple imputation
B. Mean imputation
C. Listwise deletion
D. Regression imputation
Question 2
Which of the following statements about outliers is correct?
A. All outliers should be deleted
B. Values with Z-score > 2 are definitely outliers
C. Outliers may be genuine extreme events and require business judgment
D. Winsorizing reduces sample size
Question 3
A company reports a quarterly loss, resulting in a negative P/E ratio. How should the analyst handle this?
A. Delete the record
B. Impute with the industry median P/E
C. Retain and flag it, as this is genuine information
D. Set the P/E to zero
Question 4
Which of the following is a typical data wrangling operation?
A. Deleting missing values
B. Merging two datasets
C. Imputing outliers with the median
D. Taking the log of a variable
Question 5
An analyst is working with right-skewed fund size data. What is the most appropriate transformation?
A. Min-Max normalization
B. Z-score standardization
C. Log transformation
D. Rank transformation
9. Answers and Explanations
Q1: C — With only 3% missing and MCAR, listwise deletion is the simplest method that introduces no bias. A and D are overly complex for MCAR; B underestimates variance.
Q2: C — Core concept: outlier ≠ error; business context is essential. A is wrong (genuine extreme values should be retained); B is wrong (Z-score threshold is typically 3, and distribution must be considered); D is wrong (winsorizing does not change sample size).
Q3: C — A negative P/E due to a company loss is genuine information and should not be altered. Deleting (A) loses information, imputing (B) distorts facts, and setting to zero (D) is meaningless.
Q4: B — Data wrangling focuses on structural operations: merging, reshaping, aggregating, filtering. A, C, and D are all data cleaning operations.
Q5: C — Log transformation is the standard method for right-skewed data. Normalization (A) and standardization (B) do not change distribution shape; rank transformation (D) loses magnitude information.
Next: L104 · Data Organization Comprehensive Practice — Putting All the Tools Together