In R, how create new dataframe with number of occurrences of each strings on other dataframe, like a-CodePudding

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))

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

Here a data.table version.

library(data.table)
result = data.table(buyers = unlist(strsplit(sells$cart, "\\W ")))
result[, .(cart = .N), by=buyers]

   buyers cart
1:  apple    2
2: orange    3
3:  grape    3