有年度和月度数据的r轴更长

Question

当我有年度和每月的数据时，我不知道如何构建我的支点更长的命令。例如，我有：

wide <- data.frame(region_name = character(),    # Create empty data frame
                    total_population_2019 = numeric(),
                    total_population_2020 = numeric(),
                   mean_temperature_2019_1 = numeric(),
                   mean_temperature_2019_2 = numeric(),
                   mean_temperature_2020_1 = numeric(),
                   mean_temperature_2020_2 = numeric(),
                    stringsAsFactors = FALSE)

wide[1, ] <- list("funville", 50000, 51250, 26.3, 24.6, 25.7, 24.9)

region_name total_population_2019 total_population_2020 mean_temperature_2019_1 mean_temperature_2019_2 mean_temperature_2020_1 mean_temperature_2020_2
 funville                50000             51250              26.3              24.6              25.7              24.9

我能够在每月的专栏上使用spread：

long <- pivot_longer(wide, cols = 4:7, names_to = c("layer" ,"year", "month"),
                     names_pattern = "(.*)_(.*)_?_(.*)") %>%
  group_by(layer) %>%
  mutate(n = 1:n()) %>%
  spread(layer, value) %>%
  select(-n)

这给

  region_name total_population_2019 total_population_2020 year  month mean_temperature
1 funville                    50000                 51250 2019  1                 26.3
2 funville                    50000                 51250 2019  2                 24.6
3 funville                    50000                 51250 2020  1                 25.7
4 funville                    50000                 51250 2020  2                 24.9

现在我希望有一个人口列，在该列中，值被赋值在该年的每一行/每月，理想情况如下：

desired.df <- data.frame(region_name = c("funville", "funville", "funville", "funville"),
                         year = c("2019", "2019", "2020", "2020"),
                         month = c("1", "2", "1", "2"),
                         population = c("50000", "50000", "51250", "51250"),
                         mean_temperature = c("26.3", "24.6", "25.7", "24.9"))

这给

  region_name year  month population mean_temperature           
1 funville    2019  1     50000      26.3            
2 funville    2019  2     50000      24.6            
3 funville    2020  1     51250      25.7            
4 funville    2020  2     51250      24.9 

有人有解决办法吗？提前感谢

Answer 1

一种选择是使用names_pattern参数和特殊的.value。为了完成这项工作，我首先在人口栏中添加一个助手月。此外，我还使用tidyr::fill填充人口栏：

library(dplyr)
library(tidyr)

wide |> 
  rename_with(~ paste(.x, 1, sep = "_"), starts_with("total")) |> 
  pivot_longer(-region_name, 
               names_to = c(".value", "year", "month"),
               names_pattern = "^(.*?)_(\\d+)_(\\d+)$") |> 
  group_by(year) |> 
  fill(total_population) |> 
  arrange(year)
#> # A tibble: 4 × 5
#> # Groups:   year [2]
#>   region_name year  month total_population mean_temperature
#>   <chr>       <chr> <chr>            <dbl>            <dbl>
#> 1 funville    2019  1                50000             26.3
#> 2 funville    2019  2                50000             24.6
#> 3 funville    2020  1                51250             25.7
#> 4 funville    2020  2                51250             24.9