title: R data.table: sums with varying conditions
body: <h1>Introduction</h1>
  
  <p>I am writing this both as a question (read this introduction and skip to the last section), and as an exercise to help me think more clearly. If this is deemed inappropriate for SO, please kindly point out and I'll move this to somewhere else. </p>
  
  <p>I have a list of payment records, i.e. cashflows, that is of the following structure:</p>
  
  <ul>
  <li>Each contract has an <code>ID</code>, a starting date, a (projected) ending date, and multiple payment record.</li>
  <li>Each payment has a number, a due date, a receive date, a due amount, and a received amount.</li>
  <li>Some payment may default, i.e. past due.</li>
  <li>Some payment have not happend yet, where their received dates are <code>NA</code> and received amounts 0.</li>
  <li>For each contract, the first payment is due 30 days after the contract is signed.</li>
  <li>For each contract, the actual ending date is the day all payments are received.</li>
  </ul>
  
  <p>For the purpose of this exercise, I have constructed a <code>data.table</code> with mock data below:</p>
  
  <pre><code>require(data.table)
  require(lubridate)
  
  dA &lt;- as.Date('2016-08-02')
  dB &lt;- as.Date('2017-01-25')
  dC &lt;- as.Date('2017-05-10')
  ws &lt;- data.table(
    ID       = c(rep('A', 12L), rep('B', 12L), rep('C', 12L)),
    pmt_no   = rep(1L:12L, 3),
    start    = c(
      rep(dA, 12L),
      rep(dB, 12L),
      rep(dC, 12L)
    ),
    end      = c(
      rep(dA %m+% days(360L), 12L),
      rep(dB %m+% days(360L), 12L),
      rep(dC %m+% days(360L), 12L)
    ),
    due_date = c(
      dA %m+% days(seq(30L, 360L, by = 30L)),
      dB %m+% days(seq(30L, 360L, by = 30L)),
      dC %m+% days(seq(30L, 360L, by = 30L))
    ),
    rec_date = c(
      dA %m+% days(seq(30L, 360L, by = 30L)),
      dB %m+% days(seq(30L, 360L, by = 30L)),
      dC %m+% days(seq(30L, 360L, by = 30L))
    ),
    due_amt  = c(rep(120, 12L), rep(230, 12L), rep(180, 12L)),
    rec_amt  = c(rep(120, 12L), rep(230, 12L), rep(180, 12L))
  )[
    due_date &gt;= '2017-08-01',
    `:=`(rec_date = NA, rec_amt = 0)
  ][
    c(2L:5L,14L), rec_date := rec_date %m+% days(13L)
  ][
    c(11L,18L), `:=`(rec_date = NA, rec_amt = 0)
  ]
  </code></pre>
  
  <p>Some characteristics:</p>
  
  <ul>
  <li>36 payments for 3 contracts, 12 for each.</li>
  <li>Some payments have defaulted, and some of them are still outstanding.</li>
  <li>The data date is 2017-08-01. So that any payments beyond this date has not happened yet.</li>
  </ul>
  
  <p>Here's what it looks like:</p>
  
  <pre><code>   ID pmt_no start_date   end_date   due_date   rec_date due_amt rec_amt
  1:  A      1 2016-08-02 2017-07-28 2016-09-01 2016-09-01     120     120
  2:  A      2 2016-08-02 2017-07-28 2016-10-01 2016-10-14     120     120
  3:  A      3 2016-08-02 2017-07-28 2016-10-31 2016-11-13     120     120
  </code></pre>
  
  <h1>Working the Data: <code>sum</code> and <code>cumsum</code></h1>
  
  <p>Consider the following questions:</p>
  
  <ul>
  <li>How many payments, and what amount are due by the end of each month? How many and what amount have been collected by the end of each month?</li>
  </ul>
  
  <p>My instinctive approach is to construct a <code>date</code> vector, and then loop through it a couple times to get all the numbers. However I think the following is more inline with the <code>data.table</code> way:</p>
  
  <pre><code># amount due and received during each month, and
  # total due and total received by (the end of) each month
  
  merge(
    ws[
      due_date &lt; '2017-08-01',
      .(due_amt = sum(due_amt)),
      keyby = .(year(due_date), month(due_date))
    ][
      , 
      due_total := cumsum(due_amt)
    ],
    ws[
      rec_date &lt; '2017-08-01',
      .(rec_amt = sum(rec_amt)),
      keyby = .(year(rec_date), month(rec_date))
    ][
      , 
      rec_total := cumsum(rec_amt)
    ],
    all = TRUE
  )
  </code></pre>
  
  <p>Note that I have omitted the counts. Result is pleasant enough:</p>
  
  <pre><code>    year month due_amt due_total rec_amt rec_total
   1: 2016     9     120       120     120       120
   2: 2016    10     240       360     120       240
   3: 2016    11     120       480     120       360
   &lt;truncated&gt;
  </code></pre>
  
  <h1>Conditionals: <code>.SD</code> and <code>sum</code></h1>
  
  <ul>
  <li>Total and outstanding past due amount by each month?</li>
  </ul>
  
  <p>Still solvable, but I had to manipulate the received date to facilitate comparison with due date:</p>
  
  <pre><code>ws[is.na(rec_date), rec_date := as.Date('2099-12-31')]
  ws[
    due_date &lt; '2017-08-01',
    .(
      over_total = .SD[rec_date &gt; due_date, sum(due_amt)],
      over_outst = .SD[rec_date &gt; '2017-08-01', sum(due_amt)]
    ),
    keyby = .(year(due_date), month(due_date))
  ]
  </code></pre>
  
  <p>Result: </p>
  
  <pre><code>    year month over_total over_outst
   1: 2016     9          0          0
   2: 2016    10        240          0
  &lt;truncated&gt;
  10: 2017     6        120        120
  11: 2017     7        230        230
  </code></pre>
  
  <h1>Delving deeper: forward <code>cumsum</code>?</h1>
  
  <p>Up to now I was doing fine. But consider this one:</p>
  
  <ul>
  <li>What amount would we receive in the future (including past dues), <strong>when we were at the end of March 2017</strong>?</li>
  </ul>
  
  <p>For a single date, this is not much of a problem. Although do take care to exclude any contract that has not been in effect by that date. Also, if you skipped the intermediate sections, I've manipulated the <code>rec_date</code> in order to do comparisons:</p>
  
  <pre><code>ws[is.na(rec_date), rec_date := as.Date('2099-12-31')]
  ws[
    rec_date &gt;= '2017-04-01' &amp; start &lt; '2017-04-01',
    .(bal = sum(due_amt))
  ]
  </code></pre>
  
  <p>result:</p>
  
  <pre><code>bal
  1: 3010
  </code></pre>
  
  <p>But, is there a <code>data.table</code> way to find this amount for <strong>each past month</strong>? I could write a loop, but a loop is cumbersome and ugly and slow. I guess I'm looking for something like a forward <code>cumsum</code>, so that combined with <code>keyby</code> it would be more or less equivalent to the following:</p>
  
  <pre><code># To-be-collected amount by the end of each month, the loop way
  result &lt;- data.table(
    date = as.Date('2016-09-01') %m+% months(0L:11L)
  )
  for (i in 1L:result[, .N]) {
    temp &lt;- ws[
      rec_date &gt;= result[i, date] &amp; start &lt; result[i, date],
      .(bal = sum(due_amt))
    ]
    result[i, bal := temp]
  }
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>          date  bal
   1: 2016-09-01 1440
   2: 2016-10-01 1320
   3: 2016-11-01 1200
   4: 2016-12-01 1080
   5: 2017-01-01  960
   6: 2017-02-01 3480
   7: 2017-03-01 3130
   8: 2017-04-01 3010
   9: 2017-05-01 2430
  10: 2017-06-01 4240
  11: 2017-07-01 3830
  12: 2017-08-01 3530
  </code></pre>