Return value based on condition from multiple columns

user_6700502 0
Viewed 210

I have a dataframe with 4 columns, Basically i am trying to create another column and return values meeting condition using if statement

If NR/HL1 is not equal to 0, then outputColumn(NR/HL) = NR/HL1

if NR/HL1 is equals to 0, then outputColumn(NR/HL) = NR/HL2

if NR/HL1 is equals to 0 and NR/HL2 is equal to 0, then outputColumn(NR/HL) = NR/HL3

SKU NR/HL1 NR/HL2 NR/HL3  OutputColumn(NR/HL)
123  10     20     0         10
456  0      30     20        30
567  0      0      40        40
890  10     20     50        10

I used the below code, it works ok but not 100% accurate. Always misses one or the other condition. If you check the image of output condition 3 is meeting but it return default value. NR/HL3 !=0, But still NR/HL ==0

def f(AC_off_trade):
    if AC_off_trade['NR/HL1'] != 0:
        return AC_off_trade['NR/HL1']
    if AC_off_trade['NR/HL1'] == 0:
        val = AC_off_trade['NR/HL2']
    if AC_off_trade['NR/HL1'] == 0 and AC_off_trade['NR/HL2'] == 0:
        return AC_off_trade['NR/HL3']
    else:
        return 0
AC_off_trade['NR/HL'] = AC_off_trade.apply(f,axis=1)

updated code

#defining condition
hl1_equal_0_condition = AC_off_trade["NR/HL1"]==0.0    
hl2_equal_0_contition = AC_off_trade["NR/HL2"]==0.0
#default value
AC_off_trade.loc[:,"NR/HL"]=0
#setting values depending on condition
AC_off_trade.loc[~hl1_equal_0_condition, "NR/HL"] = AC_off_trade["NR/HL1"]
AC_off_trade.loc[hl1_equal_0_condition, "NR/HL"] = AC_off_trade["NR/HL2"]
AC_off_trade.loc[hl1_equal_0_condition & hl2_equal_0_contition, "NR/HL"] = AC_off_trade["NR/HL3"]

enter image description here

pandas numpy excel python
1 Answers

First of all you should try avoid using apply in favour of vectorization since the performance is way better. Then you can use conditions in order to obtain your desired output:

df = pd.DataFrame({"SKU": [123, 456, 567, 890], "NR/HL1" : [10, 0, 0, 10], "NR/HL2": [20, 30, 0, 20],"NR/HL3": [0, 20, 40, 50]})

# Defining conditions 
hl1_equal_0_condition = df["NR/HL1"]==0    
hl2_equal_0_contition = df["NR/HL2"]==0

# Setting the default value
df.loc[:,"NR/HL"] = 0

# Setting the values deppeding on tje conditions
df.loc[~hl1_equal_0_condition, "NR/HL"] = df["NR/HL1"]
df.loc[hl1_equal_0_condition, "NR/HL"] = df["NR/HL2"]
df.loc[hl1_equal_0_condition & hl2_equal_0_contition, "NR/HL"] = df["NR/HL3"]

Output:

    SKU NR/HL1  NR/HL2  NR/HL3  NR/HL
0   123 10      20      0       10
1   456 0       30      20      30
2   567 0       0       40      40
3   890 10      20      50      10
user_125365400
Related