In R, find rows that partially match rows in another data frame

Question

I have the following two data frames:

> df1
# A tibble: 4 x 4
    x     y     z     w
  <dbl> <dbl> <dbl> <dbl>
    4     5     8     9
    4     6     7     4
    3     6     7    10
    8     2     8     9
> df2
# A tibble: 4 x 4
    x     y     z     w
  <dbl> <dbl> <dbl> <dbl>
    6     2     7     9
    2     6     7    10
    4     5     8    12
    4     5     8     3

I would like to know which rows in df2 have a match in df1, where a match means identity in at least n / 2 columns.

Thus, in this example, line 1 in df2 is a match for line 4 in df1 (columns 1 and 3), line 2 in df2 corresponds to line 2 in df1 on columns 2 and 3 and line 3 on columns 2,3, 4 and etc.

I also need to keep the location of the duplicate rows and columns on which they match.

For small datasets, I can replicate both datasets and subtract them and count zeros. However, I need a solution that will work on very large datasets (~ 20K rows).

Any ideas? A dplyr solution (not data.table) would be much appreciated.

Answer 1

, , , /.

df1 <- read.table(text =
             "x     y     z     w
              4     5     8     9
              4     6     7     4
              3     6     7    10
              8     2     8     9",
              header = T)

df2 <- read.table(text =
             "x     y     z     w
              6     2     7     9
              2     6     7    10
              4     5     8    12
              4     5     8     3",
              header = T)


library(dplyr)
library(tidyr)

gather. ( , ):

df1 <- df1 %>% 
  mutate(df1_id = row_number()) %>%
  gather(field, value, x:w) %>% 
  arrange(df1_id)

df2 <- df2 %>% 
  mutate(df2_id = row_number()) %>% 
  gather(field, value, x:w) %>% 
  arrange(df2_id)

/ inner_join. group filter, ,

df2 %>% 
  inner_join(df1, by = c('value', 'field')) %>%
  group_by(df2_id, df1_id) %>% 
  filter(n()>=2) %>%  # where 2 is the minimum number of matches
  arrange(df2_id, df1_id, value) %>% 
  select(df2_id, df1_id, field, value)

# A tibble: 13 x 4
# Groups:   df2_id, df1_id [5]
   df2_id df1_id field value
    <int>  <int> <chr> <int>
 1      1      4 y         2
 2      1      4 w         9
 3      2      2 y         6
 4      2      2 z         7
 5      2      3 y         6
 6      2      3 z         7
 7      2      3 w        10
 8      3      1 x         4
 9      3      1 y         5
10      3      1 z         8
11      4      1 x         4
12      4      1 y         5
13      4      1 z         8

, df2 1 4 df1 y w, df2 2 df1 2 y z, df2 2 df1 3 y, x w. df2 3 4 df1 1 x, y z.

arrange select .

Answer 2

? dplyr purrr, id.1/id.2 .1 .2 , . by. , inner_join -ing df2 df1, inner_join -ing id .

require(dplyr)
require(purrr)

df1 <- tibble(
  x = c(4, 4, 3, 8),
  y = c(5, 6, 6, 2),
  z = c(8, 7, 7, 8),
  w = c(9, 4, 10, 9)
)

df2 <- tibble(
  x = c(6, 2, 4, 4),
  y = c(2, 6, 5, 5),
  z = c(7, 7, 8, 8),
  w = c(9, 10, 12, 13)
)

df1 <- df1 %>%
  mutate(id.1 = 1:length(.)) %>%
  rename(
    x.1 = x,
    y.1 = y,
    z.1 = z,
    w.1 = w
  )

df2 <- df2 %>%
  mutate(id.2 = 1:length(.)) %>%
  rename(
    x.2 = x,
    y.2 = y,
    z.2 = z,
    w.2 = w
  )

inner_join_by <-
  list(
    c("x.1" = "x.2", "y.1" = "y.2"),
    c("x.1" = "x.2", "z.1" = "z.2"),
    c("x.1" = "x.2", "w.1" = "w.2"),
    c("y.1" = "y.2", "z.1" = "z.2"),
    c("y.1" = "y.2", "w.1" = "w.2"),
    c("z.1" = "z.2", "w.1" = "w.2")
  )

filtered <- inner_join_by %>%
  map_df(.f = ~inner_join(x = df1, y = df2, by = .x)) %>%
  select(id.1, id.2) %>%
  distinct()

Answer 3

apply :

apply(df1, 1, function(x)apply(df2,1,function(y)x==y))

#      [,1]  [,2]  [,3]  [,4]
# [1,] FALSE FALSE FALSE FALSE
# [2,] FALSE FALSE FALSE  TRUE
# [3,] FALSE  TRUE  TRUE FALSE
# [4,]  TRUE FALSE FALSE  TRUE
# [5,] FALSE FALSE FALSE FALSE
# [6,] FALSE  TRUE  TRUE FALSE
# [7,] FALSE  TRUE  TRUE FALSE
# [8,] FALSE FALSE  TRUE FALSE
# [9,]  TRUE  TRUE FALSE FALSE
# [10,]  TRUE FALSE FALSE FALSE
# [11,]  TRUE FALSE FALSE  TRUE
# [12,] FALSE FALSE FALSE FALSE
# [13,]  TRUE  TRUE FALSE FALSE
# [14,]  TRUE FALSE FALSE FALSE
# [15,]  TRUE FALSE FALSE  TRUE
# [16,] FALSE FALSE FALSE FALSE

Answer 4

(- ):

, :

fct <- function(x, dat){
  M1logical <- t(unlist(x) == t(dat))
  n <- which(rowSums(M1logical) > 1)
  if(length(n) > 0){
    return(n)
  }
  if(length(n) == 0){
    return(0)
  }
}

:

mylist <- rep(list(NA), nrow(df2))
for(k in 1:nrow(df2)){
  mylist[[k]] <- fct(df2[k,], df1)
}

I need my computer 23.14 seconds ( microbenchmark) to compute it with two data frames of size 20,000x4 each, see here for dummy data (about 45 seconds on an older device):

df1 <- data.frame(x=sample(1:20,20000, replace = T), y=sample(1:20,20000, replace = T), 
              z=sample(1:20,20000, replace = T), w=sample(1:20,20000, replace = T),
              stringsAsFactors = F)
df2 <- data.frame(x=sample(1:20,20000, replace = T), y=sample(1:20,20000, replace = T), 
              z=sample(1:20,20000, replace = T), w=sample(1:20,20000, replace = T),
              stringsAsFactors = F)