Why is allow.cartesian required at times when when joining data.tables with duplicate keys?

by

You don’t have to avoid duplicate keys. As long as the result does not get bigger than max(nrow(x), nrow(i)), you won’t get this error, even if you’ve duplicates. It is basically a precautionary measure.

When you’ve duplicate keys, the resulting join can sometimes get much bigger. Since data.table knows the total number of rows that’ll result from this join early enough, it provides this error message and asks you to use the argument allow.cartesian=TRUE if you’re really sure.

Here’s an (exaggerated) example that illustrates the idea behind this error message:

require(data.table)
DT1 <- data.table(x=rep(letters[1:2], c(1e2, 1e7)), 
                  y=1L, key="x")
DT2 <- data.table(x=rep("b", 3), key="x")

# not run
# DT1[DT2] ## error

dim(DT1[DT2, allow.cartesian=TRUE])
# [1] 30000000        2

The duplicates in DT2 resulted in 3 times the total number of “a” in DT1 (=1e7). Imagine if you performed the join with 1e4 values in DT2, the results would explode! To avoid this, there’s the allow.cartesian argument which by default is FALSE.

That being said, I think Matt once mentioned that it maybe possible to just provide the error in case of “large” joins (or joins that results in huge number of rows – which might be set arbitrarily I guess). This, when/if implemented, will make the join properly without this error message in case of joins that don’t combinatorially explode.

More Related Contents:

  1. Overlap join with start and end positions
  2. data.table vs dplyr: can one do something well the other can’t or does poorly?
  3. Why does data.table update names(DT) by reference, even if I assign to another variable?
  4. How to delete a row by reference in data.table?
  5. Why does X[Y] join of data.tables not allow a full outer join, or a left join?
  6. How to get week numbers from dates?
  7. dplyr mutate/replace several columns on a subset of rows
  8. Proper/fastest way to reshape a data.table
  9. Data.table meta-programming
  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. Join R data.tables where key values are not exactly equal–combine rows with closest times
  16. how to show a legend on dual y-axis ggplot
  17. Rolling Sum by Another Variable in R
  18. Why is as.Date slow on a character vector?
  19. How to group data.table by multiple columns?
  20. Apply a function to a subset of data.table columns, by column-indices instead of name
  21. Selecting a subset of columns in a data.table
  22. Merge overlapping ranges into unique groups, in dataframe
  23. add missing rows to a data table
  24. `dcast` with empty RHS [duplicate]
  25. How to select R data.table rows based on substring match (a la SQL like)
  26. Any way to force fread() of data.table not to stop on empty lines?
  27. R data table: update join
  28. Merging data.tables uses more than 10 GB RAM
  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