Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to detect duplicate data?

I have got a simple contacts database but I'm having problems with users entering in duplicate data. I have implemented a simple data comparison but unfortunately the duplicated data that is being entered is not exactly the same. For example, names are incorrectly spelled or one person will put in 'Bill Smith' and another will put in 'William Smith' for the same person.

So is there some sort of algorithm that can give a percentage for how similar an entry is to another?

like image 626
grom Avatar asked Aug 28 '08 01:08

grom


People also ask

How do you identify duplicate records in a table?

One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.

Can Excel detect duplicates?

The easiest way to detect duplicates in Excel is using the COUNTIF function. Depending on whether you want to find duplicate values with or without first occurrences, there's going to be a slight variation in the formula as shown in the following examples.


1 Answers

So is there some sort of algorithm that can give a percentage for how similar an entry is to another?

Algorithms as Soundex and Edit distances (as suggested in a previous post) can solve some of your problems. However, if you are serious about cleaning your data, this will not be enough. As others have stated "Bill" does not sound anything like "William".

The best solution I have found is to use a reduction algorithm and table to reduce the names to it's root name.

To your regular Address table, add Root-versions of the names, e.g Person (Firstname, RootFirstName, Surname, Rootsurname....)

Now, create a mapping table. FirstNameMappings (Primary KEY Firstname, Rootname)

Populate your Mapping table by: Insert IGNORE (select Firstname, "UNDEFINED" from Person) into FirstNameMappings

This will add all firstnames that you have in your person table together with the RootName of "UNDEFINED"

Now, sadly, you will have to go through all the unique first names and map them to a RootName. For example "Bill", "Billl" and "Will" should all be translated to "William" This is very time consuming, but if data quality really is important for you I think it's one of the best ways.

Now use the newly created mapping table to update the "Rootfirstname" field in your Person table. Repeat for surname and address. Once this is done you should be able to detect duplicates without suffering from spelling errors.

like image 89
Tnilsson Avatar answered Sep 21 '22 10:09

Tnilsson