Subsetting columns data.table using group by

Question

Is it possible to extract extra columns form a data.table while grouping it and selecting the minimum value?

DT <- data.table(events)
firstOrders <- as.data.frame(DT[,min(property_time),by=property_.uid])

In this example, the orderids (this is a column in the events-df) should be extracted, so the ordernumber where time is minimum in the userid-group.

akrun · Accepted Answer · 2015-09-04T09:15:49.283

3

I guess we want to get rows that have the min value of 'property_time', grouped by 'property_.uid'. In that case, we can use which.min to get the numeric index and use that to subset the dataset (.SD).

 DT[,.SD[which.min(property_time)],by=property_.uid]

A faster option would be to get the row index with .I and then subset the dataset

 i1 <- DT[,.I[which.min(property_time)],by=property_.uid]$V1
 DT1 <- DT[i1]

data

set.seed(25)
DT <- data.table(property_.uid=rep(1:3, each=3), 
   property_time=sample(1:15, 9, replace=TRUE), OtherCol=rnorm(9))

edited Sep 04 '15 at 09:15

answered Sep 04 '15 at 09:09

akrun

874,273
37
540
662

1

Thank you so much, the second method is indeed ways faster! – Matthias Adriaens Sep 04 '15 at 09:15
I have to wait for 6 minutes – Matthias Adriaens Sep 04 '15 at 09:17
@BobbiusMatthiAdriaans No problem. Thanks. – akrun Sep 04 '15 at 09:19
1

Source for the latter option (eddi's answer -- maybe this is a dupe?): http://stackoverflow.com/a/16574176/1191259 Worth keeping in mind that the `.SD` approach is in the process of being optimized (as tracked by checkmarks here): https://github.com/Rdatatable/data.table/issues/735 – Frank Sep 04 '15 at 12:22
@Frank It sure looks like a dupe. Feel free to close it as a dupe. Thanks for the github link. – akrun Sep 04 '15 at 12:30

Subsetting columns data.table using group by

1 Answers1

data