Below is the sample data and the desired result. Yes, I know that there are four columns not listed in the desired out. It is just to keep it simple. Figuring if I can get the first four created then the next four are not that bad. Any ideas on how to accomplish this? My first attempts have been to use pivot_wider but struggling to get the column names that have year and month to create.
state <- c(32,32,32,32,32,32,32,32)
indcode <-c(44,44,44,44,45,45,45,45)
area <-c("000000","000000","000000","000000","000000","000000","000000","000000")
areatype <-c("01","01","01","01","01","01","01","01")
ownership <-c("00","00","00","00","00","00","00","00")
periodyear <-c(2018,2019,2020,2021,2018,2019,2020,2021)
January <- c(44,90,45,91,46,92,48,96)
February <- c(44,91,46,91,48,92,49,99)
example <- data.frame(state,indcode,area,areatype,ownership,periodyear,January,February)
state indcode area areatype ownership 2018m1 2018m2 2019m1 2019 m2
32 44 000000 01 00 44 44 90 91
32 45 000000 01 00 46 48 92 92
>Solution :
library(tidyverse)
example %>%
pivot_longer(January:February, names_to = "month") %>%
mutate(mo_num = match(month, month.name)) %>%
mutate(col_name = paste(periodyear, mo_num, sep = "m")) %>%
select(-periodyear, -month, -mo_num) %>%
pivot_wider(names_from = col_name, values_from = value)
Result
# A tibble: 2 x 13
state indcode area areatype ownership `2018m1` `2018m2` `2019m1` `2019m2` `2020m1` `2020m2` `2021m1` `2021m2`
<dbl> <dbl> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 32 44 000000 01 00 44 44 90 91 45 46 91 91
2 32 45 000000 01 00 46 48 92 92 48 49 96 99