R语言下含重复值数据表合并及多标签分类实现方案咨询

现有两个均包含重复值的dataframe，需要合并为一个大数据框作为机器学习算法的输入。核心难点包含两张数据表的关联逻辑，同时合并后的数据集需要满足多类别预测需求。因原始数据集为医疗领域涉密数据，以下为贴近真实场景的虚构可复现代码：

library(data.table)
library(dplyr)
library(tidyr)

data1 <- data.table("color" = c("green", "green", "red", "red", "blue", "blue", "blue", "red", "pink"),
"type" = c("SUV", "SUV", "SEDAN", "SEDAN", "SEDAN", "TRUCK", "TRUCK", "CABRIO", "CABRIO"),
"NUM_SEATS" = c(4,4,5,4,4,3,3,2,2),
"MODELL_ID" = c("xyz", "xyz", "abc", "abc", "abc", "rtz", "rtz", "ghj", "ghj"))

data2 <- data.table("BRAND" = c("VW", "VW", "VW", "AUDI", "AUDI", "BMW", "BMW", "GM", "GM"),
"year_quarter" = c("20173", "20173", "20174", "20174", "20171", "20181", "20162", "20172", "20192"),
"MODELL_ID" = c("xyz", "xyz", "abc", "abc", "abc", "rtz", "rtz", "ghj", "ghj"))
data1 <- data1 %>% group_by(MODELL_ID) %>% mutate(time = row_number()) %>% ungroup()
data2 <- data2 %>% group_by(MODELL_ID) %>% mutate(time = row_number()) %>% ungroup()

data1_temp <- data1 %>% pivot_wider(names_from = time, values_from = c(-MODELL_ID), names_sort = TRUE, names_sep = "-")
data2_temp <- data2 %>% pivot_wider(names_from = time, values_from = c(-MODELL_ID), names_sort = TRUE, names_sep = "-")
data_join <- inner_join(data1_temp, data2_temp, by = c("MODELL_ID")) %>% select(-starts_with(c("n.", "time"))) %>% pivot_wider(names_from = "MODELL_ID", values_from = "MODELL_ID", names_prefix = "MODELL_ID-") %>% as.matrix()
data_join[is.na(data_join)] <- "0"

x_data <- data_join %>% as.data.table() %>% select(-starts_with("MODELL_ID-"))
y_data <- data_join %>% as.data.table() %>% select(starts_with("MODELL_ID-"))

x_data # input (unvectorized)
y_data # output (unvectorized)

x_data %>% data.matrix()-1 # input (vectorized)
y_data %>% data.matrix()-1 # output (vectorized)

上述代码中x_data为输入特征，y_data对应MODELL_ID标签，需求为搭建可落地的机器学习方案，实现给定单条X数据即可预测所有可能的MODELL_ID的效果。此前尝试前馈神经网络等方案未达到预期效果，以下为具体实现方案：

核心优化思路 #

• 修正数据预处理逻辑：现有预处理流程中的pivot_wider处理方式会把同个MODELL_ID的多组特征拉成宽表，本质上是把时序/重复观测当成了独立特征，没有利用重复值的统计规律，反而引入了大量稀疏特征，是模型效果差的核心原因。
• 替换关联逻辑：不需要用pivot_wider做宽表关联，直接按MODELL_ID+time做双字段内连接，保留每条独立观测作为训练样本，大幅降低特征维度。
• 多类别预测适配：现有y_data为手动生成的独热编码格式，不需要手动做这一步，直接用原始MODELL_ID作为分类标签即可，大部分分类模型支持直接输入字符型标签，或自动完成标签编码。

可落地实现代码 #

1. 依赖包导入 #

library(data.table)
library(dplyr)
library(caret)
library(xgboost)

2. 原始数据生成（与示例逻辑一致） #

data1 <- data.table("color" = c("green", "green", "red", "red", "blue", "blue", "blue", "red", "pink"),
"type" = c("SUV", "SUV", "SEDAN", "SEDAN", "SEDAN", "TRUCK", "TRUCK", "CABRIO", "CABRIO"),
"NUM_SEATS" = c(4,4,5,4,4,3,3,2,2),
"MODELL_ID" = c("xyz", "xyz", "abc", "abc", "abc", "rtz", "rtz", "ghj", "ghj"))

data2 <- data.table("BRAND" = c("VW", "VW", "VW", "AUDI", "AUDI", "BMW", "BMW", "GM", "GM"),
"year_quarter" = c("20173", "20173", "20174", "20174", "20171", "20181", "20162", "20172", "20192"),
"MODELL_ID" = c("xyz", "xyz", "abc", "abc", "abc", "rtz", "rtz", "ghj", "ghj"))

# 生成重复观测的序号
data1 <- data1 %>% group_by(MODELL_ID) %>% mutate(time = row_number()) %>% ungroup()
data2 <- data2 %>% group_by(MODELL_ID) %>% mutate(time = row_number()) %>% ungroup()

3. 修正后的数据合并逻辑 #

# 双字段关联，保留每条独立观测
data_join <- inner_join(data1, data2, by = c("MODELL_ID", "time"))
# 拆分特征和标签
x_data <- data_join %>% select(-MODELL_ID, -time)
y_data <- factor(data_join$MODELL_ID)

4. 特征编码 #

# 分类特征独热编码，数值特征保留原值
dummies <- dummyVars(~ ., data = x_data)
x_encoded <- predict(dummies, newdata = x_data)

5. 模型训练（采用XGBoost，适配结构化数据分类场景，效果优于普通前馈神经网络） #

# 转换为XGBoost要求的输入格式
dtrain <- xgb.DMatrix(data = x_encoded, label = as.integer(y_data)-1)
# 多分类参数设置
params <- list(
  objective = "multi:softprob",
  num_class = length(levels(y_data)),
  eta = 0.1,
  max_depth = 3,
  eval_metric = "mlogloss"
)
# 模型训练
xgb_model <- xgb.train(
  params = params,
  data = dtrain,
  nrounds = 50,
  verbose = 0
)

6. 预测实现（输出所有可能的MODELL_ID及对应概率） #

# 示例：取第一条数据做预测
test_sample <- x_encoded[1,,drop=FALSE]
pred_prob <- predict(xgb_model, newdata = test_sample)
# 转换为每个类别的概率表
pred_df <- data.frame(
  MODELL_ID = levels(y_data),
  probability = pred_prob
)
# 按概率降序排列，输出所有可能结果
pred_df <- pred_df[order(-pred_df$probability),]
print(pred_df)

方案优势 #

• 特征维度从原方案的数十级降到10级左右，无冗余稀疏特征，模型收敛速度快、精度高
• 直接利用所有重复观测样本，样本量从原方案的4条提升到9条，避免小样本过拟合
• 输出所有类别的概率，可根据业务阈值筛选TopN的可能MODELL_ID，满足多预测结果输出需求

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