pandas df.fillna - filling NaNs after outer join with correct values

Question

I have two dataframes, sharing some columns together.
I'm trying to:

1) Merge the two dataframes together, i.e. adding the columns which are different:

diff = df2[df2.columns.difference(df1.columns)]
merged = pd.merge(df1, diff, how='outer', sort=False, on='ID')

Up to here, everything works as expected.

2) Now, to replace the NaN values with the values of df2

merged = merged[~merged.index.duplicated(keep='first')]
merged.fillna(value=df2)

And it is here that I get:

pandas.core.indexes.base.InvalidIndexError

I don't have any duplicates, and I can't find any information as to what can cause this.

What exactly are you trying to do? I suppose, that there are some values in the index of the `merged` data.frame that are not in the `df2` data.frame? — Lenka Vraná, Dec 13 '18 at 16:52

score 3 · Accepted Answer · answered Dec 16 '18 at 09:55

3

The solution to this problem is to use a different method - combine_first() this way, each row with missing data is filled with data from the other dataframe, as can be seen here Merging together values within Series or DataFrame columns

answered Dec 16 '18 at 09:55

golanor

score 0 · Answer 2 · answered Dec 13 '18 at 17:06

0

In case, number of rows changes because of the merge, fillna sometimes cause error. Try the following!

merged.fillna(df2.groupby(level=0).transform("mean"))

2 Answers2