step1_clean_crash_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dea777a2-3d18-4792-aaee-5fbe5d488c81",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19c23d6e-fdc2-4ae1-9d19-3394f4d7967f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading in the raw data\n",
    "# The Motor Vehicle Collisions Crashes data is downloaded from NYC OpenData Portal\n",
    "# https://data.cityofnewyork.us/Public-Safety/Motor-Vehicle-Collisions-Crashes/h9gi-nx95\n",
    "# The data was downloaded on 12/09/2022\n",
    "df = pd.read_csv(\"Raw Data/Motor_Vehicle_Collisions_Crashes_Raw_20221209.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d88c4eb-08cb-496e-b4aa-357d490b430b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are the first five lines:\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4df2f9b5-064c-4f99-b0a7-74be514f6bbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are all the column headers:\n",
    "df.columns\n",
    "# list(df)[:30]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f44ae80",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here are the date types for all columns:\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1c06ab3-c8c9-45bb-b5c8-3c9171580058",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Are there any duplicate IDs? No. That's good. \n",
    "(df['COLLISION_ID'].value_counts() > 1).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afe89874-059d-4fb4-9372-c84f9382a6e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# What is the shape of the data? # rows and # cols\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91dfe353-62da-491d-8800-c7ad7b13522f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# What was the number of cells not filled in for each question?\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cd813ea",
   "metadata": {},
   "source": [
    "# Filter out un-needed data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e0503e2-2df3-4fcd-934a-561f4f4555ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a copy of the dataframe\n",
    "df1 = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7133d74",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the data that doesn't have a latitude and longitude (case 1: field is empty)\n",
    "df2 = df1[(df1['LATITUDE'].notnull()) & (df1['LONGITUDE'].notnull())]\n",
    "print(\"number of dropped data =\", df1.shape[0]-df2.shape[0])\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06150d04",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the data that doesn't have a latitude and longitude (case 2: both fields are zero)\n",
    "df3 = df2[(df2['LATITUDE']!=0) & (df2['LONGITUDE']!=0)]\n",
    "print(\"number of dropped data =\", df2.shape[0]-df3.shape[0])\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8df653d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the data that doesn't involve a cyclist accident\n",
    "df4 = df3[(df3['NUMBER OF CYCLIST INJURED']>0) | (df3['NUMBER OF CYCLIST KILLED']>0)]\n",
    "print(\"number of dropped data =\", df3.shape[0]-df4.shape[0])\n",
    "df4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d063a080",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the data that was after 2022/01/01, should get 41088 --> 41002 entries\n",
    "df4['CRASH DATE'] = pd.to_datetime(df4['CRASH DATE']).dt.date\n",
    "df5 = df4[(df4['CRASH DATE']<datetime.date(2022,1,1))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2fdd967",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"number of dropped data =\", df4.shape[0]-df5.shape[0])\n",
    "print(\"number of current data =\", df5.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e13e337b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the data that was before 2017/01/01, get 23455 entries\n",
    "df6 = df5[(df5['CRASH DATE']>=datetime.date(2017,1,1))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cce6dac7",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"number of dropped data =\", df5.shape[0]-df6.shape[0])\n",
    "print(\"number of current data =\", df6.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c904255",
   "metadata": {},
   "source": [
    "# Some stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba6556c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Overview of cyclist accident data\n",
    "print(\"values in the injury column =\", df5['NUMBER OF CYCLIST INJURED'].unique())\n",
    "print(\"values in the fatality column =\", df5['NUMBER OF CYCLIST KILLED'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e85a461",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print out the number of data entries that don't have a location\n",
    "num_raw_data = df1.shape[0]\n",
    "num_data_w_location = df2.shape[0]\n",
    "num_data_wo_location = num_raw_data - num_data_w_location\n",
    "print(num_raw_data)\n",
    "print(num_data_wo_location)\n",
    "print(num_data_w_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95f7f8e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of data records that don't have a location and cyclist accident\n",
    "num_data_wo_location_only_cyclist = df3.shape[0]\n",
    "num_data_wo_location_not_cyclist = num_data_wo_location - num_data_wo_location_only_cyclist\n",
    "print(num_raw_data)\n",
    "print(num_data_wo_location)\n",
    "print(num_data_w_location)\n",
    "print(num_data_wo_location_not_cyclist)\n",
    "print(num_data_wo_location_only_cyclist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b5016f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df4[(df4['NUMBER OF CYCLIST INJURED']>0)].shape[0])\n",
    "print(df4[(df4['NUMBER OF CYCLIST INJURED']==0)].shape[0])\n",
    "print(df4[(df4['NUMBER OF CYCLIST KILLED']>0)].shape[0])\n",
    "print(df4[(df4['NUMBER OF CYCLIST KILLED']==0)].shape[0])\n",
    "print(df4[(df4['NUMBER OF CYCLIST INJURED']>0) & (df4['NUMBER OF CYCLIST KILLED']>0)].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a04bbf0f",
   "metadata": {},
   "source": [
    "# Plot injury and fatality trend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81b48e3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "years = list(range(2015,2021+1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0030b16",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute cyclist injury and fatality number for each year\n",
    "cyclist_injuries = []\n",
    "cyclist_fatalities = []\n",
    "\n",
    "for year in years:\n",
    "    cyclist_injuries.append(df1[(pd.to_datetime(df1['CRASH DATE']).dt.year == year) & (df1['NUMBER OF CYCLIST INJURED']>0)].shape[0])\n",
    "    cyclist_fatalities.append(df1[(pd.to_datetime(df1['CRASH DATE']).dt.year == year) & (df1['NUMBER OF CYCLIST KILLED']>0)].shape[0])\n",
    "\n",
    "print(cyclist_injuries)\n",
    "print(cyclist_fatalities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ba2996e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute all crash-related injury and fatality number for each year\n",
    "all_injuries = []\n",
    "all_fatalities = []\n",
    "\n",
    "for year in years:\n",
    "    all_injuries.append(df1[(pd.to_datetime(df1['CRASH DATE']).dt.year == year) & (df1['NUMBER OF PERSONS INJURED']>0)].shape[0])\n",
    "    all_fatalities.append(df1[(pd.to_datetime(df1['CRASH DATE']).dt.year == year) & (df1['NUMBER OF PERSONS KILLED']>0)].shape[0])\n",
    "\n",
    "print(all_injuries)\n",
    "print(all_fatalities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fff26290",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute cyclist-to-all-crash accident ratio\n",
    "injury_ratio = [x/y for x,y in zip(cyclist_injuries, all_injuries)]\n",
    "fatality_ratio = [x/y for x,y in zip(cyclist_fatalities, all_fatalities)]\n",
    "\n",
    "print(injury_ratio)\n",
    "print(fatality_ratio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebb87869",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot cyclist injury trend\n",
    "x = years[1:]\n",
    "y = cyclist_injuries[1:]\n",
    "plt.plot(x, y, label = \"# of cyclist injuries in NYC\")\n",
    "\n",
    "# Labels\n",
    "plt.xlabel('Year')\n",
    "plt.ylabel('Number of People')\n",
    "plt.title('Cyclist Injury Trend')\n",
    "plt.legend()\n",
    "\n",
    "# Set the ticks\n",
    "plt.xticks(np.arange(min(x), max(x)+1, 1))\n",
    "\n",
    "# Save the plot\n",
    "plt.savefig('cyclist_injury_trend.jpg', dpi=300, bbox_inches=\"tight\")\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b98d0a10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot cyclist fatality trend\n",
    "x = years[1:]\n",
    "y = cyclist_fatalities[1:]\n",
    "plt.plot(x, y, label = \"# of cyclist fatalities in NYC\")\n",
    "\n",
    "# Label\n",
    "plt.xlabel('Year')\n",
    "plt.ylabel('Number of People')\n",
    "plt.title('Cyclist Fatality Trend')\n",
    "plt.legend(loc='upper left')\n",
    "\n",
    "# Set the ticks\n",
    "plt.xticks(np.arange(min(x), max(x)+1, 1))\n",
    "\n",
    "# Save the plot\n",
    "plt.savefig('cyclist_fatality_trend.jpg', dpi=300, bbox_inches=\"tight\")\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5bc5847",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot cyclist injury ratio trend\n",
    "x = years[1:]\n",
    "y = injury_ratio[1:]\n",
    "plt.plot(x, y, label = \"Ratio of cyclist injury to all crash injury\")\n",
    "\n",
    "# Label\n",
    "plt.xlabel('Year')\n",
    "plt.ylabel('Percentage')\n",
    "plt.title('Cyclist Injury Ratio Trend')\n",
    "plt.legend(loc='upper left')\n",
    "\n",
    "# Set the ticks\n",
    "plt.xticks(np.arange(min(x), max(x)+1, 1))\n",
    "\n",
    "# Save the plot\n",
    "plt.savefig('cyclist_injury_ratio_trend.jpg', dpi=300, bbox_inches=\"tight\")\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46be1b54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot cyclist fatality ratio trend\n",
    "x = years[1:]\n",
    "y = fatality_ratio[1:]\n",
    "plt.plot(x, y, label = \"Ratio of cyclist fatality to all crash fatality\")\n",
    "\n",
    "# Label\n",
    "plt.xlabel('Year')\n",
    "plt.ylabel('Percentage')\n",
    "plt.title('Cyclist Fatality Ratio Trend')\n",
    "plt.legend(loc='upper left')\n",
    "\n",
    "# Set the ticks\n",
    "plt.xticks(np.arange(min(x), max(x)+1, 1))\n",
    "\n",
    "# Save the plot\n",
    "plt.savefig('cyclist_fatality_ratio_trend.jpg', dpi=300, bbox_inches=\"tight\")\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bf233d9",
   "metadata": {},
   "source": [
    "# Export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a13ea6c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the filtered dataframe as csv file\n",
    "df6.to_csv('Motor_Vehicle_Collisions_Crashes_Cleaned_20221222.csv',index=False)\n",
    "# df3.to_csv('Motor_Vehicle_Collisions_Crashes_Cleaned_20220929.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.4 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4 (tags/v3.10.4:9d38120, Mar 23 2022, 23:13:41) [MSC v.1929 64 bit (AMD64)]"
  },
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}