如何基于部分匹配列合并两个R数据框？

需求说明 #

拥有HAVE1和HAVE2两个R数据框，需生成WANT数据框：

• 基于NAME列进行精确匹配
• 基于CLASS列进行部分匹配：CLASS1的内容是CLASS2内容的子串，CLASS2包含更多额外字符

原始数据 #

HAVE1 <- data.frame(NAME = c("Amelia", "Amelia", "Amelia", "Amelia", "Ava", "Ava", "Ava", "Charlotte", "Charlotte", "Charlotte", "Charlotte", "Emma", "Emma", "Olivia", "Olivia"), CLASS1 = c("Bee", "Chicken", "Shrimp", "Turkey", "Horse", "Pig", "Sheep", "Deer", "Dove", "Ducks", "Fish", "Crab", "Rabbit", "Cow", "Goat"))

HAVE2 <- data.frame(
  NAME = c("Amelia", "Amelia", "Amelia", "Ava", "Ava", "Ava", "Charlotte", "Charlotte", "Charlotte", "Charlotte", "Emma", "Emma", "Olivia", "Olivia"),
  CLASS2 = c("Chicken", "Some Shrimp", "TurkeyPU", "Horse-Horsey", "Pig-Farn", "Count-Sheep", "Deer Head", "Dove Count", "Ducks Count", "Fishes", "Crab- Count", "Rabbit-Count", "Cow-Type", "Goat-Feed")
)

# 目标结果
WANT <- data.frame(
  NAME = c("Amelia", "Amelia", "Amelia", "Ava", "Ava", "Ava", "Charlotte", "Charlotte", "Charlotte", "Charlotte", "Emma", "Emma", "Olivia", "Olivia"),
  CLASS1 = c("Chicken", "Shrimp", "Turkey", "Horse", "Pig", "Sheep", "Deer", "Dove", "Ducks", "Fish", "Crab", "Rabbit", "Cow", "Goat"),
  CLASS2 = c("Chicken", "Some Shrimp", "TurkeyPU", "Horse-Horsey", "Pig-Farn", "Count-Sheep", "Deer Head", "Dove Count", "Ducks Count", "Fishes", "Crab- Count", "Rabbit-Count", "Cow-Type", "Goat-Feed")
)

解决方案 #

方法1：Tidyverse风格（dplyr + stringr） #

简洁易读，适合习惯tidyverse的用户：

library(dplyr)
library(stringr)

WANT_result <- HAVE1 %>%
  # 按NAME精确合并两个数据框
  inner_join(HAVE2, by = "NAME") %>%
  # 筛选CLASS1是CLASS2子串的记录，fixed=TRUE确保字面匹配（避免正则特殊字符干扰）
  filter(str_detect(CLASS2, fixed(CLASS1))) %>%
  # 去重，确保每个NAME+CLASS1只对应唯一的CLASS2
  distinct(NAME, CLASS1, CLASS2)

方法2：基础R实现 #

无需加载额外包，适合轻量场景：

# 按NAME合并数据框
merged_data <- merge(HAVE1, HAVE2, by = "NAME")
# 筛选符合子串匹配的记录，fixed=TRUE保证字面匹配
WANT_result <- merged_data[grepl(merged_data$CLASS1, merged_data$CLASS2, fixed = TRUE), ]
# 去重，保留唯一的NAME+CLASS1+CLASS2组合
WANT_result <- WANT_result[!duplicated(WANT_result[c("NAME", "CLASS1", "CLASS2")]), ]

结果验证 #

可以通过以下代码验证生成的结果是否与目标一致：

# 忽略行名差异，比较数据内容
all.equal(WANT_result, WANT, check.attributes = FALSE)

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