Puzzleshot #013 - Normalizing region codes with awk

raw data→awk→duckdb→result

You’ve received another tractor sales delivery — but this time the region column is a mess. The data was collected from multiple sources and nobody enforced consistent values. You’re seeing abbreviations, full names, mixed case, and a few unexpected variants all representing the same four regions.

Sample rows:

order_id,customer_id,dealer_id,region,order_total,order_date,product_id,quantity,status,sales_rep
5001,8801,1001,N,124500.00,2024-01-01,X9100,2,complete,Jones
5002,8802,1003,south,87250.00,2024-01-02,CH95,1,pending,Smith
5003,8803,1001,EAST,203000.00,2024-01-03,X9100,3,complete,Davis
5004,8804,1002,W,156750.00,2024-01-04,CH95,1,complete,Jones
5005,8805,1001,North,98500.00,2024-01-05,X9100,2,complete,Brown
5006,8806,1002,SW,445000.00,2024-01-06,CH95,1,complete,Wilson

Valid region values are N, S, E, and W only. Everything else needs to be normalized to a single character abbreviation before aggregation.

The challenge:

Before passing the data to DuckDB for aggregation, use awk to normalize the region column to single character abbreviations. Then produce total order value per region — excluding any rows with region values that can’t be normalized.

Things to consider:

How do you extract and normalize the first character of a field in awk?
How do you validate that the result is one of your four valid values?
What happens to rows like SW that start with a valid character but aren’t valid values?

Reveal solution

For this Puzzleshot, the core challenge is normalizing the inconsistent region values in the stream before DuckDB sees them — extract the first character, upper-case it, validate it’s one of four valid values, and discard anything that doesn’t fit.

A solution:

zcat sales.csv.gz | \
awk -F',' 'BEGIN{OFS=","} 
  NR==1 {print; next}
  {
    first=toupper(substr($4,1,1))
    if (first ~ /^[NSEW]$/) {$4=first; print}
  }' | \
duckdb -c "
  SELECT
    region,
    sum(order_total::DOUBLE) as total_value
  FROM read_csv('/dev/stdin')
  GROUP BY region
  ORDER BY total_value DESC
"

What’s happening:

toupper(substr($4,1,1)) extracts the first character of the region field and uppercases it — handling mixed case variants like south, EAST, and North in a single operation.

if (first ~ /^[NSEW]$/) validates the result is one of the four valid single characters. If it matches, replace column 4 with just that character and print the row. If it doesn’t match, the row is dropped.

NR==1 {print; next} passes the header row through unchanged.

OFS="," ensures awk uses comma as the output delimiter when reconstructing the row after modifying $4. If you’re using a different delimiter you’ll need to update both.

The SW gotcha:

SW starts with S so it maps to South rather than being dropped. Whether this matters depends on your data — if your source only produces abbreviations or full region names it’s fine. If genuinely ambiguous values like SW are possible you need a more explicit mapping approach. Know your data before choosing your normalization strategy.

The elegant part:

Normalization and validation happen in a single condition — no lookup map, no if/else chain. The first character approach works cleanly when your valid values all start with distinct characters, which North, South, East, and West conveniently do.