R - 使用键连接数据帧，然后使用近似日期

Question

问题

我正在尝试使用 3 个 ID 列(或 1 列，如果我将 3 个粘贴在一起)合并两个数据帧，其中一个是日期时间变量，并且两个数据帧之间的差异最多可达 1 秒。

背景

我从带有事务记录的库中提取了两个数据帧。出于某种原因， checkout 和 checkin 是分开记录的，没有唯一的“交易 ID”来匹配它们。我想匹配它们。 “ checkout ”数据框有每个 checkout 项目的记录，包括到期日(应归还项目的时间)。 “签到”数据框对 checkin 的每个项目都有一个记录，包括截止日期。不幸的是，由于两个原因，我很难将这些数据帧合并在一起:

没有唯一的事务 ID 来匹配这些表。 (为什么？我不知道。)

对于同一笔交易，每笔交易的“due_date”字段最多可以有 1 秒的差异。

由于due_date 的变化似乎是随机发生的，因此没有任何方法可以确定两个due_date 对于哪些记录相等或相差1 秒。否则，我可以减去(或增加)一秒钟使它们相等。

数据

这是我正在使用的数据示例:

library(dplyr)
library(lubridate)

check_in <- tribble(
  ~ patron_id, ~item_id, ~checked_in, ~due_date,
    "A", "Z", "2018-04-16 07:00:00", "2018-04-16 08:00:00",
    "A", "Y", "2018-04-17 07:30:01", "2018-04-17 08:30:01",
    "B", "X", "2018-04-17 07:00:01", "2018-04-17 08:00:01",
    "B", "Z", "2018-04-17 08:00:01", "2018-04-17 09:00:01",
    "B", "Z", "2018-04-09 09:00:01", "2018-04-09 10:00:01",
    "C", "V", "2018-04-09 09:00:01", "2018-04-09 10:00:01",
    "C", "X", "2018-04-09 09:00:01", "2018-04-09 10:00:01")

check_out <- tribble(
  ~ patron_id, ~item_id, ~checked_out, ~due_date,
    "A", "Z", "2018-04-16 06:00:00", "2018-04-16 08:00:01",
    "A", "Y", "2018-04-17 06:30:01", "2018-04-17 08:30:00",
    "B", "X", "2018-04-17 06:00:01", "2018-04-17 08:00:00",
    "B", "Z", "2018-04-17 07:00:01", "2018-04-17 09:00:00",
    "B", "Z", "2018-04-09 08:00:01", "2018-04-09 10:00:01",
    "C", "V", "2018-04-09 08:00:01", "2018-04-09 10:00:01",
    "C", "X", "2018-04-09 08:00:01", "2018-04-09 10:00:00")

check_in$due_date <- ymd_hms(check_in$due_date)
check_in$checked_in <- ymd_hms(check_in$checked_in)

check_out$due_date <- ymd_hms(check_out$due_date)
check_out$checked_out <- ymd_hms(check_out$checked_out)

读者 ID 是借书人的唯一 ID。项目 ID 是书的唯一 ID。 checkout 是指图书被 checkout 的时间。 Checked In 是书被 checkin 的时间。而Due Date 是书的到期时间。

对于此示例数据，我将所有到期日设为退房日期后 2 小时。我还使入住日期等于退房日期后 1 小时。

期望输出

我想从 check_in 数据框中获取“checked_in”变量，并将其与 check_out 数据框中的相应事务进行匹配。输出将是这样的，但可能带有某种生成的交易 ID:

desired_output <- tribble(
  ~patron_id, ~item_id, ~checked_out, ~checked_in, ~due_date,
    "A", "Z", "2018-04-16 06:00:00", "2018-04-16 07:00:00", "2018-04-16 08:00:01",
    "A", "Y", "2018-04-17 06:30:01", "2018-04-17 07:30:01", "2018-04-17 08:30:00",
    "B", "X", "2018-04-17 06:00:01", "2018-04-17 07:00:01", "2018-04-17 08:00:00",
    "B", "Z", "2018-04-17 07:00:01", "2018-04-17 08:00:01", "2018-04-17 09:00:00",
    "B", "Z", "2018-04-09 08:00:01", "2018-04-09 09:00:01", "2018-04-09 10:00:01",
    "C", "V", "2018-04-09 08:00:01", "2018-04-09 09:00:01", "2018-04-09 10:00:01",
    "C", "X", "2018-04-09 08:00:01", "2018-04-09 09:00:01", "2018-04-09 10:00:00")

我试过的

尝试 #1:

我已尝试有条件地合并，如 this 中所述帖子，有以下修改:

check_out <- check_out %>%
             mutate(transaction_id = paste(patron_id,"-",item_id,sep=""))
check_in <- check_in %>%
              mutate(transaction_id = paste(patron_id,"-",item_id,sep=""))

output <- merge(check_out, check_in, by="transaction_id")[abs(difftime(check_out$due_date, check_in$due_date, units = "secs"))<=1,]

但是此方法不处理相同的事务 ID(显然)并且创建的记录比实际数量多。

尝试 #2:

恢复到原始数据帧，我尝试了 this post 中的解决方案，有以下修改:

output <- cbind(check_out, check_in[ 
                  sapply(check_out$due_date, 
                    function(x) which.min(abs(difftime(x, check_in$due_date)))), ])

但是这种方法不考虑“交易 ID”，或者更确切地说，我用来创建某种唯一 ID 的两个关键变量。因此，输出错误。

其他不成功的尝试:

this article 中提到的模糊连接. (以及提到的其他基于 R 的解决方案。)

此 response ，它使用过滤。

不幸的是，我无法让这些工作。我对这些方法的工作方式没有信心，也没有产生我想要的结果。很可能是用户错误，因为其他人似乎能够使类似的事情发挥作用。

感谢

先谢谢你，如果你能帮助我。我倾向于使用 Tidyverse 提供的工具，但我也愿意使用其他工具和方法。我试图确保在寻找其他解决方案时我做了尽职调查，但是如果您发现我错过了一个重要的帖子，请将其标记为重复并按照我的方式发送该帖子。

如果我可以提供任何其他信息或澄清上述任何细节，请告诉我。

Answer 1

会用这个:

inner_join(check_in, check_out, by = c("patron_id", "item_id")) %>%
  filter(abs(difftime(due_date.y, due_date.x, units= "secs"))<=as.difftime(1, format = "%S", units = "secs"))

说明:简单连接 + 过滤时间差 <= 1 秒的行