I have a large dataset (2M entries) of people, but many people have multiple entries in the database with slightly (or significantly) different identifying information. For example, I may have J. Doe and John Doe, or I may have John Doe with an accompanying email address and John Doe without an accompanying email address.
I've been looking at different clustering algorithms but nothing seems well-suited to what I'm doing, which is to aggregate the entries based on rules like the following:
- guess first and last names based on whether one of the names is written in all capitals
- aggregate "J. Doe" and "J. Doe" if email addresses match
- aggregate "J. Doe" into "John Doe" if no other people have first name starting with "J" and last name "Doe"
With a smaller dataset this would be a relatively straightforward task to do just with some simple rules, but with the number of entries I have the aggregation tasks can get really slow and the logic gets pretty convoluted. My current solution (based around using the fulltext search function within my database to find similar entries, adding hashes based on those results, and then aggregating based on a mix of hashes and types of ambiguity) works, but every time I try to run it or update it it just screams that it's the kind of problem somebody else has already solved. But I haven't been able to find a solution.
Are there algorithms that will do what I want based on rules like this? Or specific packages or software that might be helpful? Or am I approaching this problem completely wrong?
Thanks!
(Please note though that I am well aware that there are many different ways to incorrectly aggregate identities (e.g. that J. Doe could mean John Doe or James Doe), so I don't need warnings against trying to aggregate things.)
dictionarywill be your best friend to have a key, value matching in your case. $\endgroup$