How to merge 2 dataframes with 4 columns each into a matrix with each cell returning the intersect

user_15244225 0
Viewed 35

I have 2 datasets and I want to compare each columns between the 2 datasets and get their intersects. how do i go about this? I have an example below to better explain what i wish to be returned. I am working with Rstudio.

dt1 =

col1 col2 col3 col4 col5 col6
dog rabbit bird pig dog bird
cat turtle cat rabbit rabbit dog
pig bird dog turtle turtle pig

dt2=

col1 col2 col3 col4 col5 col6
rabbit dog lion turtle fish bird
giraffe bird cat dog worm dog
pig cat pig rabbit dog pig

expected return matrix=

col1 col2 col3 col4 col5 col6
intersect(dt1col1,dt2col1) intersect(dt1col1,dt2col2) intersect(dt1col1,dt2col3) intersect(dt1col1,dt2col4) intersect(dt1col1,dt2col5) intersect(dt1col1,dt2col6)
intersect(dt1col2,dt2col1) intersect(dt1col2,dt2col2) intersect(dt1col2,dt2col3) intersect(dt1col2,dt2col4) intersect(dt1col2,dt2col5) intersect(dt1col2,dt2col6)
intersect(dt1col3,dt2col1) intersect(dt1col3,dt2col2) intersect(dt1col3,dt2col3) intersect(dt1col3,dt2col4) intersect(dt1col3,dt2col5) intersect(dt1col3,dt2col6)
intersect(dt1col4,dt2col1) intersect(dt1col4,dt2col2) intersect(dt1col4,dt2col3) intersect(dt1col4,dt2col4) intersect(dt1col4,dt2col5) intersect(dt1col4,dt2col6)
intersect(dt1col5,dt2col1) intersect(dt1col5,dt2col2) intersect(dt1col5,dt2col3) intersect(dt1col5,dt2col4) intersect(dt1col5,dt2col5) intersect(dt1col5,dt2col6)
intersect(dt1col6,dt2col1) intersect(dt1col6,dt2col2) intersect(dt1col6,dt2col3) intersect(dt1col6,dt2col4) intersect(dt1col6,dt2col5) intersect(dt1col6,dt2col6)
intersect r matrix
1 Answers

Try outer

> outer(dt1, dt2, FUN = Vectorize(function(x, y) toString(intersect(x, y))))
     col1          col2             col3       col4                  col5 
col1 "pig"         "dog, cat"       "cat, pig" "dog"                 "dog"
col2 "rabbit"      "bird"           ""         "rabbit, turtle"      ""
col3 ""            "bird, cat, dog" "cat"      "dog"                 "dog"
col4 "pig, rabbit" ""               "pig"      "rabbit, turtle"      ""
col5 "rabbit"      "dog"            ""         "dog, rabbit, turtle" "dog"
col6 "pig"         "bird, dog"      "pig"      "dog"                 "dog"
     col6
col1 "dog, pig"
col2 "bird"
col3 "bird, dog"
col4 "pig"
col5 "dog"
col6 "bird, dog, pig"
user_121587570
Related