R: Merging data with partial matches

user_12220158 0
Viewed 119

I have 2 dataframes which I'm trying to merge together based on whole/partial matches. The 2 dataframes have a column with a matching identifier (ID2), however certain rows have in one of the dataframes can have a combination of identifiers separated by a "|" symbol.

A basic merge of the 2 dataframes results

df1 <- data.frame(
  ID1 = c("A1", "A2", "A3", "A4", "A5"),
  ID2 = c("B1|B2", "B1", "B3", "B6|B4", "B0|B6|B3")
)

df2 <- data.frame(
  ID3 = c("C1", "C2", "C3", "C4", "C5"),
  ID2 = c("B1", "B2", "B3", "B4", "B5")
)

merge(df1, df2, by = "ID2")
  ID2 ID1 ID3
1  B1  A2  C1
2  B3  A3  C3

This results into a dataframe where only 2 matches have been found, but I would also like to find matches for rows such as "B0|B6|B3", where B0 and B6 are not present in the second dataframe, however B3 is still a match between the 2 dataframes.

Desired output would look something like this:

  ID1      ID2   ID3
1  A1    B1|B2 C1|C2
2  A2       B1    C1
3  A3       B3    C3
4  A4    B6|B4    C4
5  A5 B0|B6|B3    C3
r merge
3 Answers

We could use regex_left_join from fuzzyjoin

library(fuzzyjoin)
library(dplyr)
regex_left_join(df1, df2, by = "ID2") %>% 
   group_by(ID1, ID2 = ID2.x) %>% 
   summarise(ID3 = str_c(ID3, collapse="|"), .groups = 'drop')

-output

# A tibble: 5 x 3
  ID1   ID2      ID3  
  <chr> <chr>    <chr>
1 A1    B1|B2    C1|C2
2 A2    B1       C1   
3 A3    B3       C3   
4 A4    B6|B4    C4   
5 A5    B0|B6|B3 C3
user_37322710

A base R option using grepl + sapply + apply

transform(
  df1,
  ID3 = apply(
    sapply(
      df2$ID2,
      function(x) grepl(x, ID2)
    ),
    1,
    function(k) paste0(df2$ID3[k], collapse = "|")
  )
)

gives

  ID1      ID2   ID3
1  A1    B1|B2 C1|C2
2  A2       B1    C1
3  A3       B3    C3
4  A4    B6|B4    C4
5  A5 B0|B6|B3    C3
user_121587570

Get the data in long format using separate_rows splitting on '|' and for each ID1 summarise the values in one concatenated string.

library(dplyr)
library(tidyr)

df1 %>%
  separate_rows(ID2, sep = '\\|') %>%
  left_join(df2, by = "ID2") %>%
  group_by(ID1) %>%
  summarise(across(c(ID2, ID3), ~paste0(na.omit(.), collapse = '|')))

#  ID1   ID2      ID3  
#  <chr> <chr>    <chr>
#1 A1    B1|B2    C1|C2
#2 A2    B1       C1   
#3 A3    B3       C3   
#4 A4    B6|B4    C4   
#5 A5    B0|B6|B3 C3

If for every ID it is guaranteed that there would be at least 1 match in df2 as in the example you may use inner_join and drop na.omit.

user_39629140
Related