fars_data_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pysal as ps\n",
    "import scipy.spatial as spatial\n",
    "#from simpledbf import Dbf5 #needed to read dbf files in pre-2015 FARS data\n",
    "from dbfread import DBF\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in FARS data from csv.  Data prior to 2015 is in sas or dbf format, so a differnt method will need to be used  Accident file contains information about the crashes at a per crash level.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "accident_15 = pd.DataFrame.from_csv('FARS2015NationalCSV/accident.csv', index_col=None)\n",
    "accident_15['YEAR'] = 2015 #add column to specify year, needed when compling multiyear dataset\n",
    "#accident_15['YEAR'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "FARS data by person, which is needed to get driver demographic data, like age.  This table can be combined with accident using the STATE and ST_CASE columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "person_15 = pd.DataFrame.from_csv('FARS2015NationalCSV/person.csv', index_col=None)\n",
    "person_15['YEAR'] = 2015 #add column to specify year, needed when compling multiyear dataset\n",
    "#person_15.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "FARS data by vehicle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_CASE</th>\n",
       "      <th>VEH_NO</th>\n",
       "      <th>VE_FORMS</th>\n",
       "      <th>NUMOCCS</th>\n",
       "      <th>DAY</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>HARM_EV</th>\n",
       "      <th>...</th>\n",
       "      <th>VTCONT_F</th>\n",
       "      <th>P_CRASH1</th>\n",
       "      <th>P_CRASH2</th>\n",
       "      <th>P_CRASH3</th>\n",
       "      <th>PCRASH4</th>\n",
       "      <th>PCRASH5</th>\n",
       "      <th>ACC_TYPE</th>\n",
       "      <th>DEATHS</th>\n",
       "      <th>DR_DRINK</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>13</td>\n",
       "      <td>34</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>42</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>10004</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>57</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10005</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>68</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 103 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE  ST_CASE  VEH_NO  VE_FORMS  NUMOCCS  DAY  MONTH  HOUR  MINUTE  \\\n",
       "0      1    10001       1         1        1    1      1     2      40   \n",
       "1      1    10002       1         1        1    1      1    22      13   \n",
       "2      1    10003       1         1        2    1      1     1      25   \n",
       "3      1    10004       1         1        1    4      1     0      57   \n",
       "4      1    10005       1         2        1    7      1     7       9   \n",
       "\n",
       "   HARM_EV  ...   VTCONT_F  P_CRASH1  P_CRASH2  P_CRASH3  PCRASH4  PCRASH5  \\\n",
       "0       35  ...          0        14        13        99        1        4   \n",
       "1       34  ...          0        14         6         1        4        4   \n",
       "2       42  ...          0         1        13         1        1        4   \n",
       "3       53  ...          0        14        13         1        1        4   \n",
       "4       12  ...          0        11        15         1        1        1   \n",
       "\n",
       "   ACC_TYPE  DEATHS  DR_DRINK  YEAR  \n",
       "0         1       1         1  2015  \n",
       "1         7       1         0  2015  \n",
       "2         1       1         1  2015  \n",
       "3         1       1         1  2015  \n",
       "4        68       1         0  2015  \n",
       "\n",
       "[5 rows x 103 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vehicle_15 = pd.DataFrame.from_csv('FARS2015NationalCSV/vehicle.csv', index_col=None, encoding=\"latin-1\")\n",
    "vehicle_15['YEAR'] = 2015 #add column to specify year, needed when compling multiyear dataset\n",
    "vehicle_15.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-2015 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''\n",
    "Define function to open dbf file and return pandas dataframe\n",
    "Arguments\n",
    "---------\n",
    "dbfile  : DBF file - Input to be imported\n",
    "'''\n",
    "def dbf2DF(dbfile): #Reads in DBF files and returns Pandas DF\n",
    "    db = DBF(dbfile, load=True) #dbfread to open DBF\n",
    "    pandasDF = pd.DataFrame(list(db)) #Convert to Pandas DF\n",
    "    return pandasDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_CASE</th>\n",
       "      <th>VEH_NO</th>\n",
       "      <th>VE_FORMS</th>\n",
       "      <th>NUMOCCS</th>\n",
       "      <th>DAY</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>HARM_EV</th>\n",
       "      <th>...</th>\n",
       "      <th>VTCONT_F</th>\n",
       "      <th>P_CRASH1</th>\n",
       "      <th>P_CRASH2</th>\n",
       "      <th>P_CRASH3</th>\n",
       "      <th>PCRASH4</th>\n",
       "      <th>PCRASH5</th>\n",
       "      <th>ACC_TYPE</th>\n",
       "      <th>DEATHS</th>\n",
       "      <th>DR_DRINK</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "      <td>42</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10004</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>54</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 103 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE  ST_CASE  VEH_NO  VE_FORMS  NUMOCCS  DAY  MONTH  HOUR  MINUTE  \\\n",
       "0      1    10001       1         1        2    1      1     1      15   \n",
       "1      1    10002       1         1        1    1      1    13      30   \n",
       "2      1    10003       1         2        4    1      1     3       7   \n",
       "3      1    10003       2         2        3    1      1     3       7   \n",
       "4      1    10004       1         3        1    2      1     9       0   \n",
       "\n",
       "   HARM_EV  ...   VTCONT_F  P_CRASH1  P_CRASH2  P_CRASH3  PCRASH4  PCRASH5  \\\n",
       "0       17  ...          0        14        12         1        1        4   \n",
       "1       42  ...          0         1        12         1        1        4   \n",
       "2       12  ...          3         1        17         1        1        1   \n",
       "3       12  ...          3         1        66         1        1        1   \n",
       "4       12  ...          0         1        54         1        1        1   \n",
       "\n",
       "   ACC_TYPE  DEATHS  DR_DRINK  YEAR  \n",
       "0         6       1         1  2014  \n",
       "1         6       1         1  2014  \n",
       "2        88       0         0  2014  \n",
       "3        89       2         0  2014  \n",
       "4        52       1         0  2014  \n",
       "\n",
       "[5 rows x 103 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "accident_14 = dbf2DF('pre_2015_fars/FARS_2014/accident.dbf')\n",
    "person_14 = dbf2DF('pre_2015_fars/FARS_2014/person.dbf')\n",
    "vehicle_14 = dbf2DF('pre_2015_fars/FARS_2014/vehicle.dbf')\n",
    "accident_14['YEAR'] = 2014 \n",
    "person_14['YEAR'] = 2014 \n",
    "vehicle_14['YEAR'] = 2014 \n",
    "\n",
    "vehicle_14.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_CASE</th>\n",
       "      <th>VEH_NO</th>\n",
       "      <th>VE_FORMS</th>\n",
       "      <th>NUMOCCS</th>\n",
       "      <th>DAY</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>HARM_EV</th>\n",
       "      <th>...</th>\n",
       "      <th>VTCONT_F</th>\n",
       "      <th>P_CRASH1</th>\n",
       "      <th>P_CRASH2</th>\n",
       "      <th>P_CRASH3</th>\n",
       "      <th>PCRASH4</th>\n",
       "      <th>PCRASH5</th>\n",
       "      <th>ACC_TYPE</th>\n",
       "      <th>DEATHS</th>\n",
       "      <th>DR_DRINK</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>55</td>\n",
       "      <td>42</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>66</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>45</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10004</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 103 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE  ST_CASE  VEH_NO  VE_FORMS  NUMOCCS  DAY  MONTH  HOUR  MINUTE  \\\n",
       "0      1    10001       1         1        8    6      1     0      55   \n",
       "1      1    10002       1         2        1    3      1    21      24   \n",
       "2      1    10002       2         2        1    3      1    21      24   \n",
       "3      1    10003       1         1        1    6      1    11      45   \n",
       "4      1    10004       1         1        3    6      1    12      25   \n",
       "\n",
       "   HARM_EV  ...   VTCONT_F  P_CRASH1  P_CRASH2  P_CRASH3  PCRASH4  PCRASH5  \\\n",
       "0       42  ...          0         1         1         1        4        4   \n",
       "1       12  ...          0         1        66         8        1        2   \n",
       "2       12  ...          3         1        17         1        1        1   \n",
       "3       30  ...          0         1        13         1        1        4   \n",
       "4       24  ...          0         1         6         1        3        4   \n",
       "\n",
       "   ACC_TYPE  DEATHS  DR_DRINK  YEAR  \n",
       "0         7       2         0  2013  \n",
       "1        88       0         0  2013  \n",
       "2        89       1         0  2013  \n",
       "3         1       1         0  2013  \n",
       "4         7       2         0  2013  \n",
       "\n",
       "[5 rows x 103 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "accident_13 = dbf2DF('pre_2015_fars/FARS_2013/accident.dbf')\n",
    "person_13 = dbf2DF('pre_2015_fars/FARS_2013/person.dbf')\n",
    "vehicle_13 = dbf2DF('pre_2015_fars/FARS_2013/vehicle.dbf')\n",
    "accident_13['YEAR'] = 2013 \n",
    "person_13['YEAR'] = 2013 \n",
    "vehicle_13['YEAR'] = 2013 \n",
    "\n",
    "vehicle_13.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_CASE</th>\n",
       "      <th>VEH_NO</th>\n",
       "      <th>VE_FORMS</th>\n",
       "      <th>NUMOCCS</th>\n",
       "      <th>DAY</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>HARM_EV</th>\n",
       "      <th>...</th>\n",
       "      <th>VTCONT_F</th>\n",
       "      <th>P_CRASH1</th>\n",
       "      <th>P_CRASH2</th>\n",
       "      <th>P_CRASH3</th>\n",
       "      <th>PCRASH4</th>\n",
       "      <th>PCRASH5</th>\n",
       "      <th>ACC_TYPE</th>\n",
       "      <th>DEATHS</th>\n",
       "      <th>DR_DRINK</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>15</td>\n",
       "      <td>14</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>87</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>10004</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>51</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10004</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 127 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE  ST_CASE  VEH_NO  VE_FORMS  NUMOCCS  DAY  MONTH  HOUR  MINUTE  \\\n",
       "0      1    10001       1         1        2    1      1    20      33   \n",
       "1      1    10002       1         1        1    1      1    13      15   \n",
       "2      1    10003       1         1        6    2      1     1      35   \n",
       "3      1    10004       1         2        1    5      1    15      12   \n",
       "4      1    10004       2         2        4    5      1    15      12   \n",
       "\n",
       "   HARM_EV  ...   VTCONT_F  P_CRASH1  P_CRASH2  P_CRASH3  PCRASH4  PCRASH5  \\\n",
       "0        1  ...          0        14        13         6        1        4   \n",
       "1       14  ...          0         1        15         1        1        4   \n",
       "2       35  ...          0         1        87         7        1        4   \n",
       "3       12  ...          0         1        51         1        1        1   \n",
       "4       12  ...          0         1        53         1        1        1   \n",
       "\n",
       "   ACC_TYPE  DEATHS  DR_DRINK  YEAR  \n",
       "0         6       1         1  2012  \n",
       "1        11       1         0  2012  \n",
       "2         1       1         0  2012  \n",
       "3        24       1         0  2012  \n",
       "4        25       0         0  2012  \n",
       "\n",
       "[5 rows x 127 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "accident_12 = dbf2DF('pre_2015_fars/FARS_2012/accident.dbf')\n",
    "person_12 = dbf2DF('pre_2015_fars/FARS_2012/person.dbf')\n",
    "vehicle_12 = dbf2DF('pre_2015_fars/FARS_2012/vehicle.dbf')\n",
    "accident_12['YEAR'] = 2012 \n",
    "person_12['YEAR'] = 2012 \n",
    "vehicle_12['YEAR'] = 2012 \n",
    "\n",
    "vehicle_12.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_CASE</th>\n",
       "      <th>VEH_NO</th>\n",
       "      <th>VE_FORMS</th>\n",
       "      <th>NUMOCCS</th>\n",
       "      <th>DAY</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>HARM_EV</th>\n",
       "      <th>...</th>\n",
       "      <th>VTCONT_F</th>\n",
       "      <th>P_CRASH1</th>\n",
       "      <th>P_CRASH2</th>\n",
       "      <th>P_CRASH3</th>\n",
       "      <th>PCRASH4</th>\n",
       "      <th>PCRASH5</th>\n",
       "      <th>ACC_TYPE</th>\n",
       "      <th>DEATHS</th>\n",
       "      <th>DR_DRINK</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>51</td>\n",
       "      <td>34</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>62</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>51</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10005</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>35</td>\n",
       "      <td>34</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 128 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE  ST_CASE  VEH_NO  VE_FORMS  NUMOCCS  DAY  MONTH  HOUR  MINUTE  \\\n",
       "0      1    10001       1         1        1    4      1    16      51   \n",
       "1      1    10002       1         1        1    4      1    23      40   \n",
       "2      1    10003       1         2        1    5      1     9      14   \n",
       "3      1    10003       2         2        4    5      1     9      14   \n",
       "4      1    10005       1         1        1    8      1    15      35   \n",
       "\n",
       "   HARM_EV  ...   VTCONT_F  P_CRASH1  P_CRASH2  P_CRASH3  PCRASH4  PCRASH5  \\\n",
       "0       34  ...          0         1        13         1        1        4   \n",
       "1       35  ...          0        14         9         1        4        4   \n",
       "2       12  ...          0        14        10         1        1        2   \n",
       "3       12  ...          0        14        62         1        1        1   \n",
       "4       34  ...          0         1        13         1        1        4   \n",
       "\n",
       "   ACC_TYPE  DEATHS  DR_DRINK  YEAR  \n",
       "0         1       1         1  2011  \n",
       "1         2       1         0  2011  \n",
       "2        50       0         1  2011  \n",
       "3        51       2         0  2011  \n",
       "4         1       1         0  2011  \n",
       "\n",
       "[5 rows x 128 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "accident_11 = dbf2DF('pre_2015_fars/FARS_2011/accident.dbf')\n",
    "person_11 = dbf2DF('pre_2015_fars/FARS_2011/person.dbf')\n",
    "vehicle_11 = dbf2DF('pre_2015_fars/FARS_2011/vehicle.dbf')\n",
    "accident_11['YEAR'] = 2011 \n",
    "person_11['YEAR'] = 2011 \n",
    "vehicle_11['YEAR'] = 2011 \n",
    "\n",
    "vehicle_11.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#make combined dataframes\n",
    "a_frames = [accident_15, accident_14, accident_13, accident_12, accident_11]\n",
    "accident_all = pd.concat(a_frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "p_frames = [person_15, person_14, person_13, person_12, person_11]\n",
    "person_all = pd.concat(p_frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "v_frames = [vehicle_15, vehicle_14, vehicle_13, vehicle_12, vehicle_11]\n",
    "vehicle_all = pd.concat(v_frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#compare headers\n",
    "#list(vehicle_all)\n",
    "#list(person_all)\n",
    "#list(accident_all)\n",
    "\n",
    "#CITY, and COUNTY are only in accident\n",
    "#combine by STATE and ST_CASE?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_CASE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>CITY</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>127</td>\n",
       "      <td>0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>-87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>83</td>\n",
       "      <td>0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>-87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>-86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>10004</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>-86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10005</td>\n",
       "      <td>45</td>\n",
       "      <td>2050</td>\n",
       "      <td>31.0</td>\n",
       "      <td>-86.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATE  ST_CASE  COUNTY  CITY  LATITUDE  LONGITUD\n",
       "0      1    10001     127     0      34.0     -87.0\n",
       "1      1    10002      83     0      35.0     -87.0\n",
       "2      1    10003      11     0      32.0     -86.0\n",
       "3      1    10004      45     0      31.0     -86.0\n",
       "4      1    10005      45  2050      31.0     -86.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loc_frame = accident_all[['STATE', 'ST_CASE', 'COUNTY', 'CITY', 'LATITUDE', 'LONGITUD']]\n",
    "loc_frame.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#merge to add location info to vehicle and person dataframes\n",
    "person_all = pd.merge(person_all,loc_frame,how='left', on = ['STATE', 'ST_CASE', 'COUNTY'])\n",
    "vehicle_all = pd.merge(vehicle_all,loc_frame,how='left', on = ['STATE', 'ST_CASE'])\n",
    "#list(person_all)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import census data, 2011-2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUMLEV</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>AGEGRP</th>\n",
       "      <th>TOT_POP</th>\n",
       "      <th>TOT_MALE</th>\n",
       "      <th>TOT_FEMALE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2011</td>\n",
       "      <td>0</td>\n",
       "      <td>55253</td>\n",
       "      <td>26999</td>\n",
       "      <td>28254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>3581</td>\n",
       "      <td>1864</td>\n",
       "      <td>1717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2011</td>\n",
       "      <td>2</td>\n",
       "      <td>3940</td>\n",
       "      <td>1954</td>\n",
       "      <td>1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2011</td>\n",
       "      <td>3</td>\n",
       "      <td>4355</td>\n",
       "      <td>2223</td>\n",
       "      <td>2132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2011</td>\n",
       "      <td>4</td>\n",
       "      <td>3954</td>\n",
       "      <td>2033</td>\n",
       "      <td>1921</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SUMLEV  STATE  COUNTY   STNAME         CTYNAME  YEAR  AGEGRP  TOT_POP  \\\n",
       "57      50      1       1  Alabama  Autauga County  2011       0    55253   \n",
       "58      50      1       1  Alabama  Autauga County  2011       1     3581   \n",
       "59      50      1       1  Alabama  Autauga County  2011       2     3940   \n",
       "60      50      1       1  Alabama  Autauga County  2011       3     4355   \n",
       "61      50      1       1  Alabama  Autauga County  2011       4     3954   \n",
       "\n",
       "    TOT_MALE  TOT_FEMALE  \n",
       "57     26999       28254  \n",
       "58      1864        1717  \n",
       "59      1954        1986  \n",
       "60      2223        2132  \n",
       "61      2033        1921  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census = pd.DataFrame.from_csv('census_data/cc-est2015-subset.csv', index_col=None, encoding = 'utf-8')\n",
    "census = census[census['YEAR'] >= 4] #filter out desired census years, 2011:2015\n",
    "#convert coded census years to actual years, 4=2011; 5=2012; 6=2013; 7=2014; 8=2015\n",
    "census['YEAR'] = census['YEAR'] + 2007\n",
    "census.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write output files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "accident_all.to_csv('wrangled_data/accident_2011_2015.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "person_all.to_csv('wrangled_data/person_2011_2015.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "vehicle_all.to_csv('wrangled_data/vehicle_2011_2015.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "census.to_csv('wrangled_data/census_2011_2015.csv')"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}