Semi-Structured Text Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('Speeding/Violations_2017_Speeding_after_geoloc.csv', parse_dates=[['Date Of Stop', 'Time Of Stop']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from difflib import SequenceMatcher\n",
    "#text = \"\"\"there are\n",
    "#some toyot in my text\n",
    "#but I cannot find them\"\"\"\n",
    "#df = df[:100]\n",
    "df['Make2'] = 'Make'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Date Of Stop_Time Of Stop</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal Injury</th>\n",
       "      <th>...</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed To Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver City</th>\n",
       "      <th>Driver State</th>\n",
       "      <th>DL State</th>\n",
       "      <th>Arrest Type</th>\n",
       "      <th>Make2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-28 23:13:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>6th district, Gaithersburg / Montgomery Village</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 39 MPH I...</td>\n",
       "      <td>LOST KNIFE RD. @ LOST KNIFE CIR</td>\n",
       "      <td>39.157853</td>\n",
       "      <td>-77.204095</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>F</td>\n",
       "      <td>GERMANTOWN</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>Make</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-28 22:53:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>6th district, Gaithersburg / Montgomery Village</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 45 MPH I...</td>\n",
       "      <td>WOODFIELD RD / EMORY GROVE RD</td>\n",
       "      <td>39.152928</td>\n",
       "      <td>-77.171317</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>GAITHERSBURG</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>Q - Marked Laser</td>\n",
       "      <td>Make</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-08-29 01:44:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 50 MPH I...</td>\n",
       "      <td>SEVEN LOCKS RD/CADBURY AVE</td>\n",
       "      <td>39.083948</td>\n",
       "      <td>-77.153498</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>COLOMBIA</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>Make</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-08-29 07:55:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...</td>\n",
       "      <td>W/B UNIVERSITY BLVD @ NEWPORT MILL</td>\n",
       "      <td>39.033387</td>\n",
       "      <td>-77.072528</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>F</td>\n",
       "      <td>FULTON</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>Q - Marked Laser</td>\n",
       "      <td>Make</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-08-29 08:07:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...</td>\n",
       "      <td>W/B UNIVERSITY BLVD @ NEWPORT MILL</td>\n",
       "      <td>39.032983</td>\n",
       "      <td>-77.073839</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>SILVER SPRING</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>Q - Marked Laser</td>\n",
       "      <td>Make</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Date Of Stop_Time Of Stop Agency  \\\n",
       "0       2017-08-28 23:13:00    MCP   \n",
       "1       2017-08-28 22:53:00    MCP   \n",
       "2       2017-08-29 01:44:00    MCP   \n",
       "3       2017-08-29 07:55:00    MCP   \n",
       "4       2017-08-29 08:07:00    MCP   \n",
       "\n",
       "                                         SubAgency  \\\n",
       "0  6th district, Gaithersburg / Montgomery Village   \n",
       "1  6th district, Gaithersburg / Montgomery Village   \n",
       "2                          1st district, Rockville   \n",
       "3                           2nd district, Bethesda   \n",
       "4                           2nd district, Bethesda   \n",
       "\n",
       "                                         Description  \\\n",
       "0  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 39 MPH I...   \n",
       "1  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 45 MPH I...   \n",
       "2  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 50 MPH I...   \n",
       "3  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...   \n",
       "4  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...   \n",
       "\n",
       "                             Location   Latitude  Longitude Accident Belts  \\\n",
       "0     LOST KNIFE RD. @ LOST KNIFE CIR  39.157853 -77.204095       No    No   \n",
       "1       WOODFIELD RD / EMORY GROVE RD  39.152928 -77.171317       No    No   \n",
       "2          SEVEN LOCKS RD/CADBURY AVE  39.083948 -77.153498       No    No   \n",
       "3  W/B UNIVERSITY BLVD @ NEWPORT MILL  39.033387 -77.072528       No    No   \n",
       "4  W/B UNIVERSITY BLVD @ NEWPORT MILL  39.032983 -77.073839       No    No   \n",
       "\n",
       "  Personal Injury  ...      Charge                 Article  \\\n",
       "0              No  ...    21-801.1  Transportation Article   \n",
       "1              No  ...    21-801.1  Transportation Article   \n",
       "2              No  ...    21-801.1  Transportation Article   \n",
       "3              No  ...    21-801.1  Transportation Article   \n",
       "4              No  ...    21-801.1  Transportation Article   \n",
       "\n",
       "  Contributed To Accident      Race Gender    Driver City Driver State  \\\n",
       "0                      No  HISPANIC      F     GERMANTOWN           MD   \n",
       "1                      No  HISPANIC      M   GAITHERSBURG           MD   \n",
       "2                      No     WHITE      M       COLOMBIA           MD   \n",
       "3                      No     OTHER      F         FULTON           MD   \n",
       "4                      No     WHITE      M  SILVER SPRING           MD   \n",
       "\n",
       "  DL State        Arrest Type  Make2  \n",
       "0       MD  A - Marked Patrol   Make  \n",
       "1       MD   Q - Marked Laser   Make  \n",
       "2       MD  A - Marked Patrol   Make  \n",
       "3       MD   Q - Marked Laser   Make  \n",
       "4       MD   Q - Marked Laser   Make  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fuzzy_search(search_key, text, strictness):\n",
    "    similarity = SequenceMatcher(None, text, search_key)\n",
    "    if similarity.ratio() > strictness:\n",
    "        return search_key\n",
    "\n",
    "make_list = ['ACURA', 'ASTON MARTIN', 'AUDI', 'BMW', 'BENTLEY', 'BUICK',\n",
    "'CADILLAC', 'CHEVROLET', 'CHRYSLER', 'DODGE', 'DUCATI', 'FERARRI',\n",
    "'FIAT', 'FORD', 'FREIGHTLINER', 'GEO', 'GMC', 'GILLIG', 'GRUMMAN',\n",
    "'HARLEY', 'HINO', 'HONDA', 'INFINITI', 'ISUZU', 'JAGUAR', 'HUMMER',\n",
    "'HYUNDAI', 'INTERNATIONAL', 'JEEP', 'KAWASAKI', 'KENWORTH',\n",
    "'KIA', 'LANDROVER', 'LEXUS', 'LINCOLN', 'MACK', 'MASERATI',\n",
    "'MAZDA', 'MERCEDES', 'MERCURY', 'MINI', 'MITSUBISHI', 'NISSAN',\n",
    "'OLDSMOBILE', 'PETERBILT', 'PLYMOUTH', 'POLARIS',\n",
    "'PONTIAC', 'PORSCHE', 'RANGE ROVER', 'SATURN', 'SCION',\n",
    "'SAAB', 'SMART', 'SUBARU', 'SUZUKI', 'TESLA', 'TOYOTA',\n",
    "'VOLKSWAGEN', 'VOLVO', 'YAMAHA']\n",
    "\n",
    "not_found = 0\n",
    "\n",
    "for index, text in df.iterrows():\n",
    "    found = 0\n",
    "    #Special fix for Chevrolet...\n",
    "    \n",
    "    if ((text['Make'] == 'CHEVY') or (text['Make']=='CHEV')):\n",
    "        text['Make'] = 'CHEVROLET'\n",
    "    \n",
    "    #Fix for Volkswagen...\n",
    "    elif ((text['Make'] == 'VW') or (text['Make'] == 'VOLKS') or (text['Make'] == 'VOLK')):\n",
    "        text['Make'] = 'VOLKSWAGEN'\n",
    "    \n",
    "    #fix for Oldsmobile...\n",
    "    elif text['Make'] == 'OLDS':\n",
    "        text['Make'] = 'OLDSMOBILE'\n",
    "        \n",
    "    #RAM is a type of Dodge...\n",
    "    elif text['Make'] == 'RAM':\n",
    "        text['Make'] = 'DODGE'\n",
    "    \n",
    "    #MERZ is abbreviation for Mercedes...\n",
    "    elif ((text['Make'] == 'MERZ') or text['Make'] == 'MERC'):\n",
    "        text['Make'] = 'MERCEDES'\n",
    "        \n",
    "    #MITZ and MITS is Mitsubishi...\n",
    "    elif ((text['Make'] == 'MITS') or (text['Make'] == 'MITZ') or (text['Make'] == 'MITSU')):\n",
    "        text['Make'] = 'MITSUBISHI'\n",
    "        \n",
    "    #HD is Harley\n",
    "    elif text['Make'] == 'HD' or text['Make'] == 'H D':\n",
    "        text['Make'] = 'HARLEY'\n",
    "        \n",
    "    #INTL is international:\n",
    "    elif text['Make'] == 'INTL':\n",
    "        text['Make'] = 'INTERNATIONAL'\n",
    "        \n",
    "        \n",
    "    #CADI is cadillac:\n",
    "    elif ((text['Make'] == 'CADI') or (text['Make'] == 'CAD')):\n",
    "        text['Make'] = 'CADILLAC'\n",
    "    \n",
    "    #CHRY is chrysler\n",
    "    elif text['Make'] == 'CHRY':\n",
    "        text['Make'] = 'CHRYSLER'\n",
    "        \n",
    "    #HON is honda\n",
    "    elif text['Make'] == 'HON':\n",
    "        text['Make'] = 'HONDA'\n",
    "        \n",
    "    #HYUN is hyundai\n",
    "    elif text['Make'] == 'HYUN':\n",
    "        text['Make'] = 'HYUNDAI'\n",
    "        \n",
    "    #infi is infiniti\n",
    "    elif text['Make'] == 'INFI':\n",
    "        text['Make'] = 'INFINITI'\n",
    "        \n",
    "    elif text['Make'] == 'LINC':\n",
    "        text['Make'] = 'LINCOLN'\n",
    "    \n",
    "    elif text['Make'] == 'LNDR':\n",
    "        text['Make'] = 'LANDROVER'\n",
    "        \n",
    "    elif text['Make'] == 'MAZ':\n",
    "        text['Make'] = 'MAZDA'\n",
    "    \n",
    "    elif text['Make'] == 'KAWK':\n",
    "        text['Make'] = 'KAWASAKI'\n",
    "        \n",
    "    elif text['Make'] == 'LEX':\n",
    "        text['Make'] = 'LEXUS'\n",
    "    \n",
    "    elif text['Make'] == 'BENT':\n",
    "        text['Make'] = 'BENTLEY'\n",
    "        \n",
    "    elif text['Make'] == 'PONT':\n",
    "        text['Make'] = 'PONTIAC'\n",
    "        \n",
    "    elif text['Make'] == 'PORS':\n",
    "        text['Make'] = 'PORSCHE'\n",
    "        \n",
    "    elif text['Make'] == 'SUB':\n",
    "        text['Make'] = 'SUBARU'\n",
    "    \n",
    "    elif text['Make'] == 'TOY':\n",
    "        text['Make'] = 'TOYOTA'\n",
    "        \n",
    "    elif ((text['Make'] == 'RANG') or (text['Make'] == 'RANGE')):\n",
    "        text['Make'] = 'RANGE ROVER'\n",
    "    \n",
    "    elif text['Make'] == 'YAM':\n",
    "        text['Make'] = 'YAMAHA'\n",
    "    \n",
    "    elif text['Make'] == 'PTRB':\n",
    "        text['Make'] = 'PETERBILT'\n",
    "        \n",
    "    elif text['Make'] == 'NISS':\n",
    "        text['Make'] = 'NISSAN'\n",
    "        \n",
    "    elif text['Make'] == 'MNNI':\n",
    "        text['Make'] = 'MINI'\n",
    "        \n",
    "    elif text['Make'] == 'JAG':\n",
    "        text['Make'] = 'JAGUAR'\n",
    "        \n",
    "    elif text['Make'] == 'KW':\n",
    "        text['Make'] = 'KENWORTH'\n",
    "        \n",
    "    elif text['Make'] == 'ISU':\n",
    "        text['Make'] = 'ISUZU'\n",
    "        \n",
    "    elif text['Make'] == 'FRHT':\n",
    "        text['Make'] = 'FREIGHTLINER'\n",
    "        \n",
    "    elif text['Make'] == 'PLYM':\n",
    "        text['Make'] = 'PLYMOUTH'\n",
    "    \n",
    "    #print(text)\n",
    "    for item in make_list:\n",
    "        if ((fuzzy_search(item, text['Make'], 0.75) != None) and found == 0):\n",
    "            #print (fuzzy_search(item, text['Make'], 0.65))\n",
    "            df.at[index, 'Make2'] = (fuzzy_search(item, text['Make'], 0.75))\n",
    "            found = 1\n",
    "            \n",
    "            \n",
    "    if found == 0:\n",
    "        #print(text)\n",
    "        not_found += 1\n",
    "        df.at[index, 'Make2'] = 'Unknown'\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "157"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            Make       Make2\n",
      "0           FORD        FORD\n",
      "1         SUBARU      SUBARU\n",
      "2           TOYT      TOYOTA\n",
      "3          HONDA       HONDA\n",
      "4         SUBARU      SUBARU\n",
      "5            BMW         BMW\n",
      "6          ACURA       ACURA\n",
      "7         TOYOTA      TOYOTA\n",
      "8           FORD        FORD\n",
      "9           JEEP        JEEP\n",
      "10          SUBA      SUBARU\n",
      "11          TOYT      TOYOTA\n",
      "12        TOYOTA      TOYOTA\n",
      "13         ACURA       ACURA\n",
      "14      MERCEDES    MERCEDES\n",
      "15          FORD        FORD\n",
      "16          TOYT      TOYOTA\n",
      "17           BMW         BMW\n",
      "18          LNDR   LANDROVER\n",
      "19          HOND       HONDA\n",
      "20          NISS      NISSAN\n",
      "21         MAZDA       MAZDA\n",
      "22           GMC         GMC\n",
      "23         DODGE       DODGE\n",
      "24          HYUN     HYUNDAI\n",
      "25           JAG      JAGUAR\n",
      "26          CADI    CADILLAC\n",
      "27          TOYT      TOYOTA\n",
      "28         HONDA       HONDA\n",
      "29          FORD        FORD\n",
      "...          ...         ...\n",
      "21567     TOYOTA      TOYOTA\n",
      "21568     TOYOTA      TOYOTA\n",
      "21569  CHEVROLET   CHEVROLET\n",
      "21570      HONDA       HONDA\n",
      "21571    HYUNDAI     HYUNDAI\n",
      "21572     TOYOTA      TOYOTA\n",
      "21573      VOLKS  VOLKSWAGEN\n",
      "21574       NISS      NISSAN\n",
      "21575       HOND       HONDA\n",
      "21576       TOYT      TOYOTA\n",
      "21577     SUBARU      SUBARU\n",
      "21578       HYUN     HYUNDAI\n",
      "21579      HONDA       HONDA\n",
      "21580       ACUR       ACURA\n",
      "21581       TOYT      TOYOTA\n",
      "21582     NISSAN      NISSAN\n",
      "21583      HONDA       HONDA\n",
      "21584     TOYOTA      TOYOTA\n",
      "21585       MERZ    MERCEDES\n",
      "21586      VOLVO       VOLVO\n",
      "21587      HONDA       HONDA\n",
      "21588       AUDI        AUDI\n",
      "21589     SUBARU      SUBARU\n",
      "21590       MINI        MINI\n",
      "21591      LEXUS       LEXUS\n",
      "21592      CHEVY   CHEVROLET\n",
      "21593       LEXS       LEXUS\n",
      "21594     TOYOTA      TOYOTA\n",
      "21595       TOYT      TOYOTA\n",
      "21596  VOLSWAGEN  VOLKSWAGEN\n",
      "\n",
      "[21597 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "#pd.set_option('display.max_rows', -1)\n",
    "print(df[['Make','Make2']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Date Of Stop_Time Of Stop</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal Injury</th>\n",
       "      <th>...</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed To Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver City</th>\n",
       "      <th>Driver State</th>\n",
       "      <th>DL State</th>\n",
       "      <th>Arrest Type</th>\n",
       "      <th>Make2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-28 23:13:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>6th district, Gaithersburg / Montgomery Village</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 39 MPH I...</td>\n",
       "      <td>LOST KNIFE RD. @ LOST KNIFE CIR</td>\n",
       "      <td>39.157853</td>\n",
       "      <td>-77.204095</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>F</td>\n",
       "      <td>GERMANTOWN</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>FORD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-28 22:53:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>6th district, Gaithersburg / Montgomery Village</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 45 MPH I...</td>\n",
       "      <td>WOODFIELD RD / EMORY GROVE RD</td>\n",
       "      <td>39.152928</td>\n",
       "      <td>-77.171317</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>GAITHERSBURG</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>Q - Marked Laser</td>\n",
       "      <td>SUBARU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-08-29 01:44:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 50 MPH I...</td>\n",
       "      <td>SEVEN LOCKS RD/CADBURY AVE</td>\n",
       "      <td>39.083948</td>\n",
       "      <td>-77.153498</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>COLOMBIA</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>TOYOTA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-08-29 07:55:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...</td>\n",
       "      <td>W/B UNIVERSITY BLVD @ NEWPORT MILL</td>\n",
       "      <td>39.033387</td>\n",
       "      <td>-77.072528</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>F</td>\n",
       "      <td>FULTON</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>Q - Marked Laser</td>\n",
       "      <td>HONDA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-08-29 08:07:00</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...</td>\n",
       "      <td>W/B UNIVERSITY BLVD @ NEWPORT MILL</td>\n",
       "      <td>39.032983</td>\n",
       "      <td>-77.073839</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>21-801.1</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>SILVER SPRING</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>Q - Marked Laser</td>\n",
       "      <td>SUBARU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Date Of Stop_Time Of Stop Agency  \\\n",
       "0       2017-08-28 23:13:00    MCP   \n",
       "1       2017-08-28 22:53:00    MCP   \n",
       "2       2017-08-29 01:44:00    MCP   \n",
       "3       2017-08-29 07:55:00    MCP   \n",
       "4       2017-08-29 08:07:00    MCP   \n",
       "\n",
       "                                         SubAgency  \\\n",
       "0  6th district, Gaithersburg / Montgomery Village   \n",
       "1  6th district, Gaithersburg / Montgomery Village   \n",
       "2                          1st district, Rockville   \n",
       "3                           2nd district, Bethesda   \n",
       "4                           2nd district, Bethesda   \n",
       "\n",
       "                                         Description  \\\n",
       "0  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 39 MPH I...   \n",
       "1  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 45 MPH I...   \n",
       "2  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 50 MPH I...   \n",
       "3  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...   \n",
       "4  EXCEEDING POSTED MAXIMUM SPEED LIMIT: 49 MPH I...   \n",
       "\n",
       "                             Location   Latitude  Longitude Accident Belts  \\\n",
       "0     LOST KNIFE RD. @ LOST KNIFE CIR  39.157853 -77.204095       No    No   \n",
       "1       WOODFIELD RD / EMORY GROVE RD  39.152928 -77.171317       No    No   \n",
       "2          SEVEN LOCKS RD/CADBURY AVE  39.083948 -77.153498       No    No   \n",
       "3  W/B UNIVERSITY BLVD @ NEWPORT MILL  39.033387 -77.072528       No    No   \n",
       "4  W/B UNIVERSITY BLVD @ NEWPORT MILL  39.032983 -77.073839       No    No   \n",
       "\n",
       "  Personal Injury   ...      Charge                 Article  \\\n",
       "0              No   ...    21-801.1  Transportation Article   \n",
       "1              No   ...    21-801.1  Transportation Article   \n",
       "2              No   ...    21-801.1  Transportation Article   \n",
       "3              No   ...    21-801.1  Transportation Article   \n",
       "4              No   ...    21-801.1  Transportation Article   \n",
       "\n",
       "  Contributed To Accident      Race Gender    Driver City Driver State  \\\n",
       "0                      No  HISPANIC      F     GERMANTOWN           MD   \n",
       "1                      No  HISPANIC      M   GAITHERSBURG           MD   \n",
       "2                      No     WHITE      M       COLOMBIA           MD   \n",
       "3                      No     OTHER      F         FULTON           MD   \n",
       "4                      No     WHITE      M  SILVER SPRING           MD   \n",
       "\n",
       "  DL State        Arrest Type   Make2  \n",
       "0       MD  A - Marked Patrol    FORD  \n",
       "1       MD   Q - Marked Laser  SUBARU  \n",
       "2       MD  A - Marked Patrol  TOYOTA  \n",
       "3       MD   Q - Marked Laser   HONDA  \n",
       "4       MD   Q - Marked Laser  SUBARU  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date Of Stop_Time Of Stop', 'Agency', 'SubAgency', 'Description',\n",
       "       'Location', 'Latitude', 'Longitude', 'Accident', 'Belts',\n",
       "       'Personal Injury', 'Property Damage', 'Fatal', 'Commercial License',\n",
       "       'HAZMAT', 'Commercial Vehicle', 'Alcohol', 'Work Zone', 'State',\n",
       "       'VehicleType', 'Year', 'Make', 'Model', 'Color', 'Violation Type',\n",
       "       'Charge', 'Article', 'Contributed To Accident', 'Race', 'Gender',\n",
       "       'Driver City', 'Driver State', 'DL State', 'Arrest Type', 'Make2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Categorical Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df1 = df[['Date Of Stop_Time Of Stop', 'Latitude', 'Longitude',\n",
    "        'Belts', 'Personal Injury', 'Property Damage', 'Fatal',\n",
    "        'Commercial License', 'Alcohol', 'Work Zone', \n",
    "          'Contributed To Accident', 'Violation Type', 'Make', 'Make2']]\n",
    "\n",
    "df1.columns = ['time_of_stop', 'latitude', 'longitude', \n",
    "               'belts', 'personal_injury', 'property_damage', 'fatal', \n",
    "               'commercial_license', 'alcohol', 'work_zone', \n",
    "               'contributed_to_accident', 'violation_type', 'make', 'make2']"
   ]
  },
  {
   "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>time_of_stop</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>belts</th>\n",
       "      <th>personal_injury</th>\n",
       "      <th>property_damage</th>\n",
       "      <th>fatal</th>\n",
       "      <th>commercial_license</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>work_zone</th>\n",
       "      <th>contributed_to_accident</th>\n",
       "      <th>violation_type</th>\n",
       "      <th>make</th>\n",
       "      <th>make2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-28 23:13:00</td>\n",
       "      <td>39.157853</td>\n",
       "      <td>-77.204095</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Citation</td>\n",
       "      <td>FORD</td>\n",
       "      <td>FORD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-28 22:53:00</td>\n",
       "      <td>39.152928</td>\n",
       "      <td>-77.171317</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Citation</td>\n",
       "      <td>SUBARU</td>\n",
       "      <td>SUBARU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-08-29 01:44:00</td>\n",
       "      <td>39.083948</td>\n",
       "      <td>-77.153498</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Citation</td>\n",
       "      <td>TOYT</td>\n",
       "      <td>TOYOTA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-08-29 07:55:00</td>\n",
       "      <td>39.033387</td>\n",
       "      <td>-77.072528</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Citation</td>\n",
       "      <td>HONDA</td>\n",
       "      <td>HONDA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-08-29 08:07:00</td>\n",
       "      <td>39.032983</td>\n",
       "      <td>-77.073839</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Citation</td>\n",
       "      <td>SUBARU</td>\n",
       "      <td>SUBARU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         time_of_stop   latitude  longitude belts personal_injury  \\\n",
       "0 2017-08-28 23:13:00  39.157853 -77.204095    No              No   \n",
       "1 2017-08-28 22:53:00  39.152928 -77.171317    No              No   \n",
       "2 2017-08-29 01:44:00  39.083948 -77.153498    No              No   \n",
       "3 2017-08-29 07:55:00  39.033387 -77.072528    No              No   \n",
       "4 2017-08-29 08:07:00  39.032983 -77.073839    No              No   \n",
       "\n",
       "  property_damage fatal commercial_license alcohol work_zone  \\\n",
       "0              No    No                 No      No        No   \n",
       "1              No    No                 No      No        No   \n",
       "2              No    No                 No      No        No   \n",
       "3              No    No                 No      No        No   \n",
       "4              No    No                 No      No        No   \n",
       "\n",
       "  contributed_to_accident violation_type    make   make2  \n",
       "0                      No       Citation    FORD    FORD  \n",
       "1                      No       Citation  SUBARU  SUBARU  \n",
       "2                      No       Citation    TOYT  TOYOTA  \n",
       "3                      No       Citation   HONDA   HONDA  \n",
       "4                      No       Citation  SUBARU  SUBARU  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "157 items removed.\n"
     ]
    }
   ],
   "source": [
    "#Scrub that which cannot be fixed\n",
    "#No blank lats/longs/make2\n",
    "df_scrub = df1[(df1.latitude.notnull()) & \\\n",
    "        (df1.longitude.notnull()) & \\\n",
    "        (df1.make2 != 'Unknown')]\n",
    "\n",
    "print(str(len(df1) - len(df_scrub)) + ' items removed.')\n",
    "\n",
    "df = df_scrub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "#Fix for the rovers\n",
    "df.loc[df['make2'] == 'LANDROVER', 'make2'] = 'LAND_ROVER'\n",
    "df.loc[df['make2'] == 'RANGE ROVER', 'make2'] = 'RANGE_ROVER'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:3697: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\n"
     ]
    }
   ],
   "source": [
    "#Drop extra make column, rename\n",
    "df.drop(['make'], axis=1, inplace=True)\n",
    "df = df.rename(columns={\"make2\": \"make\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('Speeding/Violations_2017_Speeding_before_dummies.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df\n",
    "df2 = pd.get_dummies(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time_of_stop</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>belts_No</th>\n",
       "      <th>belts_Yes</th>\n",
       "      <th>personal_injury_No</th>\n",
       "      <th>personal_injury_Yes</th>\n",
       "      <th>property_damage_No</th>\n",
       "      <th>property_damage_Yes</th>\n",
       "      <th>fatal_No</th>\n",
       "      <th>...</th>\n",
       "      <th>make_SATURN</th>\n",
       "      <th>make_SCION</th>\n",
       "      <th>make_SMART</th>\n",
       "      <th>make_SUBARU</th>\n",
       "      <th>make_SUZUKI</th>\n",
       "      <th>make_TESLA</th>\n",
       "      <th>make_TOYOTA</th>\n",
       "      <th>make_VOLKSWAGEN</th>\n",
       "      <th>make_VOLVO</th>\n",
       "      <th>make_YAMAHA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-28 23:13:00</td>\n",
       "      <td>39.157853</td>\n",
       "      <td>-77.204095</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-28 22:53:00</td>\n",
       "      <td>39.152928</td>\n",
       "      <td>-77.171317</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-08-29 01:44:00</td>\n",
       "      <td>39.083948</td>\n",
       "      <td>-77.153498</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-08-29 07:55:00</td>\n",
       "      <td>39.033387</td>\n",
       "      <td>-77.072528</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-08-29 08:07:00</td>\n",
       "      <td>39.032983</td>\n",
       "      <td>-77.073839</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         time_of_stop   latitude  longitude  belts_No  belts_Yes  \\\n",
       "0 2017-08-28 23:13:00  39.157853 -77.204095         1          0   \n",
       "1 2017-08-28 22:53:00  39.152928 -77.171317         1          0   \n",
       "2 2017-08-29 01:44:00  39.083948 -77.153498         1          0   \n",
       "3 2017-08-29 07:55:00  39.033387 -77.072528         1          0   \n",
       "4 2017-08-29 08:07:00  39.032983 -77.073839         1          0   \n",
       "\n",
       "   personal_injury_No  personal_injury_Yes  property_damage_No  \\\n",
       "0                   1                    0                   1   \n",
       "1                   1                    0                   1   \n",
       "2                   1                    0                   1   \n",
       "3                   1                    0                   1   \n",
       "4                   1                    0                   1   \n",
       "\n",
       "   property_damage_Yes  fatal_No     ...       make_SATURN  make_SCION  \\\n",
       "0                    0         1     ...                 0           0   \n",
       "1                    0         1     ...                 0           0   \n",
       "2                    0         1     ...                 0           0   \n",
       "3                    0         1     ...                 0           0   \n",
       "4                    0         1     ...                 0           0   \n",
       "\n",
       "   make_SMART  make_SUBARU  make_SUZUKI  make_TESLA  make_TOYOTA  \\\n",
       "0           0            0            0           0            0   \n",
       "1           0            1            0           0            0   \n",
       "2           0            0            0           0            1   \n",
       "3           0            0            0           0            0   \n",
       "4           0            1            0           0            0   \n",
       "\n",
       "   make_VOLKSWAGEN  make_VOLVO  make_YAMAHA  \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",
       "[5 rows x 80 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('Speeding/Violations_2017_Speeding_after_dummies.csv', index=False)"
   ]
  },
  {
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}