Yesterday marked the start of Dashboard Week for the Brisbane cohort. We kicked things off with data pertaining to crimes reported to the Los Angeles Police Department from 2020 to 2023. The premise of Dashboard Week is to create a unique dashboard every day, emphasising the importance of time boxing—a lesson I would come to appreciate as the day progressed. Let’s delve into the day’s activities.

Step 1: Beggining

My initial task was to familiarise myself with the various data fields. This involved a combination of consulting Chat GPT and conducting online research. Having acquired an understanding of the data, I pondered over the nature of the dashboard I wanted to create and who would be the target audience. I decided that I aimed to create a dashboard for the LAPD, enabling them to identify areas requiring more focused policing. Moving onto the next phase of the project, let’s talk about data transformation.

Step 2: Alteryx.

One field that particularly intrigued me was the MO_Codes field. This field lists all the MOs associated with a given crime. I stumbled upon a PDF document mapping the MO Codes to their respective descriptions. My plan was to join this mapping with the original data, leading us to the next step: transforming the data set for analysis.

A problem I identified almost immediately was the presentation of multiple MOs in a single cell in the MO Codes field. This made joining and analysis rather challenging. Two potential solutions to this issue emerged: separate each MO into different rows or separate them into different columns. I chose the latter approach, opting to separate columns instead of creating duplicate rows, which would complicate analysis in Tableau.In my first iteration of the Alteryx workflow, I separated all the MOs into distinct columns, resulting in ten columns. I then joined each column separately using the MO mapping table I had created from the PDF, and subsequently re-joined each column to the main table. Unfortunately, I encountered a problem where certain MOs didn’t join and showed a null value. Upon investigation, I discovered that not all MO codes in the data set were present in the mapping table. As a workaround, I created a list of all the MOs present in the table so that any MO not in the mapping table could remain as just the code instead of the description. Despite its complexity, this approach proved effective.

Alteryx Workflow

While I am sure this process could be streamlined and made more dynamic using macros, I chose the simpler method, considering the time constraints of the challenge. With this step completed, I imported the data into Tableau to begin my analysis.

Step 3: Exploration and New Ideas

I generated numerous charts in Tableau, seeking interesting insights from the data. I hit upon the concept of using the percentage of solved crimes—referred to as the ‘solve rate’—as the primary metric in my dashboard. Things seemed to be progressing smoothly until an idea struck me during lunch: why not pivot the intended audience of the dashboard from the police department to the criminals themselves? In this light, the dashboard would serve as a tool for potential criminals to devise the ‘perfect crime’ and avoid capture.

This shift in audience necessitated modifications to some of my previous analyses. However, it didn’t pose a significant challenge as the core focus of the dashboard—the solve rate—remained unchanged. During the dashboard creation process, I encountered another issue: the data contained too many types of crimes and locations. I had to engage with Chat GPT once again to assist in the process of automating the categorisation process. Once this hurdle was overcome, I smoothly sailed towards the completion of my dashboard. Upon reflection, I recognised the moral ambiguity surrounding this project, but it was too late to backtrack.Tableau Dashboard

Samuel Goodman
Author: Samuel Goodman