R which rows in a dataframe exist in another dataframe-CodePudding

I have two dataframes A and B:

A

     x          y
1   0.0  0.0000000
2   0.5  0.8000000
3  -0.5  0.8000000
4  -1.0  0.0000000
5  -0.5 -0.8000000
6   0.5 -0.8000000
7   1.0  0.0000000
8   1.5  0.8000000

B

     x          y
1  -1.0  0.0000000
2   0.5 -0.8000000
3   3.0  0.0000000

I want to extract just the row indexes in A that exist in B so that the final result will be:

c(4,6)

How should I go about doing this?

CodePudding user response：

interaction could be used to use %in% on multiple columns.

which(interaction(A) %in% interaction(B))
#[1] 4 6

Data

A <- read.table(header=TRUE, text="     x          y
1   0.0  0.0000000
2   0.5  0.8000000
3  -0.5  0.8000000
4  -1.0  0.0000000
5  -0.5 -0.8000000
6   0.5 -0.8000000
7   1.0  0.0000000
8   1.5  0.8000000")

B <- read.table(header=TRUE, text="     x          y
1  -1.0  0.0000000
2   0.5 -0.8000000
3   3.0  0.0000000")

CodePudding user response：

Add another column to A which is just a sequence and then merge

> A$c=1:nrow(A)
> merge(A,B)$c
[1] 4 6

CodePudding user response：

Using the join.keys function from plyr:

library(plyr)
with(join.keys(A, B), which(x %in% y))

Output:

[1] 4 6

CodePudding user response：

One possible way is to count the number of times TRUE appears twice. If the columns get wider you can map over them.

which(` `(df1$x %in% df2$x, df1$y %in% df2$y) == 2)

4 6

CodePudding user response：

Using outer

which(rowSums(outer(1:nrow(A), 1:nrow(B), Vectorize(\(i, j) all(A[i, ] == B[j, ])))) == 1)
# [1] 4 6

or a for loop

r <- c()
for (j in seq_len(nrow(B))) {
  for (i in seq_len(nrow(A))) {
    if (all(A[i, ] == B[j, ])) r <- c(r, i)
  }
}
r
# [1] 4 6

CodePudding user response：

library(data.table)
setDT(A)
setDT(B)
A[fintersect(A, B), on = names(A), which = TRUE]
# [1] 4 6


library(dplyr)
A |> 
  mutate(row = row_number()) |>
  inner_join(B, by = names(A)) |>
  pull(row)
# [1] 4 6

Data

A = data.frame(
  x = c(0, 0.5, -0.5, -1, -0.5, 0.5, 1, 1.5), 
  y = c(0, 0.8, 0.8, 0, -0.8, -0.8, 0, 0.8))
B = data.frame(
  x = c(-1, 0.5, 3), y = c(0, -0.8, 0)
)