Let's consider the following data table
dt <- data.table(a = c(1,2,3), b = c(4,5,6), d = c(7,8,9))
I would like to subset dt once with dt[, .SD[<something>], by = a] and inside the subset .SD, I would like to invoke another subsetting as
dt[, .SD[.SD[<something else>], by = b], by = a]
But inside the second subsetting <something else>, I would like to access to the first .SD in dt[, .SD[<something>], by = a].
Is this possible with the data.table syntax?
A typical application: Mean-square displacement calculations.
Consider a dataset, where you have particles identified with AgentID and their displacement over time, specified by displacement and Time variables.
For each AgentID and Time, you need to calculate cumsum(displacement) for all t > Time where.
e.g if
dt <- data.table(Time = c(1,2,3), AgentID = c(1), displacement = c(-2,4,-6))
then one would typically do
DT <- dt[, .(msd = cumsum(dt[Time >= .BY$Time & AgentID == .BY$AgentID]$displacement)), by = .(Time, AgentID)]
DT
Time AgentID msd
1: 1 1 -2
2: 1 1 2
3: 1 1 -4
4: 2 1 4
5: 2 1 -2
6: 3 1 -6
Calling dt inside dt is not ideal for several reasons. Instead I want to subset twice; e.g dt[.SD[.SD[<calculate msd>], by = Time], by = AgentID], but while inside of the inner-most .SD, I need to access the outer .SD.