Identify and remove duplicates by a criteria in R

Question

Hi I am puzzled with a problem concerning duplicates in R. I have looked around a lot and don't seem to find any help. I have a dataset like that

x = data.frame( id = c("A","A","A","A","A","A","A","B","B","B","B"),
                StartDate = c("09/07/2006", "09/07/2006", "09/07/2006", "08/10/2006", 
                              "08/10/2006", "09/04/2007", "02/03/2011","05/05/2005", "08/06/2009", "07/09/2009", "07/09/2009"),
                EndDate = c("06/08/2006", "06/08/2006", "06/08/2006", "19/11/2006", "19/11/2006", "07/05/2007", "30/03/2011",
                            "02/06/2005", "06/07/2009", "05/10/2009", "05/10/2009"),
                Group = c(1,1,1,2,2,3,4,2,3,4,4),
                TestDate = c("09/06/2006", "08/09/2006", "08/10/2006", "08/09/2006", "08/10/2006", "NA", "02/03/2011",
                              "NA", "07/09/2009", "07/09/2009", "08/10/2009"),
                Code = c(4,4,4858,4,4858,NA,4,NA, 795, 795, 4)
              )

> x
   id  StartDate    EndDate Group   TestDate Code
1   A 09/07/2006 06/08/2006     1 09/06/2006    4
2   A 09/07/2006 06/08/2006     1 08/09/2006    4
3   A 09/07/2006 06/08/2006     1 08/10/2006 4858
4   A 08/10/2006 19/11/2006     2 08/09/2006    4
5   A 08/10/2006 19/11/2006     2 08/10/2006 4858
6   A 09/04/2007 07/05/2007     3         NA   NA
7   A 02/03/2011 30/03/2011     4 02/03/2011    4
8   B 05/05/2005 02/06/2005     2         NA   NA
9   B 08/06/2009 06/07/2009     3 07/09/2009  795
10  B 07/09/2009 05/10/2009     4 07/09/2009  795
11  B 07/09/2009 05/10/2009     4 08/10/2009    4

So basically what I am trying to do is to identify duplicates in the TestDate variable by ID. For example dates 08/09/2006 and 08/10/2006 seem to be repeated in the same person but for different Group and I don't want the same Testdate to be in different Group by ID. The criteria to choose which TestDate to choose is to take the difference in days of TestDate with StartDate and EndDate for the different groups and then keep the one with the smallest difference in days. For example, about the date 08/10/2006 I would like to keep row 5 as the TestDate there is closer to the StartDate, than compared with the same differences in row 3. Eventually, I would like to get with a dataset like that

> xfinal
   id  StartDate    EndDate Group   TestDate Code
1   A 09/07/2006 06/08/2006     1 09/06/2006    4
4   A 08/10/2006 19/11/2006     2 08/09/2006    4
5   A 08/10/2006 19/11/2006     2 08/10/2006 4858
6   A 09/04/2007 07/05/2007     3         NA   NA
7   A 02/03/2011 30/03/2011     4 02/03/2011    4
8   B 05/05/2005 02/06/2005     2         NA   NA
10  B 07/09/2009 05/10/2009     4 07/09/2009  795
11  B 07/09/2009 05/10/2009     4 08/10/2009    4

Any help on that will be much appreciated. Thanks

`x[!duplicated(x[, c('id','TestDate')], fromLast = TRUE), ]` ? some more options [here](http://stackoverflow.com/questions/13967063/remove-duplicate-rows-in-r) — rawr, Nov 24 '15 at 18:45

score 0 · Answer 1 · answered Nov 24 '15 at 19:03

x$StartDate <- as.Date(x$StartDate,format="%d/%m/%Y")
x$EndDate <- as.Date(x$EndDate,format="%d/%m/%Y")
x$TestDate <- as.Date(x$TestDate,format="%d/%m/%Y")
x$Diff <- difftime(x$EndDate,x$StartDate,"days")

x <- x[order(x$id,x$Diff),]

x <- x[!duplicated(x[,c("id","TestDate")]),]
x$Diff <- NULL
x

Identify and remove duplicates by a criteria in R

1 Answers1