🧹 01 · 数据清洗与预处理¶
真实数据 80% 时间在清洗。本节处理缺失值、重复值、离群值、类型错误,并用手写 vs pandas 对照验证。
📋 本节要解决的问题¶
| # | 问题 | 方法 |
|---|---|---|
| 1 | 缺失值怎么处理? | 删除 / 均值/中位数填充 / 前向填充 |
| 2 | 重复记录怎么识别? | drop_duplicates |
| 3 | 离群值怎么检测? | z-score / IQR |
| 4 | 脏类型怎么修? | 数值/日期转换、字符串清理 |
Copied!
import matplotlib
import matplotlib.pyplot as plt
matplotlib.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'PingFang SC', 'Noto Sans CJK SC', 'Arial Unicode MS']
matplotlib.rcParams['axes.unicode_minus'] = False
import numpy as np
import pandas as pd
np.random.seed(42)
import matplotlib
import matplotlib.pyplot as plt
matplotlib.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'PingFang SC', 'Noto Sans CJK SC', 'Arial Unicode MS']
matplotlib.rcParams['axes.unicode_minus'] = False
import numpy as np
import pandas as pd
np.random.seed(42)
1️⃣ 构造含脏数据的数据集¶
Copied!
df = pd.DataFrame({
'user_id': [101, 102, 103, 104, 105, 106, 107],
'city': ['北京', '上海', None, '广州', '深圳', '北京', '北京'],
'age': [25, 30, 28, None, 41, 22, 999],
'signup_date': ['2025-01-05', '2025-01-06', '2025-1-7', '2025-01-08', None, '2025-01-10', '2025-01-11'],
'amount': [120.5, None, 88.0, 155.0, 210.5, 99.0, 133.5],
})
print(df)
print('\n缺失情况:')
print(df.isna().sum())
df = pd.DataFrame({
'user_id': [101, 102, 103, 104, 105, 106, 107],
'city': ['北京', '上海', None, '广州', '深圳', '北京', '北京'],
'age': [25, 30, 28, None, 41, 22, 999],
'signup_date': ['2025-01-05', '2025-01-06', '2025-1-7', '2025-01-08', None, '2025-01-10', '2025-01-11'],
'amount': [120.5, None, 88.0, 155.0, 210.5, 99.0, 133.5],
})
print(df)
print('\n缺失情况:')
print(df.isna().sum())
user_id city age signup_date amount 0 101 北京 25.0 2025-01-05 120.5 1 102 上海 30.0 2025-01-06 NaN 2 103 None 28.0 2025-1-7 88.0 3 104 广州 NaN 2025-01-08 155.0 4 105 深圳 41.0 None 210.5 5 106 北京 22.0 2025-01-10 99.0 6 107 北京 999.0 2025-01-11 133.5 缺失情况: user_id 0 city 1 age 1 signup_date 1 amount 1 dtype: int64
2️⃣ 缺失值处理¶
✍️ §2.1 手算缺失值处理:均值填充一行账¶
缺失值处理三策略:删除 / 均值填充 / 插值——5 行数据手算均值填充,看懂"用已知估计未知"。
设定:销售额 [100, 缺失, 120, 80, 缺失]。
第 1 步:均值只算已知值
$$\bar x=\frac{100+120+80}{3}=100$$
第 2 步:填充
$$[100,\ \mathbf{100},\ 120,\ 80,\ \mathbf{100}]$$
手算收获:
- 均值填充会压低方差:填充值都在均值附近,导致统计量偏保守(本 notebook §2 同时用中位数填年龄、均值填金额的取舍)
- 均值 vs 中位数:右偏分布用中位数更稳(离群值会拉均值)——选择取决于分布,面试必问
- 缺失率过高(>30%)填充就没意义:应回源核对或删列——"先看缺失比例再定策略"
- 离群值(§5)同理:z-score > 3 才判离群,别把正常业务波动当脏数据
下面 numpy 复算。
Copied!
# ---- §2.1 验证:numpy 复算均值填充 ----
vals = [100, None, 120, 80, None]
known = [v for v in vals if v is not None]
mean = sum(known) / len(known)
filled = [v if v is not None else mean for v in vals]
print(f'均值 = {mean},填充后 = {filled}')
assert mean == 100 and filled == [100, 100, 120, 80, 100]
print('\n✓ 与手算一致')
# ---- §2.1 验证:numpy 复算均值填充 ----
vals = [100, None, 120, 80, None]
known = [v for v in vals if v is not None]
mean = sum(known) / len(known)
filled = [v if v is not None else mean for v in vals]
print(f'均值 = {mean},填充后 = {filled}')
assert mean == 100 and filled == [100, 100, 120, 80, 100]
print('\n✓ 与手算一致')
均值 = 100.0,填充后 = [100, 100.0, 120, 80, 100.0] ✓ 与手算一致
Copied!
# 策略:年龄用中位数填充,金额用均值填充,城市用'未知'填充
df2 = df.copy()
# 先修正明显错误:999 年龄视为缺失
age_median = df2['age'].replace(999, np.nan).median()
df2['age'] = df2['age'].replace(999, np.nan).fillna(age_median)
df2['amount'] = df2['amount'].fillna(df2['amount'].mean().round(2))
df2['city'] = df2['city'].fillna('未知')
print(df2)
assert df2[['age', 'amount', 'city']].isna().sum().sum() == 0
print('✅ 数值与城市缺失全部填充(日期在下一步统一处理)')
# 策略:年龄用中位数填充,金额用均值填充,城市用'未知'填充
df2 = df.copy()
# 先修正明显错误:999 年龄视为缺失
age_median = df2['age'].replace(999, np.nan).median()
df2['age'] = df2['age'].replace(999, np.nan).fillna(age_median)
df2['amount'] = df2['amount'].fillna(df2['amount'].mean().round(2))
df2['city'] = df2['city'].fillna('未知')
print(df2)
assert df2[['age', 'amount', 'city']].isna().sum().sum() == 0
print('✅ 数值与城市缺失全部填充(日期在下一步统一处理)')
user_id city age signup_date amount 0 101 北京 25.0 2025-01-05 120.50 1 102 上海 30.0 2025-01-06 134.42 2 103 未知 28.0 2025-1-7 88.00 3 104 广州 28.0 2025-01-08 155.00 4 105 深圳 41.0 None 210.50 5 106 北京 22.0 2025-01-10 99.00 6 107 北京 28.0 2025-01-11 133.50 ✅ 数值与城市缺失全部填充(日期在下一步统一处理)
3️⃣ 日期统一格式¶
Copied!
# '2025-1-7' 与 '2025-01-07' 格式不统一 → 统一转 datetime(新版本默认 datetime64[us])
df2['signup_date'] = pd.to_datetime(df2['signup_date'], format='mixed')
print(df2[['user_id', 'signup_date']])
print('最小注册日:', df2['signup_date'].min().date())
assert str(df2['signup_date'].dtype).startswith('datetime64')
print('✅ 日期统一为 datetime')
# '2025-1-7' 与 '2025-01-07' 格式不统一 → 统一转 datetime(新版本默认 datetime64[us])
df2['signup_date'] = pd.to_datetime(df2['signup_date'], format='mixed')
print(df2[['user_id', 'signup_date']])
print('最小注册日:', df2['signup_date'].min().date())
assert str(df2['signup_date'].dtype).startswith('datetime64')
print('✅ 日期统一为 datetime')
user_id signup_date 0 101 2025-01-05 1 102 2025-01-06 2 103 2025-01-07 3 104 2025-01-08 4 105 NaT 5 106 2025-01-10 6 107 2025-01-11 最小注册日: 2025-01-05 ✅ 日期统一为 datetime
4️⃣ 重复值识别与删除¶
Copied!
dup = pd.concat([df2, df2.iloc[[0]]]) # 故意加一行完全重复
print('添加重复后行数:', len(dup))
cleaned = dup.drop_duplicates()
print('删除重复后行数:', len(cleaned))
assert len(cleaned) == len(df2)
print('✅ 重复已删除')
dup = pd.concat([df2, df2.iloc[[0]]]) # 故意加一行完全重复
print('添加重复后行数:', len(dup))
cleaned = dup.drop_duplicates()
print('删除重复后行数:', len(cleaned))
assert len(cleaned) == len(df2)
print('✅ 重复已删除')
添加重复后行数: 8 删除重复后行数: 7 ✅ 重复已删除
5️⃣ 离群值检测:z-score 与 IQR¶
Copied!
amounts = df2['amount'].values
z = (amounts - amounts.mean()) / amounts.std()
print('z-score:', z.round(2))
print('z>2 视为离群:', amounts[abs(z) > 2])
q1, q3 = np.percentile(amounts, [25, 75])
iqr = q3 - q1
out_iqr = amounts[(amounts < q1 - 1.5*iqr) | (amounts > q3 + 1.5*iqr)]
print('IQR 离群:', out_iqr)
# 离群值标记列
clean_flags = (abs(z) > 2) | ((amounts < q1 - 1.5*iqr) | (amounts > q3 + 1.5*iqr))
print('离群标记:', clean_flags)
assert (out_iqr == np.array([210.5])).all()
print('✅ IQR 正确识别 210.5 为离群')
amounts = df2['amount'].values
z = (amounts - amounts.mean()) / amounts.std()
print('z-score:', z.round(2))
print('z>2 视为离群:', amounts[abs(z) > 2])
q1, q3 = np.percentile(amounts, [25, 75])
iqr = q3 - q1
out_iqr = amounts[(amounts < q1 - 1.5*iqr) | (amounts > q3 + 1.5*iqr)]
print('IQR 离群:', out_iqr)
# 离群值标记列
clean_flags = (abs(z) > 2) | ((amounts < q1 - 1.5*iqr) | (amounts > q3 + 1.5*iqr))
print('离群标记:', clean_flags)
assert (out_iqr == np.array([210.5])).all()
print('✅ IQR 正确识别 210.5 为离群')
z-score: [-0.37 0. -1.24 0.55 2.03 -0.95 -0.02] z>2 视为离群: [210.5] IQR 离群: [210.5] 离群标记: [False False False False True False False] ✅ IQR 正确识别 210.5 为离群
6️⃣ 字符串清理与编码¶
城市列规范化 + 派生「年龄段」特征:
Copied!
df3 = df2.copy()
df3['city'] = df3['city'].str.strip() # 去首尾空格
df3['age_bucket'] = pd.cut(df3['age'], bins=[0, 18, 30, 45, 200], labels=['<18', '18-30', '30-45', '>45'])
print(df3[['city', 'age', 'age_bucket']])
print('\n年龄分层统计:')
print(df3.groupby('age_bucket', observed=False).size())
df3 = df2.copy()
df3['city'] = df3['city'].str.strip() # 去首尾空格
df3['age_bucket'] = pd.cut(df3['age'], bins=[0, 18, 30, 45, 200], labels=['<18', '18-30', '30-45', '>45'])
print(df3[['city', 'age', 'age_bucket']])
print('\n年龄分层统计:')
print(df3.groupby('age_bucket', observed=False).size())
city age age_bucket 0 北京 25.0 18-30 1 上海 30.0 18-30 2 未知 28.0 18-30 3 广州 28.0 18-30 4 深圳 41.0 30-45 5 北京 22.0 18-30 6 北京 28.0 18-30 年龄分层统计: age_bucket <18 0 18-30 6 30-45 1 >45 0 dtype: int64
📝 自测清单¶
- 能列出缺失值三种处理方式并说明取舍
- 能手写 z-score 离群检测与 1.5×IQR 规则
- 能说出 drop_duplicates / to_datetime / str.strip 的用途
- 能解释「为何离群值要先业务判断再决定删/留」