Get Started
Data hustle

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.

60 minIntermediateFree with a local modelStep 1 free

What you'll do

Clean the data with pandas

Fix everything a rule can fix, then remove exact and near duplicates before any model is involved.

Label every row with an LLM

Write a schema, run a cached labelling script, and work out what a full run costs first.

Check a sample by hand

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
Before you start
  • The JSONL or markdown from mission 2
  • Python 3.10+. pip install pandas rapidfuzz pydantic ollama
  • A local model via Ollama (ollama pull qwen3:8b), or an API key for a hosted model
  • A spreadsheet app for the validation step
Your progress0/5
01/05Deterministic cleaning with pandas0 done
STEP 01 of 05~15 min

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

  1. Install the two libraries the script uses.

    Terminal
    pip install pandas rapidfuzz
  2. Save 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)
  3. Run it, giving it your scrape first and the name of the clean file second.

    Terminal
    python clean.py books.jsonl clean.csv

    It 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, PLC and friends, collapse whitespace. 'Acme Ltd.', 'ACME LIMITED' and 'acme, ltd' become ACME. 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.

Tip
For files over a few hundred MB, pandas gets slow and memory-hungry. Same logic in DuckDB runs in seconds; analyse huge spreadsheets shows how. Below that size, pandas is fine and easier to debug.
Checkpoint
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
The file isn't one object per line, probably a single JSON array. Use 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
Raise the threshold to 95, or include a second field in the key (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.
My data is markdown from Firecrawl, not JSONL
Then there's no table yet. Skip to step 3 and use the LLM to extract fields from each markdown file into rows first (structured data from any document is the long version), then come back and clean the result.
Still stuck? Ask the community
Next mission · Data hustleBuild a breakout GitHub radar that posts a daily digest
→