How to execute a pivot_wider multiple times in R?-CodePudding

Below is the sample data and the desired result. The task at hand is to pivot multiple times(or at least I think it is multiple times) in order to create a complete historical series per row. At the moment, each qtr (quarter) is a row. The naming convention is not all that important. I know how to do a pivot_wider but the creating of the monthly column names has me stumped.

 area <- c("000000","000000","000000","000000","000003","000003","000003","000003")
 indcode <- c("432100","432100","432100","432100","432100","432100","432100","432100")
 Year <- c("2019","2019","2019","2019","2019","2019","2019","2019")
 qtr <- c("01","02","03","04","01","02","03","04")
 month1_emplvl <-c(100,101,102,103,44,44,46,52)
 month2_emplvl <-c(100,101,103,104,48,44,52,41)
 month3_emplvl <-c(101,100,102,99,44,45,46,47)

 testdata <- data.frame(area,indcode,Year,qtr,month1_emplvl,month2_emplvl,month3_emplvl)

Desired result

  area    indcode    2019-01     2019-02   2019-03    2019-04   2019-05   2019-06 and so on...
  000000    432100      100         100        101      101        101        100
  000003    432100       44          48         44       44        44          45

CodePudding user response：

Is this the solution you're looking for (pass all the months to the values_from argument)?

df <- pivot_wider(testdata, values_from = c(month1_emplvl, month2_emplvl, month3_emplvl), names_from = c(Year, qtr))

CodePudding user response：

I don't know how you want to apply pivoting multiple times, but I think you could use

library(tidyr)
library(dplyr)
library(stringr)

testdata %>% 
  pivot_longer(starts_with("month"), names_pattern = "month(\\d)_*") %>% 
  mutate(
    new_name = 
      paste(Year, 
            str_pad((as.integer(qtr) - 1) * 3   as.integer(name), 
                    width = 2, 
                    side = "left", 
                    pad = "0"),
            sep = "-"),
    .keep = "unused") %>%   
  pivot_wider(names_from = new_name)

This returns

# A tibble: 2 x 14
  area  indcode `2019-01` `2019-02` `2019-03` `2019-04` `2019-05` `2019-06` `2019-07` `2019-08`
  <chr> <chr>       <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
1 0000~ 432100        100       100       101       101       101       100       102       103
2 0000~ 432100         44        48        44        44        44        45        46        52
# ... with 4 more variables: `2019-09` <dbl>, `2019-10` <dbl>, `2019-11` <dbl>,
#   `2019-12` <dbl>