CODEWITHSUNDEEP
pythonpandassumproduct
user27074
user27074

Reputation: 637

Excel Sumproduct equivalent in Pandas

I have a simple exercise, but cannot find a simple solution to it in Pandas. My dataframe has the following form: 

         A      B      C      D              A1          B1          C1            D1
0       0.0    0.0    0.0  0.090909       0.247747    0.162398    0.381459       0.208396
1       0.0    0.0    0.0  0.090909       0.250811    0.168629    0.374599       0.205961
2       0.0    0.0    0.0  0.090909       0.252173    0.168521    0.374551       0.204755

I want to calculate the sum product of the columns using always AA1 + BB1 .. .D*D1

I found a solution, but it is a bit messy and not suitable as soon as the number of columns will increase. Any pythonic way to solve this? Using Excel I would you the sumproduct function.

Current solution:

df['SP'] = df[["A", 'A1']].prod(axis=1) + df[["B", 'B1']].prod(axis=1) + df[["C", 'C1']].prod(axis=1) + df[["D", 'D1']].prod(axis=1)

Should lead to:

 df[SP]
0.018945
0.018724
0.018614

Upvotes: 0

Views: 6059

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150785

Similar to Chris' comment, but you can ignore the column names and pass the range instead:

num_base_cols=4
num_repeat = 2

col_groups = np.tile(np.arange(num_base_cols), num_repeat)

df.groupby(col_groups, axis=1).prod().sum(1)

Output:

0    0.018945
1    0.018724
2    0.018614
dtype: float64

Upvotes: 2

NickBraunagel
NickBraunagel

Reputation: 1599

You can identify the columns on the 'left' and 'right' and then multiply and sum(axis=1).

Note that I'm assuming all of your columns follow the col_name/col_name<number> convention (if this is not the case, you will have to manually identify the column names in the lists cols_left and cols_right).

import pandas as pd

df = pd.DataFrame(data={
    'A': [0,0,0],
    'B': [0,0,0],
    'C': [0,0,0],
    'D': [0.090909, 0.090909, 0.090909],
    'A1': [0.247747,0.250811, 0.252173],
    'B1': [0.162398, 0.168629, 0.168521],
    'C1': [0.381459, 0.374599, 0.374551],
    'D1': [0.208396, 0.205961, 0.204755]
})

cols_left = [c for c in df.columns if '1' not in c]
cols_right = [c for c in df.columns if '1' in c]

out = (df[cols_left] * df[cols_right].values).sum(axis=1)

out:

0    0.018945
1    0.018724
2    0.018614

Upvotes: 1

Related Questions