R语言多匹配数据合并时按Value排序拼接结果的实现方法

问题说明 #

现有两份待处理数据集：

input_A <- data.frame(ID = c(1,2), some_var = c("bla","more bla"))

input_B <- structure(list(ID = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 
2, 2), year = c(2001, 2002, 2003, 2001, 2002, 2003, 2001, 2002, 
2003, 2001, 2002, 2003, 2001, 2002, 2003), Type = c("A", "A", 
"A", "B", "B", "B", "A", "A", "A", "B", "B", "B", "C", "C", "C"
), Subtype = c(2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2), 
    Value = c(0.480513615083894, 0.909788893002047, 0.685141970365005, 
    0.138835747632889, 0.899508237239289, 0.535632890739584, 
    0.0712054637209442, 0.655905506366812, 0.694753916517691, 
    0.469249523993816, 0.295044859429007, 0.209906890342936, 
    0.193574644156237, 0.0715219759792846, 0.626529278499682)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -15L))

input_B数据预览：

# A tibble: 15 × 5
      ID  year Type  Subtype  Value
   <dbl> <dbl> <chr>   <dbl>  <dbl>
 1     1  2001 A           2 0.481 
 2     1  2002 A           2 0.910 
 3     1  2003 A           2 0.685 
 4     1  2001 B           1 0.139 
 5     1  2002 B           1 0.900 
 6     1  2003 B           1 0.536 
 7     2  2001 A           1 0.0712
 8     2  2002 A           1 0.656 
 9     2  2003 A           1 0.695 
10     2  2001 B           1 0.469 
11     2  2002 B           1 0.295 
12     2  2003 B           1 0.210 
13     2  2001 C           2 0.194 
14     2  2002 C           2 0.0715
15     2  2003 C           2 0.627

合并约束 #

• 直接按ID + year关联会出现一对多匹配，无法得到需要的结果
• 实际业务场景下Type和Subtype的组合量级极大，无法通过手动枚举dcast的转换规则实现宽表转换

合并规则 #

按ID + year分组，组内记录按Value从高到低排序，排名第1的作为第一匹配项，排名第2的作为第二匹配项，直到组内无剩余记录，最终和input_A合并输出宽表。
之前尝试过循环提取第一匹配项后删除input_B已匹配行的方案，代码如下但实现过于繁琐，需要更简洁高效的方案：

inputA[inputB, mult = "first", on = "ID", nomatch=0L]

期望输出 #

output <- structure(list(ID = c(1, 1, 1, 2, 2, 2), some_var = c("bla", 
"bla", "bla", "more bla", "more bla", "more bla"), year = c(2001, 
2002, 2003, 2001, 2002, 2003), Type_1 = c("A", "A", "A", "A", 
"A", "A"), Subtype_1 = c(2, 2, 2, 1, 1, 1), Value_1 = c(0.480513615083894, 
0.909788893002047, 0.685141970365005, 0.0712054637209442, 0.655905506366812, 
0.694753916517691), Type_2 = c("B", "B", "B", "B", "B", "B"), 
    Subtype_2 = c(1, 1, 1, 1, 1, 1), Value_2 = c(0.138835747632889, 
    0.899508237239289, 0.535632890739584, 0.469249523993816, 
    0.295044859429007, 0.209906890342936), Type_3 = c(NA, NA, 
    NA, "C", "C", "C"), Subtype_3 = c(NA, NA, NA, 2, 2, 2), Value_3 = c(NA, 
    NA, NA, 0.193574644156237, 0.0715219759792846, 0.626529278499682
    )), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-6L))

期望输出预览：

# A tibble: 6 × 12
     ID some_var  year Type_1 Subtype_1 Value_1 Type_2 Subtype_2 Value_2 Type_3 Subtype_3 Value_3
  <dbl> <chr>    <dbl> <chr>      <dbl>   <dbl> <chr>      <dbl>   <dbl> <chr>      <dbl>   <dbl>
1     1 bla       2001 A              2  0.481  B              1   0.139 NA            NA NA     
2     1 bla       2002 A              2  0.910  B              1   0.900 NA            NA NA     
3     1 bla       2003 A              2  0.685  B              1   0.536 NA            NA NA     
4     2 more bla  2001 A              1  0.0712 B              1   0.469 C              2  0.194 
5     2 more bla  2002 A              1  0.656  B              1   0.295 C              2  0.0715
6     2 more bla  2003 A              1  0.695  B              1   0.210 C              2  0.627 

实现代码 #

使用data.table实现，无需循环、无需提前枚举类型组合，自动适配每个分组的匹配项数量，大数据量下性能优异：

library(data.table)
# 转换为data.table格式
setDT(input_A)
setDT(input_B)

# 按ID+year分组，Value降序排序后生成匹配排名
input_b_ranked <- input_B[order(ID, year, -Value)][
  , match_rank := seq_len(.N), by = .(ID, year)
]

# 自动转宽表，生成对应数量的Type/Subtype/Value列
wide_b <- dcast(
  input_b_ranked, 
  ID + year ~ match_rank, 
  value.var = c("Type", "Subtype", "Value"), 
  sep = "_"
)

# 关联input_A得到最终结果
result <- merge(input_A, wide_b, by = "ID", all.x = TRUE)

注：如果需要按数据原始行顺序而非Value降序排名，去掉排序步骤中的-Value即可。运行后会自动根据所有ID+year分组中的最大匹配项数生成对应列，匹配项不足的分组自动填充NA，完全符合输出要求。

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