Separate rows and get complete data set

user_8272788 0
Viewed 81

I have a data set in which some variables contain multiple entries. I would like to separate these into different rows. I realize there are a host of solutions to this on stackoverflow, using separate_rows, however this does not give me a full set of possible combinations.

Here is a working example:

#create the data
x <- data.frame('id' = 1:4,
           'color' = c('red', 'blue\ngreen', 'yellow\nred\norange', 'pink'), 
           'action' = c('plan\nexecute\nfile', 'run\nfind', 'find', 'hope\nfeel\npause'))

> x
  id               color              action
1  1                 red plan\nexecute\nfile
2  2         blue\ngreen           run\nfind
3  3 yellow\nred\norange                find
4  4                pink   hope\nfeel\npause

If I use separate rows I get:

x %>%
  separate_rows(c(color, action), sep = '\\n')

# A tibble: 11 x 3
      id color  action 
   <int> <chr>  <chr>  
 1     1 red    plan   
 2     1 red    execute
 3     1 red    file   
 4     2 blue   run    
 5     2 green  find   
 6     3 yellow find   
 7     3 red    find   
 8     3 orange find   
 9     4 pink   hope   
10     4 pink   feel   
11     4 pink   pause

This separates the rows however when multiple pairs occur the rows are created simultaneously. E.g. blue:run; green:find should be blue:run; blue:find; green:run; green:find

I can achieve this using strsplit and unnest as follows:

x %>%
  mutate_at(.vars = c('color', 'action'), 
            ~strsplit(., '\\n')) %>%
  unnest(color) %>%
  unnest(action)

# A tibble: 13 x 3
      id color  action 
   <int> <chr>  <chr>  
 1     1 red    plan   
 2     1 red    execute
 3     1 red    file   
 4     2 blue   run    
 5     2 blue   find   
 6     2 green  run    
 7     2 green  find   
 8     3 yellow find   
 9     3 red    find   
10     3 orange find   
11     4 pink   hope   
12     4 pink   feel   
13     4 pink   pause

What I can't seem to do is find a way to call unnest across all the variables that get nested via the mutate_at(strsplit) call (I have too many to list each one as I do above). If I try pass the variables as a vector to unnest:

x %>%
  mutate_at(.vars = c('color', 'action'), 
            ~strsplit(., '\\n')) %>% 
  unnest(c(color, action))

I get the same as separate_rows.

If I create a function to unnest and use map, I end up with lists of dataframes unnested on each column:

#create function
unnester <- function(df, x) {
  unnest(df, cols = c(x))
}
 
#generate nested df 
y <- x %>%
  mutate_at(.vars = c('color', 'action'), 
            ~strsplit(., '\\n')) 

#map function
map(.x = c('color', 'action'), .f = unnester, df = y)

Gives:

[[1]]
# A tibble: 7 x 3
     id color  action   
  <int> <chr>  <list>   
1     1 red    <chr [3]>
2     2 blue   <chr [2]>
3     2 green  <chr [2]>
4     3 yellow <chr [1]>
5     3 red    <chr [1]>
6     3 orange <chr [1]>
7     4 pink   <chr [3]>

[[2]]
# A tibble: 9 x 3
     id color     action 
  <int> <list>    <chr>  
1     1 <chr [1]> plan   
2     1 <chr [1]> execute
3     1 <chr [1]> file   
4     2 <chr [2]> run    
5     2 <chr [2]> find   
6     3 <chr [3]> find   
7     4 <chr [1]> hope   
8     4 <chr [1]> feel   
9     4 <chr [1]> pause

Any idea how I can either use unnest across all the relevant columns - without calling it over and over again. Or if there is a better way to do this?

dplyr r
2 Answers

In base R, we could do expand.grid after the strsplit

lst1 <- lapply(x[-1], function(u) strsplit(u, "\n")) 
out <- do.call(rbind, Map(cbind, id = x$id, 
       Map(expand.grid, lst1[[1]], lst1[[2]])))
names(out) <- names(x)

-output

> out
   id  color  action
1   1    red    plan
2   1    red execute
3   1    red    file
4   2   blue     run
5   2  green     run
6   2   blue    find
7   2  green    find
8   3 yellow    find
9   3    red    find
10  3 orange    find
11  4   pink    hope
12  4   pink    feel
13  4   pink   pause

Or using tidyverse

library(dplyr)
library(purrr)
library(tidyr)
x %>%
    mutate(across(c(color, action), strsplit, '\n')) %>% 
    transmute(id, out = map2(color, action, expand.grid)) %>% 
    unnest(c(out)) %>% 
    set_names(names(x))

-output

# A tibble: 13 x 3
      id color  action 
   <int> <fct>  <fct>  
 1     1 red    plan   
 2     1 red    execute
 3     1 red    file   
 4     2 blue   run    
 5     2 green  run    
 6     2 blue   find   
 7     2 green  find   
 8     3 yellow find   
 9     3 red    find   
10     3 orange find   
11     4 pink   hope   
12     4 pink   feel   
13     4 pink   pause

If we are okay with using a loop, this can be done as

for(nm in names(x)[-1]) x <- separate_rows(x, all_of(nm), sep="\n")

-output

> x
# A tibble: 13 x 3
      id color  action 
   <int> <chr>  <chr>  
 1     1 red    plan   
 2     1 red    execute
 3     1 red    file   
 4     2 blue   run    
 5     2 blue   find   
 6     2 green  run    
 7     2 green  find   
 8     3 yellow find   
 9     3 red    find   
10     3 orange find   
11     4 pink   hope   
12     4 pink   feel   
13     4 pink   pause
user_37322710

Use separate_rows twice:

x %>%
  separate_rows(color, sep = '\\n') %>%
  separate_rows(action, sep = '\\n')
user_168642540
Related