Home Split collapsed value in dataset into separate rows based on specific separate sign

Questions

Split collapsed value in dataset into separate rows based on specific separate sign

February 28, 2023

I have dataset like this:

structure(list(variable = c("avenir census and treatment data clinepi round 2::form.childpresent | avenir census and treatment data clinepi round 1::form.childpresent | avenir census and treatment data clinepi round 3::form.childpresent", 
"avenir census and treatment data clinepi round 1::form.childrenrepeat.childrengroup.dobsourceotherdoc | avenir census and treatment data clinepi round 3::dobsourceotherdoc | avenir census and treatment data clinepi round 2::dobsourceotherdoc", 
"avenir census and treatment data clinepi round 2::notreatreasonother | avenir census and treatment data clinepi round 3::notreatreasonother | avenir census and treatment data clinepi round 1::form.childrenrepeat.childrengroup.notreatreasonother", 
"avenir census and treatment data clinepi round 3::dobsourcewho | avenir census and treatment data clinepi round 2::dobsourcewho | avenir census and treatment data clinepi round 1::form.childrenrepeat.childrengroup.dobsourcewho"
), IRI = c("http://purl.obolibrary.org/obo/EUPATH_0050599", "http://purl.obolibrary.org/obo/EUPATH_0050607", 
"http://purl.obolibrary.org/obo/EUPATH_0050582", "http://purl.obolibrary.org/obo/EUPATH_0049212"
)), row.names = c(NA, 4L), class = "data.frame")

I tried the code like this:

oldcon2<-separate_rows(oldcon,variable, sep=" | ")

which oldcon is the original file I duplicated above and oldcon2 is the new one, ideally has all splitted value.

So how can I separate column "variable" based on collapsed sign "|" and put each variable in its own row? Thanks a lot~~!

>Solution :

We could add this to your code:

The\\s*\\|\\s* regex matches zero or more spaces before and after the | separator:

library(tidyr)
library(dplyr)

oldcon2 %>% 
  separate_rows(variable, sep = "\\s*\\|\\s*")

 variable                                                                                               IRI             
   <chr>                                                                                                  <chr>           
 1 avenir census and treatment data clinepi round 2::form.childpresent                                    http://purl.obo…
 2 avenir census and treatment data clinepi round 1::form.childpresent                                    http://purl.obo…
 3 avenir census and treatment data clinepi round 3::form.childpresent                                    http://purl.obo…
 4 avenir census and treatment data clinepi round 1::form.childrenrepeat.childrengroup.dobsourceotherdoc  http://purl.obo…
 5 avenir census and treatment data clinepi round 3::dobsourceotherdoc                                    http://purl.obo…
 6 avenir census and treatment data clinepi round 2::dobsourceotherdoc                                    http://purl.obo…
 7 avenir census and treatment data clinepi round 2::notreatreasonother                                   http://purl.obo…
 8 avenir census and treatment data clinepi round 3::notreatreasonother                                   http://purl.obo…
 9 avenir census and treatment data clinepi round 1::form.childrenrepeat.childrengroup.notreatreasonother http://purl.obo…
10 avenir census and treatment data clinepi round 3::dobsourcewho                                         http://purl.obo…
11 avenir census and treatment data clinepi round 2::dobsourcewho                                         http://purl.obo…
12 avenir census and treatment data clinepi round 1::form.childrenrepeat.childrengroup.dobsourcewho       http://purl.obo…