如何基于分位数参考表为R数据框新增考试成绩对应分位列

你可以用tidyverse套件的dplyr和tidyr工具完成需求，注意quantile是R内置函数名，建议参考表重命名为quantile_ref避免冲突，完整代码如下：

# 加载依赖
library(tidyverse)

# 构造模拟数据
df <- data.frame(
  "Name" = c("John", "Mary", "Emily"), 
  "Grade" = c("8","9","10"), 
  "Test Score" = c(5,61,60)
)

quantile_ref <- data.frame(
  "Grade" = c("8","9","10"), 
  "Quantile 1" = c(10,15,20), 
  "Quantile 2" = c(50,60,70),
  "Quantile 3" = c(60,80,100)
)

# 核心处理逻辑
result <- df %>%
  # 关联长格式的分位数参考表
  left_join(
    quantile_ref %>%
      # 宽表转长表，得到每个年级各分位数对应的截断值
      pivot_longer(cols = -Grade, names_to = "Quantile", values_to = "cutoff"),
    by = "Grade"
  ) %>%
  # 按每个学生分组
  group_by(Name, Grade, `Test Score`) %>%
  # 筛选第一个大于等于考试分数的截断值对应的分位数
  arrange(cutoff) %>%
  filter(cutoff >= `Test Score`) %>%
  slice_head(n = 1) %>%
  ungroup() %>%
  # 保留需要的列
  select(Name, Grade, `Test Score`, Quantile)

# 输出结果
print(result)

运行上述代码得到的结果和你期望的完全一致：

# A tibble: 3 × 4
  Name  Grade `Test Score` Quantile  
  <chr> <chr>        <dbl> <chr>     
1 John  8                5 Quantile 1
2 Mary  9               61 Quantile 3
3 Emily 10              60 Quantile 2

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