R语言如何逐行比较字符串列元素与其他列的单个值？

问题说明 #

给定如下数据集，需要新增一列Z：逐行检查列X中以逗号分隔的每个元素是否出现在同一行的A-D列中，将匹配到的元素用逗号拼接；若无匹配或X为空，则返回NA。

示例数据集：

df <- data.frame(ID=c(1,2,3,4,5), 
                 A=c("A1","A3","A5","A7","A8"),
                 B=c("B3","B6","B8","B11","B20"),
                 C=c("C2","C1","C2",NA,"C3"),
                 D=c("D7","D11","D1","D5",NA),
                 X=c("A1,B7,B11,C2","A2,A5","A1,B5,B8,D3","","A9,B11,B20,C3"))

期望结果列Z：

Z=c("A1,C2",NA,"B8",NA,"B20,C3")

解决方案 #

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

通过逐行处理拆分字符串、计算交集实现需求：

library(dplyr)
library(stringr)
library(purrr)

df <- df %>%
  rowwise() %>%
  mutate(
    # 拆分X列字符串为元素向量，空字符串返回空向量
    x_elements = ifelse(X == "", list(character(0)), list(str_split(X, ",")[[1]])),
    # 提取当前行A-D列的非NA值作为候选集合
    row_values = list(c(A, B, C, D) %>% discard(is.na)),
    # 计算交集并拼接成字符串，无匹配则返回NA
    Z = case_when(
      length(intersect(x_elements[[1]], row_values[[1]])) == 0 ~ NA_character_,
      TRUE ~ str_c(intersect(x_elements[[1]], row_values[[1]]), collapse = ",")
    )
  ) %>%
  ungroup() %>%
  select(-x_elements, -row_values)

# 查看结果
print(df$Z)

方法二：Base R原生实现 #

无需加载额外包，用apply逐行处理：

df$Z <- apply(df, 1, function(row) {
  x_str <- row["X"]
  # X为空直接返回NA
  if (x_str == "") return(NA)
  # 拆分X的逗号分隔元素
  x_elements <- strsplit(x_str, ",")[[1]]
  # 提取A-D列的非NA值
  row_values <- c(row["A"], row["B"], row["C"], row["D"])
  row_values <- row_values[!is.na(row_values)]
  # 筛选匹配元素
  matches <- intersect(x_elements, row_values)
  # 无匹配返回NA，否则拼接成字符串
  if (length(matches) == 0) NA else paste(matches, collapse = ",")
})

# 查看结果
print(df$Z)

两种方法最终都能生成符合期望的Z列结果。

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