title: How to vectorize a loop that, for each row, sums a function of the time elapsed between the current and all prior entries of that entity ID in r
body: <p>I have a large data.table (circa 900k rows) which can be represented by the following example:</p>
  
  <pre><code>        row.id entity.id event.date result
   1:      1       100 2015-01-20     NA
   2:      2       101 2015-01-20     NA
   3:      3       104 2015-01-20     NA
   4:      4       107 2015-01-20     NA
   5:      5       103 2015-01-23     NA
   6:      6       109 2015-01-23     NA
   7:      7       102 2015-01-23     NA
   8:      8       101 2015-01-26     NA
   9:      9       110 2015-01-26     NA
  10:     10       112 2015-01-26     NA
  11:     11       109 2015-01-26     NA
  12:     12       130 2015-01-29     NA
  13:     13       100 2015-01-29     NA
  14:     14       127 2015-01-29     NA
  15:     15       101 2015-01-29     NA
  16:     16       119 2015-01-29     NA
  17:     17       104 2015-02-03     NA
  18:     18       101 2015-02-03     NA
  19:     19       125 2015-02-03     NA
  20:     20       130 2015-02-03     NA
  </code></pre>
  
  <p>Essentially I have columns containing: the ID representing the entity in question (entity.id); the date of an event in which this ID partook (note that many, and differing numbers of, entities will participate in each event).  I need to calculate a factor that, for each entity.id on each event date, depends (non-linearly) on the time (in days) that has elapsed since all the previous events in which that entity ID was entered. </p>
  
  <p>To put it in other, more programmatic terms, on each row of the data.table I need to find all instances with matching ID and where the date is older than the event date of the row in question, work out the difference in time (in days) between the ‘current’ and historical events, and sum some non-linear function applied to each of the time periods (I’ll use the square in this example).</p>
  
  <p>In the example above, for entity.id = 101 on 03-02-2015 (row 18), the we would need to look back to that ID's prior entries on rows 15, 8 and 2, calculate the differences in days from the ‘current’ event (14, 8 and 5 days), and then calculate the answer by summing the squares of those periods (14^2 + 8^2 + 5^2) = 196 + 64 + 25 = 285.  (The real function is somewhat more complex but this is sufficiently representative.)</p>
  
  <p>This is trivial to achieve with for-loops, as per below:</p>
  
  <pre><code># Create sample dt
  dt &lt;- data.table(row.id = 1:20,
       entity.id = c(100, 101, 104, 107, 103, 109, 102, 101, 110, 112,
                     109, 130, 100, 127, 101, 119, 104, 101, 125, 130),
       event.date = as.Date(c("2015-01-20", "2015-01-20", "2015-01-20", "2015-01-20", 
                      "2015-01-23", "2015-01-23", "2015-01-23",
                      "2015-01-26", "2015-01-26", "2015-01-26", "2015-01-26",
                      "2015-01-29", "2015-01-29", "2015-01-29", "2015-01-29", "2015-01-29",
                      "2015-02-03", "2015-02-03", "2015-02-03", "2015-02-03")),
       result = NA)
  setkey(dt, row.id)
  
  for (i in 1:nrow(dt)) { #loop through each entry
  
    # get a subset of dt comprised of rows with this row's entity.id, which occur prior to this row
    event.history &lt;- dt[row.id &lt; i &amp; entity.id == entity.id[i]]
  
    # calc the sum of the differences between the current row event date and the prior events dates, contained within event.history, squared
    dt$result[i] &lt;- sum( (as.numeric(dt$event.date[i]) - as.numeric(event.history$event.date)) ^2 )
  }
  </code></pre>
  
  <p>Unfortunately, on the real dataset it is also extremely slow, no doubt because if the amount of subsetting operations required.  Is there a way to vectorise, or otherwise speed up, this operation?  I’ve searched and searched and wracked my brains but can’t work out how to vecotrally subset rows based on differing data per each row without looping.</p>
  
  <p>Note that I created a row.id column to allow me to extract all prior rows (rather than prior dates), as the two are broadly equivalent (an entity cannot attend more than one event a day) and this way was much quicker (I think because it avoids the need to coerce the dates to numeric before doing the comparison, ie. Dt[as.numeric(event_date) &lt; as.numeric(event_date[i])]</p>
  
  <p>Note also that I’m not wedded to it being a data.table; I’m happy to use dplyr or other mechanisms to achieve this if need be.</p>