FR: order of columns resulting from pivot_wider

[mattantaliss]

In case I'm not just missing an obvious pre-existing solution, it would be nice to be able to specify how resulting columns from pivot_wider will be ordered. Using the us_rent_income example,

us_rent_income %>%
  pivot_wider(names_from = variable, values_from = c(estimate, moe))

produces the columns, estimate_income, estimate_rent, moe_income, and moe_rent. In my use-case (getting to a double-header table), I want them to be in the order, estimate_income, moe_income, estimate_rent, and moe_rent.

[EarlGlynn]

DItto. Is the "fix" to go back to spread that always worked "correctly"? There are several closed issues but I didn't see solutions.

[EarlGlynn]

More info. Hadley's comment in another thread says to sort the data to control the column order. But this doesn't make sense for large datasets in some cases. I have data from July 2002 till Jan 2004 sorted by year and month. Why would I want to sort by month first than year to get the factor order right -- this file will eventually have over a million records.

[EarlGlynn]

The order of the output display should be independent of the order of the input data. In processing dates that start in July (example above), that should not mean the first month of the year is July for the output display. I want to code "pivot_wider" in new code, but spread works much better for now.

[huftis]

I think this issue is really two separate issues, 1) one for specifying the order in which multiply variables are combined, as per @mattantaliss original comment, and 2) one for how values within a variable are ordered, as in #850 (which was closed as a duplicate of this issue) and @EarlGlynn’s comments.

Here are my comments and regexp for the second part. The columns (within a variable) should be ordered the same as in spread(). For example, for factors, the columns should be ordered
by the ordering in the factor levels. And for numeric variables, they should be ordered by numbers. Here is a regexp, for both factors, numeric and character levels:

library(tidyr)
library(dplyr)
d = tibble(day_int = c(4,3,5,1,2),
           day_fac = factor(day_int, levels=1:5,
                            labels=c("Mon","Tue", "Wed","Thu","Fri")),
           day_char = as.character(day_fac))
d
#> # A tibble: 5 x 3
#>   day_int day_fac day_char
#>     <dbl> <fct>   <chr>   
#> 1       4 Thu     Thu     
#> 2       3 Wed     Wed     
#> 3       5 Fri     Fri     
#> 4       1 Mon     Mon     
#> 5       2 Tue     Tue
levels(d$day_fac)
#> [1] "Mon" "Tue" "Wed" "Thu" "Fri"

# spread() respects the ordering of the factor levels ...
d %>% 
  select(day_fac, day_int) %>% 
  spread(key = "day_fac", value = "day_int")
#> # A tibble: 1 x 5
#>     Mon   Tue   Wed   Thu   Fri
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     1     2     3     4     5

# ... but pivot_wider() does not
d %>% 
  select(day_fac, day_int) %>% 
  pivot_wider(names_from = day_fac, values_from = day_int)
#> # A tibble: 1 x 5
#>     Thu   Wed   Fri   Mon   Tue
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     4     3     5     1     2


# spread() respects the ordering of numeric variables ...
d %>% 
  select(day_int) %>% 
  spread(key = "day_int", value = "day_int")
#> # A tibble: 1 x 5
#>     `1`   `2`   `3`   `4`   `5`
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     1     2     3     4     5

# ... but pivot_wider() does not
d %>% 
  select(day_int) %>% 
  pivot_wider(names_from = day_int, values_from = day_int)
#> # A tibble: 1 x 5
#>     `4`   `3`   `5`   `1`   `2`
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     4     3     5     1     2


# spread() respects the (alphabetic) ordering of character variables ...
d %>% 
  select(day_char, day_int) %>% 
  spread(key = "day_char", value = "day_int")
#> # A tibble: 1 x 5
#>     Fri   Mon   Thu   Tue   Wed
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     5     1     4     2     3

# ... but pivot_wider() does not
d %>% 
  select(day_char, day_int) %>% 
  pivot_wider(names_from = day_char, values_from = day_int)
#> # A tibble: 1 x 5
#>     Thu   Wed   Fri   Mon   Tue
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     4     3     5     1     2

Basically, the request (for the second sub-issue) is that pivot_wider() should use the same ordering as order() does:

order(d$day_fac)
#> [1] 4 5 2 1 3
order(d$day_int)
#> [1] 4 5 2 1 3
order(d$day_char)
#> [1] 3 4 1 5 2

[hadley]

@huftis they're currently ordered by their first appearance in the data frame. Unfortunately, I don't think ordering to match order() is a good default because it the ordering of character vectors depends on the current environment.

[hadley]

For the ordering case, I think names_sort = TRUE makes sense.

I don't know what to call the argument for the other case which is about whether names_from or values_from comes first.

[hadley]

Random idea: maybe names_from could recognise the special .values variable:

us_rent_income %>%
  pivot_wider(names_from = c(variable, .value), values_from = c(estimate, moe))

us_rent_income %>%
  pivot_wider(names_from = c(.value, variable), values_from = c(estimate, moe))

[hadley]

No, I don't think I can make that work without a lot more thinking. So then the only choice you get is whether values comes first or last. That suggests an argument name involving loc or pos, maybe values_names_loc = "first"? Or is it names_values_pos = "last"?

[hadley]

Does it seem right to change the order of the column names when you change the position of the variable name?

library(tidyr)

pivot_wider(
  us_rent_income,
  names_from = variable, 
  values_from = c(estimate, moe),
  names_value_loc = "first"
)
#> # A tibble: 52 x 6
#>    GEOID NAME                 estimate_income estimate_rent moe_income moe_rent
#>    <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

pivot_wider(
  us_rent_income,
  names_from = variable, 
  values_from = c(estimate, moe),
  names_value_loc = "last"
)
#> # 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 2020-04-27 by the reprex package (v0.3.0)}

[hadley]

I don't think I wanted to add any more than two arguments for these features as the tradeoff increased complexity of pivot_wider() outweights the limited gain in futures. (Especially since you can also generate the pivot spec yourself to exercise greater control).