In R, how create new dataframe with number of occurrences of each strings on other dataframe, like a product list and products in basket sold

Question

Dataframe with each basket sold:

sells <- data.frame(buyers = c('buyer001', 'buyer002', 'buyer002'),
                      cart = c('apple > orange > grape > grape', 
                               'orange > orange', 'apple > grape'))

Dataframe with store stock

fruits <- data.frame(fruits = c('apple', 'orange', 'grape'))

Dataframe result

result <- data.frame(buyers = c('apple', 'orange', 'grape'),
                      cart = c(2, 3, 3))

Answer 1

stack(table(unlist(strsplit(sells$cart, "\\W+"))))
  values    ind
1      2  apple
2      3  grape
3      3 orange

Answer 2

We can use the tidyverse (please see that the "fruits" object is not used)

library(dplyr)
library(tidyr)

sells %>%
    separate_rows(cart, sep = "\\s*>\\s*") %>%
    group_by(cart) %>%
    summarise(values = n())

# A tibble: 3 × 2
  cart   values
  <chr>   <int>
1 apple       2
2 grape       3
3 orange      3