Data_Processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "474aec2d-2c98-4eb4-9eae-064f63a9e16d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-09-07T20:45:54.265949600Z",
     "start_time": "2023-09-07T20:45:39.272631200Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Ben\\AppData\\Local\\Temp\\ipykernel_29520\\1448207667.py:4: DtypeWarning: Columns (33,45) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv('C:\\\\Users\\\\Ben\\\\OneDrive\\\\Documents\\\\MarylandData\\\\Maryland_Statewide_Vehicle_Crashes.csv')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#File too large for Github - Create Relative Path\n",
    "df = pd.read_csv('C:\\\\Users\\\\Ben\\\\OneDrive\\\\Documents\\\\MarylandData\\\\Maryland_Statewide_Vehicle_Crashes.csv')\n",
    "df = df[['YEAR', 'COUNTY_DESC', 'REPORT_TYPE', 'ACC_DATE', 'ACC_TIME', 'LATITUDE', 'LONGITUDE']]\n",
    "df['COUNTY_DESC'] = df['COUNTY_DESC'].str.replace(\"'\", \"\")\n",
    "\n",
    "# Convert ACC_DATE to datetime format\n",
    "df['ACC_DATE'] = pd.to_datetime(df['ACC_DATE'], format='%Y%m%d')\n",
    "# Combine ACC_DATE and ACC_TIME into a single datetime column\n",
    "df['ACC_DATETIME'] = pd.to_datetime(df['ACC_DATE'].astype(str) + ' ' + df['ACC_TIME'])\n",
    "\n",
    "# Extract hour and month from ACC_DATETIME\n",
    "df['HOUR'] = df['ACC_DATETIME'].dt.hour\n",
    "df['MONTH'] = df['ACC_DATETIME'].dt.month\n",
    "df['DAY'] = df['ACC_DATETIME'].dt.dayofweek\n",
    "df = df.drop(columns=['ACC_DATETIME','ACC_TIME'])\n",
    "df_small = df[df['YEAR'].isin([2015,2016,2017,2018,2019,2020]) == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a512d638-d4ab-46cd-9abd-2d82b18ccaea",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-09-07T20:48:58.914985800Z",
     "start_time": "2023-09-07T20:48:56.863641500Z"
    }
   },
   "outputs": [],
   "source": [
    "df_small.to_csv('Data_Processed.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d6f901a5-22d6-4786-9453-1be1dfba64e1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-09-07T20:49:00.589830Z",
     "start_time": "2023-09-07T20:48:58.919987Z"
    }
   },
   "outputs": [],
   "source": [
    "# Pre-compute the data for the plots\n",
    "hourly_counts = df.groupby(['HOUR', 'COUNTY_DESC', 'REPORT_TYPE']).size().reset_index()\n",
    "hourly_counts.columns = ['HOUR', 'COUNTY_DESC', 'REPORT_TYPE', 'COUNT']\n",
    "county_counts = df.groupby(['COUNTY_DESC', 'REPORT_TYPE']).size().unstack(fill_value=0).reset_index()\n",
    "\n",
    "#For Long Term Trends\n",
    "daily_counts = df.groupby(['ACC_DATE', 'COUNTY_DESC']).size().reset_index()\n",
    "daily_counts['ACC_DATE'] = pd.to_datetime(daily_counts['ACC_DATE'])\n",
    "daily_counts.columns = ['ACC_DATE', 'COUNTY_DESC', 'COUNT']\n",
    "daily_counts.reset_index(inplace=True)\n",
    "daily_counts['day_of_year'] = daily_counts['ACC_DATE'].dt.dayofyear\n",
    "pre_covid = daily_counts[daily_counts['ACC_DATE'] < '2020-03-1']\n",
    "post_covid = daily_counts[daily_counts['ACC_DATE'] >= '2020-03-1']\n",
    "pre_covid_avg = pre_covid.groupby('day_of_year')['COUNT'].mean()\n",
    "post_covid = post_covid.merge(pre_covid_avg, on='day_of_year', how='left', suffixes=('_post', '_pre'))\n",
    "post_covid['percentage_of_pre_covid'] = (post_covid['COUNT_post'] / post_covid['COUNT_pre']) * 100\n",
    "post_covid['Day_of_Year'] = post_covid['ACC_DATE'].dt.dayofyear\n",
    "post_covid['Year'] = post_covid['ACC_DATE'].dt.year\n",
    "post_covid['percentage_of_pre_covid_smooth'] = post_covid['percentage_of_pre_covid'].rolling(window=30).mean()\n",
    "\n",
    "# Pre-compute the data for the plots\n",
    "monthly_counts = df.groupby(['MONTH', 'COUNTY_DESC', 'REPORT_TYPE']).size().reset_index()\n",
    "monthly_counts.columns = ['MONTH', 'COUNTY_DESC', 'REPORT_TYPE', 'COUNT']\n",
    "\n",
    "# Pre-compute the data for the plots\n",
    "daily_counts = df.groupby(['DAY', 'COUNTY_DESC', 'REPORT_TYPE']).size().reset_index()\n",
    "daily_counts.columns = ['DAY', 'COUNTY_DESC', 'REPORT_TYPE', 'COUNT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dd9a0c57-eb71-479c-9a43-2d7d398f6847",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-09-07T20:49:04.152552300Z",
     "start_time": "2023-09-07T20:49:03.865450200Z"
    }
   },
   "outputs": [],
   "source": [
    "# In FeatureEngineering.ipynb\n",
    "hourly_counts.to_csv('assets/hourly_counts.csv', index=False)\n",
    "post_covid.to_csv('assets/post_covid.csv', index=False)\n",
    "county_counts.to_csv('assets/county_counts.csv', index=False)\n",
    "monthly_counts.to_csv('assets/monthly_counts.csv', index=False)\n",
    "daily_counts.to_csv('assets/daily_counts.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}