Pandas - merge two DataFrames with Identical Column Names

Question

I have two Data Frames with identical column names and identical IDs in the first column. With the exception of the ID column, every cell that contains a value in one DataFrame contains NaN in the other. Here's an example of what they look like:

ID    Cat1    Cat2    Cat3
1     NaN     75      NaN
2     61      NaN     84
3     NaN     NaN     NaN


ID    Cat1    Cat2    Cat3
1     54      NaN     44
2     NaN     38     NaN
3     49      50      53

I want to merge them into one DataFrame while keeping the same Column Names. So the result would look like this:

ID    Cat1    Cat2    Cat3
1     54      75      44
2     61      38      84
3     49      50      53

I tried:

df3 = pd.merge(df1, df2, on='ID', how='outer')

Which gave me a DataFrame containing twice as many columns. How can I merge the values from each DataFrame into one?

Roger Fan · Accepted Answer · 2014-08-05 18:00:35Z

7

You probably want df.update. See the documentation.

df1.update(df2, raise_conflict=True)

answered Aug 5, 2014 at 18:00

Roger Fan

5,05534 silver badges38 bronze badges

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

Comments

Slavatron · Accepted Answer · 2014-08-05 18:00:35Z

6

In this case, the combine_first function is appropriate. (http://pandas.pydata.org/pandas-docs/version/0.13.1/merging.html)

As the name implies, combine_first takes the first DataFrame and adds to it with values from the second wherever it finds a NaN value in the first.

So:

df3 = df1.combine_first(df2)

produces a new DataFrame, df3, that is essentially just df1 with values from df2 filled in whenever possible.

answered Aug 5, 2014 at 18:00

Slavatron

2,3885 gold badges30 silver badges42 bronze badges

Comments

mccandar · Accepted Answer · 2018-07-11 15:21:48Z

0

You could also just change the NaN values in df1 with non-NaN values in df2.

df1[pd.isnull(df1)] = df2[~pd.isnull(df2)]

answered Jul 11, 2018 at 15:21

mccandar

7988 silver badges16 bronze badges

Collectives™ on Stack Overflow

Pandas - merge two DataFrames with Identical Column Names

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related