Why does X[Y] join of data.tables not allow a full outer join, or a left join?

by

To quote from the data.table FAQ 1.11 What is the difference between X[Y] and merge(X, Y)?

X[Y] is a join, looking up X’s rows using Y (or Y’s key if it has one) as an index.

Y[X] is a join, looking up Y’s rows using X (or X’s key if it has one)

merge(X,Y) does both ways at the same time. The number of rows of X[Y] and Y[X] usually differ, whereas the number of rows returned by merge(X,Y) and merge(Y,X) is the same.

BUT that misses the main point. Most tasks require something to be done on the
data after a join or merge. Why merge all the columns of data, only to
use a small subset of them afterwards? You may suggest
merge(X[,ColsNeeded1],Y[,ColsNeeded2]), but that requires the programmer to work out which columns are needed. X[Y,j] in data.table does all that in one step for
you. When you write X[Y,sum(foo*bar)], data.table automatically inspects the j expression to see which columns it uses. It will only subset those columns only; the others are ignored. Memory is only created for the columns the j uses, and Y columns enjoy standard R recycling rules within the context of each group. Let’s say foo is in X, and bar is in Y (along with 20 other columns in Y). Isn’t X[Y,sum(foo*bar)] quicker to program and quicker to run than a merge of everything wastefully followed by a subset?

If you want a left outer join of X[Y] 

le <- Y[X]
mallx <- merge(X, Y, all.x = T)
# the column order is different so change to be the same as `merge`
setcolorder(le, names(mallx))
identical(le, mallx)
# [1] TRUE

If you want a full outer join

# the unique values for the keys over both data sets
unique_keys <- unique(c(X[,t], Y[,t]))
Y[X[J(unique_keys)]]
##   t  b  a
## 1: 1 NA  1
## 2: 2 NA  4
## 3: 3  9  9
## 4: 4 16 16
## 5: 5 25 NA
## 6: 6 36 NA

# The following will give the same with the column order X,Y
X[Y[J(unique_keys)]]

More Related Contents:

  1. Overlap join with start and end positions
  2. Left join using data.table
  3. data.table join then add columns to existing data.frame without re-copy
  4. R: data.table cross-join not working
  5. Rolling join on data.table with duplicate keys
  6. Update subset of data.table based on join
  7. Non-equi join using data.table: column missing from the output
  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. Why does data.table update names(DT) by reference, even if I assign to another variable?
  12. Use a value from the previous row in an R data.table calculation
  13. How to efficiently calculate distance between pair of coordinates using data.table :=
  14. Sample random rows within each group in a data.table
  15. Faster way to read fixed-width files
  16. Error: package or namespace load failed for ggplot2 and for data.table
  17. No RTools compatible with R version 3.5.0 was found
  18. knitr gets tricked by data.table `:=` assignment
  19. Rolling Sum by Another Variable in R
  20. Add multiple columns to R data.table in one function call?
  21. Why does merge result in more rows than original data?
  22. Use of lapply .SD in data.table R
  23. Cleaning `Inf` values from an R dataframe
  24. can the value.var in dcast be a list or have multiple value variables?
  25. data.table row-wise sum, mean, min, max like dplyr?
  26. grouped operations that result in length not equal to 1 or length of group in dplyr
  27. `dcast` with empty RHS [duplicate]
  28. Any way to force fread() of data.table not to stop on empty lines?
  29. Index unique values in data.table
  30. R fast single item lookup from list vs data.table vs hash

Leave a Comment