r - 如何使用将规则应用于非常见列的自定义函数合并两个数据集？

Question

我正在尝试合并两个不同大小的数据框，但由于数据的面板结构，我遇到了困难。

考虑下面的示例，其中“toy.left”是一个由三个变量组成的面板：一个坐标（“coord”）和一个在特定月份（“月”）分配给该坐标的名称（“名称”）。接下来，考虑“toy.right”，它由四个变量组成：名称（“name”）、该名称的任期开始（“tenure.start”）到该坐标的分配，以及他们任期的结束对于该坐标的分配（'tenure.end'）。

toy.left <- tribble(~month, ~coord, ~name, 
"2000-01-01", 1301, "Alpha", 
"2000-03-01", 1301, "Beta", 
"2000-06-01", 1302, "Charlie", 
"2000-09-01", 1303, "Delta", 
"2000-12-01", 1303, "Epsilon")

toy.right <- tribble(~name, ~coord, ~tenure.start, ~tenure.end, 
"Alpha", 1301, "2000-02-01", "2000-04-01", 
"Beta", 1301, "1999-11-01", "2000-04-01", 
"Charlie", 1302,  "2000-04-01", "2000-07-01", 
"Delta", 1303, "2000-08-01", "2000-10-01", 
"Epsilon", 1303, "2000-11-01", "2001-01-01", 
"Delta", 1303, "2002-01-01", "2004-01-01")

我想合并这两个数据集，但是有些规则使 dplyr 中的 merge() 变得困难。例如，我不能简单地使用 inner_join() 并按“名称”和“坐标”合并，因为这违反了数据的面板结构。如果我这样做，个人的任期不会与观察月份重叠（首先，请参见第 1 行和第 2 行，应该颠倒；其次，请参见第 4 行和第 5 行，其中 merge() 重复了月份观察，但是应该只包括第 4 行）。

toy.left %>% 
  inner_join(toy.right, by = c("name", "coord"))

*Output*

month       coord   name   tenure.start tenure.end 
2000-01-01  1301    Alpha   2000-02-01  2000-04-01
2000-03-01  1301    Beta    1999-11-01  2000-04-01
2000-06-01  1302    Charlie 2000-04-01  2000-07-01
2000-09-01  1303    Delta   2000-08-01  2000-10-01
2000-09-01  1303    Delta   2002-01-01  2004-01-01
2000-12-01  1303    Epsilon 2000-11-01  2001-01-01

为了解决这个问题，我可以按“名称”、“坐标”和“月份”合并数据，但我需要根据日期是否在“tenure.start”和“月份”之间进行条件合并任期结束。搜索后，我找不到将自定义规则应用于 dplyr 中的 merge() 的方法。

我知道自定义函数或循环可能是解决此问题的最佳方法，但我不确定从哪里开始。此外，原始数据集有超过 150 万个观测值，这可能会产生更多问题。

我欢迎你的建议！

标签： rfunctiondatemerge

Answer 1

（所有这一切都在转换month为-class 之后。）tenure.*Date

模糊连接

fuzzyjoin::fuzzy_inner_join(
  toy.left, toy.right,
  by=c("name", "coord", month="tenure.start", month="tenure.end"), 
  match_fun=list(`==`, `==`, `>=`, `<=`))
# # A tibble: 4 x 7
#   month      coord.x name.x  name.y  coord.y tenure.start tenure.end
#   <date>       <dbl> <chr>   <chr>     <dbl> <date>       <date>    
# 1 2000-03-01    1301 Beta    Beta       1301 1999-11-01   2000-04-01
# 2 2000-06-01    1302 Charlie Charlie    1302 2000-04-01   2000-07-01
# 3 2000-09-01    1303 Delta   Delta      1303 2000-08-01   2000-10-01
# 4 2000-12-01    1303 Epsilon Epsilon    1303 2000-11-01   2001-01-01

sqldf

sqldf::sqldf(
  "select tl.name, tl.coord, tl.month, tr.[tenure.start], tr.[tenure.end]
   from [toy.left] tl
     inner join [toy.right] tr on tl.name=tr.name and tl.coord=tr.coord
       and tl.month between tr.[tenure.start] and tr.[tenure.end]")
#      name coord      month tenure.start tenure.end
# 1    Beta  1301 2000-03-01   1999-11-01 2000-04-01
# 2 Charlie  1302 2000-06-01   2000-04-01 2000-07-01
# 3   Delta  1303 2000-09-01   2000-08-01 2000-10-01
# 4 Epsilon  1303 2000-12-01   2000-11-01 2001-01-01

（我使用[tenure.start]括号表示法来区分表标识符tl和列名tenure.start，在 SQL 中，列名中的点通常表示schema.tablename.columnname类似命名法。）

数据表

这会进行左连接，而不是其他类型。为了确定由于左而不是内部而应该删除的内容，我将在以下位置添加一列toy.left：

library(data.table)
setDT(toy.left)
setDT(toy.right)
toy.left[, val := 2]
toy.left[toy.right, on = .(name, coord, month >= tenure.start, month <= tenure.end)][ !is.na(val),]
#         month coord    name   val    month.1
#        <Date> <num>  <char> <num>     <Date>
# 1: 1999-11-01  1301    Beta     2 2000-04-01
# 2: 2000-04-01  1302 Charlie     2 2000-07-01
# 3: 2000-08-01  1303   Delta     2 2000-10-01
# 4: 2000-11-01  1303 Epsilon     2 2001-01-01

data.table有其重命名列的方式，因此请注意。当我不确定我知道命名将如何结束时，我经常复制周围的列，以便始终清晰......但我这样做的部分原因是懒于学习它如何确定结果名称。