基于准则的R合并

Question

我正在尝试合并两个数据集df1，df2。

我的第一个数据集(df1)中的数据如下所示

     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

这是我的第二个数据集(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)我试图按ID合并这两个数据集

2)除了合并by="Id“外，容量值还应基于df2$Effective_Dt和df2$Effct_End_Dt之间的df1$ServiceDate。

例如，预期的输出应该如下所示

     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

我可以按Id进行合并，但不确定如何包含第二个逻辑，包括基于日期约束的容量。任何帮助都是非常感谢的。谢谢。

Answer 1

使用dplyr，您可以执行一个简单的左联接，然后筛选出不需要的行和列.

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

请注意，最后三个Capacity数字与您的答案不同--根据您的数据，这似乎是错误的。

Answer 2

下面是一个使用模糊连接的想法：

library(fuzzyjoin)
library(dplyr)

首先将日期字符串转换为实际日期。

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

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

然后执行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

其他选项(在转换到日期之后)包括data.table中的非equi连接。

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

也可能是我不太熟悉的sqldf。

数据

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)