title: How do you mutate a column based on the values from another ID's columns, with specific time? (panel data)
body: <p>Here's an artificial dataset I'm working on:</p>
  <pre><code>     id year work spouse_work
  1  1000 1999    1           1
  2  1000 2001    1           1
  3  1000 2003    1           1
  4  1000 2004    1           0
  5  1000 2005    0           0
  6  1001 1999    1          NA
  7  1001 2001    1          NA
  8  1001 2003    1          NA
  9  1001 2005    1          NA
  10 2000 2001    1           1
  11 2000 2003    1           1
  12 2000 2004    1           1
  13 2000 2005    0           1
  14 2001 2001    1          NA
  15 2001 2003    1          NA
  16 2001 2005    1          NA
  </code></pre>
  <p>The first three digits of <code>id</code> represent the family, and the last digit of <code>id</code> represent the child ID in this family. If the last digit of <code>id</code> is 0, then this person is the family host. If the last digit of <code>id</code> equals 1,2,3, etc., then this person is the child of the host. In this dataset, <code>id=1000</code> and <code>id=2000</code> are the host, and <code>id=1001</code> is <code>id=1000</code>'s child, <code>id=2001</code> is <code>id=2000</code>'s child.</p>
  <p>Now I want to create two new columns: <code>parent_1_work</code> and <code>parent_2_work</code>. For example, since <code>id=1001</code>'s parents' working status is already indicated in <code>id=1000</code>'s <code>work</code> column and <code>spouse_work</code> column, then we can fill in the values of <code>parent_1_work</code> and <code>parent_2_work</code> for <code>id=1001</code>, based on the values in <code>id=1000</code>'s <code>work</code> column and <code>spouse_work</code> column. Also note that it has to be year-specific. For example, <code>id=1001</code> doesn't have data in year 2004, therefore we cannot fit the value of <code>id=1000,year=2004,work=1</code> into <code>id=1001,year=2005,parents_1_work</code>.</p>
  <p>The end result should be like this:</p>
  <pre><code>     id year work spouse_work parent_1_work parent_2_work
  1  1000 1999    1           1            NA            NA
  2  1000 2001    1           1            NA            NA
  3  1000 2003    1           1            NA            NA
  4  1000 2004    1           0            NA            NA
  5  1000 2005    0           0            NA            NA
  6  1001 1999    1          NA             1             1
  7  1001 2001    1          NA             1             1
  8  1001 2003    1          NA             1             1
  9  1001 2005    1          NA             0             0
  10 2000 2001    1           1            NA            NA
  11 2000 2003    1           1            NA            NA
  12 2000 2004    1           1            NA            NA
  13 2000 2005    0           1            NA            NA
  14 2001 2001    1          NA             1             1
  15 2001 2003    1          NA             1             1
  16 2001 2005    1          NA             0             1
  </code></pre>
  <p>Can someone please help?</p>
  <p>Here's the code to create this artificial data. Much appreciated!</p>
  <pre><code>year&lt;-c(1999,2001,2003,2004,2005,1999,2001,2003,2005,2001,2003,2004,2005,2001,2003,2005)
  id&lt;-c(1000,1000,1000,1000,1000,1001,1001,1001,1001,2000,2000,2000,2000,2001,2001,2001)
  work&lt;-c(1,1,1,1,0,1,1,1,1,1,1,1,0,1,1,1)
  spouse_work&lt;-c(1,1,1,0,0,NA,NA,NA,NA,1,1,1,1,NA,NA,NA)
  parent_1_work&lt;-c(NA,NA,NA,NA,NA,1,1,1,0,NA,NA,NA,NA,1,1,0)
  parent_2_work&lt;-c(NA,NA,NA,NA,NA,1,1,1,0,NA,NA,NA,NA,1,1,1)
  before&lt;-as.data.frame(cbind(id,year,work,spouse_work))
  before
  result&lt;-as.data.frame(cbind(id,year,work,spouse_work,parent_1_work,parent_2_work))
  result
  </code></pre>