Google and Master of Business Analytics program at UBC co-hosted 4-day hackathon in Apr 2022. I participated this hackathon with other 4 classmates, Vivian Yu, Rhea Gupta, Thomas Riva, and Mokka Lin and made a dashboard for Motor Vehicle accidents status in New York city by using NYPD’s public dataset.
The dataset includes 29 columns about each accident’s related information such as location, the number of people injured, the number of people killed, vehicle types, date, time, damaged property, and demographic information about drivers and victims. Total rows for the dataset was 1,196,939
Data Preprocessing: Python, Google Big Query, Pandas, Sklearn
Data Pipeline: Google Biq Query
Dashboard and Visualization: Google Data Studio
Missing Values in ‘Borough’ Column
In brainstorming stage, we discussed about using borough information as one of the key filters for most plots in the dashboard to provide sub-regional information about accidents. However, when we imported the dataset and checked missing values, we found that around 30%(339,591) data in the column was missing.
Therefore, we had to think about how to impute these missing values and tried multiple approaches including KNN imputation and using other ML models such as CatBoost and XGBoost. Among all models we tried, the result from CatBoost was the best as it showed 99.8% accuracy score from 5 fold cross validation.
To make sure the accuracy of imputation, we pointed with different colours by boroughs for the imputed values into the New York city’s map and were able to find that the each colour’s distribution is almost same as actual areas for each borough.
Actual areas for the New York City’s boroughs
Imputed borough classification for missing values