python - Use pandas to replace values in one df with corresponding values in another

Question

This has been driving me crazy for a few hours. It feels like there is a simple solution that I'm close to but missing.

I have two DFs with two columns country_name and listed_country.

In df1 there are ~ 200 records and listed_country is either Yes or NaN.

In df2 there are ~12 records with listed_country == Yes for every record.

There are 3 records in df2 that I want to use to replace the corresponding NaN values in df1.

I've tried this a few different ways. First I narrowed down the delta between the two list to just take the country names I want to update. Then using list comparison with query and fillna. Even using inplace=True this runs but doesn't update df1.

eul = ['American Samoa', 'Guam', 'Virgin Islands, US'] 

df1.loc[:, ['country_name', 'listed_country']].query("country_name == @eul").fillna('Yes', inplace=True)

Then I tried pandas combine_first function, but it overwrites all NaN values in df1, which is not what I want:

df1.combine_first(df2)

Then I tried this solution proposed in another post, but it creates a new column, so again does not do what I want:

d = df2.set_index('country_name').listed_country

df1['listed_country'].replace(d, inplace=True)

Feels like this is a common use case and there must be a simple solution I'm overlooking?

Example dfs

df1:

country	listed_country
France	NaN
India	Yes

question from:https://stackoverflow.com/questions/65860141/use-pandas-to-replace-values-in-one-df-with-corresponding-values-in-another

Answer 1

Waitting for answers