Turn multiple incompatible files into a clean master dataset

If you struggle with data cleansing, normalizationstandardization or consolidation, this article is for you. Mammoth’s powerful new feature will save you time, money and all the headaches.

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. You can check it out at www.mammoth.io.

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. Or if you have any questions or queries, feel free to reach out to us at hello@mammoth.io.

Related Posts

Mammoth Analytics achieves SOC 2, HIPAA, and GDPR certifications

Mammoth Analytics is pleased to announce the successful completion and independent audits relating to SOC 2 (Type 2), HIPAA, and GDPR certifications. Going beyond industry standards of compliance is a strong statement that at Mammoth, data security and privacy impact everything we do. The many months of rigorous testing and training have paid off.

Read More

Announcing our partnership with NielsenIQ

We’re really pleased to have joined the NielsenIQ Connect Partner Network, the largest open ecosystem of tech-driven solution providers for retailers and manufacturers in the fast-moving consumer goods (FMCG/CPG) industry. This new relationship will allow FMCG/CPG companies to harness the power of Mammoth to align disparate datasets to their NielsenIQ data.

Read More

Hiring additional data engineers is a problem, not a solution

While the tendency to throw in more data scientists and engineers at the problem may make sense if companies have the budget for it, that approach will potentially worsen the problem. Why? Because the more the engineers, the more layers of inefficiency between you and your data. Instead, a greater effort should be redirected toward empowering knowledge workers / data owners.

Read More