[FRIAM] Fwd: [NICAR-L] New HHS COVID data / analysis
Tom Johnson
tom at jtjohnson.com
Tue Dec 15 16:07:07 EST 2020
fyi
============================================
Tom Johnson - tom at jtjohnson.com
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=mailto%3Atom%40jtjohnson.com>
Institute for Analytic Journalism -- Santa Fe, NM USA
505.577.6482(c) 505.473.9646(h)
*NM Foundation for Open Government*
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=http%3A%2F%2Fnmfog.org>
*Check out It's The People's Data
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fwww.facebook.com%2Fpages%2FIts-The-Peoples-Data%2F1599854626919671>*
============================================
---------- Forwarded message ---------
From: jacob fenton <jsfenfen at gmail.com>
Date: Tue, Dec 15, 2020 at 10:29 AM
Subject: [NICAR-L] New HHS COVID data / analysis
To: "Friam at redfish. com" <friam at redfish.com>, <swilger at chi-phi.org>,"Peter St. Cyr" <peter.stcyr at gmail.com>,Bryant Furlow <bryant.furlow at gmail.com>,"Henry M. Lopez" <hlopez at sfnewmexican.com>
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
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fpublicaccountability.org%2F>
is releasing a queryable datasette of the updated HHS data
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs>
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
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2Bcapacity%2Bratios%2Bnationwide>
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
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2520summary%2520by%2520large%2520metropolitan%2520areas%2520in%2520a%2520given%2520state%3Fstate%3DTX>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
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%3Fsql%3Dselect%2B%2B%2Bstate%252C%2B%2B%2Bhospital_name%252C%2B%2B%2Bcollection_week%252C%2B%2B%2Baddress%252C%2B%2B%2Bcity%252C%2B%2B%2Bstate%252C%2B%2B%2Bzip%252C%2Bccn%252C%2B%2B%2Bfips_code%2Bas%2Bcounty_fips%252C%2B%2B%2Ball_adult_hospital_inpatient_beds_7_day_avg%2Bas%2Badult_inpatient_beds%252C%2B%2B%2Binpatient_beds_used_7_day_avg%2Bas%2Binpatient_beds_used%252C%2B%2B%2Btotal_adult_patients_hospitalized_confirmed_and_suspected_covid_7_day_avg%2Bas%2Bcovid_all%252C%2B%2B%2Btotal_adult_patients_hospitalized_confirmed_covid_7_day_avg%2Bas%2Bconfirmed_covid%252C%2B%2B%2Btotal_icu_beds_7_day_avg%2Bas%2Btot_icu_beds%252C%2B%2B%2Btotal_staffed_adult_icu_beds_7_day_avg%2Bas%2Bstaffed_icu_beds%252C%2B%2B%2Bstaffed_adult_icu_bed_occupancy_7_day_avg%2Bas%2Bicu_occupancy%252C%2B%2B%2Bstaffed_icu_adult_patients_confirmed_and_suspected_covid_7_day_avg%2Bas%2Ball_covid_icu%252C%2B%2B%2Bstaffed_icu_adult_patients_confirmed_covid_7_day_avg%2Bas%2Bconfirmed_covid_icu%2Bfrom%2B%2B%2Bhosp_capacity%2Bwhere%2B%2B%2Bccn%2B%253D%2B%2527380051%2527%2Band%2Bcovid_all%2B%253E%2B0%2Band%2Bcollection_week%2B%253E%2B%25272020-10-01%2527%2Border%2Bby%2Bcollection_week%2Bdesc%253B%23g.mark%3Dbar%26g.x_column%3Dcollection_week%26g.x_type%3Dordinal%26g.y_column%3Dcovid_all%26g.y_type%3Dquantitative>
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
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fvega.github.io%2Feditor%2F%23%2Furl%2Fvega-lite%2FN4IgJghgLhIFygK4CcA28QHoAW2DOAdAFZ4D2AdgPx4COqAvHgKapMDGUA1N3jFEwFIATAGFunbKTwAHAJYxUAfXIQAtoNHi2pVKw6yKigO5MmAa2FjuEMGGRM8eS1vkBPZzz4arnAF6zpZzY2cg9OADMAvEVtMCZOCDxObURyKFdFSOknTWtdRRtEVChFSRl5CCVZcmloWSY0xQAjJjBogHZFSAyIADcAcwSkwuLFatqoesaWtrDxuoaSmejEZjBFTu6CgaHOecnF5taVtbCoUgUCsCKSiamoaLK5BVlfVpiKSOR1dYhydbwq2k7H4620vVk602EB6O0SyVIEN%2BujOF0qVxuijui0eUmelVe7205C%2BPw%2BSI2XRh20G8OJpKJiMhqMusjYiCObUpWz6tKS5xKbI5yxZ6N4EHC4XeI0F7M5HSpsL5nHFkveQvlYVVUt%2B11GGpmilIwUQtXIbAy0KVuw1xvZZotWpgavWGplWIWaWi9Nk32l-0UgJkIMZFKtNN2lSU4MhY3ZTolOrjHPd2K9HxJvrJMahiojdM%2BWdDsaFEWQpFU4ieMQgtTYbk4RmwTHsWhCnAEAGYACKcADknYAHAAGYcAVgAjH2Ev8ERSox3OwBRTjDmdgBG6EEGcjGUxmRcrvtCYcngC0E%2BHZ%2BHU84pGQcWQnCark3ekmhhM5k4cTwbC7ABCABkgbYLWTD0BAyDIDCIF4GBwKQdBMIgAANCA4T3qo0DwKA6TAhgJAUCAAC%2BJHoVAMHkHgmHfPAADaoBsJU7KoNATAYAAOuQAAEfH8QJfbaFu%2BifvucA8X2PGcDxkBQIgqj0UJOjvjue7mH2AC60ncQJemSahPE5gUugSVJMlyQpSnGVGWk6bx%2Bn8QAFOElTMDxlAGTxZnSbJ0BWX2dlmX2ACU9mOc5rmoO5nl9oZPkWf5imBdpwUhbpjloSAiQYIo5w6JM0iBgp2HIK4pGaehpVmBgTRQVlDSxNU-S4SAAAerWRCwYAYMJqlieYWX4RxcAgPeYDVJUWVNNU8BRcw5EgOVCAYfUqA9aNNm6ENrgEaNNCIH8kx8LIvQcehM3kHNblMIt%2BXFAEnVrRtIB5aQBUBMVqileVFG7SNY0PpN6BkSRQA>
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
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fwww.thelundreport.org%2F>
and Ben Botkin
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Ftwitter.com%2Fbenbotkin1>
for feedback on an early draft of this, and to Simon Willison for datasette
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fdatasette.io%2F>,
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.
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FShow%2Bhow%2Bmany%2Bhospitals%2Bhave%2Bfiled%2Bweekly%2Breports%2Bin%2Ba%2Bstate>
-
Show current simplified COVID hospitalization in a state
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FShow%2Bcurrent%2Bsimplified%2BCOVID%2Bhospitalization%2Bin%2Ba%2Bstate>
-
Find a hospital by name and state and week
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FFind%2Ba%2Bhospital%2Bby%2Bname%2Band%2Bstate%2Band%2Bweek>
-
Find the census bureau's CBSA id, which is useful for queries run by
metropolitan area
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FFind%2Ba%2Bmetropolitan%2Barea%2Bcode%2Bby%2Bname>
-
Show simplified COVID hospitalization over time at one hospital
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FShow%2Bsimplified%2BCOVID%2Bhospitalization%2Bfor%2Ba%2Bsingle%2Bhospital>
-
How full are ICU's and what fraction is occupied by COVID patients
nationally?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2Bcapacity%2Bratios%2Bnationwide>
-
How full are ICUs for a given state?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2Boccupancy%2Bin%2Ba%2Bgiven%2Bstate>
-
How full are ICUs for a given metropolitan area?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2Boccupancy%2Bin%2Ba%2Bgiven%2Bmetropolitan%2Barea>
-
How full are ICUs summed by metropolitan area?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2Bsummary%2Bby%2Blarge%2Bmetropolitan%2Bareas>
-
How full are ICUs summed by metropolitan area in a given state?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FICU%2Bsummary%2Bby%2Blarge%2Bmetropolitan%2Bareas%2Bin%2Ba%2Bgiven%2Bstate>
-
How full are hospital beds and what fraction is occupied by COVID
patients nationally?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FCOVID%2Bcapacity%2Broom%2Bratios%2Bnationally>
-
How full are hospital beds with COVID patients for a given state?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FHospital%2Bbed%2Boccupancy%2Bin%2Ba%2Bgiven%2Bstate>
-
How full are hospital beds summed by metropolitan area in a given state?
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fhhscovid.publicaccountability.org%2Fhhs%2FHospital%2Bbed%2Boccupancy%2Bin%2Ba%2Blarge%2Bmetropolitan%2Bareas%2Bin%2Ba%2Bgiven%2Bstate>
--
Jacob Fenton
The Accountability Project
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=https%3A%2F%2Fpublicaccountability.org%2F>
(202) 997-6477
==================================================================== To
unsubscribe from NICAR-L, please send "unsubscribe NICAR-L" in the body of
an e-mail message to "listserv at lists.missouri.edu"
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=>.
Please e-mail listmaster at ire.org
<https://www.cloudhq-mkt9.net/mail_track/link/8521730da86a7ed245_1608066484448?uid=226430&url=mailto%3Alistmaster%40ire.org>
if you need help or have questions.
====================================================================
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://redfish.com/pipermail/friam_redfish.com/attachments/20201215/8646a2d7/attachment.html>
More information about the Friam
mailing list