在R中将列中的周分组为列中的月份

Question

我想知道是否有可能将几周到几个月内进行简单整合的数据分组。数据框架看起来如下：

structure(list(c("PCE", "PCE", "PCE", "PCE", "PCE", "PCE"), c("3", 
"0", "0", "0", "0", "0"), c("2", "0", "0", "0", "0", "0"), c("4", 
"0", "0", "0", "0", "0"), c("5", "0", "0", "0", "0", "0"), c("3", 
"0", "2", "0", "0", "0"), c("3", "0", "0", "0", "0", "0"), c("2", 
"0", "0", "0", "0", "0"), c("8", "0", "0", "0", "0", "0"), c("9", 
"0", "0", "0", "0", "0"), c("6", "0", "0", "0", "0", "0"), c("5", 
"0", "2", "0", "0", "0"), c("6", "0", "0", "0", "0", "0"), c("9", 
"4", "1", "0", "0", "0"), c("4", "0", "0", "0", "0", "0"), c("3", 
"1", "0", "0", "0", "0"), c("2", "0", "0", "0", "1", "0"), c("3", 
"0", "0", "0", "1", "0"), c("3", "0", "0", "0", "2", "0"), c("2", 
"0", "0", "0", "1", "0"), c("2", "0", "0", "0", "1", "0"), c("0", 
"0", "0", "0", "2", "1"), c("0", "0", "0", "1", "1", "0"), c("0", 
"0", "0", "1", "2", "0"), c("0", "0", "0", "1", "2", "0"), c("2", 
"0", "0", "3", "1", "0"), c("3", "0", "0", "2", "1", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "0", "0"), c("3", 
"0", "0", "0", "1", "0"), c("3", "0", "0", "0", "1", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "0", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "1", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "0", "0"), c("3", 
"0", "0", "0", "0", "0"), c("1", "0", "0", "0", "2", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "1", "0"), c("3", 
"0", "0", "0", "0", "0"), c("1", "0", "0", "0", "0", "0"), c("3", 
"0", "0", "0", "1", "0"), c("3", "0", "0", "0", "1", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "0", "0"), c("2", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "0", "0"), c("3", 
"0", "0", "0", "0", "0"), c("3", "0", "0", "0", "1", "0"), c("156", 
"5", "5", "8", "24", "1"), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 
0, 0), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 
0, 0), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 
0, 0), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 0, 0), c(0, 0, 0, 0, 
0, 0)), names = c("Calendar year / week", "2022-09-12", "2022-09-19", 
"2022-09-26", "2022-10-03", "2022-10-10", "2022-10-17", "2022-10-24", 
"2022-10-31", "2022-11-07", "2022-11-14", "2022-11-21", "2022-11-28", 
"2022-12-05", "2022-12-12", "2022-12-19", "2022-12-26", "2023-01-02", 
"2023-01-09", "2023-01-16", "2023-01-23", "2023-01-30", "2023-02-06", 
"2023-02-13", "2023-02-20", "2023-02-27", "2023-03-06", "2023-03-13", 
"2023-03-20", "2023-03-27", "2023-04-03", "2023-04-10", "2023-04-17", 
"2023-04-24", "2023-05-01", "2023-05-08", "2023-05-15", "2023-05-22", 
"2023-05-29", "2023-06-05", "2023-06-12", "2023-06-19", "2023-06-26", 
"2023-07-03", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

上面提到的dataframe如下所示：

​

​

例外的输出如下：

​

​

我想知道如何将每周的数据汇总成每月的数据，以及数据下的值之和。

Answer 1

我们可以删除名称为NA (complete.cases)的列，然后用pivot_longer将'week‘名称重新定义为long，使用floor_date (从lubridate)将’week‘名称转换为month，然后在'value’上做一个sum组，然后用pivot_wider将‘wide’重新定义为wide。

library(dplyr)
library(tidyr)
library(lubridate)
# remove the columns with NA as column names
df[complete.cases(names(df))] %>% 
  # create a row sequence identifier
  mutate(rn = row_number()) %>% 
  # reshape to long format by selecting columns other than the 1st and rn
  pivot_longer(cols = -c(1, rn), names_to = 'week') %>%
  # grouped by rn, 1st column and week dates floored to month
  group_by(rn, `Calendar year / week`,  
    month = floor_date(ymd(week), 'month')) %>%
   # get the sum of value
   summarise(value = sum(as.numeric(value), na.rm = TRUE), .groups = 'drop') %>%
  # reshape to wide format
  pivot_wider(names_from = month, values_from = value) %>%
  select(-rn)

-output

# A tibble: 6 × 12
  `Calendar year /…` `2022-09-01` `2022-10-01` `2022-11-01` `2022-12-01` `2023-01-01` `2023-02-01` `2023-03-01` `2023-04-01` `2023-05-01`
  <chr>                     <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
1 PCE                           9           21           26           18           10            2           12           12           13
2 PCE                           0            0            0            5            0            0            0            0            0
3 PCE                           0            2            2            1            0            0            0            0            0
4 PCE                           0            0            0            0            0            6            2            0            0
5 PCE                           0            0            0            1            7            6            2            1            3
6 PCE                           0            0            0            0            1            0            0            0            0
# … with 2 more variables: `2023-06-01` <dbl>, `2023-07-01` <dbl>