Level 9 · Turn a messy price export into numbers

Blank rows: dropna(how=“all”)

Spreadsheet exports often carry empty lines between blocks. In the plain Python lists that build supplier, those cells are None; once they’re in a DataFrame, pandas treats them as missing, just like NaN. dropna removes rows with missing values, but by default it removes a row if any value is missing:

len(supplier)                   # 11
len(supplier.dropna())          # 8, HP-111 is gone too
len(supplier.dropna(how="all")) # 9, only the fully blank rows go

HP-111 has a sku but no price yet. That’s information Thomas wants to see (“no price yet”), so how="all" is the right choice: drop a row only when every column is empty. subset=["sku"] is the other common option, dropping rows that have no sku whatever else they hold.

From text to numbers

pd.to_numeric(supplier["price"]) fails straight away with ValueError: Unable to parse string "$1,299.00". Clean the text first so only digits and a decimal point remain:

text = (
    clean["price"].str.strip()
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

regex=False says “replace this exact character”. In patterns, $ has a special meaning, so being explicit avoids surprises. After this, “$1,299.00” is “1299.00” and ” 45 ” is “45”, but “n/a” and “TBD” are still words.

errors="coerce" tells to_numeric to turn anything it can’t read into NaN instead of stopping:

clean["price_usd"] = pd.to_numeric(text, errors="coerce")
clean["price_usd"].isna().sum()   # 3: n/a, TBD and HP-111's missing price

Coercing is a decision, not a fix: the bad values become gaps. Always count the NaNs it created and look at what they were, so a typo like “12,99” (a decimal comma) isn’t silently turned into 1299.

The cleaned list

Nine products remain, six with a real price, from $12.99 (HP-108) to $1,299.00 (HP-101). The three gaps tell Thomas exactly which skus to chase the supplier about. Because price_usd is now a real float column, he can sort it, average it or compare it with products["unit_price"], none of which works on the original text. Keep the raw price column next to it while you check the result, then drop it from the version you share.

Common mistakes

• dropna() without how="all", which also drops products that just lack a price.
• astype(float) on the raw text, which raises a ValueError on “$1,299.00”.
• Forgetting to remove the comma, so “1,050” becomes NaN instead of 1050.
• Coercing without counting the NaNs afterwards.