Panda - join two dataframe based on one column from table and combining two column from another tabl-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