Using pivot_longer to restructure columns into a row that has 2 corresponding value columns?-CodePudding

I'm sure the title could be worded better but basically I have the following data:

  location          group financial_year h0to2 h10plus h2to4 h4to10 total perc0to2 perc2to4 perc4to10 
  <chr>            <chr>      <chr>     <dbl>   <dbl> <dbl>  <dbl> <dbl>    <dbl>    <dbl>     <dbl>      
6 partnership       x         2020/21     0       5     5     15    25        0       20        60

I want to create a new column "level_of_service" which would have the rows "a0to2", "a2to4", "a4to10", "a10 ", with a column "Value" taking the values from the columns which start with h and a column "Percentage" taking the values from the perc columns.

Using the following code with pivot_longer:

mydata2 <- mydata %>%
  pivot_longer(cols = c("h0to2", "h2to4", "h4to10", "h10plus", "perc0to2", "perc2to4", "perc4to10", "perc10plus"),
               names_to = "level_of_service"
               )

I get the following data:

  location       group financial_year total level_of_service value
  <chr>         <chr>        <chr>       <dbl>    <chr>        <dbl>
1 partnership    x          2020/21       0      h0to2           0
2 partnership    x          2020/21       0      perc0to2        0

I want this but with 2 value columns, one for the h0to2 row and the other for the perc0to2 row.

Sorry if this very long winded it's been a while since I've used stackoverflow to ask a question! Any help would be greatly appreciated.

CodePudding user response：

Using the special ".value" and the names_pattern argument you could do:

library(tidyr)

mydata %>%
  pivot_longer(
    cols = matches("^(h|p)"),
    names_to = c(".value", "level_of_service"),
    names_pattern = "^(h|perc)(.*)$"
  )
#> # A tibble: 4 × 7
#>   location    group financial_year total level_of_service     h  perc
#>   <chr>       <chr> <chr>          <int> <chr>            <int> <int>
#> 1 partnership x     2020/21           25 0to2                 0     0
#> 2 partnership x     2020/21           25 10plus               5    NA
#> 3 partnership x     2020/21           25 2to4                 5    20
#> 4 partnership x     2020/21           25 4to10               15    60

DATA

mydata <- read.table(text = "location          group financial_year h0to2 h10plus h2to4 h4to10 total perc0to2 perc2to4 perc4to10
6 partnership       x         2020/21     0       5     5     15    25        0       20        60  ", header = TRUE)