R Create column based on previously observed value

Question

I would like to create a new column which reports the reported col1 value larger than zero until a new col1 value larger than zero is encountered (see col2 in df2). I.e. the value of zero in col1 is replaced by the observed value larger than zero.

ID = c(1,1,1,1,1,1,1,1,2,2,2,2)
col1 = c(500,0,0,0,600,0,0,0,450,0,0,0)
df1 = data.frame(ID,col1)

ID = c(1,1,1,1,1,1,1,1,2,2,2,2)
col1 = c(500,0,0,0,600,0,0,0,450,0,0,0)
col2 = c(500,500,500,500,600,600,600,600,450,450,450,450)
df2 = data.frame(ID,col1,col2)

Any way of doing this?

Answer 1

We can use data.table with zoo. Convert the 'data.frame' to 'data.table' (setDT(df1)), assign a new column 'col2' with the values of 'col1', change the elements which are '0' to NA and then use na.locf to replace the NA elements with the previous non-NA element grouped by "ID".

library(zoo)
library(data.table)
setDT(df1)[, col2:=col1][col2==0, col2:= NA]
df1[,col2:= na.locf(col2) ,ID]
df1
#    ID col1 col2
# 1:  1  500  500
# 2:  1    0  500
# 3:  1    0  500
# 4:  1    0  500
# 5:  1  600  600
# 6:  1    0  600
# 7:  1    0  600
# 8:  1    0  600
# 9:  2  450  450
#10:  2    0  450
#11:  2    0  450
#12:  2    0  450