基于多列分组数据构建训练测试集预测QTR4销售代表拜访量

一、基础数据预处理 #

原始数据加载后首先做类型修正，避免后续计算报错：

import pandas as pd
import numpy as np

# 加载示例数据
df = pd.DataFrame({"CUSTOMER_NUMBER": ["CUST1", "CUST1", "CUST1", "CUST1", "CUST1", "CUST1", "CUST1", "CUST1", "CUST1", "CUST2", "CUST2", "CUST2", "CUST2", "CUST2", "CUST2", "CUST2", "CUST3", "CUST3", "CUST3", "CUST4", "CUST4", "CUST4"],
"PRODUCT": ["PRODUCT1", "PRODUCT2", "PRODUCT3", "PRODUCT1", "PRODUCT2", "PRODUCT3", "PRODUCT1", "PRODUCT2", "PRODUCT3", "PRODUCT1", "PRODUCT2", "PRODUCT3", "PRODUCT1", "PRODUCT2", "PRODUCT3", "PRODUCT3", "PRODUCT3", "PRODUCT3", "PRODUCT3", "PRODUCT1", "PRODUCT1", "PRODUCT2"],
"REP_VISITS": ["3", "3", "3", "3", "3", "3", "4", "4", "4", "3", "2", "2", "4", "6", "8", "5", "3", "1", "3", "2", "0", "3"],
"QTR": ["QTR1", "QTR1", "QTR1", "QTR2", "QTR2", "QTR2", "QTR3", "QTR3", "QTR3", "QTR1", "QTR1", "QTR1", "QTR2", "QTR2", "QTR2", "QTR3", "QTR1", "QTR2", "QTR3", "QTR1", "QTR2", "QTR3"],
"START_DATE": ["2020-01-01", "2020-01-01", "2020-01-01", "2020-04-01", "2020-04-01", "2020-04-01", "2020-07-01", "2020-07-01", "2020-07-01", "2020-01-01", "2020-01-01", "2020-01-01", "2020-04-01",  "2020-04-01", "2020-04-01","2020-07-01", "2020-01-01", "2020-04-01", "2020-07-01", "2020-01-01", "2020-04-01", "2020-07-01"],
"END_DATE": ["2020-03-31", "2020-03-31", "2020-03-31", "2020-06-30", "2020-06-30", "2020-06-30", "2020-09-30", "2020-09-30", "2020-09-30", "2020-03-31", "2020-03-31", "2020-03-31", "2020-06-30", "2020-06-30", "2020-06-30", "2020-09-30", "2020-03-31", "2020-06-30", "2020-09-30", "2020-03-31", "2020-06-30", "2020-09-30"]})

# 修正字段类型
df["REP_VISITS"] = df["REP_VISITS"].astype(int)  # 原始拜访量为字符串，转整数方便计算
df["START_DATE"] = pd.to_datetime(df["START_DATE"])
df["END_DATE"] = pd.to_datetime(df["END_DATE"])

二、客户-产品维度的训练数据集构建 #

核心逻辑是把每个客户-产品组合作为独立的时序分析单元，通过滑动窗口构造匹配规则的训练样本：

• 先按CUSTOMER_NUMBER、PRODUCT分组，对每个分组内的记录按季度时间顺序排序
• 以每个季度的真实拜访量为预测目标，往前取3个季度的历史拜访量构造输入特征，完全匹配“基于最近3个季度数据预测下一季度”的需求
• 可构造的特征包括：上一季度拜访量、上两季度拜访量、上三季度拜访量、近3个季度平均拜访量、近3个季度拜访量的环比趋势
• 对没有连续季度记录的客户-产品组合，按实际可获取的历史值填充特征，不要强行补0
  对应的特征构造代码：

def build_group_features(group_df):
    # 组内按时间排序
    group_df = group_df.sort_values("START_DATE").reset_index(drop=True)
    # 生成滞后特征
    group_df["lag1_visit"] = group_df["REP_VISITS"].shift(1)
    group_df["lag2_visit"] = group_df["REP_VISITS"].shift(2)
    group_df["lag3_visit"] = group_df["REP_VISITS"].shift(3)
    group_df["avg_3q_visit"] = group_df[["lag1_visit", "lag2_visit", "lag3_visit"]].mean(axis=1)
    group_df["visit_trend"] = (group_df["lag1_visit"] - group_df["lag3_visit"]) / group_df["lag3_visit"].replace(0, np.nan)
    return group_df

# 批量生成所有组合的特征
feature_df = df.groupby(["CUSTOMER_NUMBER", "PRODUCT"], group_keys=False).apply(build_group_features)
# 筛选有真实目标值、有有效特征的样本作为训练集基础
train_samples = feature_df.dropna(subset=["REP_VISITS", "lag1_visit"])

三、测试集拆分与效果评估 #

因为是时序预测场景，绝对不能用随机打乱的方式拆分训练测试集，否则会出现数据泄漏，导致评估结果完全失真：

• 严格按时间先后顺序拆分：将较早时段的样本作为训练集，较晚时段的样本作为测试集
  • 针对当前数据的QTR1/QTR2/QTR3三个季度，可直接把QTR1+QTR2的所有样本作为训练集，QTR3的所有样本作为测试集
  • 验证逻辑：用QTR1、QTR2的历史数据训练模型，预测QTR3的拜访量，将预测值和QTR3的真实拜访量做对比，用平均绝对误差（MAE）、均方根误差（RMSE）作为评估指标，即可得到模型的实际预测精度
• 这种拆分方式和最终预测QTR4的场景完全一致，能真实反映模型的泛化能力
  对应的拆分代码：

# 按时间顺序拆分，无数据泄漏
train = train_samples[train_samples["QTR"].isin(["QTR1", "QTR2"])]
test = train_samples[train_samples["QTR"] == "QTR3"]

# 定义特征列和目标列
feature_cols = ["lag1_visit", "lag2_visit", "lag3_visit", "avg_3q_visit", "visit_trend"]
X_train, y_train = train[feature_cols], train["REP_VISITS"]
X_test, y_test = test[feature_cols], test["REP_VISITS"]

四、预测逻辑选择与最终预测 #

因为当前样本量较小，避免用复杂模型过拟合，优先选择泛化性强的简单模型，预测结果会更稳定，按精度从低到高选择即可：

• 基线方案：直接取对应客户-产品近3个季度的平均拜访量作为QTR4的预测值，可作为精度对照基准
• 进阶方案：用线性回归、LightGBM这类简单的树模型，在上述构造的特征上训练，通常比基线精度提升30%以上
• 最终预测：用所有QTR1/QTR2/QTR3的样本训练模型，为需要预测的9个客户-产品组合构造对应特征（即取各组合QTR1/QTR2/QTR3的拜访量作为滞后特征），输出QTR4的预测值
  对应的目标预测样本构造代码：

# 生成需要预测的目标客户-产品组合
target_combinations = pd.DataFrame([
    ("CUST1", "PRODUCT1", "QTR4"),
    ("CUST1", "PRODUCT2", "QTR4"),
    ("CUST1", "PRODUCT3", "QTR4"),
    ("CUST2", "PRODUCT1", "QTR4"),
    ("CUST2", "PRODUCT2", "QTR4"),
    ("CUST2", "PRODUCT3", "QTR4"),
    ("CUST3", "PRODUCT3", "QTR4"),
    ("CUST4", "PRODUCT1", "QTR4"),
    ("CUST4", "PRODUCT2", "QTR4"),
], columns=["CUSTOMER_NUMBER", "PRODUCT", "QTR"])

# 匹配各组合的历史季度拜访量
pivot_df = df.pivot(index=["CUSTOMER_NUMBER", "PRODUCT"], columns="QTR", values="REP_VISITS").reset_index()
predict_feature = target_combinations.merge(pivot_df, on=["CUSTOMER_NUMBER", "PRODUCT"], how="left")

# 构造和训练集一致的特征
predict_feature["lag1_visit"] = predict_feature["QTR3"]
predict_feature["lag2_visit"] = predict_feature["QTR2"]
predict_feature["lag3_visit"] = predict_feature["QTR1"]
predict_feature["avg_3q_visit"] = predict_feature[["lag1_visit", "lag2_visit", "lag3_visit"]].mean(axis=1)
predict_feature["visit_trend"] = (predict_feature["lag1_visit"] - predict_feature["lag3_visit"]) / predict_feature["lag3_visit"].replace(0, np.nan)

内容的提问来源于stack exchange，提问作者Yuva