Match and compare two data frames using pandas and update based on missing value

Question

I have two Dataframe with the following values.

Dataframe 1
    Colum1   Primary    
0    test1     1234
1    test2     4567
2    test3     56789
3    test4     8799

Dataframe 2
    Colum1   Primary  Category      
0    test     56789      A
1    test     8799       B

For each row in column, check for primary value. if true, update Dataframe 1 with the Category Value. The final Output should be.

Dataframe 1
    Colum1   Primary    Category
0    test1     1234
1    test2     4567
2    test3     56789        A
3    test4     8799         B

Answer 1

try pandas.join:

df1 = df1.join(df2[['Primary','Category']], by='Primary', how='left')

df2[['Primary','Category']] - selects only 2 columns for joining

by='Primary' - key for joininig

how='left' - keeps all df1 rows even without matches in df2

Answer 2

Always faster and neater to use map where possible. Put the codes or df2 into dict

d=dict(zip(df2.Primary,df2.Category))
d

Map dict onto df1

df1['Category']=df1['Primary'].map(d).fillna('')
df1

Outcome