CODEWITHSUNDEEP
rdataframedplyrtidyr
Joanna
Joanna

Reputation: 673

tidyr::pivot_wider() reorder column names grouping by `name_from`

I would like to reorder the columns grouping by names_from instead of values_from, here is my minimal example:

mtcars %>% 
tidyr::pivot_wider(names_from = gear, values_from = c(vs, am, carb))

output:

    mpg   cyl  disp    hp  drat    wt  qsec  vs_4  vs_3  vs_5  am_4  am_3  am_5 carb_4 carb_3 carb_5
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>  <dbl>  <dbl>
1  21       6   160   110  3.9   2.62  16.5     0    NA    NA     1    NA    NA      4     NA     NA
2  21       6   160   110  3.9   2.88  17.0     0    NA    NA     1    NA    NA      4     NA     NA
3  22.8     4   108    93  3.85  2.32  18.6     1    NA    NA     1    NA    NA      1     NA     NA

Here is what I want the output:

   mpg   cyl  disp    hp  drat    wt  qsec  vs_4  am_4 carb_4  vs_3  am_3 carb_3  vs_5  am_5 carb_5
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl>
1  21       6   160   110  3.9   2.62  16.5     0     1      4    NA    NA     NA    NA    NA     NA
2  21       6   160   110  3.9   2.88  17.0     0     1      4    NA    NA     NA    NA    NA     NA

Thanks in advance!

Upvotes: 3

Views: 1762

Answers (1)

Giovanni Colitti
Giovanni Colitti

Reputation: 2334

As far as I know, this can't be accomplished with pivot_wider and must be done afterwards.

Here is a long-winded attempt, but it does the job:

library(tidyverse)
suffixes <- unique(mtcars$gear)

pivoted <- mtcars %>%
  tidyr::pivot_wider(names_from = gear, values_from = c(vs, am, carb))

names_to_order <- map(suffixes, ~ names(pivoted)[grep(paste0("_", .x), names(pivoted))]) %>% unlist
names_id <- setdiff(names(pivoted), names_to_order)

pivoted %>%
  select(names_id, names_to_order)
#> # A tibble: 32 x 16
#>      mpg   cyl  disp    hp  drat    wt  qsec  vs_4  am_4 carb_4  vs_3  am_3
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl> <dbl> <dbl>
#>  1  21       6  160    110  3.9   2.62  16.5     0     1      4    NA    NA
#>  2  21       6  160    110  3.9   2.88  17.0     0     1      4    NA    NA
#>  3  22.8     4  108     93  3.85  2.32  18.6     1     1      1    NA    NA
#>  4  21.4     6  258    110  3.08  3.22  19.4    NA    NA     NA     1     0
#>  5  18.7     8  360    175  3.15  3.44  17.0    NA    NA     NA     0     0
#>  6  18.1     6  225    105  2.76  3.46  20.2    NA    NA     NA     1     0
#>  7  14.3     8  360    245  3.21  3.57  15.8    NA    NA     NA     0     0
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0      2    NA    NA
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0      2    NA    NA
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0      4    NA    NA
#> # ... with 22 more rows, and 4 more variables: carb_3 <dbl>, vs_5 <dbl>,
#> #   am_5 <dbl>, carb_5 <dbl>

Created on 2020-02-25 by the reprex package (v0.3.0)

Upvotes: 4

Related Questions