title: Merge 2 data frame based on 2 columns with different column names
body: <p>I have 2 very large data sets that looks like below:</p>
  
  <pre><code>merge_data &lt;- data.frame(ID = c(1,2,3,4,5,6,7,8,9,10), 
                           position=c("yes","no","yes","no","yes", 
                                      "no","yes","no","yes","yes"),
                           school = c("a","b","a","a","c","b","c","d","d","e"),
                           year1 = c(2000,2000,2000,2001,2001,2000,
                                     2003,2005,2008,2009), 
                           year2=year1-1)
  
  
   merge_data
  
   ID position school year1 year2
   1   1  support   a  2000  1999
   2   2   oppose   b  2000  1999
   3   3  support   a  2000  1999
   4   4   oppose   a  2001  2000
   5   5  support   c  2001  2000
   6   6   oppose   b  2000  1999
   7   7  support   c  2003  2002
   8   8   oppose   d  2005  2004
   9   9  support   d  2008  2007
   10 10  support   e  2009  2008
  
  
  
  merge_data_2 &lt;- data.frame(year=c(1999,1999,2000,2000,2000,2001,2003
                                    ,2012,2009,2009,2008,2002,2009,2005,
                                    2001,2000,2002,2000,2008,2005),
                             amount=c(100,200,300,400,500,600,700,800,900,
                                      1000,1100,1200,1300,1400,1500,1600,
                                      1700,1800,1900,2000), 
                             ID=c(1,1,2,2,2,3,3,3,5,6,8,9,10,13,15,17,19,20,21,7))
  
  
    merge_data_2
     year amount ID
  1  1999    100  1
  2  1999    200  1
  3  2000    300  2
  4  2000    400  2
  5  2000    500  2
  6  2001    600  3
  7  2003    700  3
  8  2012    800  3
  9  2009    900  5
  10 2009   1000  6
  11 2008   1100  8
  12 2002   1200  9
  13 2009   1300 10
  14 2005   1400 13
  15 2001   1500 15
  16 2000   1600 17
  17 2002   1700 19
  18 2000   1800 20
  19 2008   1900 21
  20 2005   2000  7
  </code></pre>
  
  <p>And what I want is:</p>
  
  <pre><code> ID position school year1 year2 amount
   1    yes    a      2000  1999  300
   2    no     b      2000  1999  1200
  10    yes    e      2009  2008  1300
  </code></pre>
  
  <p>for ID=1 in the merge_data_2, we have amount =300, since there are 2 cases where ID=1,and their year1 or year1 is equal to the year of ID=1 in merge_data</p>
  
  <p>So basically what I want is to perform a merge based on the ID and year.
  2 conditions:</p>
  
  <ol>
  <li>ID from merge_data matches the ID from merge_data_2</li>
  <li>one of the year1 and year2 from merge_data also matches the year from merge_data_2. 
  then make the merge based on the sum of the amount for each IDs.</li>
  </ol>
  
  <p>and I think the code will be something looks like:</p>
  
  <pre><code>merge_data_final &lt;- merge(merge_data, merge_data_2, 
                            merge_data$ID == merge_data_2$ID &amp;&amp; (merge_data$year1 || 
                              merge_data$year2 == merge_data_2$year))
  
  Then somehow to aggregate the amount by ID.
  </code></pre>
  
  <p>Obviously I know the code is wrong, and I have been thinking about plyr or reshape library, but was having difficulties of getting my hands on them.</p>
  
  <p>Any helps would be great! thanks guys!</p>