Adding values in columns from 2 dataframes

Question

I have 2 dataframes as below, some of the index values could be common between the two and I would like to add the values across the two if same index is present. The output should have all the index values present (from 1 & 2) and their cumulative values.

    Build   
2.1.3.13    2   
2.1.3.1     1   
2.1.3.15    1   
2.1.3.20    1   
2.1.3.8     1   
2.1.3.9     1   
    
Ref_Build       
 2.1.3.13   2   
 2.1.3.10   1   
 2.1.3.14   1   
 2.1.3.17   1   
 2.1.3.18   1   
 2.1.3.22   1

For example in the above case 2.1.3.13 should show 4 and the remaining 11 of them with 1 each.

What's the efficient way to do this? I tried merge etc., but some of those options were giving me 'intersection' and not 'union'.

do you need a new dataframe?

ansev
– ansev

2022-05-20 08:06:34 +00:00
Commented May 20, 2022 at 8:06 — ansev
– ansev, Commented May 20, 2022 at 8:06

ansev · Accepted Answer · 2022-05-20 08:11:14Z

3

Use Series.add and Series.fillna

df1['Build'].add(df2['Ref_Build']).fillna(df1['Build']).fillna(df2['Ref_Build'])

2.1.3.1     1.0
2.1.3.10    1.0
2.1.3.13    4.0
2.1.3.14    1.0
2.1.3.15    1.0
2.1.3.17    1.0
2.1.3.18    1.0
2.1.3.20    1.0
2.1.3.22    1.0
2.1.3.8     1.0
2.1.3.9     1.0
dtype: float64

Or:

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

2.1.3.13    4.0
2.1.3.1     1.0
2.1.3.15    1.0
2.1.3.20    1.0
2.1.3.8     1.0
2.1.3.9     1.0
2.1.3.10    1.0
2.1.3.14    1.0
2.1.3.17    1.0
2.1.3.18    1.0
2.1.3.22    1.0
dtype: float64

edited May 20, 2022 at 8:11

answered May 20, 2022 at 8:04

ansev

31k5 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ynjxsjmh · Accepted Answer · 2022-05-20 08:25:08Z

3

You can try merge with outer option or concat on columns

out = pd.merge(df1, df2, left_index=True, right_index=True, how='outer').fillna(0)
# or
out = pd.concat([df1, df2], axis=1).fillna(0)

out['sum'] = out['Build'] + out['Ref_Build']

# or with `eval` in one line
out = pd.concat([df1, df2], axis=1).fillna(0).eval('sum = Build + Ref_Build')

print(out)

          Build  Ref_Build  sum
2.1.3.13    2.0        2.0  4.0
2.1.3.1     1.0        0.0  1.0
2.1.3.15    1.0        0.0  1.0
2.1.3.20    1.0        0.0  1.0
2.1.3.8     1.0        0.0  1.0
2.1.3.9     1.0        0.0  1.0
2.1.3.10    0.0        1.0  1.0
2.1.3.14    0.0        1.0  1.0
2.1.3.17    0.0        1.0  1.0
2.1.3.18    0.0        1.0  1.0
2.1.3.22    0.0        1.0  1.0

edited May 20, 2022 at 8:25

answered May 20, 2022 at 7:44

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Adding values in columns from 2 dataframes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related