Remove rows with leading missing values in a specific column by group with data.table

user_1042478 0
Viewed 795

I have a data.table like this:

DT <- data.table(id = c(rep("a", 3), rep("b", 3)),
                 col1 = c(NA,1,2,NA,3,NA), col2 = c(NA,NA,5,NA,NA,NA))
   id col1 col2
1:  a   NA   NA
2:  a    1   NA
3:  a    2    5
4:  b   NA   NA
5:  b    3   NA
6:  b   NA   NA

For each id, I would like to remove rows with leading NAs in 'col1' using zoo::na.trim. Here's the result I'm expecting:

   id col1 col2
1:  a    1   NA
2:  a    2    5
3:  b    3   NA
4:  b   NA   NA

Here's what I have tried so far. This indeed removes leading NA in 'col1', but it omits 'col2' from the result:

DT[ , na.trim(col1), by = id]
   id V1
1:  a  1
2:  a  2
3:  b  3

This is also not working:

DT[ , .SD[na.trim(col1)], by = id]
   id col1 col2
1:  a   NA   NA
2:  a    1   NA
3:  b   NA   NA
data.table na zoo r
3 Answers

We can use 1:.N >= which.max(...) to subset the required rows

> DT[, .SD[1:.N >= which.max(!is.na(col1))], id]
   id col1 col2
1:  a    1   NA
2:  a    2    5
3:  b    3   NA
4:  b   NA   NA
user_121587570
Related