R语言高效过滤大数据集：删除自身及前后行数值列和为0的行

问题描述 #

现有业务数据集共500万+行，无时间缺口，需要过滤掉满足自身、上一行、下一行所有数值列总和等于0的行。

补充说明 #

• 实际数据中Time为dttm类型的时间列
• 连续全零行可能超过3行，该场景下所有符合条件的行均需被过滤

示例数据集结构 #

# A tibble: 13 x 4
   group  Time  Val1  Val2
   <chr> <int> <dbl> <dbl>
 1 A         1   0     0  
 2 B         1   0.1   0  
 3 A         3   0     0  
 4 B         3   0     0  
 5 A         2   0     0  
 6 B         2   0.2   0.2
 7 B         4   0     0  
 8 A         4   0     0.1
 9 A         5   0     0  
10 A         6   0     0  
11 B         6   0.1   0.5
12 B         5   0.1   0.2
13 A         7   0     0  

期望输出 #

（sum列值为0的行需被过滤）

# A tibble: 13 x 8
   group  Time  Val1  Val2 rowsum leadsum lagsum   sum
   <chr> <int> <dbl> <dbl>  <dbl>   <dbl>  <dbl> <dbl>
 1 A         1   0     0      0       0     NA    NA  
 2 A         2   0     0      0       0      0     0     本行将被过滤
 3 A         3   0     0      0       0.1    0     0.1
 4 A         4   0     0.1    0.1     0      0     0.1
 5 A         5   0     0      0       0      0.1   0.1
 6 A         6   0     0      0       0      0     0     本行将被过滤
 7 A         7   0     0      0      NA      0    NA  
 8 B         1   0.1   0      0.1     0.4   NA    NA  
 9 B         2   0.2   0.2    0.4     0      0.1   0.5
10 B         3   0     0      0       0      0.4   0.4
11 B         4   0     0      0       0.3    0     0.3
12 B         5   0.1   0.2    0.3     0.6    0     0.9
13 B         6   0.1   0.5    0.6    NA      0.3  NA  

现有实现问题 #

目前尝试用dplyr::lag()和dplyr::lead()实现逻辑，但处理大数据量时效率极低，还会抛出内存分配错误：

Error in Sys.getenv("TESTTHAT") : 
 could not allocate memory (0 Mb) in C function 'R_AllocStringBuffer'

现有代码逻辑正确但性能不足：

df0 %>% 
  arrange(group, Time) %>% 
  group_by(group) %>% 
  mutate(sum = Val1 + Val2 + lag(Val1) + lag(Val2) + lead(Val1) + lead(Val2))

运行结果：

# >  # A tibble: 13 x 5
# >  # Groups:   group [2]
# >  group  Time  Val1  Val2   sum
# >  <chr> <int> <dbl> <dbl> <dbl>
# >  1  A   1     0     0      NA  
# !  -  A   2     0     0      0  
# >  2  A   3     0     0      0.1
# >  3  A   4   0     0.1    0.1
# >  4  A   5     0     0      0.1
# !  -  A   6     0     0      0  
# >  5  A   7     0     0      NA  
# >  6  B   1     0.1   0      NA  
# >  7  B   2     0.2   0.2    0.5
# >  8  B   3     0     0      0.4
# >  9  B   4     0     0      0.3
# >  10 B   5     0.1   0.2    0.9
# >  11 B   6     0.1   0.5    NA  

示例数据集构造代码 #

df0 <- structure(list(group = c("A", "B", "A", "B", "A", "B", 
                                "B", "A", "A", "A", "B", "B", "A"),
                      Time = c(1L, 1L, 3L, 3L, 2L, 2L, 4L, 4L, 5L, 6L, 6L, 5L, 7L), 
                      Val1 = c(0, 0.1, 0, 0, 0, 0.2, 0, 0, 0, 0, 0.1, 0.1, 0), 
                      Val2 = c(0, 0, 0, 0, 0, 0.2, 0, 0.1, 0, 0, 0.5, 0.2, 0)), 
                 row.names = c(NA, -13L), 
                 class = c("tbl_df", "tbl", "data.frame"))

解决方案 #

直接使用data.table实现即可，该包底层为C语言优化，默认按引用修改数据无多余副本，内存占用仅为dplyr方案的1/3~1/2，处理500万行数据耗时在秒级，不会出现内存溢出问题。

实现代码 #

library(data.table)
# 转换为data.table格式
setDT(df0)
# 按分组和时间排序
setorder(df0, group, Time)
# 计算并过滤
res <- df0[
    # 先计算当前行所有数值列的总和
    , `:=`(row_sum = Val1 + Val2)
  ][
    # 分组计算当前行+上一行+下一行的总和
    , `:=`(total_sum = row_sum + shift(row_sum, 1, type = "lag") + shift(row_sum, 1, type = "lead")),
    by = group
  ][
    # 过滤掉总和为0的行，首尾行NA值保留
    is.na(total_sum) | total_sum != 0
  ]
# 可选：删除辅助计算列
res[, c("row_sum", "total_sum") := NULL]

多数值列适配 #

如果你的数值列不止Val1、Val2，可以用rowSums批量计算，不用手动逐列相加：

# .SDcols指定要计算的数值列，这里直接选所有数值类型的列
df0[, row_sum := rowSums(.SD), .SDcols = is.numeric]

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