Data Cleaning Broken Down

Data Cleaning: The Bane of Our Existence and the Reason Analysis Works.

Introduction

Why the duality, you might ask?

There is a strange irony in the world of data: the work that nobody wants to do is often the work that matters most. Data cleaning is arguably the bane of every data professional's existence. We spend hours chasing missing values, hunting down duplicates, fixing inconsistent dates, correcting misspelled categories, and asking why a column that should contain numbers is suddenly full of text. It is repetitive, frustrating, and rarely the glamorous part of data analysis. Yet, beneath all that frustration lies its greatest importance: clean data is the foundation of trustworthy analysis. The most sophisticated dashboard, the most advanced SQL query, or the most powerful machine-learning model cannot compensate for fundamentally flawed data. Thus, before any clean or meaningful insight, data cleaning is inevitable.

1. Then, What Is Data Cleaning?

Data cleaning is the process of detecting, correcting, removing, or appropriately handling inaccurate, incomplete, inconsistent, duplicated, or improperly formatted data. It's literally cleaning data. Only that, in this case, we don't use water and soap. We use logic, formulas, and repetitive, annoying little adjustments here and there.

What makes data dirty? There are several potential problems when dealing with any data; I will name a few:

• Different Casing for the same item.
• Dates in different formats.
• Missing values
• Ambiguous values, such as a negative sale, which may be invalid or may represent a legitimate refund. Or an end date that comes before the start date.
• Duplicates, and again I say duplicates.
• Inconsistent data types... I am already tired. In a nutshell, there are so many reasons a data set can be dirty.

2. The importance of Cleaning Data

I will quote the famous principle:

Garbage in, garbage out.

If incorrect data enters an analytical process, even a sophisticated model can produce misleading results.

Data cleaning is important because it improves:

1. Accuracy - Corrects erroneous values and records.
2. Consistency - Ensures that the same concepts are represented in the same way.
3. Completeness - Identifies missing information and determines how it should be handled.
4. Reliability - Makes analytical results more trustworthy.
5. Efficiency - Clean and standardized data is easier to query, transform, visualize, and analyze.
6. Decision-making - Business decisions based on inaccurate data can result in incorrect conclusions, financial losses, operational problems, or poor strategic decisions.

3. How do we clean data becomes the next question.

Tools will differ, but I would like to show the general data cleaning process. Whichever tool you may be using, these steps form the skeleton of data cleaning.

I would follow these steps for any data cleaning task:

1. Understand the data (Context) 
2. Profile the data  
3. Identify the problems 
4. Decide on a uniform and standard way of correcting the data  
5. Clean the data
6. Validate the data 
7. Document 

Step 1: Understanding the Data

Before changing anything, we need to understand what the data represents. Understand the context of the data and the final purpose of cleaning the data. This step seems unnecessary, but it's very crucial. It will help you make the right decisions, know which data to keep, understand the dynamics of the purpose of analysis, and even make your work easier

Here are a few questions you can ask to understand the data:

• What does each row represent?
• What does each column represent?
• What is the source of the data?
• What are the expected data types?
• What values are considered valid?
• What are the business rules?
• Which fields are mandatory?
• What constitutes a duplicate?
• Are negative values allowed?
• What date range should the data cover?

Step 2: Profile the Data

Data profiling means examining the dataset before cleaning it. This is a little different from Step 1. The main goal of this step is to categorize the data for a uniform kind of cleaning.

Look at:

• Number of rows
• Number of columns
• Data types
• Missing values
• Unique values
• Duplicate records
• Minimum and maximum values
• Average and median
• Frequency distributions
• Outliers
• Invalid values

For example, if an Age column contains -5 and 150, this should immediately raise questions. But also does not mean you automatically delete them.

Note: A value that looks unusual may actually be valid.

Step 3: Identify Data Quality Problems

Identify common data-quality problems systematically. They include:

1. Missing values
2. Duplicate records
3. Incorrect data types
4. Inconsistent formatting
5. Invalid values
6. Outliers
7. Spelling inconsistencies
8. Incorrect dates
9. Leading and trailing spaces
10. Inconsistent categorical values
11. Broken relationships between tables
12. Incorrect units
13. Calculation errors

Step 4: Decide on a uniform and standard way of correcting the data

Missing Values

How do you treat unavailable information?

Rule of Thumb: Different data sets with missing values may need different handling of missing data.

A Missing value can be in the form of: NULL, Blank, Empty string, N/A, NA, Unknown,-0

Please note carefully that these CANNOT be treated as equivalents. For Instance: 0 means the value is actually zero.

Common missing values handling approaches include:

1. Remove

Remove records when the missing information makes the record unusable.

2. Impute

Replace the missing value using a reasonable estimate such as the mean, the median, or the mode for text. But always remember to document the logic used.

• Mean - for data with a normal distribution (no outliers)
• Median - for data with outliers(extremes)
• Mode - the most recurring text

For example:

10, 12, NULL, 14

and

10, 1000, NULL, -20

The missing value could potentially be replaced with the mean or median, respectively, depending on the situation.

3. Use a default value

Some of the default values that can be used are: NULL, "Unknown", "Not Provided"

Ensure the default value is uniform across a common set of data.
.

4. Leave it missing

Sometimes the best decision is to keep the missing value. The correct approach depends on the business meaning and analytical objective.

Duplicate Data

This is when the same record appears more than once.

Example:

Order ID	Customer	Sales
1001	John	500
1002	Mary	700
1001	John	500

If each order should have a unique Order ID, the second occurrence of order 1001 may be a duplicate.

It is, however, important to investigate duplicate-looking records before deleting.

For example:

John | Kenya | 500
John | Kenya | 500

could represent:

• A duplicate transaction
• Two legitimate transactions
• Two separate orders with missing identifiers

Therefore:

Never remove duplicates simply because two rows look identical. Understand the business rules first.

Standardizing Text

Text inconsistencies are extremely common, especially casing problems when representing the same category. For example: Kenya, kenya, KENYA, Kenya

Cleaning text involves:

• Removing leading spaces
• Removing trailing spaces
• Standardizing casing (Lower/Upper/Proper cases)
• Correcting spelling
• Standardizing abbreviations

Data Types

Every field should have its appropriate data type. An Integer for an Integer, Decimal for Decimal...

Correct data types are essential for:

• Calculations
• Sorting
• Filtering
• Aggregation
• Joins
• Time-series analysis

Consider this case:

"1500"

Although it looks like a number, it may actually be stored as text. This creates problems when calculating:

1500 + 500

Similarly:

"2026-05-01"

may be stored as text instead of a date.

Date and Time Cleaning

Dates are particularly problematic because different systems use different formats.

For example:

01/05/2026
2026-05-01
2026/05/01
May 1, 2026

These may represent the same date.

However:

01/05/2026

could mean:

• 1 May 2026
• January 5, 2026

depending on the regional format.

Dates should therefore be standardized and converted to an actual date data type whenever possible.

Outliers

An outlier is a value that is unusually different from other observations.

Suppose sales values are:

500
600
450
700
550
520
50000

50,000 is potentially an outlier.

But an outlier isn't automatically an error.

It could represent:

• A large legitimate transaction
• A special customer
• A data-entry error
• Fraud
• A one-time event

Therefore:

Identify outliers; don't automatically delete them.

Step 5: Data Validation

After cleaning, the data should be validated.

Ask:

• Are duplicates gone?
• Are required fields populated?
• Are data types correct?
• Are values within acceptable ranges?
• Are categories standardized?
• Are dates valid?
• Are relationships between tables correct?
• Did the cleaning process accidentally remove valid data?

Validation ensures that cleaning didn't create new problems.

Conclusion

Data Cleaning is the foundation we build all our data systems upon. Get it right, and the pipelines, the analytics, and the business decisions become seamless and valuable; get it wrong, and we miss the mark!