Group by and select min date with data.table

Question

My Data

df1 <- structure(list(ID = c("A", "A", "A", "B", "B", "C"), c1 = 1:6, 
c2 = 1:6, myDate = c("01.01.2015", "02.02.2014", "03.01.2014", 
"09.09.2009", "10.10.2010", "06.06.2011")), .Names = c("ID", 
"c1", "c2", "myDate"), class = "data.frame", row.names = c(NA,-6L))

My desired output (note: A df, keeping all columns!):

ID    c1    c2    myDate
A     3     3     03.01.2014
B     4     4     09.09.2009
C     6     6     06.06.2011
....

My Code

library(data.table)
setDT(df1)
df1[,myDate:=as.Date(myDate, "%d.%m.%Y")]
test2 <- df1[,.(myDate == min(myDate)), by = ID]

That gives me in my corresponding column (myDate) a logical where the condition matches. But, thats not df and all the other columns get lost. I am fairly new to the data.table package so any help would be appreciated.

Answer 1

We can use which.min to get the index and use .SD to get the Subset of Data.table.

setDT(df1)[, .SD[which.min(as.Date(myDate, '%d.%m.%Y'))], by = ID]
#   ID c1 c2     myDate
#1:  A  3  3 03.01.2014
#2:  B  4  4 09.09.2009
#3:  C  6  6 06.06.2011

Or if there are ties and we need all the min value rows, use ==

setDT(df1)[, {tmp <- as.Date(myDate, '%d.%m.%Y'); .SD[tmp==min(tmp)] }, ID]
#ID c1 c2     myDate
#1:  A  3  3 03.01.2014
#2:  B  4  4 09.09.2009
#3:  C  6  6 06.06.2011

Other option would be to get the row index (.I) and then subset. It would be fast

setDT(df1)[df1[, .I[which.min(as.Date(myDate, '%d.%m.%Y'))], ID]$V1]
# ID c1 c2     myDate
#1:  A  3  3 03.01.2014
#2:  B  4  4 09.09.2009
#3:  C  6  6 06.06.2011