Data Cleaning After Web Scraping: From Raw HTML to Analysis-Ready

Scraped data arrives dirty by nature: duplicate records from overlapping pages, prices as strings ("$1,299.00", "1 299 €"), half-empty rows from layout variants, encoding artifacts, and whitespace everywhere. The cleaning pipeline is what turns a scrape into a dataset someone can trust. Mine has five stages, all pandas.

1. Deduplicate on the real key

Never dedupe on the whole row — timestamps and view counters differ between visits of the same item. Dedupe on the identity: listing ID, product URL, or a normalized composite:

df = df.drop_duplicates(subset=["listing_id"], keep="last")

2. Coerce types aggressively, but log rejects

df["price"] = (df["price"].str.replace(r"[^\d.]", "", regex=True)
                          .pipe(pd.to_numeric, errors="coerce"))
rejects = df[df["price"].isna() & df["price_raw"].notna()]  # inspect, don't discard silently

The habit that matters: keep the raw column beside the parsed one until validation passes. Silent coercion is how "average price" ends up computed over half the rows.

3. Normalize the annoying trio: text, dates, currencies

• Text: strip whitespace, collapse internal spaces, fix mojibake, normalize unicode (NFC)
• Dates: parse every format the site uses into ISO 8601; store timezone if the source has one
• Currencies/units: split value and unit into separate columns — "1,299 USD" is two facts

4. Validate with rules, not vibes

Encode what "sane" means for the dataset and assert it: prices > 0, dates within plausible ranges, required fields present, categorical values from the known set. This mirrors the quality-rule approach from the insurance claim-QA pipeline — impossible records (an invoice dated before the damage) are caught by rules, not luck. Fail the run loudly when validation rates drop; that's usually the site changing under you, not the data changing.

5. Deliver in the consumer's format

Analysis-ready means: consistent snake_case column names, one row per entity, no merged cells or footnotes, UTF-8, and the format the consumer actually loads — CSV for spreadsheets, JSON for APIs, Parquet when it's feeding a warehouse. The last step of cleaning is always a spot-check against the live site: ten random records, compared by eye. Trust is built row by row.

FAQ