library(tidyverse)
url <- "https://raw.githubusercontent.com/kailot-harris/DATA-607-Assignment-1/refs/heads/main/Motor_Vehicle_Collisions_-_Crashes_20260828.csv"
df <- read_csv(
file = url,
show_col_types = FALSE,
progress = FALSE
)
#glimpse(df)DATA607 - Assignment 1: Investigating Cyclist Injuries in Motor Vehicle Collisions in New York City - Codebase
Overview and Introduction
The selected data set is sourced from the City of New York’s open data repository. The specific data set is named “Motor Vehicle Collisions - Crashes”, and the complete data set comprises 2.27 million rows and 29 columns. Some variables are numerical (e.g. crash_time, latitude, longitude), while others are categorical (e.g. zip_code, borough, on_street_name). The data set can be accessed via this URL: https://data.cityofnewyork.us/d/h9gi-nx95.
The original data set was restricted within the open data repository to only contain rows for which the variable number_of_cyclist_injured’s numerical value is greater than 0. This step facilitated the successful download of a reasonably sized csv file, and also refined the scope of the data to focus primarily on incidents where at least 1 cyclist was reported injured.
This data set was selected because it has potential relevance to MSDS students and faculty who travel via bicycle within the five boroughs. In addition, the initial filtering step reduced the file size by nearly an order of magnitude: from 2.27 million rows down to slightly over 66 thousand rows. Anticipated challenges in this assignment include accounting for confounding factors, such as weather events, and adequately visualizing the relationships between variables. Difficulties may arise when parsing incomplete data, such as those missing the variable “Vehicle_Type_Code_1”.
The dataset initially appears to contain some data which may not be useful in further analysis. For instance, the variable “Location” is simply a concatenation of two other variables into a vector structure, namely placing the “Latitude” variable and “Longitude” variable into a coordinate pairing. The column “Location” is not independent of the other variables in this set, and can thus be safely removed from the refined set. Additionally, column (variable) names are not in snake_case, or any other friendly format, so one of the initial tasks will be to rename each column according to a convention.
Task 1
Take the data, and create one or more code blocks. You should finish with a data frame that contains a subset of the columns in your selected dataset. If there is an obvious target (aka predictor or independent) variable, you should include this in your set of columns. You should include (or add if necessary) meaningful column names and replace (if necessary) any non-intuitive abbreviations used in the data that you selected. For example, if you had instead been tasked with the UCI mushroom dataset, you would include the target column for edible or poisonous, and transform “e” values to “edible.” Your deliverable is the R code to perform these transformation tasks.
library(janitor)
#clean up the column names (variables)
clean_df <- clean_names(df)
#Remove the location column from data set
df_2 <- clean_df |>
select(crash_date,
crash_time,
borough,
zip_code,
latitude,
longitude,
number_of_cyclist_injured, vehicle_type_code_1,
contributing_factor_vehicle_1)
#print variable names
names(df_2)[1] "crash_date" "crash_time"
[3] "borough" "zip_code"
[5] "latitude" "longitude"
[7] "number_of_cyclist_injured" "vehicle_type_code_1"
[9] "contributing_factor_vehicle_1"#Observe new shape of data set
head(df_2,10)# A tibble: 10 × 9
crash_date crash_time borough zip_code latitude longitude
<chr> <time> <chr> <dbl> <dbl> <dbl>
1 07/19/2014 17:20 BROOKLYN 11230 40.6 -74.0
2 03/07/2022 07:30 QUEENS 11373 40.7 -73.9
3 07/30/2021 23:14 <NA> NA 40.8 -74.0
4 08/29/2021 10:08 <NA> NA 40.8 -73.9
5 11/13/2021 01:00 MANHATTAN 10036 40.8 -74.0
6 05/22/2022 16:05 MANHATTAN 10022 40.8 -74.0
7 10/22/2022 08:55 BROOKLYN 11249 40.7 -74.0
8 11/06/2022 17:30 MANHATTAN 10022 40.8 -74.0
9 09/21/2023 08:01 <NA> NA 40.7 -74.0
10 10/13/2023 20:40 BROOKLYN 11214 40.6 -74.0
# ℹ 3 more variables: number_of_cyclist_injured <dbl>,
# vehicle_type_code_1 <chr>, contributing_factor_vehicle_1 <chr>Exploratory Data Analysis
First, the average location (latitude, longitude) will be calculated.
#Calculate average latitude and longitude for included collisions
df_2 |>
summarize(avg_lat = mean(latitude, na.rm = TRUE),
avg_long = mean(longitude, na.rm = TRUE))# A tibble: 1 × 2
avg_lat avg_long
<dbl> <dbl>
1 40.4 -73.4## mean values are not within NYC.... some data must be input as 0 instead of NA...
## filter only latitudes and longitudes within NYC boundaries
df_2 |>
filter(latitude > 40.49 & latitude < 40.92,
longitude > -74.26 & longitude < -73.70) |>
summarize(avg_lat = mean(latitude, na.rm = TRUE),
avg_long = mean(longitude, na.rm = TRUE))# A tibble: 1 × 2
avg_lat avg_long
<dbl> <dbl>
1 40.7 -73.9Next, several plots investigating accident counts will be displayed.
df_2 |>
ggplot(aes(x=number_of_cyclist_injured)) +
geom_histogram(binwidth = 1, position = "stack") +
labs(title = "Most Cyclist Injury Events Result in Only 1 Cyclist Injury",
x = "Number of Injured Cyclists",
y= "Count") +
theme_minimal()
df_2 |>
ggplot(aes(x=borough)) +
geom_bar(position = "stack") +
labs(title = "Accident History by Borough",
x = "Borough",
y= "Number of Cyclist Injury Events") +
theme_minimal()
bronx_df <- df_2 |>
filter(borough=="BRONX", !is.na(zip_code))
bronx_df |>
ggplot(aes(y=factor(zip_code))) +
geom_bar(position="Stack") +
labs(title = "Cyclist Injury Events Across the Bronx's Zip-Codes",
x = "Number of Cyclist Injury Events",
y = "Zip Code within the Bronx") +
theme_minimal()
Findings and Recommendations
In order to visualize the data in figures, the data set was first cleaned up by removing unnecessary columns and renaming the columns to match a standardized convention. From there, the frequency of single- or multiple-cyclist injury events was plotted, and it was determined that most frequently only a single cyclist is injured in a cyclist injury event. In addition, the distribution of cyclist injury events across the five boroughs was mapped, and a new data issue was determined: missing data (NA values) represented the third most frequent borough! The number of missing boroughs exceeded the total number of events from Staten Island, Queens, and the Bronx, individually. The data provided for the Bronx was dis-aggregated by zip code, and 10451 represented the Bronx zip-code with the most cyclist injury events, exceeding 400 total events.
Future improvements to this analysis could include analysis of time-of-day as a factor in cyclist injury frequency. Additionally, the entire data set could be visually mapped onto a geographic representation of the city using the latitude and longitude of each event. This type of visualization could help an observer determine locations with high frequencies of cyclist injury events, which could be useful in city planning endeavors.