title: Finding an efficient way to count the number of overlaps between interval sets in two tables?
body: <p><em>Note: I am using an example dataset from a previous post for convenience purposes.</em> </p>
  
  <p>Suppose that there are two data sets, <code>ref</code> and <code>map</code>. They are:</p>
  
  <pre><code>ref &lt;- data.table(space=rep('nI',3),t1=c(100,300,500),t2=c(150,400,600),id=letters[1:3])
  
  map &lt;- data.table(space=rep('nI',241),t1=seq(0,1200,by=5),t2=seq(5,1205,by=5),res=rnorm(241))
  </code></pre>
  
  <p>they look like:</p>
  
  <pre><code>&gt; ref
     space t1  t2   id
  1:    nI 100 150  a
  2:    nI 300 400  b
  3:    nI 500 600  c
  
  &gt; map
     space   t1   t2        res
  1:    nI    0    5 -0.7082922
  2:    nI    5   10  1.8251041
  3:    nI   10   15  0.2076552
  4:    nI   15   20  0.8047347
  5:    nI   20   25  2.3388920
  ---                           
  237:    nI 1180 1185  1.0229284
  238:    nI 1185 1190 -0.3657815
  239:    nI 1190 1195  0.3013489
  240:    nI 1195 1200  1.2947271
  241:    nI 1200 1205 -1.5050221
  </code></pre>
  
  <p>Now, it has come to my attention that in the data.table package that is still under development, the function <code>foverlaps</code> will fill in intervals in <code>ref</code> with the corresponding rows in <code>map</code>.</p>
  
  <pre><code>setkey(ref,space,t1,t2)
  
  foverlaps(map,ref,type="within",nomatch=0L)
  </code></pre>
  
  <p>which gives: </p>
  
  <pre><code>   space t1  t2  id i.t1 i.t2         res
  1:    nI 100 150  a  100  105 -0.85202726
  2:    nI 100 150  a  105  110  0.79748876
  3:    nI 100 150  a  110  115  1.49894097
  4:    nI 100 150  a  115  120  0.47719957
  5:    nI 100 150  a  120  125 -0.95767896
  6:    nI 100 150  a  125  130 -0.51054673
  7:    nI 100 150  a  130  135 -0.08478700
  8:    nI 100 150  a  135  140 -0.69526566
  9:    nI 100 150  a  140  145  2.14917623
  10:    nI 100 150  a  145  150 -0.05348163
  11:    nI 300 400  b  300  305  0.28834548
  12:    nI 300 400  b  305  310  0.32449616
  13:    nI 300 400  b  310  315  1.16107248
  14:    nI 300 400  b  315  320  1.08550676
  15:    nI 300 400  b  320  325  0.84640788
  16:    nI 300 400  b  325  330 -2.15485447
  17:    nI 300 400  b  330  335  1.59115714
  18:    nI 300 400  b  335  340 -0.57588128
  19:    nI 300 400  b  340  345  0.23957563
  20:    nI 300 400  b  345  350 -0.60824259
  21:    nI 300 400  b  350  355 -0.84828189
  22:    nI 300 400  b  355  360 -0.43528701
  23:    nI 300 400  b  360  365 -0.80026281
  24:    nI 300 400  b  365  370 -0.62914234
  25:    nI 300 400  b  370  375 -0.83485164
  26:    nI 300 400  b  375  380  1.46922713
  27:    nI 300 400  b  380  385 -0.53965310
  28:    nI 300 400  b  385  390  0.98728765
  29:    nI 300 400  b  390  395 -0.66328893
  30:    nI 300 400  b  395  400 -0.08182384
  31:    nI 500 600  c  500  505  0.72566100
  32:    nI 500 600  c  505  510  2.27878366
  33:    nI 500 600  c  510  515  0.72974139
  34:    nI 500 600  c  515  520 -0.35358019
  35:    nI 500 600  c  520  525 -1.20697646
  36:    nI 500 600  c  525  530 -0.01719057
  37:    nI 500 600  c  530  535  0.06686472
  38:    nI 500 600  c  535  540 -0.40866088
  39:    nI 500 600  c  540  545 -1.02697573
  40:    nI 500 600  c  545  550  2.19822065
  41:    nI 500 600  c  550  555  0.57075648
  42:    nI 500 600  c  555  560 -0.52009726
  43:    nI 500 600  c  560  565 -1.82999177
  44:    nI 500 600  c  565  570  2.53776578
  45:    nI 500 600  c  570  575  0.85626293
  46:    nI 500 600  c  575  580 -0.34245708
  47:    nI 500 600  c  580  585  1.21679869
  48:    nI 500 600  c  585  590  1.87587020
  49:    nI 500 600  c  590  595 -0.23325264
  50:    nI 500 600  c  595  600  0.18845022
  space  t1  t2 id i.t1 i.t2         res
  </code></pre>
  
  <p>to run the developement version of data.table 1.9.3, the following code will help you run it:</p>
  
  <pre><code>install.packages("devtools")
  library(devtools)
  dev_mode(on=T)
  
  install_github("Rdatatable/data.table", build_vignettes=FALSE)
  
  dev_mode(on=F)
  </code></pre>
  
  <p><strong>What I am trying to do instead:</strong></p>
  
  <p>The above basically lists out all the intervals contained within the time intervals. However, I am trying to just create a new column by <code>ref</code> that <strong>counts</strong> the number of rows in <code>map</code> that are within the time intervals of <code>ref</code>. Hence, the table I would like is:</p>
  
  <pre><code>&gt; ref
     space t1  t2  id  count
  1:    nI 100 150  a     10
  2:    nI 300 400  b     20
  3:    nI 500 600  c     20
  </code></pre>
  
  <p>The count for each indicates how many rows of <code>map</code> fell in between each time interval of <code>ref</code>. While I understand a very basic solution is to just use a sum or count function to count, is there a solution that would create the counts without having to first create the larger filled in dataset? I say this because my real data contains over 300 million observations. Any suggestions would be greatly helpful! Thank you!</p>