How to merge to two pandas data frames?
<p>I have two pandas data frames (see below).I want to merge them based on the id (Dataframe1) and localid(Dataframe2).
This code is not working; it creates additional rows in dfmerged as Dataframe2 may contains multiple same localid(e.g., D3). How can I merge these two dataframes and set the value of the 'color' column as NaN if the localid does not exists in the first dataframe (DataFrame1)?</p>

<pre><code>dfmerged = pd.merge(df1, df2, left_on='id', right_on='localid')
</code></pre>

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

<p>You should probably simplify <code>df2</code> to <strong>have no repeating keys</strong>, and then tell <code>pd.merge</code> to use <a href="http://pandas.pydata.org/pandas-docs/stable/merging.html#brief-primer-on-merge-methods-relational-algebra" rel="nofollow">union of keys from both frames</a> (with <code>how:'outer'</code>):</p>

<pre><code>import pandas as pd
df1 = pd.DataFrame({    'id':['D1','D2','D3','D4','D5','D6'],
                    'Field1':[  12,  15,  11,   7,  55, 8.8]})
df2 = pd.DataFrame({'localid':['D1','D2','D3','D3','D9'],
                      'color':[['blue','grey'],
                               ['yellow'],
                               ['black','red','green'],
                               ['white'],
                               ['blue']]})
dfmerged = pd.merge(df1, df2, left_on='id', right_on='localid')
dfmerged2 = pd.merge(df1, df2, left_on='id', right_on='localid', how='outer')
</code></pre>

<p>Which results in:</p>

<pre><code>&gt;&gt;&gt; dfmerged2
   Field1   id                color localid
0    12.0   D1         [blue, grey]      D1
1    15.0   D2             [yellow]      D2
2    11.0   D3  [black, red, green]      D3
3    11.0   D3              [white]      D3
4     7.0   D4                  NaN     NaN
5    55.0   D5                  NaN     NaN
6     8.8   D6                  NaN     NaN
7     NaN  NaN               [blue]      D9
</code></pre>

python pandas merge