r merge based on criteria

Question

I am trying to merge two datasets df1, df2.

The data in my 1st dataset (df1) looks like this below

     Id       ServiceDate    
    234       2004-02-10
    234       2003-11-05
    234       2002-06-07
 117458       2002-03-14
 117458       2003-03-17
 117458       2004-07-05
2195623       2002-04-12
2195623       2002-08-15
2195623       2002-09-10

This is the data in my second dataset (df2)

     Id       Effective_Dt     Effct_End_Dt    Capacity    
    234       2004-01-01       2004-12-31      10
    234       2002-01-01       2003-12-31      17        
 117458       2000-03-14       2004-12-31      11
2195623       1995-04-01       2003-05-25      22
2195623       2003-05-26       2004-04-17      27 
2195623       2004-04-18       2004-12-31      25

1) I am trying to merge these two datasets by ID
2) Inaddition to merging by="Id" , the Capacity value should be based on the df1$ServiceDate between df2$Effective_Dt and df2$Effct_End_Dt.

For example, the expected output should look like this below

     Id       ServiceDate       Capacity 
    234       2004-02-10        10
    234       2003-11-05        17 
    234       2002-06-07        17
 117458       2002-03-14        11 
 117458       2003-03-17        11
 117458       2004-07-05        11
2195623       2002-04-12        22 
2195623       2003-08-15        27
2195623       2004-09-10        25

I can do a merge by Id but not sure how to include the second logic which is including the Capacity based on the date constraint. Any help is much appreciated. Thanks.

Possible duplicate of [overlap(intersect) time interval and xts](https://stackoverflow.com/questions/51880861/overlapintersect-time-interval-and-xts) — acylam, Aug 29 '18 at 18:22

missuse · Answer 1 · 2018-08-29T18:25:19.877

Here's an idea with fuzzy join:

library(fuzzyjoin)
library(dplyr)

First convert the date strings to actual dates

df2 %>%
  mutate(Effective_Dt = as.Date(Effective_Dt),
         Effct_End_Dt = as.Date(Effct_End_Dt)) -> df2

df1 %>%
  mutate(ServiceDate = as.Date(ServiceDate)) -> df1

Then perform a fuzzy_left_join of df2 to df1

df1 %>%
  fuzzy_left_join(df2,
                  by = c("Id" = "Id", 
                         "ServiceDate" = "Effective_Dt",
                         "ServiceDate" = "Effct_End_Dt"), #variables to join by
                  match_fun = list(`==`, `>=`, `<=`)) %>% #function to use for each pair of variables 
  select(c(1,2,6)) #select just needed variables


#output:
     Id.x ServiceDate Capacity
1     234  2004-02-10       10
2     234  2003-11-05       17
3     234  2002-06-07       17
4  117458  2002-03-14       11
5  117458  2003-03-17       11
6  117458  2004-07-05       11
7 2195623  2002-04-12       22
8 2195623  2002-08-15       22
9 2195623  2002-09-10       22

other options (after converting to date) include non-equi join in data.table

library(data.table)

setDT(df1)
setDT(df2)
df1[df2, on = .(Id = Id, ServiceDate >= Effective_Dt, ServiceDate <= Effct_End_Dt), nomatch = 0]

#output
        Id ServiceDate ServiceDate.1 Capacity
1:     234  2004-01-01    2004-12-31       10
2:     234  2002-01-01    2003-12-31       17
3:     234  2002-01-01    2003-12-31       17
4:  117458  2000-03-14    2004-12-31       11
5:  117458  2000-03-14    2004-12-31       11
6:  117458  2000-03-14    2004-12-31       11
7: 2195623  1995-04-01    2003-05-25       22
8: 2195623  1995-04-01    2003-05-25       22
9: 2195623  1995-04-01    2003-05-25       22

and probably sqldf which I am not all that familiar with.

data

df1 <- read.table(text="Id       ServiceDate    
234       2004-02-10
234       2003-11-05
234       2002-06-07
117458       2002-03-14
117458       2003-03-17
117458       2004-07-05
2195623       2002-04-12
2195623       2002-08-15
2195623       2002-09-10", header = TRUE)


df2 <- read.table(text="Id       Effective_Dt     Effct_End_Dt    Capacity    
234       2004-01-01       2004-12-31      10
234       2002-01-01       2003-12-31      17        
117458       2000-03-14       2004-12-31      11
2195623       1995-04-01       2003-05-25      22
2195623       2003-05-26       2004-04-17      27 
2195623       2004-04-18       2004-12-31      25", header = TRUE)

this works on a small dataset but my dataset has 5 million rows and it is taking for ever. Is there a faster version of this algo ? — bison2178, Aug 29 '18 at 19:15

score 0 · Accepted Answer · answered Aug 29 '18 at 18:40

Using dplyr you can do a simple left join and then filter out the rows and columns you don't need...

library(dplyr)
df1 %>% left_join(df2) %>% 
        filter(as.Date(ServiceDate)>=as.Date(Effective_Dt),
               as.Date(ServiceDate)<=as.Date(Effct_End_Dt)) %>% 
        select(-Effective_Dt,
               -Effct_End_Dt)

       Id ServiceDate Capacity
1     234  2004-02-10       10
2     234  2003-11-05       17
3     234  2002-06-07       17
4  117458  2002-03-14       11
5  117458  2003-03-17       11
6  117458  2004-07-05       11
7 2195623  2002-04-12       22
8 2195623  2002-08-15       22
9 2195623  2002-09-10       22

Note that the last three Capacity figures are different from your answer - which seems to be wrong based on your data.

that worked, there was an error in the select function but when i run without the select function the results were as expected. — bison2178, Aug 29 '18 at 20:38

r merge based on criteria

2 Answers2