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>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> and <code>sum</code> values in <code>list</code> in <code>df2</code> and then use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html" rel="nofollow"><code>drop</code></a> column <code>localid</code>:</p>

<pre><code>df1 = pd.DataFrame({'id':['D1','D2','D3','D4','D5','D6'],
                   'Field1':[12,15,11,7,55,8.8]})

print (df1)
   Field1  id
0    12.0  D1
1    15.0  D2
2    11.0  D3
3     7.0  D4
4    55.0  D5
5     8.8  D6

df2 = pd.DataFrame({'localid':['D1','D2','D3','D3','D9'],
                   'color':[['b'],['a'],['a','b'],['s','d'], ['a']]})

print (df2)
    color localid
0     [b]      D1
1     [a]      D2
2  [a, b]      D3
3  [s, d]      D3
4     [a]      D9
</code></pre>



<pre><code>df2 = df2.groupby('localid', as_index=False)['color'].sum()
print (df2)
  localid         color
0      D1           [b]
1      D2           [a]
2      D3  [a, b, s, d]
3      D9           [a]


dfmerged = pd.merge(df1, 
                    df2, 
                    left_on='id', 
                    right_on='localid', 
                    how='left')
             .drop('localid', axis=1)

print (dfmerged)
   Field1  id         color
0    12.0  D1           [b]
1    15.0  D2           [a]
2    11.0  D3  [a, b, s, d]
3     7.0  D4           NaN
4    55.0  D5           NaN
5     8.8  D6           NaN
</code></pre>

python pandas merge