What algorithms can be used to derive matching rules between known matches in datasets?

Question

Lets say I have two datasets with different column names except for a unique ID key

Table 1 CSV

first_name,middle_name,last_name,uno,id
John,D,Smith,1,1
John,C,Smith,1,2
John,B,Doe,1,3
Suzy,C,Q,1,4

Table 2 CSV

fname,mname,lname,one,id
John,D,Smith,1,1
John,C,Smith,1,2
John,B,Doe,1,3
Suzy,C,Q,1,4

John D Smith is user ID #1 and is in both tables.

Is there a pre-built algorithm, package or tool that can do the following.

• Join across tables where id is the same
• For known matches, try to identify what rules could have been used to match the two records together.
• Test hypothesis, like "fname and first_name are the same, is that enough to produce the target 'id' variable? Let me check other data. No. What about fname + lname?
• Test if assertions hold true against other known matches.

End output would be

table 1 (first_name, middle_name, lastname) are the best join against

table 2 (fname, mname, lname)

Answer 1

What you're trying to do is called "entity resolution" or "record linkage" (you can do a more thorough search in google). A typical approach is to treat it as classification, using as rows each combination of entries from one table matched against the other, label being whether they are a match or not; and any features that you can think about such as edit distance between the two, number of characters in common, whether they start and end with the same letter, largest common subsequence, number of characters in each, etc.

(a more computationally efficient approach is to treat it as one-class classification).