[FIXED] Filter pandas dataframe column and replace values using a list condition

Issue

I have the following dataframe:

ID  Type                Job
1   Employee            Doctor
2   Contingent Worker   Doctor
3   Employee            Employee
4   Employee            Employee
5   Contingent Worker   Employee
6   Contingent Worker   Consultant
7   Contingent Worker   Trainee
8   Contingent Worker   SSS
9   Contingent Worker   Agency Worker
10  Contingent Worker   

And I have this list of possible acceptable values for everyone that has a type of Contingent Workers:

list = ['Agency Worker', 'Consultant']

I need to find a way to confirm if everyone under the type "Contingent Worker" have an accetpable value in "Job" and, if not (or blank value), replace that value for "Consultant" resulting in this dataframe:

ID  Type                Job
1   Employee            Doctor
2   Contingent Worker   Consultant
3   Employee            Employee
4   Employee            Employee
5   Contingent Worker   Consultant
6   Contingent Worker   Consultant
7   Contingent Worker   Consultant
8   Contingent Worker   Consultant
9   Contingent Worker   Agency Worker
10  Contingent Worker   Consultant

What would be the best way to achieve this result?

Solution

I would do it following way

df.loc[(df.Type=='Contingent Worker') & ~df.Job.isin(['Agency Worker', 'Consultant']),'Job'] = 'Consultant'
print(df)

gives output

                 Type            Job
ID
1            Employee         Doctor
2   Contingent Worker     Consultant
3            Employee       Employee
4            Employee       Employee
5   Contingent Worker     Consultant
6   Contingent Worker     Consultant
7   Contingent Worker     Consultant
8   Contingent Worker     Consultant
9   Contingent Worker  Agency Worker
10  Contingent Worker     Consultant

Explanation: select such rows where Type is Contingent Worker and (&) Job is not (~) one of values (isin) from your list, select Job column, set value to Consultant.

Answered By - Daweo