how to merge two dataframes based on a column in pandas

Question

I have two data frames,

 df1=pd.DataFrame({"Req":["Req 1","Req 2","Req 3"],"Count":[1,2,1]})        
    Req     Count
0   Req 1   1
1   Req 2   2
2   Req 3   1

df2=pd.DataFrame({"Req":["Req 1","Req 2"],"Count":[0,1]})

    Req    Count
0   Req 1   0
1   Req 2   1

I am trying to merge these df's based on "Req" column

My desired output is,

    Req    total  from_1 from_2
    Req 1   1       1     0
    Req 2   3       2     1
    Req 3   1       1     0

I tried pd.merge(df1, df2, on = "Req", ) but it is not giving my desired output, please help, thanks in advance!

Hint: Use `concat` and `groupby`: `pd.concat([df1, df2]).groupby('Req').sum()` — Mazdak, Jan 23 '18 at 07:36
That's why I said *Hint* :). It's actually the easy part just append them to the dataframe. (you already have the values) — Mazdak, Jan 23 '18 at 07:41

jezrael · Accepted Answer · 2018-01-23T07:37:25.450

You can use merge with left join, replace NaNs, rename columns and last add new column with assign:

df = (pd.merge(df1, df2, on = "Req", how='left')
        .fillna(0)
        .rename(columns={'Count_x':'from_1','Count_y':'from_2'})
        .assign(total=lambda x: x['from_1'] + x['from_2'])
      )
print (df)
   from_1    Req  from_2  total
0       1  Req 1     0.0    1.0
1       2  Req 2     1.0    3.0
2       1  Req 3     0.0    1.0

how to merge two dataframes based on a column in pandas

1 Answers1

Linked