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
strinstead ofobject.
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=strstops pandas from converting columns on its own. A SKU like00123would otherwise become the number123.keep_default_na=Falsekeeps 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:
Find the evidence for the order.
25/10/2026in the data can't be month-first, so this site writes day first.List explicit formats and try each in turn, with
errors="coerce"so a miss becomesNaTinstead of an exception.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 withtoday(), 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.
Published via ZyVOP — Write once in Markdown, auto-backup to GitHub, and syndicate to Dev.to, Medium & Hashnode in 1 click.
Top comments (0)