不合并数据框，在R中按组计算df1值相对df2的百分位排名

问题背景 #

现有两个R数据框df1和df2，需在不合并两数据框、不修改df2的前提下，按组计算df1中每个value相对于df2同组value的百分位排名（与Excel PERCENTILERANK.INC逻辑一致），并将排名结果作为新列添加到df1中。

测试数据 #

library(tibble)

df1 <- tibble(name = c("J1","J2","J3","J4","J5","J6","J7","J8","J9","J10"), 
              value = c(1,2,3,4,5,6,7,8,9,10), 
              group = c("group1","group2","group3","group1","group2","group3","group1","group2","group3","group1"))

df2 <- tibble(name = c("k1","k2","k3","k4","k5","k6","k7","k8","k9","k10"), 
              value = c(1,2,3,4,5,6,7,8,9,10), 
              group = c("group1","group2","group3","group1","group2","group3","group1","group2","group3","group1"))

解决方案 #

方法1：dplyr分组计算（完全匹配Excel逻辑）

Excel PERCENTILERANK.INC的核心逻辑：对值x，其百分位排名 = (小于x的数值个数 + 0.5*等于x的数值个数) / (同组数据总个数 - 1)。用dplyr分组后逐组计算：

library(dplyr)

df1 <- df1 %>%
  group_by(group) %>%
  mutate(percent_rank_inc = {
    # 提取当前组的df2数值集合
    df2_group_vals <- df2$value[df2$group == cur_group()$group]
    total <- length(df2_group_vals)
    # 对每个value计算百分位排名
    sapply(value, function(x) {
      count_less <- sum(df2_group_vals < x)
      count_equal <- sum(df2_group_vals == x)
      (count_less + 0.5 * count_equal) / (total - 1)
    })
  }) %>%
  ungroup()

方法2：基础R循环实现

若不想依赖dplyr，用基础R循环也能完成需求：

# 获取所有唯一分组
unique_groups <- unique(df1$group)
# 初始化新列
df1$percent_rank_inc <- NA_real_

# 循环处理每个分组
for (g in unique_groups) {
  # 筛选当前分组的df1行和df2数值
  df1_mask <- df1$group == g
  df2_vals <- df2$value[df2$group == g]
  total_vals <- length(df2_vals)
  
  # 计算当前分组所有value的百分位排名
  df1$percent_rank_inc[df1_mask] <- sapply(df1$value[df1_mask], function(x) {
    count_less <- sum(df2_vals < x)
    count_equal <- sum(df2_vals == x)
    (count_less + 0.5 * count_equal) / (total_vals - 1)
  })
}

结果验证 #

以df1的group1为例，df2的group1数值为c(1,4,7,10)，对应df1的value分别为1、4、7、10，计算得到的百分位排名依次为0、0.333...、0.666...、1，完全符合PERCENTILERANK.INC的输出结果。

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