在R中按分组匹配另一数据框阈值生成Level变量的方法

现有两个数据框：

• 数据框df1包含国家、地区和数值（字符型，含"NA"）
• 数据框df2为每个国家定义了9个阈值（p1-p9）

需要为df1新增Level列，规则如下：

• 按Country分组，将Value与对应国家的阈值匹配
• 找到第一个大于当前Value的阈值对应的pX名称作为Level
• 若Value大于所有阈值，取最大的阈值名称p9
• 若Value为NA，Level也为NA

原始数据 #

数据框df1 #

df1 <- structure(list(Country = c("A", "A", "A", "A", "A", "A", "B", 
"B", "B", "B", "B", "B", "C", "C", "C", "C", "C", "C"), District = c(1, 
2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6), Value = c("90", 
"700", "2500", "4500", "14000", "14500", "900", "1750", "5000", 
"70", "29000", "10000", "NA", "90", "4000", "NA", "7000", "1000"
)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-18L))

数据框df2（阈值表） #

df2 <- structure(list(Country = c("A", "B", "C"), p1 = c(80, 90, 110
), p2 = c(100, 110, 200), p3 = c(900, 1000, 3000), p4 = c(1600, 
2000, 5000), p5 = c(2000, 4500, 7000), p6 = c(3000, 7000, 9000
), p7 = c(5000, 13000, 15000), p8 = c(9000, 15000, 20000), p9 = c(15000, 
20000, 25000)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-3L))

解决方案 #

方法1：使用tidyr+dplyr的长格式匹配 #

先将阈值表转为长格式，再通过分组筛选匹配对应的Level：

library(dplyr)
library(tidyr)

# 处理df1的Value列，转为数值型（字符"NA"转为真正的NA）
df1_clean <- df1 %>% mutate(Value = as.numeric(Value))

# 将阈值表转为长格式，按国家和阈值排序
df2_long <- df2 %>%
  pivot_longer(cols = starts_with("p"), 
               names_to = "Level", 
               values_to = "Threshold") %>%
  arrange(Country, Threshold)

# 匹配生成Level列
result <- df1_clean %>%
  left_join(df2_long, by = "Country") %>%
  group_by(Country, District, Value) %>%
  # 筛选大于等于当前Value的阈值，或Value为NA的情况
  filter(Threshold >= Value | is.na(Value)) %>%
  # 取最小的符合条件的阈值对应的Level
  slice_min(Threshold, n = 1) %>%
  ungroup() %>%
  # 为NA的Value设置Level为NA
  mutate(Level = ifelse(is.na(Value), NA_character_, Level)) %>%
  select(Country, District, Value, Level)

# 查看结果
result

方法2：使用group_map+findInterval #

针对分组场景优化findInterval的使用，直接按国家匹配阈值：

library(dplyr)

# 处理df1的Value列
df1_clean <- df1 %>% mutate(Value = as.numeric(Value))

# 按国家分组匹配阈值
result <- df1_clean %>%
  group_by(Country) %>%
  group_map(function(group_data, group_info) {
    # 获取当前国家的阈值和对应的Level名称
    country_thresholds <- df2 %>% 
      filter(Country == group_info$Country) %>%
      select(starts_with("p")) %>%
      unlist()
    level_names <- names(country_thresholds)
    
    # 使用findInterval计算区间索引
    idx <- findInterval(group_data$Value, country_thresholds, rightmost.closed = TRUE)
    
    # 根据索引匹配Level，处理NA和超出最大阈值的情况
    group_data$Level <- case_when(
      is.na(group_data$Value) ~ NA_character_,
      idx == length(country_thresholds) ~ level_names[idx], # Value大于所有阈值，取最后一个Level
      TRUE ~ level_names[idx + 1] # 取第一个大于Value的阈值对应的Level
    )
    group_data
  }) %>%
  bind_rows()

# 查看结果
result

两种方法都能生成符合要求的结果，最终输出与预期一致。

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