r - 在唯一 ID 和条件日期上加入数据框

Question

我有两个df我要加入

第一个是带有员工工作时间表的 df，以及时间表生效的相应日期(生效日期)

>df1 = schedule and effective date
ID      Date        Wrk_Schd
0001    8/16/2002   80.00
0001    2/27/2004   40.00
0001    2/1/2006    50.00
0001    7/1/2017    36.00

第二个是带有发薪期日期和实际工作时间的 df

>df2 = pay periods and actual hours
ID      Date        Wrk_Hrs
0001    9/9/2003    32.00
0001    10/8/2005   35.00   
0001    10/21/2006  35.00
0001    12/21/2007  35.00   
0001    9/9/2012    40.00
0001    10/9/2013   40.00
0001    12/9/2017   36.00
0001    12/21/2017  36.00

我将如何加入 ID 和日期，以便实际工作时间的 df 与适用生效日期的工作时间表相匹配？

请记住 df1 和 df2 中的日期并不完全相同。所以我正在寻找的解决方案将根据支付期是否在生效日期之后加入，条件是没有另一个可能适用的生效日期。

想要的结果如下

>df3 
ID      Date        Wrk_Hrs Wrk_Schd
0001    9/9/2003    32.00   80.00
0001    10/8/2005   35.00   40.00
0001    10/21/2006  35.00   50.00
0001    12/21/2007  35.00   50.00
0001    9/9/2012    40.00   50.00
0001    10/9/2013   40.00   50.00
0001    12/9/2017   36.00   36.00
0001    12/21/2017  36.00   36.00

Answer 1

一种可能的解决方案是使用 dplyr 和 sqldf。

# The data
df1 <- read.table(text = "ID      Date        Wrk_Schd
0001    08/16/2003   80.00
0001    02/27/2004   40.00
0001    02/01/2006    50.00
0001    07/01/2017    36.00", header = TRUE, stringsAsFactors = FALSE)

# Change Date column to date type
df1$Date <- as.Date(df1$Date, "%m/%d/%Y")

df2 <- read.table(text = "ID      Date        Wrk_Hrs
0001    09/09/2003    32.00
0001    10/08/2005   35.00   
0001    10/21/2006  35.00
0001    12/21/2007  35.00   
0001    09/09/2012    40.00
0001    10/09/2013   40.00
0001    12/09/2017   36.00
0001    12/21/2017  36.00", header = TRUE, stringsAsFactors = FALSE)

# Change Date column to date type 
df2$Date <- as.Date(df2$Date, "%m/%d/%Y")


library(dplyr)
library(sqldf)
# Use lead function to add a column that show previous day of the next
  schedule date
df1_Mod <- df1 %>%
  arrange(ID, Date) %>%
  group_by(ID) %>%
  mutate(End_Date = lead(Date) - 1)

df1_Mod
#  ID       Date Wrk_Schd   End_Date
#1  1 2003-08-16       80 2004-02-26
#2  1 2004-02-27       40 2006-01-31
#3  1 2006-02-01       50 2017-06-30
#4  1 2017-07-01       36       <NA>

#Join data.frames based on ID and Date between Date and End_Date

df3 <- sqldf("SELECT df2.ID, df2.Date, df2.Wrk_Hrs, df1_Mod.Wrk_Schd 
              FROM df2, df1_Mod
              WHERE df2.ID = df1_Mod.ID AND
              df2.Date >= df1_Mod.Date AND
             (df1_Mod.End_Date IS NULL OR df2.Date <= df1_Mod.End_Date)")

df3
#  ID       Date Wrk_Hrs Wrk_Schd
#1  1 2003-09-09      32       80
#2  1 2005-10-08      35       40
#3  1 2006-10-21      35       50
#4  1 2007-12-21      35       50
#5  1 2012-09-09      40       50
#6  1 2013-10-09      40       50
#7  1 2017-12-09      36       36
#8  1 2017-12-21      36       36