search

simonw / covid-19-datasette

Deploys a Datasette instance of COVID-19 data from Johns Hopkins CSSE and the New York Times
https://covid-19.datasettes.com/
61 stars 6 forks source link

Add table of USA state populations #10

Closed simonw closed 4 years ago

simonw commented 4 years ago

Looking at deaths-per-head-of-population could help illustrate which states are doing better or worse.

simonw commented 4 years ago

The United States Census Bureau has a CSV file for this: https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv

From https://www.census.gov/data/datasets/time-series/demo/popest/2010s-state-total.html

simonw commented 4 years ago

I'm going to build my own cut-down version of that census file and check it into the repo.

simonw commented 4 years ago

I took the original file, deleted all of the columns except for REGION, DIVISION, STATE, NAME, POPESTIMATE2019, then renamed those columns to the following:

https://github.com/simonw/covid-19-datasette/blob/c25acee7c7667dfe15c7d6aca17a2b04101aa460/us_census_state_populations_2019.csv#L1

simonw commented 4 years ago

Here's a query that attempts to calculate deaths-per-million for the most recently reported state daily numbers: https://covid-19.datasettes.com/covid?sql=select+date%2C+state%2C+fips%2C+cases%2C+deaths%2C+pop_estimate_2019%2C+1000000.0+*+deaths+%2F+pop_estimate_2019+as+deaths_per_million%0D%0Afrom+ny_times_us_states%0D%0Ajoin+us_census_state_populations_2019+on+state+%3D+state_name+%0D%0Awhere+%22date%22+%3D+%28select+max%28date%29+from+ny_times_us_states%29+order+by+deaths_per_million+desc

select date, state, fips, cases, deaths, pop_estimate_2019, 1000000.0 * deaths / pop_estimate_2019 as deaths_per_million
from ny_times_us_states
join us_census_state_populations_2019 on state = state_name 
where "date" = (select max(date) from ny_times_us_states) order by deaths_per_million desc