Pandas - join two dataframes based on one column from table and combining two columns from another t-CodePudding

I am trying to join two tables based on one column from one table and two column from other table

Table a

name, designation
Mr. james john, manager
Mr. jim james, tester
Mr. abe james, developer

Table b

first name, last name, emp id
james,john,1
jim, james,2
abe,james,3

I want to join table a name column with table b combining "Mr. " first name last name.

CodePudding user response：

Here's a way to do what your question asks:

res = a.join(b.assign(name='Mr. '   b['first name']   ' '   b['last name']).set_index('name'), on='name')

Input:

dataframe a:
             name designation
0  Mr. james john     manager
1   Mr. jim james      tester
2   Mr. abe james   developer

dataframe b:
  first name last name  emp id
0      james      john       1
1        jim     james       2
2        abe     james       3

Output:

             name designation first name last name  emp id
0  Mr. james john     manager      james      john       1
1   Mr. jim james      tester        jim     james       2
2   Mr. abe james   developer        abe     james       3