CODEWITHSUNDEEP
pythonpython-requestsdata.gov
klenwell
klenwell

Reputation: 7148

How can I programmatically access county-level COVID-19 testing data from US government?

I am trying to programmatically access COVID-19 testing data for Kent County, MI. A CSV download or JSON API endpoint would be great but I cannot locate these.

Here is how I can access the data in my browser (Firefox):

Peeking at the background request that actually loads the data in Firefox, this is what I see:

Request Method

POST https://wabi-us-gov-iowa-api.analysis.usgovcloudapi.net/public/reports/querydata?synchronous=true

Request Headers

Host: wabi-us-gov-iowa-api.analysis.usgovcloudapi.net
User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:76.0) Gecko/20100101 Firefox/76.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate, br
Content-Type: application/json;charset=UTF-8
Content-Length: 1160
DNT: 1
Connection: keep-alive
Upgrade-Insecure-Requests: 1
Cache-Control: max-age=0, no-cache
Origin: https://wabi-us-gov-iowa-api.analysis.usgovcloudapi.net
Pragma: no-cache

Request Body

{"version":"1.0.0","queries":[{"Query":{"Commands":[{"SemanticQueryDataShapeCommand":{"Query":{"Version":2,"From":[{"Name":"t1","Entity":"Testing_Data"},{"Name":"c","Entity":"Cases by County"}],"Select":[{"Column":{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"TestType"},"Name":"Testing_Data.TestType"},{"Column":{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"MessageDate"},"Name":"Testing_Data.MessageDate"},{"Aggregation":{"Expression":{"Column":{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"TestCount"}},"Function":0},"Name":"CountNonNull(Testing_Data.TestCount)"}],"Where":[{"Condition":{"In":{"Expressions":[{"Column":{"Expression":{"SourceRef":{"Source":"c"}},"Property":"COUNTY"}}],"Values":[[{"Literal":{"Value":"'Kent'"}}]]}}}]},"Binding":{"Primary":{"Groupings":[{"Projections":[1,2]}]},"Secondary":{"Groupings":[{"Projections":[0]}]},"DataReduction":{"DataVolume":4,"Primary":{"Sample":{}},"Secondary":{"Top":{}}},"Version":1}}}]},"QueryId":"","ApplicationContext":{"DatasetId":"3538771d-70f8-4399-9760-267975e37f65","Sources":[{"ReportId":"f489615d-c09e-43f9-b6bb-db2832eb0e0d"}]}}],"cancelQueries":[],"modelId":282246}

Here's my naive attempt to reproduce that using Python requests:

>>> import requests, json
>>> url = "https://wabi-us-gov-iowa-api.analysis.usgovcloudapi.net/public/reports/querydata?synchronous=true"
>>> origin = "https://wabi-us-gov-iowa-api.analysis.usgovcloudapi.net"
>>> host = "wabi-us-gov-iowa-api.analysis.usgovcloudapi.net"
>>> headers = {'Host': host, 'Origin': origin, 'DNT': '1'}
>>> body = """{"version":"1.0.0","queries":[{"Query":{"Commands":[{"SemanticQueryDataShapeCommand":{"Query":{"Version":2,"From":[{"Name":"t1","Entity":"Testing_Data"},{"Name":"c","Entity":"Cases by County"}],"Select":[{"Column":{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"TestType"},"Name":"Testing_Data.TestType"},{"Column":{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"MessageDate"},"Name":"Testing_Data.MessageDate"},{"Aggregation":{"Expression":{"Column":{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"TestCount"}},"Function":0},"Name":"CountNonNull(Testing_Data.TestCount)"}],"Where":[{"Condition":{"In":{"Expressions":[{"Column":{"Expression":{"SourceRef":{"Source":"c"}},"Property":"COUNTY"}}],"Values":[[{"Literal":{"Value":"'Kent'"}}]]}}}]},"Binding":{"Primary":{"Groupings":[{"Projections":[1,2]}]},"Secondary":{"Groupings":[{"Projections":[0]}]},"DataReduction":{"DataVolume":4,"Primary":{"Sample":{}},"Secondary":{"Top":{}}},"Version":1}}}]},"QueryId":"","ApplicationContext":{"DatasetId":"3538771d-70f8-4399-9760-267975e37f65","Sources":[{"ReportId":"f489615d-c09e-43f9-b6bb-db2832eb0e0d"}]}}],"cancelQueries":[],"modelId":282246}"""
>>> requests.post(url, json=json.loads(body), headers=headers)
<Response [401]>

As far as I can tell, there's no authentication required, but I suspect there are other restrictions like cross-origin in place.

I've tried to dig up the actual government API documentation on Google, but it's a bit of morass. I found this page:

But half the links there 404. Any help is welcome. Thanks!

Upvotes: 1

Views: 316

Answers (2)

Sidharth
Sidharth

Reputation: 66

This new API (docs at plansafe.xyz/api) officially offers COVID-19 data for any county in the United States.

Below is a picture of a little GET request I did for current data from Kent County, Michigan. The API can also be used for historical data as well (any day's COVID-19 data for any county). It's free of charge too!

COVID-19 County Data for Kent County, Michigan

In case the image does not load, I simply did a request to the endpoint with Kent and Michigan as URL parameters as specified by the docs. I tested it on Python, but since it's an HTTP GET, it should work with curl, etc.

Upvotes: 0

klenwell
klenwell

Reputation: 7148

I managed to tape together a successful request. A big help was discovering the "Copy as cURL" option when I right-clicked on the network tab request in the Firefox web console. Here's what it gave me:

curl 'https://wabi-us-gov-iowa-api.analysis.usgovcloudapi.net/public/reports/querydata?synchronous=true' \
 -H 'User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:76.0) Gecko/20100101 Firefox/76.0' \
 -H 'Accept: application/json, text/plain, */*' \
 -H 'Accept-Language: en-US,en;q=0.5' --compressed \
 -H 'Referer: https://app.powerbigov.us/view?r=eyJrIjoiMWNjYjU1YWQtNzFlMC00N2ZlLTg3NjItYmQxMWI4OWIwMGY1IiwidCI6ImQ1ZmI3MDg3LTM3NzctNDJhZC05NjZhLTg5MmVmNDcyMjVkMSJ9' \
 -H 'ActivityId: 62469790-1ee4-47e5-ae3e-183e05a7b299' \
 -H 'RequestId: ce1c2243-37ba-666e-831f-e4947a4ab1ac' 
 -H 'X-PowerBI-ResourceKey: 1ccb55ad-71e0-47fe-8762-bd11b89b00f5' \
 -H 'Content-Type: application/json;charset=UTF-8' \
 -H 'Origin: https://app.powerbigov.us' \
 -H 'DNT: 1' \
 -H 'Connection: keep-alive' \
 --data-raw $'{"version":"1.0.0", etc ...}'

This led to this scripted request which succeeded:

import requests
import json

url = 'https://wabi-us-gov-iowa-api.analysis.usgovcloudapi.net/public/reports/querydata'

# headers and body were simply copy-pasted from Firefox console.
headers = {
    'Host': 'wabi-us-gov-iowa-api.analysis.usgovcloudapi.net',
    'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv',
    'Accept': 'application/json, text/plain, */*',
    'Accept-Language': 'en-US,en;q=0.5',
    'Accept-Encoding': 'gzip, deflate, br',
    'Referer': 'https',
    'ActivityId': '62469790-1ee4-47e5-ae3e-183e05a7b299',
    'RequestId': 'ce1c2243-37ba-666e-831f-e4947a4ab1ac',
    'X-PowerBI-ResourceKey': '1ccb55ad-71e0-47fe-8762-bd11b89b00f5',
    'Content-Type': 'application/json;charset=UTF-8',
    'Origin': 'https',
    'DNT': '1',
    'Connection': 'keep-alive'
}

body = (
    '{"version":"1.0.0","queries":[{"Query":{"Commands":[{"SemanticQueryDataShapeCommand":'
    '{"Query":{"Version":2,"From":[{"Name":"t1","Entity":"Testing_Data"},{"Name":"c",'
    '"Entity":"Cases by County"}],"Select":[{"Column":{"Expression":{"SourceRef":'
    '{"Source":"t1"}},"Property":"TestType"},"Name":"Testing_Data.TestType"},{"Column":'
    '{"Expression":{"SourceRef":{"Source":"t1"}},"Property":"MessageDate"},"Name":'
    '"Testing_Data.MessageDate"},{"Aggregation":{"Expression":{"Column":{"Expression":'
    '{"SourceRef":{"Source":"t1"}},"Property":"TestCount"}},"Function":0},"Name":'
    '"CountNonNull(Testing_Data.TestCount)"}],"Where":[{"Condition":{"In":{"Expressions":'
    '[{"Column":{"Expression":{"SourceRef":{"Source":"c"}},"Property":"COUNTY"}}],'
    '"Values":[[{"Literal":{"Value":"\'Kent\'"}}]]}}}]},'
    '"Binding":{"Primary":{"Groupings":[{"Projections":[1,2]}]},"Secondary":{"Groupings":'
    '[{"Projections":[0]}]},"DataReduction":{"DataVolume":4,"Primary":{"Sample":{}},'
    '"Secondary":{"Top":{}}},"Version":1}}}]},"QueryId":"","ApplicationContext":'
    '{"DatasetId":"3538771d-70f8-4399-9760-267975e37f65","Sources":[{"ReportId":'
    '"f489615d-c09e-43f9-b6bb-db2832eb0e0d"}]}}],"cancelQueries":[],"modelId":282246}'
)

json_data = json.loads(body)
response = requests.post(url, json=json_data, headers=headers)
data = response.json()

I suspect the ID or key will eventually expire but thus far they've lasted over 24 hours.

I'd still be interested in finding the documentation on accessing this API endpoint.

Upvotes: 1

Related Questions