"Vertically str_split" in a column of a data-frame

tidyverse
1

Hi all! So here's my df:

             Formal_Name                     Options
1              sex_lookup             "Male";"Female"
2 beneficiary_type_lookup "Farmer";"Other";"Producer"

I would like to "vertically spread" the second column (Options) so that it inserts the rows that it needs in order to spread correctly. In other words, I'd like to see this output:

                        x          y
1              sex_lookup     "Male"
2              sex_lookup   "Female"
3 beneficiary_type_lookup   "Farmer"
4 beneficiary_type_lookup    "Other"
5 beneficiary_type_lookup "Producer"

The solution I have come up with is a bit clumsy... is there a sexier way?

library(tidyverse) 


LookupsDF <- structure(list(Formal_Name = c("sex_lookup", "beneficiary_type_lookup"
), Options = c("\"Male\";\"Female\"", "\"Farmer\";\"Other\";\"Producer\""
)), class = "data.frame", row.names = c(NA, -2L))

Spreader <- function(x,y) {
  a <- data.frame(x = x, y = str_split(string = y, pattern = ";"), stringsAsFactors = FALSE)
  names(a) <- c("x","y")
  a
}

map2_df(.x = LookupsDF$Formal_Name, .y = LookupsDF$Options,.f = Spreader)
#>                         x          y
#> 1              sex_lookup     "Male"
#> 2              sex_lookup   "Female"
#> 3 beneficiary_type_lookup   "Farmer"
#> 4 beneficiary_type_lookup    "Other"
#> 5 beneficiary_type_lookup "Producer"

I almost feel like this is a common enough use-case for stringr to merit its own function, no?

1 Like
2

There is such a function indeed. It is called tidyr::separate_rows

3 Likes
3

Of course there is! Awesome... I knew there had to be something! Thanks dude!

2 Likes
4

Hi,

You could try with:

library(tidyverse)

df <- tribble(
  ~ 'Formal_Name', ~ 'Options',
  'sex_lookup',  '"Male";"Female"',
  'beneficiary_type_lookup','"Farmer";"Other";"Producer"'
)

df %>% separate_rows(Options, sep=";") -> final

Edit: The same idea as @mishabalyasin

4 Likes