R语言data.table分组枚举新增N列并补全缺失行方法

直接用data.table原生语法即可实现，无需加载其他依赖包，步骤如下：

• 第一步：按instance和date双字段分组，给现有行生成从1开始的组内序号列N
• 第二步：统计所有分组的行数，取最大值作为所有分组统一的编号上限
• 第三步：用CJ生成所有分组+1到上限序号的完整组合，和原表做连接，自动补全缺失行，非分组、序号字段自动填充NA

完整可运行代码：

library(data.table)

# 读入示例数据
dt <- data.table(instance = c("A","A","A","B","B","B", "C","C","C","C","C","A","A",
                              "B","B","B", "C","C","C","C","C"), 
                 date = c("2019-02-25","2019-02-25","2019-02-25","2019-02-25","2019-02-25",
                          "2019-02-25", "2019-02-25","2019-02-25","2019-02-25","2019-02-25",
                          "2019-02-25","2019-03-01","2019-03-01","2019-03-01","2019-03-01",
                          "2019-03-01", "2019-03-01","2019-03-01","2019-03-01","2019-03-01","2019-03-01"), 
                 y = c("0,1","0,2","0,2","0,1","0,1","0,15","0,1","0,2","0,3","0,1","0,1",
                       "0,1","0,1","0,1","0,25","0,3","0,1","0,1","0,15","0,1","0,2"))

# 给现有记录加组内序号
dt[, N := seq_len(.N), by = .(instance, date)]

# 计算全局分组最大行数
max_row_count <- max(dt[, .(group_n = .N), by = .(instance, date)]$group_n)

# 补全所有分组到最大行数
final_dt <- dt[
  CJ(instance = unique(instance), date = unique(date), N = seq_len(max_row_count)),
  on = .(instance, date, N)
]

代码运行后返回的final_dt完全匹配需求：

• 每个instance+date分组内N从1到max_row_count（本示例为5）连续编号
• 原表记录不足的行，y等非分组字段自动填充NA
• 原本就有5条记录的C组两个日期的数据完全保留，无多余填充

本示例运行后，2019-02-25的A组、B组各补2行NA，2019-03-01的A组补3行NA、B组补2行NA，最终全表共6个分组*5行=30行记录。

内容的提问来源于stack exchange，提问作者Ксения