R data.table binary value for last row in group by condition
For each id, check if row number is the last row number in the group, and if 'conversion' is 1. Convert logical result to integer.
DT[ , lastconv := as.integer(.I == .I[.N] & conversion == 1), by = id]
Modifying the OP's code to join on the last row of each group:
DT[, v := 0]
DT[.(DT[conversion == 1, unique(id)]), on=.(id), mult="last", v := 1]
id conversion v
1: 1232 0 0
2: 1232 0 0
3: 1232 0 0
4: 4211 1 0
5: 4211 1 0
6: 4211 1 1
This is only different in that it selects which id
s to edit based on the desired condition.
Filter for the last row per group and set lastconv
equal to conversion
.
DT[DT[, .I[.N], by=id]$V1, lastconv := conversion]
Then replace NA
s with 0
DT[is.na(lastconv), lastconv := 0L]
Result
DT
# id conversion lastconv
#1: 1232 0 0
#2: 1232 0 0
#3: 1232 0 0
#4: 4211 1 0
#5: 4211 1 0
#6: 4211 1 1
If data.table
v1.12.3 is installed we could also use the new function setnafill
to replace NA
s in the second step
DT[DT[, .I[.N], by=id]$V1, lastconv := conversion]
setnafill(DT, cols = "lastconv", fill = 0L)