Data Quality5 July 2023 · 3 min

How To Clean A Dataset in Minutes Quick 2-Step Guide)

Have a data set that you need help with cleansing, normalizing, standardizing, or consolidating? This article shows you how to do exactly that

A diagram of three scribbled shapes being cleaned into solid shapes, transformed into squares, then consolidated into a single stacked column

Unsure of know how to clean a dataset the right way?

If you struggle with data cleansing, normalization, standardization or consolidation, this article is for you.

We’ll lay down a simple scenario from the retail world, but the concepts are applicable in a lot of other situations

Let us take the following tables.

These are transactional data for the same vendor that come from different sources & different schemas:

Three source datasets annotated in red with what is wrong with each: mismatched column names and text format issues in the first, non-conforming order ids in the second, and an additional colour column in the third

Our Objective

**Clean, Transform, and Merge the data **to look like the following:

The same three datasets merged into one nine-row table and annotated in green: normalized order ids, normalized product names, and a new Category column carrying Lighting, Office Supplies and Apparel

‍

The Challenges

If we only had these nine rows to deal with, it’s not an issue. copy and paste within MS Excel or Google Sheets and manually clean it up.

But in the real world, the problems come in various forms:

‍Size of datasets: Whether it is a couple of thousand rows or millions, a regular spreadsheet isn’t designed to handle the transformation required to achieve the end state‍
Constant inflow of data & the need for automation: Data today is rarely static. They are continually growing, and all the modifications needed become a repetitive nightmare.‍
Unavoidable data messiness: Additional column names, inconsistent content, different schemas. these are real-world problems that are almost impossible to fix at the source. They need to be handled during data consolidation.

Mammoth’s code-free, time-saving, automated solution

Let us show you how you can resolve this in a couple of minutes, without writing any code.

For those who don’t know about Mammoth Analytics, it is a lightweight, code-free data management platform.

It provides powerful tools for the entire data journey, including data retrieval, consolidation, storage, cleanup, reshaping, analysis, insights, alerts and more.

Step 1. Transform and normalize the three datasets

First, bring your data into the Mammoth Data Library.

For this example, we have simple CSV files that we uploaded directly into Mammoth, but the platform supports a lot of additional ways to ingest your data.

Mammoth's Data Library with three uploaded datasets, Ds1 selected to show its five columns and three rows and a preview of the raw transaction data

With Mammoth’s extensive data transformation functions, we can shape the data in a variety of ways to get it in the format

We’ll perform a couple of transformations here to get the data in the right shape:

Dataset Ds1 after three recorded steps: Order ID converted from number to text, a Category of Lighting annotated onto rows whose Order ID starts with 53, and Product Name reformatted to capital case

Dataset Ds2 after one step that extracts everything to the right of the hyphen, so an order id of O-643837 becomes 643837

Dataset Ds3 after two steps: the Ord- prefix stripped from OrderID, and a Category of Apparel annotated onto every row

Step 2. Save the Datasets into a Master Dataset

A diagram of three separate coloured blocks consolidating into one stacked column

Now that we have transformed the data let’s save it into a Master Dataset.

For this action, we will utilize a powerful function called “Save to Dataset”. This function allows multiple, potentially inconsistent and incompatible datasets to be merged into a single master dataset.

From Dataset 1, we will create a Master Dataset