CODEWITHSUNDEEP
pythonpandasmultiple-columns
Jose Vasquez
Jose Vasquez

Reputation: 159

Multiply many columns pandas

I have a data frame like this, but with many more columns and I would like to multiply each two adjacent columns and state the product of the two in a new column beside it and call it Sub_pro and at the end have the total sum of all Sub_pro in a column called F_Pro and reduce the precision to 3 decimal places. I don't know how to get the Sub_pro columns. Below is my code.

import pandas as pd
df = pd.read_excel("C:dummy")
df['F_Pro'] = ("Result" * "Attribute").sum(axis=1)
df.round(decimals=3)
print (df)

Input

Id Result   Attribute       Result1    Attribute1
 1 0.5621     0.56            536        0.005642
 2 0.5221     0.5677          2.15          93
 3 0.024564   5.23            6.489         8
 4 11.564256  4.005           0.45556       5.25
 5 0.6123     0.4798          0.6667        5.10

Desire Output

id  Result  Attribute   Sub_Pro     Result1     Attribute1   Sub_pro1       F_Pro
1   0.5621    0.56      0.314776        536     0.005642    3.024112    3.338888
2   0.5221    0.5677    0.29639617      2.15    93          199.95      200.2463962
3   0.024564  5.23      0.12846972      6.489   8           51.912      52.04046972
4   11.564256 4.005     46.31484528     0.45556 5.25        2.39169     48.70653528
5   0.6123    0.4798    0.29378154      0.6667  5.1         3.40017     3.69395154

Upvotes: 3

Views: 963

Answers (3)

jpp
jpp

Reputation: 164843

Here's one way using NumPy and a dictionary comprehension:

# extract NumPy array for relevant columns
A = df.iloc[:, 1:].values
n = int(A.shape[1] / 2)

# calculate products and feed to pd.DataFrame
prods = pd.DataFrame({'Sub_Pro_'+str(i): np.prod(A[:, 2*i: 2*(i+1)], axis=1) \
                      for i in range(n)})

# calculate sum of product rows
prods['F_Pro'] = prods.sum(axis=1)

# join to original dataframe
df = df.join(prods)

print(df)

   Id     Result  Attribute    Result1  Attribute1  Sub_Pro_0   Sub_Pro_1  \
0   1   0.562100     0.5600  536.00000    0.005642   0.314776    3.024112   
1   2   0.522100     0.5677    2.15000   93.000000   0.296396  199.950000   
2   3   0.024564     5.2300    6.48900    8.000000   0.128470   51.912000   
3   4  11.564256     4.0050    0.45556    5.250000  46.314845    2.391690   
4   5   0.612300     0.4798    0.66670    5.100000   0.293782    3.400170   

        F_Pro  
0    3.338888  
1  200.246396  
2   52.040470  
3   48.706535  
4    3.693952

Upvotes: 0

Ben.T
Ben.T

Reputation: 29635

Because you have several columns named kind of the same, here is one way using filter. To see how it works, on your df, you do df.filter(like='Result') and you get the columns where the name has Result in it:

      Result    Result1
0   0.562100  536.00000
1   0.522100    2.15000
2   0.024564    6.48900
3  11.564256    0.45556
4   0.612300    0.66670

You can create an array containing the columns 'Sub_Pro':

import numpy as np
arr_sub_pro = np.round(df.filter(like='Result').values* df.filter(like='Attribute').values,3)

and you get the values of the columns sub_pro such as arr_sub_pro:

array([[3.1500e-01, 3.0240e+00],
       [2.9600e-01, 1.9995e+02],
       [1.2800e-01, 5.1912e+01],
       [4.6315e+01, 2.3920e+00],
       [2.9400e-01, 3.4000e+00]])

Now you need to add them at the right position in the dataframe, I think a loop for is necessary

for nb, col in zip( range(arr_sub_pro.shape[1]), df.filter(like='Attribute').columns):
    df.insert(df.columns.get_loc(col)+1, 'Sub_pro{}'.format(nb), arr_sub_pro[:,nb])

here I get the location of the column Attibut(nb) and insert the value from column nb of arr_sub_pro at the next position

To add the column 'F_Pro', you can do:

df.insert(len(df.columns), 'F_Pro', arr_sub_pro.sum(axis=1))

the final df looks like:

   Id     Result  Attribute  Sub_pro0    Result1  Attribute1  Sub_pro1  \
0   1   0.562100     0.5600     0.315  536.00000    0.005642     3.024   
1   2   0.522100     0.5677     0.296    2.15000   93.000000   199.950   
2   3   0.024564     5.2300     0.128    6.48900    8.000000    51.912   
3   4  11.564256     4.0050    46.315    0.45556    5.250000     2.392   
4   5   0.612300     0.4798     0.294    0.66670    5.100000     3.400   

     F_Pro  
0    3.339  
1  200.246  
2   52.040  
3   48.707  
4    3.694

Upvotes: 1

alvaro nortes
alvaro nortes

Reputation: 630

import pandas as pd

src = "/opt/repos/pareto/test/stack/data.csv"
df = pd.read_csv(src)


count = 0
def multiply(x):
    res = x.copy()
    keys_len = len(x)
    idx = 1
    while idx + 1 < keys_len:
        left = x[idx]
        right = x[idx + 1]
        new_key = "sub_prod_{}".format(idx)
        # Multiply and round to three decimal places.
        res[new_key] = round(left * right,3)
        idx = idx + 1

    return res

res_df = df.apply(lambda x: multiply(x),axis=1)

It solve the problem but you need now order de columns you can iterate over the keys instead of make a deep copy of the full row. I hope that the code help you.

Upvotes: 1

Related Questions