About the project

This project is a part of the data analytics challenge that held by FP20 Analytics and ZoomCharts. The challenge was asking to view crime patterns in London based on 2021 - 2023 data.

The Dataset

The dataset contains 5 tables:

1. Crimes: contains information about the details of crime including place (in the form of Longitude and Latitude), crime type, crime committing time, and resolve status.
2. Crime Person: contains information about the person who are got involved in the crime incident. Each crime elaborates how many people involved and the roles they played, such as victim, perpetrator, etc.
3. People: contains a list of people who are got involved in the crime incident. It contains information about the date of birth, gender, and ethnicity.
4. Crime Roles: contains information about the crime role, such as victim, witness, accomplice, informant, and offender.
5. Crime Types: contains information about the crime types, including (a) anti-social behavior; (b) bicycle theft; (c) burglary; (d) criminal damage and arson; (e) drugs and illegal substances; (f) possession of weapons; (g) public order; (h) robbery and mugging; (i) shoplifting; (j) theft from person; (k) vehicle crime; (l) violence and sexual offences.

Approach

To get the most out of data, here are my approach to develop a dashboard:

1. Translated the Longitude and Latitude data into a readable form. I used Reverse Geocoding technique.
2. The information that I need to pull by doing Reverse Geocoding including London Borough, Suburb name, Road name, and kind of place like leisure place, shop, etc.
3. Extracted date of birth into age and created an age group.
4. Extracted crime committing time (date time format) into time format and created a time window group based on the information.
5. Made additional table contains list of borough's population to obtain crime rate of each borough. The information about London population density can be found in here.

Top Findings

Here are my top findings when assessing dataset:

1. Southwark borough has the highest rates of violence and sexual offenses (Current Year Crime Rate: 1.87 per 10,000 people)
2. Southwark borough's crime rate for violence and sexual offenses is 115.43% higher than the overall London crime rate for the same offenses.
3. While Dulwich has achieved a 100% clearance rate for violence and sexual offenses, it's crucial to remain vigilant in the 13 other reported suburbs and address any emerging concerns.
4. In addition to violence and sexual offenses, Southwark has recorded the highest rates for anti-social behavior, vehicle crime, robbery, public order incidents, and criminal damage in London this year.
5. Between 2021 and 2023, the most common age group for perpetrators of violence and sexual offenses in Southwark was 35 to 50.
6. Violence and sexual offenses peak on Mondays and Thursdays, particularly between 00:00 and 03:00, showing the highest crime rates during this time window.