This is the analysis behind the TrendCT.org story: Where are the most vehicles towed in Hartford?

Looking at 1.5 years of towing data compiled by Matt Zagaja from Hartford’s Open Data portal.

For other analyses, check out our Trend CT data projects repo.

library(dplyr)
library(lubridate)
library(leaflet)
library(ggmap)
library(knitr)
library(stringr)
library(geosphere)
library(ggplot2)
require(ggmap)
require(scales)
library(sp)
require(rgdal)
require(maptools)

#install.packages("devtools")
#devtools::install_github("hrecht/censusapi")
library("censusapi")
# Bring in the data
tows <- read.csv("data/tows.csv", stringsAsFactors=F)

Cleaning and prepping the data

## There are many blank fields in the Tow Firm and Address column
## There are no blank fields in the phone number column
## So let's figure out tow firm name and address based on phone number

### Subset dataframe of complete data
tows_sub <- subset(tows, Tow_Firm!="")
tows_sub <- subset(tows_sub, !duplicated(Tow_Firm))

# Getting rid of one of the tow firm names who used a phone number but two different names
tows_sub <- subset(tows_sub, Tow_Firm!="CROSS COUNTRY AUTO")


#### Geolocate tow firms
geo <- geocode(location = tows_sub$Tow_Firm_Address, output="latlon", source="google")
tows_sub <- cbind(tows_sub, geo)

#### Delete Tow_Firm and Tow_Firm_Address columns in original dataframe
tows <- tows[,-3]
tows <- tows[,-3]

#### Prep dataframe for joining
tows_sub <- tows_sub[c("Tow_Firm", "Tow_Firm_Address", "Tow_Firm_Phone", "lon", "lat")]

#### Join the dataframes
tows <- left_join(tows, tows_sub)

#### Clean up time and dates
tows$Date <- ymd(tows$Date)
tows$Time <- hms(tows$Time)
tows$created_at <- ymd_hms(tows$created_at)
tows$created_at <- ymd_hms(tows$updated_at)
tows$removed_at <- ymd_hms(tows$removed_at)


#### Prepping Lat/Lon
tows$tow_lon <- gsub(",.*", "", tows$geom)
tows$tow_lat <- gsub(".*,", "", tows$geom)

Exploring the data

About 20682 vehicles have been towed in Hartford since 2015.

There are 21 towing companies in the city.

3455 vehicles had no license plate listed.

Where are the vehicles towed from?

# Creates a circle leaflet, the radius corresponding with the number of tows at that location

address2 <- tows %>%
  group_by(Tow_From_Address, tow_lat, tow_lon) %>%
  summarise(count=n()) %>%
  arrange(-count)

# Creating a column plugging in histograms of tows by hours by address (in the hours folder)
# The histograms were created with the histograms.R script and uploaded to our server

address2$png <- gsub(" ", "", address2$Tow_From_Address)
address2$png <- paste0("<img src='http://projects.ctmirror.org/content/trend/2016/08/towed/hours/", address2$png, ".png' width='250px'></img>")
address2$pop <- ifelse(address2$count < 5, paste0(address2$Tow_From_Address, "<br /><strong>Tows: </strong>", address2$count), paste0("<strong>Tows: </strong>", address2$count, "<br />", address2$png))

leaflet(address2) %>% addTiles('http://a.tiles.mapbox.com/v3/borzechowski.gcj2gonc/{z}/{x}/{y}.png', attribution='<a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a>') %>% 
  setView(-72.690940, 41.751426, zoom = 13) %>% 
  addCircles(~tow_lon, ~tow_lat, popup=address2$pop, weight = 3, radius=address2$count*1.5, 
             color="#ffa500", stroke = TRUE, fillOpacity = 0.2) %>% 
  addLegend("bottomright", colors= "#ffa500", labels="Towed'", title="In Hartford")