JupyterCon, 12th October 2020
Simon Willison - https://twitter.com/simonw - https://simonwillison.net/
- https://docs.datasette.io/
- https://covid-19.datasettes.com/
- https://github.com/nytimes/covid-19-data
- https://covid-19.datasettes.com/covid/ny_times_us_counties
- https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_week.geojson
- https://sqlite-utils.readthedocs.io/en/stable/
Rendered Jupyter notebook (since Gists can sometimes be flaky at rendering them): https://nbviewer.jupyter.org/gist/simonw/656c21b5800d5e4624dec9930f00e093/earthquakes-to-sqlite.ipynb
Live demo of the Datasette created by the notebook: https://jupytercon-earthquakes-demo.vercel.app/earthquakes/quakes
I published this demo using the datasette-publish-vercel plugin like this:
datasette publish vercel /tmp/earthquakes.db \
--project jupytercon-earthquakes-demo \
--install datasette-cluster-map \
--about_url=https://gist.github.com/simonw/656c21b5800d5e4624dec9930f00e093
Here's how to use a SQLite lag() window function to calculate new-cases-per-day: https://til.simonwillison.net/til/til/sqlite_lag-window-function.md
Example query:
select
date,
county,
deaths,
lag(deaths, 1) OVER (
ORDER BY
date
) as deaths_previous_day,
deaths - lag(deaths, 1) OVER (
ORDER BY
date
) as new_deaths
from
ny_times_us_counties
where
"county" = :p0
and "state" = :p1
order by
date desc
limit
101