How to sort the result of pivot_wider in R when the variable is a composite string

Question

I want to sort the result of pivot_wider using x or "my_y" as sorting variables.

When using arrange(x) it works, but not when using arrange("my y").

library(tidyverse)
df <- tibble(x = c("a","a","b","b", "a","a","b","b"),
             "my y" = c("c","d","c","d", "c","d","c","d"),
             year = c("2019", "2019", "2019", "2019", "2020", "2020", "2020", "2020"),
             z = c(1, 2, 3, 4, 5, 6, 7, 8))

df %>%
  pivot_wider(names_from = year, values_from = z)
#> # A tibble: 4 x 4
#>   x     `my y` `2019` `2020`
#>   <chr> <chr>   <dbl>  <dbl>
#> 1 a     c           1      5
#> 2 a     d           2      6
#> 3 b     c           3      7
#> 4 b     d           4      8

#same result
df %>%
  pivot_wider(names_from = year, values_from = z, names_sort = TRUE)
#> # A tibble: 4 x 4
#>   x     `my y` `2019` `2020`
#>   <chr> <chr>   <dbl>  <dbl>
#> 1 a     c           1      5
#> 2 a     d           2      6
#> 3 b     c           3      7
#> 4 b     d           4      8

# it works for x
df %>%
  pivot_wider(names_from = year, values_from = z) %>%
  arrange(desc(x))
#> # A tibble: 4 x 4
#>   x     `my y` `2019` `2020`
#>   <chr> <chr>   <dbl>  <dbl>
#> 1 b     c           3      7
#> 2 b     d           4      8
#> 3 a     c           1      5
#> 4 a     d           2      6

# does not work when the variable is a string
df %>%
  pivot_wider(names_from = year, values_from = z) %>%
  arrange("my y")
#> # A tibble: 4 x 4
#>   x     `my y` `2019` `2020`
#>   <chr> <chr>   <dbl>  <dbl>
#> 1 a     c           1      5
#> 2 a     d           2      6
#> 3 b     c           3      7
#> 4 b     d           4      8
Created on 2020-10-17 by the reprex package (v0.3.0)

What do you mean by composite string ? column names with white-spaces? Have you seen this https://stackoverflow.com/questions/22842232/dplyr-select-column-names-containing-white-space ? — Ronak Shah, Oct 17 '20 at 11:52
Yes. String with spaces. Thank you. I understand now my unfortunate error. — sbac, Oct 17 '20 at 11:55

Karthik S · Accepted Answer · 2020-10-17T11:56:44.983

Works:

> df %>%
+   pivot_wider(names_from = year, values_from = z) %>%
+   arrange(`my y`)
# A tibble: 4 x 4
  x     `my y` `2019` `2020`
  <chr> <chr>   <dbl>  <dbl>
1 a     c           1      5
2 b     c           3      7
3 a     d           2      6
4 b     d           4      8
>

You commented the column name, that's why it didn't work:

For ex:

> df %>%
+   pivot_wider(names_from = year, values_from = z) %>%
+   arrange(desc("x"))
# A tibble: 4 x 4
  x     `my y` `2019` `2020`
  <chr> <chr>   <dbl>  <dbl>
1 a     c           1      5
2 a     d           2      6
3 b     c           3      7
4 b     d           4      8

After removing the comment:

> df %>%
+   pivot_wider(names_from = year, values_from = z) %>%
+   arrange(desc(x))
# A tibble: 4 x 4
  x     `my y` `2019` `2020`
  <chr> <chr>   <dbl>  <dbl>
1 b     c           3      7
2 b     d           4      8
3 a     c           1      5
4 a     d           2      6
>

In tidy evaluation, data is masked so that we can directly use the variable name without quoting them.

How to sort the result of pivot_wider in R when the variable is a composite string

1 Answers1