title: Pandas populate new dataframe column based on matching columns in another dataframe
body: <p>I have a <code>df</code> which contains my main data which has one million <code>rows</code>. My main data also has 30 <code>columns</code>. Now I want to add another column to my <code>df</code> called <code>category</code>. The <code>category</code> is a <code>column</code> in <code>df2</code> which contains around 700 <code>rows</code> and two other <code>columns</code> that will match with two <code>columns</code> in <code>df</code>.</p>
  
  <p>I begin with setting an <code>index</code> in <code>df2</code> and <code>df</code> that will match between the frames, however some of the <code>index</code> in <code>df2</code> doesn't exist in <code>df</code>.</p>
  
  <p>The remaining columns in <code>df2</code> are called <code>AUTHOR_NAME</code> and <code>CATEGORY</code>.</p>
  
  <p>The relevant column in <code>df</code> is called <code>AUTHOR_NAME</code>. </p>
  
  <p>Some of the <code>AUTHOR_NAME</code> in <code>df</code> doesn't exist in <code>df2</code> and vice versa.</p>
  
  <p>The instruction I want is: when <code>index</code> in <code>df</code> matches with <code>index</code> in <code>df2</code> and <code>title</code> in <code>df</code> matches with <code>title</code> in <code>df2</code>, add <code>category</code> to <code>df</code>, else add NaN in <code>category</code>.</p>
  
  <p>Example data:</p>
  
  <pre><code>df2
             AUTHOR_NAME              CATEGORY
  Index       
  Pub1        author1                 main
  Pub2        author1                 main
  Pub3        author1                 main
  Pub1        author2                 sub
  Pub3        author2                 sub
  Pub2        author4                 sub
  
  
  df
              AUTHOR_NAME     ...n amount of other columns        
  Index       
  Pub1        author1                 
  Pub2        author1     
  Pub1        author2 
  Pub1        author3
  Pub2        author4 
  
  expected_result
              AUTHOR_NAME             CATEGORY   ...n amount of other columns
  Index
  Pub1        author1                 main
  Pub2        author1                 main
  Pub1        author2                 sub
  Pub1        author3                 NaN
  Pub2        author4                 sub
  </code></pre>
  
  <p>If I use <code>df2.merge(df,left_index=True,right_index=True,how='left', on=['AUTHOR_NAME'])</code> my <code>df</code> becomes three times bigger than it is supposed to be.</p>
  
  <p>So I thought maybe merging was the wrong way to go about this. What I am really trying to do is use <code>df2</code> as a lookup table and then return <code>type</code> values to <code>df</code> depending on if certain conditions are met.</p>
  
  <pre><code>def calculate_category(df2, d):
      category_row = df2[(df2["Index"] == d["Index"]) &amp; (df2["AUTHOR_NAME"] == d["AUTHOR_NAME"])]
      return str(category_row['CATEGORY'].iat[0])
  
  df.apply(lambda d: calculate_category(df2, d), axis=1)
  </code></pre>
  
  <p>However, this throws me an error:</p>
  
  <pre><code>IndexError: ('index out of bounds', u'occurred at index 7614')
  </code></pre>