Calculating the number of simultaneously overlapping spells per time period (minute)

[Tobias Hagen]

Dear readers of the Statalist,

I have (spell?) data (see the example below) and I would like to calculate the number of simultaneously overlapping episodes per minute. For example, I would like to calculate the number of overlapping episodes (spells) at 11:19, at 11:20, etc. (In the end, I'm only interested in the maximum number of overlaps).

I am aware of a possible solution: convert the data into a discrete time panel (using expand; described in "discrete" in the manual for survival analysis) and simply count per row.
However, this would be very time-consuming since my dataset is large.

Hence, I wonder if there is any other solution?

Thank you very much!


start end
10:40 11:20
11:02 11:22
11:04 11:24
11:08 11:28
11:16 11:56

[Nick Cox]

What are the first start and the last end? How are these variables held? How many observations do you have? Please use dataex (FAQ Advice #12).

[Tobias Hagen]

In my original post I simplified the data.
The original data include the date (so start and end are in datetime format).
The question is the same: I would like to calculate the number of overlaps for every minute of the year 2017. From this I want to get the maximum number of overlaps in 2017.

The first start is: 01/02/2017 09:17
The last end is: 12/30/2017 19:52

Here I show the first 30 out of 8574 observations.

Thank you very much!

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(start end)
1798967820000 1798969020000
1798968060000 1798971660000
1798971240000 1798972440000
1798971780000 1798974180000
1798973040000 1798975440000
1798973940000 1798975140000
1.7989743e+12 1.7989767e+12
1798974840000 1798979640000
1798978020000 1798979220000
1.7989803e+12 1.7989815e+12
1798981680000 1798984080000
1798984020000 1798985220000
1798984560000 1798986960000
1798986840000 1798988040000
1798987020000 1798989420000
1798989060000 1798990260000
1798990860000 1798993260000
1798990860000 1798996860000
1.7989932e+12 1.7989944e+12
1798993680000 1799000880000
1799003460000 1799008260000
1799006460000 1799010060000
1799052420000 1799053620000
1799053020000 1799054220000
1799053140000 1799057940000
1799053620000 1799056020000
1.7990541e+12 1.7990553e+12
1799054160000 1799056560000
1799056320000 1799057520000
1.7990577e+12 1.7990601e+12
end
format %tcNN/DD/CCYY_HH:MM start
format %tcNN/DD/CCYY_HH:MM end

[Nick Cox]

You are asking for about 500,000 counts

. di 365 * 24 * 60
525600

The corresponding data layout with that many observations could be useful for other purposes.

If you care only about the maximum, that's a loop. You needn't keep track of all the other values.

clear
input double(start end)
1798967820000 1798969020000
1798968060000 1798971660000
1798971240000 1798972440000
1798971780000 1798974180000
1798973040000 1798975440000
1798973940000 1798975140000
1.7989743e+12 1.7989767e+12
1798974840000 1798979640000
1798978020000 1798979220000
1.7989803e+12 1.7989815e+12
1798981680000 1798984080000
1798984020000 1798985220000
1798984560000 1798986960000
1798986840000 1798988040000
1798987020000 1798989420000
1798989060000 1798990260000
1798990860000 1798993260000
1798990860000 1798996860000
1.7989932e+12 1.7989944e+12
1798993680000 1799000880000
1799003460000 1799008260000
1799006460000 1799010060000
1799052420000 1799053620000
1799053020000 1799054220000
1799053140000 1799057940000
1799053620000 1799056020000
1.7990541e+12 1.7990553e+12
1799054160000 1799056560000
1799056320000 1799057520000
1.7990577e+12 1.7990601e+12
end

gen long startM = start / 60000 
gen long endM = end / 60000 

list 

local count = 0 
su startM, meanonly 
local min = r(min)
su endM, meanonly 
local max = r(max) 
quietly forval t = `min'/`max' { 
    count if inrange(`t', startM, endM) 
    if r(N) > `count' { 
        local count = r(N) 
        local which = `t' 
    }
 } 

local when : di %tcNN/DD/CCYY_HH:MM (`which'*60e3)  
di "`when' : " `count' 

01/03/2017 09:16 : 5

[Tobias Hagen]

Thank you very much! This code does exactly what I was looking for.