Split a column (each element is a vector) to multiple columns

dplyr
tidyr
purrr

#1

There is a great tidyr::separate() function that allows splitting a character column to multiple columns based on some regex. Is there a similar function in tidyverse when the target column is a list of numbers, not a character?

library(tidyverse)
tribble(
    ~a,
    1:3,
    4:6)
#> # A tibble: 2 x 1
#>   a        
#>   <list>   
#> 1 <int [3]>
#> 2 <int [3]>

I want to split the column "a" into 3 columns


#2

You can collapse the vector down to a single element, and then split it up using separate() as you suggest:

library(tidyverse)
tribble(
    ~a,
    1:3,
    4:6
    ) %>% 
    mutate(
        b = map_chr(a, paste, collapse = ",")
    ) %>% 
    tidyr::separate(
        b, 
        c("d", "e", "f"),
        ","
    )

Might not be the most elegant way of doing it, and I'm not sure how well it will work if you have vectors of uneven length (probably just the usual separate() warnings, but this works for your simple example (assuming I've understood what you're trying to do correctly).


#3

Another way is to unnest list column after some small transformation to keep trace of information and allow spreading to wide format

library(tidyverse)
tribble(
  ~a,
  1:3,
  4:6) %>%
  # use named vector
  mutate(a = map(a, ~ set_names(.x, letters[1:3]))) %>%
  # transfor to 2 columns tibble
  mutate(a = map(a, enframe)) %>%
  # unnest list column
  unnest(.id = "id") %>%
  # mutate(id = row_number()) %>%
  spread(name, value)
#> # A tibble: 2 x 4
#>   id        a     b     c
#>   <chr> <int> <int> <int>
#> 1 1         1     2     3
#> 2 2         4     5     6

Created on 2018-09-10 by the reprex package (v0.2.0).

Not especially better but an interesting other approach to give idea. :wink:


#4

you are right it definitely will work and it is the only way I know. Looks like a solution with a big overhead


#5

In fact, base::split can help you here.

library(tidyverse)
tribble(
  ~a,
  1:3,
  4:6) %>%
  mutate(b = map(a, ~split(., letters[1:3])),
         c = map(b, as_tibble)) %>%
  unnest(c, .drop = FALSE)
#> # A tibble: 2 x 5
#>   a         b             a1    b1     c
#>   <list>    <list>     <int> <int> <int>
#> 1 <int [3]> <list [3]>     1     2     3
#> 2 <int [3]> <list [3]>     4     5     6

Created on 2018-09-10 by the reprex package (v0.2.0).

In column b, you get a list of 3 elements. You can convert to tibble to then unnest and get three columns. Simpler than my previous code...(names are poorly chosen here. sorry)


#6

a little bit shorter

library(tidyverse)
tribble(
    ~a,
    1:3,
    4:6) %>%
    mutate(b = map(a, ~as_tibble(split(., letters[1:3])))) %>%
    unnest(b, .drop = FALSE)
#> # A tibble: 2 x 4
#>   a            a1     b     c
#>   <list>    <int> <int> <int>
#> 1 <int [3]>     1     2     3
#> 2 <int [3]>     4     5     6

Created on 2018-09-10 by the reprex package (v0.2.0).

I'm thinking about it


#7

Indeed but I detailed the intermediate step to add one column for example purpose! :wink: it is obvious that a two steps pipe can be shorten in one. I was just for demo.


#8

I have a feeling that this is pretty close to the final solution but it still need a fix

library(tidyverse)
tribble(
    ~a,
    1:3,
    4:6,
    7:9) %>%
    `[<-`(., , c('d', 'e', 'f'), .$a)
#> # A tibble: 3 x 4
#>   a             d     e     f
#>   <list>    <int> <int> <int>
#> 1 <int [3]>     1     4     7
#> 2 <int [3]>     2     5     8
#> 3 <int [3]>     3     6     9

I need something like ...transpose(.$a)


#9
library(tidyverse)
tribble(
    ~a,
    1:3,
    1:3 * 10) %>%
    `[<-`(, c('d', 'e', 'f'), map(transpose(.$a), unlist))
#> # A tibble: 2 x 4
#>   a             d     e     f
#>   <list>    <dbl> <dbl> <dbl>
#> 1 <int [3]>     1     2     3
#> 2 <dbl [3]>    10    20    30

I don't like that there is no straigtforward solution for this problem. Maybe I should offer this idea to one of the tidyverse packages