IsIn for python dataframe-CodePudding

3 of the columns of my dataframe involve medical specialties, I am trying to do a isin on if column1 is in column2, its true... if column2 isin column 3 its true. so the expression would be true. I am also factoring out for nulls, and if everything matches. Swapping the Or between the first line to and & either gives alot of false positives, or seems like it doesnt do anything. The 2nd and 4th line should also be correct.

Dataframe https://i.stack.imgur.com/IqqaZ.png how it looks after doing the formatting.

dfMaster = pd.dataframe({'Specialty_M' : ['Telemetry', 'M/S; Clinic; ER', 'Healthcare', 'M/S; Telemetry', 'ICU', 'Clinic', 'ICU'], 'Specialty_AM': ['ICU', 'ER', 'HLTH', 'M/S', 'ICU', 'PEDS', 'ICU'], 'Specialty_BE' : ['ICU', 'ER', '','M/S', 'ICU', '', 'ICU']})
dfMaster['SPECIALTY Okay?'] = np.NaN

comparisonSpecialty999 = np.where(
    (dfMaster['Specialty_BE'].isin(dfMaster['Specialty_AM']) & dfMaster['Specialty_AM'].isin(dfMaster['Specialty_M'])) |
    ((dfMaster['Specialty_M'].str.upper() == dfMaster['Specialty_BE'].str.upper()) &
    (dfMaster['Specialty_M'].str.upper() == dfMaster['Specialty_AM'].str.upper())) |
    (
            (dfMaster['Specialty_M'] != dfMaster['Specialty_M']) &
            (dfMaster['Specialty_AM'] != dfMaster['Specialty_AM']) &
            (dfMaster['Specialty_BE'] != dfMaster['Specialty_BE']))
    | ~Exists, 'Correct', 'Fix')

dfMaster['SPECIALTY Okay?'] = comparisonSpecialty999

CodePudding user response：

Try with apply and in:

dfMaster['SPECIALTY Okay?'] = (np.where(dfMaster.apply(lambda x: (x["Specialty_BE"] in x["Specialty_AM"]) and 
                                                       (x["Specialty_AM"] in x["Specialty_M"]), 
                                                       axis=1), 
                                        "Correct", 
                                        "Fix")
                               )

>>> dfMaster
       Specialty_M Specialty_AM Specialty_BE SPECIALTY Okay?
0        Telemetry          ICU          ICU             Fix
1  M/S; Clinic; ER           ER           ER         Correct
2       Healthcare         HLTH                          Fix
3   M/S; Telemetry          M/S          M/S         Correct
4              ICU          ICU          ICU         Correct
5           Clinic         PEDS                          Fix
6              ICU          ICU          ICU         Correct