How to Clean and Transform Scraped Data

How do I clean and transform scraped data? Treat cleanup as a staged, reversible workflow. Keep the raw scrape and its provenance, verify that parsing is correct, profile quality before editing, apply explicit transformations, review duplicate candidates, validate against the intended schema, and export only after checks pass.

This process works whether you use OpenRefine interactively or Python, SQL, or another data pipeline. The examples below use OpenRefine concepts and Python code, but the decisions apply to any stack.

1. Preserve the raw scrape and define the target

Make an untouched copy of every downloaded file before cleaning. OpenRefine imports data into a project and does not modify the original input source, so keep the original files outside the project as an immutable reference. Record the source URL, file name, collection date, scraper or run identifier, and any pagination or query parameters.

Write the output contract before changing values:

Required columns and their names.
Data types, such as ISO dates, decimal prices, and booleans.
Which fields may be null.
The stable record key used to identify a row.
Rules for duplicates, missing values, and rejected records.

If the source has no stable identifier, define a documented key from available fields. Do not overwrite the raw value when you can retain both, for example price_raw and parsed price. Keeping both makes review and reprocessing possible.

2. Import and inspect parsing before editing

OpenRefine accepts CSV and TSV, JSON, XML, spreadsheets, and other formats. During import, inspect the preview carefully: header-row selection, delimiter, quote handling, row and column limits, and character encoding. An incorrect delimiter or encoding can create errors that later transformations cannot repair. The official Starting a project documentation recommends checking the preview and selecting an appropriate encoding when needed.

Open the source in OpenRefine and confirm that columns line up with the file.
Inspect rows near the beginning, middle, and end of the input.
Check whether nested JSON or repeated HTML fields were flattened as expected.
Save the project with a name containing the source and collection date.

For code-based ingestion, make parsing explicit and fail loudly:

import csv
from pathlib import Path

source = Path("scrape.csv")
with source.open("r", encoding="utf-8-sig", newline="") as f:
    rows = list(csv.DictReader(f))

print("rows:", len(rows))
print("columns:", list(rows[0]) if rows else [])
print("sample:", rows[:2])

3. Profile quality before normalizing

Use sorting, facets, and filters to understand the data before applying bulk changes. Look for nulls, empty strings, whitespace-only values, malformed dates and numbers, inconsistent labels, HTML remnants, repeated records, and scraper artifacts such as “Load more” text.

Profile the raw scrape before applying transformations.

A null is not the same as 0, false, whitespace, or an empty string. Imported values may initially be strings, so a value that looks numeric can still sort lexicographically. Create a small profile report in code:

from collections import Counter

fields = ["title", "price", "published_at"]
for field in fields:
    values = [row.get(field) for row in rows]
    print(field)
    print("  missing:", sum(v is None for v in values))
    print("  empty:", sum(isinstance(v, str) and not v.strip() for v in values))
    print("  examples:", Counter(values).most_common(5))

In OpenRefine, use a text facet to see value frequencies, numeric facets for ranges, and filters to isolate suspicious records. Save a sample of problematic rows before changing them.

4. Normalize values with explicit, repeatable rules

Transform one issue at a time and record the rule. OpenRefine supports editing cells, splitting and joining columns, adding derived columns, reshaping rows and columns, converting types, and clustering similar text. Its operation history lets you review and undo changes. Transformations apply to current values; expressions are not dynamic spreadsheet formulas.

Trim and standardize text

def clean_text(value):
    if value is None:
        return None
    value = " ".join(value.split())
    return value or None

for row in rows:
    row["title"] = clean_text(row.get("title"))

In OpenRefine, a GREL transformation such as value.trim().replace(/\\s+/, " ") can collapse surrounding and repeated whitespace. Preserve the original column when the distinction matters.

Convert numbers safely

from decimal import Decimal, InvalidOperation

def parse_price(value):
    if value is None:
        return None
    text = value.replace("$", "").replace(",", "").strip()
    if not text:
        return None
    try:
        return Decimal(text)
    except InvalidOperation:
        return None

for row in rows:
    row["price"] = parse_price(row.get("price_raw"))

Keep a conversion-error flag so failed values do not silently become null:

for row in rows:
    raw = row.get("price_raw")
    parsed = parse_price(raw)
    row["price"] = parsed
    row["price_parse_error"] = bool(raw and parsed is None)

Parse dates with a stated timezone policy

from datetime import datetime, timezone

formats = ("%Y-%m-%d", "%Y-%m-%dT%H:%M:%S%z")
def parse_date(value):
    if not value:
        return None
    for fmt in formats:
        try:
            dt = datetime.strptime(value.strip(), fmt)
            return dt if dt.tzinfo else dt.replace(tzinfo=timezone.utc)
        except ValueError:
            pass
    return None

Do not guess when day and month ordering is ambiguous. Put unparseable values into an exception report for review.

Split, join, and reshape fields

Split a combined field only when the delimiter is reliable. A comma in a company name is not necessarily a separator. For multi-valued cells, decide whether the target needs one row per value or an array column. OpenRefine can reshape rows and columns; verify row counts after reshaping.

5. Remove HTML and scraper artifacts carefully

When a field contains markup, parse it as HTML rather than deleting every character between angle brackets. Blind regular expressions can remove legitimate text. Keep the original HTML when it may be needed for audit or later extraction.

from bs4 import BeautifulSoup

def html_to_text(value):
    if not value:
        return None
    text = BeautifulSoup(value, "html.parser").get_text(" ")
    return " ".join(text.split()) or None

Inspect for repeated navigation labels, cookie notices, newsletter prompts, and pagination controls. These are source-specific artifacts; maintain a rule list and sample-check each rule after applying it.

6. Find duplicate candidates, then review them

Use exact keys first: canonical URL, source ID, or a composite key such as normalized domain plus product code. Then use OpenRefine clustering to expose spelling and formatting variants. Fingerprint normalization trims whitespace, lowercases, removes punctuation and control characters, normalizes some extended Latin characters, sorts tokens, and removes duplicates. Because token order and accents can carry meaning, a cluster is a review queue, not proof that records are identical.

Treat fuzzy matches as review candidates, not automatic merges.

For a simple candidate report:

import re

def fingerprint(value):
    if not value:
        return ""
    tokens = re.findall(r"\\w+", value.casefold())
    return " ".join(sorted(set(tokens)))

candidates = {}
for row in rows:
    key = fingerprint(row.get("title"))
    candidates.setdefault(key, []).append(row)

for key, group in candidates.items():
    if key and len(group) > 1:
        print("REVIEW", key, len(group))

Choose a survivor using a documented rule, such as the newest capture or the record with the most complete fields. Keep a merge log containing source IDs and the reason for the decision.

7. Reconcile against an external authority cautiously

OpenRefine reconciliation connects records to a compatible external service. Matching is semi-automated and requires human review and approval. Clean and cluster values before reconciliation, work in useful subsets, and do not accept every candidate automatically. Store the authority identifier and match confidence or review status alongside the original value.

8. Validate the cleaned dataset

Validation should answer whether the output is fit for its intended use, not whether it merely looks tidy.

Check required-field completeness.
Count failed number and date conversions.
Verify ranges and allowed categories.
Review every proposed duplicate merge.
Confirm row counts before and after reshaping.
Compare random output records with their raw source rows.
Check the exact output column names and types.

required = ["source_id", "title", "published_at"]
errors = []
for index, row in enumerate(rows, start=1):
    missing = [f for f in required if row.get(f) in (None, "")]
    if missing:
        errors.append({"row": index, "missing": missing})

print("validation errors:", len(errors))

Export only after the validation report is reviewed. OpenRefine can export the improved dataset in formats suited to the next system; keep the project history and transformation rules with the exported file.

9. A repeatable Python export pipeline

import csv
from pathlib import Path

input_path = Path("scrape.csv")
output_path = Path("clean.csv")

with input_path.open(encoding="utf-8-sig", newline="") as f:
    rows = list(csv.DictReader(f))

for row in rows:
    row["title"] = clean_text(row.get("title"))
    row["published_at"] = parse_date(row.get("published_at"))
    row["price"] = parse_price(row.get("price_raw"))

fields = ["source_id", "title", "published_at", "price"]
with output_path.open("w", encoding="utf-8", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=fields)
    writer.writeheader()
    for row in rows:
        writer.writerow({k: row.get(k) for k in fields})

10. Performance, reliability, and cost choices

For large datasets, profile a representative sample first, then process in chunks. Avoid repeatedly scanning a full table for each rule. Use stable keys and deterministic transformations so a rerun produces the same result. Keep raw files, operation history, exception reports, and exports separately.

Interactive tools are useful when a person must inspect facets, clusters, and exceptions. Scripts are better when the same cleanup runs on every scrape. A hybrid workflow is often practical: profile and discover rules interactively, encode approved rules in code, and retain a small human review queue for ambiguous matches.

11. Troubleshooting

Symptom	Likely cause	Fix
Columns are shifted	Wrong delimiter, quoting, or header setting	Reimport and verify the preview; inspect embedded delimiters.
Accents are corrupted	Encoding mismatch	Choose the source encoding during import and confirm representative rows.
Numbers sort incorrectly	Values remain strings or contain currency symbols	Strip formatting, convert explicitly, and flag failures.
Blank values behave inconsistently	Null, empty, and whitespace values were conflated	Profile each state separately and define a missing-value policy.
Too many duplicate candidates	Fingerprint normalization is aggressive	Use clusters only for review; compare IDs, URLs, and source context.
Rows disappeared after reshaping	Explode or filter logic removed records	Compare row counts and retain a source key through every step.
Dates are off by one day	Timezone was guessed or dropped	Define a timezone policy and preserve offsets where available.
Clean output cannot be audited	Raw values and transformation history were overwritten	Restore from the raw copy and store derived columns plus operation logs.

12. Or skip the browser setup

If your scraped-data workflow starts with collecting pages, ScreenshotNeo can return a clean screenshot or PDF from one GET request. Its capture accepts consent banners like a visitor and removes more than 60 known consent platforms, newsletter popups, and chat widgets; each step can be disabled. Only clean shots are billed: bot checks or CAPTCHAs, blank pages, timeouts, failed loads, and cache hits cost nothing, and the response reports the verdict in X-Page-Verdict and X-Billed headers.

See the ScreenshotNeo API documentation for all options.

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp

import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

Use full-page capture, CSS element selection, custom CSS and JavaScript, waits, blocked resource types, cookies, headers, user agents, timezone, geolocation, caching, signed links, asynchronous webhooks, bulk capture, and PDF options when your collection needs them. An MCP server provides take_screenshot, get_page_info, and capture_pdf tools for Claude, Cursor, and other MCP clients. Free accounts include 1,000 screenshots each month with no card; paid plans start at $5 for 3,000. Create a free ScreenshotNeo account.

FAQ

Should I clean data in place?

No. Preserve the raw scrape and create derived fields or a separate cleaned export so every change can be traced.

When should I use OpenRefine?

Use it when interactive profiling, facets, clustering, reconciliation, and export are central to the task. Encode stable rules in a script when the workflow must run repeatedly.

Are clustered records automatically duplicates?

No. Clustering creates candidates. Review identifiers, context, and source records before merging.

What validation threshold should I use?

The sources do not establish a universal threshold. Define required-field, type, range, uniqueness, and reconciliation checks for the dataset’s intended use.

Can screenshots replace structured scraping?

No. Screenshots and PDFs preserve visual state; structured extraction still requires parsing and validation. They are useful as a visual record of the page captured for a scrape run.