如何基于字符串中写入的相似但不精确的时间变量合并两个数据集.使用R

Question

我有两个数据集要合并，看起来像这个

df1
ID|date    |       time|
1 04/06/21   "05:02:06"
2 05/03/21   "04:12:11"
3 02/02/20   "03:02:10"
4 09/09/20   "09:12:14"
5 02/02/21   "15:18:20" 
6 04/04/21   "14:00:00"  
df2
2ID|date    |       time|
1 04/06/21   "05:12:06"
2 05/03/21   "04:08:11"
3 02/02/20   "03:09:10"
4 09/09/20   "09:12:14"
5 02/02/21   "15:18:20" 
6 04/04/21   "15:00:00"

通常情况下，如果我运行基于完全匹配的脚本

df3 <- df2 %>% left_join(df1, by=c("incident_date","incident_time"))

我会得到

ID|    date|time    |2ID
1  04/06/21 "05:02:06" 
2  05/03/21 "04:12:11"
3  02/02/20 "03:02:10"
4  09/09/20 "09:12:14" 4
5  02/02/21 "15:18:20" 5
6  12/14/22 "14:00:00"

注意，我只匹配四个变量中的两个，但我希望匹配四个时间相近的变量中的其余三个。我希望脚本在45分钟前做出让步或让步，最终看起来像这个

ID|    date|time    |2ID
1  04/06/21 "05:02:06" 1
2  05/03/21 "04:12:11" 2
3  02/02/20 "03:02:10" 3
4  09/09/20 "09:12:14" 4
5  02/02/21 "15:18:20" 5
6  12/14/22 "14:00:00"

我试图根据之前关于日期的堆栈溢出问题来做这样的事情，但没有成功。有人知道怎么做吗

来源：合并基于相似但不准确的日期

df3< - df1%>%
left_join(df2, by=c("incident_date"), suffix 
= c(".df1", ".df2")) %>%
filter(abs({strptime(df1$incident_time,format="%H:%M:%S")}.df1 - 
{strptime(df2$incident_time,format="%H:%M:%S")}.df2) <= strptime("00:45:00",format="%H:%M:%S))

Answer 1

我认为fuzzyjoin包最适合此功能。

我将向两个帧添加$tm(POSIXct(列，因为这是获得清晰的"；计算的差值"；(以秒为单位(。

df1$tm <- as.POSIXct(paste(df1$date, df1$time), format="%m/%d/%Y %H:%M:%S")
df2$tm <- as.POSIXct(paste(df2$date, df2$time), format="%m/%d/%Y %H:%M:%S")
fuzzyjoin::difference_left_join(df1, df2, by = "tm", max_dist = 45*60)
#   ID.x   date.x   time.x                tm.x ID.y   date.y   time.y                tm.y
# 1    1 04/06/21 05:02:06 0021-04-06 05:02:06    1 04/06/21 05:12:06 0021-04-06 05:12:06
# 2    2 05/03/21 04:12:11 0021-05-03 04:12:11    2 05/03/21 04:08:11 0021-05-03 04:08:11
# 3    3 02/02/20 03:02:10 0020-02-02 03:02:10    3 02/02/20 03:09:10 0020-02-02 03:09:10
# 4    4 09/09/20 09:12:14 0020-09-09 09:12:14    4 09/09/20 09:12:14 0020-09-09 09:12:14
# 5    5 02/02/21 15:18:20 0021-02-02 15:18:20    5 02/02/21 15:18:20 0021-02-02 15:18:20
# 6    6 04/04/21 14:00:00 0021-04-04 14:00:00   NA     <NA>     <NA>                <NA>

显然需要清理一堆名称，这个怎么样：

fuzzyjoin::difference_left_join(df1, df2[,c("ID","tm")], by = "tm", max_dist = 45*60) %>%
select(ID = ID.x, date, time, ID2 = ID.y)
#   ID     date     time ID2
# 1  1 04/06/21 05:02:06   1
# 2  2 05/03/21 04:12:11   2
# 3  3 02/02/20 03:02:10   3
# 4  4 09/09/20 09:12:14   4
# 5  5 02/02/21 15:18:20   5
# 6  6 04/04/21 14:00:00  NA

注意：可能会找到多个匹配项(如果多个事件发生在45分钟内(，因此您可能需要添加一个分组过滤器：

... %>%
group_by(ID.x) %>%
filter(which.min(abs(tm.x - tm.y)))

(需要在重命名和删除tm.*字段之前完成(

---

数据

df1 <- structure(list(ID = 1:6, date = c("04/06/21", "05/03/21", "02/02/20", "09/09/20", "02/02/21", "04/04/21"), time = c("05:02:06", "04:12:11", "03:02:10", "09:12:14", "15:18:20", "14:00:00")), class = "data.frame", row.names = c(NA, -6L))
df2 <- structure(list(ID = 1:6, date = c("04/06/21", "05/03/21", "02/02/20", "09/09/20", "02/02/21", "04/04/21"), time = c("05:12:06", "04:08:11", "03:09:10", "09:12:14", "15:18:20", "15:00:00")), class = "data.frame", row.names = c(NA, -6L))