data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "58f40e8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a6cea619",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Start_Time', 'End_Time', 'Distance(mi)', 'County', 'State', 'Weather_Condition', 'Sunrise_Sunset']\n",
    "df = pd.read_csv('data/raw_data.csv', usecols=cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ea0221fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Fair': [],\n",
       " 'Mostly Cloudy': ['Cloudy'],\n",
       " 'Cloudy': ['Cloudy'],\n",
       " 'Partly Cloudy': ['Cloudy'],\n",
       " 'Clear': [],\n",
       " 'Light Rain': ['Rain'],\n",
       " 'Overcast': ['Cloudy'],\n",
       " nan: [],\n",
       " 'Scattered Clouds': ['Cloudy'],\n",
       " 'Light Snow': ['Snow'],\n",
       " 'Fog': ['Visibility'],\n",
       " 'Haze': ['Precipitation'],\n",
       " 'Rain': ['Rain'],\n",
       " 'Fair / Windy': ['Windy'],\n",
       " 'Heavy Rain': ['Rain'],\n",
       " 'Smoke': ['Visibility'],\n",
       " 'Light Drizzle': ['Rain'],\n",
       " 'Thunder in the Vicinity': ['Thunder'],\n",
       " 'Cloudy / Windy': ['Cloudy', 'Windy'],\n",
       " 'T-Storm': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Mostly Cloudy / Windy': ['Cloudy', 'Windy'],\n",
       " 'Thunder': ['Thunder'],\n",
       " 'Snow': ['Snow'],\n",
       " 'Light Rain with Thunder': ['Rain', 'Thunder'],\n",
       " 'Partly Cloudy / Windy': ['Cloudy', 'Windy'],\n",
       " 'Wintry Mix': ['Rain', 'Snow'],\n",
       " 'Heavy T-Storm': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Light Rain / Windy': ['Rain', 'Visibility'],\n",
       " 'Light Snow / Windy': ['Snow', 'Windy'],\n",
       " 'Drizzle': ['Rain'],\n",
       " 'Heavy Snow': ['Snow'],\n",
       " 'Rain / Windy': ['Rain', 'Windy'],\n",
       " 'Light Thunderstorms and Rain': ['Rain', 'Thunder', 'Windy'],\n",
       " 'N/A Precipitation': ['Precipitation'],\n",
       " 'Patches of Fog': ['Visibility'],\n",
       " 'Mist': ['Rain'],\n",
       " 'Thunderstorm': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Shallow Fog': ['Visibility'],\n",
       " 'Light Freezing Rain': ['Rain'],\n",
       " 'Heavy Rain / Windy': ['Rain', 'Windy'],\n",
       " 'Showers in the Vicinity': ['Rain'],\n",
       " 'Haze / Windy': ['Precipitation', 'Windy'],\n",
       " 'Heavy Thunderstorms and Rain': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Thunderstorms and Rain': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Snow / Windy': ['Snow', 'Windy'],\n",
       " 'Light Freezing Fog': ['Visibility'],\n",
       " 'Heavy T-Storm / Windy': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Light Freezing Drizzle': ['Rain'],\n",
       " 'Fog / Windy': ['Visibility', 'Windy'],\n",
       " 'T-Storm / Windy': ['Rain', 'Thunder', 'Windy'],\n",
       " 'Thunder / Windy': ['Thunder', 'Windy'],\n",
       " 'Heavy Snow / Windy': ['Snow', 'Windy'],\n",
       " 'Blowing Snow': ['Snow'],\n",
       " 'Blowing Dust / Windy': ['Visibility', 'Windy'],\n",
       " 'Blowing Dust': ['Visibility'],\n",
       " 'Heavy Drizzle': ['Rain'],\n",
       " 'Light Rain Shower': ['Rain'],\n",
       " 'Drizzle and Fog': ['Rain', 'Visibility'],\n",
       " 'Snow and Sleet': ['Rain', 'Snow'],\n",
       " 'Light Drizzle / Windy': ['Rain', 'Windy'],\n",
       " 'Blowing Snow / Windy': ['Snow', 'Windy'],\n",
       " 'Light Ice Pellets': ['Precipitation'],\n",
       " 'Wintry Mix / Windy': ['Rain', 'Snow', 'Windy'],\n",
       " 'Light Sleet': ['Rain', 'Snow'],\n",
       " 'Widespread Dust': ['Visibility'],\n",
       " 'Smoke / Windy': ['Visibility', 'Windy'],\n",
       " 'Sleet': ['Rain', 'Snow'],\n",
       " 'Freezing Rain': ['Rain'],\n",
       " 'Light Snow and Sleet': ['Rain', 'Snow'],\n",
       " 'Light Rain Showers': ['Rain'],\n",
       " 'Rain Shower': ['Rain'],\n",
       " 'Ice Pellets': ['Precipitation'],\n",
       " 'Light Freezing Rain / Windy': ['Rain', 'Windy'],\n",
       " 'Small Hail': ['Rain', 'Precipitation'],\n",
       " 'Rain Showers': ['Rain'],\n",
       " 'Partial Fog': ['Visibility'],\n",
       " 'Light Snow Shower': ['Snow'],\n",
       " 'Squalls / Windy': ['Rain', 'Snow', 'Windy'],\n",
       " 'Widespread Dust / Windy': ['Visibility', 'Windy'],\n",
       " 'Snow and Sleet / Windy': ['Rain', 'Snow', 'Windy'],\n",
       " 'Light Snow and Sleet / Windy': ['Rain', 'Snow', 'Windy'],\n",
       " 'Sand / Dust Whirlwinds': ['Visibility', 'Windy'],\n",
       " 'Heavy Sleet': ['Rain', 'Snow'],\n",
       " 'Squalls': ['Rain', 'Snow', 'Windy'],\n",
       " 'Freezing Drizzle': ['Rain'],\n",
       " 'Light Snow with Thunder': ['Snow', 'Thunder'],\n",
       " 'Drizzle / Windy': ['Rain', 'Windy'],\n",
       " 'Tornado': ['Visibility', 'Windy'],\n",
       " 'Snow Grains': ['Snow'],\n",
       " 'Light Sleet / Windy': ['Rain', 'Snow', 'Windy'],\n",
       " 'Light Rain Shower / Windy': ['Rain', 'Windy'],\n",
       " 'Light Snow Showers': ['Rain', 'Snow'],\n",
       " 'Light Thunderstorms and Snow': ['Rain', 'Snow', 'Thunder', 'Windy'],\n",
       " 'Thunder / Wintry Mix': ['Rain', 'Snow', 'Thunder'],\n",
       " 'Volcanic Ash': ['Precipitation'],\n",
       " 'Patches of Fog / Windy': ['Visibility', 'Windy'],\n",
       " 'Heavy Thunderstorms and Snow': ['Rain', 'Snow', 'Thunder', 'Windy'],\n",
       " 'Hail': ['Rain', 'Precipitation'],\n",
       " 'Heavy Rain Shower': ['Rain'],\n",
       " 'Heavy Snow with Thunder': ['Snow', 'Thunder'],\n",
       " 'Sand': ['Visibility'],\n",
       " 'Freezing Rain / Windy': ['Rain', 'Windy'],\n",
       " 'Heavy Ice Pellets': ['Precipitation'],\n",
       " 'Funnel Cloud': ['Cloudy', 'Visibility', 'Windy'],\n",
       " 'Snow and Thunder / Windy': ['Snow', 'Thunder', 'Windy'],\n",
       " 'Blowing Snow Nearby': ['Snow', 'Windy'],\n",
       " 'Sand / Windy': ['Visibility', 'Windy'],\n",
       " 'Duststorm': ['Visibility', 'Windy'],\n",
       " 'Thunder and Hail': ['Rain', 'Precipitation', 'Thunder'],\n",
       " 'Heavy Freezing Drizzle': ['Rain'],\n",
       " 'Thunder / Wintry Mix / Windy': ['Rain', 'Snow', 'Thunder'],\n",
       " 'Light Blowing Snow': ['Snow', 'Windy'],\n",
       " 'Light Fog': ['Visibility'],\n",
       " 'Dust Whirls': ['Visibility', 'Windy'],\n",
       " 'Heavy Rain Shower / Windy': ['Rain', 'Windy'],\n",
       " 'Sand / Dust Whirlwinds / Windy': ['Visibility', 'Windy'],\n",
       " 'Heavy Freezing Rain': ['Rain'],\n",
       " 'Blowing Sand': ['Visibility', 'Windy'],\n",
       " 'Mist / Windy': ['Rain', 'Windy'],\n",
       " 'Sleet / Windy': ['Rain', 'Snow', 'Windy'],\n",
       " 'Sand / Dust Whirls Nearby': ['Visibility', 'Windy'],\n",
       " 'Drifting Snow': ['Snow'],\n",
       " 'Heavy Blowing Snow': ['Snow', 'Windy'],\n",
       " 'Low Drifting Snow': ['Snow'],\n",
       " 'Heavy Rain Showers': ['Rain'],\n",
       " 'Light Haze': ['Precipitation'],\n",
       " 'Heavy Thunderstorms with Small Hail': ['Rain',\n",
       "  'Precipitation',\n",
       "  'Thunder',\n",
       "  'Windy'],\n",
       " 'Thunder and Hail / Windy': ['Rain', 'Thunder', 'Windy']}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_df = pd.read_csv('data/weather_conditions.csv')\n",
    "\n",
    "weather_cats = list(weather_df.columns)[1:]\n",
    "weather_dict = {}\n",
    "\n",
    "for i in range(len(weather_df)):\n",
    "    \n",
    "    row = weather_df.iloc[i,:]\n",
    "    \n",
    "    weather_dict[row['Original']] = [wc for wc in weather_cats if row[wc] == 'X']\n",
    "    \n",
    "weather_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "782edc0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "states = {\n",
    "    'AK': 'Alaska',\n",
    "    'AL': 'Alabama',\n",
    "    'AR': 'Arkansas',\n",
    "    'AZ': 'Arizona',\n",
    "    'CA': 'California',\n",
    "    'CO': 'Colorado',\n",
    "    'CT': 'Connecticut',\n",
    "    'DC': 'District of Columbia',\n",
    "    'DE': 'Delaware',\n",
    "    'FL': 'Florida',\n",
    "    'GA': 'Georgia',\n",
    "    'HI': 'Hawaii',\n",
    "    'IA': 'Iowa',\n",
    "    'ID': 'Idaho',\n",
    "    'IL': 'Illinois',\n",
    "    'IN': 'Indiana',\n",
    "    'KS': 'Kansas',\n",
    "    'KY': 'Kentucky',\n",
    "    'LA': 'Louisiana',\n",
    "    'MA': 'Massachusetts',\n",
    "    'MD': 'Maryland',\n",
    "    'ME': 'Maine',\n",
    "    'MI': 'Michigan',\n",
    "    'MN': 'Minnesota',\n",
    "    'MO': 'Missouri',\n",
    "    'MS': 'Mississippi',\n",
    "    'MT': 'Montana',\n",
    "    'NC': 'North Carolina',\n",
    "    'ND': 'North Dakota',\n",
    "    'NE': 'Nebraska',\n",
    "    'NH': 'New Hampshire',\n",
    "    'NJ': 'New Jersey',\n",
    "    'NM': 'New Mexico',\n",
    "    'NV': 'Nevada',\n",
    "    'NY': 'New York',\n",
    "    'OH': 'Ohio',\n",
    "    'OK': 'Oklahoma',\n",
    "    'OR': 'Oregon',\n",
    "    'PA': 'Pennsylvania',\n",
    "    'RI': 'Rhode Island',\n",
    "    'SC': 'South Carolina',\n",
    "    'SD': 'South Dakota',\n",
    "    'TN': 'Tennessee',\n",
    "    'TX': 'Texas',\n",
    "    'UT': 'Utah',\n",
    "    'VA': 'Virginia',\n",
    "    'VT': 'Vermont',\n",
    "    'WA': 'Washington',\n",
    "    'WI': 'Wisconsin',\n",
    "    'WV': 'West Virginia',\n",
    "    'WY': 'Wyoming'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "39a17338",
   "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>Start_Time</th>\n",
       "      <th>End_Time</th>\n",
       "      <th>Distance(mi)</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Weather_Condition</th>\n",
       "      <th>Sunrise_Sunset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-02-08 00:37:08</td>\n",
       "      <td>2016-02-08 06:37:08</td>\n",
       "      <td>3.230</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>OH</td>\n",
       "      <td>Light Rain</td>\n",
       "      <td>Night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-02-08 05:56:20</td>\n",
       "      <td>2016-02-08 11:56:20</td>\n",
       "      <td>0.747</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>OH</td>\n",
       "      <td>Light Rain</td>\n",
       "      <td>Night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-02-08 06:15:39</td>\n",
       "      <td>2016-02-08 12:15:39</td>\n",
       "      <td>0.055</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>OH</td>\n",
       "      <td>Overcast</td>\n",
       "      <td>Night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-02-08 06:51:45</td>\n",
       "      <td>2016-02-08 12:51:45</td>\n",
       "      <td>0.123</td>\n",
       "      <td>Summit</td>\n",
       "      <td>OH</td>\n",
       "      <td>Overcast</td>\n",
       "      <td>Night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-02-08 07:53:43</td>\n",
       "      <td>2016-02-08 13:53:43</td>\n",
       "      <td>0.500</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>OH</td>\n",
       "      <td>Light Rain</td>\n",
       "      <td>Day</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Start_Time             End_Time  Distance(mi)      County State  \\\n",
       "0  2016-02-08 00:37:08  2016-02-08 06:37:08         3.230    Franklin    OH   \n",
       "1  2016-02-08 05:56:20  2016-02-08 11:56:20         0.747  Montgomery    OH   \n",
       "2  2016-02-08 06:15:39  2016-02-08 12:15:39         0.055    Hamilton    OH   \n",
       "3  2016-02-08 06:51:45  2016-02-08 12:51:45         0.123      Summit    OH   \n",
       "4  2016-02-08 07:53:43  2016-02-08 13:53:43         0.500    Hamilton    OH   \n",
       "\n",
       "  Weather_Condition Sunrise_Sunset  \n",
       "0        Light Rain          Night  \n",
       "1        Light Rain          Night  \n",
       "2          Overcast          Night  \n",
       "3          Overcast          Night  \n",
       "4        Light Rain            Day  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "f7acdb21",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Timestamp year month day time_of_day day_of_week duration distance county state day_or_night ... weather\n",
    "\n",
    "\n",
    "def time_of_day(x):\n",
    "    return ('Morning' if 5 <= x.hour <= 11 \n",
    "            else 'Afternoon' if 12 <= x.hour <= 12+4\n",
    "            else 'Evening' if 12+5 <= x.hour <= 12+8\n",
    "            else 'Night')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a93f48f5",
   "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>Timestamp</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Time_of_Day</th>\n",
       "      <th>Day_of_Week</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Distance</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Cloudy</th>\n",
       "      <th>Rain</th>\n",
       "      <th>Snow</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Thunder</th>\n",
       "      <th>Visibility</th>\n",
       "      <th>Windy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-02-08 00:37:08</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Night</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>3.230</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-02-08 05:56:20</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.747</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-02-08 06:15:39</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.055</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-02-08 06:51:45</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.123</td>\n",
       "      <td>Summit</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-02-08 07:53:43</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.500</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845337</th>\n",
       "      <td>2019-08-23 18:03:25</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>0.543</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845338</th>\n",
       "      <td>2019-08-23 19:11:30</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>0.338</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845339</th>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>0.561</td>\n",
       "      <td>Orange</td>\n",
       "      <td>California</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845340</th>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>0.772</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845341</th>\n",
       "      <td>2019-08-23 18:52:06</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>0.537</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2845342 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp  Year  Month  Day Time_of_Day  Day_of_Week  \\\n",
       "0        2016-02-08 00:37:08  2016      2    8       Night            0   \n",
       "1        2016-02-08 05:56:20  2016      2    8     Morning            0   \n",
       "2        2016-02-08 06:15:39  2016      2    8     Morning            0   \n",
       "3        2016-02-08 06:51:45  2016      2    8     Morning            0   \n",
       "4        2016-02-08 07:53:43  2016      2    8     Morning            0   \n",
       "...                      ...   ...    ...  ...         ...          ...   \n",
       "2845337  2019-08-23 18:03:25  2019      8   23     Evening            4   \n",
       "2845338  2019-08-23 19:11:30  2019      8   23     Evening            4   \n",
       "2845339  2019-08-23 19:00:21  2019      8   23     Evening            4   \n",
       "2845340  2019-08-23 19:00:21  2019      8   23     Evening            4   \n",
       "2845341  2019-08-23 18:52:06  2019      8   23     Evening            4   \n",
       "\n",
       "         Duration  Distance          County       State  Cloudy  Rain  Snow  \\\n",
       "0             360     3.230        Franklin        Ohio       0     1     0   \n",
       "1             360     0.747      Montgomery        Ohio       0     1     0   \n",
       "2             360     0.055        Hamilton        Ohio       1     0     0   \n",
       "3             360     0.123          Summit        Ohio       1     0     0   \n",
       "4             360     0.500        Hamilton        Ohio       0     1     0   \n",
       "...           ...       ...             ...         ...     ...   ...   ...   \n",
       "2845337        28     0.543       Riverside  California       0     0     0   \n",
       "2845338        26     0.338       San Diego  California       0     0     0   \n",
       "2845339        28     0.561          Orange  California       1     0     0   \n",
       "2845340        29     0.772     Los Angeles  California       0     0     0   \n",
       "2845341        29     0.537  San Bernardino  California       0     0     0   \n",
       "\n",
       "         Precipitation  Thunder  Visibility  Windy  \n",
       "0                    0        0           0      0  \n",
       "1                    0        0           0      0  \n",
       "2                    0        0           0      0  \n",
       "3                    0        0           0      0  \n",
       "4                    0        0           0      0  \n",
       "...                ...      ...         ...    ...  \n",
       "2845337              0        0           0      0  \n",
       "2845338              0        0           0      0  \n",
       "2845339              0        0           0      0  \n",
       "2845340              0        0           0      0  \n",
       "2845341              0        0           0      0  \n",
       "\n",
       "[2845342 rows x 17 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = pd.DataFrame({\n",
    "    'Timestamp' : df['Start_Time'],\n",
    "    'Year' : pd.to_datetime(df['Start_Time']).apply(lambda x: x.year),\n",
    "    'Month' : pd.to_datetime(df['Start_Time']).apply(lambda x: x.month),\n",
    "    'Day' : pd.to_datetime(df['Start_Time']).apply(lambda x: x.day),\n",
    "    'Time_of_Day' : pd.to_datetime(df['Start_Time']).apply(time_of_day),\n",
    "    'Day_of_Week' : pd.to_datetime(df['Start_Time']).apply(lambda x: x.day_of_week),\n",
    "    'Duration' : (pd.to_datetime(df['End_Time']) - pd.to_datetime(df['Start_Time'])).apply(lambda x: x.seconds // 60),\n",
    "    'Distance' : df['Distance(mi)'],\n",
    "    'County' : df['County'],\n",
    "    'State' : df['State'].map(states),\n",
    "})\n",
    "\n",
    "for wc in weather_cats:\n",
    "    df_final[wc] = df['Weather_Condition'].apply(lambda x: int(wc in weather_dict[x]))\n",
    "\n",
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "fb9d0b4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "1cc3f341",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cloudy 0.39000338096439724\n",
      "Rain 0.074649022859115\n",
      "Snow 0.020327960575565258\n",
      "Precipitation 0.0134082300124203\n",
      "Thunder 0.011381760083673597\n",
      "Visibility 0.019363577383667764\n",
      "Windy 0.01840973773978664\n"
     ]
    }
   ],
   "source": [
    "for wc in weather_cats:\n",
    "    \n",
    "    print(wc,(df_final[wc] == 1).mean())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "2ced83e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv('data/us_accidents.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c4b39de1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = pd.read_csv('data/us_accidents.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e5f7b2c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1f465be0",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('geography/counties.json', 'r') as f:\n",
    "    counties_dict = json.load(f)\n",
    "\n",
    "with open('geography/states.json', 'r') as f:\n",
    "    states_dict = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dcb8bb23",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_ids = {state['properties']['NAME'] : state['properties']['STATE'] for state in states_dict['features']}\n",
    "id_states = {state['properties']['STATE'] : state['properties']['NAME'] for state in states_dict['features']}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c0714f89",
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_counties = set([(county['properties']['NAME'], county['properties']['STATE']) for county in counties_dict['features']])\n",
    "data_counties = set(list(zip(df_final['County'], df_final['State'].map(state_ids))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "397bb0b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Leelanau', '26'),\n",
       " ('Humacao', '72'),\n",
       " ('Jackson', '13'),\n",
       " ('Stark', '38'),\n",
       " ('Somervell', '48'),\n",
       " ('Monroe', '26'),\n",
       " ('Henry', '13'),\n",
       " ('Piscataquis', '23'),\n",
       " ('Butler', '19'),\n",
       " ('Grant', '20')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(geo_counties)[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8fce0dd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cs = data_counties.difference(geo_counties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c90acf77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('Alexandria City', '51'),\n",
       " ('Baltimore City', '24'),\n",
       " ('Baltimore County', '24'),\n",
       " ('Bedford County', '51'),\n",
       " ('Buena Vista City', '51'),\n",
       " ('Charlottesville City', '51'),\n",
       " ('Chesapeake City', '51'),\n",
       " ('Covington City', '51'),\n",
       " ('Danville City', '51'),\n",
       " ('De Kalb', '01'),\n",
       " ('De Kalb', '18'),\n",
       " ('De Soto', '12'),\n",
       " ('Dekalb', '13'),\n",
       " ('Dekalb', '17'),\n",
       " ('Desoto', '28'),\n",
       " ('Dewitt', '17'),\n",
       " ('District Of Columbia', '11'),\n",
       " ('District Of Columbia', '24'),\n",
       " ('Do�a Ana', '35'),\n",
       " ('Dupage', '17'),\n",
       " ('Fairfax City', '51'),\n",
       " ('Fairfax County', '51'),\n",
       " ('Fond Du Lac', '55'),\n",
       " ('Franklin City', '51'),\n",
       " ('Franklin County', '51'),\n",
       " ('Fredericksburg City', '51'),\n",
       " ('Hampton City', '51'),\n",
       " ('Harrisonburg City', '51'),\n",
       " ('Hopewell City', '51'),\n",
       " ('Isle Of Wight', '51'),\n",
       " ('Kenosha', '17'),\n",
       " ('King And Queen', '51'),\n",
       " ('La Porte', '18'),\n",
       " ('La Salle', '17'),\n",
       " ('Lagrange', '18'),\n",
       " ('Lewis And Clark', '30'),\n",
       " ('Lynchburg City', '51'),\n",
       " ('Macon-Bibb County', '13'),\n",
       " ('Manassas City', '51'),\n",
       " ('Martinsville City', '51'),\n",
       " ('Mcclain', '40'),\n",
       " ('Mccormick', '45'),\n",
       " ('Mccracken', '21'),\n",
       " ('Mcduffie', '13'),\n",
       " ('Mchenry', '17'),\n",
       " ('Mcintosh', '13'),\n",
       " ('Mckean', '42'),\n",
       " ('Mclennan', '48'),\n",
       " ('Mcleod', '27'),\n",
       " ('Mcminn', '47'),\n",
       " ('Norfolk City', '51'),\n",
       " ('Obrien', '19'),\n",
       " ('Oglala Lakota', '46'),\n",
       " ('Petersburg City', '51'),\n",
       " ('Portsmouth City', '51'),\n",
       " ('Prince Georges', '24'),\n",
       " ('Queen Annes', '24'),\n",
       " ('Richmond City', '51'),\n",
       " ('Richmond County', '51'),\n",
       " ('Roanoke City', '51'),\n",
       " ('Roanoke County', '51'),\n",
       " ('Saint Bernard', '22'),\n",
       " ('Saint Charles', '22'),\n",
       " ('Saint Charles', '29'),\n",
       " ('Saint Clair', '17'),\n",
       " ('Saint Clair', '26'),\n",
       " ('Saint James', '22'),\n",
       " ('Saint Johns', '12'),\n",
       " ('Saint Joseph', '26'),\n",
       " ('Saint Lawrence', '36'),\n",
       " ('Saint Louis', '27'),\n",
       " ('Saint Louis', '29'),\n",
       " ('Saint Louis City', '29'),\n",
       " ('Saint Marys', '24'),\n",
       " ('Saint Tammany', '22'),\n",
       " ('St John The Baptist', '22'),\n",
       " ('St Joseph', '18'),\n",
       " ('St. Louis City', '29'),\n",
       " ('St. Louis County', '29'),\n",
       " ('Staunton City', '51'),\n",
       " ('Ste Genevieve', '29'),\n",
       " ('Suffolk City', '51'),\n",
       " ('Virginia Beach City', '51'),\n",
       " ('Walworth', '17'),\n",
       " ('Waynesboro City', '51')}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "99d13136",
   "metadata": {},
   "outputs": [],
   "source": [
    "def dist(s1, s2):\n",
    "    if len(s1) > len(s2):\n",
    "        s1, s2 = s2, s1\n",
    "\n",
    "    distances = range(len(s1) + 1)\n",
    "    for i2, c2 in enumerate(s2):\n",
    "        distances_ = [i2+1]\n",
    "        for i1, c1 in enumerate(s1):\n",
    "            if c1 == c2:\n",
    "                distances_.append(distances[i1])\n",
    "            else:\n",
    "                distances_.append(1 + min((distances[i1], distances[i1 + 1], distances_[-1])))\n",
    "        distances = distances_\n",
    "    return distances[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "aa8eaba4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('Mcleod', '27'): ('McLeod', '27'),\n",
       " ('Roanoke City', '51'): ('Roanoke', '51'),\n",
       " ('La Salle', '17'): ('LaSalle', '17'),\n",
       " ('Fairfax County', '51'): ('Fairfax', '51'),\n",
       " ('De Kalb', '01'): ('DeKalb', '01'),\n",
       " ('Saint Tammany', '22'): ('St. Tammany', '22'),\n",
       " ('Prince Georges', '24'): (\"Prince George's\", '24'),\n",
       " ('Mccracken', '21'): ('McCracken', '21'),\n",
       " ('Norfolk City', '51'): ('Norfolk', '51'),\n",
       " ('Fairfax City', '51'): ('Fairfax', '51'),\n",
       " ('St. Louis City', '29'): ('St. Louis', '29'),\n",
       " ('Saint Bernard', '22'): ('St. Bernard', '22'),\n",
       " ('Walworth', '17'): ('Walworth', '55'),\n",
       " ('De Soto', '12'): ('DeSoto', '12'),\n",
       " ('Harrisonburg City', '51'): ('Harrisonburg', '51'),\n",
       " ('Saint Louis', '27'): ('St. Louis', '27'),\n",
       " ('Mchenry', '17'): ('McHenry', '17'),\n",
       " ('Mcintosh', '13'): ('McIntosh', '13'),\n",
       " ('Buena Vista City', '51'): ('Buena Vista', '51'),\n",
       " ('Mcclain', '40'): ('McClain', '40'),\n",
       " ('Isle Of Wight', '51'): ('Isle of Wight', '51'),\n",
       " ('St. Louis County', '29'): ('St. Louis', '29'),\n",
       " ('De Kalb', '18'): ('DeKalb', '18'),\n",
       " ('Saint Joseph', '26'): ('St. Joseph', '26'),\n",
       " ('Dupage', '17'): ('DuPage', '17'),\n",
       " ('Dekalb', '17'): ('DeKalb', '17'),\n",
       " ('Kenosha', '17'): ('Kenosha', '55'),\n",
       " ('Lewis And Clark', '30'): ('Lewis and Clark', '30'),\n",
       " ('Do�a Ana', '35'): ('Doña Ana', '35'),\n",
       " ('St John The Baptist', '22'): ('St. John the Baptist', '22'),\n",
       " ('Saint Louis', '29'): ('St. Louis', '29'),\n",
       " ('District Of Columbia', '11'): ('District of Columbia', '11'),\n",
       " ('Covington City', '51'): ('Covington', '51'),\n",
       " ('Queen Annes', '24'): (\"Queen Anne's\", '24'),\n",
       " ('Richmond County', '51'): ('Richmond', '51'),\n",
       " ('Chesapeake City', '51'): ('Chesapeake', '51'),\n",
       " ('Saint Charles', '22'): ('St. Charles', '22'),\n",
       " ('Desoto', '28'): ('DeSoto', '28'),\n",
       " ('Alexandria City', '51'): ('Alexandria', '51'),\n",
       " ('Baltimore City', '24'): ('Baltimore', '24'),\n",
       " ('Saint Clair', '17'): ('St. Clair', '17'),\n",
       " ('Obrien', '19'): (\"O'Brien\", '19'),\n",
       " ('Mcminn', '47'): ('McMinn', '47'),\n",
       " ('Macon-Bibb County', '13'): ('Macon', '13'),\n",
       " ('Lynchburg City', '51'): ('Lynchburg', '51'),\n",
       " ('Hampton City', '51'): ('Hampton', '51'),\n",
       " ('St Joseph', '18'): ('St. Joseph', '18'),\n",
       " ('Charlottesville City', '51'): ('Charlottesville', '51'),\n",
       " ('District Of Columbia', '24'): ('District of Columbia', '11'),\n",
       " ('Portsmouth City', '51'): ('Portsmouth', '51'),\n",
       " ('Mckean', '42'): ('McKean', '42'),\n",
       " ('Dekalb', '13'): ('DeKalb', '13'),\n",
       " ('Saint Lawrence', '36'): ('St. Lawrence', '36'),\n",
       " ('Waynesboro City', '51'): ('Waynesboro', '51'),\n",
       " ('Staunton City', '51'): ('Staunton', '51'),\n",
       " ('Baltimore County', '24'): ('Baltimore', '24'),\n",
       " ('Martinsville City', '51'): ('Martinsville', '51'),\n",
       " ('Saint Charles', '29'): ('St. Charles', '29'),\n",
       " ('Saint Marys', '24'): (\"St. Mary's\", '24'),\n",
       " ('King And Queen', '51'): ('King and Queen', '51'),\n",
       " ('Bedford County', '51'): ('Bedford', '51'),\n",
       " ('Fredericksburg City', '51'): ('Fredericksburg', '51'),\n",
       " ('Petersburg City', '51'): ('Petersburg', '51'),\n",
       " ('Saint James', '22'): ('St. James', '22'),\n",
       " ('Ste Genevieve', '29'): ('Ste. Genevieve', '29'),\n",
       " ('Mclennan', '48'): ('McLennan', '48'),\n",
       " ('Franklin County', '51'): ('Franklin', '51'),\n",
       " ('Hopewell City', '51'): ('Hopewell', '51'),\n",
       " ('Manassas City', '51'): ('Manassas Park', '51'),\n",
       " ('Mcduffie', '13'): ('McDuffie', '13'),\n",
       " ('Fond Du Lac', '55'): ('Fond du Lac', '55'),\n",
       " ('Richmond City', '51'): ('Richmond', '51'),\n",
       " ('Lagrange', '18'): ('LaGrange', '18'),\n",
       " ('Saint Clair', '26'): ('St. Clair', '26'),\n",
       " ('Virginia Beach City', '51'): ('Virginia Beach', '51'),\n",
       " ('La Porte', '18'): ('LaPorte', '18'),\n",
       " ('Suffolk City', '51'): ('Suffolk', '51'),\n",
       " ('Franklin City', '51'): ('Franklin', '51'),\n",
       " ('Danville City', '51'): ('Danville', '51'),\n",
       " ('Saint Johns', '12'): ('St. Johns', '12'),\n",
       " ('Roanoke County', '51'): ('Roanoke', '51'),\n",
       " ('Dewitt', '17'): ('De Witt', '17'),\n",
       " ('Mccormick', '45'): ('McCormick', '45'),\n",
       " ('Saint Louis City', '29'): ('St. Louis', '29')}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_map = {}\n",
    "\n",
    "for c, s in cs:\n",
    "    c_map[(c, s)] = min([((county, state), dist(c, county)) for county, state  in list(geo_counties) if state == s], key=lambda x: x[1])[0]\n",
    "\n",
    "c_map[('Hampton City', '51')] = ('Hampton', '51')\n",
    "c_map[('Macon-Bibb County', '13')] = ('Macon', '13')\n",
    "c_map[('District Of Columbia', '24')] = ('District of Columbia', '11')\n",
    "c_map[('Walworth', '17')] = ('Walworth', '55')\n",
    "c_map[('Kenosha', '17')] = ('Kenosha', '55')\n",
    "\n",
    "removes = [('Oglala Lakota', '46')]\n",
    "\n",
    "for r in removes:\n",
    "    del c_map[r]\n",
    "    \n",
    "c_map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6fa6b466",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = pd.read_csv('data/us_accidents.csv')\n",
    "\n",
    "drop_indices = []\n",
    "\n",
    "def update(x):\n",
    "    \n",
    "    c, s = x.County, x.State\n",
    "    \n",
    "    if (c,state_ids[s]) in c_map:\n",
    "        c2, s2 = c_map[c,state_ids[s]]\n",
    "        return c2, id_states[s2]\n",
    "    \n",
    "    return c, s\n",
    "\n",
    "new_entries = df_final.apply(update, axis=1)\n",
    "df_final['County'] = new_entries.apply(lambda x: x[0])\n",
    "df_final['State'] = new_entries.apply(lambda x: x[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "30a5e1ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_final.drop(index=[2509171])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0c960920",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(list(zip(df_final['County'], df_final['State'].map(state_ids)))).difference(geo_counties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "10f6ed27",
   "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>Timestamp</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Time_of_Day</th>\n",
       "      <th>Day_of_Week</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Distance</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Cloudy</th>\n",
       "      <th>Rain</th>\n",
       "      <th>Snow</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Thunder</th>\n",
       "      <th>Visibility</th>\n",
       "      <th>Windy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-02-08 00:37:08</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Night</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>3.230</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-02-08 05:56:20</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.747</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-02-08 06:15:39</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.055</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-02-08 06:51:45</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.123</td>\n",
       "      <td>Summit</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-02-08 07:53:43</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Morning</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.500</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845337</th>\n",
       "      <td>2019-08-23 18:03:25</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>0.543</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845338</th>\n",
       "      <td>2019-08-23 19:11:30</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>0.338</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845339</th>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>0.561</td>\n",
       "      <td>Orange</td>\n",
       "      <td>California</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845340</th>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>0.772</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845341</th>\n",
       "      <td>2019-08-23 18:52:06</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>Evening</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>0.537</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>California</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2845341 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp  Year  Month  Day Time_of_Day  Day_of_Week  \\\n",
       "0        2016-02-08 00:37:08  2016      2    8       Night            0   \n",
       "1        2016-02-08 05:56:20  2016      2    8     Morning            0   \n",
       "2        2016-02-08 06:15:39  2016      2    8     Morning            0   \n",
       "3        2016-02-08 06:51:45  2016      2    8     Morning            0   \n",
       "4        2016-02-08 07:53:43  2016      2    8     Morning            0   \n",
       "...                      ...   ...    ...  ...         ...          ...   \n",
       "2845337  2019-08-23 18:03:25  2019      8   23     Evening            4   \n",
       "2845338  2019-08-23 19:11:30  2019      8   23     Evening            4   \n",
       "2845339  2019-08-23 19:00:21  2019      8   23     Evening            4   \n",
       "2845340  2019-08-23 19:00:21  2019      8   23     Evening            4   \n",
       "2845341  2019-08-23 18:52:06  2019      8   23     Evening            4   \n",
       "\n",
       "         Duration  Distance          County       State  Cloudy  Rain  Snow  \\\n",
       "0             360     3.230        Franklin        Ohio       0     1     0   \n",
       "1             360     0.747      Montgomery        Ohio       0     1     0   \n",
       "2             360     0.055        Hamilton        Ohio       1     0     0   \n",
       "3             360     0.123          Summit        Ohio       1     0     0   \n",
       "4             360     0.500        Hamilton        Ohio       0     1     0   \n",
       "...           ...       ...             ...         ...     ...   ...   ...   \n",
       "2845337        28     0.543       Riverside  California       0     0     0   \n",
       "2845338        26     0.338       San Diego  California       0     0     0   \n",
       "2845339        28     0.561          Orange  California       1     0     0   \n",
       "2845340        29     0.772     Los Angeles  California       0     0     0   \n",
       "2845341        29     0.537  San Bernardino  California       0     0     0   \n",
       "\n",
       "         Precipitation  Thunder  Visibility  Windy  \n",
       "0                    0        0           0      0  \n",
       "1                    0        0           0      0  \n",
       "2                    0        0           0      0  \n",
       "3                    0        0           0      0  \n",
       "4                    0        0           0      0  \n",
       "...                ...      ...         ...    ...  \n",
       "2845337              0        0           0      0  \n",
       "2845338              0        0           0      0  \n",
       "2845339              0        0           0      0  \n",
       "2845340              0        0           0      0  \n",
       "2845341              0        0           0      0  \n",
       "\n",
       "[2845341 rows x 17 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "cc0b5c0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "states = {state : i for i, state in enumerate(sorted(list(set(df_final['State']))))}\n",
    "counties = {county : i for i, county in enumerate(sorted(list(set(df_final['County']))))}\n",
    "tods = {'Morning' : 0, 'Afternoon' : 1, 'Evening' : 2, 'Night' : 3}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "3c132e0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "regions_dict = {'states' : list(states.keys()), 'counties' : list(counties.keys())}\n",
    "with open('data/regions.js', 'w') as f:\n",
    "    json.dump(regions_dict, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "d59c96e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_comp = pd.DataFrame(df_final)\n",
    "\n",
    "df_comp['Time_of_Day'] = df_comp['Time_of_Day'].map(tods)\n",
    "df_comp['County'] = df_comp['County'].map(counties)\n",
    "df_comp['State'] = df_comp['State'].map(states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "e90c2ff4",
   "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>Timestamp</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Time_of_Day</th>\n",
       "      <th>Day_of_Week</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Distance</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>Cloudy</th>\n",
       "      <th>Rain</th>\n",
       "      <th>Snow</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Thunder</th>\n",
       "      <th>Visibility</th>\n",
       "      <th>Windy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-02-08 00:37:08</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>3.230</td>\n",
       "      <td>534</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-02-08 05:56:20</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.747</td>\n",
       "      <td>996</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-02-08 06:15:39</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.055</td>\n",
       "      <td>636</td>\n",
       "      <td>33</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-02-08 06:51:45</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.123</td>\n",
       "      <td>1414</td>\n",
       "      <td>33</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-02-08 07:53:43</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>0.500</td>\n",
       "      <td>636</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845337</th>\n",
       "      <td>2019-08-23 18:03:25</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>0.543</td>\n",
       "      <td>1246</td>\n",
       "      <td>3</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845338</th>\n",
       "      <td>2019-08-23 19:11:30</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>0.338</td>\n",
       "      <td>1286</td>\n",
       "      <td>3</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845339</th>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>0.561</td>\n",
       "      <td>1089</td>\n",
       "      <td>3</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845340</th>\n",
       "      <td>2019-08-23 19:00:21</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>0.772</td>\n",
       "      <td>872</td>\n",
       "      <td>3</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2845341</th>\n",
       "      <td>2019-08-23 18:52:06</td>\n",
       "      <td>2019</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>0.537</td>\n",
       "      <td>1285</td>\n",
       "      <td>3</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2845341 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp  Year  Month  Day  Time_of_Day  Day_of_Week  \\\n",
       "0        2016-02-08 00:37:08  2016      2    8            3            0   \n",
       "1        2016-02-08 05:56:20  2016      2    8            0            0   \n",
       "2        2016-02-08 06:15:39  2016      2    8            0            0   \n",
       "3        2016-02-08 06:51:45  2016      2    8            0            0   \n",
       "4        2016-02-08 07:53:43  2016      2    8            0            0   \n",
       "...                      ...   ...    ...  ...          ...          ...   \n",
       "2845337  2019-08-23 18:03:25  2019      8   23            2            4   \n",
       "2845338  2019-08-23 19:11:30  2019      8   23            2            4   \n",
       "2845339  2019-08-23 19:00:21  2019      8   23            2            4   \n",
       "2845340  2019-08-23 19:00:21  2019      8   23            2            4   \n",
       "2845341  2019-08-23 18:52:06  2019      8   23            2            4   \n",
       "\n",
       "         Duration  Distance  County  State  Cloudy  Rain  Snow  Precipitation  \\\n",
       "0             360     3.230     534     33       0     1     0              0   \n",
       "1             360     0.747     996     33       0     1     0              0   \n",
       "2             360     0.055     636     33       1     0     0              0   \n",
       "3             360     0.123    1414     33       1     0     0              0   \n",
       "4             360     0.500     636     33       0     1     0              0   \n",
       "...           ...       ...     ...    ...     ...   ...   ...            ...   \n",
       "2845337        28     0.543    1246      3       0     0     0              0   \n",
       "2845338        26     0.338    1286      3       0     0     0              0   \n",
       "2845339        28     0.561    1089      3       1     0     0              0   \n",
       "2845340        29     0.772     872      3       0     0     0              0   \n",
       "2845341        29     0.537    1285      3       0     0     0              0   \n",
       "\n",
       "         Thunder  Visibility  Windy  \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",
       "2845337        0           0      0  \n",
       "2845338        0           0      0  \n",
       "2845339        0           0      0  \n",
       "2845340        0           0      0  \n",
       "2845341        0           0      0  \n",
       "\n",
       "[2845341 rows x 17 columns]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_comp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "1c02c1ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_comp.to_csv('data/compressed.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2cc43899",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'counties' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/6v/d1x3g4gd1p7dxlhrd5p46kjm0000gn/T/ipykernel_6179/661974289.py\u001b[0m in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcounties\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'counties' is not defined"
     ]
    }
   ],
   "source": [
    "counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "79c88954",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Alabama': 0,\n",
       " 'Arizona': 1,\n",
       " 'Arkansas': 2,\n",
       " 'California': 3,\n",
       " 'Colorado': 4,\n",
       " 'Connecticut': 5,\n",
       " 'Delaware': 6,\n",
       " 'District of Columbia': 7,\n",
       " 'Florida': 8,\n",
       " 'Georgia': 9,\n",
       " 'Idaho': 10,\n",
       " 'Illinois': 11,\n",
       " 'Indiana': 12,\n",
       " 'Iowa': 13,\n",
       " 'Kansas': 14,\n",
       " 'Kentucky': 15,\n",
       " 'Louisiana': 16,\n",
       " 'Maine': 17,\n",
       " 'Maryland': 18,\n",
       " 'Massachusetts': 19,\n",
       " 'Michigan': 20,\n",
       " 'Minnesota': 21,\n",
       " 'Mississippi': 22,\n",
       " 'Missouri': 23,\n",
       " 'Montana': 24,\n",
       " 'Nebraska': 25,\n",
       " 'Nevada': 26,\n",
       " 'New Hampshire': 27,\n",
       " 'New Jersey': 28,\n",
       " 'New Mexico': 29,\n",
       " 'New York': 30,\n",
       " 'North Carolina': 31,\n",
       " 'North Dakota': 32,\n",
       " 'Ohio': 33,\n",
       " 'Oklahoma': 34,\n",
       " 'Oregon': 35,\n",
       " 'Pennsylvania': 36,\n",
       " 'Rhode Island': 37,\n",
       " 'South Carolina': 38,\n",
       " 'South Dakota': 39,\n",
       " 'Tennessee': 40,\n",
       " 'Texas': 41,\n",
       " 'Utah': 42,\n",
       " 'Vermont': 43,\n",
       " 'Virginia': 44,\n",
       " 'Washington': 45,\n",
       " 'West Virginia': 46,\n",
       " 'Wisconsin': 47,\n",
       " 'Wyoming': 48}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bdd32f5",
   "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.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}