SyntaxPilot
Courses/Data Analytics with Python/Lesson 18

Data Analytics with Python · Lesson 18 of 90 · 11 min

Data quality and profiling before cleaning

dirty datadata quality dimensions (accuracy, completeness, consistency, validity, uniqueness, timeliness)data profilinggarbage in garbage outdata dictionarycleaning lograw vs clean layers

These are the notes for Data Analytics with Python, episode 18, the first episode of the cleaning module. Until now our data was neat. Real data is typed by real people in a hurry, exported by apps with bugs, and merged from places that disagree. This episode is about the step before cleaning: finding out exactly what is wrong. We meet dirty data, garbage in, garbage out, data profiling with tools we already know, the six data quality dimensions (accuracy, completeness, consistency, validity, uniqueness, timeliness), a reusable profile() function, a data dictionary, a cleaning log, and raw vs clean layers. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, NumPy 2.5.3, pyarrow 26.0.0); outputs are unedited.

Ramesh's new billing app exported his first October orders. Eight orders, typed by three cashiers. The text is read with read_csv (episode 13); io.StringIO makes a piece of text behave like a file, so the example needs no file on disk:

import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """order_id,date,item,qty,price,pay,city
101,2026-10-01,Masala Chai,2,20,UPI,Pune
102,2026-10-01,masala chai ,1,20,upi,Pune
103,2026-10-02,Samosa,-3,15,Cash,pune
104,2026-10-02,Coffee,1,,UPI,Pune
104,2026-10-02,Coffee,1,,UPI,Pune
105,2026-13-02,Ginger Chai,2,25,Card,Pune
106,2026-10-03,Samosa,40,15,UPI,Mumbai
107,2026-10-03,Masala Chai,3,20,Cash,Pune
"""
orders = pd.read_csv(io.StringIO(raw))

(sys.tracebacklimit = 0 only hides pandas' long internal traceback so errors are short.)

The problem: garbage in, garbage out

Ramesh asks for the revenue. The obvious move, from the last episodes:

revenue = (orders["qty"] * orders["price"]).sum()
print("Revenue:", revenue)
print("Orders:", len(orders))
Revenue: 725.0
Orders: 8

A neat number and no error. But it is wrong: one order has −3 samosas, order 104 is counted twice, two prices are missing (and sum() silently skips NaN), and a single order of 40 samosas contributes ₹600 of the ₹725. Garbage in, garbage out (GIGO): if the input is bad, the output is bad, however correct the code is. Data with errors like these is called dirty data.

Data profiling: the check-up before treatment

A good doctor examines you before prescribing anything. Data profiling is that check-up for data: measuring each column to learn what is in it and what is wrong with it. Data cleaning (the treatment) comes after, and only for what profiling found. Profiling uses tools we already know.

Step 1: size and column summary

print(orders.shape)
orders.info()
(8, 7)
<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 7 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   order_id  8 non-null      int64  
 1   date      8 non-null      str    
 2   item      8 non-null      str    
 3   qty       8 non-null      int64  
 4   price     6 non-null      float64
 5   pay       8 non-null      str    
 6   city      8 non-null      str    
dtypes: float64(1), int64(2), str(4)
memory usage: 790.0 bytes

8 rows × 7 columns. price has only 6 non-null values: two are missing. date is stored as text (str), not as a date.

Step 2: missing values and duplicates

isna().sum() counts missing values per column (True counts as 1). duplicated() marks a row that is an exact copy of an earlier row; on one column it marks a repeated value:

print(orders.isna().sum())
print(orders.duplicated().sum(), "duplicate rows")
print(orders["order_id"].duplicated().sum(), "repeated order ids")
order_id    0
date        0
item        0
qty         0
price       2
pay         0
city        0
dtype: int64
1 duplicate rows
1 repeated order ids

Step 3: every text value

value_counts() and unique() (episode 11) reveal every distinct spelling:

print(orders["pay"].value_counts())
print(orders["city"].value_counts())
print(orders["item"].unique())
pay
UPI     4
Cash    2
upi     1
Card    1
Name: count, dtype: int64
city
Pune      6
pune      1
Mumbai    1
Name: count, dtype: int64
<ArrowStringArray>
['Masala Chai', 'masala chai ', 'Samosa', 'Coffee', 'Ginger Chai']
Length: 5, dtype: str

UPI and upi are counted as two payment types, Pune and pune as two cities, and 'masala chai ' has a trailing space. To a computer these are different values; every group-by and count will split them.

Step 4: numbers

print(orders[["qty", "price"]].describe())
             qty      price
count   8.000000   6.000000
mean    5.875000  19.166667
std    13.902081   3.763863
min    -3.000000  15.000000
25%     1.000000  16.250000
50%     1.500000  20.000000
75%     2.250000  20.000000
max    40.000000  25.000000

Read min and max first: qty has a minimum of −3 (impossible) and a maximum of 40 while the median (50%) is 1.5. Errors hide at the extremes.

Step 5: rules (validity checks)

pd.to_datetime converts text to dates (episode 22 covers it fully). The format string "%Y-%m-%d" says year-month-day; errors="coerce" turns anything that is not a real date into NaT ("not a time", the missing value for dates) instead of crashing:

d = pd.to_datetime(orders["date"], format="%Y-%m-%d", errors="coerce")
print(orders[d.isna()])
print(orders[orders["qty"] <= 0])
print("latest order:", d.max().date())
   order_id        date         item  qty  price   pay  city
5       105  2026-13-02  Ginger Chai    2   25.0  Card  Pune
   order_id        date    item  qty  price   pay  city
2       103  2026-10-02  Samosa   -3   15.0  Cash  pune
latest order: 2026-10-03

Without errors="coerce", one bad date stops the script:

import pandas as pd
pd.to_datetime("2026-13-02")
ValueError: month must be in 1..12

The above exception was the direct cause of the following exception:

pandas._libs.tslibs.parsing.DateParseError: month must be in 1..12: 2026-13-02

The six data quality dimensions

To talk about problems clearly, analysts sort them into data quality dimensions: different ways of measuring how good data is. The core list of six is from DAMA, the Data Management Association (IBM's data quality documentation describes it as DAMA's six core dimensions).

Dimension Question In Ramesh's export How to check in pandas
Accuracy Does the value match the real world? 40 samosas: real, or a typo for 4? Compare with the source (paper bills); flag suspicious values
Completeness Is every required value present? 2 prices missing isna().sum(), isna().mean()
Consistency Is the same thing written the same way everywhere? UPI / upi, Pune / pune value_counts(), unique()
Validity Does the value follow the rules (type, range, format)? month 13, qty −3 to_datetime(errors="coerce"), between, isin
Uniqueness Is each real thing recorded once? order 104 twice duplicated()
Timeliness Is the data fresh enough for the question? last order 3 Oct, report on 30 Oct max() of the date column vs today

Validity vs accuracy: a value can be valid but inaccurate (40 samosas is a legal number, but maybe wrong) and consistency is about agreement between values (also between tables or systems), not about the rules for one value.

A reusable profile() function

Write the check-up once and run it on every new dataset:

def profile(df):
    return pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "missing": df.isna().sum(),
        "missing_%": (df.isna().mean() * 100).round(1),
        "unique": df.nunique(),
        "example": df.iloc[0],
    })
print(profile(orders))
            dtype  missing  missing_%  unique      example
order_id    int64        0        0.0       7          101
date          str        0        0.0       4   2026-10-01
item          str        0        0.0       5  Masala Chai
qty         int64        0        0.0       5            2
price     float64        2       25.0       3         20.0
pay           str        0        0.0       4          UPI
city          str        0        0.0       3         Pune

One row per column. isna().mean() is the share of missing values (the mean of True/False = the fraction of True). order_id has 7 unique values in 8 rows (a repeat), pay has 4 types where 3 are expected. Libraries such as ydata-profiling build a full HTML report like this automatically (episode 37); knowing the parts lets you read it.

Data dictionary: the rules, written down

How do we know qty must be 1 to 20, or that pay can only be UPI, Cash or Card? Those rules live in Ramesh's head. A data dictionary writes them down: one row per column, with its meaning, type and allowed values, like the legend of a map.

data_dict = pd.DataFrame([
    ["order_id", "bill number", "int", "unique, > 100"],
    ["qty", "cups/plates sold", "int", "1 to 20"],
    ["price", "rupees per unit", "float", "10 to 50, required"],
    ["pay", "payment type", "text", "UPI, Cash, Card"],
], columns=["column", "meaning", "type", "allowed"])
print(data_dict.to_string(index=False))
  column          meaning  type            allowed
order_id      bill number   int      unique, > 100
     qty cups/plates sold   int            1 to 20
   price  rupees per unit float 10 to 50, required
     pay     payment type  text    UPI, Cash, Card

to_string(index=False) prints a DataFrame without its index. Every validity check comes straight from the allowed column, and a new teammate reads the dictionary instead of asking. In teams it often lives in a spreadsheet or wiki page; a column for the source (which system fills it) and the owner (who to ask) is common too.

Cleaning log and raw vs clean layers

If the report suddenly shows 5 orders instead of 8, someone will ask where three went. A cleaning log records every step and how many rows it affected. And the original data is never edited: the raw layer keeps the data exactly as it arrived; cleaning works on a copy, the clean layer.

clean = orders.copy()
log = []
before = len(clean)
clean = clean.drop_duplicates()
log.append(("drop exact duplicate rows", before - len(clean)))
bad = ~clean["qty"].between(1, 20)
to_check = clean[bad]
clean = clean[~bad]
log.append(("qty outside 1-20 moved to to_check", len(to_check)))
print(pd.DataFrame(log, columns=["step", "rows"]))
print("raw:", len(orders), "| clean:", len(clean), "| to_check:", len(to_check))
                                 step  rows
0           drop exact duplicate rows     1
1  qty outside 1-20 moved to to_check     2
raw: 8 | clean: 5 | to_check: 2
  • orders.copy() (episode 14) makes an independent copy, so orders (raw) is never changed.
  • drop_duplicates() (episode 20) removes exact repeats.
  • Suspicious rows are moved aside (to_check), not deleted, until someone checks the paper bills. That's the honest way to handle possible accuracy problems.

On a real project the layers are folders:

project/
├── raw/      orders_2026-10.csv   ← as received, never edited by hand
├── clean/    orders_clean.csv     ← written only by your code
└── clean.py                       ← reads raw/, cleans, logs, writes clean/

Found a mistake in a step? Fix the code and re-run from raw/. Nothing is lost, and every number in the report can be explained and repeated. Larger teams use more layers (often called bronze → silver → gold, or raw → staging → marts), but the idea is the same.

Mini project: a quality scorecard

One number per dimension we can check in code. Each one should be zero, or explained, before you report revenue:

d = pd.to_datetime(orders["date"], format="%Y-%m-%d", errors="coerce")
checks = {
    "completeness: price missing": orders["price"].isna().sum(),
    "uniqueness: repeated order_id": orders["order_id"].duplicated().sum(),
    "validity: bad date": d.isna().sum(),
    "validity: qty not 1-20": (~orders["qty"].between(1, 20)).sum(),
    "consistency: pay not in list": (~orders["pay"].isin(["UPI", "Cash", "Card"])).sum(),
    "timeliness: days since last order": (pd.Timestamp("2026-10-30") - d.max()).days,
}
print(pd.Series(checks))
completeness: price missing           2
uniqueness: repeated order_id         1
validity: bad date                    1
validity: qty not 1-20                2
consistency: pay not in list          1
timeliness: days since last order    27
dtype: int64

pd.Timestamp is a single date-time value; subtracting two gives a Timedelta (a length of time), and .days its whole days. Accuracy is the one dimension code can't fully check: compare samples with the source. The next five episodes fix each problem: missing values (19), duplicates and types (20), text (21), dates (22), and outliers and validation (23).

Common mistakes

Code / habit Real error / symptom Why Fix
(qty * price).sum() straight away Revenue: 725.0 (no error, wrong) Garbage in, garbage out Profile first
.sum() on a column with NaN (no error: NaN silently skipped) pandas skips missing values in sums Count isna() first and decide
Skipping value_counts() UPI 4, upi 1 (no error) Different spellings are different values Check every text column
pd.to_datetime("2026-13-02") DateParseError: month must be in 1..12: 2026-13-02 An invalid date errors="coerce", then inspect the NaT rows
Deleting suspicious rows (no error: data lost) They may be real Move them to a to_check table
Editing the raw file by hand (no error: original lost) No way back, no record raw/ is read-only; clean with code; log each step

Practice

All practice questions use this table (a teacher's register with problems):

import pandas as pd, io
raw = """roll,name,class,maths,phone
1,Asha,7A,81,98765 43210
2,Ravi,7a,,9876543210
3,Meena,7A,192,98123 45678
3,Meena,7A,192,98123 45678
4,Kabir,7B,74,
5,Zoya,7B,-5,99887 76655
"""
reg = pd.read_csv(io.StringIO(raw))

1. Profile reg: shape, missing values per column, and the number of fully duplicated rows.

Solution
print(reg.shape)
print(reg.isna().sum())
print(reg.duplicated().sum(), "duplicate rows")
(6, 5)
roll     0
name     0
class    0
maths    1
phone    1
dtype: int64
1 duplicate rows

2. Which consistency problem does class have? Which validity problem does maths have (marks are out of 100)?

Solution
print(reg["class"].value_counts())
print(reg[~reg["maths"].between(0, 100) & reg["maths"].notna()])
class
7A    3
7B    2
7a    1
Name: count, dtype: int64
   roll   name class  maths        phone
2     3  Meena    7A  192.0  98123 45678
3     3  Meena    7A  192.0  98123 45678
5     5   Zoya    7B   -5.0  99887 76655

7A vs 7a (consistency). Marks of 192 and −5 break the 0-100 rule (validity). 192 might be a typo for 92: an accuracy question only the answer sheet can settle.

3. Map each problem to a dimension: (a) Kabir has no phone; (b) roll 3 appears twice; (c) phones are written with and without a space; (d) the register is from last year.

Solution

(a) completeness, (b) uniqueness, (c) consistency, (d) timeliness.

4. Run the profile() function from these notes on reg. Which column has the highest missing percentage?

Solution
def profile(df):
    return pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "missing": df.isna().sum(),
        "missing_%": (df.isna().mean() * 100).round(1),
        "unique": df.nunique(),
        "example": df.iloc[0],
    })
print(profile(reg))
         dtype  missing  missing_%  unique      example
roll     int64        0        0.0       5            1
name       str        0        0.0       5         Asha
class      str        0        0.0       3           7A
maths  float64        1       16.7       4         81.0
phone      str        1       16.7       4  98765 43210

maths and phone are tied at 16.7% (1 of 6). Note that roll has 5 unique values in 6 rows: a uniqueness problem.

5. Write a 3-row data dictionary for roll, class and maths, and a scorecard with one check for each of completeness, uniqueness, consistency and validity.

Solution
dd = pd.DataFrame([["roll", "roll number", "int", "unique"],
                   ["class", "class and section", "text", "7A, 7B"],
                   ["maths", "maths marks", "int", "0 to 100, required"]],
                  columns=["column", "meaning", "type", "allowed"])
print(dd.to_string(index=False))
print(pd.Series({
    "completeness: maths missing": reg["maths"].isna().sum(),
    "uniqueness: repeated roll": reg["roll"].duplicated().sum(),
    "consistency: class not in list": (~reg["class"].isin(["7A", "7B"])).sum(),
    "validity: maths outside 0-100": (~reg["maths"].between(0, 100) & reg["maths"].notna()).sum(),
}))
column           meaning type            allowed
  roll       roll number  int             unique
 class class and section text             7A, 7B
 maths       maths marks  int 0 to 100, required
completeness: maths missing       1
uniqueness: repeated roll         1
consistency: class not in list    1
validity: maths outside 0-100     3
dtype: int64

Quick quiz

# one customer, three phone values:
"98765 43210"
"9876543210"
"+91-98765-43210"
# all the SAME number

Which dimension is broken? A. Consistency B. Completeness C. Uniqueness D. Timeliness

Answer

A. Consistency. Nothing is missing and each value is a valid phone number, but the same thing is written in different ways. Standardising text is episode 21.

Interview questions

  1. What is data profiling and why do it before cleaning? Measuring each column's type, missing values, distinct values, ranges and patterns, so you know what to fix and can explain each fix; cleaning blindly can hide or create errors.
  2. Name the six data quality dimensions. Accuracy, completeness, consistency, validity, uniqueness, timeliness.
  3. Validity vs accuracy? Validity: the value follows the rules (type, range, format). Accuracy: it matches reality. A value can be valid but inaccurate.
  4. How do you quickly profile a DataFrame in pandas? shape, info(), isna().sum() / isna().mean(), duplicated().sum(), nunique(), value_counts() per text column, describe() for numbers (read min/max).
  5. What does "garbage in, garbage out" mean? Output quality can't exceed input quality; correct code on bad data gives confidently wrong results.
  6. What is a data dictionary? A document describing each column: meaning, type, allowed values (often also source, owner, units).
  7. Why keep a cleaning log? So every change is explainable and reviewable (rows removed or changed per step), and the results can be reproduced.
  8. Why keep raw and clean data separate? The raw data is the source of truth; if a cleaning step is wrong you fix the code and re-run from raw.
  9. How do you find invalid dates without crashing? pd.to_datetime(col, format=..., errors="coerce") and look at the NaT rows.
  10. Should you delete suspicious rows? Usually not straight away: flag or move them aside, check with the source, and document the decision.

Glossary

Term Meaning Example
Dirty data Data with errors, gaps, repeats or inconsistent values UPI and upi
Garbage in, garbage out (GIGO) Bad input gives bad output, even with correct code revenue ₹725
Data profiling Measuring each column to learn what's in it and what's wrong profile(orders)
Data cleaning Fixing the problems profiling found dropping a duplicate
io.StringIO Makes a text string behave like a file read_csv(io.StringIO(raw))
Non-null count Number of values that are not missing price 6 non-null
duplicated() True for a row (or value) that repeats an earlier one orders.duplicated()
nunique() Number of distinct values per column order_id 7
Extremes The minimum and maximum: where errors often hide qty min −3
Validity check A test that values follow a rule qty.between(1, 20)
pd.to_datetime Convert text to dates to_datetime(col, format="%Y-%m-%d")
errors="coerce" Turn values that can't be converted into missing values month 13 → NaT
NaT "Not a Time": the missing value for dates invalid date
Data quality dimension One way of measuring how good data is completeness
Accuracy Values match the real world 40 samosas, really?
Completeness Required values are present 2 prices missing
Consistency The same thing is written the same way Pune vs pune
Validity Values follow the rules (type, range, format) month 13
Uniqueness Each real thing is recorded once order 104 twice
Timeliness Data is fresh enough for the question 27 days old
DAMA The Data Management Association, a professional body for data management the six dimensions
profile() Our reusable one-row-per-column quality table missing %, unique
Data dictionary A table describing each column's meaning, type and allowed values qty: 1 to 20
to_string(index=False) Print a DataFrame without its index the dictionary
Cleaning log A record of each cleaning step and how many rows it changed ("drop duplicates", 1)
Raw layer Data exactly as received; never edited raw/ folder
Clean layer Data written by your cleaning code clean/ folder
to_check table Suspicious rows set aside for a human to verify qty −3 and 40
Scorecard One number per quality check the mini project
pd.Timestamp A single date-time value pd.Timestamp("2026-10-30")
Timedelta A length of time; the difference of two timestamps .days → 27

Summary

  • No error does not mean correct: garbage in, garbage out. Profile before you calculate.
  • Profiling = shape, info(), isna().sum(), duplicated(), value_counts() / unique(), describe() (min and max first), plus validity checks (to_datetime(errors="coerce"), between, isin).
  • Name problems with the six dimensions: accuracy, completeness, consistency, validity, uniqueness, timeliness.
  • Keep a reusable profile(), write the rules in a data dictionary, record every change in a cleaning log, and never edit the raw layer: clean a copy, set suspicious rows aside.
  • Next episode: cleaning 1, missing values.