R find overlap between strings in columns of two dataframes

user_1987607 0
Viewed 1466

I have the following dataframes df1 and df2. These are just examples, my true dataframes have around 1000 rows & 10 columns.

df1 = data.frame(V1 = c(1,2), V2 = c("a, b, d", "c, g"))

> df1
  V1         V2
1  1    a, b, d
2  2       c, g

df2 = data.frame(V3 = c(1,2,3), V4 = c("a, c", "b, e", "a, g"))

> df2
  V3      V4
1  1    a, c
2  2    b, e
3  3    a, g

As you see, columns V2 and V4 contain values that are separated by ", ".

I now want to loop through my df1 row by row and check if any of the values present in V2 matches with one of the values present in V4. So I don't want to compare the complete column content, but value per value.

This is how my output df3 should look like

df3 = data.frame(V1 = c(1, 1, 1, 2, 2), V2 = c("a, b, d", "a, b, d", "a, b, d", "c, g", "c, g"), V5 = c("a","a","b","c","g"), V3 = c(1, 3, 2, 1, 3), V4 = c("a, c","a, g", "b, e", "a, c", "a, g"), V6 = c("a", "a","b", "c", "g"))

> df3
  V1      V2 V5 V3   V4 V6
1  1 a, b, d  a  1 a, c  a
2  1 a, b, d  a  3 a, g  a
3  1 a, b, d  b  2 b, e  b
4  2    c, g  c  1 a, c  c
5  2    c, g  g  3 a, g  g

Let's look at the first row of df1. In V2 there are values "a", "b" and "d". We start with "a" and look in df2 if "a" is present in V4. This is true for lines 1 and 3 of df2. We create two lines in the output df3. Line 1 contains the info of the first match: original columns V1, V2, V3 and V4 + 2 new columns V5 and V6 which the contain the matched value "a" from df1 and df2 respectively.

I hope this is clear.

I would start with a for loop & I thought it might necessary to split my columns V2 by ',', but I'm not sure this is needed.

for (row in 1:nrow(df1)) {
 // split col V2 of df1 by ','
 // if V2 %in% in df2$V4 ...
}
dataframe r string
3 Answers

Use separate_rows to separate a collapsed column on df1 and df2, and join them together with inner_join.

library(dplyr)
library(tidyr)

df.a <- df1 %>%
  mutate(V5 = V2) %>% 
  separate_rows(V5)

df.b <- df2 %>%
  mutate(V6 = V4) %>% 
  separate_rows(V6)

inner_join(df.a, df.b, c("V5" = "V6"))

#   V1      V2 V5 V3   V4
# 1  1 a, b, d  a  1 a, c
# 2  1 a, b, d  a  3 a, g
# 3  1 a, b, d  b  2 b, e
# 4  2    c, g  c  1 a, c
# 5  2    c, g  g  3 a, g
user_100689850

Here is a base R solution using merge + strsplit

df3 <- within(merge(df1,df2),
              V5 <- unlist(Map(function(x,y) ifelse(length(u <- intersect(x,y)),u,NA),
                               strsplit(as.character(V2),", "),
                               strsplit(as.character(V4),", "))))


df3 <- (df3 <- subset(df3,complete.cases(df3)))[order(df3$V1),]

such that

> df3
  V1      V2 V3   V4 V5
1  1 a, b, d  1 a, c  a
3  1 a, b, d  2 b, e  b
5  1 a, b, d  3 a, g  a
2  2    c, g  1 a, c  c
6  2    c, g  3 a, g  g
user_121587570

Here's another solution that converts to long then does a merge, now with data.table

## Convert to data.table with character columns (rather than factor)
library(data.table)
setDT(df1)[, V2 := as.character(V2)]
setDT(df2)[, V4 := as.character(V4)]

## Find common elements
common <- 
  merge(
    df1[, .(V5 = strsplit(V2, ', ')[[1]]), V1],
    df2[, .(V5 = strsplit(V4, ', ')[[1]]), V3])

common
#    V5 V1 V3
# 1:  a  1  1
# 2:  a  1  3
# 3:  b  1  2
# 4:  c  2  1
# 5:  g  2  3

## Merge back to original data
merge(merge(common, df1, by = 'V1'), df2, by = 'V3')
#    V3 V1 V5      V2   V4
# 1:  1  1  a a, b, d a, c
# 2:  1  2  c    c, g a, c
# 3:  2  1  b a, b, d b, e
# 4:  3  1  a a, b, d a, g
# 5:  3  2  g    c, g a, g

The last step could also be written as a reduce, rather than multiple explicit calls to merge

## Merge back to original data
purrr::reduce2(list(df1, df2), c('V1', 'V3'), merge, .init = common)
#    V3 V1 V5      V2   V4
# 1:  1  1  a a, b, d a, c
# 2:  1  2  c    c, g a, c
# 3:  2  1  b a, b, d b, e
# 4:  3  1  a a, b, d a, g
# 5:  3  2  g    c, g a, g
user_67627550
Related