DataWranglingProject
Karan Ghadge
April 6, 2019
Introduction
Before the riot of 2001, Cincinnati’s overall crime rate was dropping dramatically and had reached its lowest point since 1992. After the riot violent crime increased. Reflecting national trends, crime rates in Cincinnati have dropped in all categories from 2006 to 2010.
In 2011, using FBI data, the CQ Press ranked Cincinnati the 16th most dangerous city in the United States.However, the FBI web site recommends against using its data for rankings, since there are many factors that influence crime rates. Also, the CQ Press did not include Chicago, Illinois in its ranking.[6]
According to the Hamilton County Prosecutor, the vast majority of people being murdered are inside the city and are African American. In 2009, 44 black men and 11 black women were murdered, but no whites.[8] According to the Prosecutor, people almost always commit murders inside their racial classifications, and there is a subset in the underclass of Cincinnati which “commit a lot of violent crime and tend to be black,”[8] similar to national trends.
We have analysed the data to understand how crime rates have changed by time and location.
The final conclusions of the analysis will answer:
- The number of crimes
- Violence of offense
- Weapons Used
- Days of the week
- Most dangerous locations
- Gender which is targetted the most
- Age bracket
We will be leveraging this information using various libraries in R and answer the question: Is Cincinnati a safe place as it was earlier?
Packages Required
The following packages are used :
library(tidyr)
library(dplyr)
library(tidyverse)
library(knitr)Data Cleaning and Preparation
The link to the data source is here
Import the data.
#Import CSV file
crime_data <- read.csv('crimedata.csv')To prepare the data, I first removed the unnecessary columns in the dataset.
##Removing uneccesary rows
crime_data <- crime_data[,-c(13,14,15) ]Next, formatted the dates and times of crime occurence and reporting.
##Date Reported
Date_Rpt <- substr(crime_data$DATE_REPORTED, 1, 22)
Date_Rpt <- (strptime(Date_Rpt, '%m/%d/%Y %I:%M:%S %p'))
Time_of_rpt <- substr(crime_data$DATE_REPORTED, 12, 22)
Date_Rpt <- substr(crime_data$DATE_REPORTED, 1, 10)
crime_data$DATE_REPORTED <- Date_Rpt
crime_data <- cbind(crime_data, Time_of_rpt)
##Date From and To of Occurence
From_Date <- substr(crime_data$DATE_FROM, 1, 22)
From_Date <- (strptime(From_Date, '%m/%d/%Y %I:%M:%S %p'))
From_Time <- substr(crime_data$DATE_FROM, 12, 22)
From_Date <- substr(crime_data$DATE_FROM, 1, 10)
crime_data$DATE_FROM <- From_Date
crime_data <- cbind(crime_data, From_Time)
To_Date <- substr(crime_data$DATE_TO, 1, 22)
To_Date <- (strptime(To_Date, '%m/%d/%Y %I:%M:%S %p'))
To_Time <- substr(crime_data$DATE_TO, 12, 22)
Date_To <- substr(crime_data$DATE_TO, 1, 10)
crime_data$DATE_TO <- Date_To
crime_data <- cbind(crime_data, To_Time)Classifying two variables:
UCR_Group and Hate_Bias
crime_data <- crime_data %>%
mutate(UCR_GROUP = case_when(
UCR_GROUP == "PART 2 MINOR" ~ "Part 2 Crime",
UCR_GROUP != "PART 2 MINOR" ~ "Part 1 Crime",
TRUE ~ "others"))
crime_data <- crime_data %>%
mutate(HATE_BIAS = case_when(
HATE_BIAS == "N--NO BIAS/NOT APPLICABLE" ~ "Not Biased",
HATE_BIAS != "N--NO BIAS/NOT APPLICABLE" ~ "Biased",
TRUE ~ "others"))crime_data_clean <- select(crime_data, INSTANCEID, DATE_REPORTED, DATE_FROM, From_Time, DATE_TO, To_Time, OFFENSE, HATE_BIAS, DAYOFWEEK, CPD_NEIGHBORHOOD, LONGITUDE_X, LATITUDE_X, VICTIM_AGE, VICTIM_GENDER,WEAPONS, UCR_GROUP)
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(INSTANCEID == "" | is.na(INSTANCEID)), ])
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(CPD_NEIGHBORHOOD == "" | is.na(CPD_NEIGHBORHOOD)), ])
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(OFFENSE == "" | is.na(OFFENSE)), ])
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(VICTIM_AGE == "" | is.na(VICTIM_AGE)), ])
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(DAYOFWEEK == "" | is.na(DAYOFWEEK)), ])
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(VICTIM_AGE == "UNKNOWN" | is.na(VICTIM_AGE)), ])
crime_data_clean <- with(crime_data_clean, crime_data_clean[!(VICTIM_GENDER == "" | is.na(VICTIM_GENDER)), ])
crime_data_clean$WEAPONS <- gsub(".*11.*", "FIREARM", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*12.*", "HANDGUN", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*13.*", "RIFLE", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*14.*", "SHOTGUN", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*15.*", "FIREARM", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*16.*", "FIREARM", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*17.*", "FIREARM", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*18.*", "BB AND PELLET GUNS", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*20.*", "KNIFE/CUTTING INSTRUMENT", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*30.*", "BLUNT OBJECT", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*35.*", "MOTOR VEHICLE", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*40.*", "PERSONAL WEAPON", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*60.*", "EXPLOSIVES", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*70.*", "DRUGS", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*80.*", "OTHER WEAPONS", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*U.*", "UNKNOWN", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*65.*", "FIRE/INCENDIARY DEVICE", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*50.*", "POISON", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*99.*", "NONE", (crime_data_clean$WEAPONS), perl = FALSE)
crime_data_clean$WEAPONS <- gsub(".*85.*", "ASPHYXIATION", (crime_data_clean$WEAPONS), perl = FALSE)Summary of the Data
summary(crime_data_clean)## INSTANCEID DATE_REPORTED
## 456C6412-EC72-4F59-B1B4-5980631026AD: 84 Length:292980
## 3EFCADA0-F622-403C-9B64-50E23EC5F00F: 65 Class :character
## 46504773-3D1B-48A2-96F6-74BD8EBE5F73: 64 Mode :character
## DFADAD19-5AA8-461A-BD87-C6BAFE62F255: 56
## 45668FA5-333E-44DC-B12A-C2867983670D: 50
## 647C25B8-6325-4C14-B52E-25C314D02B5E: 50
## (Other) :292611
## DATE_FROM From_Time DATE_TO
## Length:292980 12:00:00 PM: 9066 Length:292980
## Class :character 12:00:00 AM: 7354 Class :character
## Mode :character 06:00:00 PM: 7158 Mode :character
## 10:00:00 PM: 7068
## 09:00:00 PM: 6930
## 11:00:00 PM: 6691
## (Other) :248713
## To_Time OFFENSE
## 12:00:00 PM: 5564 THEFT :87090
## 08:00:00 AM: 4974 ASSAULT :37485
## 09:00:00 AM: 4536 CRIMINAL DAMAGING/ENDANGERING:36333
## 04:00:00 PM: 4325 BURGLARY :28150
## 05:00:00 PM: 4213 AGGRAVATED ROBBERY :16410
## 03:00:00 PM: 4203 DOMESTIC VIOLENCE :15343
## (Other) :265165 (Other) :72169
## HATE_BIAS DAYOFWEEK CPD_NEIGHBORHOOD
## Length:292980 SATURDAY :43309 WESTWOOD : 26582
## Class :character SUNDAY :42990 WEST PRICE HILL: 20154
## Mode :character FRIDAY :42658 EAST PRICE HILL: 18024
## MONDAY :41404 AVONDALE : 14509
## WEDNESDAY:41087 OVER-THE-RHINE : 13919
## TUESDAY :40975 WALNUT HILLS : 11455
## (Other) :40557 (Other) :188337
## LONGITUDE_X LATITUDE_X VICTIM_AGE
## Min. :-84.82 Min. :39.05 18-25 :71354
## 1st Qu.:-84.57 1st Qu.:39.12 31-40 :57700
## Median :-84.52 Median :39.14 41-50 :42904
## Mean :-84.52 Mean :39.14 26-30 :41143
## 3rd Qu.:-84.49 3rd Qu.:39.16 51-60 :35474
## Max. :-84.26 Max. :39.36 UNDER 18:17568
## NA's :39957 NA's :39957 (Other) :26837
## VICTIM_GENDER WEAPONS UCR_GROUP
## : 0 Length:292980 Length:292980
## F - FEMALE : 25 Class :character Class :character
## FEMALE :160947 Mode :character Mode :character
## M - MALE : 18
## MALE :131874
## NON-PERSON (BUSINESS: 29
## UNKNOWN : 87The Final Dataset
kable(crime_data_clean[1:10,], caption = "Preview - Clean Data")| INSTANCEID | DATE_REPORTED | DATE_FROM | From_Time | DATE_TO | To_Time | OFFENSE | HATE_BIAS | DAYOFWEEK | CPD_NEIGHBORHOOD | LONGITUDE_X | LATITUDE_X | VICTIM_AGE | VICTIM_GENDER | WEAPONS | UCR_GROUP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | D6D7D173-E416-4571-AF34-A767ACF810D9 | 03/16/2015 | 03/16/2015 | 03:02:00 PM | 03/16/2015 | 03:05:00 PM | AGGRAVATED MENACING | Not Biased | MONDAY | WALNUT HILLS | -84.49080 | 39.11996 | 26-30 | FEMALE | NONE | Part 2 Crime |
| 2 | 2A569674-9603-4ED9-82BB-783B843B94C9 | 06/23/2011 | 06/23/2011 | 10:20:00 AM | 06/23/2011 | 10:30:00 AM | ASSAULT | Not Biased | THURSDAY | AVONDALE | -84.49155 | 39.14167 | 41-50 | MALE | PERSONAL WEAPON | Part 2 Crime |
| 4 | 5B95E5DC-E35D-4F86-B77C-02F25C950329 | 06/25/2012 | 06/25/2012 | 03:35:00 PM | 06/25/2012 | 03:40:00 PM | FELONIOUS ASSAULT | Not Biased | MONDAY | COLLEGE HILL | -84.54596 | 39.20102 | 18-25 | MALE | FIREARM | Part 1 Crime |
| 5 | B908DB49-C51D-442B-ADFD-B6E64696071B | 03/13/2018 | 03/13/2018 | 04:45:00 PM | 03/13/2018 | 05:12:00 PM | ASSAULT | Not Biased | TUESDAY | WEST END | -84.52521 | 39.11082 | 41-50 | FEMALE | PERSONAL WEAPON | Part 2 Crime |
| 6 | 66F569F5-4C8E-4848-BD42-D0F8DE4D42B0 | 05/19/2013 | 05/19/2013 | 02:30:00 PM | 05/19/2013 | 02:45:00 PM | AGGRAVATED BURGLARY | Not Biased | SUNDAY | MADISONVILLE | -84.39035 | 39.15479 | 18-25 | FEMALE | FIREARM | Part 1 Crime |
| 7 | 3B4450F2-F94B-4BFB-8B0A-3FF04DBD73C4 | 07/31/2015 | 07/24/2015 | 12:00:00 AM | 07/24/2015 | 11:59:00 PM | ENDANGERING CHILDREN | Not Biased | FRIDAY | EVANSTON | -84.47835 | 39.13468 | UNDER 18 | MALE | UNKNOWN | Part 2 Crime |
| 8 | B095D1B2-9089-4CA4-85B2-A2657FB82793 | 09/05/2012 | 09/05/2012 | 01:22:00 AM | 09/05/2012 | 01:23:00 AM | ROBBERY | Not Biased | WEDNESDAY | OVER-THE-RHINE | -84.51276 | 39.11108 | 31-40 | MALE | NONE | Part 1 Crime |
| 9 | FD5706CD-7E2F-4CED-8C73-2E3AB142EF8C | 09/03/2015 | 09/03/2015 | 01:10:00 AM | 09/03/2015 | 01:11:00 AM | ASSAULT | Not Biased | THURSDAY | OVER-THE-RHINE | -84.51640 | 39.11505 | 41-50 | FEMALE | PERSONAL WEAPON | Part 2 Crime |
| 10 | 417FB2EC-1405-4F31-802B-2D3D7C9A47C3 | 09/03/2014 | 08/31/2014 | 06:00:00 PM | 08/31/2014 | 08:30:00 PM | THEFT | Not Biased | SUNDAY | MOUNT AUBURN | -84.51008 | 39.11733 | 18-25 | MALE | NONE | Part 1 Crime |
| 11 | A6B8356E-5A44-425A-8076-80BA963D09AC | 09/28/2010 | 09/25/2010 | 02:30:00 PM | 09/25/2010 | 03:00:00 PM | ASSAULT | Not Biased | SATURDAY | WALNUT HILLS | -84.49454 | 39.12494 | 31-40 | MALE | PERSONAL WEAPON | Part 2 Crime |
Proposed Exploratory Analysis
- Plan to use ggplot for analysis of the data, and visualize further using maps and box plots to uncover the crime rate pertaining to the various factors that would compromise to work for the further investigation of the crimes
- Further wish to use logistic regression and calculate the p value of the variable and use cut off values to identify the false positives and false negative
- I intend to divide the data further by slicing it into parts to meanly the identify the most common committed crime and analyze the major factor that helps investigation