与多列进行字符串匹配以查找 r 中可能的结果

我有两个数据框 DF1 和 DF2。在 DF1 中，我有不同的字符串组合，在 DF2 中，我有不同字符串组合的结果。我需要将 DF1 与 DF2 中的字符串或字符串组合进行匹配，并根据字符串匹配创建多个结果列作为结果数据帧 DF_Result。

A=c("babypink","red,blue","purple,white","skyblue","pink,violet,green","silver,white,grey")

DF1 <- data.frame(A)

P=c("abcd","qwert","wxyz","efgh")
Q=c("red,blue","red","orange,yellow","white,black")
R=c("pink","violet,green","purple,white","golden")
S=c("silver,white","orange","grey","maroon")
T=c("black,white","skyblue","babypink","green")
U=c("yellow","blue","black","white")
DF2=data.frame(P,Q,R,S,T,U)

X=c("babypink","red,blue","purple,white","skyblue","pink,violet,green","silver,white,grey")
R1=c("wxyz","abcd","wxyz","qwert","abcd","abcd")
R2=c("","qwert","efgh","","qwert","wxyz") 
R3=c("","","","","efgh","efgh") 
DF_Result=data.frame(A,R1,R2,R3)

这是一个可能的tidyverse解决方案。它得到的答案类似于你的DF_Result但不完全一样（“紫色，白色”将“abcd”与“银色，白色”和“黑色，白色”匹配）。

数据框更容易以长形式合并（使用pivot_longer）。您可以使用separate_rows将逗号分隔的值放入单独的行中。

library(tidyverse)

DF2_long <- DF2 %>%
  pivot_longer(cols = -P) %>%
  separate_rows(value)
  
DF1 %>%
  mutate(value = A) %>%
  separate_rows(value) %>%
  left_join(DF2_long) %>%
  select(-name, -value) %>%
  group_by(A) %>%
  distinct(A, P) %>%
  mutate(Count = row_number()) %>%
  pivot_wider(id_cols = A, names_from = Count, values_from = P, names_prefix = "R")

Output

  A                 R1    R2    R3   
  <chr>             <chr> <chr> <chr>
1 babypink          wxyz  NA    NA   
2 red,blue          abcd  qwert NA   
3 purple,white      wxyz  abcd  efgh 
4 skyblue           qwert NA    NA   
5 pink,violet,green abcd  qwert efgh 
6 silver,white,grey abcd  wxyz  efgh