SFPD incident database and heatmaps: drug-related incident patterns

San Francisco Police Department provides free access to a great data source:  https://data.sfgov.org/browse?category=Public+Safety. The following couple of charts display drug-related incident distribution by year, weekday, time of day, and district.




Some observations:

• the notorious "welfare Wednesday" effect is present in early years;
• incident numbers drop significantly starting 2010, which may be caused by Proposition 215: Wikipedia says "It was not until March 2009 that federal officials announced that they would no longer try to thwart medical marijuana distribution/use in California".

There is a follow-up post focusing on other types of crime in SF using the same data source.

The following are the steps to create the weekday/time heatmap chart using charte.ca editor.



1. Get data from SFPD database and prepare it for charte.ca import. In this example, I was using this spreadsheet - it contains all drug-related incidents between 2003 and 2016.

2. Log in to charte.ca, create a new "XY heatmap with multiple categories" chart, click "Import" below the data grid, paste the SFPD data from the spreadsheet link above.

3. Select appropriate fields on step 2.

4.On step 3, leave all dropdowns blank - we will use all raw data rows.

5. On step 4, make sure you are using COUNT aggregate function.

6. On step 5, make sure that X axis values (weekdays) come  in the order they appear in the source spreadsheet. For this particular example, weekdays are sorted in the spreadsheet from Monday to Sunday. It makes sense to order Y axis  and categories values alphabetically, hence the "asc" setting in correspondent dropdowns.

7. Tweak chart size, title, colors, fonts and it is ready for publishing.