How to Clean, Transform, and Enrich Scraped Data

Clean scraped data by preserving the raw extract, checking that it parsed correctly, profiling its fields, applying explicit cleaning and transformation rules, deduplicating against a defined record identity, enriching against appropriate reference sources, and validating the output for its intended use. Keep uncertain enrichment matches reviewable and retain enough provenance to reproduce the result.

This guide covers a visual workflow with OpenRefine and a repeatable Python example, along with practical checks for importing, cleaning, matching, exporting, and troubleshooting. The examples treat the original scraped data as evidence: transformations should not erase the only copy of a source value.

1. Preserve the extract and define the output

Before editing, save the original response or file as a read-only input. Record where and when it was collected, the page or endpoint, relevant query or scrape configuration, and a batch identifier in a manifest or source columns. A filename or URL can help identify an input, but by itself it is not complete provenance.

Write down what one output row represents. For example, one row might represent a product, a listing at a point in time, or a page observation. This decision determines how to identify duplicates and which fields are required.

Keep the original file or response unchanged.
Record retrieval date, source URL or endpoint, and batch ID.
Define required fields, expected types, formats, uniqueness rules, and acceptable blanks.
Choose an output format and schema before transformation.

2. Import and inspect parsing

Choose an importer that matches the actual data format instead of trusting a filename extension. In the import preview, check headers, delimiters, row boundaries, empty columns, malformed records, and character encoding. If text appears corrupted, fix the encoding at import time; mojibake can otherwise be mistaken for the source value and propagated into later steps.

OpenRefine supports formats including CSV/TSV, JSON, XML, spreadsheets, and RDF, with additional formats available through extensions. Its import guidance calls out encoding choices such as UTF-8, UTF-16, and ASCII. Start a project from a copy of the extract and confirm the preview before applying edits. See the [OpenRefine importing data documentation](https://openrefine.org/docs/manual/importing) for current import details.

3. Profile fields before changing them

Use filters, facets, and sorting to see the values that actually occur. Look for missing values, inconsistent case and whitespace, punctuation variants, mixed date formats, units, repeated records, and values that violate the expected type. Check distributions before and after major changes so an accidental broad edit is visible.

Write each normalization rule down. If a transformation loses information or changes meaning, preserve the source column and write the result to a separate normalized column. For example, retain price_source alongside a parsed numeric price, especially if currency symbols or units vary.

4. Clean and transform with explicit rules

Start with low-risk formatting cleanup, then handle semantic changes. Common operations include trimming whitespace, normalizing case where case is irrelevant, correcting known typos, converting dates to one documented format, splitting a combined field, joining fields to match a target schema, and reshaping rows or columns.

OpenRefine provides facets and filters to isolate values, transformations to edit them, and clustering to surface likely spelling variants. Clusters are suggestions, not proof that two values refer to the same thing. Review each proposed merge: “Acme Inc.” and “Acme Industries” may be different organizations even when their names look similar. Consult the [OpenRefine transformation documentation](https://openrefine.org/docs/manual/grel) for its expression language and transformation guidance.

Use a reproducible Python workflow when rules repeat

For recurring jobs, put the rules in code, keep the script with the project, and write output to a new file. The following standard-library example reads a CSV, trims text, normalizes email casing, parses a numeric price, checks required fields, and writes a separate cleaned file while retaining the original columns. It deliberately does not merge near-duplicate entities or discard rows.

import csv
from decimal import Decimal, InvalidOperation
from pathlib import Path

source = Path("scraped_raw.csv")
target = Path("scraped_clean.csv")
required = {"source_id", "name"}

with source.open("r", encoding="utf-8-sig", newline="") as f:
    reader = csv.DictReader(f)
    if not reader.fieldnames:
        raise ValueError("CSV has no header row")
    missing = required - set(reader.fieldnames)
    if missing:
        raise ValueError(f"Missing required columns: {sorted(missing)}")

    rows = []
    for line_number, raw in enumerate(reader, start=2):
        row = {key: (value.strip() if value is not None else "")
               for key, value in raw.items()}
        row["email"] = row.get("email", "").lower()
        price_text = row.get("price", "")
        if price_text:
            try:
                row["price_normalized"] = str(Decimal(price_text.replace(",", "")))
            except InvalidOperation as exc:
                raise ValueError(
                    f"Invalid price at CSV line {line_number}: {price_text!r}"
                ) from exc
        else:
            row["price_normalized"] = ""
        if not row.get("source_id") or not row.get("name"):
            raise ValueError(f"Required value missing at CSV line {line_number}")
        rows.append(row)

fieldnames = list(rows[0].keys()) if rows else []
with target.open("w", encoding="utf-8", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    if fieldnames:
        writer.writeheader()
        writer.writerows(rows)

print(f"Wrote {len(rows)} rows to {target}")

Adapt the field names and rules to the source schema. If the input may contain quoted newlines, commas, or unusual quoting, use a CSV parser rather than splitting lines manually; the standard library parser above handles ordinary CSV quoting. For JSON or other formats, select a parser that matches the input and validate the decoded structure before transforming it. Do not silently replace parse failures with empty values.

5. Deduplicate by record identity

Decide which records are duplicates based on the output’s intended grain. Prefer a stable source identifier when available. If there is no identifier, define a candidate key from fields that should identify one record, then inspect collisions before dropping anything. Similar names alone are not a safe key.

Distinguish exact duplicates from likely duplicates. Exact duplicate rows can often be flagged mechanically; likely duplicates need review or stronger evidence. Record the key and policy used, and report how many rows were retained, flagged, or removed. If repeated observations over time are meaningful, do not collapse them merely because their entity fields match.

6. Enrich with external reference data

Enrichment adds facts from a chosen authority, such as an identifier or a related property. Start with a specific purpose and an authority appropriate to the entity type. Clean and normalize the source values first, because typos, stray characters, and inconsistent spacing can make matching less reliable.

Keep the original value, the authority’s identifier and label, the source name, and retrieval date. Represent unmatched and uncertain candidates explicitly; do not make them look like confirmed matches. Review ambiguous candidates before accepting them, particularly when common names can refer to multiple people, places, or organizations.

OpenRefine supports reconciliation against external services and can extend matched records with additional properties. Its documentation describes reconciliation as semi-automated and requires human judgment to review and approve results. Read the [OpenRefine reconciliation documentation](https://openrefine.org/docs/manual/reconciling) and the selected service’s own documentation, rate limits, and terms before using it at scale. Treat service responses as candidate evidence, not ground truth.

7. Validate and export

Validation is specific to the destination and use case; there is no single universal threshold for a scraped dataset. Check the final dataset against rules you defined up front:

Required columns exist and required values are present.
Values parse as the expected types and use the expected formats and units.
Keys satisfy the intended uniqueness rule.
Row counts and category distributions changed only as expected.
Unmatched enrichment records and unresolved candidates are visible.
The output opens correctly in the next system and conforms to its schema.

Export the cleaned dataset in the format the next system requires. OpenRefine project archives can include edits and history. Share an archive when that history is appropriate to expose; when only the cleaned result should be shared, export the dataset instead. See [OpenRefine exporting data](https://openrefine.org/docs/manual/exporting) and [project files and history](https://openrefine.org/docs/manual/running#saving-and-loading-projects) for current behavior.

8. A practical OpenRefine sequence

Import a copy of the raw extract and verify the preview, headers, and encoding.
Use facets, filters, and sorting to profile missing, malformed, and inconsistent values.
Apply reversible cleanup rules first; preserve source columns when a change is lossy.
Cluster candidate variants and inspect each cluster before merging.
Define record identity, flag duplicates, and review collisions before removal.
Reconcile only fields that need external identifiers or properties; review uncertain matches.
Run output-specific checks, export the cleaned data, and save or omit project history as appropriate.

OpenRefine projects are local projects; the manual says one local project cannot be accessed by multiple people simultaneously. For team handoff, exchange exports and project files with care, or put repeatable transformation logic in version-controlled scripts. Choose a visual workflow for exploratory edits and code when rules need to run consistently in recurring jobs. Dataset size and runtime depend on the data and environment; the source documentation does not provide a general size threshold.

9. Troubleshooting common problems

Symptom	Likely cause	What to do
Accented characters appear as replacement symbols or strange sequences	Wrong character encoding at import	Re-import using the source encoding, commonly UTF-8 or UTF-16, and inspect the preview before editing.
Columns shift or rows split unexpectedly	Wrong delimiter, quoting, or line-break interpretation	Check the parser settings and preview; use a format-aware parser and preserve the raw input.
Dates or numbers fail to parse	Mixed locale formats, units, currency marks, or thousands separators	Identify the source conventions, normalize with an explicit rule, and send exceptions to a review file rather than silently coercing them.
A cluster combines different people or organizations	String similarity was treated as entity identity	Undo the merge, review candidate records with stable identifiers or other evidence, and keep unresolved matches separate.
Enrichment returns several plausible candidates	The source value is ambiguous or under-specified	Retain candidate status, add disambiguating fields, and require manual approval before treating a match as accepted.
Rows disappear after deduplication	The dedupe key is too broad or repeated observations are meaningful	Restore from the raw extract or project history, define the row grain, and rerun using a reviewed key.
Team members cannot edit the same OpenRefine project at once	Local project access is not simultaneous multi-user collaboration	Coordinate ownership and exchange exports or project files, or move repeatable work to a shared code workflow.
Output history exposes intermediate edits	A project archive was shared when only data was intended	Export only the cleaned dataset when edit history should not be included.

10. Performance, reliability, and cost considerations

Keep the raw extract so a failed transformation can be rerun. For recurring scripts, make rules explicit, log row counts and rejected records, and avoid treating parse errors as valid blanks. Split very large jobs only when you can preserve stable identifiers and validate that batching does not change the result. The cited OpenRefine documentation and book preview do not establish universal dataset-size limits or runtime benchmarks.

External reconciliation introduces a dependency on the chosen service: availability, rate limits, response behavior, and terms vary. Check those conditions before batch requests, retain source and retrieval details, and plan how to handle unmatched or unavailable results. OpenRefine is documented as free, open-source software; costs for external enrichment services depend on the selected provider and are not established by the sources here.

Or skip the browser setup

If your scrape workflow starts with web pages and your immediate job is to capture them as image or PDF inputs, ScreenshotNeo is a website screenshot API and MCP server for developers. It does not replace a data-cleaning or reconciliation workflow. Its API can provide a consistent visual capture of a source page for inspection or documentation.

One GET request returns a screenshot or PDF; see the ScreenshotNeo API documentation for parameters and formats.

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}`);

ScreenshotNeo removes cookie and consent banners, newsletter popups, and chat widgets before the shot. Bot checks, blank pages, and failed loads are not billed. Its MCP server gives AI agents tools to take screenshots, get page information, and capture PDFs. The free plan includes 1,000 screenshots a month with no card; paid plans start at $5 for 3,000 shots. Start with 1,000 free screenshots a month, no card required.

FAQ

Should I clean data before or after deduplicating?

Profile first, then normalize fields that define identity before deduplicating. Preserve original values so you can inspect whether normalization caused unrelated records to collide.

Can I enrich every scraped field automatically?

Only enrich fields with a clear purpose and a suitable authority. Automatic candidates should remain distinguishable from reviewed and accepted matches.

Should I keep the OpenRefine project or only export the result?

Keep the project when edit history is useful and safe to share. Export only the cleaned dataset when the project history should not be exposed.

Is a visual tool or a script better?

Use a visual project to explore and review data interactively. Use a script when the same documented rules need to run repeatedly and be maintained with code.