SQL & Tableau Project: Exploring Chicago Crime — “Is Your Neighborhood Safe?” 👮🏻‍♂️

Tableu Dashboard for Chicago Crimes 2018–2023 (Available at: here)

Delve deep into the realm of data as we investigate Chicago crimes between 20018 and 2022. If you’re a resident of Chicago, this analysis will help you gain insights into crime trends in your neighborhood. We’ve harnessed the power of SQL and Tableau to dissect the data and present it in an easily digestible manner.

Methods and Tools

• Data Source: The analysis is based on the Chicago Crimes Dataset, which can be accessed here. This extensive dataset documents crimes in Chicago, US from 2001. However, due to its size (a whopping 1.8 GB), I’ve chosen to focus on the period between 2018 and 2022, which still comprises a significant dataset with about 1.1 million rows.
• Tools: I employed MySQL for data analysis and Tableau to create intuitive dashboards.

Analyses and Results

With such a vast dataset, the questions are endless. Here are a few key findings:

• Q. What is the total crime casese in Chicago(2018–2022)?

SELECT COUNT(id) FROM crime;

1,190,000 cases

• Q. What is the most prevalent type of crime in Chicago?

The entire bar represents all the crimes, and the blue section indicates those where someone was arrested.

SELECT primary_type, COUNT(primary_type) as count FROM crime
GROUP BY primary_type
ORDER BY count desc
LIMIT 10;

Theft emerges as the most frequent crime in Chicago. Delving into the specifics, a substantial proportion of these theft incidents involve relatively small amounts, often less than $500. This suggests a pattern dominated by petty thefts or opportunistic crimes rather than grand-scale heists. Furthermore, retail theft, where goods are stolen from shops or stores, is also a common occurrence. Such patterns can provide valuable insights for both law enforcement agencies and businesses in devising strategies to mitigate these incidents.

• Q. At which times of the day do most crimes occur?

WITH t1 AS (
 SELECT
  DAYNAME(date) as day,
  HOUR(date) as hour,
  COUNT(*) as count
 FROM crime
 GROUP BY day, hour
 ORDER BY day, hour ASC
)
SELECT hour, ROUND(AVG(count),2) as avg_count from t1
GROUP BY hour
ORDER BY avg_count DESC;

Contrary to popular belief that crimes peak during the early hours, our analysis revealed that midday and midnight witness the highest crime rates. The reasons for this trend, however, vary by year, month, and location.

• Q. Which communities fare better in terms of safety?

SELECT 
 community,
 ROUND(AVG(CASE WHEN arrest = 'True' THEN 1 ELSE 0 END) * 100, 2) AS ArrestRate
FROM crime
GROUP BY community
ORDER BY ArrestRate DESC

At a cursory glance, “Edison Park” registers the fewest crimes. However, it’s crucial to understand that a lower crime count doesn’t directly imply safety. For instance, without data on the population or the possibility of repeat offenders, conclusions can be misleading.

One metric we’ve explored is the “Arrest Rate.” While not perfect, it provides a glimpse into law enforcement’s efficacy in a region. A high arrest rate might indicate strong police enforcement or could signal a traditionally high-crime area.

According to our metrics, “West Garfield Park” boasts the highest arrest rate at 36.36%, while “Forest Glen” lags with a meager 5.92%.

• Q. Where do most crimes happen?

SELECT location_description, COUNT(*) AS count FROM crime
GROUP BY location_description
ORDER BY count DESC;

Locations vary, from streets to gas stations. Our data shows that streets, apartments, and residences are the top crime hotspots. Delving deeper, thefts are most common on streets around 8 PM and midnight. In contrast, apartments and residences often report domestic crimes like battery, accounting for nearly 20% of all crimes between 2018 and 2022 in Chicago.

Discussion and Conclusion

The insights shared here are just the tip of the iceberg. For additional questions and insights, please visit my Github page and Tableau Public. Remember, while these patterns provide a general overview, they may not accurately represent every community or timeframe. I encourage you to delve into the data yourself, perhaps focusing on your own neighborhood or areas you frequently visit. Such insights could help you better understand potential threats.

Feel free to explore the dataset and my dashboard. If you discover any intriguing patterns, please share them in the comments! If you found this article helpful, don’t forget to follow my page and give it some claps 👏