finding the same row in two matrices/data frames and remove then in one of the matrices/data frames in R

user_12141825 0
Viewed 74

Let say we have the following matrices or data frames dyad_1 and dyad_2:

dyads_0 <- gtools::permutations(n=16,v=c(1:16), r=2)

dyad_1<- NULL

for (i in c(1,3:16)){

dyads1 <-NULL
dyads1 <- gtools::permutations(n=4,v=c(i,17:19), r=2)
dyad_1 <- rbind(dyad_1,dyads1[-c(5,6,8,9,11,12),]) 
}

dyad_1<- rbind(dyads_0,dyad_1) 
dyad_2<- gtools::permutations(n=19,v=c(1:19), r=2)

now it is clear that dyad_1 is a subset of dyad_2. I want to remove those rows in dyad_2 which is similar to dyad_1. In other words, I want to extract dyad_1 from dyad_2. Could you please let me know if there is an efficient way to do that? Thanks!

update: The generalization of this is also very important for me: that is, now assume we have dyad_2 with four columns:

 dyad_2$V0 <- rep(0, nrow(dyad_2)) 
 dyad_2$V0 [120] <- 1 
 dyad_2$V4 <- rnorm(nrow(dyad_2)) .

Now my aim is to remove the complete rows of dyad_2 whose elements in the columns V1 and V2 are the same as rows in dyad_1.

As clarification a simple example comes as follows:

Assume dyad_2 is

V0  V1  V2 V4
0   1   2  0.1
0   1   3  0.2
1   2   1  0.15 
0   2   3  0.001
0   3   1   0
0   3   2   0.1
0   10  5   0.9

Assume dyad_1 is

V1 V2
1  2
2  3
3  2
10 5

the output should be as follows:

 V0  V1  V2  V4
 0   1   3  0.2
 1   2   1  0.15 
 0   3   1   0

This is just an example for clarification and differs from the abovementioned codes.

I would be very grateful if you could give me your solution as I got stuck already.

r
2 Answers

anti_join (https://dplyr.tidyverse.org/reference/filter-joins.html) is very helpful here - it is basically the opposite of a join - per the documentation, anti_join(x,y) return all rows from x without a match in y. So setting each to a data.frame and using dplyr's anti_join, we can do this in a few lines:

library(dplyr)
dyad_2 <- as.data.frame(dyad_2)
dyad_1 <- as.data.frame(dyad_1)
# filter out the columns that appear in both datasets
dyad_2 <- dplyr::anti_join(dyad_2, dyad_1, by = c("V1", "V2"))

Which results in:

> dyad_2
    V1 V2
1    1 17
2    1 18
3    1 19
4    2 17
5    2 18
...
98  19 14
99  19 15
100 19 16
101 19 17
102 19 18
user_128482560

You can paste the values in dyad_1 and dyad_2 rowwise and keep the rows which are not included.

val1 <- apply(dyad_1, 1, paste0, collapse = '-')
val2 <- apply(dyad_2, 1, paste0, collapse = '-')
result <- dyad_2[!val2 %in% val1, ]

We can also apply this approach for specific columns and not all columns.

val1 <- apply(dyad_1, 1, paste0, collapse = '-')
val2 <- apply(dyad_2[, colnames(dyad_1)], 1, paste0, collapse = '-')
dyad_2[!val2 %in% val1, ]

#  V0 V1 V2   V4
#2  0  1  3 0.20
#3  1  2  1 0.15
#5  0  3  1 0.00
user_39629140
Related