我有一个数据帧("观测值"(,带有H:M
格式("时间"(的时间戳。在第二个数据帧("间隔"(中,我的时间范围由"From"和"Till"变量定义,也是H:M
格式。
我想计算每个区间内的观测值数量。我一直在使用data.table
的between
,当包含日期时,它一直没有任何问题。
但是,现在我只有时间戳,没有日期。这会导致在跨越午夜 (20:00 - 05:59
的间隔中发生的时间出现一些问题。这些时间不计入我尝试过的代码中。
下面的示例
interval.data <- data.frame(From = c("14:00", "20:00", "06:00"), Till = c("19:59", "05:59", "13:59"), stringsAsFactors = F)
observations <- data.frame(Time = c("14:32", "15:59", "16:32", "21:34", "03:32", "02:00", "00:00", "05:57", "19:32", "01:32", "02:22", "06:00", "07:50"), stringsAsFactors = F)
interval.data
# From Till
# 1: 14:00:00 19:59:00
# 2: 20:00:00 05:59:00 # <- interval including midnight
# 3: 06:00:00 13:59:00
observations
# Time
# 1: 14:32:00
# 2: 15:59:00
# 3: 16:32:00
# 4: 21:34:00 # Row 4-8 & 10-11 falls in 'midnight interval', but are not counted
# 5: 03:32:00 #
# 6: 02:00:00 #
# 7: 00:00:00 #
# 8: 05:57:00 #
# 9: 19:32:00
# 10: 01:32:00 #
# 11: 02:22:00 #
# 12: 06:00:00
# 13: 07:50:00
library(data.table)
library(plyr)
adply(interval.data, 1, function(x, y) sum(y[, 1] %between% c(x[1], x[2])), y = observations)
# From Till V1
# 1 14:00 19:59 4
# 2 20:00 05:59 0 # <- zero counts - wrong!
# 3 06:00 13:59 2
一种方法是在data.table
中使用非等值连接,它们的辅助函数as.ITime
处理时间字符串。
您将遇到跨越午夜的间隔问题,但是,应该只有一个。当您对每个"组"区间中的观测值数量感兴趣时,您可以将该组视为等效于其他组的"非"值。
例如,首先将data.frame
转换为data.table
library(data.table)
## set your data.frames as `data.table`
setDT(interval.data)
setDT(observations)
然后使用as.ITime
转换为时间的整数表示
## convert time stamps
interval.data[, `:=`(FromMins = as.ITime(From),
TillMins = as.ITime(Till))]
observations[, TimeMins := as.ITime(Time)]
## you could combine this step with the non-equi join directly, but I'm separating it for clarity
现在,您可以使用非 equi 联接来查找每次落入的间隔。注意到那些reutrn "NA"的时间实际上是那些落在午夜间隔内的时间
interval.data[
observations
, on = .(FromMins <= TimeMins, TillMins > TimeMins)
]
# From Till FromMins TillMins Time
# 1: 14:00 19:59 872 872 14:32
# 2: 14:00 19:59 959 959 15.59
# 3: 14:00 19:59 992 992 16:32
# 4: NA NA 1294 1294 21:34
# 5: NA NA 212 212 03:32
# 6: NA NA 120 120 02:00
# 7: NA NA 0 0 00:00
# 8: NA NA 357 357 05:57
# 9: 14:00 19:59 1172 1172 19:32
# 10: NA NA 92 92 01:32
# 11: NA NA 142 142 02:22
# 12: 06:00 13:59 360 360 06:00
# 13: 06:00 13:59 470 470 07:50
然后,要获得间隔组的观察者数量,您只需按每个时间点进行分组.N
,只需将其链接到上述语句的末尾即可
interval.data[
observations
, on = .(FromMins <= TimeMins, TillMins > TimeMins)
][
, .N
, by = .(From, Till)
]
# From Till N
# 1: 14:00 19:59 4
# 2: NA NA 7
# 3: 06:00 13:59 2
其中NA
组对应于跨越午夜的组
我只是调整了您的代码以获得所需的结果。希望这有帮助!
adply(interval.data, 1, function(x, y)
if(x[1] > x[2]) return(sum(y[, 1] %between% c(x[1], 23:59), y[, 1] %between% c(00:00, x[2]))) else return(sum(y[, 1] %between% c(x[1], x[2]))), y = observations)
输出为:
From Till V1
1 14:00 19:59 4
2 20:00 05:59 7
3 06:00 13:59 2