Home Is there a way to select a final column in the cols = argument in pivot_longer() without naming it or giving index number?

Questions

Is there a way to select a final column in the cols = argument in pivot_longer() without naming it or giving index number?

byMR

January 8, 2024

I’m writing a function to pivot a data table to long format. For the cols = argument, the name of the first column will always be the same, but the final column (and number of columns) will change. Is there a way to grab the "rest" of the columns without naming them or by their indexes?

Say I have this reprex:

data <- structure(list(Site = c("A", "B"), Group = c("1", "2"), grip = c("S", 
"H"), height = c("S", "T"), width = c("W", "N"), QA = c("Y", 
"N")), class = "data.frame", row.names = c(NA, -2L))

That looks like this:

  Site Group grip height width QA
1    A     1    S      S     W  Y
2    B     2    H      T     N  N

And sometimes the dataset will have more columns. Say I want to get columns grip:QA, without naming QA or giving its index number. I tried the following:

data %>%
  pivot_longer(cols = grip:everything(),
               names_to = "Name",
               values_to = "value")

But I get the warning Warning message: In x:y : numerical expression has 6 elements: only the first used, and it doesn’t pivot the way I want it to. Is what I’m trying to achieve possible?

>Solution :

You could use last_col:

library(tidyr)

data %>%
  pivot_longer(
    cols = grip:last_col(),
    names_to = "Name",
    values_to = "value"
  )
#> # A tibble: 8 × 4
#>   Site  Group Name   value
#>   <chr> <chr> <chr>  <chr>
#> 1 A     1     grip   S    
#> 2 A     1     height S    
#> 3 A     1     width  W    
#> 4 A     1     QA     Y    
#> 5 B     2     grip   H    
#> 6 B     2     height T    
#> 7 B     2     width  N    
#> 8 B     2     QA     N

Or as another option exclude the columns you don’t want to include when pivoting using ! or -:

data %>%
  pivot_longer(
    cols = !c(Site, Group),
    names_to = "Name",
    values_to = "value"
  )
#> # A tibble: 8 × 4
#>   Site  Group Name   value
#>   <chr> <chr> <chr>  <chr>
#> 1 A     1     grip   S    
#> 2 A     1     height S    
#> 3 A     1     width  W    
#> 4 A     1     QA     Y    
#> 5 B     2     grip   H    
#> 6 B     2     height T    
#> 7 B     2     width  N    
#> 8 B     2     QA     N