[Python] Pandas Merge - Elegant way to deal with same columns

Assume we have two data frames with columns as follows:

df1[['name', 'year', 'col1', 'col2', 'col3']]
df2[['name', 'year', 'col2', 'col3', 'col4']]


I want to do the merge of df1 and df2 by name and year with the condition to keep all value of col2 col3 on df1, if it is None then use value in df2

I know how to do this in traditional way by merging df1 and df2 then using ffill().

Since my process of cleaning data involve many steps of merging different df with same columns, it make the code not so clean when I keep have to using ffill() and drop columns. I don't know if pd.merge has any built-in option like that?

Sample code:

df1 = pd.DataFrame({'name': ['a', 'a', 'b', 'b', 'c', 'c'],
'year': [2000, 2001, 2002, 2003, 2004, 2005],
'col1': [1,2,3,4,5,6],
'col2': [0,2,4,6,8,None],
'col3': [1,3,5,7,None,9]})

df2 = pd.DataFrame({'name': ['b', 'b', 'c', 'c', 'd', 'd'],
'year': [2003, 2004, 2004, 2005, 2006, 2007],
'col2': [10,20,30,None,50,60],
'col3': [100,300,500,700,None,900],
'col4': [5,6,7,8,9,10]})


Input:

df1

name year col1 col2 col3
0 a 2000 1 0.00 1.00
1 a 2001 2 2.00 3.00
2 b 2002 3 4.00 5.00
3 b 2003 4 6.00 7.00
4 c 2004 5 8.00 NaN
5 c 2005 6 NaN 9.00


df2

name year col2 col3 col4
0 b 2003 10.00 100.00 5
1 b 2004 20.00 300.00 6
2 c 2004 30.00 500.00 7
3 c 2005 NaN 700.00 8
4 d 2006 50.00 NaN 9
5 d 2007 60.00 900.00 10


Output desired

name year col1 col2 col3 col4
0 a 2000 1.00 0.00 1.00 NaN
1 a 2001 2.00 2.00 3.00 NaN
2 b 2002 3.00 4.00 5.00 NaN
3 b 2003 4.00 6.00 7.00 5.00
4 b 2004 NaN 20.00 300.00 6.00
5 c 2004 5.00 8.00 500.00 7.00
6 c 2005 6.00 NaN 9.00 8.00
7 d 2006 NaN 50.00 NaN 9.00
8 d 2007 NaN 60.00 900.00 10.00

Continue reading...