Using Datasette with Jupyter to publish your data - JupyterCon 2020 - rendered here: https://nbviewer.jupyter.org/gist/simonw/656c21b5800d5e4624dec9930f00e093

earthquakes-to-sqlite.ipynb

nodes.md

Using Datasette with Jupyter to publish your data

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

From the Q&A

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

Try that here: https://covid-19.datasettes.com/covid?sql=select%0D%0A++date%2C%0D%0A++county%2C%0D%0A++deaths%2C%0D%0A++lag%28deaths%2C+1%29+OVER+%28%0D%0A++++ORDER+BY%0D%0A++++++date%0D%0A++%29+as+deaths_previous_day%2C%0D%0A++deaths+-+lag%28deaths%2C+1%29+OVER+%28%0D%0A++++ORDER+BY%0D%0A++++++date%0D%0A++%29+as+new_deaths%0D%0Afrom%0D%0A++ny_times_us_counties%0D%0Awhere%0D%0A++%22county%22+%3D+%3Ap0%0D%0A++and+%22state%22+%3D+%3Ap1%0D%0Aorder+by%0D%0A++date+desc%0D%0Alimit%0D%0A++101&p0=San+Francisco&p1=California#g.mark=bar&g.x_column=date&g.x_type=ordinal&g.y_column=new_deaths&g.y_type=quantitative