# 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.

```python
# 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:

```python
# 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())
```

```text
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:

```python
# 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:

```python
# 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:

```python
# 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())
```

```text
['$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:

```python
# 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:

```python
# 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
```

```text
[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:

```python
# 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:

```python
# 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])
```

```text
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.
    

```python
# 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:

```python
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:

```python
# 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:

```python
# 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")
```

```text
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:

```python
# 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:

```python
# 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`:

```text
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:

```python
# 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._

---

*Published via [ZyVOP](https://zyvop.com/cleaning-scraped-data-with-pandas-a-practical-guide-h5fpi?utm_source=hashnode&utm_medium=crosspost&utm_campaign=syndication) — Write once in Markdown, auto-backup to GitHub, and syndicate to Dev.to, Medium & Hashnode in 1 click.*
