How to sum a column of one pandas dataframe to each column of another dataframe?

Question

Situation:

Both dataframes (df1 and df2) have the same three indices, e.g. "A", "B", "C". df1 and df2 differ in the number of their columns. All cells in df1 and df2 are filled with data of the type float.

df1:

   AA    BB   CC   DD
A  28.0   2.3  2.0  113
B  24.0   2.4  2.4  116
C  33.5  10.0  4.0    0

df2:

    AAA   BBB   CCC    DDD    EEE
A  1.01  4.04  7.07  10.10  13.13
B  2.02  5.05  8.08  11.11  14.14
C  3.03  6.06  9.09  12.12  15.15

Goal:

After the column chosen from df2 (e.g. "BBB") is summed with each column of df1, the results should be stored in a new dataframe (df_new). df_new should be of the format of df1 (number of columns and rows) and have the same column-names and indices as df1.

new_df:

    AA      BB      CC      DD
A   32.04   6.34    6.04    117.04
B   29.05   7.45    7.45    121.05
C   39.56   16.06   10.06   6.06

My approach:

To sum the 3rd column of df2 with each column of df1, I tried:

df_new = df1.add(df2.iloc[:,2])

Result:

As desired, df_new matches df1 in structure and labels. The problem is: All cells show "NaN" instead of the desired sums as floats.

df_new:

    AA  BB  CC  DD
A   NaN NaN NaN NaN
B   NaN NaN NaN NaN
C   NaN NaN NaN NaN

@JohnGalt thanks for the quick reply. I hope these examples help. — N. Hinkel
– N. Hinkel, Commented Jul 5, 2017 at 9:44

Zero · Accepted Answer · 2017-07-05 09:55:43Z

1

Here's one way, using add

In [293]: df1.add(df2['BBB'], axis=0)
Out[293]:
      AA     BB     CC      DD
A  32.04   6.34   6.04  117.04
B  29.05   7.45   7.45  121.05
C  39.56  16.06  10.06    6.06

answered Jul 5, 2017 at 9:55

Zero

77.4k22 gold badges153 silver badges153 bronze badges

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

1 Comment

N. Hinkel Over a year ago

Thanks a lot! This does exactly what I needed.

Collectives™ on Stack Overflow

How to sum a column of one pandas dataframe to each column of another dataframe?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related