Left join columns order changed

[engineerchange]

Thank you for all the work put into dtplyr; it already is saving me loads of time in my day to day.

It appears that left_join causes columns to change order. Doesn't appear to be an issue with right_join or merge.

library(data.table)
library(dtplyr)
library(dplyr,warn.conflicts=FALSE)

packageVersion("data.table")
#> [1] '1.12.8'
packageVersion("dtplyr")
#> [1] '1.0.0.9000'

x1 <- data.frame(x = 1:3, y = 1)
x2 <- data.frame(x = 1:3, z = 2)

x1 %>%
  left_join(x2, by="x")
#>   x y z
#> 1 1 1 2
#> 2 2 1 2
#> 3 3 1 2

x1 %>%
  lazy_dt() %>%
  left_join(x2, by="x") %>%
  as_tibble()
#> # A tibble: 3 x 3
#>       x     z     y
#>   <int> <dbl> <dbl>
#> 1     1     2     1
#> 2     2     2     1
#> 3     3     2     1

^{Created on 2019-12-29 by the reprex package (v0.3.0)}

[hadley]

Somewhat more minimal reprex:

library(dtplyr)
library(dplyr, warn.conflicts=FALSE)

x1 <- data.frame(x = 1:3, y = 1)
x2 <- data.frame(x = 1:3, z = 2)

x1 %>%
  left_join(x2, by="x") %>% 
  names()
#> [1] "x" "y" "z"

x1 %>%
  lazy_dt() %>%
  left_join(x2, by="x") %>%
  collect() %>% 
  names()
#> [1] "x" "z" "y"

^{Created on 2021-01-25 by the reprex package (v0.3.0.9001)}

[hadley]

Hmmm, this is because of the way that data.table does joins, where the column names are the by vars, the y vars, then the x vars. Reordering the variables after the join would make the translation quite a bit more complicated, and I'm not sure if it's worth it.

[hadley]

I could just make this always return a merge() instead of trying to use the compact dt1[dt2] syntax. @MichaelChirico is this likely to have an performance implications?

[MichaelChirico]

merge.data.table always wraps [ IIRC. the main advantage of [ is the other things available, like merge+assign / merge+aggregate, non-equi-joins

…

On Tue, Jan 26, 2021, 2:29 PM Hadley Wickham ***@***.***> wrote: I could just make this always return a merge() instead of trying to use the compact dt1[dt2] syntax. @MichaelChirico <https://github.com/MichaelChirico> is this likely to have an performance implications? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#139 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB2BA5NTCA7C3JGFHMA2M2TS3463TANCNFSM4KAYSCRA> .

[hadley]

None of which have obvious dplyr translations, so I think it's probably fine to always use merge() in the interest of hewing closer to dplyr behaviour.

[MichaelChirico]

I agree it makes sense.

fwiw I think the issue could also be fixed with a call to setcolorderv(), if an approach with [ were to be revisited

[hadley]

Hmmm, that might be easier — I think I have the needed info to only do that in collect(), if there's a mismatch between dplyr and data.table orderings.