title: How to Efficiently Update data.table Conditional on a Related Entry in the data.frame Existing?
body: <p>Consider the data.table, <code>dt</code>:</p>
  
  <pre><code>   id boro block       date
   1:  1    1     1 01/01/1991
   2:  1    1     2 01/01/1991
   3:  1    2     3 01/01/1991
   4:  1    2     4 01/01/1991
   5:  2    1     1 01/01/1992
   6:  2    1     2 01/01/1992
   7:  2    2     3 01/01/1992
   8:  2    2     5 01/01/1992
   9:  3    1     1 01/01/1993
  10:  3    1     2 01/01/1993
  11:  3    2     6 01/01/1993
  12:  3    2     7 01/01/1993
  </code></pre>
  
  <p>What I want to do efficiently (I have 19 million rows, 70 columns), is for each row, check if the id+1, boro, block entry exists in <code>dt</code>. If so, create a new column, <code>end_date</code>, with the date of the id+1, boro, block entry. I</p>
  
  <p>The code for each row would be something like:</p>
  
  <pre><code>update_end_date &lt;- function(dt,Id,Boro,Block) {
    if (dt[id==(Id+1) &amp; boro==Boro &amp; block==Block,.N]==1) {
      return(dt[id==(Id+1) &amp; boro==Boro &amp; block==Block,date])
    }
  }
  </code></pre>
  
  <p>I iterate the function on rows using:</p>
  
  <pre><code>dt[,end_date := update_end_date(dt,id,boro,block), by = seq_len(nrow(dt))]
  </code></pre>
  
  <p>The resulting table would be:</p>
  
  <pre><code>    id boro block       date   end_date
   1:  1    1     1 01/01/1991 01/01/1992
   2:  1    1     2 01/01/1991 01/01/1992
   3:  1    2     3 01/01/1991 01/01/1992
   4:  1    2     4 01/01/1991         NA
   5:  2    1     1 01/01/1992 01/01/1993
   6:  2    1     2 01/01/1992 01/01/1993
   7:  2    2     3 01/01/1992         NA
   8:  2    2     5 01/01/1992         NA
   9:  3    1     1 01/01/1993         NA
  10:  3    1     2 01/01/1993         NA
  11:  3    2     6 01/01/1993         NA
  12:  3    2     7 01/01/1993         NA
  </code></pre>
  
  <p>This works but is incredibly slow. From my observations it loops over 100 rows in 80 seconds, so it would take me eons to run on 19 million rows. I'm bran new to R, so there must be a better solution I don't know about.</p>