How to split and combine matching list columns and pivot longer in R

Question

I have data that looks like this:

library(tidyverse)
data <- tibble(id = 1:3,
       events = c("E1/E2/E3", "E3/E2", "E1"),
       times = c("10/20/30", "35/20", "15"))

For each id there is a column that contains a list of events separated by /, and a list of times for those events also separated by /. The events and times match by position. I want to convert this data into a tidy format where I have a column with the event name and time, with id repeating for each separate event:time combo.

My desired format looks like this:

desired <- tibble(id = c(1,1,1,2,2,3),
       event = c("E1", "E2", "E3", "E3", "E2", "E1"),
       time = c(10, 20, 30, 35, 20, 15))

I thought about this conceptually using pivot long, but I am unsure how to do this as I am almost pivoting within column values. Also tried splitting the columns into multiple, but I run into the problem that there are different orders and amounts of event per id. Thanks for the help!

Edit: There was a similar questions asked on concept; however, this question already identifies a function to do this in the title, and may be difficult to find: tidyr use separate_rows over multiple columns

I don't understand your edit: you're saying that your question's title identifies a function, but it's not the correct function. I don't see a way you would do this with `pivot_longer`, but it's what `separate_rows` is designed to do — camille, Mar 31 '23 at 14:58
@camille I think I worded that wrong, my bad. My point was that to find the duplicate question, you already have to know the function that solves the problem. My question is worded in a more general way, so I believe it would still be useful to keep up. — Tyler, Mar 31 '23 at 15:08

score 2 · Accepted Answer · answered Mar 30 '23 at 15:54

You could use separate_rows from tidyr with sep = "/" on the two columns you want to separate like this:

library(tidyr)
data |>
  separate_rows(c(events, times), sep = "/")
#> # A tibble: 6 × 3
#>      id events times
#>   <int> <chr>  <chr>
#> 1     1 E1     10   
#> 2     1 E2     20   
#> 3     1 E3     30   
#> 4     2 E3     35   
#> 5     2 E2     20   
#> 6     3 E1     15

^{Created on 2023-03-30 with reprex v2.0.2}

How to split and combine matching list columns and pivot longer in R

1 Answers1