Regex search across two tables

I have the following df with a name column and class column:

df=data.frame(name=c("name1","name2","name3","name4"), class=c("classA","classA","classB","classC"))

and I have the following dataframe where it contains a combinations of names:

df2=data.frame(names=c("name1;name3","name5,name6","name2 name 8","name4"))

I need to determine the classification of the names in df2 by performing a regex search, and labeling which class they belong to (classes) and also create a flag (class_flag):

df2=data.frame(names=c("name1;name3","name5,name6","name2 name 8","name4"),class_flag=c("Y","N","Y","Y"),classes=c("classA,classB", NA, "classA","classC"))

I have a cumbersome process to do this now which I will show but I was thinking there is a better way to do it, and I would have to do it for every class, I'm just showing it for one of the classes below:

class_A_search=paste(paste0("\\b",toupper(df$name),collapse = "|"))

df2%>%mutate(class_flag=ifelse(str_detect(toupper(names),class_A_search),"Y","N"), class_A=ifelse(str_detect(toupper(names),class_A_search),"classA",NA))

This is a simplified version of the dataset, and df2 has 1 million rows and the names column isn't limited to the ones shown here.

Probably a base R option with regmatches could help

transform(
    transform(
        df2,
        classes = sapply(
            regmatches(names, gregexpr(paste0(df$name, collapse = "|"), names)),
            \(x) {
                with(
                    df,
                    replace(
                        d <- paste0(unique(class[match(x, name)]), collapse = ","),
                        !nzchar(d),
                        NA
                    )
                )
            }
        )
    ),
    class_flag = c("Y", "N")[1 + is.na(classes)]
)

which gives

         names       classes class_flag
1  name1;name3 classA,classB          Y
2  name5,name6          <NA>          N
3 name2 name 8        classA          Y
4        name4        classC          Y

Using dplyr, we can try

df2 %>%
    mutate(name = regmatches(names, gregexpr("name\\d", names))) %>%
    unnest(name) %>%
    left_join(df) %>%
    summarise(
        classes = str_c(unique(class), collapse = ";"),
        class_flag = c("Y", "N")[1 + (sum(is.na(class)) == n())],
        .by = names
    )

which gives

# A tibble: 4 × 3
  names        classes       class_flag
  <chr>        <chr>         <chr>
1 name1;name3  classA;classB Y
2 name5,name6  NA            N
3 name2 name 8 classA        Y
4 name4        classC        Y

Here is a tidyverse option:

library(tidyverse)

lookup <- deframe(df)
df2 |>
  mutate(class = str_extract_all(names, str_c("\\b", df$name, "\\b", collapse = "|")),
         class = map(class, ~ set_names(unname(lookup[.x]))),
         class_flag = ifelse(lengths(class), "Y", "N"),
         unnest_class = class) |>
  unnest_wider(unnest_class)

How it works

1. str_extract_all returns a list-column with each list element corresponding to a row in the data frame. Each list element is a vector of extracted regular expression matches.
2. Since class is a list-column we use map to iterate over it and look up the extracted value in the named vector created by deframe(df). set_names is used to create a named vector in each list element for un-nesting wider in step #4 (these become the column names).
3. class is still a list-column. I left it that way because there is a lot of functionality in R in dealing with lists rather than collapsing it to a string.
4. Create class_flag by using lengths, which returns 0 (equivalent of FALSE) if the list element is empty (e.g., character(0)).
5. Make a copy of the list-column class named unnest_class, which we unnest into columns.

If you really need class as a character column, you can pipe this output to mutate(class = map_chr(class, str_flatten_comma)).

Output

  names        class     class_flag classA classB classC
  <chr>        <list>    <chr>      <chr>  <chr>  <chr> 
1 name1;name3  <chr [2]> Y          classA classB NA    
2 name5,name6  <chr [0]> N          NA     NA     NA    
3 name2 name 8 <chr [1]> Y          classA NA     NA    
4 name4        <chr [1]> Y          NA     NA     classC

Benchmark

Increasing the number of rows in df2 to 100,000 to get a better sense of performance.

If you do not need the columns classA, classB, etc. and remove the unnest_wider pipe, this answer is more comparable to the one posted by @ThomasIsCoding. In the case without unnest_wider I found this answer to be faster, but I kept it in the benchmark because it looks like you want those columns:

set.seed(1)
df2 <- df2[sample(1:nrow(df2), 1E5, replace = T),, drop = F]


(bench <- microbenchmark::microbenchmark(
  stringr = {lookup <- deframe(df)
  df2 |>
    mutate(class = str_extract_all(names, str_c("\\b", df$name, "\\b", collapse = "|")),
           class = map(class, ~ set_names(unname(lookup[.x]))),
           class_flag = ifelse(lengths(class), "Y", "N"),
           unnest_class = class) |>
    unnest_wider(unnest_class)},
  baseR = {transform(
    transform(
      df2,
      classes = sapply(
        regmatches(names, gregexpr(paste0(df$name, collapse = "|"), names)),
        \(x) {
          with(
            df,
            replace(
              d <- paste0(unique(class[match(x, name)]), collapse = ","),
              !nzchar(d),
              NA
            )
          )
        }
      )
    ),
    class_flag = c("Y", "N")[1 + is.na(classes)]
  )},
  times = 20L,
  unit = "seconds"
))

Unit: seconds
    expr      min       lq     mean   median       uq      max neval cld
 stringr 3.759750 4.249305 4.461382 4.527822 4.732876 4.953810    20  a 
   baseR 2.736081 2.835327 3.019493 3.044236 3.137328 3.427364    20   b

ggplot2::autoplot(bench)

Other ways include:

library(tidyverse)

pat <- str_c(df$name, collapse = "|")

df2 %>%
  mutate(classes = map_chr(str_extract_all(names, pat), toString) %>%
           str_replace_all(names, deframe(df))%>%
           na_if(""),
          class_flag = c("Y", "N")[1+is.na(classes)])
#>          names      classes class_flag
#> 1  name1;name3 name1, name3          Y
#> 2  name5,name6         <NA>          N
#> 3 name2 name 8        name2          Y
#> 4        name4       classC          Y


df2 %>%
  mutate(classes =na_if(str_remove_all(names, sprintf("\\b((?!%s)\\W?)+", pat)), ''),
         class_flag = c("Y", "N")[1+is.na(classes)])
#>          names     classes class_flag
#> 1  name1;name3 name1;name3          Y
#> 2  name5,name6        <NA>          N
#> 3 name2 name 8       name2          Y
#> 4        name4       name4          Y

^{Created on 2023-06-28 with reprex v2.0.2}