Pandas DF merge based on matching cell name

user_8329052 0
Viewed 72

I have a dataframe DF1 that contains:

Asset Name Type Score Grade
Apple fruit nan nan
Pear fruit nan nan
Banana fruit nan nan

I have DF2 that contains:
Asset Name Score Grade
Orange 40 C
Banana 100 A
Pear 60 B
Potato 10 F
Apple 70 E 
import pandas as pd

data1 = {'Asset Name':['Apple', 'Pear', 'Banana'], \
         'Type':['fruit', 'fruit', 'fruit'],'Score':['nan', 'nan','nan'], \
             'Grade':['nan','nan','nan']}

data2 = {'Asset Name':['Orange', 'Banana','Pear','Potato','Apple'], \
         'Score':[40,100,60,10,70], 'Grade':['C','A','B','F','E']}
    
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

I can use:

combined = pd.concat([DF1, Df2]).groupby('Asset Name').first()

This gives me the following:

Asset Name Score Grade Type
Apple 70 E fruit
Banana 100 A fruit
Orange 40 C None
Pear 60 B fruit
Potato 10 F None

This is not quite what I want. I only want the score and grade for Apple, Pear and Banana to be added, as they are in DF1. If the asset name is not in DF1 and Df2 then then Score and grade should not be updated.


Similarly if i try to use:

combined = df2.combine_first(df1)

Asset Name Grade Score Type
Orange C 70 fruit
Banana A 100 fruit
Pear B 40 fruit
Potato F 10 fruit
Apple E 70 None

I get elements I also don't want, as I only want the elements from DF1 to be updated.

How can I only update rows in DF1 matching 'Asset Name' in DF2, and add the data from DF2 to those assets in DF1?


What I would like to achieve is the following:

Asset Name Score Grade Type
Apple 70 C fruit
Pear 60 B fruit
Banana 100 A fruit

Thanks

pandas dataframe concatenation merge python
1 Answers

IIUC use:

df = df2.set_index('Asset Name').combine_first(df1.set_index('Asset Name')).dropna().reset_index()
print (df)
  Asset Name Grade Score   Type
0      Apple     E    70  fruit
1     Banana     A   100  fruit
2       Pear     B    60  fruit
user_29010020
Related