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