Identify and handle missing values and duplicates

To identify and handle missing values and duplicates, first measure what is missing and understand what is repeated. An empty cell is not zero, and two students may share the same characteristics. Automatically deleting or filling in these entries can therefore change the meaning of the table. We will use pandas to assess data quality, compare several choices, and keep a record of the transformations.

Before you begin. You can edit and run the Python cells on this page, except for the library setup cell. Run them in order: Python loads on the first run, and the cells then share the same memory. Reloading the page or restarting Python starts a new session. To work locally, follow the Python, uv, VS Code and Jupyter guide and select the kernel for your .venv environment.

# Check for libraries before installing anything.
from importlib.util import find_spec
import subprocess
import sys

missing = [name for name in ("pandas", "openpyxl") if find_spec(name) is None]
if missing:
    subprocess.run(
        ["uv", "pip", "install", "--python", sys.executable, *missing],
        check=True,
    )
else:
    print("pandas and openpyxl are already installed.")

pandas and openpyxl are already installed.

1. Load a view suited to the task

Preparation starts with selecting the columns you need. In the regression dataset, the target is Exam_Score, and Result is excluded when you read the file. We keep the other 19 variables to examine missing entries and repeated rows. The read_excel function loads the Données sheet into a DataFrame, the tabular data structure used by pandas.

# Read the workbook placed next to the notebook.
import pandas as pd

filename = "StudentPerformanceFactors_Reg_Classif.xlsx"
df = pd.read_excel(
    filename, sheet_name="Données", engine="openpyxl",
    usecols=lambda name: name != "Result",
)

# To use CSV, replace the Excel loading block with:
# df = pd.read_csv("StudentPerformanceFactors_Reg_Classif.csv",
#                  usecols=lambda name: name != "Result")
print("Observations:", len(df))
print("Columns:", df.shape[1])

Observations: 6607
Columns: 20

The result contains 6,607 observations and 20 columns. To use CSV, replace the pd.read_excel(...) block with the two lines of the pd.read_csv(...) alternative, removing their # characters. You don’t need to read and combine both files. They contain the same observations in the same order.

Keep the original file unchanged. The names complete_rows, documented and workshop will refer to separate results. This lets you compare each decision with the source and reverse a choice without recovering an overwritten file.

2. Identify and count missing values

A missing value is information that is unavailable for an observation and a variable. pandas represents it using markers such as NaN or pd.NA, depending on the column type. Use isna() to detect it: this method produces a table of booleans, with True at missing entries and False elsewhere. The missing-data documentation explains these representations.

Missing information differs from an observed value: zero tutoring sessions is a known count, whereas a missing entry means the count was not recorded. Similarly, No as the response to an activity is still a known category. Replacing all missing entries with zero or No would introduce a claim that the file does not support.

Inspect missing values in eight observations

Let us display the first eight incomplete rows and the three affected columns. The selection preserves the file order. The original table has a zero-based index. Filtering preserves these index labels, so the excerpt begins at index 33 and contains gaps. This index helps locate a row in the loaded table; it is not a student identifier.

# Inspect missing entries actually present in the file.
missing_columns = [
    "Teacher_Quality", "Parental_Education_Level", "Distance_from_Home"
]
incomplete_rows = df.isna().any(axis=1)
print(df.loc[incomplete_rows, missing_columns].head(8).to_string())

    Teacher_Quality Parental_Education_Level Distance_from_Home
33           Medium                  College                NaN
127             NaN              High School                Far
240          Medium                      NaN           Moderate
275          Medium              High School                NaN
316          Medium                  College                NaN
359          Medium              High School                NaN
380          Medium              High School                NaN
396             NaN                  College           Moderate

The row at index 33 has no distance recorded; the row at index 127 has no teacher quality recorded; the row at index 240 has no parental education level recorded. The same NaN display marks missing information in different columns. It tells us neither why the information is missing nor what the value should have been.

Count cells and rows

isna().sum() counts missing values in each column. To identify affected rows, any(axis=1) checks whether any entry in a row is missing. The percentage below divides each count by all 6,607 observations, not just the incomplete rows.

# Count by column, then distinguish cells from observations.
missing_mask = df.isna()
counts = missing_mask.sum()
summary = pd.DataFrame({
    "Missing": counts,
    "Percentage": (counts / len(df) * 100).round(2),
})
print(summary.loc[summary["Missing"] > 0].to_string())
print("Missing cells:", int(missing_mask.sum().sum()))
print("Affected observations:", int(missing_mask.any(axis=1).sum()))

                          Missing  Percentage
Teacher_Quality                78        1.18
Parental_Education_Level       90        1.36
Distance_from_Home             67        1.01
Missing cells: 235
Affected observations: 229

There are 235 missing cells, affecting 229 observations, or about 3.47% of the 6,607 rows. These counts differ because one observation can contain several missing values. Every numerical column in this view is complete, so this version of the file has no missing numerical values to impute.

Before interpreting these counts, check the data-entry conventions used by your source. A string containing spaces or a code such as ? may require a specific reading rule; isna() cannot identify every domain-specific code on its own. Here, we count the missing entries recognized when reading the supplied workbook. The table alone does not establish their cause.

3. Choose how to handle missing values

Handling missing values requires a justified decision. Can you recover the information from a reliable source? Is the variable needed for the analysis? Are missing entries concentrated in a particular group? A small percentage of missing values does not answer these questions. Removing rows can reduce the available information and change the population being studied; replacing a value can create artificial similarities.

Imputation means replacing missing information with a chosen or estimated value. The rule depends on what the variable means and on the purpose of the analysis.

You can also exclude a variable from an analysis if it is unusable or of little relevance. This removes a column, whereas excluding observations removes rows: the information lost is different. Justify the choice based on the objective and the available information.

The type of variable guides the choice: a duration such as Hours_Studied expresses a quantity, whereas Teacher_Quality expresses a category. A string may hold a category, such as Medium, or free text. Conversely, a category may be stored as a numerical code, which does not make it a quantity. Choose the rule based on the field’s meaning, not just its pandas data type.

Measure the effect before removing rows

Here, dropna() returns rows with no missing entries. The subset argument restricts the condition to specific columns, as explained in the dropna documentation. Let us compare two independent rules:

# Compare two removal rules using separate copies.
complete_rows = df.dropna()
with_quality = df.dropna(subset=["Teacher_Quality"])

print("No missing entries:", len(complete_rows))
print("Teacher_Quality recorded:", len(with_quality))
print("Rows removed by dropna():", len(df) - len(complete_rows))

No missing entries: 6378
Teacher_Quality recorded: 6529
Rows removed by dropna(): 229

Removing every incomplete row would retain 6,378 rows and remove 229. Requiring a recorded value only for Teacher_Quality would retain 6,529 rows. These choices serve different purposes. If an analysis does not use distance, a missing distance alone does not justify excluding an observation from that analysis.

Neither result proves that the retained rows represent the excluded rows. When the information is available, comparing the characteristics of the affected groups is a useful check. Here, the calculation measures row loss; it does not establish deletion as a suitable strategy for a future model.

Document missing values and preserve their locations

Our three columns with missing values are categorical. For an exploratory working copy, we can write Unknown in missing entries and add a missing-value indicator, a boolean column that records their original locations. fillna replaces only values recognized as missing; existing categories remain unchanged. See the fillna documentation.

# Retain rows and make missing information visible.
documented = df.copy()
for column in missing_columns:
    documented[column + "_missing"] = df[column].isna()
    documented[column] = df[column].fillna("Unknown")

print("Observations retained:", len(documented))
print("Originally missing:", int(documented.filter(like="_missing").sum().sum()))
print("Remaining missing:", int(documented.isna().sum().sum()))

Observations retained: 6607
Originally missing: 235
Remaining missing: 0

The copy retains 6,607 observations. Its three indicators still record the 235 original missing entries, even though the table cells now contain a label. “No remaining missing values” therefore means that the representation has changed. It does not mean that we have recovered the missing information.

The Unknown label may need encoding before training, depending on the chosen model. It may also group together several reasons for missing information. Keeping a record lets you revisit preparation when the documentation or the question changes.

Impute a categorical variable with its most frequent category

For a categorical variable, a simple rule is to use the mode, its most frequent category. The scikit-learn imputation documentation describes this strategy, along with the mean and median for numerical variables.

# Illustrate replacement with the most frequent category.
quality_mode = df["Teacher_Quality"].mode().iloc[0]
imputed_quality = df["Teacher_Quality"].fillna(quality_mode)

print("Observed mode:", quality_mode)
print("Values replaced:", int(df["Teacher_Quality"].isna().sum()))
print("Missing after replacement:", int(imputed_quality.isna().sum()))

Observed mode: Medium
Values replaced: 78
Missing after replacement: 0

In this variant, the 78 missing values in Teacher_Quality are replaced with Medium. The cell starts from the source and does not modify the documented copy. This result demonstrates the replacement process; it does not establish that Medium was the actual value for those 78 observations. If several modes existed, iloc[0] would select one according to the returned order, another choice that would need to be documented.

For a future predictive evaluation, the replacement value, whether mean, median, or mode, must be calculated using only the training data, then reused on the validation or test set. Calculating it from the full dataset uses information reserved for evaluation; this is data leakage. A pipeline chains preprocessing steps and the model together and helps maintain this separation, as explained in scikit-learn’s common pitfalls. The preceding cell remains a descriptive example, without model evaluation.

4. Examine duplicates before removing them

An exact duplicate row repeats an entire row: every column in the dataset being examined contains the same value. Rows sharing only study hours, attendance, or a few other fields are not duplicates for that reason alone. By default, duplicated() marks additional occurrences and keeps the first as the reference. The index is not treated as a table variable. The subset parameter limits the comparison to selected columns; its result identifies matching values in those fields without establishing an exact duplicate of the whole row. We do not use it to identify exact duplicates in this example. See the duplicated documentation.

duplicated() compares data as stored. Missing entries in the same columns may be treated as matching during this comparison. Two rows with the same known values and the same missing entries therefore do not prove that the unknown information is identical. This technical check does not replace checking where the records came from.

# Look for repeated rows across all columns in the view.
print("Exact duplicates:", int(df.duplicated().sum()))
print("Observations after deduplication:", len(df.drop_duplicates()))

Exact duplicates: 0
Observations after deduplication: 6607

The source contains no exact duplicates in the regression dataset. drop_duplicates() therefore, it retains all 6,607 rows. This does not establish that a person never appears more than once: the file provides no student identifier with which to verify identity.

Two rows with the same study hours and attendance may describe different students. Conversely, two records for the same student may differ because of a correction or a different reporting period. First, define what a row represents and which keys should be unique; do not delete observations simply because a few values match.

Distinguish duplicate rows from repeated identifiers

Repeated identifiers require an additional check: if an ID must be unique for each observation, a repeated ID indicates a key problem even when other fields differ. Examine the records and their source before deciding what to keep. Matching values in ordinary columns do not replace this identity check.

The rule depends on what a row represents. If a person legitimately appears once per period, their personal identifier may repeat; the person–period pair must be unique. This workbook provides no student ID, and its pandas index is not a substitute. We therefore check duplicates across all twenty columns in the regression view without inventing an identifier.

Inspect repetitions with a known origin

Take the first eight observations from the regression dataset and append a complete copy of the first two: all twenty columns are copied. This operation affects only workshop. pd.concat combines whole rows; ignore_index=True assigns a new index without changing their values.

# Copy two entire rows, including all twenty columns.
workshop = pd.concat([df.head(8), df.head(2)], ignore_index=True)
print("Rows in the working example:", len(workshop))
print("Extra copies:", int(workshop.duplicated().sum()))
print("Rows in repeated groups:", int(workshop.duplicated(keep=False).sum()))
print("Row 0 matches its copy at 8:", workshop.iloc[0].equals(workshop.iloc[8]))
print("Row 1 matches its copy at 9:", workshop.iloc[1].equals(workshop.iloc[9]))

Rows in the working example: 10
Extra copies: 2
Rows in repeated groups: 4
Row 0 matches its copy at 8: True
Row 1 matches its copy at 9: True

The working example contains ten rows, including two extra copies. With keep=False, duplicated marks all four rows in the two repeated groups: the first occurrences and their copies. Those four rows are therefore not all surplus. The two equals comparisons confirm that rows at positions 0 and 8, and then 1 and 9, match across all twenty columns.

Remove only confirmed copies

Here, we know the origin of the repeated rows: we have just added them. drop_duplicates(keep="first") keeps the first occurrence and returns a new table. Its behavior is described in the drop_duplicates documentation.

# Remove the two copies whose origin is known.
unique_workshop = workshop.drop_duplicates(keep="first")
print("Before:", len(workshop))
print("After:", len(unique_workshop))
print("Rows removed:", len(workshop) - len(unique_workshop))

Before: 10
After: 8
Rows removed: 2

The excerpt returns to eight rows. Removing duplicates with keep=False would remove every occurrence in each group, including the first two rows: that is a different rule. With real collected data, also check dates, domain-specific keys and sources before choosing which occurrence to retain.

Imputation may make previously different rows identical. Examine the origin of repetitions in the source before filling missing entries, and do not automatically run deduplication again after each replacement. Distinct observations do not become confirmed copies merely because preprocessing has made them similar.

5. Record decisions and prepare the next steps

Keep the two tasks separate

Check the classification dataset preparation independently. It contains the Result target, supplied with Success or Fail labels, and excludes Exam_Score. The next cell reads these labels without reconstructing them. If you chose CSV, use the commented alternative in this cell as well:

# Read the supplied labels, excluding the other target.
df_classification = pd.read_excel(
    filename, sheet_name="Données", engine="openpyxl",
    usecols=lambda name: name != "Exam_Score",
)
# CSV alternative:
# df_classification = pd.read_csv("StudentPerformanceFactors_Reg_Classif.csv",
#                                usecols=lambda name: name != "Exam_Score")
print("Missing Result targets:", int(df_classification["Result"].isna().sum()))
print("Exact duplicates:", int(df_classification.duplicated().sum()))

Missing Result targets: 0
Exact duplicates: 0

This target has no missing values, and the classification dataset has no exact duplicates. If a supervised target were missing in another version, assigning the majority class would not create a verified label. You would need to recover the information or exclude the row from the relevant supervised training. The target is therefore not handled automatically as an incomplete input feature.

Export a documented copy

Let us keep the variant that makes missing categorical entries explicit. Its export contains the twenty regression columns and three missing-value indicators, for a total of 23 columns. index=False prevents the pandas index from being added to the CSV. The output has a different filename from the original.

# Save a result separately from the original file.
output = "Regression_AbsencesDocumentees.csv"
documented.to_csv(output, index=False, encoding="utf-8")
print("File created:", output)
print("Shape:", documented.shape)

File created: Regression_AbsencesDocumentees.csv
Shape: (6607, 23)

You can download the CSV copy with documented missing values, produced by the supplied notebook. It retains 6,607 observations. This file supports further exploration; it replaces neither the original data nor the preparation required for training.

Include a short note with the result: source file, affected columns, counts before and after, applied rule and limitations. For our copy, record that the 235 missing categorical entries were given the Unknown label, three indicators preserve their locations, and no source rows were removed. The two deleted copies belong only to the working example.

Practice: count multiple missing values and repeated rows

Using the regression dataset df, count the observations with at least two missing values among the three affected columns. Then count the extra copies in workshop. Explain why the number of rows marked with keep=False is not directly the number of rows to remove.

# Count multiple missing entries and extra copies.
multiple_missing = df[missing_columns].isna().sum(axis=1).ge(2)
print("At least two missing values:", int(multiple_missing.sum()))
print("Extra copies in the working example:", int(workshop.duplicated().sum()))

At least two missing values: 6
Extra copies in the working example: 2

Common mistakes

• Confusing missing information with zero. Zero is an observed value; use it as a replacement only when the field’s meaning justifies it.
• Applying dropna() without examining the columns. First measure the loss, then specify the fields needed for the analysis.
• Using == NaN to detect missing values. Use isna(), which handles the missing-value markers recognized by pandas.
• Deleting rows that share a few values. A partial comparison does not prove that observations are identical.
• Presenting an imputed value as recovered information. Record the rule and, when useful, a missing-value indicator.
• Running a cell before loading its data. Here, a NameError for df means you must run the data-loading cell in the current session.

Key takeaways and continuing the learning path

Assess the data before transforming it: count missing values, distinguish cells from observations and justify every deletion or replacement. An exact duplicate repeats an entire row. Examine a repeated identifier against the expected uniqueness rule. The workbook results are precise: 235 missing entries across 229 rows, three affected categorical variables, and no exact duplicates in either view.

Sources and documentation

• Jake VanderPlas, Python Data Science Handbook, 2nd edition, Early Release version, chapter 3, “Data Manipulation with Pandas,” section “Handling Missing Data.” This section covers missing-value markers and operations for detection, removal, and replacement. An introduction to these concepts is available on the author’s website.
• Aurélien Géron, Hands-On Machine Learning with Scikit-Learn and PyTorch, 2025, chapter 2, “End-to-End Machine Learning Project,” section “Clean the Data,” pp. 70–71. The passage distinguishes removing rows, removing a variable, and imputation; replacement values are learned from training data and reused on the other sets. The book’s companion notebooks are available in the author’s repository.

The outputs were produced with Python 3.14.8, pandas 3.0.6 and openpyxl 3.1.5 using the supplied workbook. The documentation linked throughout the text describes the methods. For further reading: missing-data representations in pandas, imputation strategies in scikit-learn, and preventing data leakage. The Student Performance Factors dataset page provides its description and license.