Multiply dataframe columns based on conditions

Question

I have the following dataframe.

df

ID   Q_code    A_value   B_value   C_code  Score 
1    A_code     1         0         1        0.5
2    B_code     0         0         0        1
3    C_code     0         1         1        0
4    B_code     1         1         1        1

Based on ID and Q_code, for example under Q_code column for A_code and ID == 1 I would like to multiply, A_value X Score and update Score value. under Q_code column for B_code and ID == 2, I would like to multiply, B_value X Score and update Score value on that particular row. The same works for others.

ID   Q_code    A_value   B_value   C_code  Score 
1    A_code     1         0         1        0.5
2    B_code     0         0         0        0
3    C_code     0         1         1        0
4    B_code     1         1         1        1

I have tried this as follows but I would like to consider ID and Q_code

df['Score'] = df['A_value'] * df['Score']

Can any one help with this?

Is there a finite number of Q_code values? ALso, does ID value alter the final result in any way? — Will, Feb 17 '22 at 13:29
Yes. There are around 10 Q_code values. Yes, for different ID there might be the same Q_codes. — Hiwot, Feb 17 '22 at 13:30
The logic is unclear, seems to me that you make Score 0 if all Q_scores are 0 and leave it unchanged otherwise — mozway, Feb 17 '22 at 13:36
Not sure how ID is relevant? doesn't seem to alter the logic or does it? — Will, Feb 17 '22 at 13:36
Is your logic only for ID 1 and 2 or there is a general rule? — rpanai, Feb 17 '22 at 13:53
@Hiwot can you explicit the logic? If you perform B_value*Score, how is it not 0 for 0*0.5? Please break down the calculation — mozway, Feb 17 '22 at 14:07

score 0 · Answer 1 · answered Feb 17 '22 at 14:15

If I understood correctly you want to pick values according to Q_code column. In this case you can use this answer.

Data

import pandas as pd
data = {'ID': {0: 1, 1: 2, 2: 3, 3: 4},
 'Q_code': {0: 'A_code', 1: 'B_code', 2: 'C_code', 3: 'B_code'},
 'A_value': {0: 1, 1: 0, 2: 0, 3: 1},
 'B_value': {0: 0, 1: 0, 2: 1, 3: 1},
 'C_value': {0: 1, 1: 0, 2: 1, 3: 1},
 'Score': {0: 0.5, 1: 1.0, 2: 0.0, 3: 1.0}}

df = pd.DataFrame(data)

Use lookup to select the value from given column

df["new_value"] = df.lookup(
    df.index, 
    df["Q_code"].str.replace("code", "value"))

which returns

   ID  Q_code  A_value  B_value  C_value  Score  new_value
0   1  A_code        1        0        1    0.5          1
1   2  B_code        0        0        0    1.0          0
2   3  C_code        0        1        1    0.0          1
3   4  B_code        1        1        1    1.0          1

So now you just need to multiply df["Score"] * df["new_value"]

Multiply dataframe columns based on conditions

1 Answers1

Data

Use lookup to select the value from given column