Pandas - adding columns, matching on index

Question

I'm trying to figure out if Panda's, when adding two series together, automatically matches on index or if it simply adds by element position. If its just by position, is there a way to get it to add on index? I've looked at merging, but I'm not very clear if the key in this case can be the index of both...

For example, if I have do DFs:

df1 = index  value
        0      10
        1      12
        2      15
        4      20


df2 = index  value
        0      10
        1      10
        3      10
        4      10

and I want to add df1[total] = df1[value] + df2[value] =

df1 = index  value
        0      20
        1      22
        2      15
        3      10
        4      30

Thanks for your help in advance!

Answer 1

Because of the intrinsic data alignment in pandas, you can use add with fill_value=0 and it will sum these two series based on index alignment.

df1.add(df2,fill_value=0)

Input:

df1 = pd.Series([10]*4,index=[0,1,3,4])

df2 = pd.Series([10,12,15,20], index=[0,1,2,4])

df1.add(df2,fill_value=0)

Output:

0    20.0
1    22.0
2    15.0
3    10.0
4    30.0
dtype: float64

Answer 2

Just do this:

pd.concat([df1,df2], axis=1).sum(axis=1)

pd.concat will merge the 2(or more) frames and match based on index. sum(axis=1) just sums across the rows.

Here's the working example:

#create the example data
df1 = pd.DataFrame({'index':[0,1,2,4],'value':[10,12,15,20]}).set_index('index')
df2 = pd.DataFrame({'index':[0,1,3,4],'value':[10,10,10,10]}).set_index('index')

The above will give you:

In [7]: pd.concat([df1,df2],axis=1).sum(axis=1)
Out[7]:
index
0    20.0
1    22.0
2    15.0
3    10.0
4    30.0
dtype: float64