Data Cleaning

Incorrect data does not reveal its presence. It will reside in your dataset in a seemingly normal manner, incorrect ages, duplications, blank fields where figures are supposed to be, and only when your model produces an incorrect output, which becomes obvious during a meeting, will you come to realize the problem.

By the time you discover the issue, it might already be too late.

In this comprehensive guide, we will explore everything about data cleaning – from defining it and the risks associated with failing to perform it, the six-step process for any type of dataset, a Python tutorial utilizing the Titanic dataset, and how artificial intelligence is changing the entire approach in today’s scenario.

What Is Data Cleaning?

Data cleaning refers to the process of finding and correcting errors, inconsistencies, and missing values in a dataset before it can be used effectively for analytics or machine learning purposes.

It may be called data cleansing, data scrubbing, data validation, or any number of other terms, but at the end of the day, it all means the same thing: you have a problematic raw dataset, and you need to clean it before working with it.

Clean data is accurate, meaning the values it holds represent the real world; complete, having no missing values; consistent, following the same standards across the entire dataset; and valid, conforming to the rules of its context. As soon as any one of these breaks down, anything derived from that dataset is rendered unreliable. Not inaccurate. Unreliable.

Why Data Cleaning Matters?

This is a figure worth considering: according to Gartner, low-quality data can cost businesses up to $12.9 million a year. This is no minor issue; it’s a recruiting budget. It’s a new product launch.

Data issues occur at all levels within a business structure. The marketing department sends out messages to redundant records, paying for two sends when one was sufficient. The healthcare institution uses 0 as the age for its patients – this is a standard null-fill error – and the risk management tool selects the wrong segment. The fraud detector model trained on messy data ignores the patterns that should raise red flags.

And then there’s the issue of time. A popular statistic estimates that 80% of a data scientist’s work day is spent in data cleaning and preparation – but it is misleading. While the most frequently quoted statistic comes from a 2016 CrowdFlower survey where 60% of time was devoted to cleaning and organizing the data, the 80% came when preparing datasets was included along with cleaning. Even later surveys had even lower percentages, with more mature organizations and processes coming up with significantly lower numbers, yet the actual percentage depends on all three factors: team, organization, and definition. Regardless of the exact percentage, it remains true that you are dedicating your most expensive employee’s time to fixing other people’s mistakes.

It’s why cleaning comes first, rather than being an initial process. A machine learning model run on bad data will give you bad predictions. So will any dashboard. So will your board’s quarterly report. The analysis may be mathematically impeccable – and yet be completely wrong – due to your incorrect inputs.

Data Cleaning vs. Data Transformation vs. Data Wrangling — What’s Actually Different

These three terms appear in the same conversations. They’re not the same thing, and conflating them causes real confusion when you’re scoping project timelines or dividing team responsibilities.

Term	What It Means	Example
Data Cleaning	Fix errors within existing data	Replacing null ages, removing duplicate rows, standardizing “N/A” and “Not Applicable” into one value
Data Transformation	Convert data from one format or structure to another	Encoding categorical variables, scaling numeric features, pivoting a wide table to long format
Data Wrangling	The full end-to-end process — cleaning, reshaping, enriching — from raw to ready	The complete workflow from messy CSV to analysis-ready DataFrame

Cleaning is a subset of wrangling. Transformation is a different operation entirely — it changes shape or format, not correctness. You need both; they happen in sequence. Clean first. Transform after.

6 Common Data Quality Problems

However, in reality, most issues with data tend to fit into one of six categories. Recognizing what that looks like in a live dataset, not some chart in a textbook, is the key.

1. Missing values: In a database of patients from a hospital, 18% of their age is missing. Not zero. Blank. Every analysis based on this data will be biased towards those patients where age was known, and this may not represent a random subset of the population. It doesn’t matter if there is no value; this is still information that needs to be considered.

2. Duplicate entries: In an e-commerce business following a merger, two CRM databases have been merged. All customers that were present on both systems appear twice. So does their purchasing history. Suddenly average order value is skewed in such a way that it seems completely reasonable in a dashboard visualization.

3. Incorrect data types: ZIP codes are incorrectly read in as integers. Python removes the first character in the code 08901. Suddenly there’s an issue with the geography of New Jersey.

4. Outliers and anomalies: An age of 150 years is recorded. The value of a transaction is negative $4 million. These could be data entry mistakes – but they could also be legitimate outliers. A financial anomaly that exactly matches the transaction your fraud detection algorithm is trying to identify. To assume that all outliers are noise is one of the costliest mistakes in data science.

5. Format inconsistency: Three analysts use three different date formats: 2026-01-15, January 15 2026, and 15/01/26. All group-by operations on the column fail. All time series plots result in nonsense output.

6. Spelling and typographical errors: A product category field includes the following values: “Electronics,” “electronics,” “Electronis,” and “Electrnics.” There are four different values when there is actually only one.

How to Clean Data — The 6-Step Process

No single cleaning process works on every dataset. What works is a framework you adapt, not a rigid sequence you execute blindly. Here’s the framework.

Step 1 — Assess and Profile Your Data

Don’t even think about touching your data until you know what it is.

Run df.info() and df.describe(). Every time. Before any cleaning decision is made. 

These commands will reveal: column names, data types, count of non-null entries for each column, and statistical distribution of each numeric variable.

But first, answer the following questions before cleaning:

• Which columns contain NaNs? What percentage of each?
• Which columns contain data in an incorrect data format?
• Is there any out-of-range value in numeric columns?
• How many duplicates are present in the dataset?

python
import pandas as pd
import numpy as np

df = pd.read_csv('Titanic-Dataset.csv')
df.info()
df.describe()

# Calculate missing value percentage per column
round((df.isnull().sum() / df.shape[0]) * 100, 2)

Skipping this step is like doing surgery without reading the chart. You might fix the right things. Or you might not.

Step 2 — Remove Duplicates and Irrelevant Columns

Duplicate rows happen for boring, predictable reasons: form resubmissions, API double-writes, data merges from overlapping systems. They inflate every aggregate you run without adding any information.

python
# Check how many duplicates exist
df.duplicated().sum()

# Remove them
df.drop_duplicates(inplace=True)

Irrelevant columns are another issue. A PassengerId column has no meaningful data for analysis. Nor will there be anything useful in a free-text Notes field unless you’re working on NLP. Remove those from the get-go. Less data means faster processing and debugging.

python
df = df.drop(columns=['PassengerId', 'Ticket', 'Name', 'Cabin'])

The Cabin column in the Titanic dataset contains more than 77% missing data. There is nothing to fill in the gap when there are such levels of missing data; therefore, this column is completely unrecoverable. This step is done now, not in Step 4.

Step 3 — Fix Structural and Format Errors

Standardization. This is where we deal with inconsistencies: date formats made uniform, text cases standardized, categorical values with the same meaning unified.

“N/A”, “Not Applicable”, “n/a”, and “” are issues for all datasets sourced from forms that don’t have required picklists. This is fixed by explicitly mapping the replacements:

python
df['Status'] = df['Status'].replace({
    'N/A': 'Not Available',
    'n/a': 'Not Available',
    'Not Applicable': 'Not Available',
    '': 'Not Available'
})

Date standardization:

python
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

Text casing:

python
df['Category'] = df['Category'].str.lower().str.strip()

The importance of the .strip() function cannot be overemphasized. The trailing whitespaces are not visible in a DataFrame display and will cause all equality matches to fail, including the ones used during merging operations.

Step 4 — Handle Missing Values

This is where most analysts go wrong — not in their methodology, but in their choice of methodology. There are three ways to handle missing data, each with its own ramifications.

Drop rows/columns. Quick and easy. Dropped rows will lose information; dropped columns will lose everything about them. Take this route if your missing data is minimal (less than 5%) and randomly distributed, or if there’s too much missing data (more than 60–70% of a column’s total) to be able to fill in.

Impute with a statistical figure. Imputation using means, medians, modes is very common, but does reduce variability within the dataset. It involves inventing data that clusters around the middle and minimizes variance.

Flag and Investigate. Sometimes the fact that something is blank is significant in and of itself. If someone doesn’t provide an income figure, this might be due to not wanting to answer this question. Before filling in anything, mark the column using a new one:

python
df['Age_missing'] = df['Age'].isnull().astype(int)
df['Age'] = df['Age'].fillna(df['Age'].median())

The crucial point to differentiate here — one which is not covered in any other article addressing the issue in question — is whether the missing data is MCAR (Missing Completely at Random), MAR (Missing at Random), or MNAR (Missing Not at Random). In the case of MNAR, the cause for data being missing is associated with the value itself.

Step 5 — Identify and Treat Outliers

The vast majority of writings about this issue miss this point entirely.

Outlier detection has been treated like data cleanup – detect the outliers, delete them and continue from there. 

But an outlier does not indicate an error, but rather an unusual value, two entirely different concepts that require different approaches.

If there’s an outlier in a customer age variable set at 150, then it’s simply an error. Get rid of it.

If there’s an outlier in a fraud detection system showing a $4 million transaction where the account would typically process $200 transactions, that’s what you’re trying to detect in the first place. Eliminating that outlier just threw away your best piece of data.

Two effective detection methods exist.

Z-score method — useful for roughly normal distributions:

python
from scipy import stats

z_scores = stats.zscore(df['Age'])
df_clean = df[(abs(z_scores) < 3)]

IQR method — more robust for skewed distributions:

python
Q1 = df['Age'].quantile(0.25)
Q3 = df['Age'].quantile(0.75)
IQR = Q3 - Q1

lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

df_clean = df[(df['Age'] >= lower) & (df['Age'] <= upper)]

Before removing any flagged value: plot it, understand it, and ask a domain expert if needed. The boxplot starts an investigation. It doesn’t end one.

Step 6 — Validate, Document, and QA

This is the step that everyone misses since the data is already clean. Exactly. Now record all of this.

The checks to perform after cleaning: Is the resulting dataset statistically coherent? Do the distribution approximations make sense with regard to domain expertise? Has variance been reduced in an important variable due to imputation? Does the number of rows make sense?

python
print("Rows remaining:", df_clean.shape[0])
print("Missing values:\n", df_clean.isnull().sum())
df_clean.describe()

Document each cleaning action: What columns did you drop, what is the rationale behind that, what is the outlier cutoff value, what technique have you used for filling out the missing values.

It’s not bureaucracy; it’s reproducibility. In six months’ time, when your coworker runs your code and finds out there are 200 rows missing, how would you know what happened? And since the data is lost, well, it’s a big issue under GDPR because that might involve personal data.

Data Cleaning in Python — A Practical Walkthrough

Frankly speaking, nothing beats the process of getting firsthand experience of how data cleaning is done than actually doing it yourself using a dataset. And for that purpose, the Titanic dataset is perfect since it has some problems that could be easily explained.

And here’s the step-by-step process:

python
import pandas as pd
import numpy as np
from sklearn.preprocessing import MinMaxScaler

# Load and profile
df = pd.read_csv('Titanic-Dataset.csv')
df.info()
df.describe()
print("Missing %:\n", round((df.isnull().sum() / df.shape[0]) * 100, 2))

# Step 2: Remove duplicates and irrelevant columns
df.drop_duplicates(inplace=True)
df = df.drop(columns=['Name', 'Ticket', 'Cabin', 'PassengerId'])

# Step 3: Fix structural errors
df['Embarked'] = df['Embarked'].str.strip().str.upper()
df.dropna(subset=['Embarked'], inplace=True)

# Step 4: Handle missing values
df['Age_missing'] = df['Age'].isnull().astype(int)
df['Age'] = df['Age'].fillna(df['Age'].median())

# Step 5: Remove outliers via IQR
Q1 = df['Age'].quantile(0.25)
Q3 = df['Age'].quantile(0.75)
IQR = Q3 - Q1
df = df[(df['Age'] >= Q1 - 1.5 * IQR) & (df['Age'] <= Q3 + 1.5 * IQR)]

# Step 6: Validate
print("Remaining rows:", df.shape[0])
print("Post-clean missing:\n", df.isnull().sum())

# Scale numeric features for ML
X = df[['Pclass', 'Age', 'SibSp', 'Parch', 'Fare']]
scaler = MinMaxScaler()
X_scaled = scaler.fit_transform(X)

And one thing that’s not often mentioned but should be included here: Fit your scaler on the training set, but never on the whole dataset. This means don’t use on your MinMaxScaler  whole dataset before splitting into the train and test datasets. By doing so, you have leaked information from your test set into the scaler itself. You’ll be overestimating the performance of your model because its test accuracy will never be this good in the real world.

How AI Is Changing Data Cleaning in 2026

The short answer: Yes – but probably not the one you would imagine.

Artificial intelligence does not displace the data cleansing process. Instead, it automates the known and repetitive portions of the process, and sometimes it creates new challenges where older ones were addressed. Let’s look closer at what is changing in 2026.

Anomaly detection via artificial intelligence. Platforms such as Monte Carlo and Anomalo watch for anomalies within the data pipelines around the clock. They analyze what is considered normal in a specific table and automatically highlight any deviations. This becomes a real change in operations for teams dealing with hundreds of tables in the warehouse.

NLP for text standardization.Today, large language models are used in the context of preparing data because there are unstructured text fields — product descriptions, different variants of addresses, free text category fields — that used to need complicated manual rules in the past. Semantic matching tools that do not function native inside the dbt framework and can be used side by side with dbt help in bringing together categories that may be named differently, but actually refer to the same meaning. dbt Copilot released in general availability in 2025 is dedicated to documentation purposes and optimization of the queries, rather than category standardization; semantic matching technology lies inside a wider LLM-based cleaning layer.

Automated Rule Learning. The rules for cleaning data are not manually formulated anymore, but rather the systems like Ataccama ONE learn the pattern based on previous corrections made by an analyst.

The bizarre thing about this risk is that the AI used to cleanse the data set itself picks up on whatever systemically biased information your data might contain. So the process designed to catch bias actually ends up amplifying it. If you’ve got systematic bias in how your data set classifies particular demographic groups or particular products, your AI cleanser learns that lesson and applies it broadly to your cleansed data set.

Data Cleaning Checklist — Before You Start Any Analysis

Use this before touching any new dataset. Every item, in order.

Data profiling:

• Run df.info() — data types and non-null counts confirmed
• Run df.describe() — numeric distributions reviewed
• Count duplicate rows: df.duplicated().sum()
• Calculate missing value percentage per column

Structural fixes:

• Date formats standardized to one consistent format
• Text casing normalized: str.lower().str.strip()
• Equivalent categorical values consolidated (N/A, Not Available, n/a → one value)
• Numeric columns verified as numeric — no hidden text strings

Missing values:

• Columns with >60% missing — evaluate for dropping, not imputing
• Columns with <5% missing — impute with median or mode
• Missingness type checked — MCAR, MAR, or MNAR — before imputing anything

Outliers:

• Boxplot run on all numeric columns before any removal
• Each outlier classified: data entry error or valid anomaly
• IQR or z-score method applied with documented threshold

Validation and documentation:

• Row counts before and after cleaning — any unexpected drops investigated
• Post-clean distributions match domain expectations
• Every cleaning decision documented: what, why, and what threshold was used

Data Cleaning Tools — What Actually Gets Used in 2026

Tool	Best For	Skill Level	Cost
pandas + NumPy	Programmatic cleaning in Python	Intermediate	Free, open source
OpenRefine	Messy text data, faceted browsing for category cleanup	Beginner–Intermediate	Free, open source
dbt	Pipeline-level cleaning inside data warehouses	Intermediate–Advanced	Free tier + paid plans
Tableau Prep	Visual, no-code cleaning for BI teams	Beginner	Paid (Tableau license)
Great Expectations	Automated data validation and quality checks	Intermediate	Free, open source
Power Query	Business users cleaning in Excel or Power BI	Beginner	Included with Microsoft 365
Apache Spark	Distributed cleaning on large-scale datasets	Advanced	Free, open source

But to be fair, the right tool depends entirely on where your data sits and who’s responsible for cleaning it. Data engineers constructing a dbt pipeline will have different requirements from a marketing analyst analyzing a CSV file within Excel. No one here is wrong. The problem lies in opting for an expensive enterprise-level solution when pandas gets the job done in just 20 lines.

Frequently Asked Questions 

Conclusion

The true message in data cleaning is neither the pandas methods nor the items on the check list. It is that each and every decision in cleaning the data is an analytic decision with repercussions; and these repercussions need to be known prior to making the decision rather than learning about them after your predictive models don’t work. The analyst who thinks that data cleaning is just like washing dishes will be taken aback each and every time.