Map and compare 2 columns using conditional statement Python

Question

I am looking to find and separate taxable values from the total value using an conditional statement.

If first 2 characters from column Tax_no1 and Tax_no2 are different then we have to calculate the Tax percentage 18% from the Amount column and update the value in value3 column

Value3 = Amount*18/100

If first 2 characters from column Tax_no1 and Tax_no2 are same then we have to calculate the Tax percentage 18% from the Amount column divide the calculated value by 2 and update the values in value1 and value2 column

value1 = (Amount * 18/100)/2, value1 = (Amount * 18/100)/2

OR

value1 = Amount * 9/100 , value2 = Amount * 9/100

Input Data :

Tax_no1    Tax_No2     Amount      value1    value2    value3
AZ0001B    AZ0001B     35000
BZ0002A    CD0002A     12800.00
25CA895    25CA895     28967.90
NY78615    DY78615     367899.9
LO10985    LO10985     156789
01256NY    02256NY     2890657

Expected Output Value:

Tax_no1    Tax_No2     Amount      value1    value2    value3
AZ0001B    AZ0001B     35000        3150      3150      NaN
BZ0002A    CD0002A     12800.00     NaN       NaN       2304
25CA895    25CA895     28967.90     2607.11   2607.11   NaN
NY78615    DY78615     367899.9     NaN       NaN       66221.98
LO10985    LO10985     156789       14111.01  14111.01  NaN
01256NY    02256NY     2890657      NaN       NaN       520318.26

Script I have tried to use :

df['Tax1'] = df['Tax_no1'].str[:2]
df['Tax2'] = df['Tax_no2'].str[:2]

if df['Tax1'] != df['Tax2']:
   df['value3'] = Amount * 18/100
else:
   df['value1'] = Amount * 9/100
   df['value2'] = Amount * 9/100

Andrej Kesely · Accepted Answer · 2021-04-26 19:02:18Z

3

You can use boolean indexing. First create a mask comparing the prefixes and then you can use for example np.where:

m = df["Tax_No1"].str[:2] == df["Tax_No2"].str[:2]

df["value1"] = np.where(m, df.Amount * 9 / 2 / 100, np.nan)
df["value2"] = np.where(m, df.Amount * 9 / 2 / 100, np.nan)
df["value3"] = np.where(~m, df.Amount * 18 / 100, np.nan)

print(df)

Prints:

   Tax_No1  Tax_No2     Amount     value1     value2      value3
0  AZ0001B  AZ0001B    35000.0  1575.0000  1575.0000         NaN
1  BZ0002A  CD0002A    12800.0        NaN        NaN    2304.000
2  25CA895  25CA895    28967.9  1303.5555  1303.5555         NaN
3  NY78615  DY78615   367899.9        NaN        NaN   66221.982
4  LO10985  LO10985   156789.0  7055.5050  7055.5050         NaN
5  01256NY  02256NY  2890657.0        NaN        NaN  520318.260

answered Apr 26, 2021 at 19:02

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

2 Comments

NKJ Over a year ago

Thank You @Andrej Kesely, Need your help to modify the conditional statement as : how to execute this line ""m = df["Tax_No1"].str[:2] == df["Tax_No2"].str[:2]"" only when it satisfy the below condition "if df["Tax_No1"] != df["Tax_No2"]:", Please help as not able to create it.

Andrej Kesely Over a year ago

@NKJ It isn't clear what are you trying to do. m = df["Tax_No1"].str[:2] == df["Tax_No2"].str[:2] creates boolean mask that you use np.where, df.loc etc.

KJDII · Accepted Answer · 2021-04-26 19:10:06Z

Here is another way to accomplish this.


import pandas as pd


def tax_logic(row):
    if row['Tax_no1'][:2] != row['Tax_No2'][:2]:
       row['value3'] = row['Amount'] * 18/100
    else:
       row['value1'] = row['Amount'] * 9/100
       row['value2'] = row['Amount'] * 9/100
    return row

df = df.apply(tax_logic, axis=1)

print(df)

      Amount  Tax_No2  Tax_no1     value1     value2      value3
0    35000.0  AZ0001B  AZ0001B   3150.000   3150.000         NaN
1    12800.0  CD0002A  BZ0002A        NaN        NaN    2304.000
2    28967.9  25CA895  25CA895   2607.111   2607.111         NaN
3   367899.9  DY78615  NY78615        NaN        NaN   66221.982
4   156789.0  LO10985  LO10985  14111.010  14111.010         NaN
5  2890657.0  02256NY  01256NY        NaN        NaN  520318.260

Collectives™ on Stack Overflow

Map and compare 2 columns using conditional statement Python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest