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:

Our Objective

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

‍

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.

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:

Step 2 — Save the Datasets into a Master Dataset

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

Now with Dataset 2 and 3, we’ll add the data into the Master Dataset

And we’re done

We can now see the “Master Dataset” in the Data Library. If we open that up, we’ll see our cleaned up and consolidated data.

We have achieved a code-free solution to combining multiple, incompatible datasets in a couple of minutes.

This a small example of some of the benefits of using the Mammoth Analytics platform.

To learn more, check out some of the features.