Subsetting data.table by 2nd column only of a 2 column key, using binary search not vector scan

by

Yes, you can pass all values to the first key value and subset with the specific value for the second key.

DT[J(unique(x), 25), nomatch=0]

If you need to subset by more than one value in the second key (e.g. the equivalent of DT[y %in% 25:24]), a more general solution is to use CJ

DT[CJ(unique(x), 25:24), nomatch=0]

Note that CJ by default sorts the columns and sets key to all the columns, which means the result would be sorted as well. If that’s not desirable, you should use sorted=FALSE

DT[CJ(unique(x), 25:24, sorted=FALSE), nomatch=0]

There’s also a feature request to add secondary keys to data.table in future. I believe the plan is to add a new function set2key.

FR#1007 Build in secondary keys

There is also merge, which has a method for data.table. It builds the secondary key inside it for you, so should be faster than base merge. See ?merge.data.table.

More Related Contents:

  1. Understanding exactly when a data.table is a reference to (vs a copy of) another data.table
  2. Extract row corresponding to minimum value of a variable by group
  3. Group by multiple columns and sum other multiple columns
  4. Left join using data.table
  5. Fastest way to add rows for missing time steps?
  6. How can one work fully generically in data.table in R with column names in variables
  7. Why is rbindlist “better” than rbind?
  8. Faster version of combn
  9. Pass column name in data.table using variable [duplicate]
  10. What does .SD stand for in data.table in R
  11. Apply multiple functions to multiple columns in data.table
  12. data.table – select first n rows within group [duplicate]
  13. subsetting a data.table using != excludes NA too
  14. Find the intersection of overlapping ranges in two tables using data.table function foverlaps
  15. Referring to data.table columns by names saved in variables
  16. Dynamic column names in data.table
  17. Merge rows in a dataframe where the rows are disjoint and contain NAs
  18. Calculate row-wise maximum
  19. Convert *some* column classes in data.table
  20. Long Numbers As A Character String
  21. Insert a row in a data.table
  22. rolling joins data.table in R
  23. R data.table apply function to rows using columns as arguments
  24. Efficiently merging two data frames on a non-trivial criteria
  25. Convert a data frame to a data.table without copy
  26. Row sums over columns with a certain pattern in their name
  27. data.table package in R 3.5 does not install
  28. paste two data.table columns
  29. Removing multiple columns from R data.table with parameter for columns to remove
  30. Fill option for fread

Leave a Comment