CODEWITHSUNDEEP
rdataframecomparerow
Nijat Guluzade
Nijat Guluzade

Reputation: 7

R - how to compare rows of two data frames and return the count of identical values for each case

I am new in R and need your help. I have two data frames: dat1 and dat2.

dat1 <- data.frame(X1 = c(9, 21, 30), X2 = c(3, 25, 47), X3 = c(13, 26, 51))
dat2 <- data.frame(X1 = c(3, 21, 30), X2 = c(7, 19, 47), X3 = c(13, 35, 51))

dat1

 X1 X2 X3
1  9  3 13
2 21 25 26
3 30 47 51

dat2

 X1 X2 X3
1  3  7 13
2 21 19 35
3 30 47 51

What I want is to compare the values in dat1's each row with the values in all dat2rows and return a statement or the number of matched values for each case. Something like this:

dat1 row 1 and dat2 row 1: 2 match
dat1 row 1 and dat2 row 2: 0 match
dat1 row 1 and dat2 row 3: 0 match
dat1 row 2 and dat2 row 1: 0 match
dat1 row 2 and dat2 row 2: 1 match
dat1 row 2 and dat2 row 3: 0 match
...

I hope you understood my idea. Statements don't have to be this long. I just want to learn how I can carry out such comparisons with two data frames.

Thank you!

Upvotes: 0

Views: 464

Answers (3)

Ian Campbell
Ian Campbell

Reputation: 24770

Here's a simple approach with expand.grid and apply that counts the number of matches, regardless of order, between rows of dat1 and dat2:

result <-
  apply(expand.grid(seq(1,nrow(dat1)),seq(1,nrow(dat2))), 1, 
        function(x){data.frame(dat1 = x[1], dat2 = x[2],
                    matches = (ncol(dat1) + ncol(dat2)) -
                      length(unique(c(dat1[x[1],],dat2[x[2],]))))
         })
result <- do.call(rbind,result)
result
#      dat1 dat2 matches
#Var1     1    1       2
#Var11    2    1       0
#Var12    3    1       0
#Var13    1    2       0
#Var14    2    2       1
#Var15    3    2       0
#Var16    1    3       0
#Var17    2    3       0
#Var18    3    3       3

Upvotes: 0

r2evans
r2evans

Reputation: 160407

If you can take the matrix format, then

myfun <- Vectorize(function(a, b) sum(dat1[a,] %in% dat2[b,]), vectorize.args = c("a", "b"))
outer(seq_len(nrow(dat1)), seq_len(nrow(dat2)), myfun)
#      [,1] [,2] [,3]
# [1,]    2    0    0
# [2,]    0    1    0
# [3,]    0    0    3

If you prefer the vertical nature:

eg <- expand.grid(a = seq_len(nrow(dat1)), b = seq_len(nrow(dat2)))
eg$in_common <- with(eg, myfun(a, b))
eg
#   a b in_common
# 1 1 1         2
# 2 2 1         0
# 3 3 1         0
# 4 1 2         0
# 5 2 2         1
# 6 3 2         0
# 7 1 3         0
# 8 2 3         0
# 9 3 3         3

Upvotes: 1

robintux
robintux

Reputation: 93

try the following code snippet:

for(I  in 1:3){
  for(J in 1:3){
    print(sum(dat1[I,] %in% dat2[J,]))
  }
}

Upvotes: 0

Related Questions