String match with multiple columns to look for possible result in r

user_14475827 0
Viewed 161

I have two data frames DF1 and DF2. In DF1 I have different sting combinations and in DF2 I have results for different string combinations. I need to match the string or string combinations from DF1 with DF2 and create multiple result columns based on string matches as resulting data frame 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)
match dataframe r
1 Answers

Here is a possible tidyverse solution. It gets an answer similar to your DF_Result but not exactly ("purple,white" matched "abcd" with "silver,white" and "black,white").

The data frames are easier to merge in long form (using pivot_longer). You can use separate_rows to put the comma separated values into 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
user_34606700
Related