R: Joining Tibbles Using Derived Column Values

user_14637381 0
Viewed 87

Consider the following tibbles:

library(tidyverse)

tbl_base_ids = tibble(base_id = c("ABC", "ABCDEF", "ABCDEFGHI"), base_id_length = c(3, 6, 9), record_id_length = c(10, 12, 15))
tbl_records  = tibble(record_id = c("ABC1234567", "ABCDEF123456", "ABCDEFGHI123456"))

I'd like to join matching rows to produce a third tibble:

tbl_records_with_base 

  record_id
  base_id
  base_id_length
  record_id_length

As you can see, this is not a matter of joining one or more variables from each of the first two. This requires matching variable derivatives. In SQL, I'd do this:

  SELECT A.record_id, 
    B.base_id, 
    B.base_id_length, 
    B.record_id_length
  FROM tbl_records A 
    JOIN tbl_base_ids B 
      ON LENGTH(a.record_id) = B.record_id_length
        AND LEFT(a.record_id, B.base_id_length) = B.base_id

I've tried variations of dplyr joins and using the match function, to but to no avail. Can someone help? Thank you.

tibble dplyr derivative r join
2 Answers

You should come up with some logic to separate base_id from record_id. because joining only on record_id_length would not be enough. For this example we can get base_id if we remove all numbers from record_id. Based on your actual dataset you need to change this if needed.

Once we do that we can join tbl_records with tbl_base_ids by base_id and record_id_length.

library(dplyr)

tbl_records %>%
  mutate(base_id = sub('\\d+', '', record_id),
         record_id_length = nchar(record_id)) %>%
  inner_join(tbl_base_ids, by = c("base_id", "record_id_length")) -> result

result

#  record_id       base_id   record_id_length base_id_length
#  <chr>           <chr>                <dbl>          <dbl>
#1 ABC1234567      ABC                     10              3
#2 ABCDEF123456    ABCDEF                  12              6
#3 ABCDEFGHI123456 ABCDEFGHI               15              9
user_39629140

I suggest using the fuzzyjoin package.

library(dplyr)
library(fuzzyjoin)

tbl_base_ids %>%
  mutate(record_ptn = sprintf("^%s.{%i}$", base_id, pmax(0, record_id_length - base_id_length))) %>%
  regex_full_join(tbl_records, ., by = c("record_id" = "record_ptn"))
# # A tibble: 3 x 5
#   record_id       base_id   base_id_length record_id_length record_ptn     
#   <chr>           <chr>              <dbl>            <dbl> <chr>          
# 1 ABC1234567      ABC                    3               10 ^ABC.{7}$      
# 2 ABCDEF123456    ABCDEF                 6               12 ^ABCDEF.{6}$   
# 3 ABCDEFGHI123456 ABCDEFGHI              9               15 ^ABCDEFGHI.{6}$

A note about this: the order of tables matters, where the regex must reside on the RHS of the by= settings. For instance, this does not work if we reverse it:

tbl_base_ids %>%
  mutate(record_ptn = sprintf("^%s.{%i}$", base_id, pmax(0, record_id_length - base_id_length))) %>%
  regex_full_join(., tbl_records, by = c("record_ptn" = "record_id"))
# # A tibble: 6 x 5
#   base_id   base_id_length record_id_length record_ptn      record_id      
#   <chr>              <dbl>            <dbl> <chr>           <chr>          
# 1 ABC                    3               10 ^ABC.{7}$       <NA>           
# 2 ABCDEF                 6               12 ^ABCDEF.{6}$    <NA>           
# 3 ABCDEFGHI              9               15 ^ABCDEFGHI.{6}$ <NA>           
# 4 <NA>                  NA               NA <NA>            ABC1234567     
# 5 <NA>                  NA               NA <NA>            ABCDEF123456   
# 6 <NA>                  NA               NA <NA>            ABCDEFGHI123456
user_33582720
Related