如何用R的data.table为缺失y值插入行，其余列填充NA（适配大数据）

需求说明 #

当数据中y列的编号出现中断时，需要插入对应y值的新行，并将其他列（如x）填充为NA。使用tidyr::complete()可实现该需求，但处理数百万行数据时性能不足，因此需要更高效的data.table解决方案。

初始数据 #

x <- c("a","a","a","a","a","a","a","a","a","b","b","b","b","b","b","b")
y <- c(1,1,2,4,4,4,7,7,11,1,3,3,3,6,6,8)
dat0 <- data.frame(x,y)

打印初始数据：

> dat0
   x  y
1  a  1
2  a  1
3  a  2
4  a  4
5  a  4
6  a  4
7  a  7
8  a  7
9  a 11
10 b  1
11 b  3
12 b  3
13 b  3
14 b  6
15 b  6
16 b  8  

期望输出 #

> dat1
      x  y
1     a  1
2     a  1
3     a  2
4  <NA>  3
5     a  4
6     a  4
7     a  4
8  <NA>  5
9  <NA>  6
10    a  7
11    a  7
12 <NA>  8
13 <NA>  9
14 <NA> 10
15    a 11
16    b  1
17 <NA>  2
18    b  3
19    b  3
20    b  3
21 <NA>  4
22 <NA>  5
23    b  6
24    b  6
25 <NA>  7
26    b  8  

data.table解决方案 #

利用data.table的高效分组、连接和行扩展能力，实现需求的步骤如下：

1. 加载data.table包并转换数据格式

library(data.table)
setDT(dat0)

2. 按x分组，生成每个组内y的完整连续序列

full_y_seq <- dat0[, .(y = seq(min(y), max(y), 1)), by = x]

3. 统计原数据中每个(x,y)组合的出现次数

y_counts <- dat0[, .N, by = .(x, y)]

4. 左连接完整序列与计数表，补全缺失项并处理值

full_counts <- full_y_seq[y_counts, on = .(x, y)]
# 对无匹配的行，将x设为NA，计数设为1（需插入1行）
full_counts[, `:=`(
  x = fifelse(is.na(N), NA_character_, x),
  N = fifelse(is.na(N), 1L, N)
)]

5. 根据计数值扩展行，得到最终结果

dat1 <- full_counts[rep(seq(.N), N)][, N := NULL]

运行后dat1即为符合期望的输出，该方案在处理大规模数据时性能显著优于tidyr::complete()。

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