In the realm of data analysis, an important and sometimes underestimated process is that of data cleaning and preparation. To make the most of data analyses, posterior visualizations, and conclusion-drawing (in short, making sense of data), the data must be clean and prepared for an effective processing and understanding of the underlying information.

This article guides you through the main stages of the data cleaning and preparation processes, using examples revolving around the scenario of preparing a sales database for its analysis by an online clothing store. No coding or technical skills are needed to understand the steps and examples described in this article.

The diagram below outlines the steps we will examine, sorted by the order they are typically applied. Not all datasets require exhaustively applying all of these stages: applying or not some of them will depend on the specific data and analysis problem to address.

Identify and Understand the Data

Before starting data cleaning, it is important to understand the dataset we are working with. Let’s suppose our sales data are registered at the product level (a ticket line in a customer purchase) and it includes several columns: sale date-time, product ID, customer name, customer email, sale price, and quantity purchased.

For each column or data attribute, carefully review the type of information each column contains (numerical vs. ordinal vs. categorical), identify possible inconsistencies, and a clear purpose in terms of what are you aiming to analyze: for instance, analyzing monthly sales.

Remove Duplicated Entries

One of the most frequent issues when working with data stored in databases is the presence of duplicate rows. In our scenario, it could be that a ticket line in a purchase has been accidentally added twice. To address this, carefully look for rows that are identical, with repeated values in all their columns, such as the same customer with the same date and the same products, at the same date and time. To avoid counting the same sale twice, and altering subsequent analysis results, implement an effective strategy to find and remove duplicates.

Handle Missing Values

Missing values are another commonly found issue in data, which can also affect the results of the analysis. In our sales database, it might happen that some rows are missing the customer’s email and name, or the quantity purchased.

Depending on the nature of the data attributes, there are several strategies to handle missing values. One is to remove rows containing missing values. When a crucial attribute like the quantity purchased is missing, the reason might be an error in data collection, hence it might be sensible to remove those rows. Meanwhile, a missing email or customer name might only mean the customer is not subscribed to the store fidelity program, but their sales data are still valuable: in such cases, a data imputation strategy is more appropriate. Data imputation involves replacing the missing value with a default value, for instance, “email not provided”.

Fix Typos and Formatting

Typos can often go unnoticed, hence it’s necessary to check for them. For example, minor inconsistencies in product names, e.g.”Blue shirt” vs. “Blue Shirt”, can be automatically fixed so the product names are unified. Date and time information, on the other hand, is a common case of data that might have been collected under several disparate formats, therefore it is essential to uniformly format all dates to enable smooth and consistent analyses where date information comes into play.

Standardize the Data

Suppose that some values in the sale price column ​​are in Euros and others in Dollars. Before plunging into analysis, those units should be standardized, usually by converting all sale prices into the same currency.

This standardization can also be applied to categorical, ordinal data like product sizes, where “S”, “Small” and “Pequeño” could represent the same size. Group these values ​​under the same criterion to simplify the analysis.

Filter and Select Relevant Data

Once the data is clean and standardized, it’s time to select the data (rows and/or columns) that really matter for our analysis. For instance, if we are only interested in sales made in 2024, all previous sales data should be filtered out. Likewise, if the customer email is irrelevant for analyzing monthly sales, that column may be removed. This process simplifies the analysis and improves the analysis performance and the quality of its outcomes.

Create New Data Variable(s)

Finally, in certain use cases, it is necessary to create new variables from existing ones. In our example, we could calculate a new “Total Amount” column describing the total amount spent by a customer on a product as part of a purchase, by multiplying the sales price by the quantity purchased. This variable could be useful for enabling a more agile revenue analysis by customer or by product.