You Can't Plot a ZIP Code: Mapping Where Our Co-op Students Actually Work

I wanted something specific, and I couldn't get it out of a spreadsheet no matter how I sorted it. Co-op is the thing that makes this university's model actually different, students go work real jobs for six months at a stretch, sometimes twice over four years, and I wanted to be able to look at a single picture and see where all of that had actually happened. Not a table of employer names and cities that I'd have to hold in my head and mentally place on a map. An actual map, one you could look at and see the shape of it, dense along the coasts, thin somewhere you didn't expect, shifting year over year as a new employer relationship opened up a city that hadn't shown up before.

On paper this looked like a solved problem before I'd even started. Our co-op placement system already tracks a work address for every placement: street, city, state, ZIP code. It also carries a remote flag, and separate remote city and state fields for exactly the placements where a student never sets foot at the employer's office. I had coordinates, or something close enough to coordinates. I just needed to draw them on something.

I started the way I start most things like this, in a notebook, pulling a slice of placements into a dataframe and dropping city and state onto a scatter layer. It worked, technically. Dots showed up roughly where they should. But dots weren't what I actually wanted. A dot is precise and says nothing. What I wanted was filled regions, something closer to a heat map, so that someone who'd never opened a line of code could look at it and see saturation, not go hunting for where a single dot happened to land. That meant boundaries. Actual polygons. And that's where I learned the thing nobody tells you about ZIP codes until you try to draw one: they aren't shapes.

The postal service defines a ZIP code as a mail delivery route, not an area. Routes can skip a block, run down one side of a street and not the other, double back on themselves entirely. There is no official ZIP code boundary anywhere, because the boundary, as a concept, doesn't really exist. What does exist is a Census Bureau approximation called a ZCTA, which clusters the addresses that share a ZIP into a rough polygon that's close enough for most purposes and wrong at literally every edge. I needed something that would let me join a five-digit code to an actual shape I could hand to a mapping tool, and that meant pulling in an outside dataset built for exactly this problem: a public HUD dataset that crosswalks ZIP codes to the Census geographies they mostly overlap with. I loaded it into the same Snowflake sandbox table I was already using to prototype, joined it onto the placement data, and for the first time a placement had a shape instead of a floating point.

import pandas as pd

placements = pd.read_csv("coop_placements_sandbox.csv")
crosswalk = pd.read_csv("hud_zip_geo_crosswalk.csv")

# res_ratio: share of a ZIP's addresses that actually fall inside this geography.
# keep only each ZIP's best match so one ZIP doesn't fan out into three regions.
best_match = (
    crosswalk.sort_values("res_ratio", ascending=False)
    .drop_duplicates(subset="zip")
)

joined = placements.merge(best_match, on="zip", how="left")
by_region = (
    joined.groupby("region_name")["placement_id"]
    .count()
    .sort_values(ascending=False)
)

That got me a shape to plot, but getting the shape to actually render was its own evening. The tool I was using needed the boundary layer as GeoJSON, and the key field in that GeoJSON had to match the key field in the data being visualized exactly, same casing, same padding, same everything. Get it wrong and there's no error. The map just loads empty and gray, and you sit there wondering if the data pipeline is broken when the actual problem is a mismatched string three layers away. I ended up reading through a handful of other people's write-ups of the exact same wall, comparing how they'd formatted their keys, and simplifying the polygon geometry enough that the browser would render it without choking on it. None of that was clever. It was just slow, and every step of it was a real thing someone else had already hit and documented, which is the only reason I got through it in an evening instead of a week.

Once it rendered, the map looked believable, which is a dangerous thing for a map to look like when it's wrong. The first version clustered hard around a handful of employer headquarters, which made sense until I noticed the shape wasn't changing even for placements explicitly marked remote. Those students weren't sitting in an office. They were logging in from wherever they actually lived, which is exactly what the remote city and state fields were for. The pipeline just wasn't using them. Every placement, remote or not, was falling back to the employer's home address, which meant the map wasn't showing where our students were working. It was showing where our employers happened to be headquartered, and treating those as the same thing quietly erased the entire point of the map.

select
    placement_id,
    case
        when is_remote then coalesce(remote_city, city)
        else city
    end as effective_city,
    case
        when is_remote then coalesce(remote_state, state)
        else state
    end as effective_state,
    zip
from stg_coop__placements

The numbers below are fabricated at a reasonable order of magnitude, not the real crosswalk and not a real term's placement counts, because the real dataset has a real HUD file behind it that I'm not going to publish and I'd rather show the shape of the fix than the actual figures:

Nothing in this post ties back to a real student, a real address, or a real employer. Every row up there is invented, and it stays that way for anything I write about this project, because the underlying data is exactly the kind of thing that shouldn't end up in a blog post even shaped as an example.

What stuck with me afterward wasn't the mapping library or the crosswalk dataset, it was how much a ZIP code had been quietly lying to me the whole time by looking so simple. Five digits, no ambiguity, nothing to question. Except it wasn't a shape, and an address on a placement record wasn't always where the person actually was, and I only found either of those things out by trying to draw a picture and watching it come out wrong. That's become the same habit that showed up everywhere else in this kind of work: a field that looks flat and obvious is usually hiding an assumption somebody made once and never wrote down, and the only way to find it is to go build the thing that would break if the assumption were false.