number of matches from another dataframe

Question

help me, please ) I have 2 dataframes, and I want add to df1 additional column with number of matches in df2 for pattern in "pep" column. help me

 df1 <-data.frame("id"=c(1, 2, 3), pep = c("bb", "dr", "ac"))
df2 <- data.frame("name" = c("a", "b", "c", "d", "e", "f"), "word" = c("drab", "drabbed", "drabbler", "dracaena", "drachma", "academia"))

in result I looked for df1

   id  pep     n_matches
1  1  bb         2
2  2  dr         5
3  3  ac         3

Thanks

Answer 1

My dplyr solution may look a bit ugly compared to normal dplyr operations:

df1 %>% mutate(n_matches = purr::map(pep, function(x) sum(grepl(x, df2$word))))

or

df1 %>% mutate(n_matches = purr::map(pep, function(x) length(grep(x, df2$word))))

Both returns:

  id pep n_matches
1  1  bb         2
2  2  dr         5
3  3  ac         3

Explain:

length(grep("bb", df2$word)) returns the number of matches for bb in df2$word. However in dplyr, the input to replace bb becomes the column vector, so we need the row wise map function map to let R know that we apply the function to every row/value in the column vector rather than the column vector directly.

Answer 2

Base R solution:

transform(
  df1, 
  n_matches = as.integer(
    ave(
      df1$pep, 
      df1$pep,
      FUN = function(x){
        length(
          grep(
            x, 
            df2$word
          )
        )
      }
    )
  )
)