Take the 2-minute tour ×
Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. It's 100% free, no registration required.

I have a data frame that contains some duplicate ids. I want to remove records with duplicate ids, keeping only the row with the maximum value.

So for structured like this (other variables not shown):

id var_1
1 2
1 4
2 1
2 3
3 5
4 2

I want to generate this:

id var_1
1 4
2 3
3 5
4 2

I know about unique() and duplicated(), but I can't figure out how to incorporate the maximization rule...

share|improve this question

5 Answers 5

up vote 14 down vote accepted

One way is to reverse-sort the data and use duplicated to drop all the duplicates. For me, this method is conceptually simpler than those that use apply. I think it should be very fast as well.

# Some data to start with:
z <- data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,5,2))
# id var
#  1   2
#  1   4
#  2   1
#  2   3
#  3   5
#  4   2

# Reverse sort
z <- z[order(z$id, z$var, decreasing=TRUE),]
# id var
#  4   2
#  3   5
#  2   3
#  2   1
#  1   4
#  1   2

# Keep only the first row for each duplicate of z$id; this row will have the
# largest value for z$var
z <- z[!duplicated(z$id),]

# Sort so it looks nice
z <- z[order(z$id, z$var),]
# id var
#  1   4
#  2   3
#  3   5
#  4   2

Edit: I just realized that the reverse sort above doesn't even need to sort on id at all. You could just use z[order(z$var, decreasing=TRUE),] instead and it will work just as well.

One more thought... If the var column is numeric, then there's a simple way to sort so that id is ascending, but var is descending. This eliminates the need for the sort at the end (assuming you even wanted it to be sorted).

z <- data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,5,2))

# Sort: id ascending, var descending
z <- z[order(z$id, -z$var),]

# Remove duplicates
z <- z[!duplicated(z$id),]
# id var
#  1   4
#  2   3
#  3   5
#  4   2
share|improve this answer
    
This approach is significantly faster than "split-compute-rbind". Furthermore it allows grouping on more than one factor. For a c. 650,000 row (8, narrow, columns) the "order-duplicated" approach took 55 seconds, the split-compute-rbind... 1h15minutes. Of course when the aggregate computation is other than selecting or filtering duplicates, the latter approach or similar plyr-based approaches are needed. –  mjv Aug 11 at 12:03

The base-R solution would involve split, like this:

z<-data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,4,2))
do.call(rbind,lapply(split(z,z$id),function(chunk) chunk[which.max(chunk$var),]))

split splits the data frame into a list of chunks, on which we perform cutting to the single row with max value and then do.call(rbind,...) reduces the list of single rows into a data frame again.

share|improve this answer
1  
And as usual this is about 2x faster than plyr version. –  mbq May 24 '11 at 15:39
1  
@mbq, yes, naturaly, but if you include the debugging costs, for usual data sets the resulting speed is the same :) plyr is dedicated not for speed, but for clarity and convenience. –  mpiktas May 24 '11 at 19:45
    
and using ave is twice as fast anyway :) –  Eduardo Leoni May 25 '11 at 3:26
2  
@Eduardo ave is a wrapper of lapply+split, check the code (-; –  mbq May 25 '11 at 7:09
1  
@Eduardo Yeah, but it all works only due to a quirky possibility of vectorized sorting within factors using order; for more generic problems split is inevitable. –  mbq May 25 '11 at 11:05

You actualy want to select the maximum element from the elements with the same id. For that you can use ddply from package plyr:

> dt<-data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,4,2))
> ddply(dt,.(id),summarise,var_1=max(var))
   id var_1
1  1   4
2  2   3
3  3   4
4  4   2

unique and duplicated is for removing duplicate records, in your case you only have duplicate ids, not records.

Update: Here is the code when there are additional variables:

> dt<-data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,4,2),bu=rnorm(6))
> ddply(dt,~id,function(d)d[which.max(d$var),])
share|improve this answer
    
What if there were other variables: how do you carry them along? –  Aniko May 24 '11 at 15:09
    
We don't move such questions -- too much rush for too little gain. –  mbq May 24 '11 at 15:24
    
@Aniko, thanks, I've updated the answer. –  mpiktas May 24 '11 at 19:43

I prefer using ave

dt<-data.frame(id=c(1,1,2,2,3,4),var=c(2,4,3,3,4,2))
## use unique if you want to exclude duplicate maxima
unique(subset(dt, var==ave(var, id, FUN=max)))
share|improve this answer
    
+1, did not know about ave. When did it appear in R? –  mpiktas May 25 '11 at 7:03

Yet another way to do this with base:

dt<-data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,4,2))

data.frame(id=sort(unique(dt$var)),max=tapply(dt$var,dt$id,max))
  id max
1  1   4
2  2   3
3  3   4
4  4   2

I prefer mpiktas ' plyr solution though.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.