Los Angeles Police Department
Diversity, Equity & Inclusion Case Study

For this project, I wanted to get in a little more practice with SQL specifically and try out PowerBI, as I had not worked with this platform before this case study. I used the following data sets, made available with this license, through LA City's data website: 

• Crime Data from 2020 to Present - crimes reported from 2020 to present

• Arrest Data from 2020 to Present - arrests made (bookings & RFCs)

For this project: 

My team lead has assigned me the task of reviewing both data sets, but has asked that I focus on Crimes Reported 2020 to Present, and provide some insights into trends, and any thoughts I have re: DEI recommendations to review with the team before providing to the Chief.

To begin cleaning and analyzing the data, I first reviewed the spreadsheets in a text editor (Notepad++) to get a high-level visual of how the data was structured, how many rows I should anticipate working with, and proceeded to upload the files to BigQuery. After glancing through the documentation, I made some initial observations/questions to guide my cleaning and analysis process. Those questions, queries, and results are below:

Cleaning & Analyzing Arrest Data 2020 to Present

• Are the report_ids unique? Yes, all values are unique.

SELECT DISTINCT Report_ID

FROM `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`

• How many report types are there? 2 types: booking & RFC. 

  • Booking - PD registers and enters charges against a person believed to have violated the law

  • RFC - Release From Custody - officer charges a person and issues an order to appear in court

SELECT DISTINCT Report_Type

FROM `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present

• Total # of bookings vs RFCs?

162323 bookings

SELECT COUNT (Report_Type)

FROM `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`

WHERE Report_Type = 'BOOKING'

40881 RFC's

SELECT COUNT (Report_Type)

FROM `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`

WHERE Report_Type = 'RFC'

• How many unique charge types (descriptions) are there? 27 unique (excluding "null")

SELECT DISTINCT Charge_Group_Description

FROM `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`

WHERE Charge_Group_Description <> 'null'

• How many unique charge codes are there? 3390 unique charge codes found

SELECT DISTINCT Charge

FROM `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`

• Do the DR_NO values from arrest data, and report_ids from crime data correlate? These numbers do not correlate. No results. 

SELECT Report_ID

FROM ((`lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`

INNER JOIN `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_1` 

ON `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`.Report_ID = `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_1`.DR_NO)

INNER JOIN `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_2` 

ON `lapd-data.arrests_and_crime_reports_2020_present.arrests_2020_present`.Report_ID =`lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_2`.DR_NO)

Cleaning & Analyzing Crime Data 2020 to Present

• Recombine tables (had to split original csv file in 2 to upload to BigQuery)

SELECT *

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_1`

UNION ALL

SELECT * 

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_2`

• Are the DR_NO values unique? Yes, these values are unique. I wanted practice with nesting queries, so ran both of the below:

SELECT DISTINCT DR_NO

FROM (

  SELECT DR_NO FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_2`

  UNION ALL

  SELECT DR_NO FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_2020_present_1` )

SELECT DISTINCT DR_NO

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

• How many unique crime descriptions are there? 138 descriptions

SELECT DISTINCT Crm_Cd_Desc

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

• Difference in time between when an incident occurred vs how long after it was reported?

Added a new calculated column reporting_timeframe to find the difference between when an incident occurred vs how long after it was reported:

ALTER TABLE `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

ADD COLUMN `reporting_timeframe` INTEGER;

ALTER TABLE `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

ALTER COLUMN reporting_timeframe SET DATA TYPE NUMERIC;

UPDATE `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

SET reporting_timeframe = date_diff(Date_Rptd, DATE_OCC, DAY)

WHERE TRUE

• Functional analysis of this reporting time frame field

Calculating the MIN

SELECT MIN(reporting_timeframe)

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

Result: 0

Calculating the MAX

SELECT MAX(reporting_timeframe)

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

Result: 1172

Calculating the AVG

SELECT AVG(reporting_timeframe)

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

Result: 9.673277625

Calculating the MODE

Mode = most frequent recurring number

SELECT reporting_timeframe, COUNT(1) AS frequency

FROM `lapd-data.arrests_and_crime_reports_2020_present.crime_combined`

WHERE reporting_timeframe IS NOT NULL

GROUP BY reporting_timeframe

ORDER BY frequency DESC 

Result: 0 day (same day) reporting @ 339905 instances.

~50% of the time it's reported same day, ~21% reported within 1 day

Made note of results, and saved updated table as CSV with the reporting_timeframe column included, for further analysis in PowerBI.

Finally, I reviewed how long on average it takes for F vs M victims to report crimes. Men are more likely to report crimes within 9 days of the crime occurring, while F victims are more likely to report within 12 days. This may speak to the reluctance of F victims to come forward about sexual assault or other abuses, but further research is needed.  Also, because M & F are the only official recognized genders by LAPD, we do not have statistics specifically against trans or non-binary identifying individuals.

I also reviewed how long on average it takes to report each type of crime by race/ethnicity. Some of these stats may be skewed due to the number of reports for each category (i.e., Guamanian victims accounted for less than 1% of all crimes reported, so they have a smaller pool of victims). Focusing on White, Black, and Latine populations specifically, they tend to average in the 9 - 12 day range of reporting for each population group. 

LAPD has done significant work in becoming more transparent with some of its reporting. The data shows us some interesting trends, such as: 

• Women tend to be victims of intimate partner crimes and sexual assault. 

• Black folks tend to have higher victim rates per population in LA, compared to White and Latine folks.

• Stolen Vehicles, Assault/Battery, Vandalism, Identity Theft, and Burglary are the are the most commonly reported crimes in LA.

Most of the trends we saw correlate to state and national statistics, so should not be too surprising. To conclude this analysis, LAPD should shift focus from what the data currently reports, to what it doesn't. The data gives us some information about victims of crimes, but this data could be enhanced by doing the following: 

• • Change Sex to Gender: assigned-at-birth sex is not something officers can judge on the spot, and if it's the only data collected, may exclude or minimize crimes against non-binary or transgender persons. Gender should be self-disclosed by the victim in question, and documented according to the individual.  

  • Allow for 3 Gender options: Man, Woman, Non-binary. There should also be an optional checkbox above the gender question for "Business." Businesses/business owners who are victims of crimes should still have their gender (and race/ethnic) identities documented, but should note if they are reporting on behalf of the business as a whole.

  • Collect data on sexual orientation: We do not have any stats that indicate crimes against members of the LGBTQIA+ community. Could potentially have a checkbox - "Identifies as LGBTQIA+"

  • Change race/ethnicity options: These options should better match the US Census. While LA is a diverse place, it's unbalanced and much harder to reconcile multiple options for some communities, and generalizations of others. Having citizens choose between US Census recognized options (including "more than one race"), will allow LAPD to better identify trends among populations that better correlate to the numbers of those populations in LA.

  • Additional reporting details on officers in Arrests Data: LAPD has faced a lot of scrutiny for lack of transparency and bias amongst its staff. One major part of DEI work is taking a look at the current practices of the force. It would behoove us to add data on the arresting officer (and their race & gender) when it comes to arrests, in particular. There were not many trends to be found in the arrest data (I just happened to take a peak), but adding this kind of information would be a major benefit to the force in cracking down on unconscious bias in the ranks. 

  • Digital reporting: officers should be required to fill out all police reports digitally. One of the notes for all data provided by LAPD is that the data is transcribed from hand-written documentation. There should be very little room for error when reporting things like gender, race, etc., so I suggest allowing officers to take notes manually on the spot if that's easier, but to file all official reports digitally themselves (so they can read their own writing), and limit the choices they can make when it comes to gender and race options.

My recommended next course of action is to make these few adjustments to what data is collected (and how!). These few changes have the potential to make a lasting impact with the LA community. When it comes to DEI practices in policing, initiatives should go far beyond hiring practices and recognizing cultural holidays (both of which are great!), and should include: challenging and dismantling implicit bias and profiling; assessing current police practices and providing data on things like excessive use of force; being transparent with the public about this type of data; and,  changing policies (including policies concerning transparency, culture, training, and practice). How officers view DEI can correlate to their implicit bias while on the job, and how they manage those tough, ethical decisions they face every day. If we start with the data, we can analyze a strong pathway to a culture of DEI at LAPD.

• LAPD Intradepartmental Correspondence Committing to DE&I - 2021 (DEI Initiatives)

• LAPD DE&I Website

• Police Chief Magazine - Finding Equity, Diversity and Inclusion in Policing

• Mayor Garcetti - Executive Directive 27

• US Census Information for Los Angeles

• GitHub - SQL Queries