# How do I remove all but one specific duplicate record in an R data frame? [closed]

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...

-

## closed as off-topic by gung, John, Scortchi♦Jun 21 at 18:58

This question appears to be off-topic. The users who voted to close gave this specific reason:

• "This question appears to be off-topic because EITHER it is not about statistics, machine learning, data analysis, data mining, or data visualization, OR it focuses on programming, debugging, or performing routine operations within a statistical computing platform. If the latter, you could try the support links we maintain." – gung, John, Scortchi
If this question can be reworded to fit the rules in the help center, please edit the question.

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  - 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 '14 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. - And as usual this is about 2x faster than plyr version. – mbq May 24 '11 at 15:39 @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 @Eduardo ave is a wrapper of lapply+split, check the code (-; – mbq May 25 '11 at 7:09 @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),])

-
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)))

-
+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. - If, as in the example, the column var is already in ascending order we do not need to sort the data frame. We just use the function duplicated passing the argument fromLast = TRUE, so duplication is considered from the reverse side, keeping the last elements: z <- data.frame(id=c(1,1,2,2,3,4),var=c(2,4,1,3,5,2)) z[!duplicated(z$id, fromLast = TRUE), ]

id var
2  1   4
4  2   3
5  3   5
6  4   2


Otherwise we sort the data frame in ascending order first:

z <- z[order(z$id, z$var), ]
z[!duplicated(z\$id, fromLast = TRUE), ]


Using the dplyr package:

library(dplyr)
z %>%
group_by(id) %>%
summarise(var = max(var))

Source: local data frame [4 x 2]
id var
1  1   4
2  2   3
3  3   5
4  4   2

-