How do I normalize messy scraped data into one schema?

A good way to normalize messy scraped data into one schema is to separate the process into staging → cleaning → canonical mapping → validation.

1) Define the target schema first

Before cleaning, decide what your final record should look like.

Example target schema:

{
  "source": "string",
  "source_id": "string",
  "name": "string",
  "price": "number",
  "currency": "string",
  "availability": "string",
  "url": "string",
  "scraped_at": "datetime"
}

This gives you a single “truth” to map everything into.

---

2) Keep raw data separate

Don’t overwrite raw scraped data. Store it as-is in a staging layer.

Example:

• raw_html
• raw_json
• raw_text
• source_metadata

Then create a transformed version from that.

Why:

• you can reprocess later
• you preserve evidence
• you can debug bad mappings

---

3) Create canonical field mappings

Different sources often use different names and formats for the same thing.

Examples:

• product_name, title, item, headline → name
• cost, amount, price_text → price
• in stock, available, stock_status → availability

Make a mapping table per source if needed.

Example:

FIELD_MAP = {
    "title": "name",
    "product_name": "name",
    "cost": "price",
    "price_text": "price",
}

---

4) Normalize values, not just field names

Messy scraped data usually has inconsistent values.

Common normalization rules:

Strings

• trim whitespace
• collapse repeated spaces
• lowercase if appropriate
• remove stray unicode punctuation

Example:

• " Nike Air Max " → "Nike Air Max"

Numbers

• strip currency symbols
• remove commas
• parse decimals safely

Example:

• "$1,299.00" → 1299.00

Dates

• parse all date formats into ISO 8601

Example:

• "Jan 5, 2024" → "2024-01-05T00:00:00Z"

Booleans / status fields

Map many variants to one standard set.

Example:

• "Yes", "In stock", "Available" → "available"
• "No", "Out of stock" → "unavailable"

---

5) Handle missing and ambiguous data explicitly

Scraped data is often incomplete.

Use:

• null for missing values
• a standard placeholder only if your downstream systems require it
• confidence or warning flags if a value was inferred

Example:

{
  "price": null,
  "price_parse_status": "missing"
}

If a field is ambiguous, keep both raw and normalized values:

{
  "raw_price": "From $19.99",
  "price": 19.99,
  "price_type": "starting_price"
}

---

6) Standardize units and currencies

This is a common source of inconsistency.

Examples:

• inches vs cm
• pounds vs kg
• USD vs CAD

Convert to one unit in the canonical schema.

Example:

• store all weights in grams
• store all prices in a base currency if your use case supports conversion, or keep original currency with the amount

---

7) Deduplicate and resolve conflicts

If the same entity appears multiple times:

• dedupe by stable keys like URL, SKU, or source ID
• if multiple records conflict, define a rule:
  • latest timestamp wins
  • most complete record wins
  • trusted source wins

You can also keep a merge history.

---

8) Validate against the schema

Use schema validation to catch bad transformations.

Examples:

• required fields are present
• price is numeric
• URL is valid
• timestamps parse correctly

Tools:

• Pydantic in Python
• JSON Schema
• Great Expectations for data quality checks

---

9) Build the pipeline in stages

A practical pipeline:

1. Ingest raw scrape
2. Detect source
3. Map fields to canonical names
4. Normalize values
5. Validate
6. Store clean record
7. Log errors / anomalies

This keeps things maintainable as sources change.

---

10) Use source-specific transformers

Usually one universal cleaner is not enough. A better pattern is:

• shared normalization utilities
• per-source adapter functions

Example:

def normalize_record(source_name, record):
    if source_name == "site_a":
        return normalize_site_a(record)
    if source_name == "site_b":
        return normalize_site_b(record)

Each adapter handles weird source-specific cases, then outputs the same canonical schema.

---

Example transformation

Raw input

{
  "product_name": "  ACME Widget ",
  "cost": "$1,299.00",
  "availability": "In Stock",
  "page": "https://example.com/item/123"
}

Normalized output

{
  "source": "example_site",
  "source_id": "123",
  "name": "ACME Widget",
  "price": 1299.0,
  "currency": "USD",
  "availability": "available",
  "url": "https://example.com/item/123",
  "scraped_at": "2026-10-04T00:00:00Z"
}

---

Recommended approach

If your data is very messy, use this order:

1. Preserve raw data
2. Create a canonical schema
3. Write source-specific mapping rules
4. Normalize field values
5. Validate
6. Iterate as new edge cases appear

---

If you want a practical implementation

I can give you one of these:

• a Python normalization pipeline
• a Pydantic schema example
• a SQL/dbt-style transformation design
• a generic mapping strategy for many scrape sources

If you want, I can also show a real code example for a messy product scrape.