R data.table value from previous row with conditional statement

Question

I would like to update a data table value depending on whether it meets a criteria and return either the value from another column or the value from the row above (same column). As an example:

library( data.table )
data <- data.table( Col1 = 1:5, Col2 = letters[1:5] )

I would like to return the following:

data2 <- data.table( Col1= 1:5, Col2= letters[1:5], Col3= c("NA", "NA", "3", "3", "3"))

I have read the ?shift help page but I can't adapt it to using a conditional statement and returning a value in the same column. To get my desired outcome I have tried:

data[ , ( Col3 ) := ifelse( get( Col2 ) == "c", get( Col1 ) , shift( Col3 ))]

I would be grateful for some advice.

*Please ignore my use of get() for this example as I am aware it may not be the best approach.

Answer 1

This old, so far unanswered question has been revived recently.

As of today I am aware of the following approaches:

1. zoo::na.locf()

According to Frank's comment:

data3 <- data.table(Col1= 1:10, Col2 = c(letters[1:5],letters[1:5]))
data3[Col2=='c', Col3 := Col1][, Col3 := zoo::na.locf(Col3, na.rm=FALSE)]
data3[]

    Col1 Col2 Col3
 1:    1    a   NA
 2:    2    b   NA
 3:    3    c    3
 4:    4    d    3
 5:    5    e    3
 6:    6    a    3
 7:    7    b    3
 8:    8    c    8
 9:    9    d    8
10:   10    e    8

2. cumsum()

data3 <- data.table(Col1= 1:10, Col2 = c(letters[1:5],letters[1:5]))
data3[, Col3 := Col1[which(Col2 == "c")], by = cumsum(Col2 == "c")]
data3[]

    Col1 Col2 Col3
 1:    1    a   NA
 2:    2    b   NA
 3:    3    c    3
 4:    4    d    3
 5:    5    e    3
 6:    6    a    3
 7:    7    b    3
 8:    8    c    8
 9:    9    d    8
10:   10    e    8