CODEWITHSUNDEEP
rdatetime
staove7
staove7

Reputation: 580

Counting number of observation within time interval

I have data with str of POSIXct. For each observation I want to count the number of observations which preceded this observation in time intervals of 6 and 24 hours (the time intervals need to be flexible).

Here some reproducible example:

Dat <- data.frame(x=1:5, y=as.POSIXct(c("2017-02-01 16:05:35", "2017-02-01 16:15:35", "2017-02-01 18:15:37", "2017-02-01 23:30:35", "2017-02-02 01:15:35")))

And I'd like to get the following output:

x v6 v24
1   --  --
2   1   1
3   2   2
4   1   3
5   1   4

Upvotes: 1

Views: 690

Answers (2)

Lorenzo Benassi
Lorenzo Benassi

Reputation: 621

You might try that way, with library(sqldf). I try to get you a simple example using your data:

library(sqldf)

Dat <- data.frame(x=1:5, y=c("2017-02-01 16:05:35", "2017-02-01 16:15:35", "2017-02-01 18:15:37", "2017-02-01 23:30:35", "2017-02-01 01:15:35"))

sqldf('select distinct count (x) as count, 
       x
       from Dat 
       where y between "2017-02-01 16:05:35" and "2017-02-01 18:15:35"
       group by x')

The output is as follows:

  count x
1     1 1
2     1 2

You get a count of how many x values ​​are in the range expressed by

where y between "2017-02-01 16:05:35" and "2017-02-01 18:15:35"

The extremes of the range are included in the count. Is the Correct Solution For your Problem?

Upvotes: 1

simone
simone

Reputation: 577

Probably more efficient ways exist, but this should work

Dat <- data.frame(x=1:5, y=as.POSIXct(c("2017-02-01 16:05:35", "2017-02-01 16:15:35", "2017-02-01 18:15:37", 
                                    "2017-02-01 23:30:35", "2017-02-01 01:15:35")))

Dat <- Dat[order(Dat[,"y"]),]

require(lubridate)

Dat[,"span6"] <- Dat[,"y"] - hours(6)
Dat[,"span24"] <- Dat[,"y"] - hours(24)

for(s in Dat[,"span6"]){
  Dat[which(Dat[,"span6"]==s),"v6"] <-
    nrow(Dat[which(Dat[,"y"]>=s & Dat[,"y"]<Dat[which(Dat[,"span6"]==s),"y"]),])
}
for(s in Dat[,"span24"]){
  Dat[which(Dat[,"span24"]==s),"v24"] <-
    nrow(Dat[which(Dat[,"y"]>=s & Dat[,"y"]<Dat[which(Dat[,"span24"]==s),"y"]),])
}
Dat <- Dat[,!(names(Dat) %in% c("span6","span24"))]

Upvotes: 1

Related Questions