CapstoneTwo/DataWrangling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "filepath = '/Users/jacksonpeoples/Desktop/Springboard/CapstoneTwo/Data/Vision_Zero_Crash_Report_Data.csv'\n",
    "crash_data = pd.read_csv(filepath)\n",
    "\n",
    "weather_path = '/Users/jacksonpeoples/Desktop/Springboard/CapstoneTwo/Data/WeatherAustin.csv'\n",
    "weather_data = pd.read_csv(weather_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 161824 entries, 0 to 161823\n",
      "Data columns (total 51 columns):\n",
      " #   Column                              Non-Null Count   Dtype  \n",
      "---  ------                              --------------   -----  \n",
      " 0   crash_id                            161824 non-null  int64  \n",
      " 1   crash_fatal_fl                      161824 non-null  object \n",
      " 2   crash_date                          161824 non-null  object \n",
      " 3   crash_time                          161824 non-null  object \n",
      " 4   case_id                             159592 non-null  object \n",
      " 5   rpt_latitude                        5349 non-null    float64\n",
      " 6   rpt_longitude                       5349 non-null    float64\n",
      " 7   rpt_block_num                       140411 non-null  object \n",
      " 8   rpt_street_pfx                      90518 non-null   object \n",
      " 9   rpt_street_name                     161818 non-null  object \n",
      " 10  rpt_street_sfx                      118702 non-null  object \n",
      " 11  crash_speed_limit                   161819 non-null  float64\n",
      " 12  road_constr_zone_fl                 161819 non-null  object \n",
      " 13  latitude                            155992 non-null  float64\n",
      " 14  longitude                           155992 non-null  float64\n",
      " 15  street_name                         161819 non-null  object \n",
      " 16  street_nbr                          67555 non-null   float64\n",
      " 17  street_name_2                       70496 non-null   object \n",
      " 18  street_nbr_2                        0 non-null       float64\n",
      " 19  crash_sev_id                        161819 non-null  float64\n",
      " 20  sus_serious_injry_cnt               161824 non-null  int64  \n",
      " 21  nonincap_injry_cnt                  161821 non-null  float64\n",
      " 22  poss_injry_cnt                      161821 non-null  float64\n",
      " 23  non_injry_cnt                       161821 non-null  float64\n",
      " 24  unkn_injry_cnt                      161819 non-null  float64\n",
      " 25  tot_injry_cnt                       161820 non-null  float64\n",
      " 26  death_cnt                           161824 non-null  int64  \n",
      " 27  contrib_factr_p1_id                 30884 non-null   float64\n",
      " 28  contrib_factr_p2_id                 6298 non-null    float64\n",
      " 29  units_involved                      161817 non-null  object \n",
      " 30  atd_mode_category_metadata          161817 non-null  object \n",
      " 31  pedestrian_fl                       4389 non-null    object \n",
      " 32  motor_vehicle_fl                    160319 non-null  object \n",
      " 33  motorcycle_fl                       4509 non-null    object \n",
      " 34  bicycle_fl                          3125 non-null    object \n",
      " 35  other_fl                            5130 non-null    object \n",
      " 36  point                               155992 non-null  object \n",
      " 37  apd_confirmed_fatality              161824 non-null  object \n",
      " 38  apd_confirmed_death_count           161822 non-null  float64\n",
      " 39  motor_vehicle_death_count           161824 non-null  int64  \n",
      " 40  motor_vehicle_serious_injury_count  161824 non-null  int64  \n",
      " 41  bicycle_death_count                 161824 non-null  int64  \n",
      " 42  bicycle_serious_injury_count        161824 non-null  int64  \n",
      " 43  pedestrian_death_count              161824 non-null  int64  \n",
      " 44  pedestrian_serious_injury_count     161824 non-null  int64  \n",
      " 45  motorcycle_death_count              161824 non-null  int64  \n",
      " 46  motorcycle_serious_injury_count     161824 non-null  int64  \n",
      " 47  other_death_count                   161824 non-null  int64  \n",
      " 48  other_serious_injury_count          161824 non-null  int64  \n",
      " 49  onsys_fl                            161819 non-null  object \n",
      " 50  private_dr_fl                       161819 non-null  object \n",
      "dtypes: float64(16), int64(13), object(22)\n",
      "memory usage: 63.0+ MB\n"
     ]
    }
   ],
   "source": [
    "#Checking out some basic info on the dataset:\n",
    "crash_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>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>bicycle_death_count</th>\n",
       "      <th>bicycle_serious_injury_count</th>\n",
       "      <th>pedestrian_death_count</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16776868</td>\n",
       "      <td>N</td>\n",
       "      <td>12/04/2018 03:36:00 PM</td>\n",
       "      <td>15:36:00</td>\n",
       "      <td>183381097</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900</td>\n",
       "      <td>N</td>\n",
       "      <td>IH 35 SB</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16786471</td>\n",
       "      <td>N</td>\n",
       "      <td>12/07/2018 01:38:00 PM</td>\n",
       "      <td>13:38:00</td>\n",
       "      <td>183410837</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N IH 35 SVRD SB</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16757244</td>\n",
       "      <td>N</td>\n",
       "      <td>11/18/2018 04:10:00 AM</td>\n",
       "      <td>04:10:00</td>\n",
       "      <td>183220392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4500</td>\n",
       "      <td>N</td>\n",
       "      <td>N IH 35 NB</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16776852</td>\n",
       "      <td>N</td>\n",
       "      <td>12/03/2018 11:19:00 AM</td>\n",
       "      <td>11:19:00</td>\n",
       "      <td>183370654</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NUECES ST</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16765750</td>\n",
       "      <td>N</td>\n",
       "      <td>11/29/2018 09:07:00 AM</td>\n",
       "      <td>09:07:00</td>\n",
       "      <td>183330494</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4100</td>\n",
       "      <td>N</td>\n",
       "      <td>LAMAR</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   crash_id crash_fatal_fl              crash_date crash_time    case_id  \\\n",
       "0  16776868              N  12/04/2018 03:36:00 PM   15:36:00  183381097   \n",
       "1  16786471              N  12/07/2018 01:38:00 PM   13:38:00  183410837   \n",
       "2  16757244              N  11/18/2018 04:10:00 AM   04:10:00  183220392   \n",
       "3  16776852              N  12/03/2018 11:19:00 AM   11:19:00  183370654   \n",
       "4  16765750              N  11/29/2018 09:07:00 AM   09:07:00  183330494   \n",
       "\n",
       "   rpt_latitude  rpt_longitude rpt_block_num rpt_street_pfx  rpt_street_name  \\\n",
       "0           NaN            NaN          1900              N         IH 35 SB   \n",
       "1           NaN            NaN         12400            NaN  N IH 35 SVRD SB   \n",
       "2           NaN            NaN          4500              N       N IH 35 NB   \n",
       "3           NaN            NaN           200            NaN        NUECES ST   \n",
       "4           NaN            NaN          4100              N            LAMAR   \n",
       "\n",
       "   ... bicycle_death_count  bicycle_serious_injury_count  \\\n",
       "0  ...                   0                             0   \n",
       "1  ...                   0                             0   \n",
       "2  ...                   0                             0   \n",
       "3  ...                   0                             0   \n",
       "4  ...                   0                             0   \n",
       "\n",
       "  pedestrian_death_count  pedestrian_serious_injury_count  \\\n",
       "0                      0                                0   \n",
       "1                      0                                0   \n",
       "2                      0                                0   \n",
       "3                      0                                0   \n",
       "4                      0                                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         Y              N  \n",
       "2                          0         Y              N  \n",
       "3                          0         N              N  \n",
       "4                          0         Y              N  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Quite a few columns. Many will be useful, some won't. Some have very few values and some convey the same information\n",
    "#in a different form.\n",
    "crash_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Will go ahead and get rid of columns that pertain to police response (not helpful in predicting danger) and\n",
    "#a few that are almost entirely empty.\n",
    "del_col = ['case_id', 'rpt_latitude', 'rpt_longitude', 'contrib_factr_p1_id', 'contrib_factr_p2_id', 'units_involved','atd_mode_category_metadata', 'street_nbr_2']\n",
    "crash_data = crash_data.drop(del_col, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "N    161035\n",
       "Y       789\n",
       "Name: crash_fatal_fl, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Another question worth asking is should I regard fatality info? Use it to weight the danger of a given crash,\n",
    "#or should I drop the data and focus only on the likelihood of a crash period?\n",
    "crash_data['crash_fatal_fl'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11/21/2018 05:39:00 PM\n",
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "sample = crash_data['crash_date'][7]\n",
    "print(sample)\n",
    "print(type(sample))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11/21/2018\n"
     ]
    }
   ],
   "source": [
    "#Would like to get rid of the time info in 'crash_date' so that I can work with date/time of day\n",
    "#independently.\n",
    "def split_time(crash_date):\n",
    "    tup = crash_date.partition(' ')\n",
    "    return tup[0]\n",
    "print(split_time(sample))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Will now apply the function to the 'crash_date' column:\n",
    "crash_data['crash_date_only'] = crash_data['crash_date'].apply(split_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>crash_id</th>\n",
       "      <th>crash_fatal_fl</th>\n",
       "      <th>crash_date</th>\n",
       "      <th>crash_time</th>\n",
       "      <th>rpt_block_num</th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th>rpt_street_sfx</th>\n",
       "      <th>crash_speed_limit</th>\n",
       "      <th>road_constr_zone_fl</th>\n",
       "      <th>...</th>\n",
       "      <th>bicycle_serious_injury_count</th>\n",
       "      <th>pedestrian_death_count</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>crash_date_only</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16776868</td>\n",
       "      <td>N</td>\n",
       "      <td>12/04/2018 03:36:00 PM</td>\n",
       "      <td>15:36:00</td>\n",
       "      <td>1900</td>\n",
       "      <td>N</td>\n",
       "      <td>IH 35 SB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>12/04/2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16786471</td>\n",
       "      <td>N</td>\n",
       "      <td>12/07/2018 01:38:00 PM</td>\n",
       "      <td>13:38:00</td>\n",
       "      <td>12400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N IH 35 SVRD SB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>12/07/2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16757244</td>\n",
       "      <td>N</td>\n",
       "      <td>11/18/2018 04:10:00 AM</td>\n",
       "      <td>04:10:00</td>\n",
       "      <td>4500</td>\n",
       "      <td>N</td>\n",
       "      <td>N IH 35 NB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>11/18/2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16776852</td>\n",
       "      <td>N</td>\n",
       "      <td>12/03/2018 11:19:00 AM</td>\n",
       "      <td>11:19:00</td>\n",
       "      <td>200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NUECES ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>12/03/2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16765750</td>\n",
       "      <td>N</td>\n",
       "      <td>11/29/2018 09:07:00 AM</td>\n",
       "      <td>09:07:00</td>\n",
       "      <td>4100</td>\n",
       "      <td>N</td>\n",
       "      <td>LAMAR</td>\n",
       "      <td>BLVD</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>11/29/2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   crash_id crash_fatal_fl              crash_date crash_time rpt_block_num  \\\n",
       "0  16776868              N  12/04/2018 03:36:00 PM   15:36:00          1900   \n",
       "1  16786471              N  12/07/2018 01:38:00 PM   13:38:00         12400   \n",
       "2  16757244              N  11/18/2018 04:10:00 AM   04:10:00          4500   \n",
       "3  16776852              N  12/03/2018 11:19:00 AM   11:19:00           200   \n",
       "4  16765750              N  11/29/2018 09:07:00 AM   09:07:00          4100   \n",
       "\n",
       "  rpt_street_pfx  rpt_street_name rpt_street_sfx  crash_speed_limit  \\\n",
       "0              N         IH 35 SB            NaN               -1.0   \n",
       "1            NaN  N IH 35 SVRD SB            NaN               70.0   \n",
       "2              N       N IH 35 NB            NaN               70.0   \n",
       "3            NaN        NUECES ST            NaN               30.0   \n",
       "4              N            LAMAR           BLVD               -1.0   \n",
       "\n",
       "  road_constr_zone_fl  ...  bicycle_serious_injury_count  \\\n",
       "0                   N  ...                             0   \n",
       "1                   N  ...                             0   \n",
       "2                   N  ...                             0   \n",
       "3                   N  ...                             0   \n",
       "4                   N  ...                             0   \n",
       "\n",
       "   pedestrian_death_count pedestrian_serious_injury_count  \\\n",
       "0                       0                               0   \n",
       "1                       0                               0   \n",
       "2                       0                               0   \n",
       "3                       0                               0   \n",
       "4                       0                               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  crash_date_only  \n",
       "0                           0         Y              N       12/04/2018  \n",
       "1                           0         Y              N       12/07/2018  \n",
       "2                           0         Y              N       11/18/2018  \n",
       "3                           0         N              N       12/03/2018  \n",
       "4                           0         Y              N       11/29/2018  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#See if it worked:\n",
    "crash_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'str'>\n",
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "print(type(crash_data['crash_date_only'][3]))\n",
    "print(type(crash_data['crash_time'][3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Need to convert both 'crash_date' and 'crash_time' to timeseries types.\n",
    "crash_data['crash_date_only']= pd.to_datetime(crash_data['crash_date_only'])\n",
    "crash_data['crash_date']= pd.to_datetime(crash_data['crash_date'])\n",
    "crash_data['crash_time']= pd.to_datetime(crash_data['crash_time'])\n",
    "crash_data['crash_time'] = [time.time() for time in crash_data['crash_time']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "<class 'datetime.time'>\n"
     ]
    }
   ],
   "source": [
    "print(type(crash_data['crash_date'][3]))\n",
    "print(type(crash_data['crash_time'][3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>crash_id</th>\n",
       "      <th>crash_fatal_fl</th>\n",
       "      <th>crash_date</th>\n",
       "      <th>crash_time</th>\n",
       "      <th>rpt_block_num</th>\n",
       "      <th>rpt_street_pfx</th>\n",
       "      <th>rpt_street_name</th>\n",
       "      <th>rpt_street_sfx</th>\n",
       "      <th>crash_speed_limit</th>\n",
       "      <th>road_constr_zone_fl</th>\n",
       "      <th>...</th>\n",
       "      <th>bicycle_serious_injury_count</th>\n",
       "      <th>pedestrian_death_count</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>crash_date_only</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16776868</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-04 15:36:00</td>\n",
       "      <td>15:36:00</td>\n",
       "      <td>1900</td>\n",
       "      <td>N</td>\n",
       "      <td>IH 35 SB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16786471</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-07 13:38:00</td>\n",
       "      <td>13:38:00</td>\n",
       "      <td>12400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N IH 35 SVRD SB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16757244</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-18 04:10:00</td>\n",
       "      <td>04:10:00</td>\n",
       "      <td>4500</td>\n",
       "      <td>N</td>\n",
       "      <td>N IH 35 NB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16776852</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-03 11:19:00</td>\n",
       "      <td>11:19:00</td>\n",
       "      <td>200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NUECES ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-12-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16765750</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-29 09:07:00</td>\n",
       "      <td>09:07:00</td>\n",
       "      <td>4100</td>\n",
       "      <td>N</td>\n",
       "      <td>LAMAR</td>\n",
       "      <td>BLVD</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>N</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>2018-11-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   crash_id crash_fatal_fl          crash_date crash_time rpt_block_num  \\\n",
       "0  16776868              N 2018-12-04 15:36:00   15:36:00          1900   \n",
       "1  16786471              N 2018-12-07 13:38:00   13:38:00         12400   \n",
       "2  16757244              N 2018-11-18 04:10:00   04:10:00          4500   \n",
       "3  16776852              N 2018-12-03 11:19:00   11:19:00           200   \n",
       "4  16765750              N 2018-11-29 09:07:00   09:07:00          4100   \n",
       "\n",
       "  rpt_street_pfx  rpt_street_name rpt_street_sfx  crash_speed_limit  \\\n",
       "0              N         IH 35 SB            NaN               -1.0   \n",
       "1            NaN  N IH 35 SVRD SB            NaN               70.0   \n",
       "2              N       N IH 35 NB            NaN               70.0   \n",
       "3            NaN        NUECES ST            NaN               30.0   \n",
       "4              N            LAMAR           BLVD               -1.0   \n",
       "\n",
       "  road_constr_zone_fl  ...  bicycle_serious_injury_count  \\\n",
       "0                   N  ...                             0   \n",
       "1                   N  ...                             0   \n",
       "2                   N  ...                             0   \n",
       "3                   N  ...                             0   \n",
       "4                   N  ...                             0   \n",
       "\n",
       "   pedestrian_death_count pedestrian_serious_injury_count  \\\n",
       "0                       0                               0   \n",
       "1                       0                               0   \n",
       "2                       0                               0   \n",
       "3                       0                               0   \n",
       "4                       0                               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  crash_date_only  \n",
       "0                           0         Y              N       2018-12-04  \n",
       "1                           0         Y              N       2018-12-07  \n",
       "2                           0         Y              N       2018-11-18  \n",
       "3                           0         N              N       2018-12-03  \n",
       "4                           0         Y              N       2018-11-29  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a function to get the weekday for each row based on the date.\n",
    "def get_weekday(date):\n",
    "    return date.weekday()\n",
    "\n",
    "#use that function to create weekday column.\n",
    "crash_data['Weekday'] = crash_data['crash_date'].apply(get_weekday)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    26897\n",
       "3    24202\n",
       "1    23605\n",
       "2    23297\n",
       "5    22844\n",
       "0    21798\n",
       "6    19181\n",
       "Name: Weekday, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data['Weekday'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-07-18 17:05:00    4\n",
       "2019-10-18 09:00:00    4\n",
       "2013-04-04 17:30:00    3\n",
       "2014-01-18 14:30:00    3\n",
       "2018-10-08 13:43:00    3\n",
       "                      ..\n",
       "2018-07-03 06:32:00    1\n",
       "2013-06-28 22:35:00    1\n",
       "2019-09-07 10:13:00    1\n",
       "2017-05-14 14:47:00    1\n",
       "2017-12-31 22:00:00    1\n",
       "Name: crash_date, Length: 158083, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data['crash_date'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-08-31 23:38:00\n",
      "2010-01-01 00:59:00\n"
     ]
    }
   ],
   "source": [
    "print(crash_data['crash_date'].max())\n",
    "print(crash_data['crash_date'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "<class 'datetime.time'>\n"
     ]
    }
   ],
   "source": [
    "print(type(crash_data['crash_date'][3]))\n",
    "print(type(crash_data['crash_time'][3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#need to create a column in the crash data that rounds the time to the nearest hour because the weather data is \n",
    "#hourly.\n",
    "def rounding(time):\n",
    "    return time.floor('H')\n",
    "\n",
    "crash_data['rounded_time'] = crash_data['crash_date'].apply(rounding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2018-12-04 15:00:00\n",
       "1   2018-12-07 13:00:00\n",
       "2   2018-11-18 04:00:00\n",
       "3   2018-12-03 11:00:00\n",
       "4   2018-11-29 09:00:00\n",
       "Name: rounded_time, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data['rounded_time'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>dt</th>\n",
       "      <th>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>temp</th>\n",
       "      <th>feels_like</th>\n",
       "      <th>temp_min</th>\n",
       "      <th>temp_max</th>\n",
       "      <th>...</th>\n",
       "      <th>wind_deg</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>weather_id</th>\n",
       "      <th>weather_main</th>\n",
       "      <th>weather_description</th>\n",
       "      <th>weather_icon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1199145600</td>\n",
       "      <td>2008-01-01 00:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>57.49</td>\n",
       "      <td>46.69</td>\n",
       "      <td>55.74</td>\n",
       "      <td>57.92</td>\n",
       "      <td>...</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1199149200</td>\n",
       "      <td>2008-01-01 01:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>55.13</td>\n",
       "      <td>47.39</td>\n",
       "      <td>53.96</td>\n",
       "      <td>57.22</td>\n",
       "      <td>...</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1199152800</td>\n",
       "      <td>2008-01-01 02:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>52.79</td>\n",
       "      <td>42.84</td>\n",
       "      <td>51.08</td>\n",
       "      <td>54.89</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1199156400</td>\n",
       "      <td>2008-01-01 03:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>50.65</td>\n",
       "      <td>38.66</td>\n",
       "      <td>48.98</td>\n",
       "      <td>51.98</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1199160000</td>\n",
       "      <td>2008-01-01 04:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>49.14</td>\n",
       "      <td>39.16</td>\n",
       "      <td>47.69</td>\n",
       "      <td>50.00</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt                         dt_iso  timezone city_name        lat  \\\n",
       "0  1199145600  2008-01-01 00:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "1  1199149200  2008-01-01 01:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "2  1199152800  2008-01-01 02:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "3  1199156400  2008-01-01 03:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "4  1199160000  2008-01-01 04:00:00 +0000 UTC    -21600    Austin  30.267153   \n",
       "\n",
       "         lon   temp  feels_like  temp_min  temp_max  ...  wind_deg  rain_1h  \\\n",
       "0 -97.743061  57.49       46.69     55.74     57.92  ...       360      NaN   \n",
       "1 -97.743061  55.13       47.39     53.96     57.22  ...       360      NaN   \n",
       "2 -97.743061  52.79       42.84     51.08     54.89  ...        10      NaN   \n",
       "3 -97.743061  50.65       38.66     48.98     51.98  ...        10      NaN   \n",
       "4 -97.743061  49.14       39.16     47.69     50.00  ...        10      NaN   \n",
       "\n",
       "   rain_3h  snow_1h  snow_3h  clouds_all  weather_id  weather_main  \\\n",
       "0      NaN      NaN      NaN           1         800         Clear   \n",
       "1      NaN      NaN      NaN           1         800         Clear   \n",
       "2      NaN      NaN      NaN           1         800         Clear   \n",
       "3      NaN      NaN      NaN           1         800         Clear   \n",
       "4      NaN      NaN      NaN           1         800         Clear   \n",
       "\n",
       "   weather_description  weather_icon  \n",
       "0         sky is clear           01n  \n",
       "1         sky is clear           01n  \n",
       "2         sky is clear           01n  \n",
       "3         sky is clear           01n  \n",
       "4         sky is clear           01n  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 115449 entries, 0 to 115448\n",
      "Data columns (total 25 columns):\n",
      " #   Column               Non-Null Count   Dtype  \n",
      "---  ------               --------------   -----  \n",
      " 0   dt                   115449 non-null  int64  \n",
      " 1   dt_iso               115449 non-null  object \n",
      " 2   timezone             115449 non-null  int64  \n",
      " 3   city_name            115449 non-null  object \n",
      " 4   lat                  115449 non-null  float64\n",
      " 5   lon                  115449 non-null  float64\n",
      " 6   temp                 115449 non-null  float64\n",
      " 7   feels_like           115449 non-null  float64\n",
      " 8   temp_min             115449 non-null  float64\n",
      " 9   temp_max             115449 non-null  float64\n",
      " 10  pressure             115449 non-null  int64  \n",
      " 11  sea_level            0 non-null       float64\n",
      " 12  grnd_level           0 non-null       float64\n",
      " 13  humidity             115449 non-null  int64  \n",
      " 14  wind_speed           115449 non-null  float64\n",
      " 15  wind_deg             115449 non-null  int64  \n",
      " 16  rain_1h              10096 non-null   float64\n",
      " 17  rain_3h              1634 non-null    float64\n",
      " 18  snow_1h              125 non-null     float64\n",
      " 19  snow_3h              14 non-null      float64\n",
      " 20  clouds_all           115449 non-null  int64  \n",
      " 21  weather_id           115449 non-null  int64  \n",
      " 22  weather_main         115449 non-null  object \n",
      " 23  weather_description  115449 non-null  object \n",
      " 24  weather_icon         115449 non-null  object \n",
      "dtypes: float64(13), int64(7), object(5)\n",
      "memory usage: 22.0+ MB\n"
     ]
    }
   ],
   "source": [
    "weather_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def del_tz(text):\n",
    "    tup = text.partition(' +')\n",
    "    return tup[0]\n",
    "\n",
    "def to_dt(time_string):\n",
    "    return pd.to_datetime(time_string)\n",
    "\n",
    "def cst_conv(time):\n",
    "    return time - np.timedelta64(5, 'h')\n",
    "\n",
    "def time_clean(time_string):\n",
    "    return cst_conv(to_dt(del_tz(time_string)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_data['rounded_time'] = weather_data['dt_iso'].apply(time_clean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2007-12-31 23:00:00\n",
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "2018-11-29 09:00:00\n",
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "2020-08-31 23:00:00\n",
      "2010-01-01 00:00:00\n",
      "2020-10-18 18:00:00\n",
      "2007-12-31 19:00:00\n"
     ]
    }
   ],
   "source": [
    "print(weather_data['rounded_time'][4])\n",
    "print(type(weather_data['rounded_time'][4]))\n",
    "print(crash_data['rounded_time'][4])\n",
    "print(type(crash_data['rounded_time'][4]))\n",
    "print(crash_data['rounded_time'].max())\n",
    "print(crash_data['rounded_time'].min())\n",
    "print(weather_data['rounded_time'].max())\n",
    "print(weather_data['rounded_time'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "112200\n",
      "112200\n",
      "112200\n"
     ]
    }
   ],
   "source": [
    "print(weather_data['rounded_time'].nunique())\n",
    "print(weather_data['dt_iso'].nunique())\n",
    "print(weather_data.groupby(['dt_iso','timezone']).ngroups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "weather_id\n",
      "115449\n",
      "weather_main\n",
      "115449\n",
      "weather_description\n",
      "115449\n",
      "weather_icon\n",
      "115443\n"
     ]
    }
   ],
   "source": [
    "for col in weather_data.columns:\n",
    "    if weather_data['dt_iso'].nunique() < weather_data.groupby(['dt_iso',col]).ngroups:\n",
    "        print(col)\n",
    "        print(weather_data.groupby(['dt_iso',col]).ngroups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 115449 entries, 0 to 115448\n",
      "Data columns (total 26 columns):\n",
      " #   Column               Non-Null Count   Dtype         \n",
      "---  ------               --------------   -----         \n",
      " 0   dt                   115449 non-null  int64         \n",
      " 1   dt_iso               115449 non-null  object        \n",
      " 2   timezone             115449 non-null  int64         \n",
      " 3   city_name            115449 non-null  object        \n",
      " 4   lat                  115449 non-null  float64       \n",
      " 5   lon                  115449 non-null  float64       \n",
      " 6   temp                 115449 non-null  float64       \n",
      " 7   feels_like           115449 non-null  float64       \n",
      " 8   temp_min             115449 non-null  float64       \n",
      " 9   temp_max             115449 non-null  float64       \n",
      " 10  pressure             115449 non-null  int64         \n",
      " 11  sea_level            0 non-null       float64       \n",
      " 12  grnd_level           0 non-null       float64       \n",
      " 13  humidity             115449 non-null  int64         \n",
      " 14  wind_speed           115449 non-null  float64       \n",
      " 15  wind_deg             115449 non-null  int64         \n",
      " 16  rain_1h              10096 non-null   float64       \n",
      " 17  rain_3h              1634 non-null    float64       \n",
      " 18  snow_1h              125 non-null     float64       \n",
      " 19  snow_3h              14 non-null      float64       \n",
      " 20  clouds_all           115449 non-null  int64         \n",
      " 21  weather_id           115449 non-null  int64         \n",
      " 22  weather_main         115449 non-null  object        \n",
      " 23  weather_description  115449 non-null  object        \n",
      " 24  weather_icon         115449 non-null  object        \n",
      " 25  rounded_time         115449 non-null  datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(13), int64(7), object(5)\n",
      "memory usage: 22.9+ MB\n"
     ]
    }
   ],
   "source": [
    "weather_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 161824 entries, 0 to 161823\n",
      "Data columns (total 46 columns):\n",
      " #   Column                              Non-Null Count   Dtype         \n",
      "---  ------                              --------------   -----         \n",
      " 0   crash_id                            161824 non-null  int64         \n",
      " 1   crash_fatal_fl                      161824 non-null  object        \n",
      " 2   crash_date                          161824 non-null  datetime64[ns]\n",
      " 3   crash_time                          161824 non-null  object        \n",
      " 4   rpt_block_num                       140411 non-null  object        \n",
      " 5   rpt_street_pfx                      90518 non-null   object        \n",
      " 6   rpt_street_name                     161818 non-null  object        \n",
      " 7   rpt_street_sfx                      118702 non-null  object        \n",
      " 8   crash_speed_limit                   161819 non-null  float64       \n",
      " 9   road_constr_zone_fl                 161819 non-null  object        \n",
      " 10  latitude                            155992 non-null  float64       \n",
      " 11  longitude                           155992 non-null  float64       \n",
      " 12  street_name                         161819 non-null  object        \n",
      " 13  street_nbr                          67555 non-null   float64       \n",
      " 14  street_name_2                       70496 non-null   object        \n",
      " 15  crash_sev_id                        161819 non-null  float64       \n",
      " 16  sus_serious_injry_cnt               161824 non-null  int64         \n",
      " 17  nonincap_injry_cnt                  161821 non-null  float64       \n",
      " 18  poss_injry_cnt                      161821 non-null  float64       \n",
      " 19  non_injry_cnt                       161821 non-null  float64       \n",
      " 20  unkn_injry_cnt                      161819 non-null  float64       \n",
      " 21  tot_injry_cnt                       161820 non-null  float64       \n",
      " 22  death_cnt                           161824 non-null  int64         \n",
      " 23  pedestrian_fl                       4389 non-null    object        \n",
      " 24  motor_vehicle_fl                    160319 non-null  object        \n",
      " 25  motorcycle_fl                       4509 non-null    object        \n",
      " 26  bicycle_fl                          3125 non-null    object        \n",
      " 27  other_fl                            5130 non-null    object        \n",
      " 28  point                               155992 non-null  object        \n",
      " 29  apd_confirmed_fatality              161824 non-null  object        \n",
      " 30  apd_confirmed_death_count           161822 non-null  float64       \n",
      " 31  motor_vehicle_death_count           161824 non-null  int64         \n",
      " 32  motor_vehicle_serious_injury_count  161824 non-null  int64         \n",
      " 33  bicycle_death_count                 161824 non-null  int64         \n",
      " 34  bicycle_serious_injury_count        161824 non-null  int64         \n",
      " 35  pedestrian_death_count              161824 non-null  int64         \n",
      " 36  pedestrian_serious_injury_count     161824 non-null  int64         \n",
      " 37  motorcycle_death_count              161824 non-null  int64         \n",
      " 38  motorcycle_serious_injury_count     161824 non-null  int64         \n",
      " 39  other_death_count                   161824 non-null  int64         \n",
      " 40  other_serious_injury_count          161824 non-null  int64         \n",
      " 41  onsys_fl                            161819 non-null  object        \n",
      " 42  private_dr_fl                       161819 non-null  object        \n",
      " 43  crash_date_only                     161824 non-null  datetime64[ns]\n",
      " 44  Weekday                             161824 non-null  int64         \n",
      " 45  rounded_time                        161824 non-null  datetime64[ns]\n",
      "dtypes: datetime64[ns](3), float64(11), int64(14), object(18)\n",
      "memory usage: 56.8+ MB\n"
     ]
    }
   ],
   "source": [
    "crash_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(crash_data, weather_data, how= 'left', on = 'rounded_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 169404 entries, 0 to 169403\n",
      "Data columns (total 71 columns):\n",
      " #   Column                              Non-Null Count   Dtype         \n",
      "---  ------                              --------------   -----         \n",
      " 0   crash_id                            169404 non-null  int64         \n",
      " 1   crash_fatal_fl                      169404 non-null  object        \n",
      " 2   crash_date                          169404 non-null  datetime64[ns]\n",
      " 3   crash_time                          169404 non-null  object        \n",
      " 4   rpt_block_num                       147029 non-null  object        \n",
      " 5   rpt_street_pfx                      94899 non-null   object        \n",
      " 6   rpt_street_name                     169398 non-null  object        \n",
      " 7   rpt_street_sfx                      124033 non-null  object        \n",
      " 8   crash_speed_limit                   169399 non-null  float64       \n",
      " 9   road_constr_zone_fl                 169399 non-null  object        \n",
      " 10  latitude                            163249 non-null  float64       \n",
      " 11  longitude                           163249 non-null  float64       \n",
      " 12  street_name                         169399 non-null  object        \n",
      " 13  street_nbr                          70413 non-null   float64       \n",
      " 14  street_name_2                       73519 non-null   object        \n",
      " 15  crash_sev_id                        169399 non-null  float64       \n",
      " 16  sus_serious_injry_cnt               169404 non-null  int64         \n",
      " 17  nonincap_injry_cnt                  169401 non-null  float64       \n",
      " 18  poss_injry_cnt                      169401 non-null  float64       \n",
      " 19  non_injry_cnt                       169401 non-null  float64       \n",
      " 20  unkn_injry_cnt                      169399 non-null  float64       \n",
      " 21  tot_injry_cnt                       169400 non-null  float64       \n",
      " 22  death_cnt                           169404 non-null  int64         \n",
      " 23  pedestrian_fl                       4520 non-null    object        \n",
      " 24  motor_vehicle_fl                    167872 non-null  object        \n",
      " 25  motorcycle_fl                       4565 non-null    object        \n",
      " 26  bicycle_fl                          3187 non-null    object        \n",
      " 27  other_fl                            5333 non-null    object        \n",
      " 28  point                               163249 non-null  object        \n",
      " 29  apd_confirmed_fatality              169404 non-null  object        \n",
      " 30  apd_confirmed_death_count           169402 non-null  float64       \n",
      " 31  motor_vehicle_death_count           169404 non-null  int64         \n",
      " 32  motor_vehicle_serious_injury_count  169404 non-null  int64         \n",
      " 33  bicycle_death_count                 169404 non-null  int64         \n",
      " 34  bicycle_serious_injury_count        169404 non-null  int64         \n",
      " 35  pedestrian_death_count              169404 non-null  int64         \n",
      " 36  pedestrian_serious_injury_count     169404 non-null  int64         \n",
      " 37  motorcycle_death_count              169404 non-null  int64         \n",
      " 38  motorcycle_serious_injury_count     169404 non-null  int64         \n",
      " 39  other_death_count                   169404 non-null  int64         \n",
      " 40  other_serious_injury_count          169404 non-null  int64         \n",
      " 41  onsys_fl                            169399 non-null  object        \n",
      " 42  private_dr_fl                       169399 non-null  object        \n",
      " 43  crash_date_only                     169404 non-null  datetime64[ns]\n",
      " 44  Weekday                             169404 non-null  int64         \n",
      " 45  rounded_time                        169404 non-null  datetime64[ns]\n",
      " 46  dt                                  169404 non-null  int64         \n",
      " 47  dt_iso                              169404 non-null  object        \n",
      " 48  timezone                            169404 non-null  int64         \n",
      " 49  city_name                           169404 non-null  object        \n",
      " 50  lat                                 169404 non-null  float64       \n",
      " 51  lon                                 169404 non-null  float64       \n",
      " 52  temp                                169404 non-null  float64       \n",
      " 53  feels_like                          169404 non-null  float64       \n",
      " 54  temp_min                            169404 non-null  float64       \n",
      " 55  temp_max                            169404 non-null  float64       \n",
      " 56  pressure                            169404 non-null  int64         \n",
      " 57  sea_level                           0 non-null       float64       \n",
      " 58  grnd_level                          0 non-null       float64       \n",
      " 59  humidity                            169404 non-null  int64         \n",
      " 60  wind_speed                          169404 non-null  float64       \n",
      " 61  wind_deg                            169404 non-null  int64         \n",
      " 62  rain_1h                             22072 non-null   float64       \n",
      " 63  rain_3h                             3097 non-null    float64       \n",
      " 64  snow_1h                             226 non-null     float64       \n",
      " 65  snow_3h                             18 non-null      float64       \n",
      " 66  clouds_all                          169404 non-null  int64         \n",
      " 67  weather_id                          169404 non-null  int64         \n",
      " 68  weather_main                        169404 non-null  object        \n",
      " 69  weather_description                 169404 non-null  object        \n",
      " 70  weather_icon                        169404 non-null  object        \n",
      "dtypes: datetime64[ns](3), float64(24), int64(21), object(23)\n",
      "memory usage: 93.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "161824\n",
      "161824\n"
     ]
    }
   ],
   "source": [
    "#Very confused why there are extra rows...\n",
    "print(crash_data['crash_id'].nunique())\n",
    "print(df['crash_id'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17552596    3\n",
       "15554103    3\n",
       "13736215    3\n",
       "14815982    3\n",
       "15111518    3\n",
       "           ..\n",
       "13216737    1\n",
       "16532215    1\n",
       "13490863    1\n",
       "15604897    1\n",
       "15204352    1\n",
       "Name: crash_id, Length: 161824, dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['crash_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    155103\n",
       "2      5862\n",
       "3       859\n",
       "Name: crash_id, dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['crash_id'].value_counts().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30846   2020-02-06 01:00:00\n",
       "30847   2020-02-06 01:00:00\n",
       "30848   2020-02-06 01:00:00\n",
       "Name: rounded_time, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['crash_id'] == 17552596]['rounded_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>dt</th>\n",
       "      <th>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>temp</th>\n",
       "      <th>feels_like</th>\n",
       "      <th>temp_min</th>\n",
       "      <th>temp_max</th>\n",
       "      <th>...</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>weather_id</th>\n",
       "      <th>weather_main</th>\n",
       "      <th>weather_description</th>\n",
       "      <th>weather_icon</th>\n",
       "      <th>rounded_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>109023</th>\n",
       "      <td>1580968800</td>\n",
       "      <td>2020-02-06 06:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>32.41</td>\n",
       "      <td>24.89</td>\n",
       "      <td>30.99</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>500</td>\n",
       "      <td>Rain</td>\n",
       "      <td>light rain</td>\n",
       "      <td>10n</td>\n",
       "      <td>2020-02-06 01:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109024</th>\n",
       "      <td>1580968800</td>\n",
       "      <td>2020-02-06 06:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>32.41</td>\n",
       "      <td>24.89</td>\n",
       "      <td>30.99</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>600</td>\n",
       "      <td>Snow</td>\n",
       "      <td>light snow</td>\n",
       "      <td>13n</td>\n",
       "      <td>2020-02-06 01:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109025</th>\n",
       "      <td>1580968800</td>\n",
       "      <td>2020-02-06 06:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>32.41</td>\n",
       "      <td>24.89</td>\n",
       "      <td>30.99</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>701</td>\n",
       "      <td>Mist</td>\n",
       "      <td>mist</td>\n",
       "      <td>50n</td>\n",
       "      <td>2020-02-06 01:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                dt                         dt_iso  timezone city_name  \\\n",
       "109023  1580968800  2020-02-06 06:00:00 +0000 UTC    -21600    Austin   \n",
       "109024  1580968800  2020-02-06 06:00:00 +0000 UTC    -21600    Austin   \n",
       "109025  1580968800  2020-02-06 06:00:00 +0000 UTC    -21600    Austin   \n",
       "\n",
       "              lat        lon   temp  feels_like  temp_min  temp_max  ...  \\\n",
       "109023  30.267153 -97.743061  32.41       24.89     30.99      34.0  ...   \n",
       "109024  30.267153 -97.743061  32.41       24.89     30.99      34.0  ...   \n",
       "109025  30.267153 -97.743061  32.41       24.89     30.99      34.0  ...   \n",
       "\n",
       "        rain_1h  rain_3h  snow_1h  snow_3h  clouds_all  weather_id  \\\n",
       "109023     0.51      NaN     0.33      NaN          90         500   \n",
       "109024     0.51      NaN     0.33      NaN          90         600   \n",
       "109025     0.51      NaN     0.33      NaN          90         701   \n",
       "\n",
       "        weather_main  weather_description  weather_icon        rounded_time  \n",
       "109023          Rain           light rain           10n 2020-02-06 01:00:00  \n",
       "109024          Snow           light snow           13n 2020-02-06 01:00:00  \n",
       "109025          Mist                 mist           50n 2020-02-06 01:00:00  \n",
       "\n",
       "[3 rows x 26 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repeat_time = df['rounded_time'][30846]\n",
    "weather_data.loc[weather_data['rounded_time'] == repeat_time]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115449\n",
      "112200\n"
     ]
    }
   ],
   "source": [
    "#seems like there were repeats in rounded_time in the weather data.\n",
    "print(len(weather_data['dt_iso']))\n",
    "print(weather_data['dt_iso'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115449\n",
      "1\n"
     ]
    }
   ],
   "source": [
    "print(len(weather_data['lon']))\n",
    "print(weather_data['lon'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "weath_date_vc = weather_data['rounded_time'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "repeat_3 = weath_date_vc.loc[weath_date_vc == 3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2008-03-06 15:00:00\n",
      "2020-09-09 07:00:00\n"
     ]
    }
   ],
   "source": [
    "print(repeat_3.index.min())\n",
    "print(repeat_3.index.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>dt</th>\n",
       "      <th>dt_iso</th>\n",
       "      <th>timezone</th>\n",
       "      <th>city_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>temp</th>\n",
       "      <th>feels_like</th>\n",
       "      <th>temp_min</th>\n",
       "      <th>temp_max</th>\n",
       "      <th>...</th>\n",
       "      <th>rain_1h</th>\n",
       "      <th>rain_3h</th>\n",
       "      <th>snow_1h</th>\n",
       "      <th>snow_3h</th>\n",
       "      <th>clouds_all</th>\n",
       "      <th>weather_id</th>\n",
       "      <th>weather_main</th>\n",
       "      <th>weather_description</th>\n",
       "      <th>weather_icon</th>\n",
       "      <th>rounded_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1199145600</td>\n",
       "      <td>2008-01-01 00:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>57.49</td>\n",
       "      <td>46.69</td>\n",
       "      <td>55.74</td>\n",
       "      <td>57.92</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "      <td>2007-12-31 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1199149200</td>\n",
       "      <td>2008-01-01 01:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>55.13</td>\n",
       "      <td>47.39</td>\n",
       "      <td>53.96</td>\n",
       "      <td>57.22</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "      <td>2007-12-31 20:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1199152800</td>\n",
       "      <td>2008-01-01 02:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>52.79</td>\n",
       "      <td>42.84</td>\n",
       "      <td>51.08</td>\n",
       "      <td>54.89</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "      <td>2007-12-31 21:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1199156400</td>\n",
       "      <td>2008-01-01 03:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>50.65</td>\n",
       "      <td>38.66</td>\n",
       "      <td>48.98</td>\n",
       "      <td>51.98</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "      <td>2007-12-31 22:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1199160000</td>\n",
       "      <td>2008-01-01 04:00:00 +0000 UTC</td>\n",
       "      <td>-21600</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>49.14</td>\n",
       "      <td>39.16</td>\n",
       "      <td>47.69</td>\n",
       "      <td>50.00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01n</td>\n",
       "      <td>2007-12-31 23:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115444</th>\n",
       "      <td>1603047600</td>\n",
       "      <td>2020-10-18 19:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>87.19</td>\n",
       "      <td>86.67</td>\n",
       "      <td>84.00</td>\n",
       "      <td>90.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1.02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>501</td>\n",
       "      <td>Rain</td>\n",
       "      <td>moderate rain</td>\n",
       "      <td>10d</td>\n",
       "      <td>2020-10-18 14:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115445</th>\n",
       "      <td>1603051200</td>\n",
       "      <td>2020-10-18 20:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>88.79</td>\n",
       "      <td>87.03</td>\n",
       "      <td>87.01</td>\n",
       "      <td>91.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>500</td>\n",
       "      <td>Rain</td>\n",
       "      <td>light rain</td>\n",
       "      <td>10d</td>\n",
       "      <td>2020-10-18 15:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115446</th>\n",
       "      <td>1603054800</td>\n",
       "      <td>2020-10-18 21:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>89.67</td>\n",
       "      <td>87.60</td>\n",
       "      <td>88.00</td>\n",
       "      <td>91.40</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>801</td>\n",
       "      <td>Clouds</td>\n",
       "      <td>few clouds</td>\n",
       "      <td>02d</td>\n",
       "      <td>2020-10-18 16:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115447</th>\n",
       "      <td>1603058400</td>\n",
       "      <td>2020-10-18 22:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>89.22</td>\n",
       "      <td>83.39</td>\n",
       "      <td>88.00</td>\n",
       "      <td>91.00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01d</td>\n",
       "      <td>2020-10-18 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115448</th>\n",
       "      <td>1603062000</td>\n",
       "      <td>2020-10-18 23:00:00 +0000 UTC</td>\n",
       "      <td>-18000</td>\n",
       "      <td>Austin</td>\n",
       "      <td>30.267153</td>\n",
       "      <td>-97.743061</td>\n",
       "      <td>87.48</td>\n",
       "      <td>85.59</td>\n",
       "      <td>86.00</td>\n",
       "      <td>90.00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>800</td>\n",
       "      <td>Clear</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>01d</td>\n",
       "      <td>2020-10-18 18:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>115449 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                dt                         dt_iso  timezone city_name  \\\n",
       "0       1199145600  2008-01-01 00:00:00 +0000 UTC    -21600    Austin   \n",
       "1       1199149200  2008-01-01 01:00:00 +0000 UTC    -21600    Austin   \n",
       "2       1199152800  2008-01-01 02:00:00 +0000 UTC    -21600    Austin   \n",
       "3       1199156400  2008-01-01 03:00:00 +0000 UTC    -21600    Austin   \n",
       "4       1199160000  2008-01-01 04:00:00 +0000 UTC    -21600    Austin   \n",
       "...            ...                            ...       ...       ...   \n",
       "115444  1603047600  2020-10-18 19:00:00 +0000 UTC    -18000    Austin   \n",
       "115445  1603051200  2020-10-18 20:00:00 +0000 UTC    -18000    Austin   \n",
       "115446  1603054800  2020-10-18 21:00:00 +0000 UTC    -18000    Austin   \n",
       "115447  1603058400  2020-10-18 22:00:00 +0000 UTC    -18000    Austin   \n",
       "115448  1603062000  2020-10-18 23:00:00 +0000 UTC    -18000    Austin   \n",
       "\n",
       "              lat        lon   temp  feels_like  temp_min  temp_max  ...  \\\n",
       "0       30.267153 -97.743061  57.49       46.69     55.74     57.92  ...   \n",
       "1       30.267153 -97.743061  55.13       47.39     53.96     57.22  ...   \n",
       "2       30.267153 -97.743061  52.79       42.84     51.08     54.89  ...   \n",
       "3       30.267153 -97.743061  50.65       38.66     48.98     51.98  ...   \n",
       "4       30.267153 -97.743061  49.14       39.16     47.69     50.00  ...   \n",
       "...           ...        ...    ...         ...       ...       ...  ...   \n",
       "115444  30.267153 -97.743061  87.19       86.67     84.00     90.00  ...   \n",
       "115445  30.267153 -97.743061  88.79       87.03     87.01     91.00  ...   \n",
       "115446  30.267153 -97.743061  89.67       87.60     88.00     91.40  ...   \n",
       "115447  30.267153 -97.743061  89.22       83.39     88.00     91.00  ...   \n",
       "115448  30.267153 -97.743061  87.48       85.59     86.00     90.00  ...   \n",
       "\n",
       "        rain_1h  rain_3h  snow_1h  snow_3h  clouds_all  weather_id  \\\n",
       "0           NaN      NaN      NaN      NaN           1         800   \n",
       "1           NaN      NaN      NaN      NaN           1         800   \n",
       "2           NaN      NaN      NaN      NaN           1         800   \n",
       "3           NaN      NaN      NaN      NaN           1         800   \n",
       "4           NaN      NaN      NaN      NaN           1         800   \n",
       "...         ...      ...      ...      ...         ...         ...   \n",
       "115444     1.02      NaN      NaN      NaN           1         501   \n",
       "115445     0.51      NaN      NaN      NaN          20         500   \n",
       "115446      NaN      NaN      NaN      NaN          20         801   \n",
       "115447      NaN      NaN      NaN      NaN           1         800   \n",
       "115448      NaN      NaN      NaN      NaN           1         800   \n",
       "\n",
       "        weather_main  weather_description  weather_icon        rounded_time  \n",
       "0              Clear         sky is clear           01n 2007-12-31 19:00:00  \n",
       "1              Clear         sky is clear           01n 2007-12-31 20:00:00  \n",
       "2              Clear         sky is clear           01n 2007-12-31 21:00:00  \n",
       "3              Clear         sky is clear           01n 2007-12-31 22:00:00  \n",
       "4              Clear         sky is clear           01n 2007-12-31 23:00:00  \n",
       "...              ...                  ...           ...                 ...  \n",
       "115444          Rain        moderate rain           10d 2020-10-18 14:00:00  \n",
       "115445          Rain           light rain           10d 2020-10-18 15:00:00  \n",
       "115446        Clouds           few clouds           02d 2020-10-18 16:00:00  \n",
       "115447         Clear         sky is clear           01d 2020-10-18 17:00:00  \n",
       "115448         Clear         sky is clear           01d 2020-10-18 18:00:00  \n",
       "\n",
       "[115449 rows x 26 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_data.loc[weather_data['rounded_time'].isin(repeat_3).index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2010-01-01 00:59:00')"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data['crash_date'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-08-31 23:38:00')"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crash_data['crash_date'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}