Chapter 4 · Python and Data Handling
Data Handling Fundamentals: Formats, Quality and Lineage
- Page 12 of 22
- 15 min read
Every model is a summary of its data. A clever architecture trained on duplicated, mislabelled or silently truncated data learns the duplicates, the mislabels and the gaps. That is why data people say "garbage in, garbage out", and why most of the time on a real AI project goes into getting data, understanding it, checking it and shaping it — not into modelling.
Data handling is that whole job: collecting data from its sources, reading its formats, checking its quality, cleaning and transforming it, and recording what you did so the result can be trusted and reproduced. This page gives you the vocabulary and the habits, each shown on a small example. The pages after it go deeper: Python and databases, pandas and SQLAlchemy, a full cleaning project, import and export, and pipelines.
What you will learn
- Structured, semi-structured and unstructured data, and where data comes from
- The common file formats — CSV, JSON, Excel, XML, Parquet — and what each keeps or loses
- Records, fields, types and conversion; missing, invalid, inconsistent and duplicate values
- A cleaning workflow with validation, standardisation, transformation, merging and reshaping (wide vs long)
- The six data quality dimensions, lineage and provenance, and reproducible processing
Kinds of data and where it comes from
| Kind | What it looks like | Examples | In AI work |
|---|---|---|---|
| Structured | Fixed columns, one type per column | The shop's tables, a spreadsheet, a bank statement | Classic ML features, analytics, labels |
| Semi-structured | Self-describing fields, nesting, fields that vary from record to record | JSON from an API, LLM call logs, event streams, XML feeds | Logs and traces of AI apps, metadata for RAG chunks |
| Unstructured | No predefined fields | Product reviews, support emails, PDFs, images, audio | The raw material of LLMs and vision models, usually turned into embeddings |
Data reaches you from a handful of sources, each with its own habits: databases (consistent types, but you need access and must not overload production), APIs (JSON, paginated, rate-limited, change without notice), files (CSV and Excel exports, anything goes), sensors and devices (time series, gaps, clock drift, noise) and logs (huge, semi-structured, often the best record of what users really did). Whatever the source, the data lands as records (rows: one order, one event, one document) made of fields (columns: order_id, status, total).
One dataset, five formats
Take a small extract from the shop — one row per order — and save it in each common format, then read each file back and see what survived:
import pandas as pd
from sqlhelp import con
orders = pd.read_sql("""
SELECT o.order_id, o.order_date, o.status, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_date, o.status
ORDER BY o.order_id""", con, parse_dates=["order_date"])
orders.to_csv("orders.csv", index=False)
orders.to_json("orders.json", orient="records", date_format="iso")
orders.to_excel("orders.xlsx", index=False)
orders.to_xml("orders.xml", index=False, parser="etree")
orders.to_parquet("orders.parquet", index=False)
readers = {
"orders.csv": pd.read_csv,
"orders.json": pd.read_json,
"orders.xlsx": pd.read_excel,
"orders.xml": lambda path: pd.read_xml(path, parser="etree"),
"orders.parquet": pd.read_parquet,
}
for path, read in readers.items():
back = read(path)
print(f"{path:15} order_date: {str(back['order_date'].dtype):15} total: {back['total'].dtype}")orders.csv order_date: str total: int64
orders.json order_date: str total: int64
orders.xlsx order_date: datetime64[us] total: int64
orders.xml order_date: str total: int64
orders.parquet order_date: datetime64[us] total: int64The data is the same; what each file remembers about it is not. CSV, JSON and XML have no date type, so the dates come back as text (str is how pandas 3 shows text columns) until you parse them again. Numbers survived everywhere here, but only because pandas guessed right. Excel has real date cells. Parquet stores the schema with the data, so every type comes back exactly as written. It also stores each column separately and compressed, so files are small and a reader can load only the columns it needs. The trade-offs:
| Format | Structure | Types kept? | Good for | Watch out for |
|---|---|---|---|---|
| CSV | Flat rows of text | No — everything is text | Exchange with anyone, small exports | Delimiters and quotes inside values, encodings, leading zeros, dates |
| JSON | Nested objects and lists | Numbers, strings, booleans, null (no dates) | APIs, configs, logs (JSON Lines: one object per line) | Varying fields, deep nesting, size |
| Excel | Sheets of cells | Partly (numbers, dates) | Business users, hand-made reference data | Merged cells, formulas, auto-converted values (gene names become dates) |
| XML | Nested elements with attributes | No (unless a schema is applied) | Older enterprise systems, government and bank feeds | Verbose, namespaces |
| Parquet | Columnar, compressed, with schema | Yes | Analytics and ML datasets, data lakes | Binary (not human-readable); pandas needs pyarrow (or fastparquet) to read and write it |
For anything that feeds a model, prefer a format that carries its schema (Parquet, or a database table). Importing and Exporting: CSV, JSON, Excel and Parquet goes deeper into each.
Types and conversion
Real columns arrive as text and must be converted on purpose. Prices typed by people in Bangladesh might contain thousands separators, the taka sign, Bangla digits, or words:
raw = pd.Series(["1200", "1,500", "৳ 900", "২৫০০", "free", None])
BANGLA_DIGITS = str.maketrans("০১২৩৪৫৬৭৮৯", "0123456789")
cleaned = (raw.str.translate(BANGLA_DIGITS)
.str.replace(r"[৳,\s]", "", regex=True))
price = pd.to_numeric(cleaned, errors="coerce") # what cannot be parsed becomes NaN
print(pd.DataFrame({"raw": raw, "price": price})) raw price
0 1200 1200.0
1 1,500 1500.0
2 ৳ 900 900.0
3 ২৫০০ 2500.0
4 free NaN
5 NaN NaNerrors="coerce" turns anything unparseable into a missing value instead of crashing — but then you must count those values and decide what they mean. "free" could honestly be 0; a missing price is unknown. That decision is yours, not the library's.
What goes wrong in real data
Here is a small, deliberately messy export of newsletter sign-ups, with the problems you will meet everywhere:
signups = pd.DataFrame({
"email": ["nadia@example.com", "TANVIR@example.com ", "farhana@example", None,
"nadia@example.com", "rafiq@example.com", "mitu@example.com",
"imran@example.com", "sadia@example.com"],
"city": ["Dhaka", "chattogram", "Dhaka ", "Sylhet", "Dhaka", "Dacca", "DHAKA",
"Khulna", "dhaka"],
"age": [29, 34, 27, 41, 29, -3, 25, 38, 22],
"signed_up": ["2026-05-01", "2026-05-03", "2026-05-03", "2026-05-04",
"2026-05-01", "2026-05-06", "2026-13-01", "2026-05-08", "2026-05-09"],
})
print(signups) email city age signed_up
0 nadia@example.com Dhaka 29 2026-05-01
1 TANVIR@example.com chattogram 34 2026-05-03
2 farhana@example Dhaka 27 2026-05-03
3 NaN Sylhet 41 2026-05-04
4 nadia@example.com Dhaka 29 2026-05-01
5 rafiq@example.com Dacca -3 2026-05-06
6 mitu@example.com DHAKA 25 2026-13-01
7 imran@example.com Khulna 38 2026-05-08
8 sadia@example.com dhaka 22 2026-05-09| Problem | In this table |
|---|---|
| Missing values | Row 3 has no email |
| Invalid values (break a rule) | farhana@example has no domain ending; age −3; month 13 |
| Inconsistent values (same thing written differently) | Dhaka, Dhaka , DHAKA, dhaka and the old name Dacca; chattogram in lower case; spaces and capitals in an email |
| Duplicates | Rows 0 and 4 are the same person |
Measuring quality: six dimensions
"Is this data good?" has no answer until you say good in which way. Six standard dimensions turn the question into numbers you can track over time:
EMAIL_RULE = r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$"
KNOWN_CITIES = {"Dhaka", "Chattogram", "Sylhet", "Khulna", "Rajshahi"}
AS_OF = pd.Timestamp("2026-05-10") # the report date, fixed so results repeat
dates = pd.to_datetime(signups["signed_up"], format="%Y-%m-%d", errors="coerce")
email_key = signups["email"].str.strip().str.lower()
report = {
"completeness: email present": signups["email"].notna().mean(),
"validity: email well-formed": email_key.str.match(EMAIL_RULE).fillna(False).astype(bool).mean(),
"validity: age 0-120": signups["age"].between(0, 120).mean(),
"validity: real date": dates.notna().mean(),
"consistency: city in reference list": signups["city"].isin(KNOWN_CITIES).mean(),
"uniqueness: distinct emails": email_key.dropna().nunique() / email_key.notna().sum(),
"timeliness: signed up in last 7 days": (dates >= AS_OF - pd.Timedelta(days=7)).mean(),
}
for check, share in report.items():
print(f"{check:38} {share:6.1%}")completeness: email present 88.9%
validity: email well-formed 77.8%
validity: age 0-120 88.9%
validity: real date 88.9%
consistency: city in reference list 44.4%
uniqueness: distinct emails 87.5%
timeliness: signed up in last 7 days 66.7%| Dimension | Question | Typical check |
|---|---|---|
| Completeness | Is everything that should be there, there? | Share of non-missing values; expected row counts |
| Validity | Does each value follow its rules? | Formats, ranges, allowed values, real dates |
| Consistency | Is the same thing written the same way, and do sources agree? | Values in a reference list; totals that match between systems |
| Uniqueness | Is each real thing recorded once? | Duplicate keys after normalising |
| Timeliness | Is the data fresh enough for its use? | Age of the newest record; delay since the event |
| Accuracy | Does it match reality? | Compare a sample with a trusted source (a verified address, a manual check) |
Accuracy is the one you cannot compute from the table alone: rafiq@example.com is well-formed, but only an email that bounces (or does not) tells you whether it is right.
A cleaning workflow
Cleaning is not a pile of one-off fixes. It is a repeatable sequence, written as code so it runs the same way next month:
- Profile: look at shapes, types, missing counts and value lists (as above).
- Standardise: trim spaces, fix case, map variants to one spelling, convert types.
- Validate: apply the rules; move rows that break them to a quarantine table with the reason, instead of silently dropping them.
- Deduplicate on a normalised key.
- Check again and record the counts: rows in, rows out, rows quarantined.
CITY_MAP = {"dhaka": "Dhaka", "dacca": "Dhaka", "chattogram": "Chattogram",
"chittagong": "Chattogram", "sylhet": "Sylhet", "khulna": "Khulna"}
def clean(df):
out = df.copy()
out["email"] = out["email"].str.strip().str.lower()
out["city"] = out["city"].str.strip().str.lower().map(CITY_MAP)
out["signed_up"] = pd.to_datetime(out["signed_up"], format="%Y-%m-%d", errors="coerce")
problems = pd.Series("", index=out.index)
problems[out["email"].isna()] += "no email; "
problems[out["email"].notna() & ~out["email"].str.match(EMAIL_RULE).fillna(False).astype(bool)] += "bad email; "
problems[~out["age"].between(0, 120)] += "bad age; "
problems[out["signed_up"].isna()] += "bad date; "
quarantine = out[problems != ""].assign(reason=problems[problems != ""].str.rstrip("; "))
good = out[problems == ""].drop_duplicates(subset="email", keep="first")
return good, quarantine
good, quarantine = clean(signups)
print(good)
print(quarantine[["email", "reason"]])
print(f"rows in: {len(signups)}, clean: {len(good)}, quarantined: {len(quarantine)}, "
f"duplicates removed: {len(signups) - len(good) - len(quarantine)}") email city age signed_up
0 nadia@example.com Dhaka 29 2026-05-01
1 tanvir@example.com Chattogram 34 2026-05-03
7 imran@example.com Khulna 38 2026-05-08
8 sadia@example.com Dhaka 22 2026-05-09
email reason
2 farhana@example bad email
3 NaN no email
5 rafiq@example.com bad age
6 mitu@example.com bad date
rows in: 9, clean: 4, quarantined: 4, duplicates removed: 1Notice what standardisation did before any rule was checked: five spellings of Dhaka became one, and the email with capitals and a trailing space became a normal key that duplicates can be matched on. Every row is accounted for: 4 clean + 4 quarantined + 1 duplicate = 9.
"Normalisation" means two different things. In databases it is the design process of splitting tables to remove redundancy (see Database Design: Keys, ER Diagrams and Normalization). In data processing and ML it means rescaling numbers to a common range, such as min-max scaling to 0–1 or z-scores:
(x - x.min()) / (x.max() - x.min()). Say which one you mean.
Transform, filter, aggregate, merge
Once clean, data is transformed into the shape a question needs: new fields derived from old ones, rows filtered, groups aggregated, and other sources merged in. Merging is where silent errors breed, so check the row counts:
divisions = pd.DataFrame({"city": ["Dhaka", "Chattogram", "Sylhet", "Khulna"],
"division": ["Dhaka", "Chattogram", "Sylhet", "Khulna"],
"metro": [True, True, False, False]})
enriched = good.merge(divisions, on="city", how="left", validate="many_to_one")
assert len(enriched) == len(good), "the merge must not add or lose rows"
enriched["age_band"] = pd.cut(enriched["age"], bins=[0, 24, 34, 120], labels=["<25", "25-34", "35+"])
print(enriched[["email", "division", "metro", "age_band"]])
summary = (enriched[enriched["metro"]]
.groupby("city", as_index=False)
.agg(signups=("email", "count"), mean_age=("age", "mean")))
print(summary) email division metro age_band
0 nadia@example.com Dhaka True 25-34
1 tanvir@example.com Chattogram True 25-34
2 imran@example.com Khulna False 35+
3 sadia@example.com Dhaka True <25
city signups mean_age
0 Chattogram 1 34.0
1 Dhaka 2 25.5validate="many_to_one" makes pandas raise an error if the lookup table has a duplicate city — the pandas version of the fan-out trap from Joining Many Tables, and the Duplicate-Row Trap — and the assert catches rows gained or lost.
Wide vs long
The same numbers can be laid out two ways. Wide: one row per thing, one column per measurement (good for reading, and the shape most ML libraries expect for features). Long: one row per thing per measurement (good for storing, grouping and plotting, and the shape SQL produces naturally):
long = pd.read_sql("""
SELECT c.name AS category, substr(o.order_date, 1, 7) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE o.status <> 'cancelled' AND o.order_date < '2026-04-01'
GROUP BY category, month
ORDER BY category, month""", con)
print(long.head(4))
wide = long.pivot(index="category", columns="month", values="revenue").fillna(0).astype(int)
print(wide)
back_to_long = wide.reset_index().melt(id_vars="category", var_name="month", value_name="revenue")
print(len(long), "long rows,", wide.shape, "wide,", len(back_to_long), "rows after melt") category month revenue
0 Accessories 2026-02 1500
1 Accessories 2026-03 2200
2 Books 2026-01 1200
3 Books 2026-02 2500
month 2026-01 2026-02 2026-03
category
Accessories 0 1500 2200
Books 1200 2500 2400
Courses 0 3000 15000
Electronics 5400 0 1800
9 long rows, (4, 3) wide, 12 rows after meltpivot goes long → wide; melt goes wide → long. The melt has more rows than the original because the wide table filled missing combinations with 0 — a reminder that reshaping can quietly invent values.
Lineage, provenance and reproducibility
Six months from now someone will ask: "Which data was this model trained on, and where did it come from?" Provenance is the origin of the data (which system, which export, when, under which licence or consent). Lineage is the path from there to the result (which steps, in which order, with which code). Record both as you go, in a small manifest saved next to the output:
import hashlib
import json
def sha256_of(path):
return hashlib.sha256(open(path, "rb").read()).hexdigest()[:12]
signups.to_csv("signups_raw.csv", index=False)
def run_pipeline():
raw = pd.read_csv("signups_raw.csv")
good, quarantine = clean(raw)
good.sort_values("email").to_csv("signups_clean.csv", index=False)
return {
"source": "newsletter export (signups_raw.csv)",
"input_sha256": sha256_of("signups_raw.csv"),
"steps": ["strip/lower email", "map city", "parse date", "validate", "dedupe on email"],
"rows": {"in": len(raw), "clean": len(good), "quarantined": len(quarantine)},
"output_sha256": sha256_of("signups_clean.csv"),
"as_of": str(AS_OF.date()),
"pandas": pd.__version__.split(".")[0] + ".x",
}
first, second = run_pipeline(), run_pipeline()
print(json.dumps(first, indent=2))
print("same output on a second run:", first["output_sha256"] == second["output_sha256"]){
"source": "newsletter export (signups_raw.csv)",
"input_sha256": "066c33963c6c",
"steps": [
"strip/lower email",
"map city",
"parse date",
"validate",
"dedupe on email"
],
"rows": {
"in": 9,
"clean": 4,
"quarantined": 4
},
"output_sha256": "caa2d8ed5ba9",
"as_of": "2026-05-10",
"pandas": "3.x"
}
same output on a second run: TrueRunning the pipeline twice gave byte-for-byte the same output, and the manifest proves it. That is reproducible processing, and it depends on a few habits:
- Keep the raw input unchanged; every result is a function of raw data plus code.
- Sort before saving (a database returns rows in no fixed order without
ORDER BY, and neither do parallel tools such as Spark), fix random seeds, and pass "today" in as a parameter instead of reading the clock. - Version the code (Git) and pin library versions; record both in the manifest.
- Hash inputs and outputs, so "did the data change?" has a yes/no answer.
Larger teams use tools for this — dbt and data catalogs for lineage, DVC or lakehouse table versions for datasets, MLflow for linking a model to the data it was trained on — but they all record the same facts as this manifest.
Common mistakes
- Dropping bad rows silently.
df.dropna()can remove half your data without a word, and the half it removes is rarely random. Quarantine with a reason and report the counts. - Trusting types guessed by a reader. A CSV column of IDs like
00123becomes the number 123; a mixed column becomes text. Passdtype=and date formats explicitly:pd.read_csv("x.csv", dtype={"phone": "string"}). - Cleaning by hand in a spreadsheet. It cannot be repeated or reviewed. Put every step in code, even small ones.
- Merging without checking counts. A duplicate key in a lookup table multiplies rows. Use
validate=in pandas, and compare row counts before and after every join. - Overwriting the raw data. Once the original is gone, a cleaning bug cannot be undone. Write cleaned data to a new file or table.
Try it yourself
- Easy: Convert this column to numbers and report how many values could not be parsed:
["12", "৪৫", "3.5", "N/A", "1,200"]. - Medium: Add a completeness and validity report for the shop's
customerstable (email present and well-formed, city present), read withpd.read_sql. - Hard: Turn the
widetable into a long one withmelt, drop the zero rows the pivot invented, and show that the result equals the originallongtable after sorting.
Answers
# 1. Easy
values = pd.Series(["12", "৪৫", "3.5", "N/A", "1,200"])
numbers = pd.to_numeric(values.str.translate(BANGLA_DIGITS).str.replace(",", ""), errors="coerce")
print(numbers.tolist(), "unparsed:", numbers.isna().sum())# 2. Medium
customers = pd.read_sql("SELECT customer_id, email, city FROM customers", con)
print(f"email present: {customers['email'].notna().mean():.0%}")
print(f"email well-formed: {customers['email'].str.match(EMAIL_RULE).mean():.0%}")
print(f"city present: {customers['city'].notna().mean():.0%}")# 3. Hard
melted = wide.reset_index().melt(id_vars="category", var_name="month", value_name="revenue")
melted = melted[melted["revenue"] != 0].sort_values(["category", "month"]).reset_index(drop=True)
print(melted.equals(long.astype({"revenue": int})))Summary
- Data is structured, semi-structured or unstructured, and comes from databases, APIs, files, sensors and logs — each with its own typical problems.
- Formats differ in what they keep: CSV and XML lose types, JSON nests, Excel suits people, Parquet keeps the schema and suits ML.
- Expect missing, invalid, inconsistent and duplicate values; convert types deliberately and count what failed.
- Clean with a repeatable workflow: profile, standardise, validate (quarantine, don't drop), deduplicate, check counts. Measure completeness, validity, consistency, uniqueness, timeliness and accuracy.
- Record provenance and lineage in a manifest, keep raw data, and make runs reproducible: sorted output, fixed seeds and dates, hashes.
Next: Python and Databases: Connections, Parameters and Transactions connects Python properly to SQLite, MySQL and PostgreSQL — the plumbing every pipeline on this page needs to read its sources and write its results safely.