Techniques for finding near duplicate records

by

If you’re just doing small batches that are relatively well-formed, then the compare.linkage() or compare.dedup() functions in the RecordLinkage package should be a great starting point. But if you have big batches, then you might have to do some more tinkering.

I use the functions jarowinkler(), levenshteinSim(), and soundex() in RecordLinkage to write my own function that use my own weighting scheme (also, as it is, you can’t use soundex() for big data sets with RecordLinkage).

If I have two lists of names that I want to match (“record link”), then I typically convert both to lower case and remove all punctuation. To take care of “Limited” versus “LTD” I typically create another vector of the first word from each list, which allows extra weighting on the first word. If I think that one list may contain acronyms (maybe ATT or IBM) then I’ll acronym-ize the other list. For each list I end up with a data frame of strings that I would like to compare that I write as separate tables in a MySQL database.

So that I don’t end up with too many candidates, I LEFT OUTER JOIN these two tables on something that has to match between the two lists (maybe that’s the first three letters in each list or the first three letters and the first three letters in the acronym). Then I calculate match scores using the above functions.

You still have to do a lot of manual inspection, but you can sort on the score to quickly rule out non-matches.

More Related Contents:

  1. How can I match fuzzy match strings from two datasets?
  2. Delete rows that exist in another data frame? [duplicate]
  3. duplicates in multiple columns
  4. Remove duplicates keeping entry with largest absolute value
  5. Remove all duplicate rows including the “reference” row [duplicate]
  6. how to apply a function on a data set?
  7. Sample random rows in dataframe
  8. Subset / filter rows in a data frame based on a condition in a column
  9. How to change the locale of R?
  10. Replacing character values with NA in a data frame
  11. Coerce multiple columns to factors at once
  12. Custom legend for multiple layer ggplot
  13. Find which season a particular date belongs to
  14. rank and order in R
  15. Proper idiom for adding zero count rows in tidyr/dplyr
  16. Why is enquo + !! preferable to substitute + eval
  17. Add NAs to make all list elements equal length
  18. subsetting a data.table using != excludes NA too
  19. Print “pretty” tables for h2o models in R
  20. Referring to data.table columns by names saved in variables
  21. What is integer overflow in R and how can it happen?
  22. Exactly storing large integers
  23. R semicolon delimited a column into rows
  24. How to deal with nonstandard column names (white space, punctuation, starts with numbers)
  25. Combinations by group in R
  26. R: xtable and dates
  27. Different Robust Standard Errors of Logit Regression in Stata and R
  28. dynamically add function to r6 class instance
  29. Difference between Boolean operators && and & and between || and | in R
  30. Image not showing in Shiny app R

Leave a Comment