data_cleaning.rmd

---
title: "Data Cleaning"
author: "Jason Hammett"
date: "November 22, 2017"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)

#Install function courtesy of CSCI 349
my.install <- function(pkg){
  if (!(pkg %in% installed.packages()[,1])){
    install.packages(pkg, repos = "https://cran.revolutionanalytics.com/")
  }
  return (require(pkg, character.only=TRUE))
}
#Knitr
my.install("knitr")
#Decision Tree Libs
my.install("rpart")
my.install("rpart.plot")
#Plotting Libs
my.install("ggplot2")
my.install("ggmap")
my.install("leaflet")

#colorblind settings
# The palette with grey:
cbPalette <- c("#999999", "#E69F00", "#56B4E9", "#009E73", "#F0E442", "#0072B2", "#D55E00", "#CC79A7")

# The palette with black:
cbbPalette <- c("#000000", "#E69F00", "#56B4E9", "#009E73", "#F0E442", "#0072B2", "#D55E00", "#CC79A7")

# To use for fills, add
#scale_fill_manual(values=cbPalette)

# To use for line and point colors, add
#scale_colour_manual(values=cbPalette)
```

## Baltimore Crime Analysis using Open Source Intelligence
### Initial Exploration

We have the following datasets: calls, arrests, and victims.
Let's begin by loading the the "Victims" dataset. Calls is a very large file, and arrests has pretty messy data, so we'll do a separate rmd of EDA on those.
"Victims" is a dataset of victim-based crime. These are physical crimes such as robbery, homicide, or arson. 

```{r importing data}
victims <- read.csv("Data/BPD_Part_1_Victim_Based_Crime_Data.csv")
```

Let's look at the structure of the file.

```{r structure}
str(victims)
```

Now a summary.

```{r summary}
summary(victims)
```

I know that I am going to have to be replacing many factors in the cleaning process, so I'll make a function that does it for me.

```{r replace function}
#replaceWith
#Params: vec - vector housing the data (i.e. a column from a dataframe), original, replacement
#Returns a vector
replaceWith <- function(vec, original, replacement, asFactor = TRUE){
  if (asFactor){
    vec <- as.character(vec)
  }
  vec[which(vec == original)] <- replacement
  if (asFactor){
    vec <- factor(vec)
  }
  vec
}

#Blanks to NAs, automatically convert "" to NA
#Param: vec -  vector, i.e. a column of a dataframe
blanksToNA <- function(vec){
  vec[which(vec == "")] <- NA
  vec
}
```

We've learned that this is a victim based crime dataset.
Firstly, here are the factors that make up the victims dataset.
* Crime Date - MM/DD/YYYY
* CrimeTime - HH:mm:ss
* CrimeCode
* Location
* Description
* Inside.Outside - can be transformed to a logical
* Weapon
* Post
* District
* Neighborhood
* Longitude
* Latitude
* Location.1 - A character verion of the latitude longitude pair
* Premise

Now time to clean the data.

####Inside or Outside
First, I will uniformly make Inside.Outside either "Inside" or "Outsside"

```{r inside-outside}
insideOutsideVector <- victims$Inside.Outside
#Convert single letter to full name
insideOutsideVector <- replaceWith(insideOutsideVector, "O", "Outside")
insideOutsideVector <- replaceWith(insideOutsideVector, "I", "Inside")
#Change blank's to NAs
insideOutsideVector <- blanksToNA(vec = insideOutsideVector)
#Remove unnecessary levels
insideOutsideVector <- factor(insideOutsideVector)
#Add it back into the data.frame
victims$Inside.Outside <- insideOutsideVector
```

####Premise

Looking at the levels of the Premise attribute shows inconsistency with the data.
```{r premise}
levels(victims$Premise)
```

The inconsistency is beyond simply capitalization. Phrases are cut off. There are not a uniform descriptors for similar types of premise.
For instance, there is "BAR" as well as "TAVERN/NIG". I am going to presume this is "NIGHTCLUB" cut off.
There's "Church" and "RELIGOUS", so I'll need to manually edit these.

```{r premises}
premises <- victims$Premise
#First, change blanks to NAs
premises <- blanksToNA(premises)
#Now, I'll capitalize every element and reform the levels
premises <- toupper(premises)

#Recalculate the levels
premises <- factor(premises)
#Place it back in dataframe
victims$Premise <- premises
#Clean up
rm(premises)
```

To facilitate more data, I'll create a new variable with cleaner premises and specify the old one PremiseDetailed. I'll also create a column called PremiseCategory that is RESIDENTIAL, BUSINESS, GOVERNMENT, INDUSTRIAL, PARK, TRANSPORT, PUBLICSERVICE, NIGHTLIFE, STREET, VACANT PROPERTY, RECREATION
```{r premises detail}
victims$PremiseDetailed <- victims$Premise
victims$PremiseCategory <- victims$Premise

#It'll be usefull to compare the summary of premise before and after we make changes
premise <- victims$Premise
category <- as.character(premise)
summary(premise)

#I'll begin by combing food levels into simply restaurant
premise <- replaceWith(premise, "FAST FOOD","RESTAURANT")
premise <- replaceWith(premise, "CARRY OUT", "RESTAURANT")
premise <- replaceWith(premise, "CHAIN FOOD", "RESTAURANT")
premise <- replaceWith(premise, "PIZZA/OTHE", "RESTAURANT")
premise <- replaceWith(premise, "BAKERY", "RESTAURANT")

#Nightlife
premise <- replaceWith(vec = premise, original = "TAVERN/NIG", replacement = "BAR")

#I'll move on to residential
premise <- replaceWith(premise, "APT/CONDO", "APARTMENT")
premise <- replaceWith(premise, "APT. LOCKE", "APARTMENT")

#Houses, this is more complicated
premise <- replaceWith(premise, "ROW/TOWNHO", "HOME")
premise <- replaceWith(premise, "SINGLE HOU", "HOME")
premise <- replaceWith(premise, "DWELLING", "HOME")
premise <- replaceWith(premise, "PORCH/DECK", "HOME")
premise <- replaceWith(premise, "YARD", "HOME")
premise <- replaceWith(premise, "PUBLIC HOU", "PUBLIC HOUSING")
premise <- replaceWith(premise, "MOBILE HOM", "HOME")
premise <- replaceWith(premise, "OTHER/RESI", "OTHER RESIDENTIAL")

#An important element of crime, vacant property
premise <- replaceWith(premise, "VACANT LOT", "VACANT PROPERTY")
premise <- replaceWith(premise, "VACANT BUI", "VACANT PROPERTY")
premise <- replaceWith(premise, "VACANT DWE", "VACANT PROPERTY")

#Schools
premise <- replaceWith(premise, "PUBLIC SCH", "SCHOOL")
premise <- replaceWith(premise, "PRIVATE SC", "SCHOOL")
premise <- replaceWith(premise, "SCHOOL PLA", "SCHOOL")

#Industrial
premise <- replaceWith(premise, "MANUFACTUR", "MANUFACTURING")
premise <- replaceWith(premise, "UTILITIES-", "UTILITIES")
premise <- replaceWith(premise, "SHED/GARAG", "SHED/GARAGE")

#Transport
premise <- replaceWith(premise, "PARKING LO", "PARKING LOT")
premise <- replaceWith(premise, "BRIDGE-PIE", "BRIDGE-PIER")
premise <- replaceWith(premise, "TRUCKING &", "TRUCKING")
premise <- replaceWith(premise, "TRACTOR TR", "TRACTOR TRAILER")

#Health
premise <- replaceWith(premise, "HOSP/NURS.", "HOSPITAL")
premise <- replaceWith(premise, "DAY CARE F", "DAY CARE")

#Other businesses
premise <- replaceWith(premise, "BANK/FINAN", "BANK")
premise <- replaceWith(premise, "LIQUOR STO", "LIQUOR STORE")
premise <- replaceWith(premise, "CONVENIENC", "CONVENIENCE STORE")
premise <- replaceWith(premise, "OFFICE BUI", "OFFICE BUILDING")
premise <- replaceWith(premise, "GROCERY/CO", "GROCERY")
premise <- replaceWith(premise, "DOCTORS OF", "DOCTORS OFFICE")
premise <- replaceWith(premise, "HOTEL/MOTE", "HOTEL")
premise <- replaceWith(premise, "JEWELRY ST", "JEWELRY STORE")
premise <- replaceWith(premise, "PHOTO STUD", "PHOTO STUDIO")
premise <- replaceWith(premise, "GAS STATIO", "GAS STATION")
premise <- replaceWith(premise, "OTHERS\ - IN", "OTHER INDOORS")
premise <- replaceWith(premise, "SHOPPING M", "SHOPPING MALL")
premise <- replaceWith(premise, "MINI STORA", "MINI STORAGE")
premise <- replaceWith(premise, "RETAIL/SMA", "RETAIL")
premise <- replaceWith(premise, "CAR REPAI", "CAR REPAIR")
premise <- replaceWith(premise, "SALESMAN/C", "SALESMAN")
#premise <- replaceWith(premise, "RENTAL/VID", "") #Don't know enough to answer this, rental/video store?
premise <- replaceWith(premise, "BUS. PARK", "BUSINESS PARK")
premise <- replaceWith(premise, "WHOLESALE/", "WHOLESALE")
premise <- replaceWith(premise, "LAUNDRY/CL", "LAUNDRY")
premise <- replaceWith(premise, "BARBER/BEA", "SALON")

#Outdoors
premise <- replaceWith(premise, "INNER HARB", "INNER HARBOR")
premise <- replaceWith(premise, "OTHER - OU", "OTHER OUTDOORS")
premise <- replaceWith(premise, "PUBLIC ARE", "PUBLIC AREA")
premise <- replaceWith(premise, "PUBLIC BUI", "PUBLIC BUILDING")
premise <- replaceWith(premise, "CAR LOT-NE", "CAR LOT")

#Government Entities
premise <- replaceWith(premise, "POLICE DEP", "POLICE")
premise <- replaceWith(premise, "COURT HOUS", "COURT")
premise <- replaceWith(premise, "FIRE DEPAR", "FIRE")
premise <- replaceWith(premise, "PENITENTIA", "PENITENTIARY")

#Construction
premise <- replaceWith(premise, "CONSTRUCTIO", "CONSTRUCTION")
premise <- replaceWith(premise, "CONSTRUCTI", "CONSTRUCTION")
# premise <- replaceWith(premise, "BLDG UNDER", "CONSTRUCTION") # Not sure what BLDG UNDER means, could be construction 
```

Creating a more general premise factor that can be easier to generalize

```{r premise category}
#Create the category, more general
category <- as.character(premise)
#Residential
category <- replaceWith(category, "APARTMENT", "RESIDENTIAL", asFactor = FALSE)
category <- replaceWith(category, "HOME", "RESIDENTIAL", asFactor = FALSE)
category <- replaceWith(category, "PUBLIC HOUSING", "RESIDENTIAL", asFactor = FALSE)
category <- replaceWith(category, "OTHER RESIDENTIAL", "RESIDENTIAL", asFactor = FALSE)

#Nightlife
category <- replaceWith(category, "BAR", "NIGHTLIFE", asFactor = FALSE)

#Business categories
category <- replaceWith(category, "RESTAURANT", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "SCHOOL", "SCHOOL", asFactor = FALSE)
category <- replaceWith(category, "BANK", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "LIQUOR STORE", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "DRUG STORE", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "JEWELRY STORE", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "PAWN SHOP", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "CONVENIENCE STORE", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "GAS STATION", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "DOCTORS OFFICE", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "HOTEL", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "OFFICE BUILDING", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "BANK", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "GROCERY", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "PHOTO STUDIO", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "SHOPPING MALL", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "CARRY OUT", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "FINANCE/LO", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "WHOLESALE", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "SALON", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "LAUNDRY", "BUSINESS", asFactor = FALSE)
category <- replaceWith(category, "SALON", "SALESMAN", asFactor = FALSE)

#Abandoned Property
categpry <- replaceWith(category, "VACANT PROPERTY", "VACANT", asFactor = FALSE)

#Recreation
category <- replaceWith(category, "PARK", "RECREATION", asFactor = FALSE)
category <- replaceWith(category, "PLAYGROUND", "RECREATION", asFactor = FALSE)
category <- replaceWith(category, "RACE TRACK", "RECREATION", asFactor = FALSE)

#Industrial
category <- replaceWith(category, "MANUFACTURING", "INDUSTRIAL", asFactor = FALSE)
category <- replaceWith(category, "WAREHOUSE", "INDUSTRIAL", asFactor = FALSE)
category <- replaceWith(category, "UTILITIES", "INDUSTRIAL", asFactor = FALSE)

#Transportation
category <- replaceWith(category, "CAB", "TRANSPORTATION", asFactor = FALSE)
category <- replaceWith(category, "BOAT/SHIP", "TRANSPORTATION", asFactor = FALSE)
category <- replaceWith(category, "BUS/AUTO", "TRANSPORTATION", asFactor = FALSE)
category <- replaceWith(category, "LIGHT RAIL", "TRANSPORTATION", asFactor = FALSE)
category <- replaceWith(category, "TRACTOR TRAILER", "TRANSPORTATION", asFactor = FALSE)

#Add PremiseCategory Back into the dataframe, removing unused levels
victims$PremiseCategory <- factor(category)

#Add PremiseDetailed back into the dataframe, removing unused levels
victims$PremiseDetailed <- factor(premise)
```

####Neighborhoods

Neighborhoods are a much more consistent vector.

```{r neighborhoods}
#Replace blanks with NAs
neighborhoods <- victims$Neighborhood
neighborhoods <- blanksToNA(neighborhoods)
neighborhoods <- factor(neighborhoods)
victims$Neighborhood <- neighborhoods
#Clean up
rm(neighborhoods)
```

####Location 

Location has many, many levels. Note that the column Location.1 is the lat lon pair

```{r locations}
location <- victims$Location
#Convert blanks to NA
location <- blanksToNA(location)
#Reset levels
location <- factor(location)
#Reassign
victims$Location <- location
#Clean up
rm(location)
```

Location.1 is the lat/lon pair. I'll clean this and rename it latLon.

```{r latlon}
latLon <- victims$Location.1
#Change blanks to NAs
latLon <- blanksToNA(latLon)
victims$LatLon <- factor(latLon)
victims$Location.1 <- NULL
#Clean up
rm(latLon)
```

#### Weapons

```{r}
#Grab em
weapon <- victims$Weapon
#Replace blank with NA
weapon <- blanksToNA(weapon)
#Reassign levels
weapon <- factor(weapon)
#Pop back into data frame
victims$Weapon <- weapon
```

Now it's time for a more complicated task: date.
There are two factor variables representing date and time. I'll combine them into one Date object
```{r}
#Append Time to Date
victims$CrimeDateTime <- paste(victims$CrimeDate, victims$CrimeTime)
#Let's look at it
str(victims$CrimeDateTime)
#Now let's convert these to date objects
victims$CrimeDateTime <- as.POSIXct(victims$CrimeDateTime, format = "%m/%d/%Y %H:%M:%S")
#Let's look at the new version
str(victims$CrimeDateTime)
#Let's wipe the uneeded Date and Time factors
victims$CrimeDate <- NULL
victims$CrimeTime <- NULL
```

Finally, let's save the workplace image so we can pick up from here.

```{r saving image}
save.image(file = "cleanedData.RData")
```