如何基于双变量合并数据框：因子变量精确匹配+数值变量模糊匹配

数据框示例 #

library(dplyr)
set.seed(123)
id <- rep(c("A", "B", "C"), each = 5)
score <- sample(1:50, 15)
label <- paste(sample(LETTERS, 15 * 5, replace = TRUE), collapse = "")
label <- substring(label, seq(1, 71, by = 5), seq(5, 75, by = 5))
df1 <- data.frame(id, score, label)
df1 <- df1[order(df1$id, df1$score), ]
row.names(df1) <- 1:nrow(df1)
df1

# 输出结果
#    id score label
# 1   A     3 JMGII
# 2   A    14 KGULO
# 3   A    15 ISDNQ
# 4   A    31 CHZGJ
# 5   A    42 JWUGU
# 6   B    25 OZPTF
# 7   B    26 KHVVG
# 8   B    37 LMRAY
# 9   B    43 FYBEH
# 10  B    48 YFUOI
# 11  C     5 BDMEV
# 12  C     9 PLYNC
# 13  C    27 PQVRQ
# 14  C    28 NYWCH
# 15  C    40 SYTVY

id <- rep(c("A", "B", "C"), each = 3)
score <- sample(1:50, 9)
df2 <- data.frame(id, score)
df2 <- df2[order(df2$id, df2$score), ]
df2 <- df2 %>% group_by(id) %>% mutate(order = row_number())
row.names(df2) <- 1:nrow(df2)
df2

# 输出结果
# # A tibble: 9 × 3
# # Groups:   id [3]
#   id    score order
# * <chr> <int> <int>
# 1 A        14     1
# 2 A        29     2
# 3 A        32     3
# 4 B         3     1
# 5 B         7     2
# 6 B        23     3
# 7 C        15     1
# 8 C        21     2
# 9 C        37     3

需求说明 #

有两个数据框df1和df2，均包含同名的两列：

• 第一列id：因子变量，需精确匹配
• 第二列score：数值变量，需满足：对df2中的每个score，匹配同id下df1中score差异最小且差异不超过5的所有观测。

示例：df2的第一行（id=A，score=14）应仅与df1的第2行（score=14，差异为0）连接，而不连接df1的第3行（score=15，差异为1）——因为0是最小差异，只保留这个最小差异对应的行。

尝试过fuzzyjoin等包，但无法同时实现精确匹配id和按最小差异+差异上限筛选的逻辑，Stack Overflow上的类似方案也不适用。

解决方案 #

可以通过分组内连接计算每个匹配对的差异，再筛选出每个df2行对应的最小差异且差异≤5的记录：

library(dplyr)

# 1. 按id内连接，得到所有同id的score组合
joined_df <- df2 %>%
  inner_join(df1, by = "id", suffix = c("_df2", "_df1")) %>%
  # 2. 计算score差异的绝对值
  mutate(distance = abs(score_df2 - score_df1)) %>%
  # 3. 按df2的唯一行标识分组
  group_by(id, score_df2, order) %>%
  # 4. 过滤差异≤5的记录，再找到组内最小差异
  filter(distance <= 5) %>%
  mutate(min_distance = min(distance)) %>%
  # 5. 只保留等于最小差异的记录
  filter(distance == min_distance) %>%
  # 6. 整理结果列
  ungroup() %>%
  select(id, score_df2, order, score_df1, label, distance) %>%
  rename(score = score_df2)

查看最终结果：

joined_df
# # A tibble: 4 × 6
#   id    score order score_df1 label distance
#   <chr> <int> <int>     <int> <chr>    <dbl>
# 1 A        14     1        14 KGULO        0
# 2 A        32     3        31 CHZGJ        1
# 3 B        23     3        25 OZPTF        2
# 4 C        37     3        40 SYTVY        3

逻辑说明 #

1. 先通过inner_join按id精确匹配，得到所有同id的score组合
2. 计算每个组合的score差异绝对值
3. 按df2的唯一行（id+score+order）分组，先过滤掉差异超过5的无效记录
4. 找到每组内的最小差异值，再筛选出等于该值的记录，确保只保留差异最小且符合上限的匹配

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