Match on substring and other variables

user_7742354 0
Viewed 50

I am trying to merge two dataset on key values and string patterns. Basically, I would like a function to count the number of sub-string matching occurrences, conditional on other key variables matching across two db.

Across two datasets, base and merge, when the variables k1 and k2 match and merge$str2 is included in base$str I would like a function to count one match. Specifically, I would like whole words match whole words (e.g. "BB" is not a match for "BBB", as if the first was written ad "\bBB\b"). I have multi-million dataset to merge so efficiency is paramount.

I have a possible solution, but I am not sure is the best across very large datasets.

base <- data.frame(k1=rep(1,4),
                   k2=c(rep(1,3),2),
                   str=c("AA BBB","BB CCC","CCC","CCC")) 

merge <- data.frame(k1=rep(1,2),
                   k2=rep(1,2),
                   str2=c("BB","CCC")) 


library(stringr)
library(dplyr)
library(tidyr)

base %>% 
  left_join(merge) %>% 
  mutate(match=stringi::stri_detect_regex(str, paste0("\\b",str2,"\\b")),
         match=replace_na(ifelse(match==T,1,0),0)) %>% 
  group_by(k1,k2,str) %>% 
  summarise(matches=sum(match))


desired_result <- data.frame(k1=rep(1,4),
                    k2=c(rep(1,3),2),
                    str=c("AA BBB","BB CCC","CCC","CCC"),
                    match=c(0,2,1,0))
stringi stringr dplyr tidyverse r
1 Answers

Each time efficiency is an issue, data.table is worth to consider .. May be this could help you ..

library(data.table)
m <- merge(as.data.table(base), 
      as.data.table(merge),
      by = c("k1", "k2"),
      all = TRUE, 
      allow.cartesian=TRUE)
m[, i := grepl(paste0("\\b", str2,"\\b", sep = ""), str), by = 1:nrow(m)]
m[, .(match = sum(i, na.rm = TRUE)), by = c("k1","k2", "str")]
user_90883780
Related