[datatable-help] Remove some data table rows based on three conditions

[Bacou, Melanie]

Frank,

My understanding is |unique()| will remove all duplicated records in a 
keyed data.table, keeping only the first occurrence. So if you sort your 
data by decreasing |marker|, you should achieve what you’re looking for. 
Note that |unique()| also comes with a |fromLast=FALSE| argument to keep 
unique records starting from the last occurrence instead.

|dt <- data.table(
  id = rep(c(2,5),c(3,2)),
  datep = as.Date(c('1995-04-20','1995-04-20','1997-02-19','1998-01-15','1998-01-15')),
  marker = c(2,8,5,7,5),
  group=rep(c("A","B"),c(3,2))
  )

setkey(dt, id)
setorder(dt, -marker)
dt <- unique(dt)
|

—Mel.

On 10/22/2014 1:19 PM, Frank S. wrote:

> Dear all,
> I'm working with a large database in wich I have some rows which have 
> identical id and datep variables. Of these
> duplicated rows, I only want to keep those row associated to the 
> maximum value in marker variable. As an example:
> DT <- data.table(
>  id = rep(c(2,5),c(3,2)),
>  datep = as.Date(c('1995-04-20','1995-04-20', 
> '1997-02-19', '1998-01-15','1998-01-15')),
>  marker = c(2,8,5,7,5),
>  group=rep(c("A","B"),c(3,2))
>  )
> First, I sort by key variables: id, marker
> DT[order(id,marker)]
>
> But afterwards I've tried different things and I'm not able to what I 
> want:
> DT[!duplicated(DT[c('id', 'datep')])]
> DT[ !(duplicated %chin% c('id','datep'))]
> DT[ !(duplicated %in% c('id','datep'))]
> DT[,!(duplicated(DT[c("id","datep")])), by=list(id,datep)]
> unique(DT[c('id','datep')])
> Please, does anyone know how to do it?
>
>
> _______________________________________________
> datatable-help mailing list
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help

​

-- 
Melanie BACOU
International Food Policy Research Institute
Snr. Program Manager, HarvestChoice
Work +1(202)862-5699
E-mail m.bacou at cgiar.org
Visit www.harvestchoice.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.r-forge.r-project.org/pipermail/datatable-help/attachments/20141022/9fef3fd9/attachment.html>