title: Sum over rows with multiple changing conditions R data.table
body: <p>I am trying to create a column in a <code>data.frame</code> or <code>data.table</code> with two conditions. The difference to the posts I have seen and which I have tried to modify below is that I do not have 'value' for the conditions but the conditions depend on other variables in the <code>data.frame</code>. </p>
  
  <p>Let's assume this is my data frame:</p>
  
  <pre><code>mydf &lt;- data.frame (Year = c(2000, 2001, 2002, 2004, 2005,
                               2007, 2000, 2001, 2002, 2003,
                               2003, 2004, 2005, 2006, 2006, 2007),
                      Name = c("Tom", "Tom", "Tom", "Fred", "Gill",
                               "Fred", "Gill", "Gill", "Tom", "Tom",
                               "Fred", "Fred", "Gill", "Fred", "Gill", "Gill"))
  </code></pre>
  
  <p>I want to find out how many times the 3 subjects have experienced an event in the last 5 years. However, if the event dates go back more than 5 years, I do not want to include it. I thought I could do a sum of an indicator variable (set to 1 if the subject experienced the event in the year) while specifying something along the lines of <code>Year &lt; Year &amp; Year &gt;= Year-5</code>. So basically sum the experiences for the year smaller than the focal year and larger than or equal to 5 years before the focal year.</p>
  
  <p>I have create an indicator for summing and a variable for focal year - 5 </p>
  
  <pre><code>mydf$Ind &lt;- 1
  mydf$Yearm5 &lt;- mydf$Year-5
  </code></pre>
  
  <p>Then I convert to data table for speed (the original df has +60k obs)</p>
  
  <pre><code>library(data.table)
  mydf &lt;- data.table(mydf)
  </code></pre>
  
  <p>The issue now is that I cannot get the two conditions to work. The post I have seen seem to all know a specific value by which to subset (e.g. <a href="https://stackoverflow.com/questions/20086929/r-data-table-subsetting-on-multiple-conditions/20090694#20090694">R data.table subsetting on multiple conditions.</a>), but in my case the value changes from observation to observation (not sure if this means I need to do some looping?).</p>
  
  <p>I thought I need something along the lines of:</p>
  
  <pre><code>mydf[, c("Exp"):= sum(Ind), by = c("Name")][Year &lt; Year &amp; Year &gt;= Yearm5]
  </code></pre>
  
  <p>gives:</p>
  
  <pre><code>Empty data.table (0 rows) of 5 cols: Year,Name,Ind,Yearm5,Exp
  </code></pre>
  
  <p>Using just one condition </p>
  
  <pre><code>mydf1 &lt;- mydf[, c("Exp"):= sum(Ind), by = c("Name")][Year &gt;= Yearm5] 
  </code></pre>
  
  <p>gives the total experience so I am assuming that something is wrong with the <code>Year &lt; Year</code> condition.</p>
  
  <p>I am not quite sure what though. I have also tried to modify the suggestions in:
  <a href="https://stackoverflow.com/questions/21421004/how-to-cumulatively-add-values-in-one-vector-in-r">how to cumulatively add values in one vector in R</a>
  with not luck again something seems to be wrong with the way I specify the conditions.</p>
  
  <pre><code>library(dplyr)
  mytest1 &lt;- mydf %&gt;%
             group_by(Name, Year) %&gt;%
             filter(Year &lt; Year &amp; Year &gt;= Yearm5) %&gt;%
             mutate(Exp = sum(Ind))
  </code></pre>
  
  <p>The result should look as follows:</p>
  
  <pre><code>myresult &lt;- data.frame (Year = c(2003, 2004, 2004, 2006,
                                   2007, 2000, 2001, 2005,
                                   2005, 2006, 2007, 2000,
                                   2001, 2002, 2002, 2003),
                          Name = c("Fred", "Fred", "Fred", "Fred",
                                   "Fred", "Gill", "Gill", "Gill",
                                   "Gill", "Gill", "Gill", "Tom",
                                   "Tom", "Tom", "Tom", "Tom"),
                          Ind = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
                          Exp = c(0, 1, 1, 3, 4, 0, 1, 1, 1, 2, 3, 0, 1, 2, 2, 4),
                          Yearm5 = c(1998, 1999, 1999, 2001, 2002,
                                     1995, 1996, 2000, 2000, 2001,
                                     2002, 1995, 1996, 1996, 1997, 1998))
  </code></pre>
  
  <p>Any help or pointers would be appreciated!</p>