I have the following dataframe:
JavaScript
x
12
12
1
ID Type Job
2
1 Employee Doctor
3
2 Contingent Worker Doctor
4
3 Employee Employee
5
4 Employee Employee
6
5 Contingent Worker Employee
7
6 Contingent Worker Consultant
8
7 Contingent Worker Trainee
9
8 Contingent Worker SSS
10
9 Contingent Worker Agency Worker
11
10 Contingent Worker
12
And I have this list of possible acceptable values for everyone that has a type of Contingent Workers:
JavaScript
1
2
1
list = ['Agency Worker', 'Consultant']
2
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:
JavaScript
1
12
12
1
ID Type Job
2
1 Employee Doctor
3
2 Contingent Worker Consultant
4
3 Employee Employee
5
4 Employee Employee
6
5 Contingent Worker Consultant
7
6 Contingent Worker Consultant
8
7 Contingent Worker Consultant
9
8 Contingent Worker Consultant
10
9 Contingent Worker Agency Worker
11
10 Contingent Worker Consultant
12
What would be the best way to achieve this result?
Advertisement
Answer
I would do it following way
JavaScript
1
3
1
df.loc[(df.Type=='Contingent Worker') & ~df.Job.isin(['Agency Worker', 'Consultant']),'Job'] = 'Consultant'
2
print(df)
3
gives output
JavaScript
1
13
13
1
Type Job
2
ID
3
1 Employee Doctor
4
2 Contingent Worker Consultant
5
3 Employee Employee
6
4 Employee Employee
7
5 Contingent Worker Consultant
8
6 Contingent Worker Consultant
9
7 Contingent Worker Consultant
10
8 Contingent Worker Consultant
11
9 Contingent Worker Agency Worker
12
10 Contingent Worker Consultant
13
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
.