📊 Procurement Data · 9 min read

Supplier Price List Comparison in CSV: The Unit Trap

Two supplier price lists never arrive in the same shape: the columns are named differently, the items are spelled differently, and the prices are quoted per different unit. Any comparison has to normalise three things — the item key, the unit, and the currency — before it compares a single number. Skip the unit step and the answer can reverse direction. On our five-item test it did exactly that: comparing a price per sheet against a price per square metre showed one supplier 181.6% more expensive, while the converted figures made the same supplier 6.1% cheaper.

Why the two lists never line up

RFC 4180 defines the shape of a CSV record but says nothing about what a column means — no required column names, no types, no unit. Everything that would make two files comparable lives outside the format, in conventions each supplier invents for itself. Three differences account for nearly all of the breakage:

None of that is an error by either supplier. It is what happens when two people describe the same product to two different customers.

What we measured

We wrote two lists: supplier A with five items priced in USD per m2, supplier B with four of the same items priced per sheet or per m2, spelled in lower case with a hyphen variation. Read and merged with pandas 2.3.3 on Python 3.13.5, 2026-11-02. Fixtures and script live in seo_geo_ops/nc_measure_20261102/.

The unit row is the one worth staring at. Supplier B quotes 27.60 per sheet; supplier A quotes 9.80 per square metre. Subtracting those two numbers is meaningless, yet it is the first thing any spreadsheet does when the columns are placed side by side — and it reported B as 181.6% more expensive. Divide by 3 m² per sheet and B becomes 9.20 per m², which is 6.1% cheaper than A.

Three comparable items, and the winner depends on the line: B is cheaper on the 600x600 panel and more expensive on the other two. That is the normal shape of a real comparison, and it is invisible until the units match.

Step 1: normalise the item key, then accept its limits

Trimming, lowercasing and collapsing internal spaces is the cheap 80%:

key = col.str.strip().str.lower().str.replace(r"\s+", " ", regex=True)

It is not enough, and pretending otherwise is how comparisons quietly under-report. Our clip-in versus clip inpair survived normalisation because a hyphen is a real character. Fixing that means a mapping table you maintain by hand — a deliberate list of "these two strings are the same product". That table is the actual procurement knowledge; keep it in the repo next to the script so the next comparison inherits it.

Do not try to solve this with fuzzy matching on the first pass. A 90% string similarity threshold will happily merge 600x600 with 600x1200, and a wrong merge is worse than an unmatched row because it looks like a finding.

Step 2: convert to one unit before comparing

Pick one unit per product family and convert everything into it. Common conversions in building materials: sheet → m² via the sheet area, box → m² via the pack coverage, pallet → m² via the packing density, linear metre → m² via the panel width. Write the conversion factor in its own column so a reviewer can see where a number came from.

SHEET_M2 = 3.0
b["usd_per_m2"] = np.where(b["Unit"] == "sheet", b["price"] / SHEET_M2, b["price"])

Two rules that prevent most mistakes here: never mix units inside one column (list B did exactly that, with a Unit column carrying both sheet and m2), and never derive the factor from a single sample. One sheet being 3 m² does not mean every product from that supplier is 3 m².

Step 3: unmatched rows are questions, not zeroes

In our test two items existed only in A and one only in B. A spreadsheet that sorts by "cheapest" will silently drop them, and the buyer reads that as "supplier B could not beat us on those lines". What actually happened is that B never quoted them. Those are three follow-up actions with a date, not three data points.

Keep the merge indicator — pandas returns it with how="outer", indicator=True— and print the three counts before any price sorting: both, only A, only B. If "only A" is large, the comparison is not finished, it is a draft.

Step 4: keep the sheet auditable

A comparison that cannot be re-checked in six months is worth very little. These columns are the minimum:

• item key as it appears in each source (verbatim, not normalised)
• raw price and raw unit from each supplier
• converted unit price, plus the factor used
• gap as a percentage, computed on converted values only
• source file name and the date the list was issued

The last one decides whether a 6% gap is a real discount or a comparison between last quarter's list and this quarter's. Two price lists without dates are two rumours.

What a price list cannot tell you

Unit price is one line of a landed cost. A price list says nothing about freight and container loading, minimum order quantity, packing density per pallet, tolerance and surface finish, payment terms, or lead time — and those move the total further than a 6% unit difference usually does. Model the landed cost with identical quantities for both suppliers before deciding, and treat the unit-price comparison as the screen that decides who gets the detailed request.

Two mechanical checks before you even get that far: leading zeros and long numeric codes in product references must stay text (keeping leading zeros covers the fix), and the quantity column must be numeric rather than text with thousands separators — a quantity that silently stayed a string will not sum. If the numbers arrive from a PDF, extracting the table into CSV first is more reliable than retyping it.

Frequently Asked Questions

Sources

• RFC 4180, Common Format and MIME Type for CSV Files, section 2: records consist of fields separated by commas, and fields containing commas must be quoted. The format defines no column names, types or units — which is why two valid CSVs can be incomparable without either being wrong. Retrieved 2026-11-02.
• pandas 2.3.3 documentation, pandas.merge: how="outer" keeps unmatched keys from both sides and indicator=True adds a _merge column with the values left_only, right_only and both; on requires identically named keys, while left_on/right_on match differently named columns. Retrieved 2026-11-02.
• Local measurement, 2026-11-02, Python 3.13.5 / pandas 2.3.3, fixtures and script at seo_geo_ops/nc_measure_20261102/: list A 5 items priced USD/m2, list B 4 items priced per sheet or per m2; exact-string merge matched 2 rows; strip+lower+whitespace normalisation matched 3, leaving 2 only-in-A and 1 only-in-B and one pair unmatched on a hyphen; comparing 27.60/sheet against 9.80/m2 gave +181.6%, and converting at 3 m2 per sheet gave 9.20/m2, i.e. −6.1%.