R语言中基于colour_match有效列动态合并两个data frame

要实现根据colour_match每行的非NA列作为匹配键，为person_pet添加favourite_colour列，可按以下步骤操作：

1. 定义原始数据框 #

person_pet 数据框 #

first_name <- c("Nick", "Emily", "Joe", "Kate", "Kate", "Nick", "Joe")
last_name <- c("Smith", "Doe", "Lee", "Jackson", "Deng", "Smith", "Lee")
age <- c(28, 30, 52, 18, 42, 28, 52)
pet <- c("Cat", "Dog", "Rabbit","Cat", "Mouse", "Snake", "Bird")

person_pet <- data.frame(first_name, last_name, age, pet)

colour_match 数据框 #

first_name <- c("Emily","Nick", "Kate", "Joe", "Kate")
last_name <- c("Doe", "Smith", NA, "Lee", NA)
age <- c(NA, NA, 42, 52, 29)
favourite_colour <- c("black", "yellow", "red", "green", "white")

colour_match <- data.frame(first_name, last_name, age, favourite_colour)

2. 动态匹配合并实现 #

由于colour_match每行的有效匹配键不固定，我们可以逐行提取非NA列作为匹配键，分别完成匹配后合并结果：

使用dplyr实现（直观易读） #

library(dplyr)

# 为每行标记有效匹配键
colour_match <- colour_match %>%
  rowwise() %>%
  mutate(
    key_cols = list(c(
      if(!is.na(first_name)) "first_name",
      if(!is.na(last_name)) "last_name",
      if(!is.na(age)) "age"
    ))
  ) %>%
  ungroup()

# 初始化结果数据框
result <- person_pet %>%
  mutate(favourite_colour = NA_character_)

# 逐行匹配并更新颜色列
for(i in seq_len(nrow(colour_match))) {
  current_row <- colour_match[i, ]
  match_keys <- current_row$key_cols[[1]]
  
  result <- result %>%
    left_join(
      current_row %>% select(all_of(c(match_keys, "favourite_colour"))),
      by = match_keys,
      suffix = c("", "_temp")
    ) %>%
    mutate(
      favourite_colour = coalesce(favourite_colour_temp, favourite_colour)
    ) %>%
    select(-favourite_colour_temp)
}

# 查看最终结果
print(result)

使用data.table实现（高效适用于大数据） #

library(data.table)

setDT(person_pet)
setDT(colour_match)

# 复制原始数据框用于更新
result <- copy(person_pet)
result[, favourite_colour := NA_character_]

# 逐行匹配更新
for(i in seq_len(nrow(colour_match))) {
  current_row <- colour_match[i, ]
  # 提取当前行的非NA键（排除favourite_colour）
  match_keys <- names(current_row)[!is.na(current_row) & names(current_row) != "favourite_colour"]
  
  # 按键匹配并更新颜色
  result[current_row, on = match_keys, favourite_colour := i.favourite_colour]
}

print(result)

结果说明 #

运行代码后将得到符合需求的合并结果：

• Nick Smith通过first_name+last_name匹配，favourite_colour为yellow
• Emily Doe通过first_name+last_name匹配，favourite_colour为black
• Joe Lee（age=52）通过first_name+last_name+age匹配，favourite_colour为green
• Kate Deng（age=42）通过first_name+age匹配，favourite_colour为red
• 其余未匹配的行（如Kate Jackson、Nick Smith（age=28））的favourite_colour保持NA

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