R语言：将查找表与嵌套分组数据框高效合并的方法

先模拟你的数据集与查找表（可直接运行验证） #

library(dplyr)
library(tidyr)

# 模拟患者医疗数据集
set.seed(123)
patient_data <- tibble(
  Gender = sample(c("Male", "Female"), 10000, replace = TRUE),
  Status = sample(c("Inpatient", "Outpatient"), 10000, replace = TRUE),
  Height = rnorm(10000, 170, 10),
  Weight = rnorm(10000, 70, 8),
  Hospital_Visits = sample(1:20, 10000, replace = TRUE),
  Disease = sample(c(0, 1), 10000, replace = TRUE, prob = c(0.7, 0.3))
)

# 嵌套分组计算疾病比例
grouped_result <- patient_data %>%
  group_by(Gender, Status) %>%
  mutate(
    Height_ntile = ntile(Height, 5),
    Weight_ntile = ntile(Weight, 5),
    Hospital_Visits_ntile = ntile(Hospital_Visits, 5)
  ) %>%
  group_by(Gender, Status, Height_ntile, Weight_ntile, Hospital_Visits_ntile, .add = TRUE) %>%
  summarise(Disease_rate = mean(Disease), .groups = "drop")

# 生成全局ntile范围查找表
lookup_table <- bind_rows(
  patient_data %>%
    mutate(Height_ntile = ntile(Height, 5)) %>%
    group_by(Height_ntile) %>%
    summarise(feature = "Height", min_val = min(Height), max_val = max(Height)),
  patient_data %>%
    mutate(Weight_ntile = ntile(Weight, 5)) %>%
    group_by(Weight_ntile) %>%
    summarise(feature = "Weight", min_val = min(Weight), max_val = max(Weight)),
  patient_data %>%
    mutate(Hospital_Visits_ntile = ntile(Hospital_Visits, 5)) %>%
    group_by(Hospital_Visits_ntile) %>%
    summarise(feature = "Hospital_Visits", min_val = min(Hospital_Visits), max_val = max(Hospital_Visits))
)

高效实现方案（替代ifelse） #

ifelse是逐行条件判断，属于循环式操作，数据量越大效率越低。以下两种基于键连接的方案，用向量匹配替代逐行判断，效率提升明显，且代码更易维护。

方案1：多键左连接（适合特征数量少的场景）

直接拆分查找表为各特征单独的表，通过多次左连接合并：

# 拆分查找表为单个特征的映射表
height_lookup <- lookup_table %>% 
  filter(feature == "Height") %>% 
  select(-feature) %>% 
  rename(Height_min = min_val, Height_max = max_val)

weight_lookup <- lookup_table %>% 
  filter(feature == "Weight") %>% 
  select(-feature) %>% 
  rename(Weight_min = min_val, Weight_max = max_val)

hv_lookup <- lookup_table %>% 
  filter(feature == "Hospital_Visits") %>% 
  select(-feature) %>% 
  rename(HV_min = min_val, HV_max = max_val)

# 左连接合并到分组结果
final_result <- grouped_result %>%
  left_join(height_lookup, by = "Height_ntile") %>%
  left_join(weight_lookup, by = "Weight_ntile") %>%
  left_join(hv_lookup, by = "Hospital_Visits_ntile")

方案2：长表转宽表连接（适合特征数量多的场景）

通过长表转换统一处理所有特征，避免手动拆分查找表：

# 将分组结果转成长表，统一ntile列格式
grouped_long <- grouped_result %>%
  pivot_longer(
    cols = ends_with("_ntile"),
    names_to = c("feature", ".value"),
    names_pattern = "(.*)_ntile"
  ) %>%
  rename(ntile = ntile)

# 与查找表连接
merged_long <- grouped_long %>%
  left_join(lookup_table, by = c("feature", "ntile"))

# 转回宽表，恢复原分组结果的结构
final_result_long <- merged_long %>%
  pivot_wider(
    id_cols = c(Gender, Status, Disease_rate),
    names_from = feature,
    values_from = c(ntile, min_val, max_val),
    names_glue = "{feature}_{.value}"
  ) %>%
  # 调整列顺序与原分组结果对齐
  select(Gender, Status, Height_ntile, Weight_ntile, Hospital_Visits_ntile, Disease_rate, everything())

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