How can I sum two dataframe's totals in a new dataframe?

user_18373125 0
Viewed 29

I got the following code:

df_A = pd.DataFrame ({'a1': [2,2,3,5,6],   
   'a2' : [8,6,3,5,2], 
   'a3': [7,4,3,0,6] })

df_B = pd.DataFrame ({'b1': [9,5,3,7,6],   
       'b2' : [0,6,4,5,3], 
       'b3': [7,8,8,0,10] })

This looks like:

    a1  a2  a3
0   2   8   7
1   2   6   4
2   3   3   3
3   5   5   0
4   6   2   6

and:

    b1  b2  b3
0   9   0   7
1   5   6   8
2   3   4   8
3   7   5   0
4   6   3   10

I want to have the sum of each column so I did:

total_A = df_A.sum()
total_B = df_B.sum()

The outcome for total_A was:

    0
a1  18
a2  24
a3  20

for total_B:

    0
b1  30
b2  18
b3  33

And then both totals needs to be summed as well. But I am getting NaNs

I prefer to get a df with column named total_1, total_2, total_3 and as key the total values for each column:

total_1, total_2, total_3 
  48        42       53

So 48 is sum of column a1 + column b1; 42 is sum of column a2 + column b2 and 53 is sum of column a3 + column b3.

Can someone help me please?

dataframe sum python
1 Answers

The indexes are not aligned, so pandas won't sum a1 with b1. You need to align the index and there are many different ways/

You can to use the underlying numpy data on B to avoid index aligment:

df_A.sum()+df_B.sum().values

or rename B columns to match that of A:

df_A.add(df_B.set_axis(df_A.columns, axis=1)).sum()

output:

a1    48
a2    42
a3    53
dtype: int64

or set a common index:

(df_A
 .rename(columns=lambda x: x.replace('a', 'total_'))
 .add(df_B.rename(columns=lambda x: x.replace('b', 'total_')))
 .sum()
)

output:

total_1    48
total_2    42
total_3    53
dtype: int64
as numpy array:
(df_A.to_numpy()+df_B.to_numpy()).sum(0)

output:

array([48, 42, 53])
user_163434640
Related