How to join tables with multiple primary keys in R (efficiently)?

user_12908701 0
Viewed 902

I have a little question about joins in dplyr package in R. I have 2 (big) dataframes, that I want to join. They have multiple columns in common, but one is enough to join them. For now I do something like this:

tab1 <- data.frame(id= c("a", "b", "c", "d"),
                   name = c("Mike", "Anna", "John", "Edward"),
                   score = c(10, 20, 30, 20)
tab2 <- data.frame(id= c("a", "b", "c", "d"),
                   name = c("Mike", "Anna", "John", "Edward"),
                   color = c("red", "blue", "blue", "orange")
dplyr::left_join(x, y)

This solution is ok, but you can see that the join is using id and name as keys, even if we do not need both. My worry, since I work with bigger dataframes and I have to do this with multiple iteration, is that using all the same-name columns would take useless time.

I can of course specify by = id, but then left_join will keep name.x AND name.y.

So I have 2 questions:

  1. Does a join with multiple keys (say 20 keys) takes more time than a join with one key?
  2. If the answer is yes, is there a simple mean to specify one key and drop the other duplicated columns from one table?

I hope my question is clear, do not hesitate to ask precisions, many thanks!

dplyr tidyverse r join
1 Answers

Concerning your second question. You can use suffix to add a suffix to the duplicated columns so that they can be easily dropped after the join.

To answer your first question we can benchmark the different options. As you are concerned with efficiency I also compare dplyr::left_join to a base R approach using merge which gives us four options

  1. left_join by all key columns
  2. left_join by only one key + getting rid of duplicates
  3. merge by all key columns
  4. merge by only one key + getting rid of duplicates

According to my benchmark on your example data, left_join by all keys is faster than joining by one and getting rid of duplicates afterwards. However, if you are concerned with efficiency than I would recommed to have a look at merge

library(dplyr)

tab1 <- data.frame(
  id = c("a", "b", "c", "d"),
  name = c("Mike", "Anna", "John", "Edward"),
  score = c(10, 20, 30, 20)
)
tab2 <- data.frame(
  id = c("a", "b", "c", "d"),
  name = c("Mike", "Anna", "John", "Edward"),
  color = c("red", "blue", "blue", "orange")
)
dplyr::left_join(tab1, tab2)
#> Joining, by = c("id", "name")
#>   id   name score  color
#> 1  a   Mike    10    red
#> 2  b   Anna    20   blue
#> 3  c   John    30   blue
#> 4  d Edward    20 orange

f1 <- function() left_join(tab1, tab2) 
f2 <- function() { 
  left_join(tab1, tab2, by = "id", suffix = c("", "_drop")) %>% 
    select(-ends_with("_drop")) 
}
f3 <- function() merge(tab1, tab2, by = c("id", "name"), all.x = TRUE)
f4 <- function() { 
  x <- merge(tab1, tab2, by = "id", all.x = TRUE, suffix = c("", "_drop"))
  x[, names(x)[!grepl("_drop$", names(x))]] 
}

microbenchmark::microbenchmark(f1(), f2(), f3(), f4())

#> Unit: microseconds
#>  expr      min        lq      mean   median       uq       max neval cld
#>  f1() 1574.201 1755.9010 2141.5060 1971.501 2303.651  6831.900   100  b 
#>  f2() 2861.602 3040.8505 3841.6990 3264.551 3920.701 17639.802   100   c
#>  f3()  471.801  527.6515  657.6511  588.501  702.851  2607.201   100 a  
#>  f4()  395.800  442.3005  583.3340  491.801  590.751  3824.800   100 a
user_129938610
Related