How to merge two pandas dataframes on index but fill missing values

Question

I have two dataframes

df

df1

  y
1 1
3 1

And I want to merge them on the index, but still keep the indexes that aren't present in df1. This is my desired output

I have tried merging on index, like this

pd.merge(df, df1s, left_index=True, right_index=True)

But that gets rid of the index values not in df1. For example:

  x y
1 1 1
3 1 1

This is not what I want. I have tried both outer and inner join, to no avail. I have also tried reading through other pandas merge questions, but can't seem to figure out my specific case here. Apologies if the merge questions are redundant, but again, I cannot figure out how to merge the way I would like in this certain scenario. Thanks!

I'd recommend join when doing a left merge on index: You can join and fill nas with zero: df.join(df1).fillna(0) — David Erickson
– David Erickson, Commented Dec 23, 2020 at 23:55
Your code is missing the how parameter : pd.merge(df, df1, left_index=True, right_index=True, how="left").fillna(0) — sammywemmy
– sammywemmy, Commented Dec 23, 2020 at 23:56

wwnde · Accepted Answer · 2020-12-23 23:51:46Z

3

Try to concatenate on rows and fill NaNs with 0

pd.concat([df,df1], axis=1).fillna(0)



  x    y
0  1  0.0
1  1  1.0
2  1  0.0
3  1  1.0
4  1  0.0

answered Dec 23, 2020 at 23:51

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

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

Comments

Paul Mundt · Accepted Answer · 2020-12-24 00:21:12Z

0

No need for any complicated merging, you can just copy the column over directly, fill the NaNs, and set the dtype. You can either do this directly, or with pd.concat():

pd.concat([df1, df2], axis=1).fillna(0).astype(int)

   x  y
0  1  0
1  1  1
2  1  0
3  1  1
4  1  0

answered Dec 24, 2020 at 0:21

Paul Mundt

5096 silver badges10 bronze badges

2 Comments

David Erickson Over a year ago

How is this different from the other answer aside from the fact that you added astype(int)? If someone has already posted a solution, it is best not to post the same exact solution.

Paul Mundt Over a year ago

There were no comments posted at the time I started filling my response out, so I didn't notice that someone else had come up with a similar solution. That being said, this is still the only solution that produces the desired output asked in the original question.

Collectives™ on Stack Overflow

How to merge two pandas dataframes on index but fill missing values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related