Code-Example-200-Austin-Car-Crash-Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8a0ab2ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Numpy Version 1.23.5\n",
      "Pandas Version 2.0.2\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "print(\"Numpy Version\", np. __version__)\n",
    "print(\"Pandas Version\", pd. __version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a425926a",
   "metadata": {},
   "source": [
    "# Austin Car Crash Dataset\n",
    "Crash data is obtained from the Texas Department of Transportation (TXDOT) Crash Record Information System (CRIS) database, which is populated by reports submitted by Texas Peace Officers throughout the state, including Austin Police Department (APD), and maintained by TXDOT.\n",
    "This dataset contains crash-level records for crashes which have occurred in the last ten years. Crash data may take several days or weeks to be initially provided and finalized as it is furnished to the Austin Transportation & Public Works Department, therefore a two-week delay is implemented to help ensure more accurate and complete results.\n",
    "Please note that the data and information on this website is for informational purposes only. While we seek to provide accurate information, please note that errors may be present and information presented may not be complete.\n",
    "\n",
    "https://data.austintexas.gov/Transportation-and-Mobility/Austin-Crash-Report-Data-Crash-Level-Records/y2wy-tgr5\n",
    "\n",
    "https://data.austintexas.gov/d/y2wy-tgr5?category=Transportation-and-Mobility&view_name=Austin-Crash-Report-Data-Crash-Level-Records\n",
    "\n",
    "You can find a good description of the dataset here:\n",
    "https://data.austintexas.gov/Transportation-and-Mobility/Austin-Crash-Report-Data-Crash-Level-Records/y2wy-tgr5/about_data\n",
    "\n",
    "\n",
    "<img src=\"https://data.austintexas.gov/api/views/y2wy-tgr5/files/b5e18a1a-5071-4f7f-a658-07b498fb954e?download=true&filename=52029539143_bed94b40db_c.jpg\" width=\"500\" height=\"340\">\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3ab9671e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>crash_id</th>\n",
       "      <th>crash_fatal_fl</th>\n",
       "      <th>crash_date</th>\n",
       "      <th>crash_time</th>\n",
       "      <th>case_id</th>\n",
       "      <th>rpt_latitude</th>\n",
       "      <th>rpt_longitude</th>\n",
       "      <th>rpt_block_num</th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th>...</th>\n",
       "      <th>pedestrian_serious_injury_count</th>\n",
       "      <th>motorcycle_death_count</th>\n",
       "      <th>motorcycle_serious_injury_count</th>\n",
       "      <th>other_death_count</th>\n",
       "      <th>other_serious_injury_count</th>\n",
       "      <th>onsys_fl</th>\n",
       "      <th>private_dr_fl</th>\n",
       "      <th>micromobility_serious_injury_count</th>\n",
       "      <th>micromobility_death_count</th>\n",
       "      <th>micromobility_fl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13719812</td>\n",
       "      <td>N</td>\n",
       "      <td>02/17/2014 03:03:00 AM</td>\n",
       "      <td>03:03:00</td>\n",
       "      <td>140480167</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000 W</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NOT REPORTED</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13688137</td>\n",
       "      <td>N</td>\n",
       "      <td>01/29/2014 09:39:00 PM</td>\n",
       "      <td>21:39:00</td>\n",
       "      <td>140291668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEASSAU</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13705309</td>\n",
       "      <td>N</td>\n",
       "      <td>02/12/2014 11:35:00 AM</td>\n",
       "      <td>11:35:00</td>\n",
       "      <td>140430690</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NOT REPORTED</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13707782</td>\n",
       "      <td>N</td>\n",
       "      <td>02/11/2014 06:02:00 PM</td>\n",
       "      <td>18:02:00</td>\n",
       "      <td>140421191</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NOT REPORTED</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13711454</td>\n",
       "      <td>N</td>\n",
       "      <td>02/16/2014 11:00:00 PM</td>\n",
       "      <td>23:00:00</td>\n",
       "      <td>140471694</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>MOPAC NB TO EB 290</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   crash_id crash_fatal_fl              crash_date crash_time    case_id  \\\n",
       "0  13719812              N  02/17/2014 03:03:00 AM   03:03:00  140480167   \n",
       "1  13688137              N  01/29/2014 09:39:00 PM   21:39:00  140291668   \n",
       "2  13705309              N  02/12/2014 11:35:00 AM   11:35:00  140430690   \n",
       "3  13707782              N  02/11/2014 06:02:00 PM   18:02:00  140421191   \n",
       "4  13711454              N  02/16/2014 11:00:00 PM   23:00:00  140471694   \n",
       "\n",
       "   rpt_latitude  rpt_longitude rpt_block_num rpt_street_pfx  \\\n",
       "0           NaN            NaN        2000 W            NaN   \n",
       "1           NaN            NaN         10100            NaN   \n",
       "2           NaN            NaN          2700            NaN   \n",
       "3           NaN            NaN           NaN            NaN   \n",
       "4           NaN            NaN           NaN              N   \n",
       "\n",
       "      rpt_street_name  ... pedestrian_serious_injury_count  \\\n",
       "0        NOT REPORTED  ...                               0   \n",
       "1             DEASSAU  ...                               0   \n",
       "2        NOT REPORTED  ...                               0   \n",
       "3        NOT REPORTED  ...                               0   \n",
       "4  MOPAC NB TO EB 290  ...                               0   \n",
       "\n",
       "   motorcycle_death_count motorcycle_serious_injury_count  other_death_count  \\\n",
       "0                       0                               0                  0   \n",
       "1                       0                               0                  0   \n",
       "2                       0                               0                  0   \n",
       "3                       0                               0                  0   \n",
       "4                       0                               0                  0   \n",
       "\n",
       "   other_serious_injury_count onsys_fl  private_dr_fl  \\\n",
       "0                           0        Y              N   \n",
       "1                           0        N              N   \n",
       "2                           0        Y              N   \n",
       "3                           0        Y              N   \n",
       "4                           0        Y              N   \n",
       "\n",
       "  micromobility_serious_injury_count  micromobility_death_count  \\\n",
       "0                                  0                          0   \n",
       "1                                  0                          0   \n",
       "2                                  0                          0   \n",
       "3                                  0                          0   \n",
       "4                                  0                          0   \n",
       "\n",
       "   micromobility_fl  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "\n",
       "[5 rows x 54 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crashes = pd.read_csv(\"https://github.com/kiat/Elements-of-Data-Analytics/raw/main/datasets/car_crash/Austin_Crash_Report_Data_Crash_Level_Records.csv.bz2\", \\\n",
    "                      compression=\"bz2\", low_memory=False) \n",
    "crashes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7e780901",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 148039 entries, 0 to 148038\n",
      "Data columns (total 54 columns):\n",
      " #   Column                              Non-Null Count   Dtype  \n",
      "---  ------                              --------------   -----  \n",
      " 0   crash_id                            148039 non-null  int64  \n",
      " 1   crash_fatal_fl                      148039 non-null  object \n",
      " 2   crash_date                          148039 non-null  object \n",
      " 3   crash_time                          148039 non-null  object \n",
      " 4   case_id                             146167 non-null  object \n",
      " 5   rpt_latitude                        9480 non-null    float64\n",
      " 6   rpt_longitude                       9480 non-null    float64\n",
      " 7   rpt_block_num                       128195 non-null  object \n",
      " 8   rpt_street_pfx                      79891 non-null   object \n",
      " 9   rpt_street_name                     148033 non-null  object \n",
      " 10  rpt_street_sfx                      98176 non-null   object \n",
      " 11  crash_speed_limit                   148034 non-null  float64\n",
      " 12  road_constr_zone_fl                 148034 non-null  object \n",
      " 13  latitude                            145572 non-null  float64\n",
      " 14  longitude                           145572 non-null  float64\n",
      " 15  street_name                         148034 non-null  object \n",
      " 16  street_nbr                          60897 non-null   float64\n",
      " 17  street_name_2                       66438 non-null   object \n",
      " 18  street_nbr_2                        0 non-null       float64\n",
      " 19  crash_sev_id                        148038 non-null  float64\n",
      " 20  sus_serious_injry_cnt               148039 non-null  int64  \n",
      " 21  nonincap_injry_cnt                  148036 non-null  float64\n",
      " 22  poss_injry_cnt                      148036 non-null  float64\n",
      " 23  non_injry_cnt                       148036 non-null  float64\n",
      " 24  unkn_injry_cnt                      148034 non-null  float64\n",
      " 25  tot_injry_cnt                       148037 non-null  float64\n",
      " 26  death_cnt                           148039 non-null  int64  \n",
      " 27  contrib_factr_p1_id                 28561 non-null   float64\n",
      " 28  contrib_factr_p2_id                 4568 non-null    float64\n",
      " 29  units_involved                      148037 non-null  object \n",
      " 30  atd_mode_category_metadata          148037 non-null  object \n",
      " 31  pedestrian_fl                       3476 non-null    object \n",
      " 32  motor_vehicle_fl                    146917 non-null  object \n",
      " 33  motorcycle_fl                       3637 non-null    object \n",
      " 34  bicycle_fl                          2457 non-null    object \n",
      " 35  other_fl                            4856 non-null    object \n",
      " 36  point                               145572 non-null  object \n",
      " 37  apd_confirmed_fatality              148039 non-null  object \n",
      " 38  apd_confirmed_death_count           148038 non-null  float64\n",
      " 39  motor_vehicle_death_count           148039 non-null  int64  \n",
      " 40  motor_vehicle_serious_injury_count  148039 non-null  int64  \n",
      " 41  bicycle_death_count                 148039 non-null  int64  \n",
      " 42  bicycle_serious_injury_count        148039 non-null  int64  \n",
      " 43  pedestrian_death_count              148039 non-null  int64  \n",
      " 44  pedestrian_serious_injury_count     148039 non-null  int64  \n",
      " 45  motorcycle_death_count              148039 non-null  int64  \n",
      " 46  motorcycle_serious_injury_count     148039 non-null  int64  \n",
      " 47  other_death_count                   148039 non-null  int64  \n",
      " 48  other_serious_injury_count          148039 non-null  int64  \n",
      " 49  onsys_fl                            148037 non-null  object \n",
      " 50  private_dr_fl                       148039 non-null  object \n",
      " 51  micromobility_serious_injury_count  148039 non-null  int64  \n",
      " 52  micromobility_death_count           148039 non-null  int64  \n",
      " 53  micromobility_fl                    299 non-null     object \n",
      "dtypes: float64(16), int64(15), object(23)\n",
      "memory usage: 61.0+ MB\n"
     ]
    }
   ],
   "source": [
    "crashes.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "29a17863",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "crash_id                                   0\n",
       "crash_fatal_fl                             0\n",
       "crash_date                                 0\n",
       "crash_time                                 0\n",
       "case_id                                 1872\n",
       "rpt_latitude                          138559\n",
       "rpt_longitude                         138559\n",
       "rpt_block_num                          19844\n",
       "rpt_street_pfx                         68148\n",
       "rpt_street_name                            6\n",
       "rpt_street_sfx                         49863\n",
       "crash_speed_limit                          5\n",
       "road_constr_zone_fl                        5\n",
       "latitude                                2467\n",
       "longitude                               2467\n",
       "street_name                                5\n",
       "street_nbr                             87142\n",
       "street_name_2                          81601\n",
       "street_nbr_2                          148039\n",
       "crash_sev_id                               1\n",
       "sus_serious_injry_cnt                      0\n",
       "nonincap_injry_cnt                         3\n",
       "poss_injry_cnt                             3\n",
       "non_injry_cnt                              3\n",
       "unkn_injry_cnt                             5\n",
       "tot_injry_cnt                              2\n",
       "death_cnt                                  0\n",
       "contrib_factr_p1_id                   119478\n",
       "contrib_factr_p2_id                   143471\n",
       "units_involved                             2\n",
       "atd_mode_category_metadata                 2\n",
       "pedestrian_fl                         144563\n",
       "motor_vehicle_fl                        1122\n",
       "motorcycle_fl                         144402\n",
       "bicycle_fl                            145582\n",
       "other_fl                              143183\n",
       "point                                   2467\n",
       "apd_confirmed_fatality                     0\n",
       "apd_confirmed_death_count                  1\n",
       "motor_vehicle_death_count                  0\n",
       "motor_vehicle_serious_injury_count         0\n",
       "bicycle_death_count                        0\n",
       "bicycle_serious_injury_count               0\n",
       "pedestrian_death_count                     0\n",
       "pedestrian_serious_injury_count            0\n",
       "motorcycle_death_count                     0\n",
       "motorcycle_serious_injury_count            0\n",
       "other_death_count                          0\n",
       "other_serious_injury_count                 0\n",
       "onsys_fl                                   2\n",
       "private_dr_fl                              0\n",
       "micromobility_serious_injury_count         0\n",
       "micromobility_death_count                  0\n",
       "micromobility_fl                      147740\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crashes.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e8d08b84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(148039, 54)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crashes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fe39fc5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rpt_street_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NOT REPORTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DEASSAU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NOT REPORTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NOT REPORTED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MOPAC NB TO EB 290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      rpt_street_name\n",
       "0        NOT REPORTED\n",
       "1             DEASSAU\n",
       "2        NOT REPORTED\n",
       "3        NOT REPORTED\n",
       "4  MOPAC NB TO EB 290"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crashes[['rpt_street_name']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5646a08b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(148033, 54)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can drop a row if the value of a specific column is NA. \n",
    "crashes.dropna(subset=['rpt_street_name'], inplace=True)\n",
    "crashes.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2df3b76",
   "metadata": {},
   "source": [
    "# Question 1 - Which top 10 streets in Austin has the highst car crashes in the last years in this dataset? Order the output. \n",
    "\n",
    "street_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f196c7d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NOT REPORTED</th>\n",
       "      <td>10509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IH 35</th>\n",
       "      <td>3937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LAMAR</th>\n",
       "      <td>3865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MOPAC</th>\n",
       "      <td>3680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PARMER</th>\n",
       "      <td>1785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CONGRESS</th>\n",
       "      <td>1780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESEARCH</th>\n",
       "      <td>1674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEN WHITE</th>\n",
       "      <td>1562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IH 35 SB</th>\n",
       "      <td>1463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RIVERSIDE</th>\n",
       "      <td>1386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 count\n",
       "rpt_street_name       \n",
       "NOT REPORTED     10509\n",
       "IH 35             3937\n",
       "LAMAR             3865\n",
       "MOPAC             3680\n",
       "PARMER            1785\n",
       "CONGRESS          1780\n",
       "RESEARCH          1674\n",
       "BEN WHITE         1562\n",
       "IH 35 SB          1463\n",
       "RIVERSIDE         1386"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(crashes[['rpt_street_name']].value_counts())\n",
    "\n",
    "df.nlargest(10, 'count')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0e9c564",
   "metadata": {},
   "source": [
    "# Time and Day of the Crash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "83e1f35b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "0         0\n",
      "1         2\n",
      "2         2\n",
      "3         1\n",
      "4         6\n",
      "         ..\n",
      "148034    5\n",
      "148035    4\n",
      "148036    0\n",
      "148037    3\n",
      "148038    3\n",
      "Name: weekday, Length: 148033, dtype: int32\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0          3\n",
       "1         21\n",
       "2         11\n",
       "3         18\n",
       "4         23\n",
       "          ..\n",
       "148034    16\n",
       "148035    18\n",
       "148036     0\n",
       "148037    15\n",
       "148038     8\n",
       "Name: hour, Length: 148033, dtype: int32"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Check if the dtype is object (string-like)\n",
    "is_date_format = crashes['crash_date'].dtypes == 'object'\n",
    "print(is_date_format)\n",
    "\n",
    "\n",
    "# https://stackoverflow.com/questions/33365055/attributeerror-can-only-use-dt-accessor-with-datetimelike-values\n",
    "crashes['crash_date'] = pd.to_datetime(crashes['crash_date'], errors='coerce')\n",
    "\n",
    "\n",
    "# Convert if needed\n",
    "# crashes['date'] = pd.to_datetime(crashes['crash_date'])\n",
    "\n",
    "\n",
    "# extract the weekday from the date column\n",
    "crashes['weekday'] = crashes['crash_date'].dt.weekday\n",
    "\n",
    "print(crashes['weekday'])\n",
    "\n",
    "# Extact hour of the day\n",
    "crashes['hour'] = crashes['crash_date'].dt.hour\n",
    "\n",
    "crashes['hour']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb2e6e06",
   "metadata": {},
   "source": [
    "# Question 2 - Which weekday has the highest car crashes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaf1dd88",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0ae8e843",
   "metadata": {},
   "source": [
    "# Question 3 - Which hour of the day has the highest car crashes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4c077ac",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6277f0ff",
   "metadata": {},
   "source": [
    "# Question 4 - Which Month of Year has the highest car crashes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3bb1c04",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e112be07",
   "metadata": {},
   "source": [
    "# Question 5 - Are the total number of car crashes different in different years? \n",
    "Show a table of the total car crashes in the past 10 years (OK to include 2014)! Please display in ascending order of years ex. 2014, 2015..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e6ac788",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "91a6f1db",
   "metadata": {},
   "source": [
    "# Reason and Involvment "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "059f6643",
   "metadata": {},
   "source": [
    "# Question 6 - Which ratio of crashes occurred in or was related to a construction, maintenance, or utility work zone?\n",
    "\n",
    "Cosnider the column 'road_constr_zone_fl'\n",
    "\n",
    "Construction Zone - Indicates whether the crash occurred in or was related to a construction, maintenance, or utility work zone, regardless of whether or not workers were actually present at the time of the crash\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0429173a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "512052f1",
   "metadata": {},
   "source": [
    "# Question 7 - Which ratio of crashes include involvement of pedestrain, motor vehicles, motorcycles, and bicycles? What is the ratio of each in comparision to total number of crashes?\n",
    "\n",
    "Use the columns of:\n",
    "\n",
    "pedestrian_fl  , motor_vehicle_fl , motorcycle_fl,  and bicycle_fl\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a307383",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b999f420",
   "metadata": {},
   "source": [
    "# Deaths and Fatalities &#x1F622;  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f378794",
   "metadata": {},
   "source": [
    "# Question 8 - What are the death counts of each year?\n",
    "\n",
    "cosider the 'death_cnt' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91c76b2c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "72b9ee39",
   "metadata": {},
   "source": [
    "# Question 9 - What is the ratio of bicycle deaths to total crashes each year?\n",
    "\n",
    "cosider the column 'bicycle_death_count'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0abb647c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ec8fa2d4",
   "metadata": {},
   "source": [
    "# Question 10 - What is the ratio of crashes with death count>=1 to the total crashes?\n",
    "\n",
    "cosider the 'death_cnt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7b424f4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "340f3ea8",
   "metadata": {},
   "source": [
    "# Question 11 - What are the total death counts of crashes of each different accident involvements ( pedestrain, motor vehicles, motorcycles, and bicycles) ?\n",
    "\n",
    "cosider the column 'death_cnt'\n",
    "and \n",
    "\n",
    "'pedestrian_fl' , 'motor_vehicle_fl' , 'motorcycle_fl' and 'bicycle_fl'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "317ff369",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}