I have a wide table:

states <- tibble(
  state = c("Idaho","Illinois","Indiana"),
  statefip = 16:18,
  da2012 = 0,
  da2013 = 0,
  da2014 = c(1, 0, 0),
  daes2012 = c(-4, 0, 0),
  daes2013 = c(-3, 0, 0),
  daes2014 = c(-2, 0, 0),
  rural_pct = c(0.29421874, 0.11512130, 0.27556394)
)

And I want to get a long table with a column for year, a column for da, and a column for daes. So something that looks like

I've tried a few things that return errors or not what I'm looking for, the latest of which is:

states_long <- states %>%
  pivot_longer(
    cols = starts_with("da"),
    names_to = "year",
    values_to = c("da","daes")
  )

I'm kind of just throwing things at the wall at this point and I'm stuck. I think I need to use some sort of tidy select function to isolate the prefix from the year but I'm not sure how.

Am I not thinking about this the right way?

Any help is appreciated.

CodePudding user response：

For this kind of operation pivot_longer offers a special ".value" to be used with names_to and using the names_pattern argument you can assign the pattern to split the column names in the value(s) and the name part:

library(tidyr)

states %>%
  pivot_longer(
    starts_with("da"),
    names_to = c(".value", "name"),
    names_pattern = "^(.*?)(\\d )$"
  )
#> # A tibble: 9 × 6
#>   state    statefip rural_pct name     da  daes
#>   <chr>       <int>     <dbl> <chr> <dbl> <dbl>
#> 1 Idaho          16     0.294 2012      0    -4
#> 2 Idaho          16     0.294 2013      0    -3
#> 3 Idaho          16     0.294 2014      1    -2
#> 4 Illinois       17     0.115 2012      0     0
#> 5 Illinois       17     0.115 2013      0     0
#> 6 Illinois       17     0.115 2014      0     0
#> 7 Indiana        18     0.276 2012      0     0
#> 8 Indiana        18     0.276 2013      0     0
#> 9 Indiana        18     0.276 2014      0     0