data.table – select first n rows within group [duplicate]

by

As an alternative:

dt[, .SD[1:3], cyl]

When you look at speed on the example dataset, the head method is on par with the .I method of @eddi. Comparing with the microbenchmark package:

microbenchmark(head = dt[, head(.SD, 3), cyl],
               SD = dt[, .SD[1:3], cyl], 
               I = dt[dt[, .I[1:3], cyl]$V1],
               times = 10, unit = "relative")

results in:

Unit: relative
 expr      min       lq     mean   median       uq       max neval cld
 head 1.000000 1.000000 1.000000 1.000000 1.000000 1.0000000    10  a 
   SD 2.156562 2.319538 2.306065 2.365190 2.318540 2.1908401    10   b
    I 1.001810 1.029511 1.007371 1.018514 1.016583 0.9442973    10  a

However, data.table is specifically designed for large datasets. So, running this comparison again:

# creating a 30 million dataset
largeDT <- dt[,.SD[sample(.N, 1e7, replace = TRUE)], cyl]
# running the benchmark on the large dataset
microbenchmark(head = largeDT[, head(.SD, 3), cyl],
               SD = largeDT[, .SD[1:3], cyl], 
               I = largeDT[largeDT[, .I[1:3], cyl]$V1],
               times = 10, unit = "relative")

results in:

Unit: relative
 expr      min       lq     mean   median       uq     max neval cld
 head 2.279753 2.194702 2.221330 2.177774 2.276986 2.33876    10   b
   SD 2.060959 2.187486 2.312009 2.236548 2.568240 2.55462    10   b
    I 1.000000 1.000000 1.000000 1.000000 1.000000 1.00000    10  a

Now the .I method is clearly the fastest one.

Update 2016-02-12:

With the most recent development version of the data.table package, the .I method still wins. Whether the .SD method or the head() method is faster seems to depend on the size of the dataset. Now the benchmark gives:

Unit: relative
 expr      min       lq     mean   median       uq      max neval cld
 head 2.093240 3.166974 3.473216 3.771612 4.136458 3.052213    10   b
   SD 1.840916 1.939864 2.658159 2.786055 3.112038 3.411113    10   b
    I 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000    10  a

However with a somewhat smaller dataset (but still quite big), the odds change:

largeDT2 <- dt[,.SD[sample(.N, 1e6, replace = TRUE)], cyl]

the benchmark is now slightly in favor of the head method over the .SD method:

Unit: relative
 expr      min       lq     mean   median       uq      max neval cld
 head 1.808732 1.917790 2.087754 1.902117 2.340030 2.441812    10   b
   SD 1.923151 1.937828 2.150168 2.040428 2.413649 2.436297    10   b
    I 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000    10  a

More Related Contents:

  1. data.table vs dplyr: can one do something well the other can’t or does poorly?
  2. How to delete a row by reference in data.table?
  3. Why does X[Y] join of data.tables not allow a full outer join, or a left join?
  4. dplyr mutate/replace several columns on a subset of rows
  5. Proper/fastest way to reshape a data.table
  6. Filtering out duplicated/non-unique rows in data.table
  7. Data.table meta-programming
  8. data.table with two string columns of set elements, extract unique rows with each row unsorted
  9. Evaluating both column name and the target value within `j` expression within `data.table`
  10. Fill in missing values by group in data.table
  11. dplyr on data.table, am I really using data.table?
  12. data.table and parallel computing
  13. Elegantly assigning multiple columns in data.table with lapply()
  14. Sort a data.table fast by Ascending/Descending order
  15. No RTools compatible with R version 3.5.0 was found
  16. Join R data.tables where key values are not exactly equal–combine rows with closest times
  17. how to show a legend on dual y-axis ggplot
  18. Non-equi join using data.table: column missing from the output
  19. Why is as.Date slow on a character vector?
  20. Cleaning `Inf` values from an R dataframe
  21. How to group data.table by multiple columns?
  22. can the value.var in dcast be a list or have multiple value variables?
  23. Apply a function to a subset of data.table columns, by column-indices instead of name
  24. Selecting a subset of columns in a data.table
  25. grouped operations that result in length not equal to 1 or length of group in dplyr
  26. Merge overlapping ranges into unique groups, in dataframe
  27. add missing rows to a data table
  28. assigning by reference into loaded package datasets
  29. Index unique values in data.table
  30. calculating sum of previous 3 rows in R data.table (by grid-square)

Leave a Comment