Fill missing values matching index and columns of another dataframe

Question

I'm currently having a problem with filling the missing values of my dataframe using a different dataframe.

Data samples:

df1

A   B       C
b   1.0     1.0
d   NaN     NaN
c   2.0     2.0
a   NaN     NaN
f   NaN     NaN

df2

I've tried to follow the solution in this question but it would appear that it is only possible if the values you're looking up is present in both dataframes you're joining.

My attempt

mask = df1["B"].isnull()
df1.loc[mask, "B"] = df2[df1.loc[mask, "A"]].values

Error:

"None of [Index(['d', 'a', 'f'], dtype='object')] are in the [columns]"

Expected result:

A   B       C
b   1.0     1.0
d   4.0     8.0
c   2.0     2.0
a   3.0     7.0
f   NaN     NaN

Also, can it be used it fill two columns?

yatu · Accepted Answer · 2020-04-28 20:15:59Z

4

You can use combine_first here, which is exactly aimed at filling NaNs by matching with another dataframe's columns:

df1.set_index('A').combine_first(df2.set_index('A')).reset_index()

   A    B    C
0  a  3.0  7.0
1  b  1.0  1.0
2  c  2.0  2.0
3  d  4.0  8.0
4  f  NaN  NaN

edited Apr 28, 2020 at 20:15

answered Apr 28, 2020 at 20:09

yatu

88.7k12 gold badges93 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maku Over a year ago

I didn't know this method existed and that's even less complicated that it looks. Thanks man! Stay at home!

Collectives™ on Stack Overflow

Fill missing values matching index and columns of another dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related