R: merge based on multiple conditions (with non-equal criteria)

user_5327559 0
Viewed 18912

I would like to merge 2 data frames based on multiple conditions.

DF1 <- data.frame("col1" = rep(c("A","B"), 18),
                  "col2" = rep(c("C","D","E"), 12),
                  "value"= (sample(1:100,36)),
                  "col4" = rep(NA,36))

DF2 <- data.frame("col1" = rep("A",6),
                  "col2" = rep(c("C","D"),3),
                  "data" = rep(c(1,3),3),
                  "min" = seq(0,59,by=10),
                  "max" = seq(10,69,by=10))


> DF1
   col1 col2 value col4
1     A    C    22   NA
2     B    D    58   NA
3     A    E    35   NA
4     B    C    86   NA
5     A    D    37   NA
6     B    E    16   NA
7     A    C    46   NA
8     B    D    23   NA
9     A    E    88   NA
10    B    C     3   NA
11    A    D    33   NA
12    B    E    25   NA
13    A    C    19   NA
14    B    D    24   NA
15    A    E     9   NA
16    B    C    76   NA
17    A    D    62   NA
18    B    E    68   NA
19    A    C    97   NA
20    B    D    43   NA
21    A    E     8   NA
22    B    C    84   NA
23    A    D    36   NA
24    B    E    20   NA
25    A    C    57   NA
26    B    D    99   NA
27    A    E    42   NA
28    B    C    64   NA
29    A    D    87   NA
30    B    E     1   NA
31    A    C    78   NA
32    B    D    34   NA
33    A    E    41   NA
34    B    C    32   NA
35    A    D    10   NA
36    B    E    72   NA

> DF2
  col1 col2 data min max
1    A    C    1   0  10
2    A    D    3  10  20
3    A    C    1  20  30
4    A    D    3  30  40
5    A    C    1  40  50
6    A    D    3  50  60

DF1 is the main table and DF2 is treated as a lookup table

If col1 and col2 of DF1 match that of DF2, and 'value' of DF1 is in between min and max of DF2, then column 'data' from DF2 will be added to DF1. If the conditions are not met, 'data' of DF1 will have value of NA.

Expected output (first 6 rows):

  col1 col2 value col4 data
1    A    C    22   NA    1
2    B    D    58   NA   NA
3    A    E    35   NA   NA
4    B    C    86   NA   NA
5    A    D    37   NA    3
6    B    E    16   NA   NA

I've tried using merge (to match col1 snd col2) then subset (to filter only rows that have value in between min and max) , but my goal is to maintain all the rows of DF1.

Anyone has an idea on this ?

dataframe r merge
5 Answers

Using my package safejoin which wraps fuzzyjoin functions, you can do : 

# devtools::install_github("moodymudskipper/safejoin")
library(safejoin)
debugonce(safe_left_join)

safe_left_join(DF1, DF2,  ~
                  X("col1") == Y("col1") & 
                  X("col2") == Y("col2") & 
                  X("value") >= Y("min") &
                  X("value") <= Y("max"),
               conflict = ~.x) %>% 
  head(15)
#    col1 col2 value col4 data min max
# 1     A    C    90   NA   NA  NA  NA
# 2     B    D    20   NA   NA  NA  NA
# 3     A    E     8   NA   NA  NA  NA
# 4     B    C    99   NA   NA  NA  NA
# 5     A    D    42   NA   NA  NA  NA
# 6     B    E    37   NA   NA  NA  NA
# 7     A    C    47   NA    1  40  50
# 8     B    D    61   NA   NA  NA  NA
# 9     A    E    55   NA   NA  NA  NA
# 10    B    C    11   NA   NA  NA  NA
# 11    A    D    81   NA   NA  NA  NA
# 12    B    E    48   NA   NA  NA  NA
# 13    A    C    77   NA   NA  NA  NA
# 14    B    D    58   NA   NA  NA  NA
# 15    A    E     3   NA   NA  NA  NA

The conflict argument here tells the function to return only the conflicted columns from the lhs (col1 and col2).

user_22704750

By using all.x=TRUE all rows of DF1 are kept then adjust condition in filter as follows:

iMed=merge(DF1,DF2,by.x=c('col1','col2'),by.y=c('col1','col2'),all.x=TRUE)
Res=iMed[is.na(iMed[,'min'])|is.na(iMed[,'max'])|(iMed[,'value']<=iMed[,'max'] & iMed[,'value']>=iMed[,'min'] ),]
user_54210900
Related