Data.Table Full Outer Join in R without key

Question

I have a data set as follows: dt1 =

customerid	value
1356	0.26
1298	0.38

dt2 =

project_yr	value
1	0.31
2	0.79

I want to merge/ join them to get the following output:

customerid	value	project_yr	value
1356	0.26	1	0.31
1356	0.26	2	0.79
1298	0.38	1	0.31
1298	0.38	2	0.79

Is the quickest way to replicate df1 (similar to this Repeat rows of a data.frame) and then merge the second one on?

Or is there a way to do it using the data.table merge function?

Thankyou!

score 2 · Accepted Answer · answered Feb 17 '22 at 12:21

2

What you are looking for is a cross join

setkey(dt1[,c(k=1,.SD)],k) [ dt2[,c(k=1,.SD)], allow.cartesian=TRUE ][,k:=NULL]

answered Feb 17 '22 at 12:21

Sweepy Dodo

1,761
9
15

Isn't there any easier way for cross join already? – jangorecki Feb 17 '22 at 21:09
`dt1[, as.list(dt2), names(dt1)]` Happy to learn other methods :) – Sweepy Dodo Feb 17 '22 at 21:14
Probably it wasn't yet merged to master branch and waits in open PRs then – jangorecki Feb 19 '22 at 14:00
Oh is there a cleaner x join syntax coming? Exciting. @jangorecki could we have the link to that GitHub page please? – Sweepy Dodo Feb 20 '22 at 02:43
1

https://github.com/Rdatatable/data.table/pull/4370 and there is also #4544 which plugs it into [ operator. – jangorecki Feb 20 '22 at 11:17

score 0 · Answer 2 · answered Feb 17 '22 at 12:17

0

dt1 <- tibble::tribble(
  ~customerid, ~value,
  1356L,   0.26,
  1298L,   0.38
)

dt2 <- tibble::tribble(
  ~project_yr, ~value,
  1L,   0.31,
  2L,   0.79
)

tidyr::expand_grid(dt1, dt2, .name_repair = "minimal")
#> # A tibble: 4 x 4
#>   customerid value project_yr value
#>        <int> <dbl>      <int> <dbl>
#> 1       1356  0.26          1  0.31
#> 2       1356  0.26          2  0.79
#> 3       1298  0.38          1  0.31
#> 4       1298  0.38          2  0.79

answered Feb 17 '22 at 12:17

danlooo

10,067
2
8
22

Thankyou! Is there a way to do this using data.table package tho? – tobes_b Feb 17 '22 at 12:33
Yes: function `data.table::CJ` – danlooo Feb 17 '22 at 12:36

Data.Table Full Outer Join in R without key

2 Answers2