问题
我正在尝试使用 3 个 ID 列(或者 1 列,如果我将 3 个粘贴在一起)合并两个数据帧,其中之一是日期时间变量,并且两个数据帧之间的变化最多为 1 秒。
背景
我有两个从带有交易记录的库中提取的数据帧。由于某种原因,签出和签入是分开记录的,没有唯一的“交易 ID”来匹配它们。我想匹配他们。 “签出”数据框包含已签出的每个项目的记录,包括到期日期(应归还项目的时间)。 “签入”数据框记录了签入的每个项目,包括截止日期。不幸的是,我很难将这些数据框合并在一起,原因有两个:
- 没有唯一的事务 ID 来匹配表。 (为什么?我不知道。)
- 对于同一交易,每笔交易的“due_date”字段最多可能相差一秒。
due_date 的变化看似随机发生,因此没有任何方法可以确定哪些记录的两个 due_date 相等或相差 1 秒。否则,我可以减去(或添加)一秒以使它们相等。
The Data
这是我正在使用的数据示例:
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。已签出是指图书被签出的时间。签入是指图书签入的时间。到期日期是指图书到期的时间。
对于此示例数据,我将所有截止日期设置为退房日期后 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 https://stackoverflow.com/a/42847041/9017311帖子,进行以下修改:
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:
恢复到原始数据帧,我尝试了解决方案这个帖子 https://stackoverflow.com/a/5426704/9017311,进行以下修改:
output <- cbind(check_out, check_in[
sapply(check_out$due_date,
function(x) which.min(abs(difftime(x, check_in$due_date)))), ])
但此方法不考虑“交易 ID”,或者更确切地说,不考虑我用来创建某种唯一 ID 的两个关键变量。因此,输出错误。
其他不成功的尝试:
- 模糊连接如中提到的本文 https://www.r-bloggers.com/in-between-a-rock-and-a-conditional-join/。 (以及提到的其他基于 R 的解决方案。)
- This response https://stackoverflow.com/a/37300633/9017311,它使用过滤。
不幸的是,我无法让这些发挥作用。我对这些方法的运作方式没有信心,而且它没有产生我想要的结果。很可能是用户错误,因为其他人似乎也能够实现类似的功能。
Thanks
如果您能帮助我,请先谢谢您。我倾向于使用 Tidyverse 提供的工具,但我也愿意使用其他工具和方法。我试图确保在寻找其他解决方案时尽职尽责,但如果您发现我错过了重要的帖子,请将其标记为重复并将该帖子发送给我。
如果我可以提供任何其他信息或澄清上述任何细节,请告诉我。