pandas update dataframe by another dataframe with group by columns
<p>I have two dataframe like this </p>

<pre><code>df1 = pd.DataFrame({'A': ['1', '2', '3', '4','5'],
                  'B': ['1', '1', '1', '1','1'],
                  'C': ['A', 'A1', 'A2', 'A3','A4'],
                  'D': ['B0', 'B1', 'B2', 'B3','B4'],
                  'E': ['A', 'A', 'S', 'S','S']})
df2 = pd.DataFrame({'A': ['1', '6', '9', '4'],
                  'C': ['c', 'c1', 'c2', 'c3'],
                  'D': ['d1', 'd1', 'd2', 'd3']})
</code></pre>

<p>and I want to update df1's C,D columns by df2 when they get same column values in A (if df1['A']==df2['A'] then df1['C']=df2['C'] and df1['D']=df2['D'])</p>

<p>the answer should be like this</p>

<pre><code>      A  B   C   D  E
   0  1  1   c  d1  A
   1  2  1  A1  B1  A
   2  3  1  A2  B2  S
   3  4  1  c3  d3  S
   4  5  1  A4  B4  S
</code></pre>

<p>I tried <code>df1.update(df2)</code> but it just overwrite df1 by df2</p>

<pre><code>&gt;df1.update(df2)
&gt;       A  B   C   D  E
     0  1  1   c  d1  A
     1  6  1  c1  d1  A
     2  9  1  c2  d2  S
     3  4  1  c3  d3  S
     4  5  1  A4  B4  S
</code></pre>

<p>and I tried <code>pd.merge(df1, df2,how='inner' ,on=['A'])</code> still not what I want </p>

<pre><code>   A  B C_x D_x  E C_y D_y
0  1  1   A  B0  A   c  d1
1  4  1  A3  B3  S  c3  d3
</code></pre>

<p>can anyone give me some suggestion? 
Thank you</p>

<p>I think this will be more space efficient:</p>

<h2>Edit To Add</h2>

<p>This may be more efficient:</p>

<pre><code>In [22]: df1,df2 = df1.align(df2,join='left',axis=0)

In [23]: df1
Out[23]: 
   A  B   C   D  E
0  1  1   A  B0  A
1  2  1  A1  B1  A
2  3  1  A2  B2  S
3  4  1  A3  B3  S
4  5  1  A4  B4  S

In [24]: df2
Out[24]: 
     A    C    D
0    1    c   d1
1    6   c1   d1
2    9   c2   d2
3    4   c3   d3
4  NaN  NaN  NaN
</code></pre>

<p>Now you can do find a boolean array where the columns are equal, and use <code>loc</code> based assignment to modify <code>df1</code> inplace without needed the extra columns:</p>

<pre><code>In [26]: equal_rows = df1.A == df2.A

In [27]: df1.loc[equal_rows]
Out[27]: 
   A  B   C   D  E
0  1  1   A  B0  A
3  4  1  A3  B3  S

In [28]: df1.loc[equal_rows,['C','D']] = df2.loc[equal_rows,['C','D']]

In [29]: df1
Out[29]: 
   A  B   C   D  E
0  1  1   c  d1  A
1  2  1  A1  B1  A
2  3  1  A2  B2  S
3  4  1  c3  d3  S
4  5  1  A4  B4  S
</code></pre>

<p>And if you really need df2 as it was originally:</p>

<pre><code>In [30]: df2.dropna(how='all',axis=0, inplace=True)

In [31]: df2
Out[31]: 
   A   C   D
0  1   c  d1
1  6  c1  d1
2  9  c2  d2
3  4  c3  d3
</code></pre>

<h2>Original Answer</h2>

<p>Here is a clunky way that is not space efficient:</p>

<pre><code>In [13]: merged = pd.merge(df1,df2,how='left', on=['A'])

In [14]: merged
Out[14]: 
   A  B C_x D_x  E  C_y  D_y
0  1  1   A  B0  A    c   d1
1  2  1  A1  B1  A  NaN  NaN
2  3  1  A2  B2  S  NaN  NaN
3  4  1  A3  B3  S   c3   d3
4  5  1  A4  B4  S  NaN  NaN

In [15]: merged.fillna({'C_y':df1.C,'D_y':df1.D},inplace=True)
Out[15]: 
   A  B C_x D_x  E C_y D_y
0  1  1   A  B0  A   c  d1
1  2  1  A1  B1  A  A1  B1
2  3  1  A2  B2  S  A2  B2
3  4  1  A3  B3  S  c3  d3
4  5  1  A4  B4  S  A4  B4

In [16]: merged.drop(['C_x','D_x'],axis=1,inplace=True)

In [17]: merged
Out[17]: 
   A  B  E C_y D_y
0  1  1  A   c  d1
1  2  1  A  A1  B1
2  3  1  S  A2  B2
3  4  1  S  c3  d3
4  5  1  S  A4  B4
</code></pre>

<p>And if you want the original names:</p>

<pre><code>In [20]: merged.rename(columns={"C_y":'C','D_y':'D'},inplace=True)

In [21]: merged
Out[21]: 
   A  B  E   C   D
0  1  1  A   c  d1
1  2  1  A  A1  B1
2  3  1  S  A2  B2
3  4  1  S  c3  d3
4  5  1  S  A4  B4
</code></pre>

python pandas