R - Split column values into new multiple columns

Question

I have a dataframe in the form:

    id          products
   1001     milk, cheese, sugar
   1002     milk
   1003     cheese, eggs

I would like to change it to a new data frame in the form :

id     product1    product2     product3
1001    milk        cheese        sugar
1002    milk        
1003    cheese       egg    

How can I accomplish this in R?

Answer 1

We can use separate

library(dplyr)
library(tidyr)
df1 %>%
   separate(products, into = paste0('product', 1:3),
        sep=",\\s+", extra = "merge")  
#   id product1 product2 product3
#1 1001     milk   cheese    sugar
#2 1002     milk     <NA>     <NA>
#3 1003   cheese     eggs     <NA>

---

Or cSplit which would automatically detect the number of elements without having to specify the columns

library(splitstackshape)
cSplit(df1, 'products', ', ')   

data

df1 <- structure(list(id = 1001:1003, products = c("milk, cheese, sugar", 
"milk", "cheese, eggs")), class = "data.frame", row.names = c(NA, 
-3L))

Answer 2

Let's say we have non definite number of products in our dataframe (More than three). We can use separate_rows() from tidyverse without issues about the number of products and then reshape to wide. Here the code with data you shared (I added more items to see this approach). Here the code:

library(tidyverse)
#Data
df <- structure(list(id = c(1001, 1002, 1003), products = c("milk, cheese, sugar, rice, soap, water", 
"milk", "cheese, eggs")), row.names = c(NA, -3L), class = "data.frame")

The code:

#Code
df %>% separate_rows(products,sep = ', ') %>%
  group_by(id) %>%
  mutate(Var=paste0('V',1:n())) %>%
  pivot_wider(names_from = Var,values_from=products) %>%
  replace(is.na(.),'')

Output:

# A tibble: 3 x 7
# Groups:   id [3]
     id V1     V2       V3      V4     V5     V6     
  <dbl> <chr>  <chr>    <chr>   <chr>  <chr>  <chr>  
1  1001 milk   "cheese" "sugar" "rice" "soap" "water"
2  1002 milk   ""       ""      ""     ""     ""     
3  1003 cheese "eggs"   ""      ""     ""     ""