How to count the number of concurrent users using time interval data?

Question

I have a dataset representing data from a log file that shows users and the machine accepting the connection for the server. I have a connection start time (start of a variable) and end time (end of a variable) in a dataset:

tdata <- structure(list(username = structure(c(9L, 6L, 7L, 5L, 3L, 2L, 4L, 8L, 1L, 4L), .Label = c("ESSAA", "HBRTE", "HPAIUS", "KOLA", "MAITAEN", "MARKEA", "MIAINN", "MSALA", "PAREDT"), class = "factor"), machine = structure(c(3L, 2L, 4L, 8L, 1L, 5L, 9L, 6L, 7L, 9L), .Label = c("D5785.domain.com", "D5874.domain.com", "D5927.domain.com", "D6000.domain.com", "D6092.domain.com", "D6147.domain.com", "D6142.domain.com", "D6169.domain.com", "D6194.domain.com"), class = "factor"), start = structure(c(1322672567, 1322687984, 1322465646, 1322696883, 1322695042, 1322697073, 1322697547, 1322692794, 1322697694, 1322700934), tzone = "", class = c("POSIXct", "POSIXt")), end = structure(c(1322693766, 1322695797, 1322696945, 1322697004, 1322697284, 1322697303, 1322697781, 1322700307, 1322700667, 1322701224), tzone = "", class = c("POSIXct", "POSIXt"))), .Names = c("username", "machine", "start", "end"), row.names = c(NA, 10L), class = "data.frame") > tdata username machine start end 1 PAREDT D5927.domain.com 2011-11-30 19:02:47 2011-12-01 00:56:06 2 MARKEA D5874.domain.com 2011-11-30 23:19:44 2011-12-01 01:29:57 3 MIAINN D6000.domain.com 2011-11-28 09:34:06 2011-12-01 01:49:05 4 MAITAEN D6169.domain.com 2011-12-01 01:48:03 2011-12-01 01:50:04 5 HPAIUS D5785.domain.com 2011-12-01 01:17:22 2011-12-01 01:54:44 6 HBRTE D6092.domain.com 2011-12-01 01:51:13 2011-12-01 01:55:03 7 KOLA D6194.domain.com 2011-12-01 01:59:07 2011-12-01 02:03:01 8 MSALA D6147.domain.com 2011-12-01 00:39:54 2011-12-01 02:45:07 9 ESSAA D6142.domain.com 2011-12-01 02:01:34 2011-12-01 02:51:07 10 KOLA D6194.domain.com 2011-12-01 02:55:34 2011-12-01 03:00:24 > 

Now I would like to calculate the number of concurrent users per minute using the start and end times from the tdata . I got to this:

 #create dataset containing each minute from tdata start.min <- min(tdata$start, na.rm=T) end.max <- max(tdata$end, na.rm=T) tinterval <- seq.POSIXt(start.min, end.max, by = "mins") 

Any ideas how to continue the calculation?

Answer 1

Here is an example

 n <- sapply(tinterval, function(tt) sum(tdata$start <= tt & tt <= tdata$end)) 

then

 @> tail(data.frame(tinterval, n)) tinterval n 3922 2011-12-01 09:55:06 0 3923 2011-12-01 09:56:06 1 3924 2011-12-01 09:57:06 1 3925 2011-12-01 09:58:06 1 3926 2011-12-01 09:59:06 1 3927 2011-12-01 10:00:06 1 @> plot(tinterval, n, type = "l") 

pretty slow though ...

Answer 2

Just for kicks, here is the xts solution:

 library(xts) # create an empty xts object with the minute timestamps we're interested in out <- xts(,align.time(tinterval,60)) # loop over each user for(i in 1:NROW(tdata)) { # paste the start / end times into an xts-style range timeRange <- paste(format(tdata[i,c("start","end")]),collapse="/") # add the minute "by parameter" for timeBasedSeq timeRange <- paste(timeRange,"M",sep="/") # create the by-minute sequence and align to minutes to match "out" timeSeq <- align.time(timeBasedSeq(timeRange),60) # create xts object with "1" entries for times between start and end temp <- xts(rep(1,length(timeSeq)),timeSeq) # merge temp with out and fill non-matching timestamps with "0" out <- merge(out, temp, fill=0) } # add column names (if necessary) colnames(out) <- tdata[,1] # sum across rows (need xts constructor because rowSums returns a matrix) counts <- xts(rowSums(out),index(out))