title: Conditional Sum Depending on Other Data Frame Columns
body: <p>Let's say you have a data frame generated by the following commands:</p>
  
  <pre><code>date &lt;- seq(as.Date("2012-09-01"), Sys.Date(), 1)
  id &lt;- rep(c("a","b","c","d"), 8)
  bdate &lt;- seq(as.Date("2012-08-01"), as.Date("2012-11-01"), 1)[sample(1:32, 32)]
  
  # The end date should be random but greater than the begin date. However, I set it to 15 days later for simplicity.
  edate &lt;- bdate + 15
  
  value &lt;- seq(1, 1000, 1)[sample(1:1000, 32)]
  dfa &lt;- data.frame(id, value, bdate, edate)
  names(dfa) &lt;- c("ID", "Value", "Begin.Date", "End.Date")
  </code></pre>
  
  <p>The goal is to sum all the observations by ID (i.e., "a", "b", or "c") in the following way:</p>
  
  <pre><code>Date        a   b   c
  2012-08-01  XXX YYY ZZZ
  2012-08-02  XXX YYY ZZZ
  2012-08-03  XXX YYY ZZZ
  </code></pre>
  
  <p>The values XXX, YYY, and ZZZ represent the sum of all the observations where the date on column "Date" falls between dfa$Begin.Date and dfa$End.Date on the original data frame, for each ID.</p>
  
  <p>My current solution is practically useless for large datasets so I was wondering if there are any faster ways to do it.</p>
  
  <p>My current script:</p>
  
  <pre><code># Create additional data frame
  dfb &lt;- data.frame(seq(as.Date("2012-08-01"), as.Date("2012-11-01"), 1))
  names(dfb)[1] &lt;- "Date"
  
  # Variable for unique IDs
  nid &lt;- unique(dfa$ID)
  
  # Number of total IDs
  tid &lt;- length(nid)
  
  for (i in c(1:tid))
  {
  sums &lt;- vapply(dfb$Date, function(x)
  {
  temp &lt;- subset(dfa, dfa$ID == nid[i])
  temp &lt;- subset(temp, temp$Begin.Date &lt; x &amp; temp$End.Date &gt; x)
  res &lt;- sum(temp$Value)
  res
  }, FUN.VALUE = 0.1
  )
  dfb[1+i] &lt;- sums
  }
  
  # Change column names to ID
  names(dfb) &lt;- c("Date", as.character(nid))
  </code></pre>
  
  <p>EDIT: I posted an answer below with a more efficient way to do this. However, I accepted Matthew's answer because it set me on the right path.</p>