Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

R, conditionally remove duplicate rows

I have a dataframe in R containing the columns ID.A, ID.B and DISTANCE, where distance represents the distance between ID.A and ID.B. For each value (1->n) of ID.A, there may be multiple values of ID.B and DISTANCE (i.e. there may be multiple duplicate rows in ID.A e.g. all of value 4 which each has a different ID.B and distance in that row).

I would like to be able to remove rows where ID.A is duplicated, but conditional upon the distance value such that I am left with the smallest distance values for each ID.A record.

Hopefully that makes sense?

Many thanks in advance

EDIT

Hopefully an example will prove more useful than my text. Here I would like to remove the second and third rows where ID.A = 3:

myDF <- read.table(text="ID.A ID.B DISTANCE
  1 3 1
  2 6 8
  3 2 0.4
  3 3 1
  3 8 5
  4 8  7
  5 2 11", header = TRUE)
like image 659
JSnf2012 Avatar asked May 31 '12 14:05

JSnf2012


1 Answers

You can also do it easily in base R. If dat is your dataframe,

do.call(rbind, 
        by(dat, INDICES=list(dat$ID.A), 
           FUN=function(x) head(x[order(x$DISTANCE), ], 1)))
like image 164
Matthew Plourde Avatar answered Sep 25 '22 15:09

Matthew Plourde