Skip to content

Instantly share code, notes, and snippets.

@simonw

simonw/vice-news-police-shootings-datasette.md

Last active January 5, 2018 16:35
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save simonw/6785c842e6b0849aee3a9b1ef59d492e to your computer and use it in GitHub Desktop.
Save simonw/6785c842e6b0849aee3a9b1ef59d492e to your computer and use it in GitHub Desktop.
Download ZIP
VICE News Police Shootings data with Datasette
Raw
vice-news-police-shootings-datasette.md

Running in Datasette at https://vice-police-shootings.now.sh/vice-bc7c892/ViceNews_FullOISData

Here's what I did:

First, I exported as CSV the data from https://docs.google.com/spreadsheets/d/1CaOQ7FUYsGFCHEqGzA2hlfj69sx3GE9GoJ40OcqI9KY/edit#gid=1271324584

Then I converted it to a SQLite database using https://github.com/simonw/csvs-to-sqlite like so:

csvs-to-sqlite ~/Downloads/ViceNews_FullOISData.csv /tmp/vice.db \
    -c Fatal -c SubjectArmed -c SubjectRace -c SubjectGender -c OfficerRace \
    -c OfficerGender -c Department -c city -c NatureOfStop \
    -f city -f Department -f Notes -f FullNarrative -f NatureOfStop

Each -c option means "extract this column into a new foreign key table", e.g. https://vice-police-shootings.now.sh/vice-bc7c892/NatureOfStop

Each -f option means "make the contents of this column searchable using SQLite full text search", e.g. https://vice-police-shootings.now.sh/vice-bc7c892/ViceNews_FullOISData?_search=vehicle

Then I published /tmp/vice.db using https://github.com/simonw/datasette like this:

datasette publish now /tmp/vice.db \
    --title="VICE News Police Shootings" \
    --source="VICE News" \
    --source_url="https://news.vice.com/story/nonfatal-police-shootings-data" \
    --license="CC BY-NC-SA 4.0" \
    --license_url="https://creativecommons.org/licenses/by-nc-sa/4.0/" \
    --name=vice-police-shootings
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment