pivot_wider/tidyselect and col: how to exclude variables?

[zoowalk]

I am struggling to exclude columns from the col parameter in pivot_longer.
Is this not possible?

E.g. let's say I want to take all columns which have "wk" in their name, except "wk2".

library(tidyverse)

billboard %>%
pivot_longer(
cols = vars(starts_with("wk"), -wk2),
names_to = "week",
names_prefix = "wk",
values_to = "rank",
values_drop_na = TRUE
)

Error: vars(starts_with("wk"), -wk2) must evaluate to column positions or names, not a list

Many thanks!

[phiggins]

Hi zoowalk,

I think that using matches with a bit of regex will get you where you want to go.
This selects for wk followed by a digit in the set 1,3,4 (the ones between the square brackets).
I am assuming that you wanted to get rid of the wk2 column, so I selected this out at the end. You could remove the select from the end of the pipe to keep it in if you want to.

library(tidyverse)

billboard <- tibble(
  name = c("Alice", "Bob", "Carla"),
  wk1 = c(5,7,6),
  wk2 = c(9,11,13),
  wk3 = c(20,17,39),
  wk4 = c(22,33,40),
)
billboard %>%
  pivot_longer(
    cols = matches("wk[134]"),
    names_to = "week",
    names_prefix = "wk",
    values_to = "rank",
    values_drop_na = TRUE
  ) %>% 
  select(-wk2)
#> # A tibble: 9 x 3
#>   name  week   rank
#>   <chr> <chr> <dbl>
#> 1 Alice 1         5
#> 2 Alice 3        20
#> 3 Alice 4        22
#> 4 Bob   1         7
#> 5 Bob   3        17
#> 6 Bob   4        33
#> 7 Carla 1         6
#> 8 Carla 3        39
#> 9 Carla 4        40

^{Created on 2019-10-01 by the reprex package (v0.3.0)}

[zoowalk]

Excellent. Didn't think of match. Many thanks.