join each row to the whole second table in R dplyr

Question

I have two tables:

table 1:
|   | a  | b  |
|---|----|----|
| 1 | a1 | b1 |
| 2 | a2 | b2 |

and

table 2: 
|   | c  | d  |
|---|----|----|
| 1 | c1 | d1 |
| 2 | c2 | d2 |

I want to join them in a way that each row of table one bind column-wise with table two to get this result:

|   | a  | b  | c  | d  |
|---|----|----|----|----|
| 1 | a1 | b1 | c1 | d1 |
| 2 | a1 | b1 | c2 | d2 |
| 3 | a2 | b2 | c1 | d1 |
| 4 | a2 | b2 | c2 | d2 |

I feel like this is a duplicated question, but I could not find right wordings and search terms to find the answer.

I don't quite understand: where does row 1 come from: ` a1 | d1 | c1 | d1`? — TimTeaFan, Sep 17 '21 at 10:56
Sorry, still don't understand: `a1 | b1 | c1 | d1`. In table one there is no row with `a1 | b1`. Should it be created? — TimTeaFan, Sep 17 '21 at 10:59
Also where does a2 and a3 come from? They are neither in table one nor two. — deschen, Sep 17 '21 at 11:01
@TimTeaFan. They were typos. My original data is restricted so I have to make dummy ones. — Babak Fi Foo, Sep 17 '21 at 11:04
I think this post answers your question. https://stackoverflow.com/questions/43228379/cartesian-product-with-dplyr-r — Ronak Shah, Sep 17 '21 at 11:17

score 2 · Accepted Answer · answered Sep 17 '21 at 11:09

There is no need to join, we can use tidyr::expand_grid:

library(dplyr)
library(tidyr)

table1 <- tibble(a = c("a1", "a2"),
                 b = c("b1", "b2"))

table2 <- tibble(c = c("c1","c2"),
                 d = c("d1", "d2"))

expand_grid(table1, table2)
#> # A tibble: 4 x 4
#>   a     b     c     d    
#>   <chr> <chr> <chr> <chr>
#> 1 a1    b1    c1    d1   
#> 2 a1    b1    c2    d2   
#> 3 a2    b2    c1    d1   
#> 4 a2    b2    c2    d2

^{Created on 2021-09-17 by the reprex package (v2.0.1)}

score 0 · Answer 2 · answered Sep 17 '21 at 11:06

0

I found a crude answer:

table1$key <- 1
table2$key <- 1
result <- left_join(table1,table2, by="key") %>%
              select(-key)

Any better answers is much appreciated.

answered Sep 17 '21 at 11:06

Babak Fi Foo

926
7
17

join each row to the whole second table in R dplyr

2 Answers2