jeudi 24 septembre 2020

Filtering employee dataframe with conflicted roles

I have the following data frame that represents the employee number, the department they are and their role code in the company that can be "1" or "2". On the column "Department Name" you can either have the department the employee has their role (naming convention being "XX:Dept Name" where XX is the country code) or, in case the employee has that role for the entire country, instead of the department name you will find the name of the country the employee has the role. The data frame would look like this:

  Department Name     Employee Number      Role Code   
0  AU:Dept1               1000                     1
1  All Australia          1000                     2
2  AU:Dept7               1000                     1
4  CZ:Dept3               1001                     2
5  CZ:Dept3               1001                     1
6  CZ:Dept4               1001                     1 
7  All Poland             1002                     1
8  PL:Dept1               1002                     2
9  PL:Dept2               1002                     1
10 ES:Dept1               1002                     2
11 All Singapore          1003                     1
12 All Singapore          1003                     2

Employees can have either only Role 1 OR Role 2 in each unique department name and, if the role is for the entire country, it means this person has the role for all departments in that country. Still, as you can see in the sample, we could have employees that have in the database a role assigned to "all country" but also to a specific department of that country (for example a role 1 for "All Poland" but also a role 1 for a department in Poland as you can see the example, which would be redundant and also should be flagged).

I need to create a code that would return the rows which the employee number has:

  • Role 1 and Role 2 in the same Department
  • Role 1 and Role 2 for the entire country
  • Role 1 for the entire country and role 2 in any department of that country
  • Role 2 for the entire country and role 1 in any department of that country

In this case, the output would look like this (returning all offending rows)

  Department Name     Employee Number      Role Code   
0  AU:Dept1               1000                     1
1  All Australia          1000                     2
2  AU:Dept7               1000                     1
4  CZ:Dept3               1001                     2
5  CZ:Dept3               1001                     1
7  All Poland             1002                     1
8  PL:Dept1               1002                     2
9  PL:Dept2               1002                     1
11 All Singapore          1003                     1
12 All Singapore          1003                     2

What would be the best way to do that?

Aucun commentaire:

Enregistrer un commentaire