Multiply multiple columns based on similar column IDs in Pandas

Question

i have two data frames DF1 and DF2. Both Df have the common columns ID. I want to perform the multiplication of each columns based on the common id column and make a new data frame to store it.

I have attached below the expected output.

my try

df1.soln = df1.merge(df2).assign(soln=lambda df: df.number * df.mult).soln

DF1

Df2

Expected DF after multiplication based on common ID in ID column

https://stackoverflow.com/questions/54964836/multiplying-columns-in-separate-pandas-dataframe-based-on-similar-column-values — Michael, Aug 29 '21 at 07:27
kindly provide reproducible examples. Also, is there anything wrong with the solution referred to by Michael? — sammywemmy, Aug 29 '21 at 07:43

score 1 · Accepted Answer · answered Aug 29 '21 at 08:55

1

You need to set 'ID' ax index first, then multiply:

(df1.set_index('ID')*df2.set_index('ID')).reset_index()

NB. You should provide your data as text or, better, DataFrame constructors, this would make your question non-ambiguous

answered Aug 29 '21 at 08:55

mozway

194,879
13
39
75

Hi @Mozway Code is working fine, but i want slight change in the equation, i did it but not getting expected output. Plz can you check below code for this equation. `abs((df1.set_index('ID')-df2.set_index('ID')/df1.set_index('ID')) * 100).reset_index()` that code giving wrong output. – user286076 Aug 29 '21 at 09:27
There are parentheses mismatches I think. Not sure what the expected output is as you didn't provide it, but try: `(abs((df1.set_index('ID')-df2.set_index('ID'))/df1.set_index('ID')) * 100).reset_index()` – mozway Aug 29 '21 at 12:10

score 0 · Answer 2 · answered Aug 29 '21 at 08:32

Maybe, you don't need merge:

>>> df1
     20/12/2020  21/12/2020  20/12/2020  21/12/2020
A_B           1           2           3           4
B_C           5           6           7           8
A_C           9          10          11          12

>>> df2
     20/12/2020  21/12/2020  20/12/2020  21/12/2020
A_C           9          10          11          12
A_B           1           2           3           4
B_C           5           6           7           8

>>> df1 * df2
     20/12/2020  21/12/2020  20/12/2020  21/12/2020
A_B           1           4           9          16
A_C          81         100         121         144
B_C          25          36          49          64

# Just for readability
>>> df1.mul(df2).reindex(df1.index)
     20/12/2020  21/12/2020  20/12/2020  21/12/2020
A_B           1           4           9          16
B_C          25          36          49          64
A_C          81         100         121         144

I am getting error. Row numbers are not same in my data frame. Error msg : can't multiply sequence by non-int of type 'str' — user286076, Aug 29 '21 at 08:43

Multiply multiple columns based on similar column IDs in Pandas

2 Answers2