title: How to calculate number of occurrences per minute for a large dataset
body: <p>I have a dataset with 500k appointments lasting between 5 and 60 minutes. </p>
  
  <pre><code>tdata &lt;- structure(list(Start = structure(c(1325493000, 1325493600, 1325494200, 1325494800, 1325494800, 1325495400, 1325495400, 1325496000, 1325496000, 1325496600, 1325496600, 1325497500, 1325497500, 1325498100, 1325498100, 1325498400, 1325498700, 1325498700, 1325499000, 1325499300), class = c("POSIXct", "POSIXt"), tzone = "GMT"), End = structure(c(1325493600, 1325494200, 1325494500, 1325495400, 1325495400, 1325496000, 1325496000, 1325496600, 1325496600, 1325496900, 1325496900, 1325498100, 1325498100, 1325498400, 1325498700, 1325498700, 1325499000, 1325499300, 1325499600, 1325499600), class = c("POSIXct", "POSIXt"), tzone = "GMT"), Location = c("LocationA", "LocationA", "LocationA", "LocationA", "LocationA", "LocationA", "LocationA", "LocationA", "LocationA", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB", "LocationB"), Room = c("RoomA", "RoomA", "RoomA", "RoomA", "RoomB", "RoomB", "RoomB", "RoomB", "RoomB", "RoomB", "RoomA", "RoomA", "RoomA", "RoomA", "RoomA", "RoomA", "RoomA", "RoomA", "RoomA", "RoomA")), .Names = c("Start", "End", "Location", "Room"), row.names = c(NA, 20L), class = "data.frame")
  </code></pre>
  
  <pre class="lang-r prettyprint-override"><code>&gt; head(tdata)
                  Start                 End  Location  Room
  1 2012-01-02 08:30:00 2012-01-02 08:40:00 LocationA RoomA
  2 2012-01-02 08:40:00 2012-01-02 08:50:00 LocationA RoomA
  3 2012-01-02 08:50:00 2012-01-02 08:55:00 LocationA RoomA
  4 2012-01-02 09:00:00 2012-01-02 09:10:00 LocationA RoomA
  5 2012-01-02 09:00:00 2012-01-02 09:10:00 LocationA RoomB
  6 2012-01-02 09:10:00 2012-01-02 09:20:00 LocationA RoomB
  </code></pre>
  
  <p>I would like calculate the <strong>number of concurrent appointments</strong> in total, per Location, and per Room (and several other factors in de original dataset). </p>
  
  <p>I have tried using <code>mysql</code> package to perform a left join, which works for a small dataset, but takes forever for the entire dataset: </p>
  
  <pre><code># SQL Join.
  start.min &lt;- min(tdata$Start, na.rm=T)
  end.max &lt;- max(tdata$End, na.rm=T)
  tinterval &lt;- seq.POSIXt(start.min, end.max, by = "mins")
  tinterval &lt;- as.data.frame(tinterval)
  
  library(sqldf)
  system.time(
    output &lt;- sqldf("SELECT *
                FROM tinterval 
                LEFT JOIN tdata 
                ON tinterval.tinterval &gt;= tdata.Start
                AND tinterval.tinterval &lt; tdata.End "))
  
  head(output)
              tinterval               Start                 End  Location  Room
  1 2012-01-02 09:30:00 2012-01-02 09:30:00 2012-01-02 09:40:00 LocationA RoomA
  2 2012-01-02 09:31:00 2012-01-02 09:30:00 2012-01-02 09:40:00 LocationA RoomA
  3 2012-01-02 09:32:00 2012-01-02 09:30:00 2012-01-02 09:40:00 LocationA RoomA
  4 2012-01-02 09:33:00 2012-01-02 09:30:00 2012-01-02 09:40:00 LocationA RoomA
  5 2012-01-02 09:34:00 2012-01-02 09:30:00 2012-01-02 09:40:00 LocationA RoomA
  6 2012-01-02 09:35:00 2012-01-02 09:30:00 2012-01-02 09:40:00 LocationA RoomA
  </code></pre>
  
  <p>It creates a data frame where all the "active" appointments are listed for each minute. The large dataset covers a full year (~525600 minutes). With an average appointment duration of 18 minutes, I expect the sql join to create a data set with ~ 5 million rows, which I can use to create occupancy plots for different factors (Location/Room etc). </p>
  
  <p>Building on the sapply solution suggested in <a href="https://stackoverflow.com/questions/9003333/how-to-count-the-number-of-concurrent-users-using-time-interval-data?rq=1" title="how-to-count-the-number-of-concurrent-users-using-time-interval-data">How to count number of concurrent users</a> I tried using <code>data.table</code> and <code>snowfall</code> as follows: </p>
  
  <pre><code>require(snowfall) 
  require(data.table)
  sfInit(par=T, cpu=4)
  sfLibrary(data.table)
  
  tdata &lt;- data.table(tdata)
  tinterval &lt;- seq.POSIXt(start.min, end.max, by = "mins")
  setkey(tdata, Start, End)
  sfExport("tdata") # "Transport" data to cores
  
  system.time( output &lt;- data.frame(tinterval,sfSapply(tinterval, function(i) length(tdata[Start &lt;= i &amp; i &lt; End,Start]) ) ) )
  
  &gt; head(output)
              tinterval sfSapply.tinterval..function.i..length.tdata.Start....i...i...
  1 2012-01-02 08:30:00                                                              1
  2 2012-01-02 08:31:00                                                              1
  3 2012-01-02 08:32:00                                                              1
  4 2012-01-02 08:33:00                                                              1
  5 2012-01-02 08:34:00                                                              1
  6 2012-01-02 08:35:00                                                              1
  </code></pre>
  
  <p>This solution is fast, takes ~18 seconds to calculate 1 day (about 2 hours for a full year). The downside is I cannot create subsets of number of concurrent appointments for certain factors (Location, Room etc). I have the feeling there must be a better way to do this.. any advice?</p>
  
  <p><strong>UPDATE</strong>:
  Final solution looks like this, based on Geoffrey's answer. The example shows how the occupancies for each location can be determined. </p>
  
  <pre><code>setkey(tdata, Location, Start, End)
  vecTime &lt;- seq(from=tdata$Start[1],to=tdata$End[nrow(tdata)],by=60)
  res &lt;- data.frame(time=vecTime)
  
  for(i in 1:length(unique(tdata$Location)) ) { 
    addz &lt;- array(0,length(vecTime))
    remz &lt;- array(0,length(vecTime))
  
    tdata2 &lt;- tdata[J(unique(tdata$Location)[i]),] # Subset a certain location.
  
    startAgg &lt;- aggregate(tdata2$Start,by=list(tdata2$Start),length)
    endAgg &lt;- aggregate(tdata2$End,by=list(tdata2$End),length)
    addz[which(vecTime %in% startAgg$Group.1 )] &lt;- startAgg$x
    remz[which(vecTime %in% endAgg$Group.1)] &lt;- -endAgg$x
  
    res[,c( unique(tdata$Location)[i] )] &lt;- cumsum(addz + remz)
  }
  
  &gt; head(res)
                   time LocationA LocationB
  1 2012-01-01 03:30:00         1         0
  2 2012-01-01 03:31:00         1         0
  3 2012-01-01 03:32:00         1         0
  4 2012-01-01 03:33:00         1         0
  5 2012-01-01 03:34:00         1         0
  6 2012-01-01 03:35:00         1         0
  </code></pre>