Python Forum

Hi,

I need to merge this two dataframes:

df1

pais   año  cantidad
 0  Chile  2000        10
 1  Chile  2001        11
 2  Chile  2002        12

df2

pais   año  cantidad
 0  Chile  1999         0
 1  Chile  2000         0
 2  Chile  2001         0
 3  Chile  2002         0
 4  Chile  2003         0

Right now I'm trying this code:

df=pd.merge(df1,df2,on=['pais','año','cantidad'],how='outer')

And getting this result:

pais 	año 	cantidad
0 	Chile 	2000 	10
1 	Chile 	2001 	11
2 	Chile 	2002 	12
3 	Chile 	1999 	0
4 	Chile 	2000 	0
5 	Chile 	2001 	0
6 	Chile 	2002 	0
7 	Chile 	2003 	0

Which is the simply union of both df's. I need something that produces this result:

pais 	año 	cantidad
0   Chile   1999  0
1 	Chile 	2000 	10
2 	Chile 	2001 	11
3 	Chile 	2002 	12
4 	Chile 	2002 	0
5 	Chile 	2003 	0

Any ideas? Thank!!

Hope this helps;

df1 = pd.DataFrame(data = {'pais':['Chile','Chile','Chile'],
                             'año':[2000,2001,2002],
                             'cantidad':[10,11,12],})
    
df2 = pd.DataFrame(data = {'pais':['Chile','Chile','Chile','Chile','Chile'],
                             'año':[1999,2000,2001,2002,2003],
                             'cantidad':[0,0,0,0,0],})

df1=df1.set_index(['pais','año'])
df2=df2.set_index(['pais','año'])    

df1_2 = df1.add(df2,fill_value=0)
df1_2.reset_index()

Output:
Out[280]: 
    pais   año  cantidad
0  Chile  1999       0.0
1  Chile  2000      10.0
2  Chile  2001      11.0
3  Chile  2002      12.0
4  Chile  2003       0.0