Kehinde_combine_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PROJECT 5 \n",
    "Kehinde Ajayi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CREATE MULTI-YEAR DATAFRAMES FROM FARS DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function for merging data\n",
    "\n",
    "def get_merged_dataframes(main_df, main_df_columns, csv_files, year, merge_columns):\n",
    "    \n",
    "    df1 = pd.read_csv(f'./data/{year}_fars/{main_df}.csv', encoding='unicode_escape', low_memory=False)\n",
    "    df1.columns = df1.columns.str.lower()\n",
    "    merged_dataframe = df1[df1.columns.intersection(main_df_columns)]\n",
    "    \n",
    "    for csv in csv_files:\n",
    "        try:\n",
    "            df2 = pd.read_csv(f'./data/{year}_fars/{csv}.csv', encoding='unicode_escape', low_memory=False)\n",
    "            df2.columns = df2.columns.str.lower() \n",
    "            merged_dataframe = pd.merge(merged_dataframe, df2, how='outer', on=merge_columns, suffixes=('', '_remove'))\n",
    "            merged_dataframe = merged_dataframe.drop([column for column in merged_dataframe.columns if 'remove' in column], axis=1)\n",
    "        except FileNotFoundError:\n",
    "            print(f'The {csv} file does not exist for the year {year}.')           \n",
    "    \n",
    "    merged_dataframe.loc[:,'data_year'] = year\n",
    "    \n",
    "    return merged_dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# funcion for capturing files, columns, and years of interest\n",
    "\n",
    "def combine_data(main_csv, columns, csvs_to_merge, merge_columns, start_year, end_year=None):    \n",
    "\n",
    "    if end_year is None:\n",
    "        end_year = start_year\n",
    "    \n",
    "    full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n",
    "    \n",
    "    return pd.concat(full_df_list, ignore_index=True)\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merge all person-level data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The nmdistract file does not exist for the year 2010.\n",
      "The drugs file does not exist for the year 2010.\n",
      "The personrf file does not exist for the year 2010.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_29668/506839148.py:8: DtypeWarning: Columns (33) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The nmdistract file does not exist for the year 2011.\n",
      "The drugs file does not exist for the year 2011.\n",
      "The personrf file does not exist for the year 2011.\n",
      "The nmdistract file does not exist for the year 2012.\n",
      "The drugs file does not exist for the year 2012.\n",
      "The personrf file does not exist for the year 2012.\n",
      "The nmdistract file does not exist for the year 2013.\n",
      "The drugs file does not exist for the year 2013.\n",
      "The personrf file does not exist for the year 2013.\n",
      "The nmdistract file does not exist for the year 2014.\n",
      "The drugs file does not exist for the year 2014.\n",
      "The personrf file does not exist for the year 2014.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_29668/506839148.py:8: DtypeWarning: Columns (15) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The nmdistract file does not exist for the year 2015.\n",
      "The drugs file does not exist for the year 2015.\n",
      "The personrf file does not exist for the year 2015.\n",
      "The nmdistract file does not exist for the year 2016.\n",
      "The drugs file does not exist for the year 2016.\n",
      "The personrf file does not exist for the year 2016.\n",
      "The nmdistract file does not exist for the year 2017.\n",
      "The drugs file does not exist for the year 2017.\n",
      "The personrf file does not exist for the year 2017.\n",
      "The nmdistract file does not exist for the year 2018.\n",
      "The personrf file does not exist for the year 2018.\n",
      "The personrf file does not exist for the year 2019.\n"
     ]
    }
   ],
   "source": [
    "## add columns of interest to 'columns' variable\n",
    "columns = ['state', 'st_case', 'veh_no', 'per_no', 'county', 'age', 'sex', \n",
    "           'per_typ', 'inj_sev', 'seat_pos', 'rest_use', 'rest_mis', 'air_bag', 'ejection',\n",
    "           'ej_path', 'extricat', 'drinking', 'alc_det', 'alc_status', 'atst_typ', 'alc_res',\n",
    "           'drugs', 'drug_det', 'dstatus', 'hospital', 'doa', 'death_da', 'death_mo', \n",
    "           'death_yr', 'death_hr', 'death_mn', 'death_tm', 'n_mot_no', 'location',\n",
    "           'helm_use', 'helm_mis', 'str_veh', 'p_sf1', 'p_sf2', 'p_sf3', 'drugres1', 'drugres2',\n",
    "           'drugres3', 'drugtst1', 'drugtst2', 'drugtst3']\n",
    "\n",
    "## add .csv files of interest to 'csvs_to'merge' variable\n",
    "csvs_to_merge = ['per_aux', 'nmprior', 'nmcrash', 'nmimpair',\n",
    "                   'safetyeq', 'nmdistract', 'drugs', 'personrf']\n",
    "\n",
    "## add columns on which .csv files will be merged to the 'merge_columns' variable\n",
    "merge_columns = ['st_case', 'veh_no', 'per_no']\n",
    "\n",
    "\n",
    "## get DataFrame\n",
    "full_person_df = combine_data('person', columns, csvs_to_merge, merge_columns, 2010, 2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(962382, 103)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_person_df.shape"
   ]
  },
  {
   "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>state</th>\n",
       "      <th>st_case</th>\n",
       "      <th>veh_no</th>\n",
       "      <th>per_no</th>\n",
       "      <th>n_mot_no</th>\n",
       "      <th>county</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>per_typ</th>\n",
       "      <th>inj_sev</th>\n",
       "      <th>...</th>\n",
       "      <th>mnmdstrd</th>\n",
       "      <th>mnmdstrdname</th>\n",
       "      <th>nmaction</th>\n",
       "      <th>nmactionname</th>\n",
       "      <th>nmcc</th>\n",
       "      <th>nmccname</th>\n",
       "      <th>nmdistract</th>\n",
       "      <th>nmdistractname</th>\n",
       "      <th>personrf</th>\n",
       "      <th>personrfname</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>0.0</td>\n",
       "      <td>81</td>\n",
       "      <td>51</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>81</td>\n",
       "      <td>999</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35</td>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>0.0</td>\n",
       "      <td>97</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10003</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>97</td>\n",
       "      <td>45</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 103 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  st_case  veh_no  per_no  n_mot_no  county  age  sex  per_typ  \\\n",
       "0      1    10001       1       1       0.0      81   51    2        1   \n",
       "1      1    10001       1       2       0.0      81  999    1        2   \n",
       "2      1    10002       1       1       0.0      35   44    2        1   \n",
       "3      1    10003       1       1       0.0      97   27    1        1   \n",
       "4      1    10003       2       1       0.0      97   45    2        1   \n",
       "\n",
       "   inj_sev  ...  mnmdstrd  mnmdstrdname  nmaction  nmactionname  nmcc  \\\n",
       "0        4  ...       NaN           NaN       NaN           NaN   NaN   \n",
       "1        3  ...       NaN           NaN       NaN           NaN   NaN   \n",
       "2        4  ...       NaN           NaN       NaN           NaN   NaN   \n",
       "3        4  ...       NaN           NaN       NaN           NaN   NaN   \n",
       "4        0  ...       NaN           NaN       NaN           NaN   NaN   \n",
       "\n",
       "   nmccname  nmdistract  nmdistractname  personrf  personrfname  \n",
       "0       NaN         NaN             NaN       NaN           NaN  \n",
       "1       NaN         NaN             NaN       NaN           NaN  \n",
       "2       NaN         NaN             NaN       NaN           NaN  \n",
       "3       NaN         NaN             NaN       NaN           NaN  \n",
       "4       NaN         NaN             NaN       NaN           NaN  \n",
       "\n",
       "[5 rows x 103 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_person_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merge all driver-level data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (49,50) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2010.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (48,60,61) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2011.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (60,61) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2012.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (36,37) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2013.\n",
      "The driverrf file does not exist for the year 2014.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (14,56,58) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2015.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (14,56,58,189,191) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2016.\n",
      "The driverrf file does not exist for the year 2017.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (14,56,58,193,195) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2018.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (56,58,193,195) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The driverrf file does not exist for the year 2019.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_267943/506839148.py:8: DtypeWarning: Columns (14,56,58,179,181) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  full_df_list = [get_merged_dataframes(main_csv, columns, csvs_to_merge, year, merge_columns) for year in range(start_year, end_year + 1)]\n"
     ]
    }
   ],
   "source": [
    "columns = ['state', 'st_case', 'veh_no', 'dr_pres', 'l_state', 'dr_zip', 'l_type', 'l_status', \n",
    "                  'cdl_stat', 'l_endors', 'l_compl', 'l_restri', 'dr_hgt', 'dr_wgt', 'prev_oth',\n",
    "                  'first_mo', 'first_yr', 'last_mo', 'last_yr', 'speedrel', 'dr_sf1', 'dr_sf2', \n",
    "           'dr_sf3', 'dr_sf4' ]\n",
    "\n",
    "csvs_to_merge = ['veh_aux', 'violatn', 'drimpair', 'driverrf'] \n",
    "\n",
    "merge_columns = ['st_case', 'veh_no']\n",
    "\n",
    "\n",
    "full_driver_df = combine_data('vehicle', columns, csvs_to_merge, merge_columns, 2010, 2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(584270, 50)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_driver_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>state</th>\n",
       "      <th>st_case</th>\n",
       "      <th>veh_no</th>\n",
       "      <th>dr_pres</th>\n",
       "      <th>l_state</th>\n",
       "      <th>dr_zip</th>\n",
       "      <th>l_status</th>\n",
       "      <th>l_type</th>\n",
       "      <th>cdl_stat</th>\n",
       "      <th>l_endors</th>\n",
       "      <th>l_compl</th>\n",
       "      <th>l_restri</th>\n",
       "      <th>dr_hgt</th>\n",
       "      <th>dr_wgt</th>\n",
       "      <th>prev_oth</th>\n",
       "      <th>first_mo</th>\n",
       "      <th>first_yr</th>\n",
       "      <th>last_mo</th>\n",
       "      <th>last_yr</th>\n",
       "      <th>speedrel</th>\n",
       "      <th>dr_sf1</th>\n",
       "      <th>dr_sf2</th>\n",
       "      <th>dr_sf3</th>\n",
       "      <th>dr_sf4</th>\n",
       "      <th>year</th>\n",
       "      <th>a_drdis</th>\n",
       "      <th>a_drdro</th>\n",
       "      <th>a_vrd</th>\n",
       "      <th>a_body</th>\n",
       "      <th>a_imp1</th>\n",
       "      <th>a_imp2</th>\n",
       "      <th>a_vroll</th>\n",
       "      <th>a_lic_s</th>\n",
       "      <th>a_lic_c</th>\n",
       "      <th>a_cdl_s</th>\n",
       "      <th>a_mc_l_s</th>\n",
       "      <th>a_spveh</th>\n",
       "      <th>a_sbus</th>\n",
       "      <th>a_mod_yr</th>\n",
       "      <th>a_fire_exp</th>\n",
       "      <th>mviolatn</th>\n",
       "      <th>drimpair</th>\n",
       "      <th>data_year</th>\n",
       "      <th>statename</th>\n",
       "      <th>mviolatnname</th>\n",
       "      <th>drimpairname</th>\n",
       "      <th>violation</th>\n",
       "      <th>violationname</th>\n",
       "      <th>driverrf</th>\n",
       "      <th>driverrfname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>30296.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10002</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36401.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36607.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36606.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36607.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  st_case  veh_no  dr_pres  l_state   dr_zip  l_status  l_type  \\\n",
       "0    1.0    10001       1      1.0     13.0  30296.0       6.0     1.0   \n",
       "1    1.0    10002       1      1.0      1.0  36401.0       6.0     1.0   \n",
       "2    1.0    10003       1      1.0      1.0  36607.0       6.0     1.0   \n",
       "3    1.0    10003       2      1.0      1.0  36606.0       6.0     1.0   \n",
       "4    1.0    10003       3      1.0      1.0  36607.0       6.0     1.0   \n",
       "\n",
       "   cdl_stat  l_endors  l_compl  l_restri  dr_hgt  dr_wgt  prev_oth  first_mo  \\\n",
       "0       0.0       0.0      3.0       1.0    65.0   180.0       0.0       9.0   \n",
       "1       0.0       0.0      3.0       0.0    65.0   137.0       0.0       0.0   \n",
       "2       0.0       0.0      2.0       1.0    80.0   300.0       2.0       9.0   \n",
       "3       0.0       0.0      3.0       0.0    61.0   140.0       0.0       8.0   \n",
       "4       0.0       0.0      3.0       1.0    66.0   124.0       0.0       0.0   \n",
       "\n",
       "   first_yr  last_mo  last_yr  speedrel  dr_sf1  dr_sf2  dr_sf3  dr_sf4  \\\n",
       "0    2008.0     11.0   2008.0       0.0     0.0     0.0     0.0     0.0   \n",
       "1       0.0      0.0      0.0       0.0     0.0     0.0     0.0     0.0   \n",
       "2    2009.0     10.0   2009.0       1.0     0.0     0.0     0.0     0.0   \n",
       "3    2008.0      8.0   2008.0       0.0    38.0     0.0     0.0     0.0   \n",
       "4       0.0      0.0      0.0       0.0     0.0     0.0     0.0     0.0   \n",
       "\n",
       "     year  a_drdis  a_drdro  a_vrd  a_body  a_imp1  a_imp2  a_vroll  a_lic_s  \\\n",
       "0  2010.0      2.0      1.0    1.0     3.0     7.0     7.0      1.0      1.0   \n",
       "1  2010.0      2.0      2.0    1.0     1.0     2.0     2.0      2.0      1.0   \n",
       "2  2010.0      2.0      2.0    2.0     7.0     6.0     6.0      2.0      4.0   \n",
       "3  2010.0      2.0      2.0    2.0     1.0     2.0     2.0      2.0      1.0   \n",
       "4  2010.0      2.0      2.0    2.0     1.0     2.0     2.0      2.0      1.0   \n",
       "\n",
       "   a_lic_c  a_cdl_s  a_mc_l_s  a_spveh  a_sbus  a_mod_yr  a_fire_exp  \\\n",
       "0      1.0      2.0       4.0      2.0     3.0    2000.0         2.0   \n",
       "1      1.0      2.0       4.0      2.0     3.0    1998.0         1.0   \n",
       "2      2.0      2.0       2.0      1.0     3.0    2005.0         1.0   \n",
       "3      1.0      2.0       4.0      2.0     3.0    2008.0         1.0   \n",
       "4      1.0      2.0       4.0      2.0     3.0    2006.0         1.0   \n",
       "\n",
       "   mviolatn  drimpair  data_year statename mviolatnname drimpairname  \\\n",
       "0       0.0         2       2010       NaN          NaN          NaN   \n",
       "1       0.0         0       2010       NaN          NaN          NaN   \n",
       "2       0.0         0       2010       NaN          NaN          NaN   \n",
       "3       0.0         0       2010       NaN          NaN          NaN   \n",
       "4       0.0         0       2010       NaN          NaN          NaN   \n",
       "\n",
       "   violation violationname  driverrf driverrfname  \n",
       "0        NaN           NaN       NaN          NaN  \n",
       "1        NaN           NaN       NaN          NaN  \n",
       "2        NaN           NaN       NaN          NaN  \n",
       "3        NaN           NaN       NaN          NaN  \n",
       "4        NaN           NaN       NaN          NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_driver_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save multi-year person- and driver-level data in separate .csv files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_person_df.to_csv('./data/person-level_data_2010-2020.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_driver_df.to_csv('./data/driver-level_data_2010-2020.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "14fbf35ae29aaef9ec85ed54127d8f739882b43edc5189ff8e97bf95ba3ac371"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}