1

I have a data frame with two columns: ID and product. The product contains a list of items like the ones below:

ID Product
1 'desk','chair','clock'
2 NA
3 'pen'

I want to extract every single product in a separate row with the corresponding ID, as below:

ID Product
1 'desk'
1 'chair'
1 'clock'
3 'pen'

It would be appreciated if you had any suggestions.

Mohammad
  • 41
  • 7
  • Could you please share your data using `dput`? – Quinten Jan 14 '23 at 11:00
  • @Quinten, I am new in R and don't have any idea about dput! But I have edited the example table in the question to be identical to my real data. – Mohammad Jan 14 '23 at 15:53
  • 1
    Hi @Mohammad, no problem! Your question is clear. This [post](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) could help you a lot. – Quinten Jan 14 '23 at 17:04

3 Answers3

2

You can do it with separate.

library(tidyverse)

df <- data.frame(
  id = c(1,2,3),
  product=c('desk, chair, clock', NA, 'pen')
) 

df |> 
  separate_rows(product) |> 
  drop_na()
#> # A tibble: 4 × 2
#>      id product
#>   <dbl> <chr>  
#> 1     1 desk   
#> 2     1 chair  
#> 3     1 clock  
#> 4     3 pen
MarBlo
  • 4,195
  • 1
  • 13
  • 27
  • Hi MarBlo. very close to what I want. just needs a sep option in the separate_rows(). – Mohammad Jan 14 '23 at 17:21
  • 1
    @`Mohammad` that is not necessary here, as `tidyr` recognizes the separator. If you feel better, you may add `sep=','`. – MarBlo Jan 14 '23 at 17:25
1

You can do it with tidyr lib separate_rows

library(tidyr)

df = df %>%
  separate_rows(Product, sep = ",")
tomerar
  • 805
  • 5
  • 10
0

Beside the above answers, I tried this method and works fine as well.

result_df <-  unnest(df, Product)
Mohammad
  • 41
  • 7