Select rows until last non-missing value by group using data.table

Question

I have the following data:

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)

For each store and client in DT, I want to keep rows until the last non-NA value of sells, and remove the subsequent rows.

     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

Desired result:

     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

I have tried using .SD together with which.max to get the maximum week, but I can't solve it

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

Thank you very much for your kind answers.

Answer 1

Another attempt, using the .I rows index to subset all rows with an index <= to the last non-NA:

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