Missing Values

Missing Values

Missing values are one of the most common data-quality problems encountered
by data analysts. A missing value occurs when information that is expected
for a record or variable is unavailable, unknown, not collected, or not
properly recorded.

Missing values can occur in spreadsheets, databases, surveys, transaction
systems, websites, forms, APIs, and almost every other source of real-world
data.

Learning how to identify, understand, and appropriately handle missing values
is an essential part of preparing data for analysis.

Learning Objectives

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

• Explain what a missing value is.
• Identify different ways missing values can appear.
• Explain common causes of missing data.
• Measure missing values in a dataset.
• Distinguish between missing, zero, and not-applicable values.
• Explain deletion and imputation approaches.
• Choose appropriate strategies for different situations.
• Use Excel and Python to identify missing values.
• Validate a dataset after handling missing values.

1. What Is a Missing Value?

A missing value is an expected piece of information that is not available in
a dataset.

For example:

Mary’s age and David’s score are missing.

2. Missing Does Not Always Mean Zero

A very important distinction in data analysis is the difference between a
missing value and zero.

Suppose a sales dataset contains a quantity column.

• 0 may mean that no units were sold.
• Blank may mean that the quantity was not recorded.

These two situations have different meanings and should not automatically be
treated as the same.

3. Missing Does Not Always Mean Not Applicable

A field may also be empty because the question does not apply to a particular
record.

For example, a dataset may contain a column called “Company Name” for
employees.

For a person who is unemployed, the field might legitimately be not
applicable rather than missing because of an error.

4. Common Representations of Missing Data

Missing values can appear in many forms.

Blank
NULL
NA
N/A
n/a
NaN
Unknown
Not Available
Not Applicable
-
?

The correct interpretation depends on the system and dataset.

5. Blank Cells

In Excel, a missing value may simply appear as an empty cell.

For example:

Student     Age     Score
John        22      85
Mary                78
David       24

Empty cells should be investigated before deciding how they should be
handled.

6. NULL Values

In databases, missing information is commonly represented using
NULL.

NULL means that a value is absent or unknown. It is not the same thing as
zero, an empty string, or the word “NULL”.

7. NaN Values

In Python and data-analysis libraries, missing numerical values may be
represented using NaN, meaning “Not a Number”.

For example, pandas commonly uses NaN or compatible missing-value markers
when numerical data is unavailable.

8. Why Do Missing Values Occur?

Missing values can occur for many reasons.

• A user did not provide an answer.
• A field was not required.
• A system failed to capture information.
• A sensor stopped working.
• A database migration lost information.
• A data-entry operator skipped a field.
• A question was not applicable.
• Information was intentionally withheld.
• An API returned incomplete information.
• A data-processing operation introduced blanks.

9. Missing Data in Surveys

Survey datasets frequently contain missing responses.

A respondent may skip a question because it is sensitive, confusing,
irrelevant, or simply overlooked.

For example, a survey asking for monthly income may contain many blank
responses if participants are uncomfortable providing the information.

10. Missing Data in Business Systems

Business systems can also produce incomplete records.

For example:

• Customers without phone numbers.
• Orders without delivery dates.
• Products without categories.
• Transactions without sales representatives.
• Employees without department information.

11. Missing Data in Sensor Systems

Sensors may temporarily fail to collect measurements.

For example, a temperature sensor expected to record data every minute may
produce a sequence such as:

29.1
29.3
29.4
29.5
29.7
Missing
Missing
30.1
30.2

The analyst should investigate why the readings are missing before deciding
how to handle them.

12. Missing Data in Student Records

A training organization may have records such as:

Before analysis, the analyst needs to determine why these values are missing
and whether they are important for the intended analysis.

13. Why Missing Values Matter

Missing data can affect calculations, visualizations, statistical models,
reports, and decisions.

For example, if many customer income values are missing, an analysis of
average customer income may be unreliable.

14. Missing Data Can Reduce Sample Size

If records containing missing values are removed, the number of records
available for analysis may decrease.

For example, a dataset may contain 10,000 customer records, but if 2,000
records are removed because of missing information, only 8,000 records remain
for the particular analysis.

15. Missing Data Can Introduce Bias

Missing values can sometimes be related to the people, transactions, or
events being studied.

If certain types of records are more likely to be incomplete than others,
simply removing them can change the composition of the dataset.

This can introduce bias into the analysis.

16. Missing Data Patterns

Analysts should examine where missing values occur.

For example:

• Are missing values concentrated in one column?
• Are they concentrated in one group?
• Are they randomly distributed?
• Do missing values occur during a particular period?
• Are they associated with a particular data source?

17. Missing Completely at Random

A missing value may sometimes occur without any obvious relationship to
other variables in the dataset.

This situation is commonly described as
Missing Completely At Random (MCAR).

For example, a small number of records may be missing because of an
unexpected temporary system interruption.

18. Missing at Random

Missingness may also be related to other information that is already
available in the dataset.

This is commonly described as
Missing At Random (MAR).

Understanding the reason for missingness can help determine an appropriate
handling strategy.

19. Missing Not at Random

In some situations, whether a value is missing may be related to the value
itself or an unobserved factor.

This is commonly described as
Missing Not At Random (MNAR).

For example, people with extremely high incomes may be less willing to
report their income than people with moderate incomes.

The important lesson is that the reason for missingness can matter as much
as the missing value itself.

20. Measuring Missing Values

An analyst should measure missingness rather than simply looking at a few
records.

For example, if a dataset has 10,000 records and 500 records have missing
customer phone numbers:

Missing percentage =
500 / 10000 × 100
= 5%

The 5% figure provides useful context for deciding how the field should be
handled.

21. Excel: Counting Blank Cells

Excel’s COUNTBLANK() function can be used to count blank
cells.

=COUNTBLANK(A2:A100)

This returns the number of blank cells in the specified range.

22. Excel: Checking for Missing Values

The IF function can be used to identify blank cells.

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

This can be useful when creating a simple data-quality check.

23. Excel: Counting Specific Missing Markers

If a dataset uses a marker such as “N/A”, COUNTIF can identify it.

=COUNTIF(A2:A100,"N/A")

However, analysts should first understand whether “N/A” means missing,
not applicable, or something else in that dataset.

24. Finding Missing Values with Filters

Excel filters can help locate blank values.

1. Select the dataset.
2. Enable Filter.
3. Open the filter for the relevant column.
4. Select blanks.
5. Review the affected records.

This method is especially useful for inspecting missing records manually.

25. Conditional Formatting

Conditional Formatting can be used to highlight blank cells or unusual
markers.

This makes missing values easier to see in a large worksheet.

26. Python: Detecting Missing Values

Python’s pandas library provides convenient methods for identifying missing
values.

import pandas as pd

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

print(df.isna().sum())

The result shows the number of missing values in each column.

27. Missing Percentage with Python

An analyst can calculate the percentage of missing values in each column.

missing_percentage = df.isna().mean() * 100

print(missing_percentage)

This is useful when profiling large datasets.

28. Finding Rows with Missing Data

To display records containing at least one missing value:

missing_rows = df[df.isna().any(axis=1)]

print(missing_rows)

29. Finding Complete Rows

To identify records that contain no missing values:

complete_rows = df.dropna()

print(complete_rows)

This does not mean that dropping rows is always the correct solution. It
simply demonstrates how complete records can be identified.

30. Option 1: Remove Records

One possible strategy is to remove records containing missing values.

This may be reasonable when:

• Only a very small number of records are affected.
• The records are not important to the analysis.
• The missingness is unlikely to introduce bias.
• The dataset remains sufficiently large.

31. Risks of Removing Records

Removing records can cause problems if many records are affected.

It can also introduce bias when missing records have different
characteristics from complete records.

Therefore, deletion should be a deliberate decision rather than an automatic
reaction.

32. Option 2: Imputation

Imputation means replacing missing values with reasonable estimates or
substitute values.

The appropriate method depends on the type of data and the purpose of the
analysis.

33. Mean Imputation

For numerical data, one simple method is to replace missing values with the
mean.

Suppose the observed scores are:

60
70
80
90

The mean is:

(60 + 70 + 80 + 90) / 4 = 75

A missing score could therefore be replaced with 75 under a simple mean
imputation approach.

However, mean imputation can distort the distribution and should not be used
without considering the context.

34. Median Imputation

The median can sometimes be a better choice than the mean, especially when
the data contains extreme values.

For example, customer income may contain a few extremely high values.
The median may provide a more representative central value than the mean.

35. Mode Imputation

For categorical data, the most frequently occurring category is called the
mode.

A missing category may sometimes be replaced with the mode, depending on
the analytical context.

However, analysts should avoid automatically assuming that the most common
category is the correct replacement.

36. Forward Fill

In time-series data, a previous valid value can sometimes be carried forward
to fill a missing observation.

For example:

Monday     100
Tuesday    105
Wednesday  Missing
Thursday   110

Under an appropriate business rule, Wednesday could temporarily inherit
Tuesday’s value.

This approach is only appropriate when the nature of the data supports it.

37. Backward Fill

Backward filling uses a subsequent value to fill a missing observation.

Again, this should only be used when the data and business context justify
the approach.

38. Interpolation

Interpolation estimates a missing value based on surrounding observations.
It is commonly used in some time-series and numerical datasets.

For example, if a measurement is known before and after a short missing
period, an analyst may estimate the intermediate value.

39. Business Rules

Some missing values should be handled using explicit business rules.

For example, if a customer has never placed an order, an “Order Count”
field may legitimately be zero rather than missing.

This is why data cleaning requires understanding the meaning of each field.

40. Do Not Replace Every Missing Value with Zero

A common mistake is replacing every blank with zero.

This can completely change the meaning of the data.

For example, if a student’s exam score is missing, replacing the blank with
zero incorrectly suggests that the student scored zero.

41. Do Not Replace Every Missing Value with the Mean

Mean imputation is simple but can be inappropriate.

It can reduce natural variation and may produce misleading results if the
missing values follow a particular pattern.

The method should therefore be selected based on the data and analytical
purpose.

42. Missing Values and Statistical Analysis

Missing data can affect averages, totals, percentages, correlations, and
statistical models.

For example, if high-income customers are disproportionately missing from
an income column, calculating the average using only complete records may
underestimate the true average.

43. Missing Values in Pivot Tables

Pivot Tables may display blank categories when source data contains missing
values.

Before building a Pivot Table, analysts should understand whether blanks
represent actual missing data, not-applicable records, or legitimate empty
categories.

44. Missing Values in Lookup Functions

Missing lookup keys can prevent functions such as XLOOKUP or VLOOKUP from
returning the expected result.

For example, if a Student ID is missing, the analyst may not be able to
retrieve the corresponding student information.

Functions such as IFERROR can help provide a more readable result when a
lookup fails.

=IFERROR(XLOOKUP(A2,Students[ID],Students[Name]),"Not Found")

45. Missing Values and Data Quality Rules

A data-quality rule can specify whether a field is allowed to be missing.

For example:

46. Missing Values and Data Validation

Data validation can prevent some missing-value problems before they enter a
dataset.

For example, an Excel form can require a Student ID before a record is
accepted.

Preventing poor data at the point of entry is often better than correcting
the problem later.

47. Missing Data Documentation

When missing values are handled, the analyst should document the decision.

Useful documentation includes:

• The affected column.
• The number of missing records.
• The reason for missingness, if known.
• The chosen handling method.
• The reason for choosing the method.
• The number of records affected.

48. Example: Student Dataset

Suppose a training center has 1,000 students and 20 students have missing
scores because they have not yet completed their examinations.

It would be inappropriate to replace those missing scores with zero.

The correct interpretation may be “assessment not completed.”

The analyst may exclude those students from a completed-student performance
analysis while retaining them in other analyses such as enrollment counts.

49. Example: Customer Dataset

Suppose an e-commerce dataset contains 20,000 customers and 2,000 missing
phone numbers.

If the purpose of the analysis is customer purchasing behavior, the missing
phone numbers may not prevent the analysis.

However, if the purpose is to plan an SMS marketing campaign, the missing
phone numbers become highly relevant.

This demonstrates that missing-value handling depends on the analytical
question.

50. Example: Sales Dataset

Suppose a sales dataset contains:

Transaction ID
Product
Quantity
Price
Salesperson
Date

If the price is missing, calculating revenue for that transaction may be
impossible until the value is investigated.

If the salesperson is missing, the transaction may still be usable for
total company sales but may need to be excluded from salesperson performance
analysis.

51. Column-Level Analysis

Missing values should often be analyzed column by column.

A dataset might contain:

This immediately shows that the phone column requires more attention than
the Student ID column.

52. Row-Level Analysis

Analysts can also investigate how many missing values occur within each
record.

A record with one missing optional field may still be useful, while a record
missing most required fields may need special treatment.

53. Missing-Value Thresholds

Organizations sometimes establish thresholds for deciding how to handle
columns with high levels of missing data.

For example, a team might investigate whether a field containing 80% missing
values is useful enough to retain.

There is no universal percentage that should automatically determine the
decision. The appropriate threshold depends on the dataset, business
requirements, and analytical purpose.

54. Preventing Missing Values

The best way to handle some missing values is to prevent them from occurring
in the first place.

Useful techniques include:

• Required form fields.
• Dropdown lists.
• Data validation.
• Default values where appropriate.
• Clear instructions.
• Automated validation.
• Database constraints.
• Regular quality checks.

55. Missing Values in Excel Tables

Excel Tables make it easier to identify incomplete records because filters
and formulas can be applied consistently across the dataset.

An analyst can use filters to identify blank cells and create calculated
columns for quality checks.

56. Missing Values and Data Cleaning Workflow

1. Identify missing values.
2. Measure how many values are missing.
3. Determine why the values are missing.
4. Assess the importance of the affected field.
5. Choose a handling strategy.
6. Apply the strategy.
7. Validate the result.
8. Document the decision.

57. Practical Exercise

Create an Excel dataset containing at least 30 student records.

Include columns for:

• Student ID.
• Name.
• Age.
• Course.
• Score.
• Phone Number.
• Enrollment Date.

Intentionally leave some values blank.

Then:

1. Count the missing values in each column.
2. Calculate the percentage of missing values.
3. Use filters to locate blank records.
4. Determine which fields are required.
5. Decide how each type of missing value should be handled.
6. Document your decisions.

58. Knowledge Check

1. What is a missing value?
2. Is a missing value the same as zero?
3. What does NULL mean?
4. Give five causes of missing values.
5. What is imputation?
6. What is mean imputation?
7. When might median imputation be useful?
8. Why should missing values not automatically be replaced with zero?
9. How can Excel identify blank cells?
10. How can pandas calculate missing values?
11. Why can deleting records introduce bias?
12. Why should the reason for missingness be investigated?

59. Key Terms

• Missing Value: An expected value that is unavailable.
• NULL: A database representation indicating an absent or unknown value.
• NaN: A common representation of missing numerical data in analytical software.
• Imputation: Replacing missing values with estimated or selected values.
• Mean: The arithmetic average.
• Median: The middle value when observations are ordered.
• Mode: The most frequently occurring value.
• MCAR: Missing Completely At Random.
• MAR: Missing At Random.
• MNAR: Missing Not At Random.

60. Key Takeaways

• Missing values are common in real-world datasets.
• A blank value does not necessarily mean zero.
• A missing value can have different causes and meanings.
• Analysts should measure missingness before deciding how to handle it.
• Deleting records is only one possible approach.
• Imputation can replace missing values when appropriate.
• Mean, median, mode, interpolation, and business rules are possible approaches.
• Replacing all missing values with zero is usually unsafe.
• Missing values can affect analysis and introduce bias.
• Missing-value decisions should be documented and validated.

61. Summary

Missing values are one of the most common challenges in data cleaning and
preparation. They can occur because information was not provided, a system
failed to capture it, a question was not applicable, or an error occurred
during data collection or processing.

An analyst should first identify and measure missing values, investigate why
they are missing, and understand their importance to the intended analysis.
Only then should an appropriate strategy such as deletion, imputation, or a
business-specific rule be selected.

The goal is not simply to remove blanks. The goal is to preserve as much
useful information as possible while ensuring that the resulting dataset is
appropriate and reliable for analysis.

In the next lesson, we will examine another major data-quality problem:
Duplicate Records.

Next Lesson

Module 3, Lesson 3 — Duplicate Records