如何用dplyr::left_join实现多徽章列匹配并标记重复匹配行？

实现思路 #

要同时匹配两个徽章列的条件，无法通过单次left_join完成，需拆分两步分别匹配后合并：

1. 分别用df1的badge.number.one、badge.number.two，结合date、district等列与df2做左连接
2. 合并两次连接结果，标记匹配来源
3. 筛选并标记出存在多匹配的行

样本数据 #

# 构造df1
df1 <- data.frame(
  id = c(1, 2, 3),
  date = c("2024-01-01", "2024-01-02", "2024-01-03"),
  district = c("A", "B", "A"),
  badge.number.one = c("1001", "1002", "1003"),
  badge.number.two = c("1004", "1002", "1005"),
  other_col = c("x", "y", "z")
)

# 构造df2
df2 <- data.frame(
  badge.number = c("1001", "1002", "1002", "1004"),
  date = c("2024-01-01", "2024-01-02", "2024-01-02", "2024-01-01"),
  district = c("A", "B", "B", "A"),
  match_data = c("data1", "data2", "data3", "data4")
)

完整代码 #

library(dplyr)

# 1. 分别基于两个徽章列做左连接
join_one <- df1 %>%
  left_join(df2, 
            by = c("date", "district", "badge.number.one" = "badge.number")) %>%
  mutate(match_source = "badge.number.one")

join_two <- df1 %>%
  left_join(df2, 
            by = c("date", "district", "badge.number.two" = "badge.number")) %>%
  mutate(match_source = "badge.number.two")

# 2. 合并结果并对齐列名
combined <- bind_rows(join_one, join_two) %>%
  rename(
    matched_badge = coalesce(badge.number.one, badge.number.two),
    match_data = match_data
  ) %>%
  filter(!is.na(match_data)) # 过滤无匹配的行，如需保留原df1所有行可跳过

# 3. 标记重复匹配行
result <- combined %>%
  group_by(id) %>%
  mutate(
    is_duplicate = n() > 1,  # 同一id有多条匹配则标记为TRUE
    match_count = n()        # 统计匹配次数
  ) %>%
  ungroup()

# 可选：保留原df1中未匹配的行
full_result <- df1 %>%
  left_join(result, by = names(df1)) %>%
  mutate(
    is_duplicate = ifelse(is.na(is_duplicate), FALSE, is_duplicate),
    match_count = ifelse(is.na(match_count), 0, match_count)
  )

样本输出 #

print(full_result)
#   id       date district badge.number.one badge.number.two other_col match_source matched_badge match_data is_duplicate match_count
# 1  1 2024-01-01        A             1001             1004        x badge.number.one         1001     data1        FALSE           1
# 2  1 2024-01-01        A             1001             1004        x badge.number.two         1004     data4        FALSE           1
# 3  2 2024-01-02        B             1002             1002        y badge.number.one         1002     data2         TRUE           2
# 4  2 2024-01-02        B             1002             1002        y badge.number.two         1002     data2         TRUE           2
# 5  2 2024-01-02        B             1002             1002        y badge.number.one         1002     data3         TRUE           2
# 6  2 2024-01-02        B             1002             1002        y badge.number.two         1002     data3         TRUE           2
# 7  3 2024-01-03        A             1003             1005        z          <NA>          <NA>      <NA>        FALSE           0

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