Important things to know
Data is often described as the fuel that powers modern businesses but before that fuel can power anything, it has to be refined. Raw data is rarely ready to use straight out of the box. It may contain missing values, duplicate records, inconsistent formats, spelling errors, outliers, and other problems that can quietly damage an analysis or machine learning model.
For a data scientist, cleaning data is therefore more than a tedious preparation step. It is one of the most important parts of the entire data science workflow.
Think of messy data like a cluttered kitchen. You cannot prepare a great meal efficiently if the ingredients are mixed together, expired, mislabeled, or scattered across the floor. Before cooking, you need to organize the workspace, inspect what you have, throw away what is unusable, and prepare the ingredients properly.
The same principle applies to data.
Here is a practical approach to cleaning messy data as a data scientist.
1. Understand Your Data Before Cleaning It
The first mistake many beginners make is jumping straight into cleaning.
Before changing anything, you need to understand what you are working with.
Start by asking questions such as:
- What does each column represent?
- What is the expected data type?
- How many rows and columns are there?
- Which columns are categorical, numerical, or dates?
- What values are considered valid?
- Where did the data come from?
- What is the purpose of the dataset?
In Python, tools such as pandas make this initial inspection relatively straightforward.
You might use functions such as head(), info(), describe(), and value_counts() to get a quick overview of the dataset.
This stage is like reading a map before starting a journey. You need to know where you are before deciding where to go.
2. Identify Missing Values
Missing data is one of the most common problems in real-world datasets. The missing values may have occurred because customers skipped questions, a system failed to record information, or data from different sources was merged incorrectly.
The important thing is not to automatically replace every missing value.
First, determine why the values are missing.
For numerical variables, you might replace missing values with the mean or median. Median imputation is often useful when the data contains extreme values because it is less affected by outliers.
For categorical variables, you might use the most common category or create a separate category such as "Unknown".
In some cases, however, removing rows with missing values is appropriate. The decision should depend on how much data is missing and how important the affected variable is.
Think of missing data as empty spaces in a jigsaw puzzle. Sometimes you can infer what belongs there. Sometimes the piece is impossible to recover, and trying to manufacture one could make the final picture worse.
3. Remove Duplicate Records
Duplicate data can distort your analysis.
Suppose your customer database contains 10,000 records, but 800 customers were accidentally entered twice. If you calculate the number of customers without removing duplicates, your results could be misleading.
Duplicates can occur because of repeated data imports, system errors, manual entry, or combining multiple datasets.
Use unique identifiers where possible to determine whether two records actually represent the same entity.
However, be careful: two rows that look similar are not necessarily duplicates.
For example, two customers may share the same name but have different email addresses. Removing one simply because the names match could destroy useful information.
Cleaning data is not about deleting everything that looks suspicious. It is about making informed decisions based on the meaning of the data.
4. Standardize Inconsistent Values
Inconsistent formatting is another common source of messy data.
Consider a column containing:
Lagos
lagos
LAGOS
Lagos
To a human, these values obviously refer to the same place. To a computer, however, they may be treated as different categories.
The same problem occurs with dates, currencies, units, and spelling.
For example:
Male
male
M
MALE
These values may all represent the same category.
Standardization can involve converting text to lowercase, removing unnecessary whitespace, correcting spelling, or mapping different representations to a single standardized value.
For dates, make sure values follow a consistent format. For numerical measurements, make sure units are consistent.
Imagine asking five people to measure the same table, with one using centimeters, another using inches, and another using meters. The numbers would look inconsistent even though the measurements describe the same thing.
Data cleaning ensures everyone is effectively speaking the same language.
5. Check Data Types
A column containing numbers may not actually be stored as a numerical data type.
For example:
"100"
"250"
"500"
These values look like numbers, but if they are stored as strings, mathematical operations may produce unexpected results.
Dates are another common example. A date stored as text is much less useful than a properly formatted datetime object when you need to calculate time differences, extract months, or identify trends.
Check whether each variable has the correct data type and convert it when necessary.
This is especially important before analysis or machine learning because algorithms generally expect data to be represented in particular formats.
6. Deal With Outliers Carefully
Outliers are observations that are unusually different from the rest of the data.
Imagine a dataset containing the annual salaries of 500 employees. Most employees earn between $30,000 and $100,000, but one value is recorded as $10,000,000.
That value deserves investigation.
But an outlier is not automatically an error.
It could be a legitimate observation. A company's CEO, for example, may genuinely earn far more than other employees.
You can identify potential outliers using methods such as box plots, the interquartile range (IQR), or statistical techniques such as standard deviations and z-scores.
The key word is investigate.
Deleting an outlier simply because it makes your graph look better is like removing a nail from a wall because it is sticking out farther than the others. Sometimes the nail is the problem. Sometimes it is exactly where it needs to be.
7. Look for Impossible or Invalid Values
Some data problems are easier to identify because the values simply do not make sense.
For example:
- Age = -5
- Percentage = 150%
- Number of children = 47
- Temperature = -500°C in a dataset where such a measurement is impossible
- Transaction date occurring before the customer's birth date
These values may be caused by data-entry errors, faulty sensors, software bugs, or incorrect transformations.
Create validation rules based on the domain you are working in.
A data scientist working with healthcare data will need different validation rules from someone working with financial transactions or e-commerce data.
This is why domain knowledge matters. Data cleaning is not purely a technical exercise; it requires understanding what the data actually represents.
8. Handle Categorical Data Properly
Categorical variables often require special attention.
Suppose you have a column called Education containing:
Bachelor's
Bachelors
Bachelor
BSc
bachelor's degree
These values may represent the same underlying category. If you leave them untouched, your analysis might incorrectly conclude that there are several different education groups. Create standardized categories where appropriate. For machine learning, categorical variables may also need to be transformed into numerical representations using techniques such as one-hot encoding or label encoding, depending on the problem. The goal is to preserve meaning while making the data usable.
9. Watch for Data Leakage
Data cleaning can also introduce a more subtle problem: data leakage. Data leakage occurs when information that should not be available to a model during prediction accidentally enters the training data.
For example, suppose you are building a model to predict whether a customer will cancel a subscription. If you include a column that was created after the customer canceled, the model may appear incredibly accurate but only because it has effectively been given the answer. When cleaning and preparing data, understand the timeline and origin of each variable. A model that knows the future is not intelligent. It is cheating.
10. Validate Your Cleaned Data
Cleaning should never end with, “The code ran successfully.” You need to check whether the cleaned dataset actually makes sense. After cleaning, ask:
- Are there still missing values?
- Are duplicates gone?
- Are data types correct?
- Are categories standardized?
- Are values within reasonable ranges?
- Did cleaning accidentally remove too many records?
- Do summary statistics still make sense?
- Does the dataset match the original business rules?
Compare the dataset before and after cleaning. For example, if you started with 100,000 records and ended with 42,000, you should know exactly why 58,000 records disappeared.
Good data cleaning is traceable and reproducible. Whenever possible, document your transformations rather than manually editing files.
11. Automate the Process
If you clean the same dataset manually every week, you are building a recurring opportunity for human error.
Where possible, turn your cleaning process into a reusable pipeline. Automation makes the process faster, more consistent, and easier to reproduce.
Messy data is not an unusual situation for a data scientist. In fact, cleaning data is often where much of the real work happens. The most important lesson is that data cleaning is not simply about making a dataset look neat. It is about improving its accuracy, consistency, reliability, and usefulness without destroying information that matters.
Approach every dataset like an investigator. Inspect it before changing it. Question unusual values. Understand why information is missing. Standardize what should be consistent. Validate your assumptions. And, most importantly, remember that every cleaning decision can affect the conclusions you eventually draw.
A beautifully designed machine learning model trained on bad data is still a bad model.
As the saying goes, garbage in, garbage out. If you want reliable insights, start by giving your analysis reliable data. If all of this sounds like theory to you and you want to gain real-world experience but no recruiter will give you the job because they equally need you to have experience, then our Data Science Work Experience Program will solve your problem. Click here to speak with us and find out how you can join the next cohort.
