How can I control the order of pivoted (wider) columns in R? tidyverse issue 839

user_2884859 0
Viewed 1539

I can pivot the data in wider format if the values to be pivoted consist in more than one column.

us_rent_income %>%
  pivot_wider(
    names_from = variable,
    names_glue = "{variable}_{.value}",
    values_from = c(estimate, moe)
  )

# A tibble: 52 x 6
   GEOID NAME                 income_estimate rent_estimate income_moe rent_moe
   <chr> <chr>                          <dbl>         <dbl>      <dbl>    <dbl>
 1 01    Alabama                        24476           747        136        3
 2 02    Alaska                         32940          1200        508       13
 3 04    Arizona                        27517           972        148        4
 4 05    Arkansas                       23789           709        165        5
 5 06    California                     29454          1358        109        3
 6 08    Colorado                       32401          1125        109        5
 7 09    Connecticut                    35326          1123        195        5
 8 10    Delaware                       31560          1076        247       10
 9 11    District of Columbia           43198          1424        681       17
10 12    Florida                        25952          1077         70        3
# ... with 42 more rows

In this code output, I want the order of columns to be income_estimate, income_moe, rent_estimate and rent_moe. Setting names_sort = T isn't helping. Changing the order in names_glue doesn't help either. I know I can reorder columns by select and through other functions, but I just want to know that is there any argument in pivot_wider to do so?

EDIT the issue seems already in development; it has been discussed here and here at least.

tidyverse pivot r
3 Answers

For fine-grained control, you can use pivot_wider_spec(), which lets you define the specification for the resulting data frame:

library(tidyverse)

spec <- tibble(
  .name = c("income_estimate", "income_moe", "rent_estimate", "rent_moe"),
  .value = c("estimate", "moe", "estimate", "moe"),
  variable = c("income", "income", "rent", "rent")
)

us_rent_income %>% pivot_wider_spec(spec)

Output:

# A tibble: 52 x 6
   GEOID NAME                 income_estimate income_moe rent_estimate rent_moe
   <chr> <chr>                          <dbl>      <dbl>         <dbl>    <dbl>
 1 01    Alabama                        24476        136           747        3
 2 02    Alaska                         32940        508          1200       13
 3 04    Arizona                        27517        148           972        4
 4 05    Arkansas                       23789        165           709        5
 5 06    California                     29454        109          1358        3
 6 08    Colorado                       32401        109          1125        5
 7 09    Connecticut                    35326        195          1123        5
 8 10    Delaware                       31560        247          1076       10
 9 11    District of Columbia           43198        681          1424       17
10 12    Florida                        25952         70          1077        3
# … with 42 more rows

And with a few pre-processing steps, you can avoid having to manually enter all the values in spec:

field <- us_rent_income %>% distinct(variable) %>% pull()
sub_field <- colnames(us_rent_income)[4:5]

pivot_names <- map(field, ~paste(., sub_field, sep = "_")) %>% unlist()
pivot_vals <- rep(sub_field, 2)
pivot_vars <- map(field, rep, 2) %>% unlist()

spec <- tibble(.name = pivot_names, .value = pivot_vals, variable = pivot_vars)

us_rent_income %>% pivot_wider_spec(spec)
user_27999410

With the advent of tidyr 1.2.0, it is now super easy with the use of argument names_vary

library(tidyr)
us_rent_income %>%
  pivot_wider(
    names_from = variable,
    names_glue = "{variable}_{.value}",
    values_from = c(estimate, moe),
    names_vary = 'slowest'
  )
#> # A tibble: 52 x 6
#>    GEOID NAME                 income_estimate income_moe rent_estimate rent_moe
#>    <chr> <chr>                          <dbl>      <dbl>         <dbl>    <dbl>
#>  1 01    Alabama                        24476        136           747        3
#>  2 02    Alaska                         32940        508          1200       13
#>  3 04    Arizona                        27517        148           972        4
#>  4 05    Arkansas                       23789        165           709        5
#>  5 06    California                     29454        109          1358        3
#>  6 08    Colorado                       32401        109          1125        5
#>  7 09    Connecticut                    35326        195          1123        5
#>  8 10    Delaware                       31560        247          1076       10
#>  9 11    District of Columbia           43198        681          1424       17
#> 10 12    Florida                        25952         70          1077        3
#> # ... with 42 more rows

Created on 2022-02-17 by the reprex package (v2.0.1)

user_28848590

After the pivoting, we could do a select by ordering the substring of column names

library(dplyr)
library(tidyr)
library(stringr0
us_rent_income %>%
  pivot_wider(
    names_from = variable,
    names_glue = "{variable}_{.value}",
    values_from = c(estimate, moe)
 ) %>%
  select(GEOID, NAME, order(str_remove(names(.)[-(1:2)], "_.*")) + 2)

-output

# A tibble: 52 x 6
#   GEOID NAME                 income_estimate income_moe rent_estimate rent_moe
#   <chr> <chr>                          <dbl>      <dbl>         <dbl>    <dbl>
# 1 01    Alabama                        24476        136           747        3
# 2 02    Alaska                         32940        508          1200       13
# 3 04    Arizona                        27517        148           972        4
# 4 05    Arkansas                       23789        165           709        5
# 5 06    California                     29454        109          1358        3
# 6 08    Colorado                       32401        109          1125        5
# 7 09    Connecticut                    35326        195          1123        5
# 8 10    Delaware                       31560        247          1076       10
# 9 11    District of Columbia           43198        681          1424       17
#10 12    Florida                        25952         70          1077        3
# … with 42 more rows

The ordering is based on the names_from column and so the names_sort have no impact on the column names from values_from i.e. in the OP's solution, it wouldn't change if we change the order in names_glue. In the data, the 'variable' column unique value appearance is in income, followed by rent. So, it does that order, when the default names_sort = FALSE. If it is changed to TRUE, it does alphabetic order, which is again i followed by r.

It can be checked if we first reshape to 'long', unite the columns and then do the pivot_wider

us_rent_income %>%
  pivot_longer(cols = c(estimate, moe)) %>% 
  unite(variable, variable, name) %>% 
  pivot_wider(names_from = variable, values_from = value)

-output

# A tibble: 52 x 6
#   GEOID NAME                 income_estimate income_moe rent_estimate rent_moe
#   <chr> <chr>                          <dbl>      <dbl>         <dbl>    <dbl>
# 1 01    Alabama                        24476        136           747        3
# 2 02    Alaska                         32940        508          1200       13
# 3 04    Arizona                        27517        148           972        4
# 4 05    Arkansas                       23789        165           709        5
# 5 06    California                     29454        109          1358        3
# 6 08    Colorado                       32401        109          1125        5
# 7 09    Connecticut                    35326        195          1123        5
# 8 10    Delaware                       31560        247          1076       10
# 9 11    District of Columbia           43198        681          1424       17
#10 12    Florida                        25952         70          1077        3
# … with 42 more rows

Now, we check by changing into custom order with factor and specify names_sort = TRUE, it will go in the order we wanted

us_rent_income %>%
   pivot_longer(cols = c(estimate, moe)) %>% 
   unite(variable, variable, name) %>%
   mutate(variable = factor(variable, 
    levels = c('income_estimate', 'rent_moe', 'rent_estimate', 'income_moe'))) %>%
    pivot_wider(names_from = variable, values_from = value, names_sort = TRUE)
# A tibble: 52 x 6
#   GEOID NAME                 income_estimate rent_moe rent_estimate income_moe
#   <chr> <chr>                          <dbl>    <dbl>         <dbl>      <dbl>
# 1 01    Alabama                        24476        3           747        136
# 2 02    Alaska                         32940       13          1200        508
# 3 04    Arizona                        27517        4           972        148
# 4 05    Arkansas                       23789        5           709        165
# 5 06    California                     29454        3          1358        109
# 6 08    Colorado                       32401        5          1125        109
# 7 09    Connecticut                    35326        5          1123        195
# 8 10    Delaware                       31560       10          1076        247
# 9 11    District of Columbia           43198       17          1424        681
#10 12    Florida                        25952        3          1077         70
# … with 42 more rows
user_37322710
Related