title: How to create a conditional variable based on rules?
body: <p>I have a time-series panel dataset that is structured in the following way: There are multiple funds that each own multiple stocks and we have the time series of the weight of the stock within that portfolio and the total value of the portfolio for that year. As you can see the panel is not balanced.</p>
  <pre><code>df &lt;- data.frame(
    fund_id = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L),
    stock_id = c(1L, 1L, 2L, 2L, 2L, 1L, 1L, 1L, 2L, 2L, 3L, 3L, 2L, 2L, 3L, 3L, 3L),
    year = c(2011L, 2012L, 2011L, 2012L, 2013L, 2011L, 2012L, 2013L, 2012L,2013L, 2011L, 2012L, 2013L, 2014L, 2011L, 2012L, 2013L),
    weight = c(0.3L, 0.2L, 0.7L, 0.8L, 1L, 0.2L, 0.1L, 0.2L, 0.7L, 0.8L, 0.8L, 0.2L, 0.3L, 1L, 0.5L, 0.2L, 0.7L),
   aum = c(3L, 3.5L, 3L, 3.5L, 4L, 5L, 4L, 5L, 4L, 5L, 5L, 4L, 6L, 7L, 5L, 6L, 6L)
  )
  &gt; df
     fund_id stock_id year weight aum
  1        1        1 2011    0.3 3.0
  2        1        1 2012    0.2 3.5
  3        1        2 2011    0.7 3.0
  4        1        2 2012    0.8 3.5
  5        1        2 2013    1.0 4.0
  6        2        1 2011    0.2 5.0
  7        2        1 2012    0.1 4.0
  8        2        1 2013    0.2 5.0
  9        2        2 2012    0.7 4.0
  10       2        2 2013    0.8 5.0
  11       2        3 2011    0.8 5.0
  12       2        3 2012    0.2 4.0
  13       3        2 2013    0.3 6.0
  14       3        2 2014    1.0 7.0
  15       3        3 2011    0.5 5.0
  16       3        3 2012    0.2 6.0
  17       3        3 2013    0.7 6.0
  &gt; 
  </code></pre>
  <p>I would like to assign the value of zero to weight column if the stock was present in the fund in the past 3 years and then all of sudden it's not. e.g. if stock &quot;1&quot; was part of fund &quot;1&quot; portfolio in 2011 and 2012 then as long as there is a positive weight for that stock in the past 3 years I would like to have a new row to be created for stock &quot;1&quot; in year 2013 with the weight value of zero and the correct fund aum (Value) of 4.</p>
  <p>I think the rule should start by grouping by the fund and then looking at the max year within the fund and then creating the missing year rows for every stock up the max year. Then update the value of newly created rows' weight based on the fact that stock was present in the past 3 years or not if true assign zero value to the weight if not delete the row. So for example, if the stock was present only in 4 years before, then we do not want to have that stock for that year at all so that row should be deleted.</p>