John - 1 year ago 138

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

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**