使用data.table按组保留至sells最后一个非NA值的行

需求说明 #

对DT按store和client分组，保留每组中到sells字段最后一个非NA值为止的所有行，移除该值之后的所有行。

原始数据代码 #

library(data.table)

D1 <- data.table(store = "store1",
                 client = "123456",
                 week = c(2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2007, 2010, 2011,2012, 2013, 2014, 2015),
                 sells = c(3434, NA, 6566, NA, 8788, 4343, NA , NA, NA, NA, NA, NA, NA, NA, NA))

D2 <- data.table(store = "store1",
                client = "654321",
                week = c(2001,2002,2003, 2004, 2005, 2006, 2007 , 2008, 2007, 2010, 2011, 2012, 2013, 2014, 2015),
                sells = c(45455, 45454, 5454, NA, 65656, 5858, 43434, 55898, NA, NA, NA, NA, NA, NA, NA))

DT <- rbind(D1, D2)

原始数据展示 #

store client week sells
1:  store1 123456 2001  3434
2:  store1 123456 2002    NA
3:  store1 123456 2003  6566
4:  store1 123456 2004    NA
5:  store1 123456 2005  8788
6:  store1 123456 2006  4343 # last non-NA for store1, client 123456
7:  store1 123456 2007    NA
8:  store1 123456 2008    NA
9:  store1 123456 2007    NA
10: store1 123456 2010    NA
11: store1 123456 2011    NA
12: store1 123456 2012    NA
13: store1 123456 2013    NA
14: store1 123456 2014    NA
15: store1 123456 2015    NA
16: store1 654321 2001 45455
17: store1 654321 2002 45454
18: store1 654321 2003  5454
19: store1 654321 2004    NA
20: store1 654321 2005 65656
21: store1 654321 2006  5858
22: store1 654321 2007 43434
23: store1 654321 2008 55898 # last non-NA for store1, client 654321
24: store1 654321 2007    NA
25: store1 654321 2010    NA
26: store1 654321 2011    NA
27: store1 654321 2012    NA
28: store1 654321 2013    NA
29: store1 654321 2014    NA
30: store1 654321 2015    NA

期望结果 #

store client week sells
 1: store1 123456 2001  3434
 2: store1 123456 2002    NA
 3: store1 123456 2003  6566
 4: store1 123456 2004    NA
 5: store1 123456 2005  8788
 6: store1 123456 2006  4343
 7: store1 654321 2001 45455
 8: store1 654321 2002 45454
 9: store1 654321 2003  5454
10: store1 654321 2004    NA
11: store1 654321 2005 65656
12: store1 654321 2006  5858
13: store1 654321 2007 43434
14: store1 654321 2008 55898

尝试过的无效代码 #

DT[, .SD[which.max(week)], keyby = c("store", "client")]

解决方案 #

方法一：分组内直接筛选行 #

DT[, .SD[1:max(which(!is.na(sells)))], by = .(store, client)]

方法二：全局行索引筛选（大数据量更高效） #

DT[DT[, .I[1:max(which(!is.na(sells)))], by = .(store, client)]$V1]

代码说明 #

1. which(!is.na(sells))：获取当前分组内所有sells非NA的行位置
2. max(...)：取这些位置的最大值，即最后一个非NA值所在的行位置
3. 1:max(...)：保留从第一行到该位置的所有行
4. 方法二中的.I用于获取数据表全局行索引，通过索引直接筛选，适合处理大规模数据

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