Multiply column values with another column values of two dataframes-CodePudding

There are two dataframe

print(priority)
ConnectionID Value
1     CN01491920     2
3     CN01491922     2
5     CN01491931     1
7     CN01491928     2
9     CN01491935     2
..           ...   ...
589   CN01493318     1
591   CN01493288     1
593   CN01493307     1
595   CN01493322     2
597   CN01493321     2

[299 rows x 2 columns]

print(client)
   ConnectionID  Value
0    CN01493292   1400
1    CN01493278    864
2    CN01493318    616
3    CN01493346    413
4    CN01492425    412
5    CN01493307    211
6    CN01492290    111
7    CN01492933    106
8    CN01493112     96
9    CN01492697     94
10   CN01492209     93
11   CN01493277     93
12   CN01492456     93
13   CN01493259     93
14   CN01493342     93
15   CN01492533     93
16   CN01491983     93
17   CN01492705     93
18   CN01492417     93
19   CN01492828     93
20   CN01493321     93
21   CN01492218     93
22   CN01492297     93
23   CN01492145     93
24   CN01492142     93
25   CN01492147     93
26   CN01493007     93
27   CN01492221     93
28   CN01492374     93
29   CN01493377     93
30   CN01492713     93
31   CN01493010     93
32   CN01491970     93
33   CN01492556     93
34   CN01492822     93
35   CN01492044     93
36   CN01492841     93
37   CN01493280     93
38   CN01492175     93
39   CN01492782     93
40   CN01493015     93
41   CN01492214     93
42   CN01493098     89
43   CN01493026     89
44   CN01493290     84
45   CN01492618     80
46   CN01493014     77
47   CN01492848     76
48   CN01491931     68
49   CN01492513     60
50   CN01492910     49
51   CN01493260     33
52   CN01492277     25
53   CN01492229     17
54   CN01493053     13
55   CN01492672      9
[55 rows x 2 columns]

I would like to multiply client['Value'] with the priority['Value'] where client['ConnectionID']==priority['ConnectionID']. However, there should be a condition if priority['ConnectionID'] is not found in client['ConnectionID'], then the multiplication is ignored in this case. The output of this is stored in another dataframe. I tried with the following, but it did not work:

for i in range(len(client)):
   for j in range(len(priority)):
       if(client['ConnectionID'].loc[i]==priority['ConnectionID'].loc[j]):
           client['Multiplication'] = client['Value'].loc[i]*priority['Value'].loc[j]

Does anybody have an idea how to do in a simple way?

CodePudding user response：

So, here I have mainly used the merge function of pandas.I hope this helps:

client.rename(columns={'value': 'client_val'}, inplace=True)
merged = priority.merge(client, how='left')
merged['multiplicated_value'] = merged['value'] * merged['client_val']

CodePudding user response：

One way could be:

priority = priority.assign(mul=(priority['ConnectionID']\
               .map(client.set_index('ConnectionID')\
                    ['Value'])*priority['Value']).fillna(priority['Value']))

print(priority)

    ConnectionID  Value    mul
1     CN01491920      2    2.0
3     CN01491922      2    2.0
5     CN01491931      1   68.0
7     CN01491928      2    2.0
9     CN01491935      2    2.0
589   CN01493318      1  616.0
591   CN01493288      1    1.0
593   CN01493307      1  211.0
595   CN01493322      2    2.0
597   CN01493321      2  186.0