Scraping

Cleaning Scraped Data with pandas: A Practical Guide

Turn messy scraped data into a clean pandas table: fix whitespace, duplicates, prices, dates and missing values, then validate the result.

·
11 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

Cleaning Scraped Data with pandas: A Practical Guide

Your scraper finished and you have a CSV. Then you open it. Prices are text like "$1,299.00", the same product appears twice, dates arrive in five formats, and one category is spelled three ways. Scraped data is rarely ready to use, and the cleaning step decides whether your charts and averages can be trusted.

This guide builds a small, rerunnable clean.py that turns a messy scrape into a tidy table. Along the way you'll see the mistakes that fail silently, such as a price that quietly becomes NaN, because those are the ones that hurt.

One rule first: never overwrite your raw file. Read the raw CSV, write a new clean one, and rerun the script whenever you find a bug.

Version note: tested with Python 3.13 and pandas 3.1.1. The same code also ran unchanged on pandas 2.3.3 and produced the same cleaned table. In pandas 3, text columns show as str instead of object.


What Goes Wrong

Problem

Example from a scrape

Fix

Hidden characters and stray spaces

" Wireless Mouse\xa0M300 "

str.replace, str.strip

Placeholder text

"N/A", "-", "No reviews yet"

Convert to NaN

Duplicates

The same product on two pages, or with ?ref=home

Build a stable key, then drop_duplicates

Numbers stored as text

"$1,299.00", "4.5 out of 5"

Extract, then to_numeric

Mixed date formats

"03/11/2026", "3 days ago"

Explicit formats

Inconsistent labels

"ELECTRONICS", "electronics "

Normalize case

The Messy Data

To follow along, run pip install pandas, then save this as make_raw.py and run it. It writes 12 rows of a made-up shop's scrape, with every problem from the table built in. The invisible characters are written as escapes so you can see them.

# make_raw.py
import pandas as pd

NBSP = "\u00a0"   # non-breaking space
ZWSP = "\u200b"   # zero-width space (invisible)
SHOP = "https://shop.example/p/"

columns = ["name", "price", "rating", "stock", "date_listed", "category", "url", "scraped_at"]
rows = [
    ("  Wireless Mouse" + NBSP + "M300 ", "$24.99", "4.5 out of 5", "In stock (12 available)", "2026-11-03", "Electronics", SHOP + "1001", "2026-11-20T09:00:00"),
    ("Mechanical Keyboard K8\n", "$89.00", "4/5", "Only 3 left", "03/11/2026", "electronics ", SHOP + "1002?ref=home", "2026-11-20T09:00:00"),
    ('4K Monitor 32"', "$1,299.00", "4.8 out of 5", "In stock", "Nov 3, 2026", "ELECTRONICS", SHOP + "1003/", "2026-11-20T09:00:00"),
    ("Cast Iron Skillet", "$ 34.50", "No reviews yet", "Out of stock", "3 days ago", "Home & Kitchen", SHOP + "2001", "2026-11-20T09:00:00"),
    ("Glass Water Bottle" + ZWSP, "Free", "4.1 out of 5", "In stock (240 available)", "yesterday", "home & kitchen", SHOP + "2002", "2026-11-20T09:00:00"),
    ("Notebook Set (3-pack)", "N/A", "", "", "N/A", "-", SHOP + "3001", "2026-11-20T09:00:00"),
    ("Desk Lamp", "$19.99", "3.9 out of 5", "In stock (7 available)", "25/10/2026", "Home & Kitchen", SHOP + "2003", "2026-11-20T09:00:00"),
    ("Standing Desk", "$449.00", "4.7 out of 5", "In stock (5 available)", "2026-10-28", "Furniture", SHOP + "4001", "2026-11-20T09:00:00"),
    ("Ergonomic Chair", "from $199.00", "4.2 out of 5", "In stock (9 available)", "1 week ago", "Furniture", SHOP + "4002", "2026-11-20T09:00:00"),
    ("", "", "", "", "", "", SHOP + "9999", "2026-11-20T09:00:00"),
    # the same product scraped twice because two result pages overlapped
    ("Glass Water Bottle" + ZWSP, "Free", "4.1 out of 5", "In stock (240 available)", "yesterday", "home & kitchen", SHOP + "2002", "2026-11-20T09:00:00"),
    # the mouse again, from a later pass: new tracking parameter, new price
    ("Wireless Mouse M300", "$22.99", "4.5 out of 5", "In stock (11 available)", "2026-11-03", "Electronics", SHOP + "1001?ref=search", "2026-11-20T09:30:00"),
]
pd.DataFrame(rows, columns=columns).to_csv("raw_products.csv", index=False)
print("wrote raw_products.csv with", len(rows), "rows")

Step 1: Load as Text and Look First

Don't let pandas guess types. Read everything as strings and decide yourself what each column means:

# explore.py
import pandas as pd

df = pd.read_csv("raw_products.csv", dtype=str, keep_default_na=False)

print(df["category"].value_counts())
print(df["name"].head(5).map(repr).to_string())
print("identical rows:", df.duplicated().sum())
category
Electronics       2
Home & Kitchen    2
home & kitchen    2
Furniture         2
electronics       1
ELECTRONICS       1
-                 1
                  1
Name: count, dtype: int64
0    '  Wireless Mouse\xa0M300 '
1     'Mechanical Keyboard K8\n'
2               '4K Monitor 32"'
3            'Cast Iron Skillet'
4     'Glass Water Bottle\u200b'
identical rows: 1

Two arguments matter here:

  • dtype=str stops pandas from converting columns on its own. A SKU like 00123 would otherwise become the number 123.

  • keep_default_na=False keeps empty cells and "N/A" as visible text. pandas' built-in list of missing markers covers "N/A" but not "-" or "No reviews yet", and you want to choose what counts as missing.

Notice the habit in the second print: .map(repr) shows hidden characters. \xa0 is a non-breaking space that passes for an ordinary one, and \u200b is a zero-width space that shows as nothing at all. Both break matching.

Step 2: Tidy Text and Placeholders

Start clean.py with a loader and a text cleaner:

# clean.py
import pandas as pd

# Placeholders this (imaginary) site uses for "no value". Adjust per site.
MISSING = ["", "N/A", "n/a", "NA", "-", "--", "null", "None", "No reviews yet"]


def load(path):
    """Read everything as text so pandas can't guess a type for you."""
    return pd.read_csv(path, dtype=str, keep_default_na=False)


def tidy_text(df):
    """Strip invisible junk, collapse whitespace, turn placeholders into NaN."""
    out = df.copy()
    for col in out.columns:
        text = (
            out[col]
            .str.replace(r"[\u200b\ufeff]", "", regex=True)  # zero-width characters
            .str.replace(r"\s+", " ", regex=True)            # newlines, tabs, nbsp, runs of spaces
            .str.strip()
        )
        out[col] = text.mask(text.isin(MISSING))             # placeholders become NaN
    return out

The regex \s+ covers newlines, tabs and non-breaking spaces, but not zero-width spaces or byte-order marks, which is why they get their own line. Whitespace is cleaned before placeholders are checked, so "N/A " with a trailing space still matches "N/A". We use mask instead of df.replace(MISSING, np.nan) because replace raises a downcasting FutureWarning on pandas 2.3.3 when a whole column is placeholders.

Step 3: Remove Duplicates

Scrapes produce two kinds of duplicates: identical rows (overlapping result pages) and the same item with different details (a later pass with a new price or a tracking parameter in the URL). drop_duplicates() alone only catches the first kind.

For the second, build a key from the most stable identifier you have. Product URLs work well, but do not remove every query parameter: some sites identify products using values such as ?id=1001. Drop only parameters you have confirmed are tracking parameters, and preserve path case because some servers distinguish uppercase from lowercase:

# clean.py (continued)
from urllib.parse import parse_qsl, urlencode, urlsplit, urlunsplit

TRACKING_PARAMS = {"ref"}  # confirmed tracking parameter on this example site


def canonical_url(urls):
    """Remove known tracking parameters, fragments and trailing slashes."""
    def clean_one(url):
        if pd.isna(url):
            return pd.NA
        parts = urlsplit(url)
        query = urlencode(sorted(
            (key, value)
            for key, value in parse_qsl(parts.query, keep_blank_values=True)
            if key not in TRACKING_PARAMS
        ))
        return urlunsplit((
            parts.scheme.lower(), parts.netloc.lower(),
            parts.path.rstrip("/") or "/", query, ""
        ))

    return urls.map(clean_one)


def drop_duplicates(df):
    df = df.dropna(subset=["name"])      # empty records from failed parses
    df = df.drop_duplicates()            # identical rows (overlapping pages)
    return (
        df.assign(url_key=canonical_url(df["url"]))
        .sort_values("scraped_at", kind="stable")
        .drop_duplicates("url_key", keep="last")   # newest scrape wins
    )

The order matters. Tidying comes first, so two rows that differed only by a stray space now count as identical. This site's ref parameter is tracking-only; adjust TRACKING_PARAMS for your source rather than applying the same rule to every website.

Step 4: Turn Prices into Numbers

The obvious one-liner looks right and isn't:

# pitfall_price.py
import pandas as pd

df = pd.read_csv("raw_products.csv", dtype=str, keep_default_na=False)
naive = pd.to_numeric(df["price"].str.replace("$", ""), errors="coerce")
print(df.loc[naive.isna(), "price"].tolist())
['$1,299.00', 'Free', 'N/A', 'from $199.00', '', 'Free']

errors="coerce" turns anything it can't read into NaN without a word. Two of those six are genuinely missing. The other four are real information, and the loss that hurts most is the $1,299.00 monitor, your most expensive product, vanishing because of one comma.

A safer version validates that each value contains one nonnegative amount before removing thousands separators. It also treats "Free" as a real zero:

# clean.py (continued)
def parse_amount(s, decimal="."):
    """'$1,299.00' -> 1299.0. Pass decimal=',' for '1.299,00 €'."""
    patterns = {
        ".": r"(?:\d{1,3}(?:,\d{3})+|\d+)(?:\.\d+)?",
        ",": r"(?:\d{1,3}(?:\.\d{3})+|\d+)(?:,\d+)?",
    }
    if decimal not in patterns:
        raise ValueError("decimal must be '.' or ','")
    thousands = "," if decimal == "." else "."
    text = s.str.strip().str.replace(r"(?i)^from\s+", "", regex=True)
    text = text.str.extract(
        rf"^(?:[$€£₹]\s*)?({patterns[decimal]})(?:\s*[$€£₹])?$",
        expand=False,
    )
    text = text.str.replace(thousands, "", regex=False).str.replace(decimal, ".", regex=False)
    return pd.to_numeric(text, errors="coerce")


def clean_price(s):
    price = parse_amount(s)
    return price.mask(s.str.lower().eq("free"), 0.0)      # "Free" is a real zero

The full-match check rejects unsupported values rather than turning -$25.00 into 25.00 or $20-$30 into 2030.00. Step 8 reports those rejected values so you can decide how to handle them.

The decimal argument exists because number formats differ by country, and a string like "1.299" is ambiguous. Choose the format once per source, from knowledge of the site, never per value. A mismatched format can make a valid price unparseable:

# locale_demo.py
import pandas as pd
from clean import parse_amount

print(parse_amount(pd.Series(["1.299,00 €", "€ 24,99"]), decimal=",").tolist())
print(parse_amount(pd.Series(["1,299.00"]), decimal=",").tolist())   # wrong locale
[1299.0, 24.99]
[nan]

"from $199.00" is a starting price, not the price, so Step 9 records it in a price_is_from flag you can filter on.

Step 5: Ratings, Stock and Categories

Text that holds a number needs str.extract. With expand=False and a single capture group it returns a plain Series:

# clean.py (continued)
def clean_rating(s):
    return pd.to_numeric(s.str.extract(r"(\d+(?:\.\d+)?)\s*(?:out of|/)\s*5", expand=False))


def clean_stock(s):
    qty = pd.to_numeric(s.str.extract(r"(\d+)", expand=False))
    qty = qty.mask(s.str.contains("out of stock", case=False, na=False), 0)
    return qty.astype("Int64")                            # whole numbers, with gaps allowed

For stock, "Out of stock" is a real 0, while a bare "In stock" stays missing because you were never told how many. The capital-I Int64 is pandas' nullable integer, which can hold a gap; a plain int64 can't, and a float column would show 3.0 instead of 3.

Categories need no function of their own, because tidy text plus str.title() turns "ELECTRONICS", "electronics " and "Electronics" into one label.

Step 6: Dates in Many Formats

Our dates arrive as 2026-11-03, 03/11/2026, Nov 3, 2026, 3 days ago and yesterday. The first attempt fails loudly:

# pitfall_dates.py
import pandas as pd

df = pd.read_csv("raw_products.csv", dtype=str, keep_default_na=False)
try:
    pd.to_datetime(df["date_listed"])
except ValueError as err:
    print(str(err).splitlines()[0])
time data "03/11/2026" doesn't match format "%Y-%m-%d". You might want to try:

pandas fixes the format from the first value, then rejects the rest. The error message suggests format="mixed", but that guesses, and it guesses month first: 03/11/2026 becomes 11 March. dayfirst=True isn't a safe cure either: in my test on pandas 3.1.1, format="mixed", dayfirst=True read the ISO date 2026-11-03 as 11 March, which pandas 2.3.3 did not do.

The reliable approach has three parts:

  1. Find the evidence for the order. 25/10/2026 in the data can't be month-first, so this site writes day first.

  2. List explicit formats and try each in turn, with errors="coerce" so a miss becomes NaT instead of an exception.

  3. Anchor relative dates to the scrape time, not today. "3 days ago" written on 20 November means 17 November forever. If you compute it a week later with today(), you're a week off.

# clean.py (continued)
DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%b %d, %Y"]
UNIT_DAYS = {"hour": 1 / 24, "day": 1, "week": 7}


def relative_date(s, scraped_at):
    """'3 days ago' -> a date, counted back from when the page was scraped."""
    low = s.str.lower()
    parts = low.str.extract(r"^(\d+) (hour|day|week)s? ago$")
    days = pd.to_numeric(parts[0]) * parts[1].map(UNIT_DAYS)
    days = days.mask(low.eq("yesterday"), 1.0).mask(low.isin(["today", "just now"]), 0.0)
    return (scraped_at - pd.to_timedelta(days, unit="D")).dt.normalize()


def clean_dates(s, scraped_at):
    out = pd.to_datetime(s, format=DATE_FORMATS[0], errors="coerce")
    for fmt in DATE_FORMATS[1:]:
        out = out.fillna(pd.to_datetime(s, format=fmt, errors="coerce"))
    return out.fillna(relative_date(s, scraped_at))

On a default English setup, %b only understands English month abbreviations like Nov, so translate other languages' month names first. Phrases the code doesn't know, such as "an hour ago" or "3. Nov 2026", come out as NaT, and Step 8 reports them.

Step 7: Decide What "Missing" Means

Missing data isn't one thing:

Value

Meaning

What we do

"Free"

A real price of zero

0.0

"N/A" price

Unknown

Keep NaN

Bare "In stock"

Quantity unknown

Keep missing, don't guess 0

"No reviews yet"

No rating exists

Keep NaN, not 0

Blank category

A label is missing

Fill with "Unknown"

Fill labels, not measurements. Replacing a missing price with the average, or a missing rating with 0, invents data. Keep the gap, and remove those rows only at analysis time, for example with df.dropna(subset=["price"]).

One pandas 3 trap while you fill: df["category"].fillna("Unknown", inplace=True) no longer changes your data. In my test pandas 3.1.1 raised a ChainedAssignmentError warning and left the column untouched, where pandas 2.3.3 still applied the change with a FutureWarning. Assign the result back instead:

df["category"] = df["category"].fillna("Unknown")

Step 8: Report What Failed, Then Validate

errors="coerce" is only safe if you check what it swallowed. This helper lists values that had text but ended up empty:

# clean.py (continued)
def report_unparsed(raw, parsed, label):
    """Print values that had text but still ended up as NaN/NaT."""
    bad = raw[raw.notna() & parsed.isna()]
    if len(bad):
        print(f"WARNING {label}: {len(bad)} value(s) not parsed: {bad.unique().tolist()}")

If the site adds a price range one day, you hear about it instead of losing it:

# try_unparsed.py
import pandas as pd
from clean import clean_price, report_unparsed

raw = pd.Series(["$24.99", "$1,299.00 - $1,499.00"])
report_unparsed(raw, clean_price(raw), "price")
WARNING price: 1 value(s) not parsed: ['$1,299.00 - $1,499.00']

Finally, check for values that parsed but are impossible. Treat these four rules as a starting point:

# clean.py (continued)
def validate(df):
    problems = []
    if df["url"].duplicated().any():
        problems.append("duplicate products")
    if (df["price"] < 0).any():
        problems.append("negative price")
    if not df["rating"].dropna().between(0, 5).all():
        problems.append("rating outside 0-5")
    if (df["date_listed"] > df["scraped_at"]).any():
        problems.append("listing date after scrape time")
    if problems:
        raise ValueError("; ".join(problems))

A rating of 7.5 plus a listing date after the scrape time would raise ValueError: rating outside 0-5; listing date after scrape time, with every problem named at once. Comparisons against NaN come out False, so missing values don't trigger these checks.

Step 9: Put It Together and Save

One function builds the clean table:

# clean.py (continued)
def clean(path):
    df = tidy_text(load(path))
    df["scraped_at"] = pd.to_datetime(df["scraped_at"])
    df = drop_duplicates(df)

    out = pd.DataFrame({
        "name": df["name"],
        "price": clean_price(df["price"]),
        "price_is_from": df["price"].str.contains(r"(?i)^from\b", na=False),
        "rating": clean_rating(df["rating"]),
        "stock_qty": clean_stock(df["stock"]),
        "date_listed": clean_dates(df["date_listed"], df["scraped_at"]),
        "category": df["category"].str.title().fillna("Unknown"),
        "url": df["url_key"],
        "scraped_at": df["scraped_at"],
    })
    report_unparsed(df["price"], out["price"], "price")
    report_unparsed(df["rating"], out["rating"], "rating")
    report_unparsed(df["date_listed"], out["date_listed"], "date_listed")
    return out.reset_index(drop=True)


if __name__ == "__main__":
    raw_rows = len(load("raw_products.csv"))
    df = clean("raw_products.csv")
    validate(df)
    print(f"{raw_rows} rows read, {len(df)} kept\n")
    print(df.drop(columns=["url", "scraped_at"]).to_string())
    df.to_csv("clean_products.csv", index=False)

Run python make_raw.py and then python clean.py:

12 rows read, 9 kept

                     name    price  price_is_from  rating  stock_qty date_listed        category
0  Mechanical Keyboard K8    89.00          False     4.0          3  2026-11-03     Electronics
1          4K Monitor 32"  1299.00          False     4.8       <NA>  2026-11-03     Electronics
2       Cast Iron Skillet    34.50          False     NaN          0  2026-11-17  Home & Kitchen
3      Glass Water Bottle     0.00          False     4.1        240  2026-11-19  Home & Kitchen
4   Notebook Set (3-pack)      NaN          False     NaN       <NA>         NaT         Unknown
5               Desk Lamp    19.99          False     3.9          7  2026-10-25  Home & Kitchen
6           Standing Desk   449.00          False     4.7          5  2026-10-28       Furniture
7         Ergonomic Chair   199.00           True     4.2          9  2026-11-13       Furniture
8     Wireless Mouse M300    22.99          False     4.5         11  2026-11-03     Electronics

Twelve rows became nine: the empty record, the identical duplicate and the older mouse scrape are gone, and the mouse kept its newer price of 22.99. Every gap is now an honest NaN, NaT or <NA>.

A CSV forgets types. Read clean_products.csv back with plain read_csv and your dates return as text and stock_qty as floats. Restore them when you load:

# reload.py
import pandas as pd

df = pd.read_csv(
    "clean_products.csv",
    parse_dates=["date_listed", "scraped_at"],
    dtype={"stock_qty": "Int64"},
)

If you install pyarrow, df.to_parquet("clean_products.parquet") and pd.read_parquet(...) keep these types without any extra arguments.

Troubleshooting

Symptom

Likely cause

What to try

ValueError: time data ... doesn't match format

Mixed formats; pandas locked onto the first one

Parse with explicit formats and errors="coerce"

Dates wrong by a month or a day

format="mixed" guesses month first, and dayfirst can flip ISO dates

Use explicit formats

Prices of 1,000 or more become NaN

The thousands separator breaks to_numeric

Remove separators first (parse_amount)

fillna(inplace=True) does nothing

pandas 3 no longer modifies a column that way

Reassign: df[col] = df[col].fillna(...)

Integer column shows 3.0

A gap forced it to float

astype("Int64")

IDs lose their leading zeros

read_csv guessed integers

Read with dtype=str

Same product still appears twice

Names or prices differ between scrapes

Deduplicate on a stable key such as a URL path

A regex can't find text that looks present

Hidden characters

Print with .map(repr)

Where to Go from Here

You now have a script that reads raw scrapes, cleans them predictably and refuses to continue when something looks wrong. To build on it:

  • Load the clean table into SQLite or PostgreSQL instead of a CSV.

  • Run the whole pipeline on a schedule and keep each raw file, so a bug fix can be rerun over old data.

Happy scraping, and happy cleaning!


Found this helpful? Share it with a fellow developer or leave your questions in the comments.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Bhavya Arora

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Bhavya Arora's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.