How can I select rows from table A whose id matches those from table B, but whose (non id) values are different?

Question

Consider these two data.tables, foo and bar.

foo <- data.table(id = c(1,2,3,4), f1 = c("a", "b", "c", "d"), f2 = c("a", "b", "c", "d"))
bar <- data.table(id = c(1,2,3,4), f1 = c("a", "a", "c", "d"), f2 = c("a", "b", "c", "e"))

foo
   id f1 f2
1:  1  a  a
2:  2  b  b
3:  3  c  c
4:  4  d  d

bar
   id f1 f2
1:  1  a  a
2:  2  a  b
3:  3  c  c
4:  4  d  e

I know that foo and bar have a 1-1 relationship.

I would like to select rows from bar such that the corresponding row in foo has different values. For example,

• id 1: the values of f1 and f2 are the same in foo and bar, so exclude this one
• id 2: the value of f1 has changed! include this in the result
• id 3: the values of f1 and f2 are the same in foo and bar, so exclude this one
• id 4: the value of f2 has changed! include this in the result

Expected Result

bar[c(2,4)]
   id f1 f2
1:  2  a  b
2:  4  d  e

What I tried

I thought a non-equi join would work great here.. Unfortunately, it seems the "not equals" operator isn't supported.?

foo[!bar, on = c("id=id", "f1!=f1", "f2!=f2")]
# Invalid operators !=,!=. Only allowed operators are ==<=<>=>.

foo[!bar, on = c("id=id", "f1<>f1", "f2<>f2")]
# Found more than one operator in one 'on' statement: f1<>f1. Please specify a single operator.

Answer 1

With data.table:

bar[foo,.SD[i.f1!=x.f1|i.f2!=x.f2],on="id"]

      id     f1     f2
   <num> <char> <char>
1:     2      a      b
2:     4      d      e

Answer 2

I think this is best (cleanest, but perhaps not fastest?):

bar[!foo, on=.(id,f1,f2)]

      id     f1     f2
   <num> <char> <char>
1:     2      a      b
2:     4      d      e

Answer 3

Benchmarking on a larger dataset with a few different data.table options. The mapply option works only if all.equal(foo$id, bar$id) (depends on exactly what is meant by "1-1 relationship").

library(data.table)
set.seed(123)

foo <- data.table(id = 1:1e5, f1 = 1:1e5, f2 = 1:1e5)
mix <- sample(1e5, 5e4)
bar <- copy(foo)[mix[1:25e3], f1 := 0L][mix[25001:5e4], f2 := 0L]

head(fsetdiff(bar, foo))
#>    id f1 f2
#> 1:  5  0  5
#> 2:  6  6  0
#> 3:  7  0  7
#> 4:  8  0  8
#> 5: 10  0 10
#> 6: 12 12  0

microbenchmark::microbenchmark(join = bar[foo,.SD[i.f1!=x.f1|i.f2!=x.f2],on="id"],
                               antijoin = bar[!foo, on=.(id,f1,f2)],
                               fsetdiff = fsetdiff(bar, foo),
                               duplicated = bar[(!duplicated(rbindlist(list(bar, foo)), fromLast = TRUE))[1:nrow(bar)]],
                               mapply = bar[rowSums(mapply(function(i) foo[[i]] != bar[[i]], 2:length(bar))) > 0,],
                               check = "equal")
#> Unit: milliseconds
#>        expr     min       lq      mean   median       uq     max neval
#>        join 12.2306 14.07125 15.133795 14.76330 15.96645 22.4534   100
#>    antijoin 16.2002 17.60420 19.234747 18.30230 19.44395 59.1581   100
#>    fsetdiff 20.1408 21.76150 23.080961 23.03760 23.73860 30.9594   100
#>  duplicated 17.8954 20.12690 21.673165 21.66795 22.79185 27.3250   100
#>      mapply  3.2440  3.56480  4.346703  3.87415  4.63610 10.2100   100

Answer 4

As other than data.table solution are also welcomed. Here is a tidyverse solution:

library(dplyr)
library(tidyr)

left_join(foo, bar, by="id") %>%
  group_by(id) %>% 
  mutate(identical = n_distinct(unlist(cur_data())) == 1) %>% 
  filter(identical == FALSE) %>% 
  select(id, f1=f1.y, f2=f2.y)

 Groups:   id [2]
     id f1    f2   
  <dbl> <chr> <chr>
1     2 a     b    
2     4 d     e