John - 6 months ago 29

R Question

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

Answer

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)
```

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 ]
```