Count rows based on multiple consecutive time flows

Question

I have a large file of time-series data, which looks as follows. The dataset covers years, in increments of 15 minutes. A small subset looks like:

uniqueid     time
a            2014-04-30 23:30:00 
a            2014-04-30 23:45:00
a            2014-05-01 00:00:00
a            2014-05-01 00:15:00
a            2014-05-12 13:45:00
a            2014-05-12 14:00:00
b            2014-05-12 13:45:00
b            2014-05-12 14:00:00
b            2014-05-12 14:30:00

To reproduce above:

time<-c("2014-04-30 23:30:00","2014-04-30 23:45:00","2014-05-01 00:00:00","2014-05-01 00:15:00",
    "2014-05-12 13:45:00","2014-05-12 14:00:00","2014-05-12 13:45:00","2014-05-12 14:00:00",
    "2014-05-12 14:30:00")

uniqueid<-c("a","a","a","a","a","a","b","b","b")
mydf<-data.frame(uniqueid,time)

My goal is to count the number of rows per unique id, per consecutive timeflow. A consecutive timespan is when a unique id is stamped for each 15 minutes in a row (such as id A, which is stamped from 30.04.14 23.30 hrs until 01.05.14 00.15 hrs - hence 4 rows), yet when this flow of 15-minute iterations is disrupted (after 01.05.14 00:15, it is not stamped at 01.05.14 00:30 hence it is disrupted), it should count the next timestamp as start of a new consecutive timeflow and again calculate the number of rows until this flow is disrupted again. Time is POSIX.

As you can see in above example; a consecutive timeflow may cover different days, different months, or different years. I have many unique ids (and as said, a very large file), so I'm looking for a way that my computer can handle (loops probably wouldn't work).

I am looking for output something like:

uniqueid    flow     number_rows
a           1        4
a           2        2
b           3        2
b           4        1

I have looked into some time packages (such as lubridate), but given my limited R knowledge, I don't even know where to begin.

I hope all is clear - if not, I'd be happy to try to clarify it further. Thank you very much in advance!

Answer 1

Another way to do this with data.table also using a time difference would be to make use of the data.table internal values for group number and number of rows in each group:

library(data.table)
res<-setDT(mydf)[, list(number_rows=.N,flow=.GRP),
                 by=.(uniqueid,cumsum(as.numeric(difftime(time,shift(time,1L,type="lag",fill=0))) - 15))][,cumsum:=NULL]
print(res)

   uniqueid number_rows flow
1:        a           4    1
2:        a           2    2
3:        b           2    3
4:        b           1    4

Also since the sample data you posted didn't align with the subset you posted, I have included my data below:

Data

time<-as.POSIXct(c("2014-04-30 23:30:00","2014-04-30 23:45:00","2014-05-01 00:00:00","2014-05-01 00:15:00",
        "2014-05-12 13:45:00","2014-05-12 14:00:00","2014-05-12 13:45:00","2014-05-12 14:00:00",
        "2014-05-12 14:30:00"))


uniqueid<-c("a","a","a","a","a","a","b","b","b")
mydf<-data.frame(uniqueid,time)