0

I need to create a program with the purpose of cross-referencing personal info from a spreadsheet(s), to check for conflicts of interest between clients of 3 different law firms. All of this client data will originate from 3 different spreadsheets using the same template for naming/columns.

I plan to write this in Python, as python is what I'm most familiar with using, though I am not totally unfamiliar with node/js, and might consider another language if there's a particular library/module especially well-suited for this task.

I have not yet decided whether to bother compiling all data into a single spreadsheet (if it will be of any benefit), or just read in the 3 spreadsheets- but it will read/parse-in the data from various columns of spreadsheet(s) (name, age, address, date of incident, law firm).

My first idea was to then parse this info into a dictionary for each client's personal info ({lname: Jones, fname: Matt, DOB: 1-22-87}- then into 1 of 3 "superset" dictionaries for each law firm being looked at. The final task is to cross/compare the client's data and check for any duplicate individuals that are a client of more than one of the law firms being looked at. Or- any matches between any of the client info dictionaries contained within 2 or more of the 3 larger dictionaries.

to determine a "light match" I was thinking any matching last names with same DOB could be considered a match. Then these "light matches" could either be manually reviewed/vetted, or additional data points could be analyzed for these light matches, to determine, for certain whether they are the same individual.

So if 2 entries have the same last name and birthday, they will be considered a light match, and the data could then be processed further.

Questions-

  1. does this type of project sound like a good candidate for pandas? I've never used pandas before, but from what I've heard, this is exactly the type of thing pandas would be useful for. Any other modules that might be helpful in accomplishing this?

  2. Does anybody foresee any issues in the procedure I'm planning to use to "store and sort" the data sets? I.E. the "dictionaries within larger dictionaries" idea? Would it be better to use lists, and have a list of dictionaries for each law firm? Do you think there's a better/easier way to do this?

  3. Is there any straightforward way to handle case/capitalization/spacing/special character differences that might be found between 2 different entries for the same individual? For example, for date of birth, 1/1/1989 vs 01/01/1989 vs 1989/01/01 vs 1/1/89, etc. I know I can only do my best with the data I have, but wondering if there's a library or something that exists to help with this.

Any input or comments at all would be appreciated, I just want to make sure I have a sound plan going into this project, and that there aren't any major issues with any of my ideas.

Thank you!

2
  • Good luck Ontological and Semantic comparisons are hard. – Kain0_0 Mar 9 at 9:01
  • lol wonderful, thanks! – boog Mar 9 at 21:33
0

Blackboards and Advisors

Its probably a good idea to keep the spreedsheets in a generic data representation (kind of like a json data model, but for the kind of input documents you want to process). This generic data representation is a Blackboard. Create a blackboard per data source.

Advisors on the other hand are small programs that somehow select two sub-regions on a board, and then rate them for similarity.

The simplest advisor would be something like a query that selects sub-sections of each blackboard. Then has a separate function comparing the selected regions and rating their similarity. The matches should probably be record on a special matches blackboard (for simplicity).

For example comparing two date fields for equality, or for being within a few days of each other with a drop off in certainty the further they are apart.

A more complex advisor would operate on the results of the other advisors. They would select for those linked regions and look for clusters of these given a larger sub-region a rating.

For example combing the date of birth check and a couple of different styles of name check, to create a more complicated id check.

This can be turtles all the way down. More and more complex advisors feeding off the results of a simpler advisor. Just avoid feeding the advisors own results back into itself. This is a quick way to get an advisor to agree with itself. Also make sure the advisors don't form cycles: Advisor A -> Advisor B -> Advisor C -> Advisor A

It might also be helpful to have advisors give ratings on a scale of +1 to -1.

  • +1 = 100% match,
  • -1 = 100% does not match.

In this way you should have blackboard of matches from definitely to definitely not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.