# Fast ways in R to get the first row of a data frame grouped by an identifier

Sometimes I need to get only the first row of a data set grouped by an identifier, as when retrieving age and gender when there are multiple observations per individual. What's a fast (or the fastest) way to do this in R? I used aggregate() below and suspect there are better ways. Before posting this question I searched a bit on google, found and tried ddply, and was surprised that it was extremely slow and gave me memory errors on my dataset (400,000 rows x 16 cols, 7,000 unique IDs), whereas the aggregate() version was reasonably fast.

(dx <- data.frame(ID = factor(c(1,1,2,2,3,3)), AGE = c(30,30,40,40,35,35), FEM = factor(c(1,1,0,0,1,1))))
# ID AGE FEM
#  1  30   1
#  1  30   1
#  2  40   0
#  2  40   0
#  3  35   1
#  3  35   1
ag <- data.frame(ID=levels(dx$ID)) ag <- merge(ag, aggregate(AGE ~ ID, data=dx, function(x) x[1]), "ID") ag <- merge(ag, aggregate(FEM ~ ID, data=dx, function(x) x[1]), "ID") ag # ID AGE FEM # 1 30 1 # 2 40 0 # 3 35 1 #same result: library(plyr) ddply(.data = dx, .var = c("ID"), .fun = function(x) x[1,])  UPDATE: See Chase's answer and Matt Parker's comment for what I consider to be the most elegant approach. See @Matthew Dowle's answer for the fastest solution which uses the data.table package. - Thanks for all your answers. The data.table solution of @Steve was the fastest by a factor of ~5 on my data set over the aggregate() solution of @Gavin (which in turn was faster than my aggregate() code), and a factor of ~7.5 over the by() solution of @Matt. I didn't time the reshape idea because I couldn't get it working quickly. I'm guessing the solution that @Chase gave will be the fastest and it was actually what I was looking for, but when I started writing this comment, the code wasn't working (I see it's fixed now!). – lockedoff Mar 4 '11 at 18:17 Actually @Chase was faster by a factor of ~9 over data.table, so I changed my accepted answer. Thanks again everyone - learned a bunch of new tools. – lockedoff Mar 4 '11 at 18:29 sorry, I fixed my code. The one caveat or trick here is to concatenate a value that isn't one of your IDs in the diff() so that you can pick up the first ID in dx. – Chase Mar 4 '11 at 18:30 add comment ## 6 Answers Is your ID column really a factor? If it is in fact numeric, I think you can use the diff function to your advantage. You could also coerce it to numeric with as.numeric(). dx <- data.frame( ID = sort(sample(1:7000, 400000, TRUE)) , AGE = sample(18:65, 400000, TRUE) , FEM = sample(0:1, 400000, TRUE) ) dx[ diff(c(0,dx$ID)) != 0, ]

-
Clever! You could also do dx[c(TRUE, dx$ID[-1] != dx$ID[-length(dx$ID)], ] for non-numeric data - I get 0.03 for character, 0.05 for factors. PS: there's an extra ) in your first system.time() function, after the second zero. – Matt Parker Mar 4 '11 at 18:32 @Matt - good call and nice catch. I don't appear to be able to copy/paste code worth a flip today. – Chase Mar 4 '11 at 18:33 add comment You don't need multiple merge() steps, just aggregate() both variables of interest: > aggregate(dx[, -1], by = list(ID = dx$ID), head, 1)
ID AGE FEM
1  1  30   1
2  2  40   0
3  3  35   1

> system.time(replicate(1000, aggregate(dx[, -1], by = list(ID = dx$ID), + head, 1))) user system elapsed 2.531 0.007 2.547 > system.time(replicate(1000, {ag <- data.frame(ID=levels(dx$ID))
+ ag <- merge(ag, aggregate(AGE ~ ID, data=dx, function(x) x[1]), "ID")
+ ag <- merge(ag, aggregate(FEM ~ ID, data=dx, function(x) x[1]), "ID")
+ }))
user  system elapsed
9.264   0.009   9.301


Comparison timings:

1) Matt's solution:

> system.time(replicate(1000, {
+ agg <- by(dx, dx$ID, FUN = function(x) x[1, ]) + # Which returns a list that you can then convert into a data.frame thusly: + do.call(rbind, agg) + })) user system elapsed 3.759 0.007 3.785  2) Zach's reshape2 solution: > system.time(replicate(1000, { + dx <- melt(dx,id=c('ID','FEM')) + dcast(dx,ID+FEM~variable,fun.aggregate=mean) + })) user system elapsed 12.804 0.032 13.019  3) Steve's data.table solution: > system.time(replicate(1000, { + dxt <- data.table(dx, key='ID') + dxt[, .SD[1,], by=ID] + })) user system elapsed 5.484 0.020 5.608 > dxt <- data.table(dx, key='ID') ## one time step > system.time(replicate(1000, { + dxt[, .SD[1,], by=ID] ## try this one line on own + })) user system elapsed 3.743 0.006 3.784  4) Chase's fast solution using numeric, not factor, ID: > dx2 <- within(dx, ID <- as.numeric(ID)) > system.time(replicate(1000, { + dy <- dx[order(dx$ID),]
+ dy[ diff(c(0,dy$ID)) != 0, ] + })) user system elapsed 0.663 0.000 0.663  and 5) Matt Parker's alternative to Chase's solution, for character or factor ID, which is slightly faster than Chase's numeric ID one: > system.time(replicate(1000, { + dx[c(TRUE, dx$ID[-1] != dx$ID[-length(dx$ID)]), ]
+ }))
user  system elapsed
0.513   0.000   0.516

-
Oh, right, thanks! Forgot about that syntax for aggregate. –  lockedoff Mar 4 '11 at 17:53
If you'd like to add Chase's solution, here's what I got: dx$ID <- sample(as.numeric(dx$ID)) #assuming IDs arent presorted system.time(replicate(1000, { dy <- dx[order(dx$ID),] dy[ diff(c(0,dy$ID)) != 0, ] })) user system elapsed 0.58 0.00 0.58 –  lockedoff Mar 4 '11 at 18:34
@lockedoff - done, thanks, but I didn't randomly sample the IDs so the result were comparable to other solutions. –  Gavin Simpson Mar 4 '11 at 20:17
And time @Matt Parker's version in the comments to @Chase's answer –  Gavin Simpson Mar 4 '11 at 20:21
Thanks for doing the timings, Gavin - that's really helpful for questions like these. –  Matt Parker Mar 4 '11 at 20:53

Following up to Steve's reply, there is a much faster way in data.table :

> # Preamble
> dx <- data.frame(
+     ID = sort(sample(1:7000, 400000, TRUE))
+     , AGE = sample(18:65, 400000, TRUE)
+     , FEM = sample(0:1, 400000, TRUE)
+ )
> dxt <- data.table(dx, key='ID')

> # fast self join
> system.time(ans2<-dxt[J(unique(ID)),mult="first"])
user  system elapsed
0.048   0.016   0.064

> # slower using .SD
> system.time(ans1<-dxt[, .SD[1], by=ID])
user  system elapsed
14.209   0.012  14.281

> mapply(identical,ans1,ans2)  # ans1 is keyed but ans2 isn't, otherwise identical
ID  AGE  FEM
TRUE TRUE TRUE


If you merely need the first row of each group, it's much faster to join to that row directly. Why create the .SD object each time, only to use the first row of it?

Compare the 0.064 of data.table to "Matt Parker's alternative to Chase's solution" (which seemed to be the fastest so far) :

> system.time(ans3<-dxt[c(TRUE, dxt$ID[-1] != dxt$ID[-length(dxt$ID)]), ]) user system elapsed 0.284 0.028 0.310 > identical(ans1,ans3) [1] TRUE  So ~5 times faster, but it's a tiny table at under 1 million rows. As size increases, so does the difference. - Wow, I never really appreciated how "smart" the [.data.table function can get ... I guess I didn't realize you didn't create an .SD object if you didn't really need it. Nice one! – Steve Lianoglou Mar 8 '11 at 4:18 Yes, that is indeed fast! Even if you include dxt <- data.table(dx, key='ID') in the call to system.time(), it's faster than @Matt's solution. – lockedoff Mar 8 '11 at 16:32 add comment You can try to use the data.table package. For your particular case, the upside is that it's (insanely) fast. The first time I was introduced to it, I was working on data.frame objects with hundreds of thousands of rows. "Normal" aggregate or ddply methods were taken ~ 1-2 mins to complete (this was before Hadley introduced the idata.frame mojo into ddply). Using data.table, the operation was literally done in a matter of seconds. The downside is that its so fast because it will resort your data.table (it's just like a data.frame) by "key columns" and use a smart searching strategy to find subsets of your data. This will result in a reordering of your data before you collect stats over it. Given that you will just want the first row of each group -- maybe the reordering will mess up which row is first, which is why it might not be appropriate in your situation. Anyway, you'll have to judge whether or not data.table is appropriate here, but this is how you would use it with the data you've presented: install.packages('data.table') ## if yo udon't have it already library(data.table) dxt <- data.table(dx, key='ID') dxt[, .SD[1,], by=ID] ID AGE FEM [1,] 1 30 1 [2,] 2 40 0 [3,] 3 35 1  Update: Matthew Dowle (the main developer of the data.table package) has provided a better/smarter/(extremely) more efficient way to use data.table to solve this problem as one of the answers here ... definitely check that out. - add comment Try reshape2 library(reshape2) dx <- melt(dx,id=c('ID','FEM')) dcast(dx,ID+FEM~variable,fun.aggregate=mean)  - add comment You could try agg <- by(dx, dx$ID, FUN = function(x) x[1, ])
# Which returns a list that you can then convert into a data.frame thusly:
do.call(rbind, agg)


I have no idea if this will be any faster than plyr, though.

-