Diagnosing Data Quality

First lesson of the Data Cleaning and Preparation course, free to read: about 15 minutes, 3 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

Measure missing values, duplicates and distinct values
Spot incorrect types and suspicious values
Produce a summary quality report before any cleaning

Quality diagnosis Detecting missing values Detecting duplicates

Diagnosing data quality

It is often estimated that cleaning takes up a large share of a data project’s time. The temptation is to immediately fix what jumps out at you. Bad idea: start with a complete diagnosis, so you know what to fix, how many rows are affected, and can justify it.

Let’s take a typical customer export:

import io
import pandas as pd

CSV = """id,nom,ville,age,ca
1,Ana Martin,Lyon,34,1250.5
2,Bob Leroy,lyon ,,980
3,Chloé Petit,Paris,29,N/A
3,Chloé Petit,Paris,29,N/A
4,Dina Roux,PARIS,-5,640
5,Eli Faure,,41,0
"""
clients = pd.read_csv(io.StringIO(CSV))

1. Missing values

clients.isna().sum()          # number of missing values per column
clients.isna().mean().round(2) # proportion of missing values
clients[clients["age"].isna()] # see the affected rows

When reading, pandas by default recognizes a list of common markers as missing (empty field, "NA", "N/A", "NaN", "null"…): here, the "N/A" in the ca column does become NaN. On the other hand, in-house codes such as "-", "inconnu" or 999 remain “normal” values: you have to spot them with value_counts() and declare them (next lesson).

2. Duplicates

clients.duplicated().sum()                  # fully identical rows: 1
clients.duplicated(subset=["id"]).sum()     # repeated identifiers
clients[clients.duplicated(subset=["id"], keep=False)]  # show all copies

A fully duplicated row is often an export artifact; a repeated identifier with different values is more serious: you have to decide which version is correct.

3. Types and impossible values

clients.dtypes        # is ca really numeric?
clients.describe()    # min, max: an age of -5 stands out
(clients["age"] < 0).sum()

A numeric column read as text signals a stray value (“12 €”, decimal comma). An absurd minimum or maximum signals an entry error or a special code.

4. Spelling variants

clients["ville"].value_counts(dropna=False)
# Lyon, lyon (with a space), Paris, PARIS, NaN: 3 real cities written in 5 ways

dropna=False also displays the number of missing values. This is often where you discover case, whitespace and accent problems.

5. A quality report

Gather these measures in a table, one row per column:

rapport = pd.DataFrame({
    "type": clients.dtypes.astype(str),
    "manquants": clients.isna().sum(),
    "pct_manquants": clients.isna().mean().round(3),
    "distinctes": clients.nunique(),
})
print(rapport)

Keep this report: it is the starting point for cleaning, and for comparing before / after to prove the effect of your fixes.

Key takeaways

Diagnosis first: missing values, duplicates, types, impossible values, spelling variants.
isna().sum(), duplicated().sum(), describe(), value_counts(dropna=False).
Document your findings to justify each fix.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

CodeDifficulty 1Python in the browser2 hints

Measure missing values and duplicates

From clients, compute:

manquants: a dictionary column → number of missing values;
nb_doublons: the number of fully duplicated rows;
nb_id_repetes: the number of rows whose id has already been seen (duplicated(subset=...));
nb_age_negatif: the number of strictly negative ages.

import pandas as pd

clients = pd.DataFrame({
    "id":   [1, 2, 3, 3, 4, 5, 5],
    "ville": ["Lyon", "lyon ", "Paris", "Paris", "PARIS", None, "Nice"],
    "age":  [34, None, 29, 29, -5, 41, 41],
    "ca":   [1250.5, 980.0, None, None, 640.0, 0.0, 15.0],
})

manquants = ...
nb_doublons = ...
nb_id_repetes = ...
nb_age_negatif = ...

CodeDifficulty 2Python in the browser2 hints

Quality report function

Write rapport_qualite(df) that returns a DataFrame indexed by the column names of df, with the columns manquants (count), pct_manquants (percentage rounded to 1 decimal place, from 0 to 100) and distinctes (number of distinct non-missing values).

import pandas as pd

def rapport_qualite(df):
    ...

Multiple choiceDifficulty 21 hint

Disguised missing values

A ville column contains “inconnu” values. What does df["ville"].isna().sum() return for these rows?

They are counted as missing
They are not counted: for pandas, "inconnu" is an ordinary string
An error

End-of-lesson quiz

Which expression gives the proportion of missing values per column?

df.isna().sum()
df.isna().mean()
df.count()

What is keep=False for in duplicated?

Keeping no duplicates
Marking all copies of a duplicate, including the first
Ignoring missing values

Why run a diagnosis before cleaning?

It is mandatory for pandas
To know what to fix, measure the scale and justify the choices
To speed up the code