Count matches between two strings

Question

I have two data frames:

df.1 <- data.frame(loc = c('A','B','C','C'), person = c(1,2,3,4), str = c("door / window / table", "window / table / toilet / vase ", "TV / remote / phone / window", "book / vase / car / chair"))

Thus,

  loc person                             str
1   A      1           door / window / table
2   B      2 window / table / toilet / vase 
3   C      3    TV / remote / phone / window
4   C      4       book / vase / car / chair

And,

df.2 <- data.frame(loc = c('A','B','C'), str = c("book / chair / chair", " table / remote / vase ", "window"))

which gives,

  loc                     str
1   A    book / chair / car
2   B  table / remote / vase 
3   C                  window

I want to create a variable df.1$percentage that calculates the percentages of elements in df.1$str that are in df.2$str edit by loc, or:

  loc person                             str percentage
1   A      1           door / window / table       0.00
2   B      2 window / table / toilet / vase        0.50
3   C      3    TV / remote / phone / window       0.25
4   C      4       book / vase / car / chair       0.00

(1 has 0/3, 2 has 2/4 matches, 3 has 1/4, and 4 has 0/4)

Thanks!

Answer 1

As you might know, data.frame columns can also hold lists (see Create a data.frame where a column is a list). So you can split your str into lists of words:

df.1 <- transform(df.1, words.1 = I(strsplit(as.character(str), " / ")))
df.2 <- transform(df.2, words.2 = I(strsplit(as.character(str), " / ")))

Then merge your data:

m <- merge(df.1, df.2, by = "loc")

And simply compute the percentage using mapply:

transform(m, percentage = mapply(function(x, y) sum(x%in%y) / length(x),
                                 words.1, words.2))

Answer 2

Someone can probably come up with a smarter solution, but here's a straightforward approach:

library(data.table)
dt1 = data.table(df.1, key = "loc") # set the key to match by loc
dt2 = data.table(df.2)

dt1[, percentage := dt1[dt2][, # merge
           # clean up spaces and convert to strings
           `:=`(str = gsub(" ", "", as.character(str)),
                str.1 = gsub(" ", "", as.character(str.1)))][,
           # calculate the percentage for each row
           lapply(1:.N, function(i) {
                tmp = strsplit(str, "/")[[i]];
                sum(tmp %in% strsplit(str.1, "/")[[i]])/length(tmp)
           })
   ]]

dt1
#   loc person                             str percentage
#1:   A      1           door / window / table          0
#2:   B      2 window / table / toilet / vase         0.5
#3:   C      3    TV / remote / phone / window       0.25
#4:   C      4       book / vase / car / chair          0