Reputation: 125
I have the following data structure:
myDF <- data.frame(as.POSIXct(c("2010-02-16 12:45:37 CST",
"2010-02-16 13:22:23 CST",
"2010-02-16 13:49:47 CST",
"2010-02-16 14:23:13 CST",
"2010-02-16 16:29:17 CST",
"2010-02-16 16:49:26 CST")))
colnames(myDF) <- c("DateTimeArrival")
How do I get the number of events that happens between 12:00:00 to 12:59:59. I want the following result:
Time Number
12:59:59 1
13:59:59 1
14:59:59 1
15:59:59 0
16:59:59 2
Upvotes: 1
Views: 98
Reputation: 3728
Here's another solution using cut
and table
:
> as.data.frame(table(cut(myDF$DateTimeArrival+3600,
breaks=seq(as.POSIXct("2010-02-16 11:59:59 PST"),
by="1 hour", length.out=7))))
Var1 Freq
1 2010-02-16 11:59:59 0
2 2010-02-16 12:59:59 1
3 2010-02-16 13:59:59 2
4 2010-02-16 14:59:59 1
5 2010-02-16 15:59:59 0
6 2010-02-16 16:59:59 2
Since 11:59 to 12:59 was by default coded as 11:59, but you want it coded as 12:59 so I added 3600
(1 hr) to all the times.
Upvotes: 2
Reputation: 26258
We can also use foverlaps
from library(data.table)
library(data.table)
## create table of intervals
dt_intervals <- data.table(start_interval = seq(as.POSIXct("2010-02-16 11:00:00"), as.POSIXct("2010-02-16 17:00:00"), by="hour"),
end_interval = seq(as.POSIXct("2010-02-16 11:59:59"), as.POSIXct("2010-02-16 17:59:59"), by="hour"))
## set our df to a data.table
myDT <- data.table(myDF)
myDT[, DateTimeArrival_copy := DateTimeArrival]
setkey(dt_intervals, start_interval, end_interval)
setkey(myDT, DateTimeArrival, DateTimeArrival_copy)
foverlaps(dt_intervals,
myDT,
type="any")[, sum(!is.na(DateTimeArrival)), by=end_interval]
# end_interval V1
#1: 2010-02-16 11:59:59 0
#2: 2010-02-16 12:59:59 1
#3: 2010-02-16 13:59:59 2
#4: 2010-02-16 14:59:59 1
#5: 2010-02-16 15:59:59 0
#6: 2010-02-16 16:59:59 2
#7: 2010-02-16 17:59:59 0
Upvotes: 2
Reputation: 93938
Use trunc
to chop your date/times back to the hour, then aggregate
trtimes <- as.POSIXct(trunc(myDF$DateTimeArrival, units="hours")) + 3599
aggregate(
Count ~ Time,
merge(
list(Time=seq(min(trtimes), max(trtimes), by="hour")),
list(Time=trtimes, Count=1),
all.x=TRUE
),
FUN=sum,
na.rm=TRUE,
na.action=na.pass
)
# Time Count
#1 2010-02-16 12:59:59 1
#2 2010-02-16 13:59:59 2
#3 2010-02-16 14:59:59 1
#4 2010-02-16 15:59:59 0
#5 2010-02-16 16:59:59 2
Upvotes: 1