Cleaning messy data

Text to numbers

The price column mixes "$89.00", "25", "USD 35", "$1,194.00" and "Free". The string methods under .str work on every value at once, and a regular expression (a pattern for text) removes all the junk in one call:

import pandas as pd

price = pd.Series(["$89.00", "25", "USD 35", "$1,194.00", "Free", "12 dollars"])
digits = price.str.replace(r"[$,]|USD", "", regex=True).str.strip()
print(digits.tolist())

print(pd.to_numeric(digits, errors="coerce").tolist())
print(pd.to_numeric(digits.replace("Free", "0"), errors="coerce").tolist())

The pattern [$,]|USD means "a dollar sign or a comma, or the letters USD". Replacing it with nothing leaves just the digits. errors="coerce" turns anything that still isn't a number into NaN instead of crashing. That makes it a good detector: convert, then look at the rows that came out NaN to find the cases your pattern missed. Here it caught "Free" and the made-up "12 dollars".

The units column has its own twist. "1,000+" means at least a thousand. Converting it to 1000 is fair, but it's a floor, not a count. Keep a flag column, units_capped, so anyone ranking sellers later knows those numbers are cut off.

import pandas as pd
s = pd.Series(["$5", "7", "n/a"])
print(pd.to_numeric(s.str.replace("$", ""), errors="coerce").isna().sum())

Categories: one spelling each

Count the raw categories and you'll find 21 spellings for five categories: "TENTS", " tents", "tent", "Backpacks", "shoes", "camp-kitchen" and so on. Fix them in two moves. First, .str.strip().str.lower() removes spaces at the ends and makes everything lowercase. That alone merges most variants. Then a dictionary maps the remaining synonyms to one name:

CATEGORY = {
    "tents": "tents", "tent": "tents",
    "packs": "packs", "backpacks": "packs",
    "footwear": "footwear", "shoes": "footwear",
    "apparel": "apparel", "clothing": "apparel",
    "camp kitchen": "camp kitchen", "kitchen": "camp kitchen", "camp-kitchen": "camp kitchen",
}
df["category"] = df["category"].str.strip().str.lower().map(CATEGORY)
print(df["category"].isna().sum())     # 0, or a spelling the dictionary missed

map gives NaN for any value that isn't a key in the dictionary. That's a feature: the isna().sum() check right after tells you if next month's export brings a spelling you haven't seen, instead of letting it slip through.

Dates in two formats

Most dates look like "2024-01-12", but about a third look like "06/09/2024". Is that June 9 or September 6? In the United States it's June 9; in most other countries it would be September 6. If you let pandas guess, it may guess differently on different rows. Parse each format explicitly instead:

import pandas as pd

listed = pd.Series(["2024-01-12", "06/09/2024", "2025-04-27", "08/26/2024"])
iso = pd.to_datetime(listed, format="%Y-%m-%d", errors="coerce")
us = pd.to_datetime(listed, format="%m/%d/%Y", errors="coerce")
dates = iso.fillna(us)
print(dates.dt.strftime("%B %d, %Y").tolist())
print(dates.isna().sum(), "dates failed both formats")

Each attempt fills in the rows it understands and leaves NaT ("not a time") for the rest. fillna combines them. A date that fails both formats stays NaT, and the count on the last line tells you about it. With real datetime values you can compare dates, take the month with .dt.month, and drop the listings dated after the export.

Missing values: what does empty mean?

There are two kinds of empty cell in this table, and they need opposite treatment:

• Units sold is empty when a listing has never sold anything. Empty means zero here, so filling it with 0 isn't a guess. It's the true value.
• Rating is empty when a listing has no reviews. No rating exists, so filling in 0 would claim every unreviewed listing was rated terrible. Leave it missing; mean() and median() skip it.

Ask "why is this missing?" before you touch a missing value. Missing because nothing happened yet (no sales, no reviews) is different from missing because someone forgot to type it in.

One more messy shape: the tags column holds several values in one cell, like "hiking;budget". .str.split(";") turns each cell into a list, and .explode() gives each tag its own row, so value_counts() can count them:

tag_counts = df["tags"].str.split(";").explode().value_counts()

import pandas as pd

raw = pd.read_csv("https://api.gradient.test/v1/datasets/marketplace.csv")
CATEGORY = {"tents": "tents", "tent": "tents", "packs": "packs", "backpacks": "packs",
            "footwear": "footwear", "shoes": "footwear", "apparel": "apparel", "clothing": "apparel",
            "camp kitchen": "camp kitchen", "kitchen": "camp kitchen", "camp-kitchen": "camp kitchen"}

df = raw.sort_values("reviews").drop_duplicates(subset="listing_id", keep="last")
print(len(raw), "->", len(df), "after removing repeat scrapes")
df = df[~df["tags"].str.contains("test")].copy()
print(len(df), "after removing test listings")

df["price"] = pd.to_numeric(df["price"].str.replace(r"[$,]|USD", "", regex=True).str.strip().replace("Free", "0"))
df["units_capped"] = df["units_sold"] == "1,000+"
df["units_sold"] = pd.to_numeric(df["units_sold"].str.replace(r"[,+]", "", regex=True)).fillna(0).astype(int)
df["category"] = df["category"].str.strip().str.lower().map(CATEGORY)
df["listed"] = pd.to_datetime(df["listed"], format="%Y-%m-%d", errors="coerce").fillna(
    pd.to_datetime(df["listed"], format="%m/%d/%Y", errors="coerce"))
df = df[df["listed"] <= pd.Timestamp("2025-09-01")]
print(len(df), "after removing listings dated after the export")

print(df["category"].isna().sum(), "unmapped categories")
print(df.groupby("category")["price"].median())