pandas merge on columns with different names and avoid duplicates
<p>How can I merge two pandas DataFrames on two columns with different names and keep one of the columns?</p>

<pre><code>df1 = pd.DataFrame({'UserName': [1,2,3], 'Col1':['a','b','c']})
df2 = pd.DataFrame({'UserID': [1,2,3], 'Col2':['d','e','f']})
pd.merge(df1, df2, left_on='UserName', right_on='UserID')
</code></pre>

<p>This provides a DataFrame like this</p>

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

<p>But clearly I am merging on <code>UserName</code> and <code>UserID</code> so they are the same. I want it to look like this. Is there any clean ways to do this? </p>

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

<p>Only the ways I can think of are either re-naming the columns to be the same before merge, or droping one of them after merge. I would be nice if pandas automatically drops one of them or I could do something like</p>

<pre><code>pd.merge(df1, df2, left_on='UserName', right_on='UserID', keep_column='left')
</code></pre>

<p>There is nothing really nice in it: it's meant to be keeping the columns as the larger cases like left right or outer joins would bring additional information with two columns. Don't try to overengineer your merge line, be explicit as you suggest</p>

<p>Solution 1:</p>

<pre><code>df2.columns = ['Col2', 'UserName']

pd.merge(df1, df2,on='UserName')
Out[67]: 
  Col1  UserName Col2
0    a         1    d
1    b         2    e
2    c         3    f
</code></pre>

<p>Solution 2:</p>

<pre><code>pd.merge(df1, df2, left_on='UserName', right_on='UserID').drop('UserID', axis=1)
Out[71]: 
  Col1  UserName Col2
0    a         1    d
1    b         2    e
2    c         3    f
</code></pre>

python pandas merge