05. More ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/boada/.local/lib/python3.7/site-packages/pandas/compat/__init__.py:85: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.\n",
      "  warnings.warn(msg)\n",
      "/home/boada/.local/lib/python3.7/site-packages/ipykernel_launcher.py:6: TqdmExperimentalWarning: Using `tqdm.autonotebook.tqdm` in notebook mode. Use `tqdm.tqdm` instead to force console mode (e.g. in jupyter console)\n",
      "  \n"
     ]
    }
   ],
   "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, DATE, VIOLATIONS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Second round of thoughts\n",
    "\n",
    "I've done some more thinking about it, looking at the data, googling around, and reading about how NYC does their inspection process. There are basically two types of inspections... ones that center around food handling and ones that don't. The ones that have to do with food handling contribute to the grade posted in the window. \n",
    "\n",
    "* From NYC: \n",
    "\n",
    "The New York City Health Department inspects all food service establishments to make sure they meet Health Code requirements, which helps prevent foodborne illness. How often a restaurant is inspected depends on its inspection score. Restaurants that receive a low score on the initial or first inspection in the inspection cycle are inspected less often than those that receive a high score.\n",
    "\n",
    "https://www1.nyc.gov/assets/doh/downloads/pdf/rii/inspection-cycle-overview.pdf\n",
    "\n",
    "\n",
    "* From the blue book \n",
    "\n",
    "Only certain inspections result in a grade . Every food service establishment is scheduled for at least one inspection per year . A restaurant that scores 0 to 13 violation points on its first inspection will receive an A-grade card that must be posted immediately . An establishment that does not score an A on its initial inspection will not have to post a grade until it has had the opportunity to improve its sanitary conditions and is re-inspected . If an A is issued on re-inspection, the A-grade card must be posted immediately . An establishment receiving a B or C grade on re-inspection receives two cards: one showing the letter grade and one that says “Grade Pending”; one of those cards must be posted immediately . The final grade is determined at OATH .The frequency of inspections depends on a restaurant’s score .Restaurants with A grades are inspected less often than those with B or C grades . Frequent inspections of poorer-performing establishments enable the Health Department to closely monitor their food safety practices, while giving them more opportunities to improve their grades .The letter grade or “Grade Pending” card must be posted in a place where it is easily seen by people passing by . It must be on the front window, door or an outside-facing wall . The card must be within 5 feet from the front door or other entrance, and within 6 feet from the ground or floor \n",
    "\n",
    "https://www1.nyc.gov/assets/doh/downloads/pdf/rii/blue-book.pdf\n",
    "\n",
    "\n",
    "### Things we need to change from the intial try:\n",
    "\n",
    "* Only focus on cycle inspections\n",
    "* Only focus on violations that are listed in the blue book\n",
    "* ???\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load all the data\n",
    "inspecs = pd.read_csv('./data/inspections.csv', parse_dates=[DATE])\n",
    "threeoneone = pd.read_csv('./data/311.csv')\n",
    "weather = pd.read_csv('./data/NYC_historical_weather.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_cycles = inspecs[inspecs['inspection_type'].str.contains('Cycle', regex=False, na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 334879 entries, 0 to 398252\n",
      "Data columns (total 15 columns):\n",
      "camis                    334879 non-null int64\n",
      "dba                      334879 non-null object\n",
      "boro                     334879 non-null object\n",
      "zipcode                  329883 non-null float64\n",
      "cuisine_description      334879 non-null object\n",
      "inspection_date          334879 non-null datetime64[ns]\n",
      "action                   334879 non-null object\n",
      "violation_code           333864 non-null object\n",
      "violation_description    332858 non-null object\n",
      "critical_flag            332858 non-null object\n",
      "score                    334879 non-null float64\n",
      "inspection_type          334879 non-null object\n",
      "latitude                 334608 non-null float64\n",
      "longitude                334608 non-null float64\n",
      "grade                    179091 non-null object\n",
      "dtypes: datetime64[ns](1), float64(4), int64(1), object(9)\n",
      "memory usage: 40.9+ MB\n"
     ]
    }
   ],
   "source": [
    "inspecs_cycles.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save just the cycle inspections\n",
    "inspecs_cycles.to_csv('./data/cycle_inspections.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove all the non-food violations\n",
    "\n",
    "codes from the blue book\n",
    "\n",
    "It removes ~30 violations, so we are just gonna over write it here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = inspecs_cycles.violation_code.isin(VIOLATIONS)\n",
    "inspecs_cycles = inspecs_cycles[mask]\n",
    "inspecs_cycles.to_csv('./data/cycle_inspections_onlybbcodes.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 332842 entries, 0 to 398252\n",
      "Data columns (total 15 columns):\n",
      "camis                    332842 non-null int64\n",
      "dba                      332842 non-null object\n",
      "boro                     332842 non-null object\n",
      "zipcode                  327925 non-null float64\n",
      "cuisine_description      332842 non-null object\n",
      "inspection_date          332842 non-null datetime64[ns]\n",
      "action                   332842 non-null object\n",
      "violation_code           332842 non-null object\n",
      "violation_description    332842 non-null object\n",
      "critical_flag            332842 non-null object\n",
      "score                    332842 non-null float64\n",
      "inspection_type          332842 non-null object\n",
      "latitude                 332574 non-null float64\n",
      "longitude                332574 non-null float64\n",
      "grade                    177537 non-null object\n",
      "dtypes: datetime64[ns](1), float64(4), int64(1), object(9)\n",
      "memory usage: 40.6+ MB\n"
     ]
    }
   ],
   "source": [
    "inspecs_cycles.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Group all the violations together\n",
    "\n",
    "Just using some code from notebook 03."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a1adb7e2425245d4b21b552813cb5be4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=4.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# this gets all the unique inspections\n",
    "inspecs_single_visits = inspecs_cycles.drop_duplicates(subset=['camis', 'inspection_date'])\n",
    "\n",
    "cols_to_merge = ['violation_code', 'violation_description', 'critical_flag', 'inspection_type']\n",
    "# this merges all of the individual inspections together. \n",
    "for col in tqdm(cols_to_merge, total=4):\n",
    "    df_tmp = inspecs[col].groupby([inspecs_cycles.camis, inspecs_cycles[DATE]]).apply(list).reset_index()\n",
    "    inspecs_single_visits = pd.merge(inspecs_single_visits, df_tmp,  how='left', \n",
    "                                     left_on=['camis', DATE], right_on = ['camis', 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": 9,
   "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>camis</th>\n",
       "      <th>dba</th>\n",
       "      <th>boro</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>cuisine_description</th>\n",
       "      <th>inspection_date</th>\n",
       "      <th>action</th>\n",
       "      <th>score</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>grade</th>\n",
       "      <th>violation_code</th>\n",
       "      <th>violation_description</th>\n",
       "      <th>critical_flag</th>\n",
       "      <th>inspection_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50072444</td>\n",
       "      <td>THE SPOT CAFE</td>\n",
       "      <td>Queens</td>\n",
       "      <td>11385.0</td>\n",
       "      <td>Café/Coffee/Tea</td>\n",
       "      <td>2019-07-09</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>28.0</td>\n",
       "      <td>40.707167</td>\n",
       "      <td>-73.896938</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[08A, 06A, 04L, 06B, 04H]</td>\n",
       "      <td>[Facility not vermin proof. Harborage or condi...</td>\n",
       "      <td>[N, Y, Y, Y, Y]</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41660537</td>\n",
       "      <td>AMERICAN BAR</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10075.0</td>\n",
       "      <td>American</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>12.0</td>\n",
       "      <td>40.770759</td>\n",
       "      <td>-73.953849</td>\n",
       "      <td>A</td>\n",
       "      <td>[10H, 04A]</td>\n",
       "      <td>[Proper sanitization not provided for utensil ...</td>\n",
       "      <td>[N, Y]</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50037997</td>\n",
       "      <td>JALAPENOS MEXICAN</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>11220.0</td>\n",
       "      <td>Mexican</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>24.0</td>\n",
       "      <td>40.641044</td>\n",
       "      <td>-74.014550</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[06A, 04N, 06D, 08A, 10F]</td>\n",
       "      <td>[Personal cleanliness inadequate. Outer garmen...</td>\n",
       "      <td>[Y, Y, Y, N, N]</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40365904</td>\n",
       "      <td>MEE SUM CAFE</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10013.0</td>\n",
       "      <td>Café/Coffee/Tea</td>\n",
       "      <td>2017-02-14</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>9.0</td>\n",
       "      <td>40.714861</td>\n",
       "      <td>-73.998200</td>\n",
       "      <td>A</td>\n",
       "      <td>[10F, 09A, 06D]</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[N, N, Y]</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>41346103</td>\n",
       "      <td>LA MAISON DU MACARON</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10011.0</td>\n",
       "      <td>French</td>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>13.0</td>\n",
       "      <td>40.743346</td>\n",
       "      <td>-73.993887</td>\n",
       "      <td>A</td>\n",
       "      <td>[10B, 10H, 02B]</td>\n",
       "      <td>[Plumbing not properly installed or maintained...</td>\n",
       "      <td>[N, N, Y]</td>\n",
       "      <td>[Cycle Inspection / Re-inspection, Cycle Inspe...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      camis                   dba       boro  zipcode cuisine_description  \\\n",
       "0  50072444         THE SPOT CAFE     Queens  11385.0     Café/Coffee/Tea   \n",
       "1  41660537          AMERICAN BAR  Manhattan  10075.0            American   \n",
       "2  50037997     JALAPENOS MEXICAN   Brooklyn  11220.0             Mexican   \n",
       "3  40365904          MEE SUM CAFE  Manhattan  10013.0     Café/Coffee/Tea   \n",
       "4  41346103  LA MAISON DU MACARON  Manhattan  10011.0              French   \n",
       "\n",
       "  inspection_date                                           action  score  \\\n",
       "0      2019-07-09  Violations were cited in the following area(s).   28.0   \n",
       "1      2017-09-20  Violations were cited in the following area(s).   12.0   \n",
       "2      2017-09-21  Violations were cited in the following area(s).   24.0   \n",
       "3      2017-02-14  Violations were cited in the following area(s).    9.0   \n",
       "4      2018-08-31  Violations were cited in the following area(s).   13.0   \n",
       "\n",
       "    latitude  longitude grade             violation_code  \\\n",
       "0  40.707167 -73.896938   NaN  [08A, 06A, 04L, 06B, 04H]   \n",
       "1  40.770759 -73.953849     A                 [10H, 04A]   \n",
       "2  40.641044 -74.014550   NaN  [06A, 04N, 06D, 08A, 10F]   \n",
       "3  40.714861 -73.998200     A            [10F, 09A, 06D]   \n",
       "4  40.743346 -73.993887     A            [10B, 10H, 02B]   \n",
       "\n",
       "                               violation_description    critical_flag  \\\n",
       "0  [Facility not vermin proof. Harborage or condi...  [N, Y, Y, Y, Y]   \n",
       "1  [Proper sanitization not provided for utensil ...           [N, Y]   \n",
       "2  [Personal cleanliness inadequate. Outer garmen...  [Y, Y, Y, N, N]   \n",
       "3  [Non-food contact surface improperly construct...        [N, N, Y]   \n",
       "4  [Plumbing not properly installed or maintained...        [N, N, Y]   \n",
       "\n",
       "                                     inspection_type  \n",
       "0  [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "1  [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "2  [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "3  [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "4  [Cycle Inspection / Re-inspection, Cycle Inspe...  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_single_visits.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_single_visits.grade = inspecs_single_visits.apply(update_grade, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    73954\n",
       "B    27197\n",
       "C    12897\n",
       "Name: grade, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_single_visits.grade.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    0.648446\n",
       "B    0.238470\n",
       "C    0.113084\n",
       "Name: grade, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_single_visits.grade.value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add more info\n",
    "\n",
    "More code from notebook 03"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the missing lats and lons with nan\n",
    "inspecs_single_visits['latitude'].replace(0, np.nan, inplace=True)\n",
    "inspecs_single_visits['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_single_visits.dropna(subset = ['latitude', 'longitude'], inplace=True)\n",
    "threeoneone.dropna(subset = ['latitude', 'longitude'], inplace=True)\n",
    "\n",
    "# Do date things\n",
    "inspecs_single_visits[DATE] = pd.to_datetime(inspecs_single_visits[DATE])\n",
    "threeoneone.created_date = pd.to_datetime(threeoneone.created_date)\n",
    "weather.DATE = pd.to_datetime(weather.DATE)\n",
    "\n",
    "# Does the inspection have a critical violation?\n",
    "nested = inspecs_single_visits['critical_flag'].values.tolist()\n",
    "inspecs_single_visits['critical'] = [1 if 'Y' in sublist else 0 for sublist in nested]\n",
    "\n",
    "# how many criticals?\n",
    "inspecs_single_visits['num_critical'] = [sublist.count('Y') 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_single_visits.inspection_date.dt.weekday\n",
    "# threeoneone['weekday'] = threeoneone.created_date.dt.weekday\n",
    "# decided I want the day name\n",
    "inspecs_single_visits['weekday'] = inspecs_single_visits.inspection_date.dt.day_name()\n",
    "\n",
    "# merge the weather data into the frame\n",
    "inspecs_single_visits = inspecs_single_visits.merge(weather[['DATE', 'TMAX']], left_on=DATE, right_on='DATE')\n",
    "# drop the extra date column\n",
    "inspecs_single_visits.drop(labels='DATE', axis=1, inplace=True)\n",
    "# rename to be lower case\n",
    "inspecs_single_visits.rename(columns={'TMAX': 'tmax'}, inplace=True)\n",
    "\n",
    "# another suggestion was to add the three-day average temp before the inspection\n",
    "threeday = weather[['DATE', 'TMAX']].rolling('3D', min_periods=3, on='DATE', closed='left').mean()\n",
    "inspecs_single_visits = inspecs_single_visits.merge(threeday[['DATE', 'TMAX']], left_on=DATE, right_on='DATE')\n",
    "# drop the extra date column\n",
    "inspecs_single_visits.drop(labels='DATE', axis=1, inplace=True)\n",
    "# rename to be lower case\n",
    "inspecs_single_visits.rename(columns={'TMAX': 'tmax_3d'}, 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_single_visits.sort_values(['camis', 'inspection_date'], inplace=True)\n",
    "\n",
    "# time since last inspection\n",
    "inspecs_single_visits['time_since_last'] = inspecs_single_visits.groupby('camis')['inspection_date'].diff().apply(lambda x: x.days)\n",
    "# past critical violation?\n",
    "inspecs_single_visits['past_critical'] = inspecs_single_visits.groupby(['camis'])['critical'].shift()\n",
    "# past grade and score\n",
    "inspecs_single_visits['past_grade'] = inspecs_single_visits.groupby(['camis'])['grade'].shift()\n",
    "inspecs_single_visits['past_score'] = inspecs_single_visits.groupby(['camis'])['score'].shift()\n",
    "\n",
    "# clean up a few things with the temporal stuff\n",
    "inspecs_single_visits.time_since_last.replace(np.nan, 0, inplace=True)\n",
    "inspecs_single_visits.past_critical.replace(np.nan, 0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>13387</th>\n",
       "      <th>66368</th>\n",
       "      <th>89621</th>\n",
       "      <th>12847</th>\n",
       "      <th>63036</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>camis</th>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dba</th>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>boro</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zipcode</th>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cuisine_description</th>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inspection_date</th>\n",
       "      <td>2017-05-18 00:00:00</td>\n",
       "      <td>2018-05-11 00:00:00</td>\n",
       "      <td>2019-05-16 00:00:00</td>\n",
       "      <td>2019-06-11 00:00:00</td>\n",
       "      <td>2019-10-21 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>action</th>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>score</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grade</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violation_code</th>\n",
       "      <td>[06D, 10F]</td>\n",
       "      <td>[10F, 08C]</td>\n",
       "      <td>[10F, 04L, 08A]</td>\n",
       "      <td>[08C, 10F, 10B]</td>\n",
       "      <td>[08A, 04L, 10B, 06D]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violation_description</th>\n",
       "      <td>[Food contact surface not properly washed, rin...</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[Pesticide use not in accordance with label or...</td>\n",
       "      <td>[Facility not vermin proof. Harborage or condi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>critical_flag</th>\n",
       "      <td>[Y, N]</td>\n",
       "      <td>[N, N]</td>\n",
       "      <td>[N, Y, N]</td>\n",
       "      <td>[N, N, N]</td>\n",
       "      <td>[N, Y, N, Y]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inspection_type</th>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Re-inspection, Cycle Inspe...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>critical</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_critical</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>weekday</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tmax</th>\n",
       "      <td>92</td>\n",
       "      <td>73</td>\n",
       "      <td>73</td>\n",
       "      <td>77</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tmax_3d</th>\n",
       "      <td>79</td>\n",
       "      <td>72.3333</td>\n",
       "      <td>56.3333</td>\n",
       "      <td>76.3333</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time_since_last</th>\n",
       "      <td>0</td>\n",
       "      <td>358</td>\n",
       "      <td>370</td>\n",
       "      <td>26</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_critical</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_grade</th>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_score</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                   13387  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2017-05-18 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  7   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                                [06D, 10F]   \n",
       "violation_description  [Food contact surface not properly washed, rin...   \n",
       "critical_flag                                                     [Y, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               1   \n",
       "num_critical                                                           1   \n",
       "weekday                                                         Thursday   \n",
       "tmax                                                                  92   \n",
       "tmax_3d                                                               79   \n",
       "time_since_last                                                        0   \n",
       "past_critical                                                          0   \n",
       "past_grade                                                           NaN   \n",
       "past_score                                                           NaN   \n",
       "\n",
       "                                                                   66368  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2018-05-11 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  5   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                                [10F, 08C]   \n",
       "violation_description  [Non-food contact surface improperly construct...   \n",
       "critical_flag                                                     [N, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               0   \n",
       "num_critical                                                           0   \n",
       "weekday                                                           Friday   \n",
       "tmax                                                                  73   \n",
       "tmax_3d                                                          72.3333   \n",
       "time_since_last                                                      358   \n",
       "past_critical                                                          1   \n",
       "past_grade                                                             A   \n",
       "past_score                                                             7   \n",
       "\n",
       "                                                                   89621  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2019-05-16 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                 14   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  B   \n",
       "violation_code                                           [10F, 04L, 08A]   \n",
       "violation_description  [Non-food contact surface improperly construct...   \n",
       "critical_flag                                                  [N, Y, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               1   \n",
       "num_critical                                                           1   \n",
       "weekday                                                         Thursday   \n",
       "tmax                                                                  73   \n",
       "tmax_3d                                                          56.3333   \n",
       "time_since_last                                                      370   \n",
       "past_critical                                                          0   \n",
       "past_grade                                                             A   \n",
       "past_score                                                             5   \n",
       "\n",
       "                                                                   12847  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2019-06-11 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  6   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                           [08C, 10F, 10B]   \n",
       "violation_description  [Pesticide use not in accordance with label or...   \n",
       "critical_flag                                                  [N, N, N]   \n",
       "inspection_type        [Cycle Inspection / Re-inspection, Cycle Inspe...   \n",
       "critical                                                               0   \n",
       "num_critical                                                           0   \n",
       "weekday                                                          Tuesday   \n",
       "tmax                                                                  77   \n",
       "tmax_3d                                                          76.3333   \n",
       "time_since_last                                                       26   \n",
       "past_critical                                                          1   \n",
       "past_grade                                                             B   \n",
       "past_score                                                            14   \n",
       "\n",
       "                                                                   63036  \n",
       "camis                                                           30075445  \n",
       "dba                                                MORRIS PARK BAKE SHOP  \n",
       "boro                                                               Bronx  \n",
       "zipcode                                                            10462  \n",
       "cuisine_description                                               Bakery  \n",
       "inspection_date                                      2019-10-21 00:00:00  \n",
       "action                   Violations were cited in the following area(s).  \n",
       "score                                                                 17  \n",
       "latitude                                                         40.8482  \n",
       "longitude                                                        -73.856  \n",
       "grade                                                                  B  \n",
       "violation_code                                      [08A, 04L, 10B, 06D]  \n",
       "violation_description  [Facility not vermin proof. Harborage or condi...  \n",
       "critical_flag                                               [N, Y, N, Y]  \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "critical                                                               1  \n",
       "num_critical                                                           2  \n",
       "weekday                                                           Monday  \n",
       "tmax                                                                  67  \n",
       "tmax_3d                                                               59  \n",
       "time_since_last                                                      132  \n",
       "past_critical                                                          0  \n",
       "past_grade                                                             A  \n",
       "past_score                                                             6  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_single_visits.head(5).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 111854 entries, 13387 to 14288\n",
      "Data columns (total 24 columns):\n",
      "camis                    111854 non-null int64\n",
      "dba                      111854 non-null object\n",
      "boro                     111854 non-null object\n",
      "zipcode                  111854 non-null float64\n",
      "cuisine_description      111854 non-null object\n",
      "inspection_date          111854 non-null datetime64[ns]\n",
      "action                   111854 non-null object\n",
      "score                    111854 non-null float64\n",
      "latitude                 111854 non-null float64\n",
      "longitude                111854 non-null float64\n",
      "grade                    111854 non-null object\n",
      "violation_code           111854 non-null object\n",
      "violation_description    111854 non-null object\n",
      "critical_flag            111854 non-null object\n",
      "inspection_type          111854 non-null object\n",
      "critical                 111854 non-null int64\n",
      "num_critical             111854 non-null int64\n",
      "weekday                  111854 non-null object\n",
      "tmax                     111854 non-null int64\n",
      "tmax_3d                  111854 non-null float64\n",
      "time_since_last          111854 non-null float64\n",
      "past_critical            111854 non-null float64\n",
      "past_grade               89594 non-null object\n",
      "past_score               89594 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(8), int64(4), object(11)\n",
      "memory usage: 26.3+ MB\n"
     ]
    }
   ],
   "source": [
    "inspecs_single_visits.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_single_visits.to_csv('./data/inspecs_single_visits.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add the Heatmap Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "22686b987631456bba6f956ca23fb871",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=1239.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "57e76f39e9e54f419782d682bbe57f95",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=1239.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d3cb444e9a59446d9b3cea9c07473222",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=1239.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "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\n",
    "\n",
    "target_frame = pd.DataFrame(index=inspecs_single_visits.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\n",
    "\n",
    "heatmap_frame = threeoneone[threeoneone.complaint_type.isin(DSNY_311)]\n",
    "target_frame = score_heatmap(inspecs_single_visits, 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_single_visits, 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_single_visits, target_frame, heatmap_frame, 'inspection_date', 'dohmh_heat_score')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>dsny_heat_score</th>\n",
       "      <th>dep_heat_score</th>\n",
       "      <th>dohmh_heat_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>111854.000000</td>\n",
       "      <td>111854.000000</td>\n",
       "      <td>111854.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.221378</td>\n",
       "      <td>0.170150</td>\n",
       "      <td>0.152492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.220264</td>\n",
       "      <td>0.195124</td>\n",
       "      <td>0.167318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.067819</td>\n",
       "      <td>0.052735</td>\n",
       "      <td>0.039844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.164837</td>\n",
       "      <td>0.118542</td>\n",
       "      <td>0.110121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.306675</td>\n",
       "      <td>0.224206</td>\n",
       "      <td>0.218130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.574699</td>\n",
       "      <td>4.522346</td>\n",
       "      <td>10.359245</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dsny_heat_score  dep_heat_score  dohmh_heat_score\n",
       "count    111854.000000   111854.000000     111854.000000\n",
       "mean          0.221378        0.170150          0.152492\n",
       "std           0.220264        0.195124          0.167318\n",
       "min           0.000000        0.000000          0.000000\n",
       "25%           0.067819        0.052735          0.039844\n",
       "50%           0.164837        0.118542          0.110121\n",
       "75%           0.306675        0.224206          0.218130\n",
       "max           4.574699        4.522346         10.359245"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_frame.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores = inspecs_single_visits.merge(target_frame, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>13387</th>\n",
       "      <th>66368</th>\n",
       "      <th>89621</th>\n",
       "      <th>12847</th>\n",
       "      <th>63036</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>camis</th>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dba</th>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>boro</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zipcode</th>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cuisine_description</th>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inspection_date</th>\n",
       "      <td>2017-05-18 00:00:00</td>\n",
       "      <td>2018-05-11 00:00:00</td>\n",
       "      <td>2019-05-16 00:00:00</td>\n",
       "      <td>2019-06-11 00:00:00</td>\n",
       "      <td>2019-10-21 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>action</th>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>score</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grade</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violation_code</th>\n",
       "      <td>[06D, 10F]</td>\n",
       "      <td>[10F, 08C]</td>\n",
       "      <td>[10F, 04L, 08A]</td>\n",
       "      <td>[08C, 10F, 10B]</td>\n",
       "      <td>[08A, 04L, 10B, 06D]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violation_description</th>\n",
       "      <td>[Food contact surface not properly washed, rin...</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[Pesticide use not in accordance with label or...</td>\n",
       "      <td>[Facility not vermin proof. Harborage or condi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>critical_flag</th>\n",
       "      <td>[Y, N]</td>\n",
       "      <td>[N, N]</td>\n",
       "      <td>[N, Y, N]</td>\n",
       "      <td>[N, N, N]</td>\n",
       "      <td>[N, Y, N, Y]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inspection_type</th>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Re-inspection, Cycle Inspe...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>critical</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_critical</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>weekday</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tmax</th>\n",
       "      <td>92</td>\n",
       "      <td>73</td>\n",
       "      <td>73</td>\n",
       "      <td>77</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tmax_3d</th>\n",
       "      <td>79</td>\n",
       "      <td>72.3333</td>\n",
       "      <td>56.3333</td>\n",
       "      <td>76.3333</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time_since_last</th>\n",
       "      <td>0</td>\n",
       "      <td>358</td>\n",
       "      <td>370</td>\n",
       "      <td>26</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_critical</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_grade</th>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_score</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dsny_heat_score</th>\n",
       "      <td>0.406452</td>\n",
       "      <td>0.203028</td>\n",
       "      <td>0.160208</td>\n",
       "      <td>0.256666</td>\n",
       "      <td>0.309046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dep_heat_score</th>\n",
       "      <td>0.117716</td>\n",
       "      <td>0.074187</td>\n",
       "      <td>0.0273974</td>\n",
       "      <td>0.399057</td>\n",
       "      <td>0.0314142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dohmh_heat_score</th>\n",
       "      <td>0.0989595</td>\n",
       "      <td>0.169137</td>\n",
       "      <td>0.173178</td>\n",
       "      <td>0.316888</td>\n",
       "      <td>0.274051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                   13387  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2017-05-18 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  7   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                                [06D, 10F]   \n",
       "violation_description  [Food contact surface not properly washed, rin...   \n",
       "critical_flag                                                     [Y, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               1   \n",
       "num_critical                                                           1   \n",
       "weekday                                                         Thursday   \n",
       "tmax                                                                  92   \n",
       "tmax_3d                                                               79   \n",
       "time_since_last                                                        0   \n",
       "past_critical                                                          0   \n",
       "past_grade                                                           NaN   \n",
       "past_score                                                           NaN   \n",
       "dsny_heat_score                                                 0.406452   \n",
       "dep_heat_score                                                  0.117716   \n",
       "dohmh_heat_score                                               0.0989595   \n",
       "\n",
       "                                                                   66368  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2018-05-11 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  5   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                                [10F, 08C]   \n",
       "violation_description  [Non-food contact surface improperly construct...   \n",
       "critical_flag                                                     [N, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               0   \n",
       "num_critical                                                           0   \n",
       "weekday                                                           Friday   \n",
       "tmax                                                                  73   \n",
       "tmax_3d                                                          72.3333   \n",
       "time_since_last                                                      358   \n",
       "past_critical                                                          1   \n",
       "past_grade                                                             A   \n",
       "past_score                                                             7   \n",
       "dsny_heat_score                                                 0.203028   \n",
       "dep_heat_score                                                  0.074187   \n",
       "dohmh_heat_score                                                0.169137   \n",
       "\n",
       "                                                                   89621  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2019-05-16 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                 14   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  B   \n",
       "violation_code                                           [10F, 04L, 08A]   \n",
       "violation_description  [Non-food contact surface improperly construct...   \n",
       "critical_flag                                                  [N, Y, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               1   \n",
       "num_critical                                                           1   \n",
       "weekday                                                         Thursday   \n",
       "tmax                                                                  73   \n",
       "tmax_3d                                                          56.3333   \n",
       "time_since_last                                                      370   \n",
       "past_critical                                                          0   \n",
       "past_grade                                                             A   \n",
       "past_score                                                             5   \n",
       "dsny_heat_score                                                 0.160208   \n",
       "dep_heat_score                                                 0.0273974   \n",
       "dohmh_heat_score                                                0.173178   \n",
       "\n",
       "                                                                   12847  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2019-06-11 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  6   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                           [08C, 10F, 10B]   \n",
       "violation_description  [Pesticide use not in accordance with label or...   \n",
       "critical_flag                                                  [N, N, N]   \n",
       "inspection_type        [Cycle Inspection / Re-inspection, Cycle Inspe...   \n",
       "critical                                                               0   \n",
       "num_critical                                                           0   \n",
       "weekday                                                          Tuesday   \n",
       "tmax                                                                  77   \n",
       "tmax_3d                                                          76.3333   \n",
       "time_since_last                                                       26   \n",
       "past_critical                                                          1   \n",
       "past_grade                                                             B   \n",
       "past_score                                                            14   \n",
       "dsny_heat_score                                                 0.256666   \n",
       "dep_heat_score                                                  0.399057   \n",
       "dohmh_heat_score                                                0.316888   \n",
       "\n",
       "                                                                   63036  \n",
       "camis                                                           30075445  \n",
       "dba                                                MORRIS PARK BAKE SHOP  \n",
       "boro                                                               Bronx  \n",
       "zipcode                                                            10462  \n",
       "cuisine_description                                               Bakery  \n",
       "inspection_date                                      2019-10-21 00:00:00  \n",
       "action                   Violations were cited in the following area(s).  \n",
       "score                                                                 17  \n",
       "latitude                                                         40.8482  \n",
       "longitude                                                        -73.856  \n",
       "grade                                                                  B  \n",
       "violation_code                                      [08A, 04L, 10B, 06D]  \n",
       "violation_description  [Facility not vermin proof. Harborage or condi...  \n",
       "critical_flag                                               [N, Y, N, Y]  \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "critical                                                               1  \n",
       "num_critical                                                           2  \n",
       "weekday                                                           Monday  \n",
       "tmax                                                                  67  \n",
       "tmax_3d                                                               59  \n",
       "time_since_last                                                      132  \n",
       "past_critical                                                          0  \n",
       "past_grade                                                             A  \n",
       "past_score                                                             6  \n",
       "dsny_heat_score                                                 0.309046  \n",
       "dep_heat_score                                                 0.0314142  \n",
       "dohmh_heat_score                                                0.274051  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_scores.head(5).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Decide if the restaurants are chains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "groups = inspecs_scores.groupby(['dba'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores['is_chain'] = 0\n",
    "for dba, group in groups:\n",
    "    if group.camis.unique().shape[0] > 1:\n",
    "        locs = [inspecs_scores.index.get_loc(idx) for idx in group.index]\n",
    "        inspecs_scores['is_chain'].iloc[locs] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>13387</th>\n",
       "      <th>66368</th>\n",
       "      <th>89621</th>\n",
       "      <th>12847</th>\n",
       "      <th>63036</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>camis</th>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "      <td>30075445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dba</th>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "      <td>MORRIS PARK BAKE SHOP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>boro</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Bronx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zipcode</th>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "      <td>10462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cuisine_description</th>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inspection_date</th>\n",
       "      <td>2017-05-18 00:00:00</td>\n",
       "      <td>2018-05-11 00:00:00</td>\n",
       "      <td>2019-05-16 00:00:00</td>\n",
       "      <td>2019-06-11 00:00:00</td>\n",
       "      <td>2019-10-21 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>action</th>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>score</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "      <td>40.8482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "      <td>-73.856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grade</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violation_code</th>\n",
       "      <td>[06D, 10F]</td>\n",
       "      <td>[10F, 08C]</td>\n",
       "      <td>[10F, 04L, 08A]</td>\n",
       "      <td>[08C, 10F, 10B]</td>\n",
       "      <td>[08A, 04L, 10B, 06D]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violation_description</th>\n",
       "      <td>[Food contact surface not properly washed, rin...</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[Non-food contact surface improperly construct...</td>\n",
       "      <td>[Pesticide use not in accordance with label or...</td>\n",
       "      <td>[Facility not vermin proof. Harborage or condi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>critical_flag</th>\n",
       "      <td>[Y, N]</td>\n",
       "      <td>[N, N]</td>\n",
       "      <td>[N, Y, N]</td>\n",
       "      <td>[N, N, N]</td>\n",
       "      <td>[N, Y, N, Y]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inspection_type</th>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "      <td>[Cycle Inspection / Re-inspection, Cycle Inspe...</td>\n",
       "      <td>[Cycle Inspection / Initial Inspection, Cycle ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>critical</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>num_critical</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>weekday</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tmax</th>\n",
       "      <td>92</td>\n",
       "      <td>73</td>\n",
       "      <td>73</td>\n",
       "      <td>77</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tmax_3d</th>\n",
       "      <td>79</td>\n",
       "      <td>72.3333</td>\n",
       "      <td>56.3333</td>\n",
       "      <td>76.3333</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time_since_last</th>\n",
       "      <td>0</td>\n",
       "      <td>358</td>\n",
       "      <td>370</td>\n",
       "      <td>26</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_critical</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_grade</th>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>past_score</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dsny_heat_score</th>\n",
       "      <td>0.406452</td>\n",
       "      <td>0.203028</td>\n",
       "      <td>0.160208</td>\n",
       "      <td>0.256666</td>\n",
       "      <td>0.309046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dep_heat_score</th>\n",
       "      <td>0.117716</td>\n",
       "      <td>0.074187</td>\n",
       "      <td>0.0273974</td>\n",
       "      <td>0.399057</td>\n",
       "      <td>0.0314142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dohmh_heat_score</th>\n",
       "      <td>0.0989595</td>\n",
       "      <td>0.169137</td>\n",
       "      <td>0.173178</td>\n",
       "      <td>0.316888</td>\n",
       "      <td>0.274051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_chain</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                   13387  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2017-05-18 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  7   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                                [06D, 10F]   \n",
       "violation_description  [Food contact surface not properly washed, rin...   \n",
       "critical_flag                                                     [Y, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               1   \n",
       "num_critical                                                           1   \n",
       "weekday                                                         Thursday   \n",
       "tmax                                                                  92   \n",
       "tmax_3d                                                               79   \n",
       "time_since_last                                                        0   \n",
       "past_critical                                                          0   \n",
       "past_grade                                                           NaN   \n",
       "past_score                                                           NaN   \n",
       "dsny_heat_score                                                 0.406452   \n",
       "dep_heat_score                                                  0.117716   \n",
       "dohmh_heat_score                                               0.0989595   \n",
       "is_chain                                                               0   \n",
       "\n",
       "                                                                   66368  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2018-05-11 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  5   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                                [10F, 08C]   \n",
       "violation_description  [Non-food contact surface improperly construct...   \n",
       "critical_flag                                                     [N, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               0   \n",
       "num_critical                                                           0   \n",
       "weekday                                                           Friday   \n",
       "tmax                                                                  73   \n",
       "tmax_3d                                                          72.3333   \n",
       "time_since_last                                                      358   \n",
       "past_critical                                                          1   \n",
       "past_grade                                                             A   \n",
       "past_score                                                             7   \n",
       "dsny_heat_score                                                 0.203028   \n",
       "dep_heat_score                                                  0.074187   \n",
       "dohmh_heat_score                                                0.169137   \n",
       "is_chain                                                               0   \n",
       "\n",
       "                                                                   89621  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2019-05-16 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                 14   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  B   \n",
       "violation_code                                           [10F, 04L, 08A]   \n",
       "violation_description  [Non-food contact surface improperly construct...   \n",
       "critical_flag                                                  [N, Y, N]   \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...   \n",
       "critical                                                               1   \n",
       "num_critical                                                           1   \n",
       "weekday                                                         Thursday   \n",
       "tmax                                                                  73   \n",
       "tmax_3d                                                          56.3333   \n",
       "time_since_last                                                      370   \n",
       "past_critical                                                          0   \n",
       "past_grade                                                             A   \n",
       "past_score                                                             5   \n",
       "dsny_heat_score                                                 0.160208   \n",
       "dep_heat_score                                                 0.0273974   \n",
       "dohmh_heat_score                                                0.173178   \n",
       "is_chain                                                               0   \n",
       "\n",
       "                                                                   12847  \\\n",
       "camis                                                           30075445   \n",
       "dba                                                MORRIS PARK BAKE SHOP   \n",
       "boro                                                               Bronx   \n",
       "zipcode                                                            10462   \n",
       "cuisine_description                                               Bakery   \n",
       "inspection_date                                      2019-06-11 00:00:00   \n",
       "action                   Violations were cited in the following area(s).   \n",
       "score                                                                  6   \n",
       "latitude                                                         40.8482   \n",
       "longitude                                                        -73.856   \n",
       "grade                                                                  A   \n",
       "violation_code                                           [08C, 10F, 10B]   \n",
       "violation_description  [Pesticide use not in accordance with label or...   \n",
       "critical_flag                                                  [N, N, N]   \n",
       "inspection_type        [Cycle Inspection / Re-inspection, Cycle Inspe...   \n",
       "critical                                                               0   \n",
       "num_critical                                                           0   \n",
       "weekday                                                          Tuesday   \n",
       "tmax                                                                  77   \n",
       "tmax_3d                                                          76.3333   \n",
       "time_since_last                                                       26   \n",
       "past_critical                                                          1   \n",
       "past_grade                                                             B   \n",
       "past_score                                                            14   \n",
       "dsny_heat_score                                                 0.256666   \n",
       "dep_heat_score                                                  0.399057   \n",
       "dohmh_heat_score                                                0.316888   \n",
       "is_chain                                                               0   \n",
       "\n",
       "                                                                   63036  \n",
       "camis                                                           30075445  \n",
       "dba                                                MORRIS PARK BAKE SHOP  \n",
       "boro                                                               Bronx  \n",
       "zipcode                                                            10462  \n",
       "cuisine_description                                               Bakery  \n",
       "inspection_date                                      2019-10-21 00:00:00  \n",
       "action                   Violations were cited in the following area(s).  \n",
       "score                                                                 17  \n",
       "latitude                                                         40.8482  \n",
       "longitude                                                        -73.856  \n",
       "grade                                                                  B  \n",
       "violation_code                                      [08A, 04L, 10B, 06D]  \n",
       "violation_description  [Facility not vermin proof. Harborage or condi...  \n",
       "critical_flag                                               [N, Y, N, Y]  \n",
       "inspection_type        [Cycle Inspection / Initial Inspection, Cycle ...  \n",
       "critical                                                               1  \n",
       "num_critical                                                           2  \n",
       "weekday                                                           Monday  \n",
       "tmax                                                                  67  \n",
       "tmax_3d                                                               59  \n",
       "time_since_last                                                      132  \n",
       "past_critical                                                          0  \n",
       "past_grade                                                             A  \n",
       "past_score                                                             6  \n",
       "dsny_heat_score                                                 0.309046  \n",
       "dep_heat_score                                                 0.0314142  \n",
       "dohmh_heat_score                                                0.274051  \n",
       "is_chain                                                               0  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_scores.head(5).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 111854 entries, 13387 to 14288\n",
      "Data columns (total 28 columns):\n",
      "camis                    111854 non-null int64\n",
      "dba                      111854 non-null object\n",
      "boro                     111854 non-null object\n",
      "zipcode                  111854 non-null float64\n",
      "cuisine_description      111854 non-null object\n",
      "inspection_date          111854 non-null datetime64[ns]\n",
      "action                   111854 non-null object\n",
      "score                    111854 non-null float64\n",
      "latitude                 111854 non-null float64\n",
      "longitude                111854 non-null float64\n",
      "grade                    111854 non-null object\n",
      "violation_code           111854 non-null object\n",
      "violation_description    111854 non-null object\n",
      "critical_flag            111854 non-null object\n",
      "inspection_type          111854 non-null object\n",
      "critical                 111854 non-null int64\n",
      "num_critical             111854 non-null int64\n",
      "weekday                  111854 non-null object\n",
      "tmax                     111854 non-null int64\n",
      "tmax_3d                  111854 non-null float64\n",
      "time_since_last          111854 non-null float64\n",
      "past_critical            111854 non-null float64\n",
      "past_grade               89594 non-null object\n",
      "past_score               89594 non-null float64\n",
      "dsny_heat_score          111854 non-null float64\n",
      "dep_heat_score           111854 non-null float64\n",
      "dohmh_heat_score         111854 non-null float64\n",
      "is_chain                 111854 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(11), int64(5), object(11)\n",
      "memory usage: 29.7+ MB\n"
     ]
    }
   ],
   "source": [
    "inspecs_scores.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deal with missing values\n",
    "\n",
    "Two columns have missing values, both of which have to do with the past scoring. \n",
    "\n",
    "There are a couple of things we can try. \n",
    "\n",
    "1. Fill in all the \"past\" initial scores with the median of all initial scores\n",
    "2. Try to fetch each store's pre-opening inspection score. (hard?)\n",
    "3. Create a flag that says whether or not it is an initial cycle inspection\n",
    "\n",
    "Some stores don't have a pre-opening inspection (eg., camis = 30075445). So we are gonna try a combination of 1 and 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first let's add a column saying if it's in the initial inspection\n",
    "inspecs_scores['init_inspec'] = 0\n",
    "inspecs_scores.loc[inspecs_scores.past_score.isnull(), 'init_inspec'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# median score of all the initial inspections\n",
    "score_init = inspecs_scores[inspecs_scores.past_score.isnull()].score.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# That's an A grade\n",
    "grade_init = 'A'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores.past_score.fillna(score_init, inplace=True)\n",
    "inspecs_scores.past_grade.fillna(grade_init, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 111854 entries, 13387 to 14288\n",
      "Data columns (total 29 columns):\n",
      "camis                    111854 non-null int64\n",
      "dba                      111854 non-null object\n",
      "boro                     111854 non-null object\n",
      "zipcode                  111854 non-null float64\n",
      "cuisine_description      111854 non-null object\n",
      "inspection_date          111854 non-null datetime64[ns]\n",
      "action                   111854 non-null object\n",
      "score                    111854 non-null float64\n",
      "latitude                 111854 non-null float64\n",
      "longitude                111854 non-null float64\n",
      "grade                    111854 non-null object\n",
      "violation_code           111854 non-null object\n",
      "violation_description    111854 non-null object\n",
      "critical_flag            111854 non-null object\n",
      "inspection_type          111854 non-null object\n",
      "critical                 111854 non-null int64\n",
      "num_critical             111854 non-null int64\n",
      "weekday                  111854 non-null object\n",
      "tmax                     111854 non-null int64\n",
      "tmax_3d                  111854 non-null float64\n",
      "time_since_last          111854 non-null float64\n",
      "past_critical            111854 non-null float64\n",
      "past_grade               111854 non-null object\n",
      "past_score               111854 non-null float64\n",
      "dsny_heat_score          111854 non-null float64\n",
      "dep_heat_score           111854 non-null float64\n",
      "dohmh_heat_score         111854 non-null float64\n",
      "is_chain                 111854 non-null int64\n",
      "init_inspec              111854 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(11), int64(6), object(11)\n",
      "memory usage: 30.6+ MB\n"
     ]
    }
   ],
   "source": [
    "inspecs_scores.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['camis',\n",
       " 'dba',\n",
       " 'boro',\n",
       " 'zipcode',\n",
       " 'cuisine_description',\n",
       " 'inspection_date',\n",
       " 'action',\n",
       " 'score',\n",
       " 'latitude',\n",
       " 'longitude',\n",
       " 'grade',\n",
       " 'violation_code',\n",
       " 'violation_description',\n",
       " 'critical_flag',\n",
       " 'inspection_type',\n",
       " 'critical',\n",
       " 'num_critical',\n",
       " 'weekday',\n",
       " 'tmax',\n",
       " 'tmax_3d',\n",
       " 'time_since_last',\n",
       " 'past_critical',\n",
       " 'past_grade',\n",
       " 'past_score',\n",
       " 'dsny_heat_score',\n",
       " 'dep_heat_score',\n",
       " 'dohmh_heat_score',\n",
       " 'is_chain',\n",
       " 'init_inspec']"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspecs_scores.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspecs_scores.to_csv('./data/inspec_scores.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# this is how we are gonna dummy encode all the violation codes\n",
    "# s = inspecs_single_visits.violation_code\n",
    "# pd.get_dummies(s.apply(pd.Series).stack()).sum(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 4
}