Adding missing rows and filling them with zeros in pandas python dataframe

Question

I have 2 dataframes That I'm trying to combine so I used the stackoverflow example with some modifications to demonstrate how they look:

Dataframe1:

left	center	right
a	0.1	Tee
b	0.2	The
c	0.1	Three
d	0.3	ee

Dataframe2:

left	center
a	0.4
c	0.7
b	0.5

Looking for:

left	center	center2	right
a	0.1	0.4	Tee
b	0.2	0.5	The
c	0.1	0.7	Three
d	0.3	0	ee

So essentially dataframe 2 is missing some rows and the index/order of the column that I want to join them on is different.

score 2 · Answer 1 · answered Mar 25 '23 at 10:03

Try:

out = (
    df1.merge(df2, on="left", how="outer")
    .rename(columns={"center_x": "center", "center_y": "center2"})
    .fillna(0)
)
print(out)

Prints:

  left  center  right  center2
0    a     0.1    Tee      0.4
1    b     0.2    The      0.5
2    c     0.1  Three      0.7
3    d     0.3     ee      0.0

score 0 · Answer 2 · answered Mar 25 '23 at 10:09

import pandas as pd
import numpy as np

df1 = pd.DataFrame({'left' : ['a', 'b', 'c', 'd'],
                    'center' : [0.1, 0.2, 0.1, 0.3],
                    'right' : ['Tee', 'The', 'Three','ee']})

df2 = pd.DataFrame({'left' : ['a', 'c', 'b'],
                    'center' : [0.4, 0.7, 0.5]})

r = df1.merge(df2, how='outer',on='left').fillna(0)[['left', 'center_x', 'center_y', 'right']]
r.columns = ['left', 'center_1', 'center_2', 'right']

print(r)

  left  center_1  center_2  right
0    a       0.1       0.4    Tee
1    b       0.2       0.5    The
2    c       0.1       0.7  Three
3    d       0.3       0.0     ee

Adding missing rows and filling them with zeros in pandas python dataframe

2 Answers2