基于匹配多个数据集中的多个条件和日期范围添加列

我一直在努力寻找解决这个问题的最佳方法。

为了概括这个问题并帮助其他可能需要执行类似任务的人，我试图找到将列添加到第三个数据集的最佳方法，该方法基于中间数据集中的匹配，并且属于第三数据集的日期范围。最终结果是将第三个数据集中的匹配值返回到第一个数据集中。

以下是示例数据帧的头部，以增加清晰度：

> head(SalesData, 10)
   sale_id sale_amt int_rate  sale_date sale_status
1        1     7000    10.71 2008-05-01  Fully Paid
2        2    10800    13.57 2009-11-01  Fully Paid
3        3     7500    10.08 2008-04-01  Fully Paid
4        4     3000    14.26 2009-09-01  Fully Paid
5        5     5600    14.96 2010-02-01 Charged Off
6        6     2800    11.49 2010-08-01  Fully Paid
7        7    10000     8.59 2009-10-01  Fully Paid
8        8    18000    10.39 2008-03-01  Fully Paid
9        9     5000    15.13 2008-04-01  Fully Paid
10      10     9600    12.29 2008-03-01  Fully Paid

> head(EmployeeSales, 10)
   sale_id empl_name empl_num
1        1    Dakota        4
2        2    Dakota        4
3        3      Kami        9
4        4      Adel        1
5        5      Adel        1
6        6     Farah        6
7        7      Kami        9
8        8      Kami        9
9        9       Ida        7
10      10      Kami        9

> head(EmployeeMap, 10)
   empl_num empl_name skill_lvl team start_date   end_date
1         1      Adel       Beg  Red 2007-06-01 2008-05-31
2         1      Adel       Int  Red 2008-06-01 2010-10-31
3         1      Adel       Adv  Red 2010-11-01 2999-12-12
4         2    Bailey       Beg Blue 2010-08-01 2011-04-30
5         2    Bailey       Beg  Red 2011-05-01 2999-12-12
6         3     Casey       Beg Blue 2010-08-01 2010-12-31
7         3     Casey       Int Blue 2011-01-01 2999-12-12
8         4    Dakota       Beg  Red 2007-06-01 2009-08-30
9         4    Dakota       Int  Red 2009-09-01 2010-08-30
10        4    Dakota       Adv  Red 2010-09-01 2011-08-30

所需的输出会将 EmployeeMap 中的 empl_num、sales_team 和 Skill_level 添加到每个 sale_id 的 SalesData 中。

在尝试概念化这些步骤时，这就是我的想法，但也许有更好的方法： 从 SalesData 中获取 sale_id，将其与 Employee Sales 中的 sale_id 进行匹配，并获取 empl_num。获取 empl_num 并将其与 Employee Map 中的 empl_num 进行匹配。现在我们需要从 SalesData 中取出 sale_date 并找出它属于“start_date, end_date”的范围。然后，我们将获取匹配的团队和技能水平，并将其添加到 SalesData。

见下表：

 > head(df2,10)
    sale_id sale_amt int_rate  sale_date sale_status empl_num  team skill_lvl
 1        1     7000    10.71 2008-05-01  Fully Paid        4   Red       Beg
 2        2    10800    13.57 2009-11-01  Fully Paid        4   Red       Int
 3        3     7500    10.08 2008-04-01  Fully Paid        9  Blue       Beg
 4        4     3000    14.26 2009-09-01  Fully Paid        1   Red       Int
 5        5     5600    14.96 2010-02-01 Charged Off        1   Red       Int
 6        6     2800    11.49 2010-08-01  Fully Paid        6   Red       Beg
 7        7    10000     8.59 2009-10-01  Fully Paid        9  Blue       Int
 8        8    18000    10.39 2008-03-01  Fully Paid        9  Blue       Beg
 9        9     5000    15.13 2008-04-01  Fully Paid        7  Blue       Beg
 10      10     9600    12.29 2008-03-01  Fully Paid        9  Blue       Int

对我来说，让这个问题变得复杂的是，在 EmployeeMap 中，start_date 和 end_date 告诉我们每个员工开始和结束属于特定技能水平和团队的日期。但每个员工都改变了技能水平和/或团队，因此每个员工都有多行。

例如，在 empl_id 1 的 EmployeeMap 中，我们可以看到 3 行告诉我们他们的开始日期和结束日期，而他们的技能级别 Beg、Int、Adv 都属于红队。但有些人（例如 empl_id 2）会在保持相同技能水平的情况下更换团队。其他人则改变技能水平和团队。

如果您对解决此问题的最佳方法有任何见解，我将不胜感激。

也许实现这一点的最简单方法是使用两个类似 SQL 的连接（我建议你给出一些像这样 https://en.wikipedia.org/wiki/Join_(SQL)如果您不熟悉连接/关系代数，请阅读）。

许多连接可以通过merge基础 R 和许多其他流行软件包中的函数（dplyr, data.table, sqldf，仅举几例）在连接操作中提供替代语法或扩展功能。

您的两个连接中的第一个（在SalesData and EmployeeSales）可以很容易地完成merge:

merge(SalesData, EmployeeSales, by = "sale_id")

#    sale_id sale_amt int_rate  sale_date sale_status empl_name empl_num
# 1        1     7000    10.71 2008-05-01  Fully Paid    Dakota        4
# 2        2    10800    13.57 2009-11-01  Fully Paid    Dakota        4
# 3        3     7500    10.08 2008-04-01  Fully Paid      Kami        9
# ...

然而，第二个连接更为复杂，因为它不是典型的连接等值连接 https://en.wikipedia.org/wiki/Join_(SQL)#Equi-join。相反，连接逻辑需要在以下位置查找行：EmployeeMap where start_date小于sale_date and end date大于它（除了相等条件empl_num).

幸运的是，前面提到的data.table https://rstudio-pubs-static.s3.amazonaws.com/52230_5ae0d25125b544caab32f75f0360e775.html包提供了应用所述逻辑的能力。

library(data.table)

# convert all three dataframes to data.table objects
setDT(SalesData) ; setDT(EmployeeSales) ; setDT(EmployeeMap)

EmployeeMap[SalesData[EmployeeSales[, c("sale_id","empl_num")],
                      on = "sale_id"], 
            on = .(empl_num, start_date <= sale_date, end_date >= sale_date)]

#    empl_num empl_name skill_lvl team start_date   end_date sale_id sale_amt int_rate sale_status
# 1:        4    Dakota       Beg  Red 2008-05-01 2008-05-01       1     7000    10.71  Fully Paid
# 2:        4    Dakota       Int  Red 2009-11-01 2009-11-01       2    10800    13.57  Fully Paid
# 3:        9        NA        NA   NA 2008-04-01 2008-04-01       3     7500    10.08  Fully Paid
# ...

请注意，为了进行比较，所有三个日期列都应该是日期类型，而不是字符串。另请注意，NA上面输出中的值是快照的结果EmployeeMap问题中提供，仅映射empl_num 1-4.

我还建议阅读以下答案这个问题 https://stackoverflow.com/questions/23934361/merge-2-dataframes-if-value-within-range有关如何在日期范围内加入的更多背景信息。