Non-equi join using data.table: column missing from the output

by

In data.table, joins of the form x[i] traditionally uses values from i but uses column names from x. Even though this is different from SQL which returns both, this default makes a lot of sense for equi joins since we are interested in all rows from i and if they match then both data.tables have equal values anyway, and if they don’t we need to keep those unmatched values from i in result.

But for non-equi joins, since the values might not match exactly, i.e., can fall within a range, there might be cases where we will have to return outputs similar to SQL (or identify such cases and return the result user expects, similar to the case of equi joins). This hasn’t been done yet, but I’ve placed a provision for it at the moment, which is to refer to the columns explicitly with a x. prefix. It is not convenient, I agree. Hopefully this’ll be taken care of automatically soon.

Here’s how to get your result using x. prefix.

ans <- DT2[DT1, .(CERT_NUMBER, FORENAME, SURNAME, x.START_DATE, x.EXPIRY_DATE, ID, MONTH), 
         on=.(FORENAME, SURNAME, START_DATE <= MONTH, EXPIRY_DATE >= MONTH)]

IIRC there’s an issue filed on the GitHub project page about this as well.

More Related Contents:

  1. Overlap join with start and end positions
  2. Left join using data.table
  3. Why does X[Y] join of data.tables not allow a full outer join, or a left join?
  4. data.table join then add columns to existing data.frame without re-copy
  5. R: data.table cross-join not working
  6. Rolling join on data.table with duplicate keys
  7. Update subset of data.table based on join
  8. Conditional binary join and update by reference using the data.table package
  9. rolling joins data.table in R
  10. Merging multiple data.tables
  11. Understanding exactly when a data.table is a reference to (vs a copy of) another data.table
  12. Extract row corresponding to minimum value of a variable by group
  13. Group by multiple columns and sum other multiple columns
  14. How can one work fully generically in data.table in R with column names in variables
  15. Faster version of combn
  16. Pass column name in data.table using variable [duplicate]
  17. What does .SD stand for in data.table in R
  18. data.table – select first n rows within group [duplicate]
  19. subsetting a data.table using != excludes NA too
  20. Using cbind on an arbitrarily long list of objects
  21. Dynamic column names in data.table
  22. Convert *some* column classes in data.table
  23. Insert a row in a data.table
  24. Identify records in data frame A not contained in data frame B [closed]
  25. Efficiently merging two data frames on a non-trivial criteria
  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