Why is mutate () for a group taken forever?

Question

I have a 2.5 M x 13 matrix that I am trying to combine by ID variable. At first I tried using ddply, but my memory exploded. Subsequently, I tried using data.table, which works much faster:

data <- as.data.table(data)
key(data) <- "ID"

agg<-mydata[,mutate(.SD,
           start = min(Date))
           , by = ID]

Now there is no memory problem, however, this requires more than ~ 4 hours on Intel i5 2.50GHz with 4.0 GB of RAM. The operating system is Windows 7, so there is no parallel computing.

What am I doing wrong?

Answer 1

You do not need mutate, just use start := min(Date). I believe this should speed it up.

agg <- mydata[, start := min(Date), by = ID]

Answer 2

@konvas , , := :

##
library(data.table)
library(plyr)
library(microbenchmark)
##
t0 <- as.Date("2013-01-01")
Df <- data.frame(
  ID=sample(LETTERS,500000,replace=TRUE),
  Date=t0+sample((-100):100,500000,replace=TRUE),
  stringsAsFactors=FALSE)
Dt1 <- data.table(Df)
setkeyv(Dt1,cols="ID")
Dt2 <- copy(Dt1)
##
f1 <- function(){
  Agg <- Dt1[
    ,
    mutate(.SD,start = min(Date)),
    by = list(ID)]
}
f2 <- function(){
  Agg <- Dt2[
    ,
    "Start":=min(Date),
    by=list(ID)]
}
##
Res <- microbenchmark(
  f1(),f2()
)
##
Unit: milliseconds
expr      min       lq   median       uq      max neval
f1() 25.08676 27.30188 28.22867 31.60754 63.97749   100
f2() 10.48293 11.39930 13.25193 14.26284 47.80564   100