CanViz: SFPD Incidents Involving Robberies or Narcotics [2017-10-18]


padjo-2017 can-viz sfpd-robbers-drugs-incidents your_sunet_id

Turn-in instructions

Send an email to

Subject line should be almost exactly like the following (spacing, case, etc.) except for your Sunet ID:

padjo-2017 can-viz sfpd-robbers-drugs-incidents lelandjr

Send me an attachment containing a 1-page Google Document. No more than 2 charts. No more than 300 words of explanatory information. Use Google Spreadsheets as your visualization tool. Please do not do a geospatial analysis (for now).

2017-10-18 23:59
The SFPD incident log contains more than 2 million reported crimes and events since 2003. For this assignment, we’ll be using an excerpt of that data: incidents categorized as either “ROBBERY” or “DRUG/NARCOTIC”, as reported from 2010 to the end of 2016.


Like any major city, San Francisco has its fair share of criminal incidents. The SFPD has posted an incident log (not exactly the same thing as crime reports/stats) containing events since 2003 in an online database:

The online log contains more than 2.1 million incidents. Is that a lot? Compared to other cities? Are all those incidents equally bad?

Well, don’t worry about comparing to other cities just yet. For this assignment, we’ll be using a subset: about 68,000 incidents categorized as robbery or drug/narcotics, from 2010 to 2016.

You can make a copy of the data at this Google Spreadsheet.

I picked these two categories for a reason. Partly because there were few enough of them between 2010 and 2016 to fit in a spreadsheet that Google Sheets can manage. Partly because these crimes have interesting characteristics, to the extent that some of them are fairly serious and straightforward to categorize. While other kinds of crimes have proven to be more…malleable, as The LAPD can attest


Use Google Spreadsheets and Pivot Tables to summarize something interesting about crime trends from this limited dataset. It doesn’t have to be about both robberies and ddrug/narcotics. You can look at just one or the other if you don’t think there’s anything interesting to compare between them.

The data is here at this Google Spreadsheet.

Make a copy of it. Your first step should probably involve a pivot table just to see how many incidents are involved. Maybe the yearly trend is interesting? Or the monthly/weekly/daily trend? If there is a trend, is there an event external to the data that would explain it? Google/LexisNexis is your friend!

No mapping stuff

For this assignment, limit yourself to what Google Docs/Sheets provides you. Yes, this data contains attributes that make it easily mappable, we’ll get to that later.

In fact, you probably don’t need to worry about these columns, for the scope of this assignment:

  • IncidntNum
  • Address
  • X
  • Y
  • PdId