Simon Willison's Weblog: vaccines

Weeknotes: Vaccinate The States, and how I learned that returning dozens of MB of JSON works just fine these days

2021-04-26T01:02:22+00:00

On Friday VaccinateCA grew in scope, a lot: we launched a new website called Vaccinate The States. Patrick McKenzie wrote more about the project here - the short version is that we're building the most comprehensive possible dataset of vaccine availability in the USA, using a combination of data collation, online research and continuing to make a huge number of phone calls.

VIAL, the Django application I've been working on since late February, had to go through some extensive upgrades to help support this effort!

VIAL has a number of responsibilities. It acts as our central point of truth for the vaccination locations that we are tracking, powers the app used by our callers to serve up locations to call and record the results, and as-of this week it's also a central point for our efforts to combine data from multiple other providers and scrapers.

The data ingestion work is happening in a public repository, CAVaccineInventory/vaccine-feed-ingest. I have yet to write a single line of code there (and I thoroughly enjoy working on that kind of code) because I've been heads down working on VIAL itself to ensure it can support the ingestion efforts.

Matching and concordances

If you're combining data about vaccination locations from a range of different sources, one of the biggest challenges is de-duplicating the data: it's important the same location doesn't show up multiple times (potentially with slightly differing details) due to appearing in multiple sources.

Our first step towards handling this involved the addition of "concordance identifiers" to VIAL.

I first encountered the term "concordance" being used for this in the Who's On First project, which is building a gazetteer of every city/state/country/county/etc on earth.

A concordance is an identifier in another system. Our location ID for RITE AID PHARMACY 05976 in Santa Clara is receu5biMhfN8wH7P - which is e3dfcda1-093f-479a-8bbb-14b80000184c in VaccineFinder and 7537904 in Vaccine Spotter and ChIJZaiURRPKj4ARz5nAXcWosUs in Google Places.

We're storing them in a Django table called ConcordanceIdentifier: each record has an authority (e.g. vaccinespotter_org) and an identifier (7537904) and a many-to-many relationship to our Location model.

Why many-to-many? Surely we only want a single location for any one of these identifiers?

Exactly! That's why it's many-to-many: because if we import the same location twice, then assign concordance identifiers to it, we can instantly spot that it's a duplicate and needs to be merged.

Raw data from scrapers

ConcordanceIdentifier also has a many-to-many relationship with a new table, called SourceLocation. This table is essentially a PostgreSQL JSON column with a few other columns (including latitude and longitude) into which our scrapers and ingesters can dump raw data. This means we can use PostgreSQL queries to perform all kinds of analysis on the unprocessed data before it gets cleaned up, de-duplicated and loaded into our point-of-truth Location table.

How to dedupe and match locations?

Initially I thought we would do the deduping and matching inside of VIAL itself, using the raw data that had been ingested into the SourceLocation table.

Since we were on a tight internal deadline it proved more practical for people to start experimenting with matching code outside of VIAL. But that meant they needed the raw data - 40,000+ location records (and growing rapidly).

A few weeks ago I built a CSV export feature for the VIAL admin screens, using Django's StreamingHttpResponse class combined with keyset pagination for bulk export without sucking the entire table into web server memory - details in this TIL.

Our data ingestion team wanted a GeoJSON export - specifically newline-delimited GeoJSON - which they could then load into GeoPandas to help run matching operations.

So I built a simple "search API" which defaults to returning 20 results at a time, but also has an option to "give me everything" - using the same technique I used for the CSV export: keyset pagination combined with a StreamingHttpResponse.

And it worked! It turns out that if you're running on modern infrastructure (Cloud Run and Cloud SQL in our case) in 2021 getting Django to return 50+MB of JSON in a streaming response works just fine.

Some of these exports are taking 20+ seconds, but for a small audience of trusted clients that's completely fine.

While working on this I realized that my idea of what size of data is appropriate for a dynamic web application to return more or less formed back in 2005. I still think it's rude to serve multiple MBs of JavaScript up to an inexpensive mobile phone on an expensive connection, but for server-to-server or server-to-automation-script situations serving up 50+ MB of JSON in one go turns out to be a perfectly cromulent way of doing things.

Export full results from django-sql-dashboard

django-sql-dashboard is my Datasette-inspired library for adding read-only arbitrary SQL queries to any Django+PostgreSQL application.

I built the first version last month to help compensate for switching VaccinateCA away from Airtable - one of the many benefits of Airtable is that it allows all kinds of arbitrary reporting, and Datasette has shown me that bookmarkable SQL queries can provide a huge amount of that value with very little written code, especially within organizations where SQL is already widely understood.

While it allows people to run any SQL they like (against a read-only PostgreSQL connection with a time limit) it restricts viewing to the first 1,000 records to be returned - because building robust, performant pagination against arbitrary SQL queries is a hard problem to solve.

Today I released django-sql-dashboard 0.10a0 with the ability to export all results for a query as a downloadable CSV or TSV file, using the same StreamingHttpResponse technique as my Django admin CSV export and all-results-at-once search endpoint.

I expect it to be pretty useful! It means I can run any SQL query I like against a Django project and get back the full results - often dozens of MBs - in a form I can import into other tools (including Datasette).

TIL this week

Releases this week

django-sql-dashboard: 0.10a1 - (21 total releases) - 2021-04-25
Django app for building dashboards using raw SQL queries

Tags: csv, django, django-admin, postgresql, projects, vaccines, weeknotes, vaccinate-ca, django-sql-dashboard

Animated choropleth of vaccinations by US county

2021-04-04T05:37:24+00:00

Last week I mentioned that I've recently started scraping and storing the CDC's per-county vaccination numbers in my cdc-vaccination-history GitHub repository. This week I used an Observable notebook and d3's TopoJSON support to render those numbers on an animated choropleth map.

The full code is available at https://observablehq.com/@simonw/us-county-vaccinations-choropleth-map

From scraper to Datasette

My scraper for this data is a single line in a GitHub Actions workflow:

curl https://covid.cdc.gov/covid-data-tracker/COVIDData/getAjaxData?id=vaccination_county_condensed_data \
  | jq . > counties.json

I pipe the data through jq to pretty-print it, just to get nicer diffs.

My build_database.py script then iterates over the accumulated git history of that counties.json file and uses sqlite-utils to build a SQLite table:

for i, (when, hash, content) in enumerate(
    iterate_file_versions(".", ("counties.json",))
):
    try:
        counties = json.loads(
            content
        )["vaccination_county_condensed_data"]
    except ValueError:
        # Bad JSON
        continue
    for county in counties:
        id = county["FIPS"] + "-" + county["Date"]
        db[
            "daily_reports_counties"
        ].insert(
            dict(county, id=id), pk="id",
            alter=True, replace=True
        )

The resulting table can be seen at cdc/daily_reports_counties.

From Datasette to Observable

Observable notebooks are my absolute favourite tool for prototyping new visualizations. There are examples of pretty much anything you could possibly want to create, and the Observable ecosystem actively encourages forking and sharing new patterns.

Loading data from Datasette into Observable is easy, using Datasette's various HTTP APIs. For this visualization I needed to pull two separate things from Datasette.

Firstly, for any given date I need the full per-county vaccination data. Here's the full table filtered for April 2nd for example.

Since that's 3,221 rows Datasette's JSON export would need to be paginated... but Datasette's CSV export can stream all 3,000+ rows in a single request. So I'm using that, fetched using the d3.csv() function:

county_data = await d3.csv(
    `https://cdc-vaccination-history.datasette.io/cdc/daily_reports_counties.csv?_stream=on&Date=${county_date}&_size=max`
);

In order to animate the different dates, I need a list of available dates. I can get those with a SQL query:

select distinct Date
from daily_reports_counties
order by Date

Datasette's JSON API has a ?_shape=arrayfirst option which will return a single JSON array of the first values in each row, which means I can do this:

https://cdc-vaccination-history.datasette.io/cdc.json?sql=select%20distinct%20Date%20from%20daily_reports_counties%20order%20by%20Date&_shape=arrayfirst

And get back just the dates as an array:

[
  "2021-03-26",
  "2021-03-27",
  "2021-03-28",
  "2021-03-29",
  "2021-03-30",
  "2021-03-31",
  "2021-04-01",
  "2021-04-02",
  "2021-04-03"
]

Mike Bostock has a handy Scrubber implementation which can provide a slider with the ability to play and stop iterating through values. In the notebook that can be used like so:

viewof county_date = Scrubber(county_dates, {
  delay: 500,
  autoplay: false
})

county_dates = (await fetch(
  "https://cdc-vaccination-history.datasette.io/cdc.json?sql=select%20distinct%20Date%20from%20daily_reports_counties%20order%20by%20Date&_shape=arrayfirst"
)).json()

import { Scrubber } from "@mbostock/scrubber"

Drawing the map

The map itself is rendered using TopoJSON, an extension to GeoJSON that efficiently encodes topology.

Consider the map of 3,200 counties in the USA: since counties border each other, most of those border polygons end up duplicating each other to a certain extent.

TopoJSON only stores each shared boundary once, but still knows how they relate to each other which means the data can be used to draw shapes filled with colours.

I'm using the https://d3js.org/us-10m.v1.json TopoJSON file built and published with d3. Here's my JavaScript for rendering that into an SVG map:

{
  const svg = d3
    .create("svg")
    .attr("viewBox", [0, 0, width, 700])
    .style("width", "100%")
    .style("height", "auto");

  svg
    .append("g")
    .selectAll("path")
    .data(
      topojson.feature(topojson_data, topojson_data.objects.counties).features
    )
    .enter()
    .append("path")
    .attr("fill", function(d) {
      if (!county_data[d.id]) {
        return 'white';
      }
      let v = county_data[d.id].Series_Complete_65PlusPop_Pct;
      return d3.interpolate("white", "green")(v / 100);
    })
    .attr("d", path)
    .append("title") // Tooltip
    .text(function(d) {
      if (!county_data[d.id]) {
        return '';
      }
      return `${
        county_data[d.id].Series_Complete_65PlusPop_Pct
      }% of the 65+ population in ${county_data[d.id].County}, ${county_data[d.id].StateAbbr.trim()} have had the complete vaccination`;
    });
  return svg.node();
}

Next step: a plugin

Now that I have a working map, my next goal is to package this up as a Datasette plugin. I'm hoping to create a generic choropleth plugin which bundles TopoJSON for some common maps - probably world countries, US states and US counties to start off with - but also allows custom maps to be supported as easily as possible.

Datasette 0.56

Also this week, I shipped Datasette 0.56. It's a relatively small release - mostly documentation improvements and bug fixes, but I've alse bundled SpatiaLite 5 with the official Datasette Docker image.

TIL this week

Useful Markdown extensions in Python

Releases this week

airtable-export: 0.6 - (8 total releases) - 2021-04-02
Export Airtable data to YAML, JSON or SQLite files on disk
datasette: 0.56 - (85 total releases) - 2021-03-29
An open source multi-tool for exploring and publishing data

Tags: choropleths, vaccines, d3, datasette, observable, weeknotes

The Worst Ideas of the Decade: Vaccine scares

2009-12-22T21:17:04+00:00

The Worst Ideas of the Decade: Vaccine scares

“The movement blaming vaccines for causing autism emerged in the early 2000s, and it was one of the most catastrophically horrible ideas of the decade.”

Tags: science, vaccines