R which rows in a dataframe exist in another dataframe

Question

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?

If there are multiple matches then it should list the indexes of those too e.g. if row 2 is -1.0 0.0000000 then the result would be c(2,4,6). — locket, Jun 28 '22 at 10:34
Have a look at [match two data.frames based on multiple columns](https://stackoverflow.com/q/26596305/10488504). — GKi, Jun 28 '22 at 11:25

score 3 · Accepted Answer · answered Jun 28 '22 at 11:03

3

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")

answered Jun 28 '22 at 11:03

GKi

37,245
2
26
48

Will this be efficient for larger data structures as well? – mhovd Jun 28 '22 at 11:22
Using `paste` instead of `interaction` can be more efficient. – GKi Jun 28 '22 at 11:25

score 1 · Answer 2 · answered Jun 28 '22 at 10:36

1

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

answered Jun 28 '22 at 10:36

user2974951

9,535
1
17
24

score 0 · Answer 3 · 2022-06-28T10:59:12.940

0

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

edited Jun 28 '22 at 10:59

answered Jun 28 '22 at 10:41

Quinten · Answer 4 · 2022-06-28T10:45:18.160

0

Using the join.keys function from plyr:

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

Output:

[1] 4 6

edited Jun 28 '22 at 10:45

answered Jun 28 '22 at 10:41

Quinten

35,235
5
20
53

s_baldur · Answer 5 · 2022-06-28T11:18:44.417

0

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)
)

edited Jun 28 '22 at 11:18

answered Jun 28 '22 at 11:13

s_baldur

29,441
4
36
69

score 0 · Answer 6 · answered Jun 28 '22 at 11:13

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

R which rows in a dataframe exist in another dataframe

A

B

6 Answers6