How to combine two data frames using merge or VLOOKUP functionality in Pandas-CodePudding

I have two data frame:

df1

    EmID    Employee Name
0   12345   Frist Person
1   35658   Second Person
2   65865   Third Person
3   28568   Foo Person
4   26699   Boo Person

df2

    Manager ID  Subordinate ID
1   28568       35658   
2   12466       12358
3   35658       12345

I want to merge this two data frames so that my output should looks like:

    Manger Name  Emp Name      
1   Foo Person  Second Person

I have used df2.merge(df1, left_on = ManagerID, right_on = EmID) but I didn't get what I am looking for. I have also try to use for loop still doesn't give me the desired out put. Any idea? Thanks :)

CodePudding user response：

Assuming all the id's in df2 are also present in df1, we can set the index of df1 to EmpID and select Employee Name column to create replacement series, then use DataFrame.replace to substitute the values in df2.

df3 = df2.replace(df1.set_index('EmID')['Employee Name'])
df3.columns = ['Manager name', 'Emp name']

>>> df3

    Manager name       Emp name
1     Foo Person  Second Person

CodePudding user response：

Share the output you are getting using above code. I can't comment so writing here as an answer