Turn Messy Vendor Lists Into Consistent, Auditable Data
Parse the outer CSV first, split vendor or product lists by an explicit delimiter rule, map approved aliases, and preserve first-seen order.
To deduplicate and normalize comma-separated vendor or product names safely, parse the outer CSV first, extract the multi-value field, split that field under an explicit delimiter rule, normalize each token, map approved aliases to canonical entities, and then deduplicate by canonical identity while preserving first-seen order. Keep the raw values for auditing, and never assume that similar product names identify the same product.
The short answer: parse, normalize, map, then deduplicate
Use this seven-step workflow:
- Parse the outer CSV with a CSV-aware reader.
- Select the list field from the parsed record.
- Split the inner list according to a documented delimiter rule.
- Remove blank tokens and trim application-level whitespace.
- Create normalized matching keys, such as case-folded, space-collapsed text.
- Map approved aliases to canonical IDs and display names.
- Deduplicate by canonical key, preserving first appearance, before rejoining the names or storing them as related rows.
For example:
AWS, aws , Amazon Web Services, GCP, Google Cloud, , AWS
can become:
Amazon Web Services, Google Cloud
The raw strings AWS, aws, and Amazon Web Services are different. They become duplicates only after whitespace normalization, case folding, and approved alias mapping resolve them to the same canonical vendor.
Do not use an unordered set as the final output if display order or lineage matters. Use a seen collection for membership checks while appending the first occurrence of each canonical entity to an ordered list.
Before cleaning, determine whether each token represents:
- a vendor;
- a product;
- or a vendor-product pair.
Do not split linked vendor-product pairs into independent lists and deduplicate those lists separately. That can detach a product from its vendor. Preserve the pair as a structured value or, preferably, as linked fields or rows.
Parse the CSV and the inner list as two separate layers
A CSV containing an inner comma-separated list has two parsing layers:
record_id,raw_names
1,"AWS, GCP, Google Cloud"
At the outer layer, the quotes keep AWS, GCP, Google Cloud in one field. Fields containing commas, double quotes, or line breaks require a CSV-aware parser. Within a quoted field, a literal double quote is represented by two double quotes. Spaces are treated as part of field values rather than automatically discarded under the commonly used conventions documented in RFC 4180.
Parsing the outer CSV extracts the field. It does not decide how the list inside that field should be divided.
For the examples in this article, the inner grammar is deliberately simple:
Every comma in the extracted field is a separator, so an individual vendor or product name cannot contain a comma.
Under that rule, this value is ambiguous:
Acme Widget, Pro
It could mean two entries—Acme Widget and Pro—or one product named Acme Widget, Pro. The simple splitter cannot distinguish them.
If individual names may contain commas, use a different representation:
- a documented secondary quoting or escaping convention with a compatible parser;
- a delimiter that cannot occur in names;
- a structured list such as a JSON array;
- or one related row per entity.
Do not assume the pandas example below supports escaped inner commas. It splits every comma in the extracted field. Adding an escape syntax would require a different parser and a precisely defined format.
CSV-level whitespace and application-level whitespace are also separate concerns. At the CSV layer, spaces belong to the value. After extracting the list field, the cleaning policy may deliberately trim whitespace around each token so AWS becomes AWS. Preserve the original token before applying that transformation.
Run an order-preserving pandas cleanup
This illustrative example is runnable, but it is not benchmarked production code. It uses a CSV-aware reader for the outer file and applies Series.str.split(",", regex=False) only to the extracted raw_names field. With regex=False, pandas treats the separator as a literal string, as described in the official Series.str.split documentation.
from io import StringIO
import pandas as pd
csv_text = """record_id,raw_names
1,"AWS, aws , Amazon Web Services, GCP, Google Cloud, , AWS"
2,"amazon-web-services, GCP, Google Cloud"
3,
4,", AWS,,GCP,"
"""
# Layer 1: parse the outer CSV.
df = pd.read_csv(
StringIO(csv_text),
dtype={"record_id": "Int64"},
keep_default_na=False
)
ALIASES = {
"aws": ("vendor:aws", "Amazon Web Services"),
"amazon web services": ("vendor:aws", "Amazon Web Services"),
"amazon-web-services": ("vendor:aws", "Amazon Web Services"),
"gcp": ("vendor:google-cloud", "Google Cloud"),
"google cloud": ("vendor:google-cloud", "Google Cloud"),
}
def normalized_key(raw_token):
"""Trim outer whitespace, collapse internal spaces, and case-fold."""
cleaned = " ".join(raw_token.strip().split())
return cleaned.casefold()
def canonicalize_tokens(tokens):
seen = set()
canonical_names = []
for raw_token in tokens:
key = normalized_key(raw_token)
if not key:
continue
canonical_id, canonical_name = ALIASES.get(
key,
(
f"unresolved:{key}",
" ".join(raw_token.strip().split())
)
)
if canonical_id in seen:
continue
seen.add(canonical_id)
canonical_names.append(canonical_name)
return canonical_names
# Layer 2: split the extracted inner-list field.
df["tokens"] = (
df["raw_names"]
.fillna("")
.str.split(",", regex=False)
)
df["canonical_list"] = df["tokens"].apply(canonicalize_tokens)
# Serialize only after mapping and deduplication.
df["canonical_names"] = df["canonical_list"].apply(", ".join)
output = df[["record_id", "raw_names", "canonical_names"]]
print(output.to_string(index=False))
A publication-formatted rendering is:
| record_id | raw_names | canonical_names |
|---|---|---|
| 1 | AWS, aws , Amazon Web Services, GCP, Google Cloud, , AWS | Amazon Web Services, Google Cloud |
| 2 | amazon-web-services, GCP, Google Cloud | Amazon Web Services, Google Cloud |
| 3 | ||
| 4 | , AWS,,GCP, | Amazon Web Services, Google Cloud |
keep_default_na=False preserves strings such as NA and NULL as vendor names. Empty fields produce empty lists. Blank tokens from leading, trailing, or repeated delimiters are omitted from the display output; retain them in the audit table when counts need to reconcile.
Deduplication must follow normalization and alias mapping. Exact-string uniqueness would retain both AWS and aws because they differ before normalization. Serialization with ", ".join(...) belongs at the end.
For product titles, keep the rules conservative. Do not remove model numbers, versions, editions, sizes, regions, colors, or bundle terms merely to increase match rates. Values such as Widget 2, Widget 2 Pro, and Widget 2 Pro EU Bundle may represent different records.
Use an alias table instead of increasingly aggressive text cleanup
A maintained alias table is safer than a growing series of transformations that delete punctuation or words.
| Input alias | Lookup key | Canonical display name |
|---|---|---|
| AWS | aws |
Amazon Web Services |
| Amazon Web Services | amazon web services |
Amazon Web Services |
| amazon-web-services | amazon-web-services |
Amazon Web Services |
| GCP | gcp |
Google Cloud |
| Google Cloud | google cloud |
Google Cloud |
The matching key and display name serve different purposes. A case-folded key enables deterministic matching, while the canonical display value preserves approved casing.
A durable alias table should include:
| Field | Purpose |
|---|---|
normalized_alias |
Deterministic lookup key |
canonical_id |
Stable entity identifier |
canonical_name |
Approved display value |
entity_type |
Vendor, product, family, or another class |
review_status |
Approved, unresolved, ambiguous, or retired |
rule_version |
Ruleset that approved the mapping |
Retain each raw_token beside its normalized key and canonical result. If a rule changes, affected records can then be audited and reprocessed from their original values.
Keep vendor and product rules separate. Legal-suffix handling may sometimes help create a vendor matching key, but it should not automatically alter product titles or legal, billing, contractual, and compliance fields. Preserve legal names separately when they matter.
Treat punctuation as an explicit policy decision. A hyphen may be cosmetic in one known alias but meaningful in another brand or product. Prefer an approved mapping such as amazon-web-services → Amazon Web Services over a universal rule that removes all punctuation.
Short or context-dependent values also need review. Teams might mean Microsoft Teams, another product, or an ordinary category label. Leave it unresolved unless a vendor, domain, identifier, or other source context establishes the intended entity.
Do not confuse duplicate text with duplicate products
Three operations must remain distinct:
- Exact duplicate removal removes repeated equivalent strings or keys.
- Approved alias canonicalization maps known names to maintained entities.
- Product-identity resolution determines whether records describe the same real product.
Normalized titles can identify candidates, but similar wording does not prove identity. As practical product-matching guidance, prefer a barcode such as GTIN, UPC, EAN, or ISBN; use manufacturer part number plus brand when a barcode is unavailable; and use ASIN for Amazon-origin records while recognizing that it is a platform identifier. Names are better suited to broad discovery than final identity decisions, according to this product-identifier matching overview.
When identifiers are unavailable or conflicting, keep product records or merchant offers separate until identity can be established. A false merge can erase distinctions needed for analysis or publication.
Prefer structured fields or linked records for:
- vendor;
- product family;
- model;
- variant;
- barcode;
- manufacturer part number;
- platform identifier;
- merchant offer.
Fuzzy matching can help create a review queue, but it should not silently merge records.
Produce an audit table and validate the rules
A clean display column is not sufficient for a reviewable pipeline. Create a long-form audit dataset with one row per extracted token.
The following is a target audit schema, not output generated by the compact pandas example above:
source_row,position,raw_token,normalized_key,canonical_id,canonical_name,match_type,review_status
1,1,"AWS",aws,vendor:aws,"Amazon Web Services",approved_alias,approved
1,2," aws ",aws,vendor:aws,"Amazon Web Services",approved_alias,duplicate
1,3," Amazon Web Services","amazon web services",vendor:aws,"Amazon Web Services",normalized_exact,duplicate
1,4," GCP",gcp,vendor:google-cloud,"Google Cloud",approved_alias,approved
1,5," Google Cloud","google cloud",vendor:google-cloud,"Google Cloud",normalized_exact,duplicate
1,6," ","",,,unresolved,discarded_blank
1,7," AWS",aws,vendor:aws,"Amazon Web Services",approved_alias,duplicate
Quoted raw_token values make leading and trailing spaces visible. In a production audit export, preserve those values exactly rather than reconstructing them from cleaned text.
Define match types deterministically. For example:
approved_alias: an approved alias maps to another canonical display value;normalized_exact: the normalized token matches an approved canonical name;unresolved: no approved mapping exists;ambiguous: more than one plausible entity remains.
Do not attach invented confidence percentages. Record the rule, mapping version, and review outcome that produced the decision.
Test at least these cases before changing historical data:
- null fields and quoted empty strings;
- blank tokens and repeated delimiters;
- leading and trailing commas;
- repeated aliases for one entity;
- quoted outer CSV fields;
- embedded double quotes and line breaks at the CSV layer;
- names containing commas;
- malformed records;
- vendor-product pairs;
- product models and meaningful variants.
Add collision checks. Flag cases where distinct canonical IDs, barcodes, manufacturer part numbers, models, or variants receive the same normalized key. A collision is a reason to narrow the rule, not permission to merge automatically.
Review a sample and run new mappings in report-only mode before rewriting historical records. Test idempotency as well: processing canonical output again should not change it.
Operational reporting can track unmatched-token count, mapping coverage, duplicates removed, identifier conflicts, and reviewed false merges. These are monitoring measures, not accuracy guarantees.
Choose the final shape: cleaned display field or related rows
Choose the output according to its intended use:
| Output | Best for | Main tradeoff |
|---|---|---|
| Rejoined display field | Lightweight publication and exports | Convenient but still multi-valued |
| Long-form audit table | Provenance, review, and rule debugging | More rows |
| Related child rows | Durable storage and analysis | Requires schema changes |
A comma-separated multi-value field is not atomic under First Normal Form. When the schema can be changed, one child row per vendor, product, or relationship is generally preferable to a serialized list, as illustrated in this data-normalization guide.
A child table might retain:
parent_record_id
canonical_entity_id
raw_token
position
rule_version
Use a uniqueness constraint appropriate to the data model, often based on the parent key and canonical entity ID. If repeated occurrences have independent meaning, include position or another relationship key instead of deleting them blindly.
If consumers still need a comma-separated display field, generate it from the cleaned child rows. Do not treat the rejoined string as the authoritative representation.
A small publication-ready export could look like this:
record_id,raw_names,canonical_names
1,"AWS, aws , Amazon Web Services, GCP, Google Cloud, , AWS","Amazon Web Services, Google Cloud"
2,"amazon-web-services, GCP, Google Cloud","Amazon Web Services, Google Cloud"
3,,
4,", AWS,,GCP,","Amazon Web Services, Google Cloud"
This synthetic example exposes both the source and canonical values so readers can inspect the transformation. TablePage can turn a spreadsheet or structured dataset into a public interactive data page. As a general data-safety practice, publish only synthetic, public, or otherwise non-sensitive information.
Preserve raw values, publish a transparent canonical output, and move recurring multi-value data into linked rows whenever you control the schema.