Pandas populate new dataframe column based on matching columns in another dataframe
<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>

<p>see <a class='doc-link' href="http://stackoverflow.com/documentation/pandas/1966/merge-join-and-concatenate/23978/what-is-the-difference-between-join-and-merge#t=201610021451405843568">What is the difference between join and merge</a></p>

<p>consider the following dataframes <code>df</code> and <code>df2</code></p>

<pre><code>df = pd.DataFrame(dict(
        AUTHOR_NAME=list('AAABBCCCCDEEFGG'),
        title=      list('zyxwvutsrqponml')
    ))

df2 = pd.DataFrame(dict(
        AUTHOR_NAME=list('AABCCEGG'),
        title      =list('zwvtrpml'),
        CATEGORY   =list('11223344')
    ))
</code></pre>

<p><strong><em>option 1</em></strong><br>
<code>merge</code></p>

<pre><code>df.merge(df2, how='left')
</code></pre>

<p><strong><em>option 2</em></strong><br>
<code>join</code></p>

<pre><code>cols = ['AUTHOR_NAME', 'title']
df.join(df2.set_index(cols), on=cols)
</code></pre>

<hr>

<p><strong><em>both options yield</em></strong></p>

<p><a href="http://i.stack.imgur.com/AYxvS.png" rel="nofollow"><img src="http://i.stack.imgur.com/AYxvS.png" alt="enter image description here"></a></p>

python pandas merge populate