How to Clean and Deduplicate Scraped Data with Python

Overview

A web scraper can successfully retrieve pages while still producing unreliable data. The same product may appear under several URLs, a price may include different currencies, a title may contain hidden whitespace, and a missing value may be represented as an empty string, N/A, a dash, or a failed request. If these differences are not handled before analysis, counts, rankings, comparisons, and exports become difficult to trust.

A useful cleaning process separates four jobs:

1. Standardisation: make equivalent values use the same format.
2. Validation: identify values that are missing, malformed, or outside an expected range.
3. Deduplication: remove repeated records without accidentally merging different entities.
4. Auditability: retain enough information to explain what changed.

For SEO and growth work, the record key should reflect the question being answered. A page-level dataset may use a canonical URL. An ecommerce dataset may use a product identifier supplied by the source. A SERP dataset may need a combination of keyword, search location, device, result position, and collection timestamp. There is no universally safe duplicate rule.

Keep the raw scrape separate from the cleaned output. A simple project layout might contain raw/, clean/, logs/, and a script or notebook that records the transformation steps. This makes it possible to correct a cleaning rule without scraping the source again.

How to estimate

Before cleaning, estimate the scale of the work and the quality of the result you need. This is more useful than applying every possible transformation to every dataset.

Start with four measurements:

• Input rows: the number of records collected.
• Unique entities: the number remaining after applying a documented identity key.
• Completeness: the proportion of non-empty values in required columns.
• Validity: the proportion of values that pass type and range checks.

These can be calculated in pandas before and after cleaning. For example:

import pandas as pd

required = ['title', 'url', 'price']

rows_before = len(df)
missing_required = df[required].isna().any(axis=1).sum()
completeness = 1 - (missing_required / rows_before)

rows_after = len(clean)
rows_removed = rows_before - rows_after
removal_rate = rows_removed / rows_before if rows_before else 0

print({
    'rows_before': rows_before,
    'rows_after': rows_after,
    'rows_removed': rows_removed,
    'required_field_completeness': round(completeness, 3),
    'removal_rate': round(removal_rate, 3),
})

The numbers are estimates of dataset condition, not a guarantee of business accuracy. A page can contain a valid-looking title that belongs to the wrong template, and a product can have a correctly parsed price that is not the price for the selected variant. Add source-specific checks where the decision depends on the field.

Set acceptance rules before reviewing the output. For example, you might require every record to have a non-empty URL, allow a missing description, reject prices that cannot be parsed, and preserve records with unusual values in a review file rather than deleting them. This prevents the cleaning process from silently optimising for a convenient row count.

Inputs and assumptions

Normalise text first

HTML extraction often leaves leading spaces, line breaks, non-breaking spaces, and inconsistent casing. A small helper makes the rule reusable:

import re
import unicodedata

MISSING = {'', 'n/a', 'na', 'none', 'null', '-', '—'}

def clean_text(value):
    if pd.isna(value):
        return pd.NA
    value = unicodedata.normalize('NFKC', str(value))
    value = re.sub(r'\s+', ' ', value).strip()
    return pd.NA if value.casefold() in MISSING else value

for column in ['title', 'description', 'brand']:
    if column in df:
        df[column] = df[column].map(clean_text)

Do not lowercase every text field automatically. Lowercasing can help with comparison keys, but it can damage brand names, headings, or content intended for publication. Keep the original display value and create a separate comparison column when necessary.

Parse prices and dates deliberately

Prices should be stored as numbers only after you have considered currency, decimal separators, thousands separators, discounts, and unavailable values. A simple parser may be suitable for one known source, but it should not be treated as a universal currency parser.

def parse_price(value):
    if pd.isna(value):
        return pd.NA
    text = str(value).strip()
    text = re.sub(r'[^0-9.,-]', '', text)
    if not text:
        return pd.NA
    # Assumption: this source uses a dot as the decimal separator.
    text = text.replace(',', '')
    try:
        return float(text)
    except ValueError:
        return pd.NA

df['price_amount'] = df['price_raw'].map(parse_price)
df['collected_at'] = pd.to_datetime(df['collected_at'], errors='coerce', utc=True)

Keep a separate currency column instead of assuming that every amount uses the same currency. For dates, use a timezone-aware representation when records from different locations or collection runs will be compared.

Canonicalise URLs without destroying identity

URL cleaning commonly includes lowercasing the hostname, removing fragments, resolving relative links, and removing known tracking parameters. Query parameters should only be removed when you know they do not identify a different page, product, filter, or variant.

from urllib.parse import urlsplit, urlunsplit, parse_qsl, urlencode

TRACKING = {'utm_source', 'utm_medium', 'utm_campaign', 'gclid', 'fbclid'}

def canonical_url(value):
    if pd.isna(value):
        return pd.NA
    parts = urlsplit(str(value).strip())
    query = [(key, val) for key, val in parse_qsl(parts.query)
             if key not in TRACKING]
    return urlunsplit((parts.scheme.lower(), parts.netloc.lower(),
                       parts.path or '/', urlencode(query), ''))

df['url_clean'] = df['url'].map(canonical_url)

Handle missing and invalid values explicitly

Use pd.NA for unknown values and distinguish it from a genuine zero, such as a free item or a position of zero in a source-specific system. Do not fill missing descriptions with a phrase that could later be mistaken for source content. For required fields, create a rejection or review output:

required = ['title', 'url_clean']
review_mask = df[required].isna().any(axis=1)
review = df.loc[review_mask].copy()
clean = df.loc[~review_mask].copy()

Worked examples

Assume a scraper produced these fields:

After normalisation, deduplicate using the strongest available identity key. If the source provides a stable product ID, prefer it over a title or URL. Otherwise, a canonical URL may be appropriate for page-level records:

# Keep the most recent collection for each canonical page.
clean = (clean
         .sort_values('collected_at')
         .drop_duplicates(subset=['url_clean'], keep='last'))

For records where URLs are not stable, build a composite key carefully:

clean['record_key'] = (
    clean['brand'].fillna('').str.casefold().str.strip() + '|' +
    clean['sku'].fillna('').str.casefold().str.strip()
)

clean = clean.drop_duplicates(subset=['record_key'], keep='last')

Avoid fuzzy matching until exact rules have been tested. Similar titles can represent different products, pages, or keyword results. If you use fuzzy matching later, send uncertain pairs to a review file and retain the original values and match score.

Finally, validate the cleaned dataset:

assert clean['url_clean'].notna().all()
assert clean['title'].notna().all()
assert (clean['price_amount'].dropna() >= 0).all()
assert clean['record_key'].is_unique

clean.to_csv('clean/records.csv', index=False)
review.to_csv('clean/review.csv', index=False)

These assertions turn silent data problems into visible failures. For scheduled scrapers, log the row count, duplicate count, missing-field count, and validation failures for every run. A sudden change is often a reason to inspect the source or extraction selector before publishing a report.

When to recalculate

Revisit the cleaning workflow whenever the input or the intended use changes. Recalculate quality measurements after a scraper changes selectors, begins following pagination, switches from HTML to an API response, or starts using a browser automation workflow. The same applies when a site introduces new URL parameters, price formats, date formats, product variants, or localisation.

For recurring SEO and growth datasets, compare each run with the previous run using a small checklist:

• Did the number of raw rows change unexpectedly?
• Did the duplicate rate increase or fall sharply?
• Are required fields still complete?
• Did the distribution of prices, positions, categories, or page types change?
• Did the number of rejected records increase?
• Are new values appearing in categorical fields?

Update assumptions when the source format changes, not merely when the Python or pandas version changes. It is also sensible to review date parsing, URL rules, and price parsing periodically because edge cases tend to appear in newly collected records.

As a practical next step, save one raw sample, one cleaned sample, and one review sample for each important source. Run the same transformations against them after every scraper change. Keep the raw data immutable, version the cleaning code, and record the acceptance rules beside the output. This turns messy scrape results into a repeatable data extraction process rather than a one-off spreadsheet repair.