03. Initial ETL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Initial ETL\n",
    "\n",
    "In this notebook I am gonna do a bunch of stuff to get the data ready for the model. I might need to come back and do some more ETL as I work on the modeling bit, but this is the initial round."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib notebook\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.cm as cm\n",
    "from tqdm.autonotebook import tqdm\n",
    "import traceback\n",
    "\n",
    "from sklearn import preprocessing\n",
    "\n",
    "from utils.utilities import mk_heatmap, value_heatmap, select_by_date, update_grade\n",
    "from utils.strings import DSNY_311, DEP_311, DOHMH_311"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load all the data\n",
    "inspecs = pd.read_csv('./data/inspections.csv')\n",
    "threeoneone = pd.read_csv('./data/311.csv')\n",
    "weather = pd.read_csv('./data/NYC_historical_weather.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### But there are multiple citations for each restaurant in the df\n",
    "\n",
    "For some reason I missed the fact that each row in the inspections data contains a single violation. We are gonna need to merge all the different rows into single restaurant inspections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_single_visits = inspecs.drop_duplicates(subset=['camis', 'inspection_date'])\n",
    "\n",
    "cols_to_merge = ['violation_code', 'violation_description', 'critical_flag', 'inspection_type']\n",
    "\n",
    "for col in tqdm(cols_to_merge, total=4):\n",
    "    df_tmp = inspecs[col].groupby([inspecs.camis, inspecs.inspection_date]).apply(list).reset_index()\n",
    "    inspecs_single_visits = pd.merge(inspecs_single_visits, df_tmp,  how='left', left_on=['camis','inspection_date'], right_on = ['camis','inspection_date'])\n",
    "    inspecs_single_visits.drop(columns=f'{col}_x', axis=1, inplace=True)\n",
    "    inspecs_single_visits.rename(columns={f'{col}_y': f\"{col}\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_single_visits.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs = inspecs_single_visits"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean up some of the dtypes and replace missing values with better values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the missing lats and lons with nan\n",
    "inspecs['latitude'].replace(0, np.nan, inplace=True)\n",
    "inspecs['longitude'].replace(0, np.nan, inplace=True)\n",
    "threeoneone['latitude'].replace(0, np.nan, inplace=True)\n",
    "threeoneone['longitude'].replace(0, np.nan, inplace=True)\n",
    "\n",
    "# drop rows that don't have a lat and lon attached\n",
    "inspecs.dropna(subset = ['latitude', 'longitude'], inplace=True)\n",
    "threeoneone.dropna(subset = ['latitude', 'longitude'], inplace=True)\n",
    "\n",
    "# Do date things\n",
    "inspecs.inspection_date = pd.to_datetime(inspecs.inspection_date)\n",
    "threeoneone.created_date = pd.to_datetime(threeoneone.created_date)\n",
    "weather.DATE = pd.to_datetime(weather.DATE)\n",
    "\n",
    "# update the grades where a score is given but the letter grade is missing\n",
    "inspecs.grade = inspecs.apply(update_grade, axis=1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge (or create) extra data into the inspections frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Does the inspection have a critical violation?\n",
    "nested = inspecs['critical_flag'].values.tolist()\n",
    "inspecs['critical'] = [1 if 'Y' in sublist else 0 for sublist in nested]\n",
    "\n",
    "# make a column to store which day of the week it is... \n",
    "# Remember that Monday is 0 Sunday = 6\n",
    "# inspecs['weekday'] = inspecs.inspection_date.dt.weekday\n",
    "# threeoneone['weekday'] = threeoneone.created_date.dt.weekday\n",
    "# decided I want the day name\n",
    "inspecs['weekday'] = inspecs.inspection_date.dt.day_name()\n",
    "threeoneone['weekday'] = threeoneone.created_date.dt.day_name()\n",
    "\n",
    "\n",
    "# merge the weather data into the frame\n",
    "inspecs = inspecs.merge(weather[['DATE', 'TMAX']], left_on='inspection_date', right_on='DATE')\n",
    "# drop the extra date column\n",
    "inspecs.drop(labels='DATE', axis=1, inplace=True)\n",
    "# rename to be lower case\n",
    "inspecs.rename(columns={'TMAX': 'tmax'}, inplace=True)\n",
    "\n",
    "# now we are gonnna create a bunch of temporal stuff\n",
    "\n",
    "# gotta make sure the dates are in order\n",
    "inspecs.sort_values(['camis', 'inspection_date'], inplace=True)\n",
    "\n",
    "# time since last inspection\n",
    "inspecs['time_since_last'] = inspecs.groupby('camis')['inspection_date'].diff().apply(lambda x: x.days)\n",
    "# past critical violation?\n",
    "inspecs['past_critical'] = inspecs.groupby(['camis'])['critical'].shift()\n",
    "# past grade and score\n",
    "inspecs['past_grade'] = inspecs.groupby(['camis'])['grade'].shift()\n",
    "inspecs['past_score'] = inspecs.groupby(['camis'])['score'].shift()\n",
    "\n",
    "# clean up a few things with the temporal stuff\n",
    "inspecs.time_since_last.replace(np.nan, 0, inplace=True)\n",
    "inspecs.past_critical.replace(np.nan, 0, inplace=True)\n",
    "\n",
    "# start_date = '2016-01-01'\n",
    "# end_date = '2016-12-31'\n",
    "\n",
    "# inspecs_2016 = select_by_date(inspecs, start_date, end_date)\n",
    "# threeoneone_2016 = select_by_date(threeoneone, start_date, end_date)\n",
    "# nypd_2016 = select_by_date(nypd, start_date, end_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs.head(5).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now we need to add all the heat map data\n",
    "\n",
    "This is going to be the longest/most challenging bit. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def score_heatmap(in_frame, out_frame, heatmap_frame, date_key='inspection_date', \n",
    "                  out_key='heat_score', window=90, s=2, bins=1000):\n",
    "    \n",
    "    grouped = in_frame.groupby(date_key)\n",
    "    \n",
    "    for date, group in tqdm(grouped, total=len(grouped)):\n",
    "    \n",
    "        # create the heat map\n",
    "        end_date = pd.to_datetime(date)\n",
    "        start_date = end_date - pd.to_timedelta(window, unit='days')\n",
    "\n",
    "        # build the heataps for the 311 and nypd\n",
    "        heatmap_frame_date = select_by_date(heatmap_frame, start_date, end_date)        \n",
    "        \n",
    "        if not heatmap_frame_date.shape[0]:\n",
    "            continue\n",
    "\n",
    "        img, extent, xedges, yedges= mk_heatmap(heatmap_frame_date.longitude.values, \n",
    "                                                heatmap_frame_date.latitude.values, s, bins=bins)\n",
    "\n",
    "        for idx, lo, la in zip(group.index, group.longitude.values, group.latitude.values):\n",
    "    \n",
    "            out_frame[out_key].iloc[idx] = value_heatmap(lo, la, xedges, yedges, img)\n",
    "    \n",
    "    return out_frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "target_frame = pd.DataFrame(index=inspecs.index)\n",
    "target_frame['dsny_heat_score'] = 0.0\n",
    "target_frame['dep_heat_score'] = 0.0\n",
    "target_frame['dohmh_heat_score'] = 0.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "heatmap_frame = threeoneone[threeoneone.complaint_type.isin(DSNY_311)]\n",
    "target_frame = score_heatmap(inspecs, target_frame, heatmap_frame, 'inspection_date', 'dsny_heat_score')\n",
    "\n",
    "heatmap_frame = threeoneone[threeoneone.complaint_type.isin(DEP_311)]\n",
    "target_frame = score_heatmap(inspecs, target_frame, heatmap_frame, 'inspection_date', 'dep_heat_score')\n",
    "\n",
    "heatmap_frame = threeoneone[threeoneone.complaint_type.isin(DOHMH_311)]\n",
    "target_frame = score_heatmap(inspecs, target_frame, heatmap_frame, 'inspection_date', 'dohmh_heat_score')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "target_frame.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores = inspecs.merge(target_frame, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores.head(5).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write things to a CSV !!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores.to_csv('./data/inspecs_heat_scores.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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}