For this project, I sought to demonstrate a relationship between the occurrence of crimes in Austin, Texas (May 2014 and November 2015) and the relevant moon phases in the same time period. My hypothesis was that more crimes occur when the moon is in its full period in comparison to other moon phase periods.
In order to visualize this relationship, I first had to download datasets, create a database using phpMyAdmin, import data using Python, export data for visualization, and finally create visualizations in Tableau.
I worked individually on this project, so I was responsible for all project phases and creation of the final workflow report.
For my more complete workflow, please reference my full report. The below figure displays the workflow used for this project.
This project uses data sets from two sources. The APD Incident Report Data contains police crime incident reports in the City of Austin Texas over the course of 18 months. This data set displays incident number, crime type, date, time and location within the city. The other data file is for moon phases, which contains phase information for New Moon, First Quarter, Full Moon, and Last Quarter and the dates on which the phases occurred over the course of 2014-2016.
Moon and Crime Comparison Database
The above files were exported from a csv into a database using Python. The below figure represents the ER diagram used for this project's database.
Files Extracted from SQL Queries
I made SQL queries to the database for overall count of crimes for each moon phase, as well each individual moon phase period. I exported the query results from MySQL into a csv file using a python script. The data from this file was used for later visualization.
For visualization, I imported 2 csvs into Tableau that were created in my Data Export process. The below visualizations demonstrate Austin’s crime in relation to both the moon phase’s period (i.e., date range between the fullest expression of a phase and the start of the subsequent phase - approximately 6.5 days) and the exact date of the moon phase’s fullest expression (e.g., date of a full moon). To examine crime by both the moon phase period (Viz 1-3) and exact expression date (Viz 4-6), I created 3 visualizations that display the same criteria for purposes of comparison.
These visualizations illustrate crime in relation to moon phases over the course of April 2014 to November 2015. Entries in the original dataset between April 2014 to December 2015 are limited, which explains the sharp increase in crime starting in January 2015.
In viewing Austin’s crime by moon phase period, the highest incidents of crime occur when the moon is in its First Quarter period (33,554). However, the Full Moon period also hosts a similar number of crimes (33,524), which can be observed in Viz 3. A potentially interesting pattern can be observed in Viz 2, in which more crimes occur in the Full Moon periods at the beginning of 2015 and decrease halfway through the year, while crimes in the New Moon periods are low at the beginning of 2015, then increase halfway through the year. Extending the year range of the moon and crime data would be necessary in order to draw any clearer correlation.