CODEWITHSUNDEEP
rdate
LuisMoncayo
LuisMoncayo

Reputation: 125

"POSIXct" "POSIXt" Count the number of event in

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

Answers (3)

fishtank
fishtank

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

SymbolixAU
SymbolixAU

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

thelatemail
thelatemail

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

Related Questions