title: R: Conditional Sum by Row in DataTable
body: <p>I've got a very large dataset (millions of rows that I need  to loop through thousands of times), and during the loop I have to do a conditional sum that appears to be taking a very long time. Is there a way of making this more efficient?</p>
  <p>Datatable format as follows:</p>
  <pre><code>DT &lt;- data.table('A' = c(1,1,1,2,2,3,3,3,3,4),
                   'B' = c(500,510,540,500,540,500,510,519,540,500),
                   'C' = c(10,20,10,20,10,50,20,50,20,10))
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>500</td>
  <td>10</td>
  </tr>
  <tr>
  <td>1</td>
  <td>510</td>
  <td>20</td>
  </tr>
  <tr>
  <td>1</td>
  <td>540</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>500</td>
  <td>20</td>
  </tr>
  <tr>
  <td>2</td>
  <td>540</td>
  <td>10</td>
  </tr>
  <tr>
  <td>3</td>
  <td>500</td>
  <td>50</td>
  </tr>
  <tr>
  <td>3</td>
  <td>510</td>
  <td>20</td>
  </tr>
  <tr>
  <td>3</td>
  <td>519</td>
  <td>50</td>
  </tr>
  <tr>
  <td>3</td>
  <td>540</td>
  <td>20</td>
  </tr>
  <tr>
  <td>4</td>
  <td>500</td>
  <td>10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need the sum of column C (in a new column, D) subject to A == A, and B &gt;= B &amp; B &lt; B + 20 (by row). So the output table would look like the following:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>500</td>
  <td>10</td>
  <td>30</td>
  </tr>
  <tr>
  <td>1</td>
  <td>510</td>
  <td>20</td>
  <td>30</td>
  </tr>
  <tr>
  <td>1</td>
  <td>540</td>
  <td>10</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>500</td>
  <td>20</td>
  <td>20</td>
  </tr>
  <tr>
  <td>2</td>
  <td>540</td>
  <td>10</td>
  <td>10</td>
  </tr>
  <tr>
  <td>3</td>
  <td>500</td>
  <td>50</td>
  <td>120</td>
  </tr>
  <tr>
  <td>3</td>
  <td>510</td>
  <td>20</td>
  <td>120</td>
  </tr>
  <tr>
  <td>3</td>
  <td>519</td>
  <td>50</td>
  <td>120</td>
  </tr>
  <tr>
  <td>3</td>
  <td>540</td>
  <td>20</td>
  <td>20</td>
  </tr>
  <tr>
  <td>4</td>
  <td>500</td>
  <td>10</td>
  <td>10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The code I'm currently using:</p>
  <pre><code>DT[,D:= sum(DT$C[A == DT$A &amp; ((B &gt;= DT$B) &amp; (B &lt; DT$B + 20))]), by=c('A', 'B')]
  </code></pre>
  <p>This takes a very long time to actually run, as well as giving me the wrong answer. The output I get looks like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>500</td>
  <td>10</td>
  <td>10</td>
  </tr>
  <tr>
  <td>1</td>
  <td>510</td>
  <td>20</td>
  <td>30</td>
  </tr>
  <tr>
  <td>1</td>
  <td>540</td>
  <td>10</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>500</td>
  <td>20</td>
  <td>20</td>
  </tr>
  <tr>
  <td>2</td>
  <td>540</td>
  <td>10</td>
  <td>10</td>
  </tr>
  <tr>
  <td>3</td>
  <td>500</td>
  <td>50</td>
  <td>50</td>
  </tr>
  <tr>
  <td>3</td>
  <td>510</td>
  <td>20</td>
  <td>70</td>
  </tr>
  <tr>
  <td>3</td>
  <td>519</td>
  <td>50</td>
  <td>120</td>
  </tr>
  <tr>
  <td>3</td>
  <td>540</td>
  <td>20</td>
  <td>20</td>
  </tr>
  <tr>
  <td>4</td>
  <td>500</td>
  <td>10</td>
  <td>10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>(i.e. D only appears to increase cumulatively).</p>
  <p>I'm less concerned with the cumulative thing, more about speed. Ultimately what I'm trying to get to is the largest sum of C, by A, subject to B being within 20 of eachother. I would really appreciate any help on this! Thanks in advance.</p>