Pandas: Join dataframes on selected columns

Question

I have two data frames as following

 Data Set A
 ID type   msg 
 1  High   Lets do
 2  Low    whats it 
 3  Medium thats it

 Data Set B 
 ID  Accounttype
 2   Facebook
 3   Linkedin

How can I get an updated table with help of join in pandas, it should look like an

Updated DatasetA 

ID Account    type  msg
 1            High   Lets do
 2  Facebook  Low    whats it 
 3  Linkedin  Medium thats it

I can easily do it in SQL with Update and inner join, how to perform it in pandas, I tried to do it, but most of the operations for append/ merge. Any help will be appreciated

Solution 1

Try this:

df4:
    
#      ID    type      msg
#   0   1    High   Letsdo
#   1   2     Low  whatsit
#   2   3  Medium  thatsit

df3:
    
#      ID Accounttype  xxx
#   0   2    Facebook   24
#   1   3    Linkedin   44

df4.merge(df3[['ID', 'Accounttype']], how='left').fillna("")
    
#      ID    type      msg Accounttype
#   0   1    High   Letsdo            
#   1   2     Low  whatsit    Facebook
#   2   3  Medium  thatsit    Linkedin

Solution 2

Seems there is no direct way to do it, so following is suggested

 a=b.merge(account,how='left',on='ID')

create a list of columns you want in final data set

 list=['ID','Account','type','msg'] 

 final=a[[col for col in list if col in b.columns]]

It will give you only desired columns after the left join

Comments

[Manu Sharma]

Makes complete Sense, Thanks Merlin