CODEWITHSUNDEEP
rdataframemerge
Carrol
Carrol

Reputation: 1285

R - Merging dataframes by two columns with additional "between values" condition?

I have two dataframes, as follows:

Dataframe A:

code1 code2 element1 from to
c1a    c2a     e1a     1    15
c1a    c2a     e1b     17   50
c1a    c2b     e1c     14   67
c1b    c2c     e1d     1    20
c1b    c2d     e1e    40   60

Dataframe B:

code1 code2 element2 number
c1a    c2a     e2a    7
c1a    c2a     e2b    10
c1a    c2a     e2c    35

I basically need to join them if from =< number <= to, to obtain something like:


RESULT DATAFRAME

(Fragment, I don't have enough mock up data. I want this merge for both full dataframes A and B).

code1 code2 element1 element2 from   to   number
c1a    c2a     e1a    e2a      1     15     7
c1a    c2a     e1a    e2b      1     15     10
c1a    c2a     e1b    e2c      17    50     35

I can do this with a for loop, and manually check, but I was wondering if there is a more "elegant" way of doing this?

Upvotes: 1

Views: 83

Answers (2)

HNSKD
HNSKD

Reputation: 1644

Here's one using fuzzyjoin::fuzzy_inner_join. I understand from your output that besides the criteria for from =< number <= to, you would like to join by code1 and code2.

  1. Join code1 and code2 by equality
  2. Join from to number by the first inequality, i.e. from <= number
  3. Join to to number by the second inequality, i.e. number <= to

The thing with fuzzy_join is that they output all columns in both dataframes.

-

library(fuzzyjoin)
fuzzy_inner_join(
      df_A, df_B,
      by = c(
        "code1" = "code1",
        "code2" = "code2",
        "from" = "number",
        "to" = "number"),
      match_fun = c(
        "code1" = function(l, r) l == r,
        "code2" = function(l, r) l == r,
        "from" = function(l, r) l <= r,
        "to" = function(l, r) r <= l))

# code1.x code2.x element1 from to code1.y code2.y element2 number
# 1     c1a     c2a      e1a    1 15     c1a     c2a      e2a      7
# 2     c1a     c2a      e1a    1 15     c1a     c2a      e2b     10
# 3     c1a     c2a      e1b   17 50     c1a     c2a      e2c     35

data

df_A <- structure(list(code1 = c("c1a", "c1a", "c1a", "c1b", "c1b"), 
    code2 = c("c2a", "c2a", "c2b", "c2c", "c2d"), element1 = c("e1a", 
    "e1b", "e1c", "e1d", "e1e"), from = c(1L, 17L, 14L, 1L, 40L
    ), to = c(15L, 50L, 67L, 20L, 60L)), class = "data.frame", row.names = c(NA, -5L))

df_B <- structure(list(code1 = c("c1a", "c1a", "c1a"), code2 = c("c2a", 
"c2a", "c2a"), element2 = c("e2a", "e2b", "e2c"), number = c(7L, 
10L, 35L)), class = "data.frame", row.names = c(NA, -3L))

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389235

You can join data and then filter the values which are in range.

You can do this in dplyr

library(dplyr)
left_join(B, A, by = c('code1', 'code2')) %>% 
    filter(number >= from & number <= to)

#  code1 code2 element2 number element1 from to
#1   c1a   c2a      e2a      7      e1a    1 15
#2   c1a   c2a      e2b     10      e1a    1 15
#3   c1a   c2a      e2c     35      e1b   17 50

Or in base R : 

subset(merge(B, A, by = c('code1', 'code2')), number >= from & number <= to)

Upvotes: 2

Related Questions