Partner request for API: appointment websites, vaccine types, appointment availability and structured hours information

[simonw]

• appointment websites
• vaccine types
• appointment availability
• structured hours information - Make structured hours information available to the API #721

[simonw]

Vaccine types and appointment availability will come from #650.

Appointment websites is a bit harder, because most of those are for things like pharmacy chains and we've not been keeping our "provider" information robust as we scaled beyond California. We may well be able to pull this together based on concordance identifiers for walgreens: etc though.

High quality structured hours information may be available from specific trusted source location providers.

[simonw]

select count(*) from location where provider_id is not null returns 27933 - more than I expected.

https://vial.calltheshots.us/dashboard/?sql=select+provider.name%2C+count%28%2A%29+from+location+join+provider+on+location.provider_id+%3D+provider.id+group+by+provider.id+order+by+count%28%2A%29+desc%3ACDDhxBBt0RPMY1I_hz_wBsabdkxDCjrztAGiZN61Z7c

name	count
Walgreens	6673
Walmart	4603
CVS	3849
None / Unknown / Unimportant	3318
Kroger	1911
Rite-Aid	1690
Publix	1178
Safeway	662
Sam's Pharmacy	568
Rite-Aid Pharmacy	541
County	440
Health Mart	435
Kaiser Permanente	337
Winn-Dixie	215
Costco Pharmacy	126
Von's	119
The Medicine Shoppe	109
Sav-on Pharmacy	98
Ralph's Pharmacy	81

Do those look right though?

select count(*) from concordance_identifier where authority = 'walgreens' returns 9015 (against 6673 for the provider)

select count(*) from concordance_identifier where authority = 'walmart' returns 4679 (against 4603 for the provider, pretty close)

select count(*) from concordance_identifier where authority = 'cvs' returns 13934 (against 3849, so a lot missing there)

[simonw]

Two ways we could do this then:

• Populate provider_id for our locations based on concordance identifiers, then make sure that the fields on our providers are correctly populated: https://vial.calltheshots.us/dashboard/?sql=select+%2A+from+provider%3AEYBxqAZh3b_mRgszyWdhr2pj7fCn7Svpp7AZjWFSUxA
• Introduce some new mechanism for associating website URLs with those concordance IDs

I like option 1 the best.

Provider is one of our version-tracked tables, so I can tell how often they are updated.

select count(*) from reversion_version where content_type_id = 24 returns 38909 - so we have updated providers a lot!

https://vial.calltheshots.us/dashboard/?sql=select+reversion_version.%2A%2C+reversion_revision.%2A+from+reversion_version+join+reversion_revision+on+reversion_version.revision_id+%3D+reversion_revision.id+where+content_type_id+%3D+24+order+by+date_created+desc%3A_vyWE9gFIqEB47IR8WvQxekVsuakvChoUtRztdfe7WM shows them all.

But not many of those were manual edits: https://vial.calltheshots.us/dashboard/?sql=select+%28select+username+from+auth_user+where+auth_user.id+%3D+user_id%29%2C+count%28%2A%29+as+n+from+%28select+reversion_version.%2A%2C+reversion_revision.%2A+from+reversion_version+join+reversion_revision+on+reversion_version.revision_id+%3D+reversion_revision.id+where+content_type_id+%3D+24+order+by+date_created+desc%29+as+results+group+by+%22user_id%22+order+by+n+desc%3A6BiGaXQWJYKhMk_B5mYxy8RnfkzqpID4bnkQW9BNVgQ

username	n
	38749
cho...	84
swil...	30
lau...	15
mle...	12
ale...	10
kjc...	3
iso...	2
aar...	2
yco...	1
lis...	1

[simonw]

https://vial.calltheshots.us/dashboard/?sql=select%20%22comment%22%2C%20count%28%2A%29%20as%20n%20from%20%28select%20reversion_version.%2A%2C%20reversion_revision.%2A%20from%20reversion_version%20join%20reversion_revision%20on%20reversion_version.revision_id%20%3D%20reversion_revision.id%20where%20content_type_id%20%3D%2024%20and%20user_id%20is%20null%20order%20by%20date_created%20desc%29%20as%20results%20group%20by%20%22comment%22%20order%20by%20n%20desc%3Ay41JTjxSbd4hwNU1wkMRO6qa6TRCCNpZs38NER8i3EQ shows that 38628 of those provider edits had a comment of /api/importLocations called with API key 5:c39e2f3c...

[simonw]

I've now assigned providers to locations that were missing them for the following authorities:

• cvs
• walgreens
• health_mart
• walmart
• rite_aid
• kroger
• safeway
• sams
• hyvee
• costco

Next step: expose the vaccination information for those providers in the APIv0 output.

[simonw]

I'm going to expose it like this:

{
    "name": "Name",
    "provider": {
        "name": "CVS",
        "vaccine_info_url": "..."
     }
}

So the only field from Provider that I will be exposing is the vaccine_info_url one.

[simonw]

Demo: https://vial-staging.calltheshots.us/api/searchLocations?format=v0preview&size=50

Some of them have this:

    {
      "id": "rec01tcOLdRjMfCnZ",
      "name": "ZELZAH PHARMACY",
      "provider": {
        "name": "None / Unknown / Unimportant",
        "vaccine_info_url": null
      },

https://vial-staging.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&size=0 shows 3244 with that value on staging and https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&size=0 shows 2730 with that value in production.

A map of those in production confirms that they are almost all in CA: https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&format=map&all=1

I'm going to update all of those locations to have provider of null instead, then I'm going to mark that provider as obsolete - since provider can be null which means the same thing.

[simonw]

I archived the list of location IDs that currently have None / Unknown / Unimportant in this gist: https://gist.github.com/simonw/023d476fc8b825183551c43ec4153214

[simonw]

Ran this:

url = "https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&all=1&format=ids"
ids = httpx.get(url, headers={"Authorization": "Bearer {}".format(api_key)}).json()
chunks = list(make_chunks(ids, 100))
for chunk in tqdm(chunks):
    httpx.post("https://vial.calltheshots.us/api/updateLocations", timeout=20, json={
      "update": {
        id: {
          "provider_null": True
        } for id in chunk
      },
      "revision_comment": "Issue #707"
    }, headers={"Authorization": "Bearer {}".format(api_key)})

https://vial.calltheshots.us/api/searchLocations?provider=None%20/%20Unknown%20/%20Unimportant&size=0 now returns 0

[simonw]

Renamed https://vial.calltheshots.us/admin/core/provider/5/change/ to have OBSOLETE in the name.

[simonw]

One of the locations in https://api.vaccinatethestates.com/v0/locations.json now looks like this:

{
    "id": "rec07ksibBimtTgNA",
    "name": "WALGREENS #2810",
    "provider": {
        "name": "Walgreens",
        "provider_type": "Pharmacy",
        "vaccine_info_url": "https://www.walgreens.com/findcare/vaccination/covid-19"
    },
    "state": "CA",
    "latitude": 36.93495,
    "longitude": -121.77202,
    "location_type": "Pharmacy",
    "phone_number": "831-768-0183",
    "full_address": "1810 FREEDOM BLVD, Freedom, CA 95019",
    "city": null,
    "county": "Santa Cruz",
    "zip_code": null,
    "hours": {
        "unstructured": "Monday - Sunday: Open 24 hours\nMeal break from 1:30 PM - 2 PM everyday ",
        "structured": [
            {
                "day": "monday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "tuesday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "wednesday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "thursday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "friday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "saturday",
                "opens": "00:00",
                "closes": "23:59"
            },
            {
                "day": "sunday",
                "opens": "00:00",
                "closes": "23:59"
            }
        ]
    },
    "website": null,
    "vaccines_offered": [
        "Pfizer"
    ],
    "concordances": [
        "google_places:ChIJxd3pxlYajoARRxz1FkiAacg",
        "getmyvax_org:62e6da23-df70-40d6-b655-b5643cfe74c5",
        "placekey:223@5vh-dx4-dd9",
        "walgreens:2810",
        "vaccinespotter_org:2736889",
        "vaccinefinder_org:450773d2-cf63-4ddd-a632-45809b45f28b",
        "_tag_provider:walgreens",
        "us_carbon_health:499e577b-78ec-475f-8652-dd0833601f3a",
        "us_carbon_health:da1c2de2-1aab-420e-a366-a178b4020052",
        "placekey:223-223@5vh-dx4-dd9"
    ],
    "last_verified_by_vts": "2021-05-13T16:21:27.244660+00:00",
    "vts_url": "https://www.vaccinatethestates.com/?lng=-121.77202&lat=36.93495#rec07ksibBimtTgNA"
}

[simonw]

I'm going to expose these two fields next:

vial/vaccinate/core/models.py

Lines 314 to 319 in 1a79b1e

	accepts_appointments = models.BooleanField(
	null=True, blank=True, help_text="Does this location accept appointments"
	)
	accepts_walkins = models.BooleanField(
	null=True, blank=True, help_text="Does this location accept walkins"
	)

[simonw]

https://vial-staging.calltheshots.us/location/ltbpt is an example of this:

{
    "id": "ltbpt",
    "name": "Walgreen Drug Store",
    "provider": null,
    "state": "NY",
    "latitude": 40.5824,
    "longitude": -73.82907,
    "location_type": "Unknown",
    "phone_number": null,
    "full_address": "10640 ROCKAWAY BEACH BOULEVARD\nROCKAWAY PARK, NY 11694",
    "city": "ROCKAWAY PARK",
    "county": "Queens",
    "zip_code": "11694",
    "hours": {
        "unstructured": null,
        "structured": null
    },
    "website": "https://www.walgreens.com/findcare/vaccination/covid-19",
    "vaccines_offered": null,
    "accepts_appointments": true,
    "accepts_walkins": false,
    "concordances": [
        "vaccinespotter_org:2713291",
        "us_giscorps_vaccine_providers:82c5f284-453d-4cfe-84d8-e0548ac397c1"
    ],
    "last_verified_by_vts": null,
    "vts_url": "https://www.vaccinatethestates.com/?lng=-73.82907&lat=40.58240#ltbpt"
}

Note that accepts_appointments and accepts_walkins can currently be true or false or null for "we don't know".

[simonw]

This is all now live in production, see https://api.vaccinatethestates.com/v0/locations.json

[simonw]

Summary of the changes:

• New vaccines_offered field with a list of available vaccines (or null if we don't know yet) - this is derived from a combination of our human-generated reports (from calls and web banking) and the results of our family of scrapers - we use the most recent data provided it's from a trusted source
• New accepts_appointments and accepts_walkins fields - these can each be true, false or null for "we don't know yet". These again are from a combination of human reports and scraped data.
• If a location is part of a chain of providers - such as Walgreens or Rite-Aid - and we know that the provider has a vaccine information page for the entire chain, that's now available in the nested provider.vaccine_info_url field
• I've added a hours.structured field - this is populated for locations that we have structured opening hours data, based on our imported data from https://www.vaccines.gov/ - note that the hours.unstructured field (which we have had for a while) is populated by human reports and may differ from the structured field. I recommend only using data from the structured field and ignoring the unstructured field entirely.