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)