Dynamically multiplying multiple columns pandas

Question

Let's say I have the two dfs:

      line amount#1    line amount#2    line amount#3    line amount#4
--  ---------------  ---------------  ---------------  ---------------
 0               95           nan              nan                 nan
 2               60             8.99             8.99              nan
 6              500            20               10                 nan


      taxrate#1    taxrate#2    taxrate#3    taxrate#4
--  -----------  -----------  -----------  -----------
 0         0.21       nan          nan             nan
 2         0.09         0.09         0.09          nan
 6         0.21         0.09         0             nan

I want to multiply them by each other and create a new column called TaxSubAmount per column, so 4 columns in total:

I've tried the following using list comprehension:

#TO-DO DYNAMICALLY UPDATE 
df1 = dflineamount.loc[:,'line amount#1':'line amount#4'].multiply(dftaxrate, axis="index")
df1.columns = ['TaxSubAmount{}'.format(x) for x in range(1, len(df1.columns) + 1)]

However, this does not work. Output:

      TaxSub1    TaxSub2    TaxSub3    TaxSub4    TaxSub5    TaxSub6    TaxSub7    TaxSub8
--  ---------  ---------  ---------  ---------  ---------  ---------  ---------  ---------
 0        nan        nan        nan        nan        nan        nan        nan        nan
 2        nan        nan        nan        nan        nan        nan        nan        nan
 6        nan        nan        nan        nan        nan        nan        nan        nan

Wanted output:

      TaxSub1    TaxSub2    TaxSub3    TaxSub4    
--  ---------  ---------  ---------  ---------  
 0       19,95        nan        nan        nan  
 2        5,4        0,81        0,81        nan  
 6        105        1,8         0        nan

n1colas.m · Accepted Answer · 2021-03-29 03:09:07Z

1

You could use a for loop using the column's name in association with f-strings.

import pandas as pd

dflineamount = pd.read_csv("dflineamount.csv")
dftaxrate = pd.read_csv("dftaxrate.csv")

df1 = pd.DataFrame()
for i in range(1, len(dflineamount.columns)+1):
    df1[f"TaxSub{i}"] = dflineamount[f"line amount#{i}"] * dftaxrate[f"taxrate#{i}"]

print(df1)

Output from df1

   TaxSub1  TaxSub2  TaxSub3  TaxSub4
0    19.95      NaN      NaN      NaN
1     5.40   0.8091   0.8091      NaN
2   105.00   1.8000   0.0000      NaN

answered Mar 29, 2021 at 3:09

n1colas.m

3,9894 gold badges19 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Dynamically multiplying multiple columns pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related