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, soorders(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
- 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.
- Name the six data quality dimensions. Accuracy, completeness, consistency, validity, uniqueness, timeliness.
- Validity vs accuracy? Validity: the value follows the rules (type, range, format). Accuracy: it matches reality. A value can be valid but inaccurate.
- 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). - What does "garbage in, garbage out" mean? Output quality can't exceed input quality; correct code on bad data gives confidently wrong results.
- What is a data dictionary? A document describing each column: meaning, type, allowed values (often also source, owner, units).
- Why keep a cleaning log? So every change is explainable and reviewable (rows removed or changed per step), and the results can be reproduced.
- 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.
- How do you find invalid dates without crashing?
pd.to_datetime(col, format=..., errors="coerce")and look at the NaT rows. - 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.