Split delimited strings in multiple columns and separate them into rows

Question

I have this dataframe to_expand with two columns:

to_expand <- data.frame(first = c('a~b'), second = paste(list(c('1~2~3'), c('4~5~6')), collapse = '|'))

  first       second
1   a~b 1~2~3|4~5~6

How can I turn it to:

# A tibble: 2 x 2
  first second
  <chr> <chr> 
1 a     1~2~3  
2 b     4~5~6

I have tried using sepratate_rows() from tidyr but it gave all the possible combinations between the two columns.

Any help would be much appreciated!

---

Edit: using separate_rows(second, sep = '\\|') gave me a~b on both rows.

> to_expand %>% separate_rows(second, sep = '\\|')
# A tibble: 2 x 2
  first second
  <chr> <chr> 
1 a~b   1~2~3 
2 a~b   4~5~6 

Answer 1

We may do this in an easier way if we make the delimiter same

library(dplyr)
library(tidyr)
library(stringr)
to_expand %>% 
    mutate(first = str_replace(first, "~", "|")) %>% 
    separate_rows(first, second, sep = "\\|")
# A tibble: 2 x 2
  first second
  <chr> <chr> 
1 a     1~2~3 
2 b     4~5~6 

Answer 2

You can use tidyr::separate_rows() on multiple columns simultaneously if these columns are to be separated into the same number of rows.

to_expand %>%
   separate_rows(first, second, sep = "(?<=\\D)~|(?<=\\d)\\|")

# A tibble: 2 x 2
  first second
  <chr> <chr> 
1 a     1~2~3 
2 b     4~5~6 

Answer 3

A perhaps more transparent pattern is this:

to_expand %>%
  separate_rows(first, second, sep = "(?<=[a-z])~|\\|")

Herer we separate_rowseither if there's

• a ~ provided there's a lower-case letter on the left or if there's
• a |

Answer 4

You can pass different separator for different columns.

purrr::map2_df(to_expand, c('~', '|'), ~strsplit(.x, .y, fixed = TRUE)[[1]])

#  first second
#  <chr> <chr> 
#1 a     1~2~3 
#2 b     4~5~6