Effortless data deduplication at scale.

In today’s data-driven world, the importance of high-quality data to construct quality systems can’t be overstated.

The supply of reliable data is extremely critical for teams to make informed decisions, develop effective strategies, and gain useful insights.

Nonetheless, at times, the standard of this data gets compromised by various aspects, certainly one of which is the presence of fuzzy duplicates.

A set of records are fuzzy duplicates when they give the impression of being similar but will not be 100% equivalent.

As an illustration, consider the 2 records:

In this instance, the 2 records have similar but not equivalent values for each the name and address fields.

How will we get duplicates?

Duplicates can arise as a result of various reasons, resembling misspellings, abbreviations, variations in formatting, or data entry errors.

These, at times, could be difficult to discover and address, as they is probably not immediately apparent. Thus, they might require sophisticated algorithms and techniques to detect.

Implications of duplicates

Fuzzy duplicates can pose significant implications on data quality. It is because they end in inaccurate or incomplete evaluation and decision-making.

As an illustration, in case your dataset comprises fuzzy duplicates, and also you analyze it, you might find yourself overestimating or underestimating certain variables. This may result in flawed conclusions.

Having understood the importance of the issue, on this blog post, let’s understand how you’ll be able to perform data deduplication.

Let’s begin 🚀!

Imagine you have got a dataset with over 1,000,000 records which will contain some fuzzy duplicates.

The best yet intuitive approach that many often provide you with involves comparing every pair of records.

Nonetheless, this quickly gets infeasible as the scale of your dataset grows.

As an illustration, if you have got 1,000,000 records (10⁶), by following the naive approach, you would need to perform over 10¹² comparisons (n²), as shown below:

def is_duplicate(record1, record2):
## function to find out whether record1 and record2
## are similar or not.
...
for record1 in all_records:
for record2 in all_records:
result = is_duplicate(record1, record2)

Even when we assume an honest speed of 10,000 comparisons per second, it should take roughly to finish.

CSVDedupe is an ML-based open-source command-line tool that identifies and removes duplicate records in a CSV file.

One in all its key features is , which drastically improves the run-time of deduplication.

As an illustration, for those who are finding duplicates in names, the approach suggests that comparing the name “Daniel” to “Philip” or “Shannon” to “Julia” is mindless. They’re guaranteed to be distinct records.

In other words, two duplicates will all the time have some common lexical overlap. Nonetheless, the naive approach still compares them.

Using blocking, CSVDedupe groups records into smaller buckets and only performs comparisons between them.

That is an efficient option to reduce the variety of redundant comparisons, because it is unlikely that records in numerous groups might be duplicates.

For instance, one grouping rule might be to examine if the primary three letters of the name field are the identical.

In that case, records with different first three letters of their name field can be in numerous groups and wouldn’t be compared.

Nonetheless, records with the identical first three letters of their name field can be in the identical block, and only those records can be in comparison with one another.

This protects us from many redundant comparisons, that are guaranteed to be non-duplicates, like “John” and “Peter.”

CSVDedupe uses lively learning to discover these blocking rules.

Let’s now take a look at a demo of CSVDedupe.

Install Dedupe

To put in CSVDedupe, run the next command:

And done! We will now move to experimentation.

Dummy data

For this experiment, I actually have created dummy data of potential duplicates. That is shown below:

As you’ll be able to predict, the fuzzy duplicates are (0,1), (2,3), and (6,7).

CSVDedupe is used as a command-line tool. Thus, we should always dump this data right into a CSV file.

Marking duplicates

Within the command line, CSVDedupe takes an input CSV file and a pair more arguments.

The command is written below:

First, we offer the input CSV file. Next, we specify the fields we wish to contemplate for deduplication. That is specified as --field_names. On this case, we wish to contemplate all fields for deduplication, but when you wish to mark duplicates based on a subset of column entries, you’ll be able to do it with this argument.

Lastly, we now have the --output_file argument, which, because the name suggests, is used to specify the name of the output file.

Once we run this within the command line, CSVDedupe will perform its lively learning step.

In a gist, it should pick some instances from the given data and ask you in the event that they are duplicates or not, as shown below:

You must provide your input so long as you want to. Once you’re done, press f.

Next, it should mechanically start identifying duplicates based on the blocking predicates learned by CSVDedupe during its lively learning.

Once done, the output might be stored within the file provided, laid out in the --output_file argument.

Post deduplication, we get the next output:

CSVDedupe inserts a latest column, namely Cluster ID. A set of records with the identical Cluster ID refers to potentially duplicated records, as identified by the CSVDedupe’s model.

As an illustration, on this case, the model suggests that each records under Cluster ID = 0 are duplicates, which can also be correct.