Detecting Conflicts of Interest with Python

One way to identify conflicts of interest is to compare employee data to vendor data to see if there is an employee has set themselves up as a vendor to enable themselves to get funds from the company. 

Visit my blog to download the program and example data to test out how it works.

If you'd like to see how I did it see my video:

1.    Import Employee and Vendor Data

First, we will import employee data and vendor data. Note we are saving employee data as df_e and vendor data as df_v:

2.   Cleaning the Employee Data

We can see that the formats of phone numbers and social security numbers are not the same format. I will use the replace function to remove all non-numeric values:

3.   Cleaning the Vendor Data

4.    Identifying Phone Numbers Matches between Employees and Vendors

We will now look if the employee phone number matches any of the vendor phone numbers. It’s important to understand what’s going on here. We are filtering the vendor table df_v when the phone numbers in the vendor data match the phone number in the df_e phone number.

We will also reverse this filter so we can see which employees match phone numbers in the vendor data.

5.   Matching Social Security Numbers to Tax ID Numbers

We will reverse this filter to see the matches within the employee data

6.    Identifying Bank Routing Number Matches

We will reverse to see the match on the employee side

Note: that routing number is often not the best item to match because a single routing number can be common for a whole bank. It may be a better idea to match on account number.

7.    Identifying Bank Account Number Matches

We can see that this looks like a match on both the route and account number to follow up on.

How you escalate and follow up on these will depend on your organization, but that is how you can identify some potential conflicts of interest.