CODEWITHSUNDEEP
rdata.tablelagdifference
user974465
user974465

Reputation:

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
                 6 3 2',header=TRUE)
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

Upvotes: 1

Views: 3385

Answers (1)

akrun
akrun

Reputation: 886948

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 ]

Upvotes: 1

Related Questions