Perl : Tracking duplicates

Question

I am trying to figure out what would be the best way to go ahead and locate duplicates in a 5 column csv data. The real data has more than million rows in it.

Following is the content of mentioned 6 columns.

Name, address, city, post-code, phone number, machine number

Data does not have fixed length, data might in certain columns might be missing in certain instances.

I am thinking of using perl to first normalize all the short forms used in names, city and address. Fellow perl enthusiasts from stackoverflow have helped me a lot.
But there would still be a lot of data which would be difficult to match. So I am wondering is it possible to match content based on "LIKELINESS / SIMILARITY" (eg. google similar to gugl) the likeliness would be required to overcome errors that creeped in while collecting data.

I have 2 tasks in hand w.r.t. the data.

1. Flag duplicate rows with certain identifier
2. Mention the percentage match between similar rows.

I would really appreciate if I could get suggestions as to what all possible methods could be employed and which would propbably be best because of their certain merits.

Answer 1

You could write a Perl program to do this, but it will be easier and faster to put it into a SQL database and use that.

Most SQL databases have a way to import CSV. For this answer, I suggest PostgreSQL because it has very powerful string functions which you will need to find your fuzzy duplicates. Create your table with an auto incremented ID column if your CSV data doesn't already have unique IDs.

Once the import is done, add indexes on the columns you want to check for duplicates.

CREATE INDEX name ON whatever (name);

You can do a self-join to look for duplicates in whatever way you like. Here's an example that finds duplicate names.

SELECT id
FROM   whatever t1
JOIN   whatever t2 ON t1.id < t2.id
WHERE  t1.name = t2.name

PostgreSQL has powerful string functions including regexes to do the comparisons.

Indexes will have a hard time working on things like lower(t1.name). Depending on the sorts of duplicates you want to work with, you can add indexes for these transforms (this is a feature of PostgreSQL). For example, if you wanted to search case insensitively you can add an index on the lower-case name. (Thanks @asjo for pointing that out)

CREATE INDEX ON whatever ((lower(name)));

// This will be muuuuuch faster
SELECT id
FROM   whatever t1
JOIN   whatever t2 ON t1.id < t2.id
WHERE  lower(t1.name) = lower(t2.name)

A "likeness" match can be achieved in several ways, a simple one would be to use the fuzzystrmatch functions like metaphone(). Same trick as before, add a column with the transformed row and index it.

Other simple things like data normalization are better done on the data itself before adding indexes and looking for duplicates. For example, trim out and squish extra whitespace.

UPDATE whatever SET name = trim(both from name);
UPDATE whatever SET name = regexp_replace(name, '[[:space:]]+', ' ');

Finally, you can use the Postgres Trigram module to add fuzzy indexing to your table (thanks again to @asjo).