Creating a new variable with lots of conditions, combining blank and categorical values

Question

I have a dataframe that looks generally like this

ID Loc SL_Local SL_State TP Overall
12 54X          E        I  D
45 86I D        I        H  E
98 65R H        H        H  H

I want to create a new column (SL), that uses the SL_Local and SL_State columns with some if/then logic. Basically if SL_Local is blank then SL = SL_State, else if SL_Local = D and SL_State = I then SL = D, and so on. In total, there would be around 20 possible combinations.

I've found some examples of using if/else logic to create flags (like here), but that's not quite what I'm looking for. I've also found some questions about having multiple conditions (like this one), but that returns ValueError: operands could not be broadcast together with shapes (1131952,) (2,) and doesn't seem to work with the blank values. I tried this logic on a column with NaNs considering blanks might be too challenging to work with, but that wouldn't work either.

Answer 1

Use np.select

import pandas as pd
import numpy as np
df = pd.DataFrame({'ID':[12,45,98],'Loc':['54X','86I','65R'],'SL_Local':['','D','H'],'SL_State':['E','I','H'],'TP':['I','H','H'],'Overall':['D','E','H']})
df = df[['ID','Loc','SL_Local','SL_State','TP','Overall']]
df

Out:

    ID  Loc  SL_Local   SL_State    TP  Overall
0   12  54X                E        I      D
1   45  86I    D           I        H      E
2   98  65R    H           H        H      H

then do

condition_list = [
          (df['SL_Local'] =='') & (df['SL_State'] is not None),
          (df['SL_Local'] == 'D') & (df['SL_State'] == 'I')
           ]
choice_list = [df['SL_State'], 'D']

df['SL'] = np.select(condition_list, choice_list, default = '')
df

Out:

   ID  Loc    SL_Local    SL_State    TP    Overall    SL
0  12  54X                   E         I       D        E
1  45  86I        D          I         H       E        D
2  98  65R        H          H         H       H