Fillna in pandas using another pandas dataframe-CodePudding

Here's my inital dataset

sitename            L1        L2         L3
America      Continent       NaN     Others
Mexico             NaN   Spanish    America

This is reference dataset

sitename            L1        L2
America        Country   English
Mexico         Country   Spanish

This is expected output dataset

sitename            L1        L2         L3
America      Continent   English     Others
Mexico         Country   Spanish    America

The reference data is only works on Null vales

CodePudding user response：

For align by column sitemap convert it to index in both DataFrames and then replace missing values:

df = df1.set_index('sitemap').fillna(df2.set_index('sitemap')).reset_index()

Or:

df = df1.set_index('sitemap').combine_first(df2.set_index('sitemap')).reset_index()

Or:

df1 = df1.set_index('sitemap')
df2 = df2.set_index('sitemap')
df1.update(df2)

CodePudding user response：

You can use the:

df.fillna(df2)

If indexes are not the same, you can set them to be the same and use the same function:

df = df.set_index('measure')
df.fillna(df2.set_index('measure'))