John - 1 year ago 145
R Question

# R: How to calculate lag for multiple columns by group for data table

I would like to calculate the diff of variables in a data table, grouped by id. Here is some sample data. The data is recorded at a sample rate of 1 Hz. I would like to estimate the first and second derivatives (speed, acceleration)

``````df <- read.table(text='x y id
1 2 1
2 4 1
3 5 1
1 8 2
5 2 2
dt<-data.table(df)
``````

Expected output

``````# dx dy id
# NA NA 1
# 1  2  1
# 1  1  1
# NA NA 2
# 4  -6  2
# 1 1    2
``````

Here's what I've tried

``````dx_dt<-dt[, diff:=c(NA,diff(dt[,'x',with=FALSE])),by = id]
``````

Output is

``````Error in `[.data.frame`(dt, , `:=`(diff, c(NA, diff(dt[, "x", with = FALSE]))),  :
unused argument (by = id)
``````

As pointed out by Akrun, the 'speed' terms (dx, dy) can be obtained using either data table or plyr. However, I'm unable to understand the calculation well enough to extend it to acceleration terms. So, how to calculate the 2nd lag terms?

``````dt[, c('dx', 'dy'):=lapply(.SD, function(x) c(NA, diff(x))),
+ by=id]
``````

produces

``````   x y id dx dy
1: 1 2  1 NA NA
2: 2 4  1  1  2
3: 3 5  1  1  1
4: 1 8  2 NA NA
5: 5 2  2  4 -6
6: 6 3  2  1  1
``````

How to expand to get a second diff, or the diff of dx, dy?

``````   x y id dx dy  dx2  dy2
1: 1 2  1 NA NA   NA   NA
2: 2 4  1  1  2   NA   NA
3: 3 5  1  1  1    0   -1
4: 1 8  2 NA NA   NA   NA
5: 5 2  2  4 -6   NA   NA
6: 6 3  2  1  1   -3    7
``````

You can try

`````` setnames(dt[, lapply(.SD, function(x) c(NA,diff(x))), by=id],
2:3, c('dx', 'dy'))[]
#    id dx dy
#1:  1 NA NA
#2:  1  1  2
#3:  1  1  1
#4:  2 NA NA
#5:  2  4 -6
#6:  2  1  1
``````

Another option would be to use `dplyr`

`````` library(dplyr)
df %>%
group_by(id) %>%
mutate_each(funs(c(NA,diff(.))))%>%
rename(dx=x, dy=y)
``````

### Update

You can repeat the step twice

``````dt[, c('dx', 'dy'):=lapply(.SD, function(x) c(NA, diff(x))), by=id]
dt[,c('dx2', 'dy2'):= lapply(.SD, function(x) c(NA, diff(x))),
by=id, .SDcols=4:5]
dt
#   x y id dx dy dx2 dy2
#1: 1 2  1 NA NA  NA  NA
#2: 2 4  1  1  2  NA  NA
#3: 3 5  1  1  1   0  -1
#4: 1 8  2 NA NA  NA  NA
#5: 5 2  2  4 -6  NA  NA
#6: 6 3  2  1  1  -3   7
``````

Or we can use the `shift` function from `data.table`

``````dt[, paste0("d", c("x", "y")) := .SD - shift(.SD), by = id
][, paste0("d", c("x2", "y2")) := .SD - shift(.SD) , by =  id, .SDcols = 4:5 ]
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download