Sort a data.table fast by Ascending/Descending order

by

Update June 5 2014:

The current development version of data.table v1.9.3 has two new functions implemented, namely: setorder and setorderv, which does exactly what you require. These functions reorder the data.table by reference with the option to choose either ascending or descending order on each column to order by. Check out ?setorder for more info.

In addition, DT[order(.)] is also by default optimised to use data.table‘s internal fast order instead of base:::order. This, unlike setorder, will make an entire copy of the data, and is therefore less memory efficient, but will still be orders of magnitude faster than operating using base’s order.

Benchmarks:

Here’s an illustration on the speed differences using setorder, data.table’s internal fast order and with base:::order:

require(data.table) ## 1.9.3
set.seed(1L)
DT <- data.table(Year     = sample(1950:2000, 3e6, TRUE), 
                 memberID = sample(paste0("V", 1:1e4), 3e6, TRUE), 
                 month    = sample(12, 3e6, TRUE))

## using base:::order
system.time(ans1 <- DT[base:::order(Year, memberID, -month)])
#   user  system elapsed 
# 76.909   0.262  81.266 

## optimised to use data.table's fast order
system.time(ans2 <- DT[order(Year, memberID, -month)])
#   user  system elapsed 
#  0.985   0.030   1.027

## reorders by reference
system.time(setorder(DT, Year, memberID, -month))
#   user  system elapsed 
#  0.585   0.013   0.600 

## or alternatively
## setorderv(DT, c("Year", "memberID", "month"), c(1,1,-1))

## are they equal?
identical(ans2, DT)    # [1] TRUE
identical(ans1, ans2)  # [1] TRUE

On this data, benchmarks indicate that data.table’s order is about ~79x faster than base:::order and setorder is ~135x faster than base:::order here.

data.table always sorts/orders in C-locale. If you should require to order in another locale, only then do you need to resort to using DT[base:::order(.)].

All these new optimisations and functions together constitute FR #2405. bit64::integer64 support also has been added.

NOTE: Please refer to the history/revisions for earlier answer and updates.

More Related Contents:

  1. Fastest way to replace NAs in a large data.table
  2. .EACHI in data.table?
  3. How to speed up subset by groups
  4. Efficiently computing a linear combination of data.table columns
  5. Reason behind speed of fread in data.table package in R
  6. Naming columns in a data table in R
  7. Does ifelse really calculate both of its vectors every time? Is it slow?
  8. What is the purpose of setting a key in data.table?
  9. Use a value from the previous row in an R data.table calculation
  10. Faster way to read fixed-width files
  11. Melt using patterns when variable names contain string information – avoid coercion to numeric
  12. Error: package or namespace load failed for ggplot2 and for data.table
  13. How to perform join over date ranges using data.table?
  14. Split text string in a data.table columns
  15. Update subset of data.table based on join
  16. Practical limits of R data frame
  17. Fast Levenshtein distance in R?
  18. How can I prevent rbind() from geting really slow as dataframe grows larger?
  19. What are the performance differences between for-loops and the apply family of functions?
  20. Subsetting data.table using variables with same name as column
  21. Extract a column from a data.table as a vector, by position
  22. best way to transpose data.table
  23. := (pass by reference) operator in the data.table package modifies another data table object simultaneously
  24. Select NA in a data.table in R
  25. Rowwise cumulative sum
  26. How to select R data.table rows based on substring match (a la SQL like)
  27. Any way to force fread() of data.table not to stop on empty lines?
  28. R data table: update join
  29. Split a string column into several dummy variables
  30. R fast single item lookup from list vs data.table vs hash

Leave a Comment