48

I have two data frames (A and B), both with a column 'C'. I want to check if values in column 'C' in data frame A exists in data frame B.

A = data.frame(C = c(1,2,3,4))
B = data.frame(C = c(1,3,4,7))
Henrik
  • 65,555
  • 14
  • 143
  • 159
user1631306
  • 4,350
  • 8
  • 39
  • 74

1 Answers1

114

Use %in% as follows

A$C %in% B$C

Which will tell you which values of column C of A are in B.

What is returned is a logical vector. In the specific case of your example, you get: 

A$C %in% B$C
# [1]  TRUE FALSE  TRUE  TRUE

Which you can use as an index to the rows of A or as an index to A$C to get the actual values: 

# as a row index
A[A$C %in% B$C,  ]  # note the comma to indicate we are indexing rows

# as an index to A$C
A$C[A$C %in% B$C]
[1] 1 3 4  # returns all values of A$C that are in B$C

We can negate it too:

A$C[!A$C %in% B$C]
[1] 2   # returns all values of A$C that are NOT in B$C


If you want to know if a specific value is in B$C, use the same function: 
  2 %in% B$C   # "is the value 2 in B$C ?"  
  # FALSE

  A$C[2] %in% B$C  # "is the 2nd element of A$C in B$C ?"  
  # FALSE
zx8754
  • 52,746
  • 12
  • 114
  • 209
Ricardo Saporta
  • 54,400
  • 17
  • 144
  • 178
  • The last expression would tell you whether the second value in A$C were in B$C. – IRTFM Dec 08 '12 at 06:24
  • 1
    Yes, absolutely. In the original example `A$C[2]` is `2`, hence the lack of clarity. I edited the comments in my example to hopefully clarify. – Ricardo Saporta Dec 08 '12 at 06:30
  • 1
    This is really useful; how might we extend this technique to search for a string within a given variable? Can we combine with `str_detect` ? – Ben Jul 25 '19 at 20:31