DEV Community

saripalli shanmukha kiran sagar
saripalli shanmukha kiran sagar

Posted on

Cleaning a messy product catalogue CSV with pandas (without silently breaking it)

#python #pandas #datacleaning #ecommerce

If you've ever imported a supplier's product list into Shopify, WooCommerce or an ERP, you've probably seen something like this:

sku,title,category,length,price
hb-m8-40-zp,Hex bolt M8x40 zinc,Fasteners,40mm,£0.18
HB-M8-40-ZP ,HEX BOLT M8 X 40MM ZINC PLATED,fasteners,40 mm,0.18
HB-M10-50-A4,Hex Bolt M10 x 50 Stainless A4,Fasteners,5cm,0.71
THR-M12-3FT,Threaded Bar M12 x 3ft,Fasteners,3ft,2.10
HOS-PVC-12,PVC hose 12mm ID,Hose & clips,30m,24.00
SHT-MS-3,Mild steel plate 3mm,sheet metal,2500x1250,
Enter fullscreen mode Exit fullscreen mode

The same bolt appears twice with different casing and a trailing space. Lengths are in mm, cm, ft and m. One price has a currency symbol, one is missing, and "sheet metal" and "Sheet Metal" count as two different categories.

The tempting fix is drop_duplicates() plus a few str.lower() calls. The trouble is that a catalogue is business data. Rows that look the same may differ in finish, pack size or price, and a blank is not always a mistake. My rule: normalise automatically, but anything that changes meaning goes on a review sheet that a human checks.

The pipeline 

import re
import pandas as pd

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

# 1. Normalise SKUs: trim, uppercase, unify separators
df["sku_norm"] = (df["sku"].str.strip().str.upper()
                  .str.replace(r"[\s_/]+", "-", regex=True))

# 2. Tidy category casing so "fasteners" and "Fasteners" are one category
df["category"] = df["category"].str.strip().str.title()

# 3. Turn free-text lengths into one unit (mm)
TO_MM = {"mm": 1, "cm": 10, "m": 1000, "in": 25.4, "ft": 304.8}
def length_mm(text):
    m = re.fullmatch(r"\s*([\d.]+)\s*(mm|cm|m|in|ft)?\s*", text.lower())
    if not m:
        return None          # blank or unparseable -> flag, don't guess
    value, unit = float(m.group(1)), m.group(2) or "mm"
    return round(value * TO_MM[unit], 1)
df["length_mm"] = df["length"].map(length_mm)

# 4. Prices: strip currency symbols, keep blanks as missing
df["price"] = pd.to_numeric(df["price"].str.replace(r"[^\d.]", "", regex=True),
                            errors="coerce")

# 5. Find duplicates on the normalised SKU, but don't delete them blindly
df["dup_group"] = df.groupby("sku_norm")["sku_norm"].transform("size")
dupes = df[df["dup_group"] > 1].sort_values("sku_norm")

# 6. Build a review sheet: everything a human must look at before import
issues = []
for i, row in df.iterrows():
    if row["dup_group"] > 1:
        issues.append((row["sku"], "possible duplicate of " + row["sku_norm"]))
    if row["length"] and pd.isna(row["length_mm"]):
        issues.append((row["sku"], f"length not understood: {row['length']!r}"))
    if pd.isna(row["price"]):
        issues.append((row["sku"], "missing price"))
review = pd.DataFrame(issues, columns=["sku", "issue"])

with pd.ExcelWriter("catalogue_cleaned.xlsx") as xl:
    df.drop(columns="dup_group").to_excel(xl, sheet_name="cleaned", index=False)
    review.to_excel(xl, sheet_name="needs_review", index=False)
print(review.to_string(index=False))
Enter fullscreen mode Exit fullscreen mode

Running it on the sample above prints the review sheet:

         sku                              issue
 hb-m8-40-zp  possible duplicate of HB-M8-40-ZP
HB-M8-40-ZP   possible duplicate of HB-M8-40-ZP
    SHT-MS-3 length not understood: '2500x1250'
    SHT-MS-3                      missing price
Enter fullscreen mode Exit fullscreen mode

and the cleaned sheet now has one category spelling, length_mm as a number (3ft → 914.4, 5cm → 50.0) and numeric prices.

Why each step is written the way it is

Read everything as strings (dtype=str, keep_default_na=False). Otherwise pandas turns SKUs like 00123 into 123 and blanks into NaN before you've even looked at them.

Keep the original column, add a normalised one. sku_norm sits next to sku, so the review sheet can show the value the supplier actually sent. That's what you'll be asked about when something goes wrong.

Unit parsing returns None instead of guessing. 2500x1250 is a sheet size, not a length. A clever regex could "fix" it into something wrong. Flagging it costs one line on the review sheet.

Duplicates are grouped, not dropped. The two M8 bolts really are the same item here, but in real catalogues "duplicates" often differ in pack quantity or finish (ZP vs BZP). dup_group lets you sort and decide which row survives.

Missing prices stay missing. errors="coerce" turns junk into NaN, and the review step lists it. Never fill prices with 0 or a mean.

Extending it

  • Add your own unit table for inches written as " or 1/2" (fractions need a small parser).
  • Normalise materials with a mapping dict ("S/S", "SS", "stainless" → "Stainless steel") and send unmapped values to the review sheet.
  • For titles, generate them from the attributes (Hex Bolt M8 x 40mm, Zinc Plated) instead of trying to clean free text.

The output is two tabs, cleaned and needs_review, which is usually what the person doing the import wants: most of the work done, plus a short list of decisions only they can make.

I'm Shanmukh, a student and solo developer. I do this kind of catalogue and spreadsheet cleanup, plus small websites and REST APIs, as fixed-scope freelance work. If you'd rather not do it yourself, the packages are on Fiverr and Whop. Code is on GitHub.

Top comments (0)