R: How to make transformations on subset of a subset fast (optimize or alternative to `ddply`)?

Question

I have a somewhat large data set (784,932 rows/items, 27,492 unique IDs). For each Item in each ID, I am trying to create a dummy variable equal to 1, if the difference between the dates is less than 60 secs. Stylised data and code: ``` ID <- c(1,1,1,1,1,1,3,3,3,3,3,3) Item <- c(10,10,10,20,20,20,10,20,10,10,10,20) Date <- c("19/11/13 18:58:00","19/11/13 18:58:21","19/11/13 20:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 19:58:00") df <- data.frame(ID, Item, Date) df <- df[order(ID, Date), ] df[, "Date"] = lapply(df["Date"],function(x){strptime(x, "%d/%m/%y %H:%M:%S")}) # less than 60 sec difference = 1 (first item set to 999 -> 0) fnDummy <- function(date) { ifelse(c(999, diff(date))<60, 1, 0) } library(plyr) ddply(df, .(ID, Item), transform, Dummy=fnDummy(Date) ) ``` Output: ``` ID Item Date Dummy 1 1 10 2013-11-19 18:58:00 0 2 1 10 2013-11-19 18:58:21…

Accepted Answer

You can use `data.table` as you suggest. You'll have to convert your `POSIXlt` to `POSIXct` though:

```
library(data.table)
df$Date <- as.POSIXct(df$Date)
DT <- as.data.table(df)

DT[, dummy_date := fnDummy(Date), by=c('ID', 'Item')]
```

However, a big part of the slowdown is probably in the `ifelse` function and you don't really need it since you're creating a boolean:

```
as.integer(c(FALSE, diff(date) < 60))
```

R: How to make transformations on subset of a subset fast (optimize or alternative to `ddply`)?

Solution

Problem