Counting matches between two lines

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")) 

In this way,

  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")) 

what 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 , which 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 may know, data.frame columns can also contain lists (see Create data.frame, where a column is a list ). So you can split str into word lists:

 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 just calculate the percentage using mapply :

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

Answer 2

Maybe someone can come up with a more reasonable solution, but there is a simple approach here:

 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 

Answer 3

Alternative way

 test <- data.frame(str1 = df.1[1:nrow(df.2),]$str, str2 = df.2$str) df.1$percent <- NA getwords <- function(x) { gsub(" ","",unlist(strsplit(as.character(x),"/"))) } percent <- function(x,y) { sum(!is.na(unlist(sapply(getwords(x), function (d) grep(d, getwords(y))))))/ length(getwords(x)) } df.1[1:nrow(df.2),]$percent <- apply(test, 1, function(x) percent(x[1],x[2])) > df.1 loc person str percent # A 1 door / window / table 0.00 # B 2 window / table / toilet / vase 0.50 # C 3 TV / remote / phone / window 0.25 # C 4 book / vase / car / chair NA