Filling na values with merge from another dataframe

Question

I have a column with na values that I want to fill according to values from another data frame according to a key. I was wondering if there is any simple way to do so.

Example: I have a data frame of objects and their colors like this:

  object   color
0  chair   black
1   ball  yellow
2   door   brown
3   ball     **NaN**
4  chair   white
5  chair     **NaN**
6   ball    grey

I want to fill na values in the color column with default color from the following data frame:

  object default_color
0  chair         brown
1   ball          blue
2   door          grey

So the result will be this:

  object   color
0  chair   black
1   ball  yellow
2   door   brown
3   ball     **blue**
4  chair   white
5  chair     **brown**
6   ball    grey

Is there any "easy" way to do this?

Thanks :)

Answer 1

Use np.where and mapping by setting a column as index i.e

df['color']= np.where(df['color'].isnull(),df['object'].map(df2.set_index('object')['default_color']),df['color'])

or df.where

df['color'] = df['color'].where(df['color'].notnull(), df['object'].map(df2.set_index('object')['default_color'])) 

 object   color
0  chair   black
1   ball  yellow
2   door   brown
3   ball    blue
4  chair   white
5  chair   brown
6   ball    grey