I started this for the DEV Weekend Challenge: Dog Days Edition and didn't finish in time to submit. Publishing it anyway — what the data turned out to say deserves an audience more than it deserves a deadline.
International Dog Day is August 26. Most of the internet will spend it posting
photos of the dogs people have. I spent a weekend counting the ones nobody came
for.
The Austin Animal Center — the largest no-kill shelter in the United States —
publishes two public registers: every animal that arrived, and every animal that
left. Between them sit 297,982 rows going back to October 2013.
Neither register says how long a dog stayed. That number doesn't exist in the
data. It has to be built.
So I built it, for all 82,510 dogs.
The thing everyone knows is not true
"Black dog syndrome" is the widely repeated belief that black dogs are adopted
more slowly than lighter ones. It has its own Wikipedia section. Shelters run
campaigns about it. Rescue blogs treat it as settled.
Across a decade of records:
| Coat | Dogs | Median wait |
|---|---|---|
| Black | 29,596 | 5 days |
| Every other colour | 52,914 | 5 days |
Identical. Among dogs that were actually adopted, also identical — eight days
each. The mean is marginally lower for black dogs.
I built an entire cohort filter expecting to visualise a gap. There isn't one.
The real divide is breed, and it isn't close
A pit-bull-type dog waits a median of 23 days for adoption. A terrier or a
small dog waits 5 — same building, same staff, same day.
And the number that stopped me:
Pit-bull-type dogs are 18.7% of the dogs in this dataset.
They are 44.7% of every dog that waited more than ninety days.
Nearly half of the forgotten dogs are one kind of dog.
The oldest dogs leave fastest, and that's the worst finding here
Senior dogs have the shortest median stay of any age group — three days. For a
moment that reads as good news.
| Age at intake | Dogs | Median stay | Adopted | Euthanised |
|---|---|---|---|---|
| Puppy (<1 yr) | 26,616 | 5 d | 61.0% | 1.1% |
| Adult (3–7) | 18,192 | 6 d | 36.1% | 3.1% |
| Senior (8+) | 6,939 | 3 d | 25.5% | 6.3% |
A senior is less than half as likely to be adopted as a puppy, and six times more
likely to be euthanised. They leave quickly because somebody comes to reclaim
them, or because they die. Not because they're wanted.
Speed of exit is not the same thing as being chosen — a distinction invisible in
any single average, which is exactly why I ended up showing every dog instead.
See it
The Waiting Room renders all 82,510 stays as 82,510 dots.
One dot is one real dog, sorted by how long it waited and stacked into bands, so
the shape of the field is the distribution: a huge mass gone within a week, and
a thin bright line at the bottom of dogs who waited over three months. Filter by
coat or breed and watch a cohort concentrate — or fail to — in the long-wait
bands. Hover any dot for that animal's actual record.
kengnidoriane
/
The-waiting-room
94,505 real shelter dogs, and how long each one waited. Twelve years of open records joined in Snowflake — one dot is one dog.
The Waiting Room
94,505 real shelter dogs, and how long each one waited.
Twelve years of intake and outcome records from the Austin Animal Center, joined to answer a question neither register states directly: how many days did each dog wait before somebody came?
One dot on the page is one dog.
Built for the DEV Weekend Challenge: Dog Days Edition.
The problem this solves
The City of Austin publishes two separate public registers:
- Intakes — every animal that arrived, with breed, colour, age and condition
- Outcomes — every animal that left, and what became of it
Neither one contains a length of stay. That number does not exist in the source data. It has to be reconstructed by pairing each arrival with its matching departure — and that is harder than it looks, because a dog can pass through the shelter more than once. A naive join on…
How I built it
The join is the whole project. A dog can pass through the shelter more than
once — 12,932 of these stays belong to an animal that had been there before. A
naive join on animal ID pairs every arrival with every departure and produces
nonsense. What you need, for each arrival, is its next departure:
SELECT i.*, o.out_ts, o.outcome_type,
date_diff('day', i.in_ts, o.out_ts) AS days_waited
FROM i
LEFT JOIN o ON o.animal_id = i.animal_id AND o.out_ts >= i.in_ts
QUALIFY row_number() OVER (PARTITION BY i.animal_id, i.visit_no
ORDER BY o.out_ts) = 1
QUALIFY is a WHERE that runs after the window function. Without it this
needs a nested subquery and the intent gets buried; with it the rule reads in one
line. That single clause is what turns two flat CSVs into this piece.
Everything is computed before the browser sees it. DuckDB handles ingestion,
the join and the aggregation, precomputing every coat × breed-group combination
with a CUBE. The page is entirely static — no API, no server, nothing that can
fall over months from now. The browser does no analysis. It draws.
Canvas, not SVG. 82,510 SVG nodes would cost hundreds of megabytes of DOM and
seconds of layout; the same marks as fillRect calls paint in about twenty
milliseconds, so the field redraws instantly on every filter change. Sorting the
dogs and stacking them into six labelled bands — each band's height proportional
to its count — is what makes 82,510 dots a chart instead of a texture. The palette
was validated rather than eyeballed: worst adjacent colour-vision-deficiency
separation ΔE 26.8 against a floor of 8, with a legend and a table view so
identity never rests on colour alone.
What went wrong. The Austin portal 403s Python's default User-Agent, then
refused my network entirely, browser included — the pipeline now tries three
sources and prints a manual fallback. The two sources also disagree about
everything: one calls the column animal_id, the other "Animal ID"; one dates
in ISO 8601, the other as 12/02/2013 12:00:00 AM. Columns are matched on letters
and digits alone, and timestamps parse under either format.
I also wired an optional Gemini path to normalise the 2,503 free-text breed
strings, classifying the distinct values and joining back rather than labelling
all 82,510 rows. My network dropped the outbound connection, the script degraded
to pattern matching, and the run completed. The published figures come from
pattern matching. I'd rather say that than imply an AI step that didn't happen.
What this doesn't prove
One shelter, one American city, 1 October 2013 to 11 March 2023. This describes
Austin, not dogs. "Black" is a substring match on a free-text colour field, so a
black-and-white dog counts as black. Breed groups come from staff guesses at
intake, unreliable for mixed dogs — a caveat that lands hardest on exactly the
group with the worst numbers. Median stay mixes adoption, reclaim, transfer and
death, which is why the age table breaks outcomes out separately.
Every figure here is reproducible with one command. If you think I got something
wrong, the repo is right there and I'd genuinely like to know.
Source. Austin Animal Center Intakes and Outcomes, published by the
City of Austin Open Data Portal.
The portal returns 403 Forbidden on some networks, mine included, so I worked
from a mirror of the same export
— a copy, not the source. The data is the City of Austin's, and a decade of every
arrival and every departure, free to anyone who asks, is the reason a stranger on
another continent could find this out in a weekend.
3,040 dogs in this dataset waited more than ninety days. 1,359 of them were the
same kind of dog.
Top comments (0)