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 county fips code population data #18

Closed simonw closed 4 years ago

simonw commented 4 years ago

There's an un-merged PR against the NYT repo with this data here: https://github.com/nytimes/covid-19-data/pull/155

simonw commented 4 years ago

I asked Aaron King and he's happy for me to re-use his work from that PR.

simonw commented 4 years ago

Example query: https://covid-19.datasettes.com/covid?sql=select%0D%0A++ny_times_us_counties.date%2C%0D%0A++ny_times_us_counties.county%2C%0D%0A++ny_times_us_counties.state%2C%0D%0A++ny_times_us_counties.fips%2C%0D%0A++ny_times_us_counties.cases%2C%0D%0A++ny_times_us_counties.deaths%2C%0D%0A++us_census_county_populations_2019.population%2C%0D%0A++1000000+*+ny_times_us_counties.deaths+%2F+us_census_county_populations_2019.population+as+deaths_per_million%2C%0D%0A++1000000+*+ny_times_us_counties.cases+%2F+us_census_county_populations_2019.population+as+cases_per_million%0D%0Afrom%0D%0A++ny_times_us_counties%0D%0A++join+us_census_county_populations_2019+on+ny_times_us_counties.fips+%3D+us_census_county_populations_2019.fips%0D%0Awhere%0D%0A++%22date%22+%3D+%28select+max%28date%29+from+ny_times_us_counties%29%0D%0Aorder+by%0D%0A++deaths_per_million+desc%0D%0A

date county state fips cases deaths population deaths_per_million cases_per_million
2020-07-14 Hancock Georgia 13141 236 33 8457 3902 27905
2020-07-14 Randolph Georgia 13243 212 25 6778 3688 31277
2020-07-14 Terrell Georgia 13273 249 28 8531 3282 29187
2020-07-14 Early Georgia 13099 313 31 10190 3042 30716
2020-07-14 Emporia city Virginia 51595 146 15 5346 2805 27310
2020-07-14 McKinley New Mexico 35031 3679 192 71367 2690 51550
2020-07-14 Neshoba Mississippi 28099 1050 77 29118 2644 36060
2020-07-14 Essex New Jersey 34013 19307 2078 798975 2600 24164
2020-07-14 Lowndes Alabama 1085 502 25 9726 2570 51614
simonw commented 4 years ago

I'm going to turn this into a view.