No `by` argument when merging lists of data.tables

[popovs]

Hello, I'd like to request the incorporation of a by column when merging lists of data.tables. Here is a minimum reproducible example of the desired behavior:

dt1 <- data.table::data.table(idcol = c(1:10),
                              spp = c("a", "b", "a", "b", "c", "c", "d", "a", "d", "c"),
                              value = sample(1:100, 10))
dt2 <- data.table::data.table(idcol = c(1:10),
                              temp_c = sample(5:15, 10),
                              elevation_m = sample(0:500, 10))
dt3 <- data.table::data.table(idcol = c(1, 5, 2, 6, 8),
                              tree_spp = c("fir", "pine", "pine", "fir", "cedar"))

# Option 1 - Correct
# This works fine - we get the expected output
dt.x <- merge(dt1, dt2)
dt.y <- merge(dt.x, dt3, all = TRUE)

dt.y # All records merged, we've got NAs for cases where no tree_spp was recorded for a given record

# Option 2 - Incorrect
# This succeeds, but we get incorrect merging, as it recycles missing values
some_dts <- list(dt2, dt3)
merge(dt1, some_dts, by = "idcol") # Incorrect output - the tree_spp col recycles values instead of NAs - not merging by "idcol"

# Option 3 - Incorrect
# This also fails :( we get the recycling error
all_dts <- list(dt1, dt2, dt3)
data.table::cbindlist(all_dts) # Fails with error

# Ideal situation:
# merge(dt.x, list_of_dts, by = "idcol") # == dt.y
# OR
# data.table::cbindlist(all_dts, by = "idcol") # == dt.y

Thank you to those who maintain this excellent package.

EDIT: In meantime, I have discovered this solution, so perhaps this is low priority:

dt.y <- purrr::reduce(all_dts, merge, by = "idcol", all.x = TRUE)

I will leave this up in case anyone has a similar issue down the line.

[jangorecki]

Try mergelist function

[trobx]

TL;DR

1. Use mergelist here - it's what Jan has written it for (and your "ideal situation" is a reality)
2. There is nothing "incorrect" going on above (just a misconception about what merge does)
3. In other contexts, if you are using merge instead of native joins, have a look at fjoin - it is merge.data.table on steroids (also works on non-data.tables) and is very user-friendly and rich in features
4. But the key point here is 1!!!

You have:

merge(dt1, some_dts, by = "idcol")`

where:

some_dts <- list(dt2, dt3)

?merge

y is coerced to a data.table if it isn't one already.

The coercion is is done by as.data.table. And:

?as.data.table

If a list is supplied, each element is converted to a column in the data.table with shorter elements recycled automatically.

Actually what is happening is that each column of dt2 is recycled - but the point is the same. There is nothing incorrect - data.table is doing what it promises to do. y becomes:

as.data.table(some_dts)
#>     idcol temp_c elevation_m idcol.1 tree_spp
#>     <int>  <int>       <int>   <num>   <char>
#>  1:     1      6         488       1      fir
#>  2:     2     15         251       5     pine
#>  3:     3      8         337       2     pine
#>  4:     4      5          79       6      fir
#>  5:     5      7         442       8    cedar
#>  6:     6     12         355       1      fir
#>  7:     7     13          27       5     pine
#>  8:     8     11         210       2     pine
#>  9:     9     10         146       6      fir
#> 10:    10     14          63       8    cedar

Instead:

mergelist(list(dt1, dt2, dt3), on="idcol", how=list("inner", "full"))
#>     idcol    spp value temp_c elevation_m tree_spp
#>     <int> <char> <int>  <int>       <int>   <char>
#>  1:     1      a     7     14         492      fir
#>  2:     2      b    12      5         444     pine
#>  3:     3      a    74     10          26     <NA>
#>  4:     4      b    61     13         295     <NA>
#>  5:     5      c    29     15          63     pine
#>  6:     6      c    87      8         500      fir
#>  7:     7      d     4     12         435     <NA>
#>  8:     8      a    51     11         481    cedar
#>  9:     9      d    23      9         248     <NA>
#> 10:    10      c    49      7         213     <NA>

Boom!

[trobx]

@jangorecki @MichaelChirico I noticed that mergelist shipped with the "join_from"/"join_to" documentation still unfixed. I actually spent a lot of time over the winter break coming up with a proposed version that I think really works but I haven't got round to sending it to you. Let me raise a PR now. I accidentally deleted the last one (sorry).