Need a data.table method to use a lagged computed value by group

user_13783803 0
Viewed 75

Sample data:

> DT <- data.table(yr=rep(1:4,2), grp=sort(rep(c('a','b'),4)), val=c(25,20,NA,NA,10,12,NA,NA))
> DT
   yr grp val
1:  1   a  25
2:  2   a  20
3:  3   a  NA
4:  4   a  NA
5:  1   b  10
6:  2   b  12
7:  3   b  NA
8:  4   b  NA

I want to compute val2 as half of val. For yr<=2 the equation is val2==0.5*val. However, starting in yr==3, val2 is half of the previous year yr computed val2 for a given group grp. So ideally I would get:

   yr grp val val2
1:  1   a  25 12.5
2:  2   a  20 10.0
3:  3   a  NA  5.0
4:  4   a  NA  2.5
5:  1   b  10  5.0
6:  2   b  12  6.0
7:  3   b  NA  3.0
8:  4   b  NA  1.5

I prefer to avoid a loop since my really data is very large.

data.table r
2 Answers
 DT[, val2 := nafill(val, "locf")/2^(1 + cumsum(is.na(val))), grp]
 DT
   yr grp val val2
1:  1   a  25 12.5
2:  2   a  20 10.0
3:  3   a  NA  5.0
4:  4   a  NA  2.5
5:  1   b  10  5.0
6:  2   b  12  6.0
7:  3   b  NA  3.0
8:  4   b  NA  1.5
user_83802720

here is an option

library(data.table)
DT[, val2 := val/2, grp]
DT[, val2 := replace(val2, is.na(val2),
   last(na.omit(val2))/2^(seq_len(sum(is.na(val2))))), grp]

-output

DT
#   yr grp val val2
#1:  1   a  25 12.5
#2:  2   a  20 10.0
#3:  3   a  NA  5.0
#4:  4   a  NA  2.5
#5:  1   b  10  5.0
#6:  2   b  12  6.0
#7:  3   b  NA  3.0
#8:  4   b  NA  1.5
user_37322710
Related