Matching rows to subset by marching ID

Question

I have two data frames

df1 = data.frame( Id=c(1, 2, 3), address = c(3130, 5234, 1400))    
df2 = data.frame(Id=c(1,1,2, 3, 3), address = c(5300, 3130, 5430, 5500, 2410))

I'm trying to match each address in df1 against every address in df2 that has a matching Id, and return a True if it matched and a False if it didn't.

So the result would be TRUE, FALSE, FALSE

Possible duplicate of [Compare two data.frames to find the rows in data.frame 1 that are not present in data.frame 2](https://stackoverflow.com/questions/3171426/compare-two-data-frames-to-find-the-rows-in-data-frame-1-that-are-not-present-in) — Jared C, Nov 30 '18 at 16:33

d.b · Answer 1 · 2018-11-30T17:17:43.817

1

For both data.frame, combine the Id and address in each row together using paste and then check if the combinations from df1 are also present in the combinations from df2 using %in%

do.call(paste, df1[c("Id", "address")]) %in% do.call(paste, df2[c("Id", "address")])
#[1]  TRUE FALSE FALSE

OR

df1$Id %in% merge(df1, df2, by = c("Id", "address"))$Id
#[1]  TRUE FALSE FALSE

edited Nov 30 '18 at 17:17

answered Nov 30 '18 at 17:01

d.b

32,245
6
36
77

score 0 · Answer 2 · answered Nov 30 '18 at 16:39

0

sapply(1:nrow(df1), function(i) df1[i, 2] %in% df2[df2[,1] == df1[i, 1], 2])

Although this returns F, F, F correctly. I think you have mistook 3120 and 3130 as a match.

answered Nov 30 '18 at 16:39

Adam Waring

1,158
8
20

They only want to match when the id is the same. – Adam Waring Nov 30 '18 at 16:47

score 0 · Answer 3 · answered Nov 30 '18 at 17:31

Here's a solution using purrr:

  library(purrr)
  map2_df(df1, df2, ~ .x %in% .y) %>%
    reduce(~ .x == .y)
  #[1] TRUE FALSE FALSE

How it works:

The first function call, map2_df(df1, df2, ~ .x %in% .y), creates a dataframe that shares the same shape as df1 but is populated with booleans indicating shared membership between df1 and df2:

  # A tibble: 4 x 2
    Id    address
    <lgl> <lgl>  
  1 TRUE  TRUE   
  2 TRUE  FALSE  
  3 TRUE  FALSE

The second function call, reduce(~ .x == .y), collapses each row by equivalence and yields our final answer:

  #[1] TRUE FALSE FALSE

By performing a row-wise equivalence, we ensure that only values with a matching Id value may return TRUE.

Matching rows to subset by marching ID

3 Answers3