CODEWITHSUNDEEP
rmergedata.table
Deb
Deb

Reputation: 539

Data merge with data.table for repeating unique values

I am trying two merge two columns in data table 'A' with another column in another data table 'B' which is the unique value of a column . I want to merge in such a way that for every unique combination of two variables in data table 'A' , we get all unique values of column in data table 'B' repeated.

I tried merge but it doesn't give me all the values.I also tried the automated recycling function in data.table but this also doesn't give me the result.

Input: data.table A

X Y

1 1

1 2

1 3

2 1

3 1

4 4

4 5

5 6

data.table B

Z

1

2

Expected output

X Y Z

1 1 1

1 1 2

1 2 1

1 2 2

1 3 1

1 3 2

2 1 1

2 1 2

3 1 1

3 1 2

4 4 1

4 4 2

4 5 1

4 5 2

5 6 1

5 6 2

Upvotes: 1

Views: 265

Answers (1)

akrun
akrun

Reputation: 886938

We can make use of crossing from tidyr

library(tidyr)
crossing(A, B)
#   X Y Z
#1  1 1 1
#2  1 1 2
#3  1 2 1
#4  1 2 2
#5  1 3 1
#6  1 3 2
#7  2 1 1
#8  2 1 2
#9  3 1 1
#10 3 1 2
#11 4 4 1
#12 4 4 2
#13 4 5 1
#14 4 5 2
#15 5 6 1
#16 5 6 2

Or with merge from base R, but the order will be slightly different

merge(A, B)

To get the correct order, replace the arguments in reverse and then order the columns

merge(B, A)[c(names(A), names(B))]

Upvotes: 2

Related Questions