缺失值分析报告
- 数据集概况
- 规模：8条记录，9个字段
- 字段类型：日期型(signup_date)、分类型(gender, city)、数值型(age)、等级型(income_bracket: L1-L5)、货币型(monthly_spend)、布尔型(premium_user)、评分型(satisfaction_score: 1-5)
- 缺失概览（条数/占比）
- signup_date: 1/8=12.5%
- gender: 2/8=25.0%
- age: 1/8=12.5%
- income_bracket: 2/8=25.0%
- monthly_spend: 2/8=25.0%
- premium_user: 2/8=25.0%
- city: 1/8=12.5%
- satisfaction_score: 2/8=25.0%
- 缺失模式分析（按字段）
- signup_date：主要因埋点延迟，倾向MAR（与时间/采集流程相关）
- gender：隐私拒答，倾向MNAR（与用户特征相关）；建模时可按“近似MAR”（条件于city/age/spend等）处理并保留缺失指示
- age：问卷缺失，倾向MAR（受性别/城市/收入影响）
- income_bracket：隐私拒答，倾向MNAR（与真实收入相关）；拟合时建议使用条件模型+缺失指示
- monthly_spend：埋点/延迟或早期空值，倾向MAR（与注册时间/付费状态相关）
- premium_user：延迟标记，倾向MAR（与消费/注册时间相关）
- city：定位失败/延迟，倾向MAR（与设备/网络相关）
- satisfaction_score：自愿题，倾向MNAR（高/低满意度人群更可能不答）；需保守填充并加入缺失指示
- 业务影响评估
- 城市与性别需保持分布稳定：对分群/地域投放、性别偏好建模敏感
- 需保持age与monthly_spend相关性：影响LTV与留存/付费预测的核心关系
- satisfaction_score用作标签侧特征：避免过度乐观填充（防止高估满意度带来模型偏乐观）
- premium_user与monthly_spend互相关：错误填充将造成系统性偏差
填充策略推荐
总体原则
- 先建立“缺失指示”特征；对MNAR可在模型中显式使用
- 混合方法：KNN预测填充（连续/分类）、回归/分类模型（带概率抽样），必要时使用分层众数/中位数作为备选
- 分布与相关性约束：性别/城市使用“边际分布保持”的概率抽样；age—monthly_spend采用KNN/回归互预测，避免均值填充的相关性收缩
首选方案（与偏好一致：KNN预测填充 + 回归预测填充 + 边际分布保持）
备选方案
- 分类众数填充（gender/city）+ 缺失指示
- 适用：极少量缺失或KNN邻域过稀
- 要点：在分层（如city内）计算众数；保持总体配额（按观测占比分配缺失样本）
- 效果：实现简单但信息损失较大，不能利用条件结构
- 回归预测填充（age/spend）使用线性回归/岭回归
- 适用：样本量较大、线性关系较明显的场景
- 要点：对spend用log1p，交叉验证选择正则；预测区间外截断
- 效果：可解释性强，但对非线性/局部结构不如KNN
- 常量填充（signup_date）
- 适用：KNN不可行时，用“同城中位注册日”作为常量；并加date_missing指示
- 要点：每个city单独计算中位数；超出范围则回退全局中位数
- 效果：季节性可能略被削弱，作为兜底方案
详细实施指南
-
数据预处理要求
- 统一类型：布尔转0/1，等级型L1-L5映射为1-5；日期转为天序数（与全局最小日期的天差）
- 添加缺失指示列：对于存在缺失的字段均添加 is__missing（二元）
- 特征缩放：数值型用于KNN需标准化（StandardScaler）
- 类别编码：gender/city独热编码，用“UNK”表示临时缺失（仅作为其他列的预测特征）
-
填充操作步骤
- 建立观测边际分布：gender、city（基于非缺失样本）
- signup_date：KNN回归填充日期序数（邻域包含city/gender/age/income/spend/premium）
- gender、city：KNN分类得到每类概率，使用“边际分布保持”的配额分配（概率加权的贪心匹配）
- premium_user：KNN或逻辑回归概率抽样；如需总体占比稳定，也可做边际保持
- income_bracket：KNN分类（1-5）后映射回L1-L5
- age：KNN回归，结果夹在[18,60]
- monthly_spend：对log1p回归，反变换后≥0
- satisfaction_score：KNN近邻的加权第40百分位，四舍五入与夹取；同时保留缺失指示
- 复核约束：类型、取值范围、分布偏离（KS/卡方）、age—spend相关系数对比
-
结果验证方法与指标
- 分布保持
- gender/city：总体占比变化 |Δp| ≤ 1-2个百分点
- KS检验（连续：age/spend）或卡方（city/gender/income）对比“观测 vs 观测+填充”，p值>0.05为佳
- 关系保持
- age–monthly_spend Pearson/Spearman：与仅用完整样本计算的基线差异 |Δρ| ≤ 0.05-0.10
- 预测一致性
- 若有下游模型：留存/流失AUC/Logloss在填充前后变化≤1-2个百分点
- 稳健性
- 使用留一法/交叉验证评估KNN/回归填充误差（MAE/RMSE或分类准确率）
-
常见问题与处理
- 邻域过稀：降低n_neighbors或放宽特征集，必要时回退到分层中位/众数
- 概率抽样波动：固定随机种子，或在边际保持算法中使用配额控制
- 极端值回传：spend使用log1p+剪裁；age强制范围裁剪；日期强制落在观测区间
质量保证建议
- 评估指标
- 缺失减少率：填充后缺失占比→0
- 分布漂移：Jensen-Shannon距离/KS统计量；类别卡方统计
- 相关性保持：关键对（age, monthly_spend）的ρ差异
- 模型稳定性：下游验证集AUC/MAE变化
- 异常值检测
- IQR/箱线图规则或基于残差（回归预测值±3σ）
- 分类一致性检查：income与spend、premium的逻辑一致性（如L1却极高消费需复核）
- 监控与优化
- 持续监控边际分布变化（性别/城市月度趋势）
- 周期性重新拟合KNN/回归模型
- 若样本增大，升级为多重插补（MICE）以量化不确定性
Python填充示例（可直接运行的最小可行实现）
说明：
- 采用pandas/numpy/scikit-learn
- KNN用于回归与分类
- 提供“边际分布保持”的贪心分配器
- 对评分使用KNN加权分位数填充（τ=0.4）
import numpy as np
import pandas as pd
from io import StringIO
from datetime import datetime, timedelta
from sklearn.preprocessing import StandardScaler
from sklearn.neighbors import KNeighborsRegressor, KNeighborsClassifier
# ---------- 1) 读入数据 ----------
raw = """user_id,signup_date,gender,age,income_bracket,monthly_spend,premium_user,city,satisfaction_score
U1001,2023-11-05,F,28,L2,89.3,TRUE,Shenzhen,5
U1002,,M,35,L3,120.5,,Guangzhou,4
U1003,2024-01-12,,27,,75.0,FALSE,Shenzhen,
U1004,2023-12-30,F,,L1,,FALSE,,3
U1005,2024-02-10,Other,22,L2,45.0,TRUE,Shenzhen,5
U1006,2024-02-15,M,31,L4,,TRUE,Hangzhou,4
U1007,2023-11-20,,29,L3,0.0,FALSE,Guangzhou,
U1008,2024-03-01,F,26,,68.0,,Shenzhen,5
"""
df = pd.read_csv(StringIO(raw), dtype=str)
# ---------- 2) 基础清洗与类型转换 ----------
def to_bool(x):
if pd.isna(x): return np.nan
x = str(x).strip().lower()
if x in ['true','1','yes','y','t']: return 1
if x in ['false','0','no','n','f']: return 0
return np.nan
def parse_date(s):
if pd.isna(s) or s=='':
return pd.NaT
return pd.to_datetime(s)
df['signup_date'] = df['signup_date'].apply(parse_date)
df['age'] = pd.to_numeric(df['age'], errors='coerce')
df['monthly_spend'] = pd.to_numeric(df['monthly_spend'], errors='coerce')
df['premium_user'] = df['premium_user'].apply(to_bool).astype('float') # float to allow NaN
# 等级映射
lvl_map = {'L1':1,'L2':2,'L3':3,'L4':4,'L5':5}
df['income_level'] = df['income_bracket'].map(lvl_map).astype('float')
# 缺失指示
for col in ['signup_date','gender','age','income_level','monthly_spend','premium_user','city','satisfaction_score']:
df[f'is_{col}_missing'] = df[col].isna().astype(int)
# 日期序数（天）
min_date = df['signup_date'].min()
if pd.isna(min_date):
min_date = pd.Timestamp('2023-01-01')
df['signup_ordinal'] = (df['signup_date'] - min_date).dt.days
# ---------- 3) 特征构造（用于KNN的通用X） ----------
def make_features(df_local):
# 临时用UNK填充类别、用中位数填充数值（仅作预测特征，不是最终填充值）
tmp = df_local.copy()
cat_cols = ['gender','city']
for c in cat_cols:
tmp[c] = tmp[c].fillna('UNK')
# 数值临时填
num_cols = ['age','income_level','monthly_spend','premium_user','signup_ordinal']
for c in num_cols:
med = tmp[c].median(skipna=True)
tmp[c] = tmp[c].fillna(med)
X = pd.get_dummies(tmp[cat_cols], dummy_na=False)
X = pd.concat([X, tmp[num_cols],
tmp[['is_signup_date_missing','is_gender_missing','is_age_missing',
'is_income_level_missing','is_monthly_spend_missing',
'is_premium_user_missing','is_city_missing','is_satisfaction_score_missing']]], axis=1)
return X
# 标准化器（仅对数值列）
def scale_fit_transform(X):
X = X.copy()
num_mask = X.dtypes != 'uint8' # one-hot为uint8
scaler = StandardScaler()
X.loc[:, num_mask] = scaler.fit_transform(X.loc[:, num_mask])
return X, scaler, num_mask
def scale_transform(X, scaler, num_mask):
X = X.copy()
X.loc[:, num_mask] = scaler.transform(X.loc[:, num_mask])
return X
# ---------- 4) 边际分布保持的分配器 ----------
def marginal_preserving_assign(prob_mat, classes, target_counts, random_state=42):
# prob_mat: n_missing x n_classes 的概率
rng = np.random.default_rng(random_state)
n_missing, n_classes = prob_mat.shape
# 贪心：迭代选择当前剩余额度中概率最大的(样本,类)配对
remaining = target_counts.copy()
assigned = -np.ones(n_missing, dtype=int)
# 排序所有对
pairs = []
for i in range(n_missing):
for j in range(n_classes):
pairs.append((i,j,prob_mat[i,j]))
pairs.sort(key=lambda x: x[2], reverse=True)
used_rows = set()
for i,j,p in pairs:
if assigned[i] == -1 and remaining[j] > 0:
assigned[i] = j
remaining[j] -= 1
used_rows.add(i)
if len(used_rows) == n_missing:
break
# 若配额未完全用尽/分配失败，则用剩余最大概率类补齐
for i in range(n_missing):
if assigned[i] == -1:
j = int(np.argmax(prob_mat[i]))
assigned[i] = j
return np.array([classes[k] for k in assigned])
def build_targets_for_missing(obs_series, missing_cnt, classes):
# 目标：填完后总体分布 ≈ 观测分布
obs = obs_series.dropna()
if len(obs)==0:
# 平均分配
base = np.array([missing_cnt // len(classes)]*len(classes))
base[:missing_cnt - base.sum()] += 1
return base
counts = obs.value_counts().reindex(classes).fillna(0).values.astype(int)
p = counts / counts.sum()
target = np.round(p * missing_cnt).astype(int)
# 调整四舍五入误差
diff = missing_cnt - target.sum()
if diff != 0:
order = np.argsort(-(p - target/missing_cnt)) # 优先给占比高的
for k in order[:abs(diff)]:
target[k] += int(np.sign(diff))
return target
# ---------- 5) 各字段填充 ----------
X_base = make_features(df)
X_scaled, scaler, num_mask = scale_fit_transform(X_base)
# 5.1 signup_date -> KNN回归日期序数
mask_sd = df['signup_ordinal'].notna()
if mask_sd.sum() >= 2 and (~mask_sd).sum() > 0:
knn_sd = KNeighborsRegressor(n_neighbors=3, weights='distance')
knn_sd.fit(X_scaled[mask_sd], df.loc[mask_sd, 'signup_ordinal'])
X_miss = scale_transform(make_features(df[~mask_sd]), scaler, num_mask)
pred = knn_sd.predict(X_miss)
pred = np.round(np.clip(pred, df['signup_ordinal'].min(), df['signup_ordinal'].max())).astype(int)
df.loc[~mask_sd, 'signup_ordinal'] = pred
df['signup_date'] = df['signup_ordinal'].apply(lambda d: (min_date + pd.Timedelta(days=int(d))) if pd.notna(d) else pd.NaT)
# 5.2 gender -> KNN分类 + 边际分布保持
classes_gender = ['F','M','Other']
mask_g = df['gender'].isna()
if mask_g.any():
mask_g_obs = ~mask_g
knn_g = KNeighborsClassifier(n_neighbors=3, weights='distance')
knn_g.fit(X_scaled[mask_g_obs], df.loc[mask_g_obs, 'gender'])
X_miss = scale_transform(make_features(df[mask_g]), scaler, num_mask)
prob = knn_g.predict_proba(X_miss)
# 重新排序为固定类别顺序
proba_mat = np.zeros((prob.shape[0], len(classes_gender)))
for j, cls in enumerate(knn_g.classes_):
proba_mat[:, classes_gender.index(cls)] = prob[:, j]
tgt_counts = build_targets_for_missing(df.loc[mask_g, 'gender'], mask_g.sum(), classes_gender)
fill_vals = marginal_preserving_assign(proba_mat, classes_gender, tgt_counts, random_state=42)
df.loc[mask_g, 'gender'] = fill_vals
# 5.3 city -> KNN分类 + 边际分布保持
classes_city = sorted(df['city'].dropna().unique().tolist() + ['Shenzhen','Guangzhou','Hangzhou'])
classes_city = sorted(list(set([c for c in classes_city if isinstance(c, str) and c!=''])))
mask_c = df['city'].isna()
if mask_c.any():
mask_c_obs = ~mask_c
knn_c = KNeighborsClassifier(n_neighbors=3, weights='distance')
knn_c.fit(X_scaled[mask_c_obs], df.loc[mask_c_obs, 'city'])
X_miss = scale_transform(make_features(df[mask_c]), scaler, num_mask)
prob = knn_c.predict_proba(X_miss)
# 对齐类别顺序
proba_mat = np.zeros((prob.shape[0], len(classes_city)))
for j, cls in enumerate(knn_c.classes_):
proba_mat[:, classes_city.index(cls)] = prob[:, j]
tgt_counts = build_targets_for_missing(df.loc[mask_c, 'city'], mask_c.sum(), classes_city)
fill_vals = marginal_preserving_assign(proba_mat, classes_city, tgt_counts, random_state=42)
df.loc[mask_c, 'city'] = fill_vals
# 5.4 premium_user -> KNN分类（可做边际保持）
mask_p = df['premium_user'].isna()
if mask_p.any():
mask_p_obs = ~mask_p
knn_p = KNeighborsClassifier(n_neighbors=3, weights='distance')
knn_p.fit(X_scaled[mask_p_obs], df.loc[mask_p_obs, 'premium_user'].astype(int))
X_miss = scale_transform(make_features(df[mask_p]), scaler, num_mask)
prob = knn_p.predict_proba(X_miss)
# 概率抽样（保持总体占比，可选）
classes_p = [0,1]
proba_mat = np.zeros((prob.shape[0], len(classes_p)))
for j, cls in enumerate(knn_p.classes_):
proba_mat[:, classes_p.index(cls)] = prob[:, j]
# 构造总占比目标
tgt_counts = build_targets_for_missing(df.loc[mask_p, 'premium_user'], mask_p.sum(), classes_p)
fill_vals = marginal_preserving_assign(proba_mat, classes_p, tgt_counts, random_state=42)
df.loc[mask_p, 'premium_user'] = fill_vals
# 5.5 income_bracket（等级）-> KNN分类（1-5）
mask_inc = df['income_level'].isna()
if mask_inc.any():
mask_inc_obs = ~mask_inc
knn_inc = KNeighborsClassifier(n_neighbors=5, weights='distance')
knn_inc.fit(X_scaled[mask_inc_obs], df.loc[mask_inc_obs, 'income_level'].astype(int))
X_miss = scale_transform(make_features(df[mask_inc]), scaler, num_mask)
pred = knn_inc.predict(X_miss)
df.loc[mask_inc, 'income_level'] = pred
df['income_bracket'] = df['income_level'].round().clip(1,5).map({v:k for k,v in lvl_map.items()})
# 5.6 age -> KNN回归
mask_age = df['age'].isna()
if mask_age.any():
mask_age_obs = ~mask_age
knn_age = KNeighborsRegressor(n_neighbors=5, weights='distance')
knn_age.fit(X_scaled[mask_age_obs], df.loc[mask_age_obs, 'age'])
X_miss = scale_transform(make_features(df[mask_age]), scaler, num_mask)
pred = knn_age.predict(X_miss)
df.loc[mask_age, 'age'] = np.clip(pred, 18, 60)
# 5.7 monthly_spend -> KNN回归（log1p）
mask_sp = df['monthly_spend'].isna()
if mask_sp.any():
mask_sp_obs = ~mask_sp
y = np.log1p(df.loc[mask_sp_obs, 'monthly_spend'].astype(float))
knn_sp = KNeighborsRegressor(n_neighbors=5, weights='distance')
knn_sp.fit(X_scaled[mask_sp_obs], y)
X_miss = scale_transform(make_features(df[mask_sp]), scaler, num_mask)
pred = knn_sp.predict(X_miss)
df.loc[mask_sp, 'monthly_spend'] = np.maximum(np.expm1(pred), 0.0)
# 5.8 satisfaction_score -> KNN加权分位数(τ=0.4) + 指示
def knn_weighted_quantile_impute(df_local, target_col, tau=0.4, n_neighbors=7, eps=1e-6):
mask_miss = df_local[target_col].isna()
if not mask_miss.any(): return
mask_obs = ~mask_miss
y = df_local.loc[mask_obs, target_col].astype(float).values
X_obs = scale_transform(make_features(df_local[mask_obs]), scaler, num_mask).values
X_miss = scale_transform(make_features(df_local[mask_miss]), scaler, num_mask).values
# 计算距离
from sklearn.metrics import pairwise_distances
D = pairwise_distances(X_miss, X_obs, metric='euclidean')
# 对每个缺失样本取最近邻
nn_idx = np.argsort(D, axis=1)[:, :min(n_neighbors, X_obs.shape[0])]
filled = []
for i in range(nn_idx.shape[0]):
idx = nn_idx[i]
d = D[i, idx]
w = 1.0 / (d + eps)
w = w / w.sum()
yy = y[idx]
# 计算加权分位数
ord_idx = np.argsort(yy)
yy_sorted = yy[ord_idx]
w_sorted = w[ord_idx]
cdf = np.cumsum(w_sorted)
q_val = yy_sorted[np.searchsorted(cdf, tau, side='right')]
filled.append(int(np.clip(np.round(q_val), 1, 5)))
df_local.loc[mask_miss, target_col] = filled
df['satisfaction_score'] = pd.to_numeric(df['satisfaction_score'], errors='coerce')
knn_weighted_quantile_impute(df, 'satisfaction_score', tau=0.4, n_neighbors=7)
df['satisfaction_missing'] = df['is_satisfaction_score_missing']
# ---------- 6) 结果与简要评估 ----------
# 性别与城市分布
gender_dist_before = (df.loc[df['is_gender_missing']==0, 'gender'].value_counts(normalize=True)*100).round(1)
gender_dist_after = (df['gender'].value_counts(normalize=True)*100).round(1)
city_dist_before = (df.loc[df['is_city_missing']==0, 'city'].value_counts(normalize=True)*100).round(1)
city_dist_after = (df['city'].value_counts(normalize=True)*100).round(1)
# 相关性（仅演示）
corr_before = df.loc[(df['is_age_missing']==0)&(df['is_monthly_spend_missing']==0), ['age','monthly_spend']].corr().iloc[0,1]
corr_after = df[['age','monthly_spend']].corr().iloc[0,1]
print("Gender dist (obs):\n", gender_dist_before.to_dict())
print("Gender dist (all):\n", gender_dist_after.to_dict())
print("City dist (obs):\n", city_dist_before.to_dict())
print("City dist (all):\n", city_dist_after.to_dict())
print("Age-Spend corr (obs only):", round(float(corr_before),3))
print("Age-Spend corr (after):", round(float(corr_after),3))
# 输出最终数据
out_cols = ['user_id','signup_date','gender','age','income_bracket','monthly_spend','premium_user','city','satisfaction_score','satisfaction_missing']
print(df[out_cols])
运行要点与注意
- 随机性：marginal_preserving_assign使用random_state固定；可重复
- 小样本：n_neighbors建议3-5；大样本可增大到5-15
- 范围/裁剪：age/score/spend/日期均已设定边界
- 指示变量：保留is_*_missing，尤其是satisfaction_missing供下游使用
质量保证建议
- 评估
- 性别/城市分布差异（观测 vs 全量）：绝对偏差≤1-2个百分点
- KS/卡方检验：p>0.05优先
- age—spend相关性Δρ≤0.05-0.10
- 交叉验证KNN误差：age/spend的MAE、RMSE，income/gender/city/premium的准确率
- 监控
- 月度重跑分布对比与漂移报警
- 样本量增长后，用迭代填充（如MICE）替代一次性KNN，以量化不确定性
- 文档化
该方案遵循：
- 统计合理性：基于KNN与概率模型，避免未经验证的启发式
- 分布与相关性不扭曲：边际保持+KNN局部结构
- 业务约束：性别/城市稳定、age—spend关系保持、满意度保守填充并显式标注缺失
- 无系统性偏差：概率抽样与配额控制，防止单边偏置
