r - Inner_join 有两个条件和区间内的区间条件
问题描述
尝试根据多个条件和时间间隔条件加入 2 个数据帧,如下例所示:
# two sample dataframes with time intervals
df1 <- data.frame(key1 = c("a", "b", "c", "d", "e"),
key2 = c(1:5),
time1 = as.POSIXct(hms::as.hms(c("00:00:15", "00:15:15", "00:30:15", "00:40:15", "01:10:15"))),
time2 = as.POSIXct(hms::as.hms(c("00:05:15", "00:20:15", "00:35:15", "00:45:15", "01:15:15")))) %>%
mutate(t1 = interval(time1, time2)) %>%
select(key1, key2, t1)
df2 <- data.frame(key1 = c("b", "c", "a", "e", "d"),
key2 = c(2, 6, 1, 8, 5),
sam1 = as.POSIXct(hms::as.hms(c("00:21:15", "00:31:15", "00:03:15", "01:20:15", "00:43:15"))),
sam2 = as.POSIXct(hms::as.hms(c("00:23:15", "00:34:15", "00:04:15", "01:25:15", "00:44:15")))) %>%
mutate(t2 = interval(sam1, sam2)) %>%
select(key1, key2, t2)
需要对应的第一件事是列key1
和key2
,可以通过以下方式完成(产生错误):
df <- inner_join(df1, df2, by = c("key1", "key2"))
但是在加入时还有一个条件需要检查,那就是间隔t2
是否在t1
. 我可以像这样手动执行此操作:
df$t2 %within% df$t1
我猜这个错误来自于以间隔加入数据帧,这可能不是正确的方法,这就是为什么会出现错误。
# desired dataframe
df <- data.frame(key1 = c("a", "b"), key2 = c(1,2), time_condition = c(TRUE, FALSE))
如果 t1 来自"00:00:15" to "00:05:15"
则对应的 t2"00:03:15" to "00:04:15"
将在间隔 t1 内。这将导致 time_condition 列,TRUE
如果 t2 在 t1 内,则为 FALSE,否则为 FALSE。
解决方案
使用 data.table,您可以在加入时执行操作。这是一个例子
library(data.table)
df2[df1, # left join
.(time_condition = sam1 > time1 & sam2 < time2), # condition while joining
on = .(key1, key2), # keys
by = .EACHI, # check condition per join
nomatch = 0L] # make it an inner join
# key1 key2 time_condition
# 1: a 1 TRUE
# 2: b 2 FALSE
# your data generated using data.table
df1 <- data.table(key1 = c("a", "b", "c", "d", "e"),
key2 = c(1:5),
time1 = as.ITime(c("00:00:15", "00:15:15", "00:30:15", "00:40:15", "01:10:15")),
time2 = as.ITime(c("00:05:15", "00:20:15", "00:35:15", "00:45:15", "01:15:15")))
df2 <- data.table(key1 = c("b", "c", "a", "e", "d"),
key2 = c(2, 6, 1, 8, 5),
sam1 = as.ITime(c("00:21:15", "00:31:15", "00:03:15", "01:20:15", "00:43:15")),
sam2 = as.ITime(c("00:23:15", "00:34:15", "00:04:15", "01:25:15", "00:44:15")))
推荐阅读
- python - 具有日期值的元素最大值
- c# - 我可以先使用 EF6 代码创建计算列吗
- javascript - 无法在 PWA 中使用移动浏览器访问移动摄像头和麦克风(react js)
- perl - perl - 如何为每个字节打印 utf8 代码点
- obiee - 无法在 OBIEE 12C 上打开与 SMTP 服务器的连接
- c# - 从 Excel 复制并粘贴到 web asp:GridView
- python - 如何将具有自定义 keras 层(.h5)的 keras 模型冻结到张量流图(.pb)?
- amazon-web-services - 如何调试失败的 NetCore AWS Elastic Beanstalk 部署?
- shiny - 具有默认选定值的 ShinyTree
- python - 如何为图像/图标小部件设置动画