Create Sequence Number for a block of records in an R Data Frame

Question

I have a fairly large dataset (by my standards) and I want to create a sequence number for blocks of records. I can use the plyr package, but the execution time is very slow. The code below replicates a comparable size dataframe.

## simulate an example of the size of a normal data frame
N <- 30000
id <- sample(1:17000, N, replace=T)
term <- as.character(sample(c(9:12), N, replace=T))
date <- sample(seq(as.Date("2012-08-01"), Sys.Date(), by="day"), N, replace=T)
char <- data.frame(matrix(sample(LETTERS, N*50, replace=T), N, 50))
val <- data.frame(matrix(rnorm(N*50), N, 50))
df <- data.frame(id, term, date, char, val, stringsAsFactors=F)
dim(df)

In reality, this is a little smaller than what I work with, as the values are typically larger...but this is close enough.

Here is the execution time on my machine:

> system.time(test.plyr <- ddply(df, 
+                                .(id, term), 
+                                summarise, 
+                                seqnum = 1:length(id), 
+                                .progress="text"))
  |===============================================================================================| 100%
   user  system elapsed 
  63.52    0.03   63.85 

Is there a "better" way to do this? Unfortunately, I am on a Windows machine.

Thanks in advance.

EDIT: Data.table is extremely fast, but I can't get my sequence numbers to calc correctly. Here is what my ddply version created. The majority only have one record in the group, but some have 2 rows, 3 rows, etc.

> with(test.plyr, table(seqnum))
seqnum
    1     2     3     4     5 
24272  4950   681    88     9 

And using data.table as shown below, the same approach yields:

> with(test.dt, table(V1))
V1
    1 
24272 

Answer 1

Use data.table

dt = data.table(df)
test.dt = dt[,.N,"id,term"]

Here is a timing comparison. I used N = 3000 and replaced the 17000 with 1700 while generating the dataset

f_plyr <- function(){
  test.plyr <- ddply(df, .(id, term), summarise, seqnum = 1:length(id), 
 .progress="text")
}

f_dt <- function(){
 dt = data.table(df)
 test.dt = dt[,.N,"id,term"]
}

library(rbenchmark)
benchmark(f_plyr(), f_dt(), replications = 10,
  columns = c("test", "replications", "elapsed", "relative"))

data.table speeds up things by a factor of 170

test replications elapsed relative
2   f_dt()           10   0.779    1.000
1 f_plyr()           10 132.572  170.182

Also check out Hadley's latest work on dplyr. I wouldn't be surprised if dplyr provides an additional speedup, given that a lot of the code is being reworked in C.

UPDATE: Edited code, changing length(id) to .N as per Matt's comment.