在R中按年度和周为家庭-个体组合补全缺失周记录并填充0

步骤说明 #

• 先动态生成全局完整的周序列（基于数据集的起止周）
• 按household和individual分组，补全每个组合的所有周记录，缺失的duration填充为0

完整代码 #

# 加载所需包
library(dplyr)
library(tidyr)

# 示例数据集
data <- data.frame(household=c(1001,1001,1001,1001,1001,1002,1002,1002,1003,1003,1003),
                   individual = c(1,1,1,1,1,2,2,2,1,1,1),
                   year = c(2021,2021,2022,2022,2022,2021,2022,2022,2022,2022,2022),
                   week =c("w51","w52","w1","w2","w4","w51","w1","w3","w1","w2","w3"),
                   duration =c(20,23,24,56,78,12,34,67,87,89,90))

# 1. 生成全局完整的year-week序列（动态适配数据集起止周）
full_weeks <- data %>%
  # 将year和week转换为可排序的数字标识（如2021w51转为202151）
  mutate(week_num = year * 100 + as.numeric(substr(week, 2, nchar(week)))) %>%
  # 获取数据集的起止周标识
  summarize(min_week = min(week_num), max_week = max(week_num)) %>%
  # 生成连续的周标识序列
  expand(week_num = seq(min_week, max_week)) %>%
  # 拆分回year和原格式的week
  mutate(
    year = floor(week_num / 100),
    week = paste0("w", week_num %% 100)
  ) %>%
  select(year, week)

# 2. 按家庭-个体分组补全记录，填充缺失的duration为0
data_complete <- data %>%
  group_by(household, individual) %>%
  # 补全所有全局周序列的记录，缺失duration填0
  complete(nesting(year, week) = full_weeks, fill = list(duration = 0)) %>%
  ungroup()

# 查看结果
print(data_complete)

代码解释 #

• 生成完整周序列：通过week_num将年份和周数合并为可排序的数字，确保跨年的周序列能正确连续生成（比如2021w52之后直接接2022w1），再拆分回原有的year和week格式。
• 分组补全：使用group_by(household, individual)确保每个组合独立补全，nesting(year, week)保证年份和周数的对应关系正确，避免生成无效的组合（比如2021w1），fill = list(duration = 0)直接将缺失的时长填充为0。

内容的提问来源于stack exchange，提问作者joy_1379