Fwd: [NICAR-L] New HHS COVID data / analysis

[Tom Johnson]

fyi

---------- Forwarded message ---------
From: jacob fenton <[hidden email]>
Date: Tue, Dec 15, 2020 at 10:29 AM
Subject: [NICAR-L] New HHS COVID data / analysis
To: <[hidden email]>

WARNING: This message has originated from an External Source. This may be a phishing expedition that can result in unauthorized access to our IT System. Please use proper judgment and caution when opening attachments, clicking links, or responding to this email.

Folks:

The Investigative Reporting Workshop’s Accountability Project is releasing a queryable datasette of the updated HHS data on how full hospitals are with COVID patients, as well as a longish list of “canned queries” you can easily adapt, maybe, to your purposes. We use the *raw HHS data* which is a teensy bit complicated, and uses -999999 to indicate cell sizes smaller than 4. Some hospitals are better at treating patients than maintaining public data.

Here’s a query for hospitals ranked by the ratio of ICU beds with COVID patients divided by staffed ICU beds for the most recent week available (12/4-12/10). 

Here’s a listing of ICU covid occupancy and occupancy overall for metro areas in Texas having more than 50 ICU staffed beds in the most recent week available. You can just enter another state in the box and hit “Run SQL” to update it. 

It’s also got a nifty vega dataviz plugin enabled. Here’s a bar chart of  total covid patients at a particular hospital for the last few weeks (identified by CMS id, aka CCN). Because of other folks’ really smart work, you can hover over the chart, click on the funky round thing that appears at the upper right, and edit this in the vega lite editor to export charts, static graphics, etc. 

Please lemme know off-list if there’s something you’re trying to figure out that’s not addressed. SQLite uses essentially all your favorite SQL keywords, but you might need more than a single query. It’s often easiest to just write a SQL query from scratch as the length of variable names can be a bit much. 

Special thanks to The Lund Report and Ben Botkin for feedback on an early draft of this, and to Simon Willison for datasette, the awesome tech this runs on.  Any mistakes are probably mine, please lmk off list and I will try to fix. 

Full list of queries follows below. 

 Example Queries

• Show how many hospitals have filed weekly reports in a state.

• Show current simplified COVID hospitalization in a state

• Find a hospital by name and state and week

• Find the census bureau's CBSA id, which is useful for queries run by metropolitan area

• Show simplified COVID hospitalization over time at one hospital

• How full are ICU's and what fraction is occupied by COVID patients nationally?

• How full are ICUs for a given state?

• How full are ICUs for a given metropolitan area?

• How full are ICUs summed by metropolitan area?

• How full are ICUs summed by metropolitan area in a given state?

• How full are hospital beds and what fraction is occupied by COVID patients nationally?

• How full are hospital beds with COVID patients for a given state?

• How full are hospital beds summed by metropolitan area in a given state?

--

Jacob Fenton

The Accountability Project

[phone]

==================================================================== To unsubscribe from NICAR-L, please send "unsubscribe NICAR-L" in the body of an e-mail message to "listserv@...". Please e-mail listmaster@... if you need help or have questions. ====================================================================

- .... . -..-. . -. -.. -..-. .. ... -..-. .... . .-. .
FRIAM Applied Complexity Group listserv
Zoom Fridays 9:30a-12p Mtn GMT-6  bit.ly/virtualfriam
un/subscribe http://redfish.com/mailman/listinfo/friam_redfish.com
archives: http://friam.471366.n2.nabble.com/
FRIAM-COMIC http://friam-comic.blogspot.com/