I'm trying to build a column in a data.table by interacting with another data.table and having trouble referring to variables correctly to do this without a for-loop. Once I enter the second data.table, I can no longer seem to refer to the column in the first data.table correctly.
This is kind of similar to Subsetting a data.table using another data.table but I believe the merge-style solutions aren't appropriate.
Consider something like
#used numbers instead of dates to not have to deal with formatting, but idea is the same.
dt1 <- data.table(id = c('a', 'b', 'c'), date1 = c(1.1, 5.4, 9.1), amt= '100')
dt2 <- data.table(date2 = c(1.3, 3, 6.4, 10.5),
dt2col = c(1.5, 1.02, 1.005, .99)
)
dt1[result := prod(dt2[date2-(date1)>0,
dt2col
]
)
]
I want the result to be a new column in dt1 which is the product of dt2col when date2 (in dt2) is later than date1 (in dt1) for each specific row in dt1. I think the (date1) part is the problem.
I expect result[1] to be the product of dt2col for all of them, but result[2] to be the product of dt2col for only the dates after '5/4/2018', etc.
year
asyear('1/10/2018')# [1] 1
– akrun