r - 使用 data.table 按组选择行直到最后一个非缺失值

Question

我有以下数据:

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)

对于 DT 中的每个商店和客户，我想保留行直到销售的最后一个非 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 # 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

我试过使用 .SD 和 which.max 来获得最大周数，但我无法解决

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

非常感谢您的友好回答。

Answer 1

另一次尝试，使用 .I行索引以索引所有行的子集 <=到最后一个非NA :

DT[DT[, .I <= .I[last(which(!is.na(sells)))], by=.(store,client)]$V1]