如何基于另一数据框为数据框中人员匹配相同ID（无匹配填NA）

我有两个数据集df和df2，是大型杂乱数据框的简化版本。在df中，我已按belt（腰带）和weight（体重）分组，为每个人创建了唯一id。现在需要将df中对应人员的id匹配到df2中，匹配条件是姓名相同且属于同一belt和weight分组。df2中存在不在df里的人员，这类人员的id需填充为NA。需注意：同一人在不同体重级别参赛时id不同，必须严格按belt和weight分组匹配。

原始数据集df #

belt     weight rank id        name
1  purple open class    1 55  Tom Cruise
2   black    rooster    2 79 Emma Watson
3    blue    feather    3 63    John Doe
4    blue    feather    4 63    John Doe
5  purple open class    5 55  Tom Cruise
6   brown      heavy    6  3  James Bond
7  purple open class    7 55  Tom Cruise
8  purple      heavy    8 61  Tom Cruise
9   black open class    9 70    Jane Doe
10 purple      heavy   10 61  Tom Cruise

待匹配数据集df2 #

belt2    weight2 rank2        name points
1  purple open class     1  Tom Cruise    100
2   black    rooster     2 Emma Watson     30
3    blue    feather     3    John Doe     50
4    blue    feather     4    John Doe     50
5  purple open class     5  Tom Cruise    100
6   brown      heavy     6  James Bond    200
7   black    rooster     7    Jon Snow     92
8  purple      heavy     8  Tom Cruise     77
9   black open class     9    Jane Doe     88
10 purple      heavy    10  Tom Cruise     77

期望结果 #

belt2    weight2 rank2 id           name points
1  purple open class     1 55     Tom Cruise    100
2   black    rooster     2 79    Emma Watson     30
3    blue    feather     3 63       John Doe     50
4    blue    feather     4 63       John Doe     50
5  purple open class     5 55     Tom Cruise    100
6   brown      heavy     6  3     James Bond    200
7   black    rooster     7 NA       Jon Snow     92
8  purple      heavy     8 61     Tom Cruise     77
9   black open class     9 70       Jane Doe     88
10 purple      heavy    10 61     Tom Cruise     77

数据集构建代码 #

# df
belt <- c("purple", "black", "blue", "blue", "purple", "brown", "purple", "purple", "black", "purple")
weight <- c("open class", "rooster", "feather", "feather", "open class", "heavy", "open class", "heavy", "open class", "heavy")
rank <- 1:10
id <- c(55, 79, 63, 63, 55, 3, 55, 61, 70, 61)
names <- c("Tom Cruise", "Emma Watson", "John Doe", "John Doe", "Tom Cruise", "James Bond", "Tom Cruise", "Tom Cruise", "Jane Doe", "Tom Cruise")
(df <- data.frame(belt, weight, rank, id, name = names))

#df2
belt2 <- c("purple", "black", "blue", "blue", "purple", "brown", "black", "purple", "black", "purple")
weight2 <- c("open class", "rooster", "feather", "feather", "open class", "heavy", "rooster", "heavy", "open class", "heavy")
rank2 <- 1:10
names2 <- c("Tom Cruise", "Emma Watson", "John Doe", "John Doe", "Tom Cruise", "James Bond", "Jon Snow", "Tom Cruise", "Jane Doe", "Tom Cruise")
points <- c(100, 30, 50, 50, 100, 200, 92, 77, 88, 77)
(df2 <- data.frame(belt2, weight2, rank2, name = names2, points))

解决方案 #

方法1：Base R实现 #

通过创建唯一映射表，再进行左连接匹配：

# 提取df中唯一的(belt, weight, name)与id的映射关系
id_map <- unique(df[, c("belt", "weight", "name", "id")])

# 对齐df2的列名，方便合并
colnames(df2)[1:2] <- c("belt", "weight")

# 左连接匹配id，保留df2所有行，无匹配则id为NA
result <- merge(df2, id_map, by = c("belt", "weight", "name"), all.x = TRUE)

# 恢复df2原列名
colnames(result)[1:2] <- c("belt2", "weight2")

# 调整列顺序与期望结果一致
result <- result[, c("belt2", "weight2", "rank2", "id", "name", "points")]

print(result)

方法2：dplyr包实现（更简洁） #

使用dplyr的管道语法完成匹配：

library(dplyr)

result_df2 <- df2 %>%
  # 重命名列以匹配df的列名
  rename(belt = belt2, weight = weight2) %>%
  # 左连接df的唯一映射表，匹配三列
  left_join(unique(df[, c("belt", "weight", "name", "id")]), 
            by = c("belt", "weight", "name")) %>%
  # 恢复原列名
  rename(belt2 = belt, weight2 = weight) %>%
  # 调整列顺序
  select(belt2, weight2, rank2, id, name, points)

print(result_df2)

两种方法均可得到符合要求的结果，df2中不存在于df的人员id会自动填充为NA。

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