An alternative approach is to use join setting the index of the right hand side DataFrame to the columns ['username', 'column1']:

df1.join(df2.set_index(['username', 'column1']), on=['userid', 'column1'], how='left')

The output of this join merges the matched keys from the two differently named key columns, userid and username, into a single column named after the key column of df1, userid; whereas the output of the merge maintains the two as separate columns. To illustrate, consider the following example:

import numpy as np
import pandas as pd

df1 = pd.DataFrame({'ID': [1,2,3,4,5,6], 'pID' : [21,22,23,24,25,26], 'Values' : [435,33,45,np.nan,np.nan,12]})
##    ID  Values  pID
## 0   1   435.0   21
## 1   2    33.0   22
## 2   3    45.0   23
## 3   4     NaN   24
## 4   5     NaN   25
## 5   6    12.0   26

df2 = pd.DataFrame({'ID' : [4,4,5], 'pid' : [24,25,25], 'Values' : [544, 545, 676]})
##    ID  Values  pid
## 0   4     544   24
## 1   4     545   25
## 2   5     676   25

pd.merge(df1, df2, how='left', left_on=['ID', 'pID'], right_on=['ID', 'pid']))
##    ID  Values_x  pID  Values_y   pid
## 0   1     435.0   21       NaN   NaN
## 1   2      33.0   22       NaN   NaN
## 2   3      45.0   23       NaN   NaN
## 3   4       NaN   24     544.0  24.0
## 4   5       NaN   25     676.0  25.0
## 5   6      12.0   26       NaN   NaN

df1.join(df2.set_index(['ID','pid']), how='left', on=['ID','pID'], lsuffix='_x', rsuffix='_y'))
##    ID  Values_x  pID  Values_y
## 0   1     435.0   21       NaN
## 1   2      33.0   22       NaN
## 2   3      45.0   23       NaN
## 3   4       NaN   24     544.0
## 4   5       NaN   25     676.0
## 5   6      12.0   26       NaN

Here, we also need to specify lsuffix and rsuffix in join to distinguish the overlapping column Value in the output. As one can see, the output of merge contains the extra pid column from the right hand side DataFrame, which IMHO is unnecessary given the context of the merge. Note also that the dtype for the pid column has changed to float64, which results from upcasting due to the NaNs introduced from the unmatched rows.

This aesthetic output is gained at a cost in performance as the call to set_index on the right hand side DataFrame incurs some overhead. However, a quick and dirty profile shows that this is not too horrible, roughly 30%, which may be worth it:

sz = 1000000 # one million rows
df1 = pd.DataFrame({'ID': np.arange(sz), 'pID' : np.arange(0,2*sz,2), 'Values' : np.random.random(sz)})
df2 = pd.DataFrame({'ID': np.concatenate([np.arange(sz/2),np.arange(sz/2)]), 'pid' : np.arange(0,2*sz,2), 'Values' : np.random.random(sz)})

%timeit pd.merge(df1, df2, how='left', left_on=['ID', 'pID'], right_on=['ID', 'pid'])
## 818 ms ± 33.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit df1.join(df2.set_index(['ID','pid']), how='left', on=['ID','pID'], lsuffix='_x', rsuffix='_y')
## 1.04 s ± 18.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

you need to make county_ID as index for the right frame:

frame_2.join ( frame_1.set_index( [ 'county_ID' ], verify_integrity=True ),
               on=[ 'countyid' ], how='left' )

for your information, in pandas left join breaks when the right frame has non unique values on the joining column. see this bug.

so you need to verify integrity before joining by , verify_integrity=True

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

Solution 1:

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

Solution 2:

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

1. It merges according to the ordering of left_on and right_on, i.e., the i-th element of left_on will match with the i-th of right_on.

   In the example below, the code on the top matches A_col1 with B_col1 and A_col2 with B_col2, while the code on the bottom matches A_col1 with B_col2 and A_col2 with B_col1. Evidently, the results are different.

   

2. As can be seen from the above example, if the merge keys have different names, all keys will show up as their individual columns in the merged dataframe. In the example above, in the top dataframe, A_col1 and B_col1 are identical and A_col2 and B_col2 are identical. In the bottom dataframe, A_col1 and B_col2 are identical and A_col2 and B_col1 are identical. Since these are duplicate columns, they are most likely not needed. One way to not have this problem from the beginning is to make the merge keys identical from the beginning. See bullet point #3 below.

3. If left_on and right_on are the same col1 and col2, we can use on=['col1', 'col2']. In this case, no merge keys are duplicated.

   df1.merge(df2, on=['col1', 'col2'])
   

   

4. You can also merge one side on column names and the other side on index too. For example, in the example below, df1's columns are matched with df2's indices. If the indices are named, as in the example below, you can reference them by name but if not, you can also use right_index=True (or left_index=True if the left dataframe is the one being merged on index).

   df1.merge(df2, left_on=['A_col1', 'A_col2'], right_index=True)
   # or
   df1.merge(df2, left_on=['A_col1', 'A_col2'], right_on=['B_col1', 'B_col2'])
   

   

5. By using the how= parameter, you can perform LEFT JOIN (how='left'), FULL OUTER JOIN (how='outer') and RIGHT JOIN (how='right') as well. The default is INNER JOIN (how='inner') as in the examples above.

6. If you have more than 2 dataframes to merge and the merge keys are the same across all of them, then join method is more efficient than merge because you can pass a list of dataframes and join on indices. Note that the index names are the same across all dataframes in the example below (col1 and col2). Note that the indices don't have to have names; if the indices don't have names, then the number of the multi-indices must match (in the case below there are 2 multi-indices). Again, as in bullet point #1, the match occurs according to the ordering of the indices.

   df1.join([df2, df3], how='inner').reset_index()