Merging two datasets with duplicate key values

Question

I have two data tables x and y as shown below:

x
Sample  code    code_conn   pool_content
Sample1   A1        X1      ASD
Sample1   A1        X3      POS
Sample1   B1        Y1      QAW
Sample1   B1        Y2      QWP
Sample1   B1        Y3      SDE
Sample1   B1        Y4      LKS
Sample1   C1        Z2      JMP
Sample1   C1        Z3      MSK
Sample2   A1        X1      ASD
Sample2   A1        X2      POS
Sample2   B1        Y1      QAW
Sample2   B1        Y2      QWP
Sample2   B1        Y3      LKS
Sample2   B1        Y4      ASE
Sample2   B1        Y5      ASM
Sample2   C1        Z1      MSK
Sample2   D1        P1      EAX
Sample2   D1        P2      EBX
Sample2   D1        P3      ECX
Sample2   D1        P4      EDX
Sample2   D1        P5      MOV
Sample2   D1        P6      ALL

y
code    code_conn
A1      X1
A1      X2
A1      X3
B1      Y1
B1      Y2
B1      Y3
B1      Y4
B1      Y5
C1      Z1
C1      Z2
C1      Z3
C1      Z4
D1      P1
D1      P2
D1      P3
D1      P4
D1      P5
D1      P6

I want the output to be as follows:

Sample  code    code_conn   pool_content
Sample1 A1      X1          ASD
Sample1 A1      X2          …
Sample1 A1      X3          POS
Sample1 B1      Y1          QAW
Sample1 B1      Y2          QWP
Sample1 B1      Y3          SDE
Sample1 B1      Y4          LKS
Sample1 B1      Y5          …
Sample1 C1      Z2          JMP
Sample1 C1      Z3          MSK
Sample1 C1      Z1          …
Sample1 C1      Z4          …
Sample2 A1      X1          ASD
Sample2 A1      X2          POS
Sample2 A1      X3          …
Sample2 B1      Y1          QAW
Sample2 B1      Y2          QWP
Sample2 B1      Y3          LKS
Sample2 B1      Y4          ASE
Sample2 B1      Y5          ASM
Sample2 C1      Z1          MSK
Sample2 C1      Z2          …
Sample2 C1      Z3          …
Sample2 C1      Z4          …
Sample2 D1      P1          EAX
Sample2 D1      P2          EBX
Sample2 D1      P3          ECX
Sample2 D1      P4          EDX
Sample2 D1      P5          MOV
Sample2 D1      P6          ALL

So, what I basically want is, for every unique sample in x, for every group of code I want all the missing code_conn from table y in table x and their pool_content to be set to "...". Is there an memory efficient and fast way to do this. The code and code_conn can be any string (Not particularly a serial combination of alphabets and numbers). I've attached the code to generate the data below. Thank you very much.

x <- data.table("Sample" = c(rep("Sample1", 8), rep("Sample2", 14)), 
           "code" = c("A1", "A1", "B1", "B1", "B1", "B1", "C1", "C1", "A1", "A1", rep("B1", 5), "C1", "C1", rep("D1", 5)), 
           "code_conn" = c("X1","X3","Y1","Y2","Y3","Y4","Z2","Z3","X1","X2","Y1","Y2","Y3","Y4","Y5","Z1","P1","P2","P3","P4","P5","P6"), 
           "pool_content" = c("ASD","POS","QAW","QWP","SDE","LKS","JMP","MSK","ASD","POS","QAW","QWP","LKS","ASE","ASM","MSK","EAX","EBX","ECX","EDX","MOV","ALL"))

y <- data.table("code" = c(rep("A1", 3), rep("B1", 5), rep("C1", 4), rep("D1", 6)), 
                "code_conn" = c("X1","X2","X3","Y1","Y2","Y3","Y4","Y5","Z1","Z2","Z3","Z4","P1","P2","P3","P4","P5","P6"))

Answer 1

Maybe you can try the following base R solution (using merge())

z <- merge(x,cbind(Sample = rep(unique(x$Sample),each = nrow(y)),y),all = T)

such that

> z
    Sample code code_conn pool_content
1  Sample1   A1        X1          ASD
2  Sample1   A1        X2         <NA>
3  Sample1   A1        X3          POS
4  Sample1   B1        Y1          QAW
5  Sample1   B1        Y2          QWP
6  Sample1   B1        Y3          SDE
7  Sample1   B1        Y4          LKS
8  Sample1   B1        Y5         <NA>
9  Sample1   C1        Z1         <NA>
10 Sample1   C1        Z2          JMP
11 Sample1   C1        Z3          MSK
12 Sample1   C1        Z4         <NA>
13 Sample1   D1        P1         <NA>
14 Sample1   D1        P2         <NA>
15 Sample1   D1        P3         <NA>
16 Sample1   D1        P4         <NA>
17 Sample1   D1        P5         <NA>
18 Sample1   D1        P6         <NA>
19 Sample2   A1        X1          ASD
20 Sample2   A1        X2          POS
21 Sample2   A1        X3         <NA>
22 Sample2   B1        Y1          QAW
23 Sample2   B1        Y2          QWP
24 Sample2   B1        Y3          LKS
25 Sample2   B1        Y4          ASE
26 Sample2   B1        Y5          ASM
27 Sample2   C1        Z1          MSK
28 Sample2   C1        Z2         <NA>
29 Sample2   C1        Z3         <NA>
30 Sample2   C1        Z4         <NA>
31 Sample2   D1        P1          EAX
32 Sample2   D1        P2          EBX
33 Sample2   D1        P3          ECX
34 Sample2   D1        P4          EDX
35 Sample2   D1        P5          MOV
36 Sample2   D1        P6          ALL

Answer 2

I think dplyr::left_join by sample and code can help you. It will give you NULL for non existent values and will keep it the rows the same number as you have.