为每个ID补全2023年月度数据并添加活跃状态标识（R语言）

需求说明 #

现有按id和month排序的数据集，包含数百万个id和数百个var类列，month列取值为2023年的任意月份。需要实现：

• 为每个id补全2023年全年12个月份的记录
• 新增active列：若原数据中有该id对应月份的记录则标记为1，无则标记为0
• 最终数据集总行数为唯一id数量 × 12

示例数据 #

输入数据（dput格式） #

input = structure(list(id = c(1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 3L, 3L), month = c(202301L, 202304L, 202305L, 
202301L, 202302L, 202303L, 202304L, 202305L, 202306L, 202307L, 
202308L, 202309L, 202310L, 202311L, 202312L, 202307L, 202308L
), var1 = c(1L, 2L, 3L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 
14L, 15L, 16L, 17L, 18L), var2 = 1:17), class = "data.frame", row.names = c(NA, 
-17L))

输入表格样例 #

+----+--------+------+------+
| id | month  | var1 | var2 |
+----+--------+------+------+
|  1 | 202301 |    1 |    1 |
|  1 | 202304 |    2 |    2 |
|  1 | 202305 |    3 |    3 |
|  2 | 202301 |    5 |    4 |
|  2 | 202302 |    6 |    5 |
|  2 | 202303 |    7 |    6 |
|  2 | 202304 |    8 |    7 |
|  2 | 202305 |    9 |    8 |
|  2 | 202306 |   10 |    9 |
|  2 | 202307 |   11 |   10 |
|  2 | 202308 |   12 |   11 |
|  2 | 202309 |   13 |   12 |
|  2 | 202310 |   14 |   13 |
|  2 | 202311 |   15 |   14 |
|  2 | 202312 |   16 |   15 |
|  3 | 202307 |   17 |   16 |
|  3 | 202308 |   18 |   17 |
+----+--------+------+------+

期望输出数据（dput格式） #

output = structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), month = c(202301L, 202302L, 
202303L, 202304L, 202305L, 202306L, 202307L, 202308L, 202309L, 
202310L, 202311L, 202312L, 202301L, 202302L, 202303L, 202304L, 
202305L, 202306L, 202307L, 202308L, 202309L, 202310L, 202311L, 
202312L, 202301L, 202302L, 202303L, 202304L, 202305L, 202306L, 
202307L, 202308L, 202309L, 202310L, 202311L, 202312L), var1 = c(1L, 
NA, NA, 2L, 3L, NA, NA, NA, NA, NA, NA, NA, 5L, 6L, 7L, 8L, 9L, 
10L, 11L, 12L, 13L, 14L, 15L, 16L, NA, NA, NA, NA, NA, NA, 17L, 
18L, NA, NA, NA, NA), var2 = c(1L, NA, NA, 2L, 3L, NA, NA, NA, 
NA, NA, NA, NA, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 
15L, NA, NA, NA, NA, NA, NA, 16L, 17L, NA, NA, NA, NA), active = c(1L, 
0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 
0L, 0L, 0L)), class = "data.frame", row.names = c(NA, -36L))

期望输出表格样例 #

+----+--------+------+------+--------+
| id | month  | var1 | var2 | active |
+----+--------+------+------+--------+
|  1 | 202301 |    1 |    1 |      1 |
|  1 | 202302 |      |      |      0 |
|  1 | 202303 |      |      |      0 |
|  1 | 202304 |    2 |    2 |      1 |
|  1 | 202305 |    3 |    3 |      1 |
|  1 | 202306 |      |      |      0 |
|  1 | 202307 |      |      |      0 |
|  1 | 202308 |      |      |      0 |
|  1 | 202309 |      |      |      0 |
|  1 | 202310 |      |      |      0 |
|  1 | 202311 |      |      |      0 |
|  1 | 202312 |      |      |      0 |
|  2 | 202301 |    5 |    4 |      1 |
|  2 | 202302 |    6 |    5 |      1 |
|  2 | 202303 |    7 |    6 |      1 |
|  2 | 202304 |    8 |    7 |      1 |
|  2 | 202305 |    9 |    8 |      1 |
|  2 | 202306 |   10 |    9 |      1 |
|  2 | 202307 |   11 |   10 |      1 |
|  2 | 202308 |   12 |   11 |      1 |
|  2 | 202309 |   13 |   12 |      1 |
|  2 | 202310 |   14 |   13 |      1 |
|  2 | 202311 |   15 |   14 |      1 |
|  2 | 202312 |   16 |   15 |      1 |
|  3 | 202301 |      |      |      0 |
|  3 | 202302 |      |      |      0 |
|  3 | 202303 |      |      |      0 |
|  3 | 202304 |      |      |      0 |
|  3 | 202305 |      |      |      0 |
|  3 | 202306 |      |      |      0 |
|  3 | 202307 |   17 |   16 |      1 |
|  3 | 202308 |   18 |   17 |      1 |
|  3 | 202309 |      |      |      0 |
|  3 | 202310 |      |      |      0 |
|  3 | 202311 |      |      |      0 |
|  3 | 202312 |      |      |      0 |
+----+--------+------+------+--------+

解决方案 #

1. Tidyverse方案 #

适合熟悉tidyverse语法的场景，代码可读性强：

library(tidyverse)

# 生成所有id和2023年12个月份的笛卡尔积
full_grid = expand_grid(
  id = unique(input$id),
  month = 202301:202312
)

# 左连接原数据，补全缺失月份，生成active列
result_tidy = full_grid %>%
  left_join(input, by = c("id", "month")) %>%
  mutate(active = as.integer(!is.na(var1)))  # 用任意var列判断是否有记录均可

2. Data.table方案 #

适合百万级以上大数据场景，运行效率更高：

library(data.table)

# 转换为data.table格式
setDT(input)

# 生成所有id和月份的组合
full_grid = CJ(id = unique(input$id), month = 202301:202312)

# 左连接并生成active列
result_dt = full_grid[input, on = .(id, month)] %>%
  .[, active := as.integer(!is.na(var1))]

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