时间戳

首页 > 解决方案 > 如何基于以字符串编写的相似但不准确的时间变量合并两个数据集。使用 R?

r - 如何基于以字符串编写的相似但不准确的时间变量合并两个数据集。使用 R?

问题描述

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

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))

标签： rmergestrptime

解决方案

我认为这个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))

推荐阅读