# r – Pivot longer to multiple values and remove prefix on years

## r – Pivot longer to multiple values and remove prefix on years

Contents

Problem Description:

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

Idaho1620120-40.29421874
Idaho1620130-30.29421874
Idaho1620141-20.29421874
Illinois172012000.11512130
Illinois172013000.11512130
Illinois172014000.11512130
Indiana182012000.27556394
Indiana182013000.27556394
Indiana182014000.27556394

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.

Any help is appreciated.

## Solution – 1

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
``````
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.