Understanding Dirty Data

Understanding Dirty Data

Data cleaning and preparation is one of the most important stages of the data
analysis process. Before data can be analyzed effectively, analysts must
inspect it, identify problems, correct errors, and prepare it for reliable
analysis.

Raw data collected from real-world systems is rarely perfect. It may contain
missing values, duplicate records, spelling differences, incorrect formats,
invalid values, inconsistent categories, typing mistakes, and other problems.
Data containing these kinds of problems is commonly referred to as
dirty data.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain what dirty data means.
• Identify common forms of dirty data.
• Explain why data cleaning is necessary.
• Understand how dirty data affects analysis.
• Identify data-quality problems in a dataset.
• Distinguish between data errors and unusual but valid observations.
• Explain the importance of documenting cleaning decisions.
• Describe a basic data-cleaning workflow.

1. What Is Dirty Data?

Dirty data is data that contains errors, inconsistencies, missing information,
duplicates, invalid values, incorrect formats, or other quality problems that
can reduce the reliability of analysis.

Dirty data does not necessarily mean that every value is wrong. A dataset can
contain mostly correct information while still having a few records that need
attention.

For example:

This small dataset already contains several possible quality problems:

• A missing age.
• A possible duplicate record.
• An age stored as text instead of a number.
• Inconsistent capitalization of the course name.

2. Why Dirty Data Matters

Data analysis depends on the quality of the underlying data. If the input data
is unreliable, the resulting calculations, charts, reports, and decisions may
also be unreliable.

This is often described using the principle:

Garbage In, Garbage Out (GIGO).

The principle means that poor-quality input can produce poor-quality output,
even when the analytical method itself is correct.

3. Examples of Dirty Data

Common examples include:

• Missing values.
• Duplicate records.
• Incorrect spelling.
• Incorrect data types.
• Invalid dates.
• Inconsistent category names.
• Extra spaces.
• Incorrect numerical values.
• Outliers.
• Mixed units of measurement.
• Incorrect capitalization.
• Broken or incomplete records.
• Impossible values.
• Formatting inconsistencies.

4. Missing Values

A missing value occurs when an expected piece of information is not available.

For example:

Missing values require investigation. An analyst should determine why the
values are missing before deciding what to do with them.

Possible approaches include leaving them missing, removing affected records,
or replacing values using an appropriate method.

Missing values will be studied in detail in the next lesson.

5. Duplicate Records

A duplicate record occurs when the same record is entered more than once.

For example, if a customer purchases one product but the transaction is
recorded twice, the total sales could be incorrectly increased.

However, not every repeated value represents a duplicate record. Two
customers can have the same name, or a customer can legitimately make
multiple purchases.

Therefore, duplicate detection should consider the business meaning of the
data.

6. Incorrect Formats

Data can be stored in a format that does not match its intended meaning.

Examples include:

• A date stored as plain text.
• A number stored as text.
• Currency values stored inconsistently.
• Phone numbers containing inconsistent formatting.
• Percentages stored as whole numbers in some records and decimals in others.

Incorrect formats can cause formulas, sorting, filtering, and statistical
calculations to behave unexpectedly.

7. Inconsistent Categories

The same category can be written in different ways.

For example:

Lagos
lagos
LAGOS
Lagos 
lagos state

If these values represent the same category, they should normally be
standardized before analysis.

Otherwise, a Pivot Table or grouping operation may treat them as separate
categories.

8. Spelling Errors

Typing mistakes can create incorrect categories or values.

For example:

Data Analysis
Data Analysiss
Data Analaysis
Data analysis

An analyst should determine whether these are separate categories or spelling
variations representing the same category.

9. Extra Spaces

Extra spaces are a common data-quality problem, particularly when data is
entered manually or imported from another system.

For example:

" Lagos"
"Lagos "
" Lagos "
"Lagos"

These values may look identical to a human but can behave differently during
exact matching and lookup operations.

Excel’s TRIM() function can help remove unnecessary spaces
from text.

=TRIM(A2)

10. Incorrect Numerical Values

A dataset may contain numbers that are impossible or clearly inconsistent
with the business context.

For example:

• A person’s age recorded as 250.
• A test score recorded as 150 when the maximum is 100.
• A quantity recorded as -500 when negative quantities are not allowed.
• A product price recorded as ₦0 when the business does not sell products for free.

Such values require investigation rather than automatic deletion.

11. Invalid Dates

Dates can become problematic when different formats are mixed.

For example:

01/02/2026
2026-02-01
February 1, 2026
01-02-26

Depending on the system and regional settings, a date such as
01/02/2026 could potentially be interpreted differently.

Standardizing date formats helps prevent incorrect analysis.

12. Mixed Units

Data becomes difficult to compare when measurements use different units.

For example:

• Some weights recorded in kilograms and others in pounds.
• Some distances recorded in kilometers and others in miles.
• Some prices recorded in naira and others in dollars.

Before analysis, the units should be identified and standardized where
appropriate.

13. Dirty Data and Data Quality

Data quality describes how suitable data is for its intended purpose.

Important data-quality dimensions include:

• Accuracy.
• Completeness.
• Consistency.
• Validity.
• Uniqueness.
• Timeliness.
• Relevance.

Dirty data can affect one or more of these dimensions.

14. Accuracy

Accuracy refers to whether the data correctly represents the real-world
value it is intended to describe.

For example, if a student’s actual score is 85 but the database contains 58,
the data is inaccurate.

15. Completeness

Completeness refers to whether the required information is available.

A dataset containing thousands of customers but missing phone numbers for
most records may have a completeness problem.

16. Consistency

Consistency means that information follows the same rules across the dataset.

For example, if gender is represented as “Male” and “Female” in one part of
the dataset but “M”, “F”, “Man”, and “Woman” elsewhere, the data may need
standardization.

17. Validity

Validity refers to whether a value follows the rules defined for a field.

For example, if an examination score must be between 0 and 100, a score of
150 is invalid.

18. Uniqueness

Uniqueness means that records that should be unique are not unnecessarily
repeated.

A customer ID, transaction ID, or student ID may be expected to uniquely
identify a record.

19. Timeliness

Timeliness refers to whether data is current enough for the purpose for which
it is being used.

A dashboard showing last year’s customer information may be inappropriate
for a decision requiring current customer information.

20. Relevance

Data should be relevant to the question being analyzed.

Collecting large amounts of unnecessary information can increase complexity
without improving the analysis.

21. Dirty Data Can Affect Calculations

Consider a sales dataset containing one transaction twice.

If both records are included in a total sales calculation, revenue will be
overstated.

Similarly, if some values are stored as text rather than numbers, calculations
may fail or exclude those values.

22. Dirty Data Can Affect Averages

Suppose five students have scores:

70
75
80
85
90

If an incorrect score of 900 is accidentally included, the average becomes
misleading.

This demonstrates why analysts must inspect unusual values before performing
statistical calculations.

23. Dirty Data Can Affect Charts

Dirty data can also produce misleading charts.

For example, if “Lagos”, “lagos”, and “LAGOS” are treated as separate
categories, a regional chart may display three different Lagos categories
instead of one.

24. Dirty Data Can Affect Pivot Tables

Pivot Tables group values based on the underlying data.

If category names are inconsistent, the Pivot Table may create multiple
categories that should actually be one.

Cleaning the source data before creating the Pivot Table can prevent this
problem.

25. Dirty Data Can Affect Lookup Functions

Lookup functions such as XLOOKUP and VLOOKUP depend on matching values.

Small differences can prevent a successful match.

For example:

Student-001
Student-001 
 Student-001

These values may appear identical but can behave differently when matching
records.

26. Dirty Data in Real-World Systems

Dirty data can come from many sources.

• Manual data entry.
• Online forms.
• Spreadsheets.
• Databases.
• APIs.
• Website scraping.
• Point-of-sale systems.
• Mobile applications.
• IoT devices.
• Legacy systems.
• Data imports.
• System migrations.

27. Manual Data Entry

Manual entry is one of the common sources of data-quality problems.

Users may accidentally:

• Type the wrong value.
• Leave fields blank.
• Use different formats.
• Misspell names.
• Enter numbers incorrectly.
• Use inconsistent categories.

Good data-entry controls can reduce these problems.

28. Data Import Problems

Importing data from another system can introduce new inconsistencies.

For example, one system may store dates as:

2026-10-06

while another may use:

06/10/2026

The analyst must understand the source system and import rules before
cleaning the data.

29. Data Profiling

Data profiling is the process of examining a dataset to understand its
structure, values, patterns, and quality problems.

During profiling, an analyst may inspect:

• Number of rows.
• Number of columns.
• Data types.
• Missing values.
• Unique values.
• Duplicate records.
• Minimum and maximum values.
• Common categories.
• Unexpected values.

30. Questions to Ask During Data Profiling

An analyst can ask:

• How many records are present?
• What does each column represent?
• Which columns contain missing values?
• Which columns should be numeric?
• Which columns should contain dates?
• Are there duplicate records?
• Are category values consistent?
• Are there impossible values?
• Are there unusual values?
• Are there unexpected blanks?

31. Data Cleaning vs Data Analysis

Data cleaning is not the same as data analysis.

Data analysis attempts to discover patterns, relationships, trends, and
insights from data.

Data cleaning prepares the data so that analysis can be performed reliably.

Both activities are important parts of the overall analytical workflow.

32. Basic Data Cleaning Workflow

1. Understand the purpose of the dataset.
2. Inspect the structure of the data.
3. Profile the dataset.
4. Identify quality problems.
5. Determine the cause of problems where possible.
6. Clean or transform the data.
7. Validate the cleaned dataset.
8. Document important changes.
9. Save the cleaned version separately.
10. Prepare the dataset for analysis.

33. Do Not Automatically Delete Bad-Looking Data

One of the most important principles in data cleaning is that unusual data
should not automatically be deleted.

An unusual value may be a genuine observation.

For example, if most customers spend between ₦10,000 and ₦100,000 but one
customer spends ₦5,000,000, the value may appear unusual. However, it could
be a legitimate large purchase.

The analyst should investigate the value before deciding whether it is an
error.

34. Error vs Unusual Observation

35. Documenting Cleaning Decisions

Data cleaning decisions should be documented whenever possible.

Documentation can include:

• What was changed?
• Why was it changed?
• What rule was used?
• Which records were affected?
• Was information removed?
• Was information replaced?
• What was the original source?

Documentation improves transparency and reproducibility.

36. Keep the Original Data

Whenever possible, keep an untouched copy of the original dataset before
cleaning it.

This makes it possible to compare the original and cleaned versions and
provides a recovery point if a cleaning decision needs to be reversed.

A useful approach is:

01_raw_data
02_cleaned_data
03_analysis
04_reports

37. Excel Tools for Identifying Dirty Data

Microsoft Excel provides several useful tools for identifying and cleaning
data-quality problems.

• Sort.
• Filter.
• Conditional Formatting.
• Remove Duplicates.
• Find and Replace.
• Data Validation.
• Text to Columns.
• TRIM.
• CLEAN.
• VALUE.
• COUNTIF.
• COUNTIFS.
• IF.
• IFERROR.

38. Python and Data Cleaning

Python can also be used to inspect and clean datasets, particularly when
datasets become too large or complex for manual spreadsheet processing.

The pandas library is commonly used for data cleaning and preparation.

import pandas as pd

df = pd.read_csv("students.csv")

print(df.head())
print(df.info())
print(df.isna().sum())
print(df.duplicated().sum())

The example above loads a CSV file and provides basic information about
missing values and duplicate records.

39. Simple Excel Data-Quality Example

Suppose names are stored in column A.

To remove unnecessary spaces:

=TRIM(A2)

To check whether a cell is blank:

=IF(A2="","Missing","Available")

To count how many times a value occurs:

=COUNTIF(A:A,A2)

40. Business Example

Imagine an online store has 50,000 transaction records.

During initial inspection, the analyst discovers:

• 300 duplicate transactions.
• 500 missing customer IDs.
• Several inconsistent product names.
• Some dates stored as text.
• Some prices stored as text.
• Some negative quantities.

If the analyst calculates total revenue immediately without investigating
these issues, the results may be misleading.

The appropriate approach is to profile the data, investigate the problems,
apply suitable cleaning rules, validate the result, and only then proceed
with analysis.

41. Student Dataset Example

Suppose a training center has a student dataset containing:

• Student ID.
• Name.
• Gender.
• Age.
• Course.
• Score.
• Attendance.

Potential dirty-data problems could include:

• Duplicate student IDs.
• Missing ages.
• Scores above 100.
• Different spellings of course names.
• Extra spaces in names.
• Attendance entered as both percentages and decimals.

42. Data Cleaning Should Be Rule-Based

Cleaning should not be random. Analysts should define rules based on the
meaning and requirements of the dataset.

For example:

• Student scores must be between 0 and 100.
• Student IDs must be unique.
• Course names must come from an approved list.
• Dates must use a standard format.
• Required fields should not be blank.

43. The Importance of Business Context

A cleaning decision cannot always be made from the data alone.
Business or domain knowledge may be required.

For example, a negative value could be invalid for product quantity, but
negative values may be perfectly valid in another context, such as account
adjustments or financial transactions.

Therefore, analysts should understand what the data represents before
changing it.

44. Data Lineage

Data lineage describes where data came from and how it changed as it moved
through different systems or processing stages.

Understanding data lineage helps analysts determine:

• Where the data originated.
• Which systems processed it.
• What transformations occurred.
• Who changed it.
• Which version should be trusted.

45. Cleaning Does Not Mean Making Data Perfect

The goal of data cleaning is not necessarily to create a dataset with no
unusual values or no missing information.

The goal is to make the dataset sufficiently reliable and appropriate for
its intended analytical purpose.

Some missing values may remain. Some unusual values may be legitimate.
The important thing is that the analyst understands and appropriately handles
the quality issues.

46. Practical Exercise

Create a small Excel dataset containing at least 20 student records.
Intentionally introduce several data-quality problems.

Include examples of:

• Missing values.
• Duplicate records.
• Extra spaces.
• Different capitalization.
• Incorrect score values.
• Different date formats.
• Inconsistent course names.

Use Excel’s sorting, filtering, conditional formatting, Find and Replace,
and formulas to identify the problems.

Do not clean the dataset completely yet. The purpose of this exercise is to
learn how to recognize dirty data before learning detailed cleaning
techniques in the following lessons.

47. Knowledge Check

1. What is dirty data?
2. Why can dirty data produce misleading analysis?
3. What does GIGO mean?
4. Give five examples of dirty data.
5. What is a duplicate record?
6. Why should unusual values not automatically be deleted?
7. What is data profiling?
8. Why is business context important when cleaning data?
9. Why should the original dataset be preserved?
10. Name five Excel tools that can help identify data-quality problems.

48. Key Terms

• Dirty Data: Data containing errors, inconsistencies, missing information, duplicates, or other quality problems.
• Data Cleaning: The process of identifying and correcting or appropriately handling data-quality problems.
• Data Profiling: Examining a dataset to understand its structure, values, patterns, and quality.
• Duplicate: A repeated record that may represent the same real-world event or entity.
• Missing Value: An expected value that is unavailable.
• Validity: Whether a value follows defined rules.
• Accuracy: Whether a value correctly represents reality.
• Consistency: Whether data follows the same rules across the dataset.
• Data Lineage: The history and origin of data as it moves through systems and transformations.
• GIGO: Garbage In, Garbage Out.

49. Key Takeaways

• Real-world data is rarely perfect.
• Dirty data can produce unreliable analytical results.
• Common problems include missing values, duplicates, inconsistent formats, and invalid values.
• Data profiling helps analysts discover quality problems.
• Unusual values should be investigated before being removed.
• Cleaning decisions should be based on business and analytical context.
• The original dataset should be preserved whenever possible.
• Cleaning rules should be documented.
• Excel provides many tools for identifying data-quality problems.
• Python and pandas can automate data-cleaning tasks for larger datasets.

50. Summary

Dirty data is one of the major challenges faced by data analysts. Data may
contain missing values, duplicates, incorrect formats, spelling mistakes,
inconsistent categories, invalid values, and other quality problems.

Before performing analysis, an analyst should understand the purpose and
structure of the dataset, profile the data, identify quality problems,
investigate their causes, and establish appropriate cleaning rules.

Good data cleaning requires more than simply deleting unusual records.
Analysts must consider business context, preserve useful information,
document their decisions, and validate the resulting dataset.

In the next lesson, we will examine one of the most common data-quality
problems in detail: Missing Values.

Next Lesson

Module 3, Lesson 2 — Missing Values