
Mission 3 of 6
Turn a messy scrape into a clean, labelled table you can trust.
The scrape is the easy part. Clean it with pandas, let a local or hosted LLM add the columns that answer a buyer's question, then check the labels by hand.
What you'll do
Fix everything a rule can fix, then remove exact and near duplicates before any model is involved.
Write a schema, run a cached labelling script, and work out what a full run costs first.
Label 50 random rows yourself and get an accuracy number you can quote.
- Reuse the same cleaning script on every dataset
- Never pay twice for the same answer
- End with
labelled.csv, your dataset plus the enrichment columns
Your progress0/5
Deterministic cleaning with pandas
Your scrape is a pile of JSON with trailing spaces and duplicates in disguise. Everything that can be fixed with a rule gets fixed with a rule. The LLM is for what's left.
This is the first pass over your data, and it happens before any model is involved. The order matters: cheap deterministic fixes first, then dedupe, then the expensive LLM pass on a smaller, cleaner table.
Running a model over duplicates is paying twice to get two different answers for the same record.
Run the cleaning script
Install the two libraries the script uses.
Terminalpip install pandas rapidfuzzSave the script below as
clean.py. It turns your raw JSONL into one clean, deduplicated CSV.clean.py# clean.py : raw JSONL -> one clean, deduplicated CSV # usage: python clean.py books.jsonl clean.csv import sys, re, pandas as pd from rapidfuzz import fuzz SUFFIXES = r"\b(LTD|LIMITED|PLC|LLP|CIC|INC|CO)\b\.?" def norm_name(s): s = re.sub(r"[^A-Z0-9 ]+", " ", str(s).upper()) s = re.sub(SUFFIXES, " ", s) return re.sub(r"\s+", " ", s).strip() def norm_postcode(s): s = re.sub(r"\s+", "", str(s).upper()) return f"{s[:-3]} {s[-3:]}" if re.fullmatch(r"[A-Z]{1,2}\d[A-Z\d]?\d[A-Z]{2}", s) else "" df = pd.read_json(sys.argv[1], lines=True) print("rows in:", len(df)) for col in df.select_dtypes("object"): # strip every text column df[col] = df[col].astype(str).str.strip().replace({"None": "", "nan": ""}) if "price_gbp" in df: # numbers become numbers df["price_gbp"] = pd.to_numeric(df["price_gbp"], errors="coerce") if "postcode" in df: df["postcode"] = df["postcode"].map(norm_postcode) key_col = "title" if "title" in df else df.columns[0] df["key"] = df[key_col].map(norm_name) df = df.drop_duplicates(subset=["key"]) # exact dupes after normalising print("after exact dedupe:", len(df)) keep, seen = [], [] # near dupes: 'Acme Ltd' vs 'ACME Limited.' for i, k in zip(df.index, df["key"]): if any(fuzz.ratio(k, s) >= 92 for s in seen[-500:]): continue seen.append(k); keep.append(i) df = df.loc[keep] print("after fuzzy dedupe:", len(df)) print(df.isna().sum().sort_values(ascending=False).head(8)) # blanks per column df.to_csv(sys.argv[2], index=False)Run it, giving it your scrape first and the name of the clean file second.
Terminalpython clean.py books.jsonl clean.csvIt prints the row count at each stage, then a table of blanks per column. Those printouts are how you'll judge whether the cleaning worked.
What each part does
You'll adapt every line to your own dataset, so it's worth knowing what each part is for:
- Strip every text column and turn the strings
'None'and'nan'back into real blanks. Scrapers produce both constantly. - Make numbers numbers.
pd.to_numeric(errors='coerce')turns'£51.77'leftovers into NaN rather than crashing, which is what you want: a blank you can count, not a string that breaks sorting. - Normalise names. Upper-case, strip punctuation, remove
LTD,LIMITED,PLCand friends, collapse whitespace.'Acme Ltd.','ACME LIMITED'and'acme, ltd'becomeACME. This single function removes more duplicates than anything else you'll write. - Normalise postcodes. UK postcodes are the join key to everything (Land Registry, ONS, council data). The rule: remove all spaces, upper-case, put one space before the last three characters. Anything that doesn't match the pattern becomes blank rather than a wrong postcode.
- Exact dedupe on the normalised key, then fuzzy dedupe with rapidfuzz: two keys with a similarity of 92 or more are treated as the same record. It compares only against the last 500 seen, which keeps it fast on big files; sort by key first if you want a stricter pass.
- Count the blanks per column at the end. That table is your honesty check: a column that's 60 percent blank doesn't go in the product, or goes in with a warning.
Make it fit your data
Rename price_gbp, title, category and postcode to whatever your fields are called. If your dataset has a natural unique ID (a company number, a UPC, a URL), dedupe on that first and use the name fuzzing only for records without one.
clean.csv exists, the three row counts printed go down (or stay equal) at each stage, and the blanks table shows you which columns are trustworthy.If it breaksValueError: Trailing data when reading the JSONL + 2 more
ValueError: Trailing data when reading the JSONL
pd.read_json(path) without lines=True, or re-export from Scrapy with -O file.jsonl (the .jsonl extension selects JSON lines).The fuzzy pass removes records that are genuinely different
name + postcode). Fuzzy matching on names alone is too aggressive for things like 'Smith & Co' vs 'Smyth & Co'. Print the pairs it merges while tuning: add print(k, '~', s) inside the any(...) check.