Normalise messy data values

Map inconsistent free-text values onto a controlled vocabulary, with permission to return null rather than force a wrong match.

When to use it

Cleaning a column of inconsistent free-text values — countries, currencies, categories — onto a fixed set of codes before it feeds analysis or a join.

What it prevents

The forced match. Told to map values to codes, a model will assign a code to every input, including the junk and the ambiguous ones — and a wrong code is harder to catch than a null, because it silently corrupts the aggregate. The permission to return null is what keeps the bad rows flagged.

The prompt

Convert the messy country values in the list below to ISO 3166 alpha-2 codes.

### Output format
Return JSON and nothing else: [{"input": "string", "code": "string or null"}]

### Requirements
- Map each input to its two-letter code, and preserve the original string in "input".
- Use null when a value is ambiguous or not a country. Do not force a match.
- Treat common names, adjectives, and abbreviations as the same country (for instance "USA", "United States", and "American" all map to US).

### Example
[{"input": "U.S.A.", "code": "US"}, {"input": "Mars", "code": null}]

### Values
"""
USA
united kingdom
Deutschland
n/a
"""

Why each part is there

Every section of that prompt exists because a specific failure happens without it. These are the checks it passes, and what each one is protecting you from:

• States a concrete task — why this matters
• Avoids vague language — why this matters
• Specifies an output format — why this matters
• Avoids dangling references — why this matters
• Includes an example — why this matters
• Separates data from instructions — why this matters
• Avoids over-aggressive phrasing — why this matters

How to adapt this one

Swap the target vocabulary — currency codes, product categories, status values — and keep the two rules: preserve the original input alongside the code, and use null rather than force a match. Keeping the original is what makes the mapping auditable; without it you cannot tell a correct code from a lucky guess. Adapt the synonym guidance to your domain's real variants.

The mistake this prompt avoids

No escape value. Given a fixed set of codes and no “null”, the model maps “n/a” and “Mars” to whatever is nearest, and your clean column now contains confident nonsense. A normalisation without an out is a normalisation that lies about the messy rows — which are the exact rows you were trying to catch.

A variation

Normalising values a human will review rather than a pipeline will consume? Add a confidence field — high for exact matches, low for anything inferred — and route the low ones to a review queue. Bulk cleaning has a long tail of genuinely ambiguous values, and surfacing the model's uncertainty turns an unreviewable column into a short list someone can actually resolve.

Check your version

Once you have adapted it, run it through the checker. The same nine checks that graded this page will grade yours, free and in your browser — paste it in, or build one from scratch by answering a few questions.