Cleaning Big Data the Easy Way

Cleaning big data usually invokes big stress levels. With the advancements we’ve seen in technology over the years, many industries have been transformed from the very core. For academic researchers and data scientists, data has become more expansive and detailed than ever before. However, this has also affected business analysts and millions of others in the world whether in the business niche or not. With this in mind, it has become tougher to address the real analytic questions. In more recent times, data preparation tools have grown in popularity and I’m going to share some tips so you can embrace big data rather than cowering in its sheer presence!

big data word cloud

Be Wary of the Negatives

First and foremost, working with a full dataset that’s new to you and your job can be a little daunting. Normally, a business analyst will filter out the irrelevant values before then running various computations to check the quality of the data. From here, they can use a BI tool to fully assess the information. Suddenly, one thousand rows of data has turned into one million rows and it can lead to confusion and frustration.

When facing this much data it’s unpractical, to say the least, to visually assess the quality of data, and when dealing with millions of records, there’s simply too much data for office software or hardware to manage. You could run the data on a clustered platform but for a basic assessment, this is definitely overkill.

Create a Subset for Testing

The most effective way is to structure and segregate the data into more manageable sections. Once you do this, the process becomes more efficient and the results can be obtained as before. It’s important to note that, in order to create a meaningful subset of data, the analyst should be familiar with the questions of the business to get an overall understanding of the project at hand.

Once you know what you’re looking for, create a small subset of records to work with until you have the exact outcome you need. This subset can be run effectively on a desktop or small server to refine the process of transforming the data.

Create a Set of Generalized Rules

If you’ve got 200 records, some simple Excel formulas will usually do the trick but that’s not an option with millions of records. But the principle is the same – you need to create a set of rules that can be applied to the whole data systematically. Time for an example…

Let’s say you have data like this:

Cleaning big dataAnd you want it to look like this:

Cleaning big data - after transformation

The rules, in plain English, will look something like this:

  1. Split the full name into first name and last name
  2. Format the SSN to nnn-nn-nnnn

This is a very basic example, but you get the idea.

Once you have your rules, it’s time to fire up DataMatch Enterprise and do a test run on the sample we extracted.

Validate and Iterate

Finally, the results then need to be verified (sometimes, more refinement and iteration will be necessary). If you’ve already identified the main subsets of data for which you need to create the preparation steps, you can apply the changes to a specific sample. For example, many choose to search for an error they know exists or is common just to make sure it has been picked up successfully. Once you run this test a couple of times and feel confident in the results, you can hit the magic button to apply all the changes to the whole dataset and wait as your heartbeat thumps against the ribcage.

When you work on smaller sets of data, the feedback will normally come in a matter of seconds. Unfortunately, just to keep the nerves jangling for a little longer, you might need to take a small break before then returning for the results with this process. Eventually, a summary of statistics will show and this can act as a final validation. If the summary highlights issues, you can take the right steps to fix them and this methodical approach will ensure you aren’t overwhelmed at any point whilst still allowing you to achieve success!

Which brings me to my final tip for today…

Use DataMatch Enterprise for Cleaning Big Data

DataMatch Enterprise connects to Hadoop and Apache Spark to clean, match, merge and purge your big data, all for a fraction of the cost of other solutions.

To see it in action, contact us today for a demo!