R - Regex matching on elements in character string

Question

I want to know if any elements in character string occur in other character string.

My data contains of millions of rows and is structures as the the following mock data:

dt <- data.table(product = c("A", "B", "C", "A,C,E", "A,B", "A,B,C", "D", "A", "B", "A", "A", "A,B,C", "D", "D"),
              stock = c("A", "A,B", "A,B,C", "A,B,C,E", "A,B,C,E", "A,B,C,E", "A,B,C,D,E", "A", "A,B", "A,B", "A", "A,B,C", "A,B,C,D", "A,B,C,D"), stringsAsFactors = F)


dt[, product.2 := shift(product, type = "lead")]
dt[, product.3 := shift(product, n = 2, type = "lead")]

> dt
product     stock product.2 product.3
 1:       A         A         B         C
 2:       B       A,B         C     A,C,E
 3:       C     A,B,C     A,C,E       A,B
 4:   A,C,E   A,B,C,E       A,B     A,B,C
 5:     A,B   A,B,C,E     A,B,C         D
 6:   A,B,C   A,B,C,E         D         A
 7:       D A,B,C,D,E         A         B
 8:       A         A         B         A
 9:       B       A,B         A         A
10:       A       A,B         A     A,B,C
11:       A         A     A,B,C         D
12:   A,B,C     A,B,C         D         D
13:       D   A,B,C,D         D      <NA>
14:       D   A,B,C,D      <NA>      <NA>

From this data I would like to know the following:

If any element (separated by comma) in product.3 occurs in product.2
If any element (separated by comma) in product.3 does not occur in stock.
Do all elements (separated by comma) in product.3 occur in stock?

The expected outcome is something like this:

> dt
product     stock product.2 product.3 outcome1 outcome2 outcome3
 1:       A         A         B         C    FALSE    FALSE    FALSE
 2:       B       A,B         C     A,C,E     TRUE     TRUE    FALSE
 3:       C     A,B,C     A,C,E       A,B     TRUE     TRUE     TRUE
 4:   A,C,E   A,B,C,E       A,B     A,B,C     TRUE     TRUE     TRUE
 5:     A,B   A,B,C,E     A,B,C         D    FALSE    FALSE    FALSE
 6:   A,B,C   A,B,C,E         D         A    FALSE     TRUE     TRUE
 7:       D A,B,C,D,E         A         B    FALSE     TRUE     TRUE
 8:       A         A         B         A    FALSE     TRUE     TRUE
 9:       B       A,B         A         A     TRUE     TRUE     TRUE
10:       A       A,B         A     A,B,C     TRUE     TRUE    FALSE
11:       A         A     A,B,C         D    FALSE    FALSE    FALSE
12:   A,B,C     A,B,C         D         D     TRUE    FALSE    FALSE
13:       D   A,B,C,D         D      <NA>    FALSE    FALSE    FALSE
14:       D   A,B,C,D      <NA>      <NA>    FALSE    FALSE    FALSE

This question is part of this question on Stackoverflow.

EDIT 08/20/2019: Included a third expected outcome.

akrun · Accepted Answer · 2019-08-21T16:06:18.070

library(data.table)
library(stringr)

According to ?str_detect

Vectorised over string and pattern. Equivalent to grepl(pattern, x). See str_which() for an equivalent to grep(pattern, x).

So, one option is to replace the , with | (or matches) and directly compare the corresponding elements of 'product.2' with 'product.3' similarly with 'stock' comparison on 'product.3'. Then, replace the NA elements with FALSE with set

dt[, outcome1 := str_detect(product.2, str_replace_all(product.3, ",", "|"))]
dt[, outcome2 := str_detect(stock, str_replace_all(product.3, ",", "|"))]
for(j in names(dt)[5:6]) set(dt, i = which(is.na(dt[[j]])), j = j, value = FALSE)
dt
#    product     stock product.2 product.3 outcome1 outcome2
# 1:       A         A         B         C    FALSE    FALSE
# 2:       B       A,B         C     A,C,E     TRUE     TRUE
# 3:       C     A,B,C     A,C,E       A,B     TRUE     TRUE
# 4:   A,C,E   A,B,C,E       A,B     A,B,C     TRUE     TRUE
# 5:     A,B   A,B,C,E     A,B,C         D    FALSE    FALSE
# 6:   A,B,C   A,B,C,E         D         A    FALSE     TRUE
# 7:       D A,B,C,D,E         A         B    FALSE     TRUE
# 8:       A         A         B         A    FALSE     TRUE
# 9:       B       A,B         A         A     TRUE     TRUE
#10:       A       A,B         A     A,B,C     TRUE     TRUE
#11:       A         A     A,B,C         D    FALSE    FALSE
#12:   A,B,C     A,B,C         D         D     TRUE    FALSE
#13:       D   A,B,C,D         D      <NA>    FALSE    FALSE
#14:       D   A,B,C,D      <NA>      <NA>    FALSE    FALSE

The first two lines of code can be simplified as @d.b mentioned

dt[, paste0("outcome", 1:2) := lapply(.SD, str_detect, 
 pattern = str_replace_all(product.3, ",", "|")), .SDcols = c("product.2", "stock")]

Update

With the update in OP's question

dt[, paste0("outcome", 1:2) := lapply(.SD, function(x) 
     str_detect(product.3, str_replace_all(x, ",", "|"))), 
          .SDcols = c('product.2', 'stock')]
dt[, outcome3 :=unlist(Map(function(x, y) {
       x1 <- sort(x[!is.na(x)])
       y1 <- sort(y[!is.na(y)]);
       length(intersect(x1, y1)) == length(x1)},
       str_extract_all(product.3, "[A-Z]"), 
       str_extract_all(stock, "[A-Z]"))) & !is.na(product.3)]

for(j in names(dt)[5:6]) set(dt, i = which(is.na(dt[[j]])), j = j, value = FALSE)
dt
#    product     stock product.2 product.3 outcome1 outcome2 outcome3
# 1:       A         A         B         C    FALSE    FALSE    FALSE
# 2:       B       A,B         C     A,C,E     TRUE     TRUE    FALSE
# 3:       C     A,B,C     A,C,E       A,B     TRUE     TRUE     TRUE
# 4:   A,C,E   A,B,C,E       A,B     A,B,C     TRUE     TRUE     TRUE
# 5:     A,B   A,B,C,E     A,B,C         D    FALSE    FALSE    FALSE
# 6:   A,B,C   A,B,C,E         D         A    FALSE     TRUE     TRUE
# 7:       D A,B,C,D,E         A         B    FALSE     TRUE     TRUE
# 8:       A         A         B         A    FALSE     TRUE     TRUE
# 9:       B       A,B         A         A     TRUE     TRUE     TRUE
#10:       A       A,B         A     A,B,C     TRUE     TRUE    FALSE
#11:       A         A     A,B,C         D    FALSE    FALSE    FALSE
#12:   A,B,C     A,B,C         D         D     TRUE    FALSE    FALSE
#13:       D   A,B,C,D         D      <NA>    FALSE    FALSE    FALSE
#14:       D   A,B,C,D      <NA>      <NA>    FALSE    FALSE    FALSE

Great! Shouldn't the `x` and `pattern` in `str_detect` be switched? — wake_wake, Aug 20 '19 at 22:18
do you have an idea for the 3 question listed (edited after your original answer) — wake_wake, Aug 21 '19 at 14:57

score 3 · Answer 2 · answered Aug 20 '19 at 17:57

split the product.3 at comma and then use grepl to check if it is present in product.2 or stock

temp = strsplit(dt$product.3, ",")
sapply(seq_along(temp), function(i){
    any(sapply(temp[[i]], function(x) grepl(x, dt$product.2[i])))
})
# [1] FALSE  TRUE  TRUE  TRUE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE
#[12]  TRUE    NA    NA
sapply(seq_along(temp), function(i){
    any(sapply(temp[[i]], function(x) grepl(x, dt$stock[i])))
})
# [1] FALSE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE
#[12] FALSE    NA    NA

Does this approach scale well, considering the call to `grepl` — wake_wake, Aug 21 '19 at 14:56

R - Regex matching on elements in character string

2 Answers2

Update