SafeBuild-Accident-Predictor.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "READ PERMIT DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-06-25 09:56:07.835000\n",
      "2018-06-25 09:56:32.126000\n",
      "Reading BIS job filing data ...\n",
      "length before drop 96782\n",
      "length after drop 96782\n",
      "number dropped 0\n",
      "Reading DOB Now job filing data ...\n",
      "len of dob now data 4324\n",
      "Reading CO/TCO data for dictionary ...\n",
      "lengh of combined 101106\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "print str(datetime.now())\n",
    "\n",
    "import pandas as pd\n",
    "#import logging\n",
    "pd.set_option('chained_assignment', None)\n",
    "\n",
    "from datetime import datetime\n",
    "print str(datetime.now())\n",
    "\n",
    "#logging.basicConfig(filename=\"I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\automation_log.log\",level=logging.DEBUG)\n",
    "#logging.debug('This message should go to the log file')\n",
    "#logging.warning('And this, too')\n",
    "\n",
    "#text_file = open(\"I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\automation_log.txt\", \"a\" )\n",
    "\n",
    "\n",
    "#string = str(datetime.now()) + \"...\" + \"Reading active permit data\"  + \"\\n\"\n",
    "#print string\n",
    "#text_file.write(\" \\n\")\n",
    "#text_file.write(\"******\\n\")\n",
    "#text_file.write(string)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def PermitDataInput():\n",
    "    #import datetime\n",
    "    #import time\n",
    "    #from datetime import date\n",
    "    #from dateutil.relativedelta import relativedelta\n",
    "    #from dateutil import parser\n",
    "\n",
    "    #permits issued\n",
    "    df = pd.read_csv('I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\active permitsAuto.csv', low_memory=False)\n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "    #drop columns\n",
    "    df = df.drop('Borough Name', 1)\n",
    "    df = df.drop('Current Job Status', 1)\n",
    "    df = df.drop('Permit Filing Status (Raw)', 1)\n",
    "    df = df.drop('Permit Sequence Number Max Flag', 1)\n",
    "    df = df.drop('issue_DATE', 1)\n",
    "    df = df.drop('issue_YEAR', 1)\n",
    "    df = df.drop('Permit Filing Status', 1)\n",
    "    df = df.drop('Job-Permit', 1)\n",
    "    \n",
    "    #df = df.drop('Applicant Business Name', 1)\n",
    "    \n",
    "    dropList =   [\"Applicant First Name\", \"Applicant Last Name\", \"Applicant License Number\", \n",
    "                  \"Applicant License Type\", \"Applicant Middle Initial\"]   \n",
    "\n",
    "    df = df.drop(dropList, 1)\n",
    "    \n",
    "    \n",
    "  \n",
    "\n",
    "\n",
    "    print \"length before drop\", len(df)\n",
    "\n",
    "    before = len(df)\n",
    "\n",
    "    df = df.dropna(subset = ['expire_DATE'])\n",
    "\n",
    "    print \"length after drop\", len(df)\n",
    "\n",
    "    print \"number dropped\", before - len(df)\n",
    "\n",
    "    df = df.reset_index(drop=True)\n",
    "    \n",
    "    #ts = time.time()\n",
    "    #st = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')\n",
    "    #print \"Current Date\", st\n",
    "  \n",
    "    #df['expire_DATE'] = pd.to_datetime(df['expire_DATE'])\n",
    "    #df = df.sort_values(by = 'expire_DATE', ascending=True).reset_index(drop=True)\n",
    "    \n",
    "    #df = df[(df['expire_DATE'] >= st) ]\n",
    "    \n",
    "    df = df.rename(columns={'Latitude Point': 'Latitude', 'Longitude Point': 'Longitude', 'Borough Name.1': 'Borough',\n",
    "                           'Applicant Business Name':'General Contractor'})\n",
    "\n",
    "    df = df.reindex_axis(sorted(df.columns), axis=1)\n",
    "    \n",
    "    \n",
    "    return df\n",
    "\n",
    "\n",
    "#print \"Reading BIS job filing data ...\"\n",
    "#df = PermitDataInput()\n",
    "\n",
    "#print df.dtypes\n",
    "\n",
    "\n",
    "#IMPORT DOB NOW DATA\n",
    "def DNDataInput():\n",
    "    df = pd.read_csv('I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\Active Permits - DOB NOWAuto.csv', low_memory=False)\n",
    "    \n",
    "    df[\"Permit Type\"] = \"EQ\"\n",
    "    df[\"Proposed Stories\"] = \"\"\n",
    "    df = df.rename(columns={'Work Type Name': 'Permit Sub Type', 'ExisitingBuildingStories':'Existing Stories',\n",
    "                           'Permit Expiration Date':'expire_DATE', 'JobType':'Job Type', \n",
    "                            'Business Name':'General Contractor'})\n",
    "    \n",
    "    \n",
    "    \n",
    "    df = df.drop('Sequence Number', 1)\n",
    "    df = df.drop('Permit Issued Date', 1)\n",
    "    df = df.drop('Filing Type', 1)\n",
    "    df = df.drop('Filing Status', 1)\n",
    "    \n",
    "    df = df.drop('Last Name', 1)\n",
    "    df = df.drop('First Name', 1)\n",
    "    df = df.drop('MiddleI nitial', 1)\n",
    "    #df = df.drop('Business Name', 1)\n",
    "    \n",
    "    df = df.drop('License', 1)\n",
    "    df = df.drop('License Number', 1)\n",
    "    \n",
    "    df['Permit Sub Type'] = df['Permit Sub Type'].str.replace('Side Walk Shed', 'SH')\n",
    "    df['Permit Sub Type'] = df['Permit Sub Type'].str.replace('Scaffold', 'SF')\n",
    "    df['Permit Sub Type'] = df['Permit Sub Type'].str.replace('Fence', 'FN')\n",
    "    \n",
    "    df = df.rename(columns={'Bin': 'BIN Number'})\n",
    "    df = df.reindex_axis(sorted(df.columns), axis=1)\n",
    "    \n",
    "    print \"len of dob now data\", len(df)\n",
    "\n",
    "    \n",
    "    return df\n",
    "\n",
    "#dobnow = DNDataInput()\n",
    "#print dobnow.dtypes\n",
    "\n",
    "\n",
    "def CODataInput():\n",
    "    df = pd.read_csv('I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\C of O Issued (BIS)Auto.csv', low_memory=False)\n",
    "    df = df[[\"J_BIN_NUMBER\", \"J_JOB_NUMBER\", \"AP_A_ISSUE_TYPE\", \"C of O Issue Date\"]]\n",
    "    df[\"J_BIN_NUMBER\"] = df[\"J_BIN_NUMBER\"].astype(str)\n",
    "    df[\"J_BIN_NUMBER\"] = df[\"J_BIN_NUMBER\"].map(str.strip)\n",
    "    df = df.rename(columns={'J_BIN_NUMBER': 'BIN Number', 'AP_A_ISSUE_TYPE': 'CO_TCO', 'J_JOB_NUMBER':'Job Number'})\n",
    "    \n",
    "    return df\n",
    "    \n",
    "#CREATE DATAFRAMES\n",
    "print \"Reading BIS job filing data ...\"\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Reading BIS job filing data\"  + \"\\n\")\n",
    "df = PermitDataInput()\n",
    "print \"Reading DOB Now job filing data ...\"\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Reading DOB Now job filing data\"  + \"\\n\")\n",
    "dobnow = DNDataInput()\n",
    "print \"Reading CO/TCO data for dictionary ...\"\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Reading CO/TCO data for dictionary\"  + \"\\n\")\n",
    "co = CODataInput()\n",
    "\n",
    "\n",
    "\n",
    "df[\"BIN Number\"] = df[\"BIN Number\"].astype(str)\n",
    "df[\"BIN Number\"] = df[\"BIN Number\"].map(str.strip)\n",
    "dobnow[\"BIN Number\"] = dobnow[\"BIN Number\"].astype(str)\n",
    "dobnow[\"BIN Number\"] = dobnow[\"BIN Number\"].map(str.strip)\n",
    "\n",
    "#INSERT THE CORRECT LAT LON COORDINATES FROM BIS INTO DOBNOW\n",
    "latDic = df.set_index('BIN Number')['Latitude'].to_dict()\n",
    "lonDic = df.set_index('BIN Number')['Latitude'].to_dict()\n",
    "\n",
    "for i in range(0, len(dobnow)):\n",
    "    if dobnow[\"BIN Number\"][i] in latDic:\n",
    "        dobnow[\"Latitude\"][i] = latDic[dobnow[\"BIN Number\"][i]]\n",
    "    if dobnow[\"BIN Number\"][i] in lonDic:\n",
    "        dobnow[\"Longitude\"][i] = lonDic[dobnow[\"BIN Number\"][i]]\n",
    "\n",
    "\n",
    "frames = [df, dobnow]\n",
    "result = pd.concat(frames)\n",
    "\n",
    "df = result.reset_index(drop=True)\n",
    "df['expire_DATE'] = pd.to_datetime(df['expire_DATE'])\n",
    "df = df.sort_values(by = 'BIN Number', ascending=True).reset_index(drop=True)\n",
    "\n",
    "df[\"Borough\"] = df[\"Borough\"].astype(str)\n",
    "df[\"Borough\"] = df[\"Borough\"].map(str.upper)\n",
    "\n",
    "\n",
    "print \"lengh of combined\", len(df)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "AGGREGATE AND COUNT DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aggregating to BIN level ...\n",
      "Number of BINs 39909\n",
      "0\n",
      "3990\n",
      "7980\n",
      "11970\n",
      "15960\n",
      "19950\n",
      "23940\n",
      "27930\n",
      "31920\n",
      "35910\n",
      "39900\n"
     ]
    }
   ],
   "source": [
    "def GetStories(dtf):    \n",
    "    existStories = dtf[dtf[\"Existing Stories\"] != 0]\n",
    "    existStories = existStories[\"Existing Stories\"].tolist()\n",
    "    existStories = filter(None, existStories)\n",
    "    #existStories = sorted(existStories, reverse=True)\n",
    "    #print existStories\n",
    "    \n",
    "    proposedStories = dtf[dtf[\"Proposed Stories\"] != 0]\n",
    "    proposedStories = proposedStories[\"Proposed Stories\"].tolist()\n",
    "    proposedStories = filter(None, proposedStories)\n",
    "    #proposedStories = sorted(proposedStories, reverse=True)\n",
    "    #print proposedStories\n",
    "\n",
    "    #stories\n",
    "    if len(proposedStories) > 0:\n",
    "        #if len(existStories) != 0:\n",
    "        stories = proposedStories[0]\n",
    "    elif len(existStories) > 0:\n",
    "        stories = existStories[0]\n",
    "    else:\n",
    "        stories = 0\n",
    "    return stories\n",
    "\n",
    "\n",
    "\n",
    "print \"Aggregating to BIN level ...\"\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Aggregating to BIN level\"  + \"\\n\")\n",
    "\n",
    "\n",
    "qn = pd.DataFrame(columns=('BIN', 'Address', 'Borough', 'Community Board', 'Number Permits' \n",
    "                           'Number Stories', 'NB', 'ShedScafFence', 'Job Type List'  'Latitude', 'Longitude'))\n",
    "\n",
    "binList = sorted(list(set(df[\"BIN Number\"].tolist())))\n",
    "\n",
    "print \"Number of BINs\", len(binList)\n",
    "\n",
    "\n",
    "AggList = []\n",
    "\n",
    "df[\"Job Type\"] = df[\"Job Type\"].astype(str)\n",
    "df[\"Job Type\"] = df[\"Job Type\"].map(str.strip)\n",
    "\n",
    "df[\"Job Number\"] = df[\"Job Number\"].astype(str)\n",
    "df[\"Job Number\"] = df[\"Job Number\"].map(str.strip)\n",
    "\n",
    "df[\"Permit Type\"] = df[\"Permit Type\"].astype(str)\n",
    "df[\"Permit Type\"] = df[\"Permit Type\"].map(str.strip)\n",
    "\n",
    "df[\"Permit Sub Type\"] = df[\"Permit Sub Type\"].astype(str)\n",
    "df[\"Permit Sub Type\"] = df[\"Permit Sub Type\"].map(str.strip)\n",
    "\n",
    "for i in range(0, len(binList)):\n",
    "#for i in range(0, 5):    \n",
    "    if i%(len(binList)/10) == 0:\n",
    "        print i\n",
    "    \n",
    "    df2 = df[df[\"BIN Number\"] == binList[i]]\n",
    "    df2 = df2.reset_index(drop=True)\n",
    "    \n",
    "    df2 = df2.sort_values(by = 'expire_DATE', ascending=False).reset_index(drop=True)\n",
    "    \n",
    "    #print df2\n",
    "    \n",
    "    #COUNT NUMBER OF PERMITS\n",
    "    df2[\"Permit Agg\"] = df2[\"Job Type\"] + \" \" + df2[\"Permit Type\"] + \" \" + df2[\"Permit Sub Type\"]\n",
    "   \n",
    "    PermitAggList = list(set(df2[\"Permit Agg\"].tolist() ) )\n",
    "    if 'nan' in PermitAggList:\n",
    "        PermitAggList.remove('nan')\n",
    "    \n",
    "    numPermits = len(PermitAggList)\n",
    "    if len(PermitAggList) == 0:\n",
    "        print PermitAggList, binList[i]\n",
    "    \n",
    "    #print df2\n",
    "    #print\n",
    "    #print PermitAggList\n",
    "\n",
    "\n",
    "    \n",
    "    \n",
    "    \n",
    "    #DETERMIN NUMBER OF FLOORS, LOOK AT NB FIRST, THEN DM, THEN AFTER THE MOST RECENT\n",
    "    storiesNB = df2.copy()\n",
    "    storiesNB = storiesNB[storiesNB[\"Job Type\"] == \"NB\"]\n",
    "    storiesDM = df2.copy()\n",
    "    storiesDM = storiesDM[storiesDM[\"Job Type\"] == \"DM\"]\n",
    "    storiesA1 = df2.copy()\n",
    "    storiesA1 = storiesA1[storiesA1[\"Job Type\"] == \"A1\"]\n",
    "    \n",
    "    if len(storiesNB) > 0:\n",
    "        stories = GetStories(storiesNB)\n",
    "    elif len(storiesDM) > 0:\n",
    "        stories = GetStories(storiesDM) \n",
    "    elif len(storiesA1) > 0:\n",
    "        stories = GetStories(storiesA1)\n",
    "    else:\n",
    "        a2 = df2.copy()\n",
    "        stories = GetStories(a2)\n",
    "           \n",
    "        \n",
    "    #NB\n",
    "    #job_types = df2[\"Job Type\"].tolist()\n",
    "    #if \"NB\" in job_types:\n",
    "    #    nb = \"YES\"\n",
    "    #else:\n",
    "    #    nb = \"NO\"\n",
    "        \n",
    "    #shed, scaffold or fence\n",
    "    permitSub = df2[\"Permit Sub Type\"].tolist()\n",
    "    if ('SH' in permitSub or 'SF' in permitSub or 'FN' in permitSub):\n",
    "        ssf = \"YES\"\n",
    "    else:\n",
    "        ssf = \"NO\"\n",
    "        \n",
    "        \n",
    "    #JOB TYPE AND JOB NUMBER, MAJOR JOB TYPES ONLY\n",
    "    jobTypeList = [\"NB\", \"DM\", \"A1\"]\n",
    "    df3 = df2.copy()\n",
    "    df3 = df3[df3['Job Type'].isin(jobTypeList)]\n",
    "    \n",
    "    #df3 = df2.copy()\n",
    "    jt_number = []\n",
    "    contList = []\n",
    "    if len(df3) > 0:\n",
    "        df3[\"Job Type Number\"] = df3[\"Job Type\"] + \" \" + df3[\"Job Number\"]\n",
    "        jt_number = df3[\"Job Type Number\"].tolist()\n",
    "        contList = df3[\"General Contractor\"].tolist()\n",
    "        \n",
    "        \n",
    "    #GET JOB TYPE, THE HIGHEST IN HIEARCHY,JOB NUMBER AND CONTRACTOR\n",
    "    nb = df2.copy()\n",
    "    nb = nb[nb[\"Job Type\"] == \"NB\"]\n",
    "    nb = nb.reset_index(drop=True)\n",
    "    dm = df2.copy()\n",
    "    dm = dm[dm[\"Job Type\"] == \"DM\"]\n",
    "    dm = dm.reset_index(drop=True)\n",
    "    a1 = df2.copy()\n",
    "    a1 = a1[a1[\"Job Type\"] == \"A1\"]\n",
    "    a1 = a1.reset_index(drop=True)\n",
    "    a2 = df2.copy()\n",
    "    a2 = a2[a2[\"Job Type\"] == \"A2\"]\n",
    "    a2 = a2.reset_index(drop=True)\n",
    "    a3 = df2.copy()\n",
    "    a3 = a3[a3[\"Job Type\"] == \"A3\"]\n",
    "    a3 = a3.reset_index(drop=True)\n",
    "\n",
    "    \n",
    "    if len(nb) > 0:\n",
    "        jobtype = nb[\"Job Type\"][0]\n",
    "        jobnumber = nb[\"Job Number\"][0]\n",
    "        contractor = nb[\"General Contractor\"][0]\n",
    "    elif len(dm) > 0:\n",
    "        jobtype = dm[\"Job Type\"][0]\n",
    "        jobnumber = dm[\"Job Number\"][0]\n",
    "        contractor = dm[\"General Contractor\"][0]\n",
    "    elif len(a1) > 0:\n",
    "        jobtype = a1[\"Job Type\"][0]\n",
    "        jobnumber = a1[\"Job Number\"][0]\n",
    "        contractor = a1[\"General Contractor\"][0]\n",
    "    elif len(a2) > 0:\n",
    "        jobtype = a2[\"Job Type\"][0]\n",
    "        jobnumber = a2[\"Job Number\"][0]\n",
    "        contractor = a2[\"General Contractor\"][0]\n",
    "    else:\n",
    "        jobtype = a3[\"Job Type\"][0]\n",
    "        jobnumber = a3[\"Job Number\"][0]\n",
    "        contractor = a3[\"General Contractor\"][0]\n",
    "          \n",
    "     \n",
    "    \n",
    "    #print \"***************\"\n",
    "    #print df3\n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "    AggList.append([binList[i],df2[\"Address\"][0], df2[\"Borough\"][0], df2[\"Community Board\"][0], \n",
    "                    numPermits, stories, ssf,jt_number,jobtype, jobnumber, contractor, contList, df2[\"Latitude\"][0], df2[\"Longitude\"][0] ] )\n",
    "    #print\n",
    "    #print df2\n",
    "    \n",
    "    \n",
    "data = pd.DataFrame(AggList)\n",
    "\n",
    "data = data.rename(columns={0: 'BIN', 1: 'Address', 2:'Borough', 3:'Community Board', 4:'Number of Permits',\n",
    "                            5:'Number of Stories',  6:'ScaffoldShedFence', 7:'Job Type and Number List', 8:'Job Type',\n",
    "                            9:'Job Number', 10:'Contractor', 11:'Contractor List',\n",
    "                            12:'Lat', 13:'Lon'})\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "IMPORT ACCIDENT DATA AND INSERT INTO DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "length before drop nan 1869\n",
      "length after drop nan 1838\n"
     ]
    }
   ],
   "source": [
    "pd.set_option('chained_assignment', None)\n",
    "\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Import accident data and insert into dataframe\"  + \"\\n\")\n",
    "\n",
    "\n",
    "def AccidentDataInput():\n",
    "\n",
    "    #import datetime\n",
    "    #import time\n",
    "    #from datetime import date\n",
    "    #from dateutil.relativedelta import relativedelta\n",
    "    #from dateutil import parser\n",
    "\n",
    "    #permits issued\n",
    "    df = pd.read_csv('I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\construction related accidents_3 YRS PERIODAuto.csv', low_memory=False)\n",
    "    \n",
    "    df = df[[\"BIN\"]]\n",
    "    \n",
    "    print \"length before drop nan\", len(df)\n",
    "    df = df.dropna()\n",
    "    print \"length after drop nan\", len(df)\n",
    "    \n",
    "    df[\"BIN\"] = df[\"BIN\"].astype(int)\n",
    "    df[\"BIN\"] = df[\"BIN\"].astype(str)\n",
    "    df[\"BIN\"] = df[\"BIN\"].map(str.strip)\n",
    "\n",
    "\n",
    "\n",
    "    \n",
    "    df[\"Value\"] = 1\n",
    "    \n",
    "    df = df.groupby(['BIN']).sum()\n",
    "    df = df.add_suffix('').reset_index()\n",
    "    \n",
    "    df = df.set_index('BIN')['Value'].to_dict()\n",
    "    \n",
    "    return df\n",
    "\n",
    "AccDic = AccidentDataInput()\n",
    "\n",
    "#INSERT ACCIDENTS\n",
    "data[\"Accidents\"] = 0\n",
    "data[\"BIN\"] = data[\"BIN\"].astype(str)\n",
    "data[\"BIN\"] = data[\"BIN\"].map(str.strip)\n",
    "\n",
    "for i in range(0, len(data)):\n",
    "    if data[\"BIN\"][i] in AccDic:\n",
    "        data[\"Accidents\"][i] = AccDic[data[\"BIN\"][i]]\n",
    "        \n",
    "#data = data[[\"BIN\", \"Number of Permits\", \"Number of Stories\", \"NB\", \"ScaffoldShedFence\", \"Accidents\", \"Lat\", \"Lon\"]]\n",
    "\n",
    "#data = data.reindex_axis(sorted(df.columns), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "INSERT CO/TCO DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Remove those CO/TCO not active ...\n",
      "Length of co 39351\n",
      "Length after drop co 2853\n",
      "Creating CO, TCO dictionary ...\n",
      "Creating date dictionary ...\n",
      "Inserting certificat of occupancy and recent date ... \n",
      "Number that match 655\n"
     ]
    }
   ],
   "source": [
    "#keep only active job numbers\n",
    "\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Insert CO and TCO data\"  + \"\\n\")\n",
    "\n",
    "\n",
    "count = 0\n",
    "\n",
    "df[\"Job Number\"] = df[\"Job Number\"].astype(str)\n",
    "co[\"Job Number\"] = co[\"Job Number\"].astype(str)\n",
    "\n",
    "dfJobNum = list(set(df[\"Job Number\"].tolist() ) )\n",
    "\n",
    "print \"Remove those CO/TCO not active ...\"\n",
    "print \"Length of co\", len(co)\n",
    "\n",
    "co = co[co['Job Number'].isin(dfJobNum)]\n",
    "co = co.reset_index(drop=True)\n",
    "\n",
    "print \"Length after drop co\", len(co)\n",
    "\n",
    "\n",
    "print \"Creating CO, TCO dictionary ...\"\n",
    "#includes co and tco\n",
    "data[\"CO/TCO\"] = \"NO\"\n",
    "data[\"CO/TCO Recent Date\"] = \"\"\n",
    "\n",
    "coDic = co.set_index('BIN Number')['CO_TCO'].to_dict()\n",
    "\n",
    "\n",
    "#create dictionary\n",
    "\n",
    "co_binList = list( set(co[\"BIN Number\"].tolist() ) )\n",
    "\n",
    "co['C of O Issue Date'] = pd.to_datetime(co['C of O Issue Date'])\n",
    "\n",
    "dateDic = {}\n",
    "\n",
    "print \"Creating date dictionary ...\"\n",
    "\n",
    "coG = co[[\"BIN Number\", \"C of O Issue Date\"]]\n",
    "#get the most recent date on the BIN\n",
    "coG = coG.groupby(['BIN Number'], sort=False)['C of O Issue Date'].max()\n",
    "\n",
    "#convert series to dataframe\n",
    "coG_df = pd.DataFrame(coG)\n",
    "\n",
    "coG_df = coG_df.add_suffix('').reset_index()\n",
    "\n",
    "coG_df[\"C of O Issue Date\"] = coG_df[\"C of O Issue Date\"].astype(str)\n",
    "\n",
    "coG_df\n",
    "\n",
    "\n",
    "dateDic = coG_df.set_index('BIN Number')['C of O Issue Date'].to_dict()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "print \"Inserting certificat of occupancy and recent date ... \"\n",
    "for i in range(0, len(data)):\n",
    "    if data[\"BIN\"][i] in coDic:\n",
    "        data[\"CO/TCO\"][i] = \"YES\" \n",
    "    \n",
    "        data[\"CO/TCO Recent Date\"][i] = dateDic[data[\"BIN\"][i]]\n",
    "        \n",
    "        count+=1\n",
    "            \n",
    "print \"Number that match\", count\n",
    "        \n",
    "        \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE THE SCORES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating the scores...\n"
     ]
    }
   ],
   "source": [
    "#text_file.write(str(datetime.now()) + \"...\" + \"Score the data\"  + \"\\n\")\n",
    "print \"Creating the scores...\"\n",
    "\n",
    "data['Score'] = 0\n",
    "for i in range(0, len(data)):\n",
    "    score = []\n",
    "    #number of permits\n",
    "    if (data[\"Number of Permits\"][i] >= 1 and data[\"Number of Permits\"][i] <= 4):\n",
    "        score.append(1)\n",
    "    if (data[\"Number of Permits\"][i] >= 5 and data[\"Number of Permits\"][i] <= 7):\n",
    "        score.append(2)\n",
    "    if (data[\"Number of Permits\"][i] >= 8 and data[\"Number of Permits\"][i] <= 10):\n",
    "        score.append(3)\n",
    "    if (data[\"Number of Permits\"][i] >= 11 ):\n",
    "        score.append(4)\n",
    "    \n",
    "    if  \"NB\" in data[\"Job Type\"][i] > 0:\n",
    "        score.append(1)\n",
    "    else:\n",
    "        score.append(0)\n",
    "        \n",
    "    if data[\"ScaffoldShedFence\"][i] > 0:\n",
    "        score.append(1)\n",
    "    else:\n",
    "        score.append(0)\n",
    "        \n",
    "    if data[\"Number of Stories\"][i] >= 10:\n",
    "        score.append(1)\n",
    "    else:\n",
    "        score.append(0)\n",
    "        \n",
    "    if data[\"Accidents\"][i] > 0:\n",
    "        score.append(1)\n",
    "    else:\n",
    "        score.append(0)\n",
    "        \n",
    "        \n",
    "    data['Score'][i] = sum(score)\n",
    "        \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OUTPUT ALL DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BIN                          object\n",
       "Address                      object\n",
       "Borough                      object\n",
       "Community Board             float64\n",
       "Number of Permits             int64\n",
       "Number of Stories             int64\n",
       "ScaffoldShedFence            object\n",
       "Job Type and Number List     object\n",
       "Job Type                     object\n",
       "Job Number                   object\n",
       "Contractor                   object\n",
       "Contractor List              object\n",
       "Lat                         float64\n",
       "Lon                         float64\n",
       "Accidents                     int64\n",
       "CO/TCO                       object\n",
       "CO/TCO Recent Date           object\n",
       "Score                         int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data.to_csv(\"I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\test.csv\", index=False)\n",
    "data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OUTPUT SUBSETTED DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "length before drop low risk 39909\n",
      "len after drop 528\n",
      "drop CO/TCO\n",
      "len after drop 419\n"
     ]
    }
   ],
   "source": [
    "output = data.copy()\n",
    "\n",
    "#output[\"Job Type and Number List\"] = output[\"Job Type and Number List\"].astype(str)\n",
    "#output[\"Job Type and Number List\"] = output[\"Job Type and Number List\"].str.replace(\"'\",\"\")\n",
    "#output[\"Job Type and Number List\"] = output[\"Job Type and Number List\"].str.replace(\"[\", '')\n",
    "#output[\"Job Type and Number List\"] = output[\"Job Type and Number List\"].str.replace(\"]\", '')\n",
    "\n",
    "#output[\"Contractor List\"] = output[\"Contractor List\"].astype(str)\n",
    "#output[\"Contractor List\"] = output[\"Contractor List\"].str.replace(\"'\",\"\")\n",
    "#output[\"Contractor List\"] = output[\"Contractor List\"].str.replace(\"[\", '')\n",
    "#output[\"Contractor List\"] = output[\"Contractor List\"].str.replace(\"]\", '')\n",
    "\n",
    "#drop columns\n",
    "output = output.drop('Job Type and Number List', 1)\n",
    "output = output.drop('Contractor List', 1)\n",
    "\n",
    "output[\"Contractor\"] = output[\"Contractor\"].str.replace(',', '')\n",
    "output[\"Address\"] = output[\"Address\"].str.replace(',', '')\n",
    "\n",
    "#output[\"Job Type and Number\"] = output[\"Job Type and Number\"].str.replace('[^\\w\\s]','')\n",
    "\n",
    "#output[\"Job Type and Number List\"] = output[\"Job Type and Number List\"].map(str.strip)\n",
    "\n",
    "#remove low risk\n",
    "print \"length before drop low risk\", len(output)\n",
    "output = output[output[\"Score\"] > 4]\n",
    "output = output.reset_index(drop=True)\n",
    "print \"len after drop\", len(output)\n",
    "\n",
    "#remove low risk\n",
    "print \"drop CO/TCO\"\n",
    "output = output[output[\"CO/TCO\"] != \"YES\"]\n",
    "output = output.reset_index(drop=True)\n",
    "print \"len after drop\", len(output)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-06-25 10:09:15.302000\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "#EXPORT AS CSV AND JSON FILE\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"Output the data\"  + \"\\n\")\n",
    "output.to_csv(\"I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\SiteSafetyAutomationOutput.csv\", index=False)\n",
    "#text_file.write(str(datetime.now()) + \"...\" + \"DONE\"  + \"\\n\")\n",
    "#text_file.close()\n",
    "\n",
    "#EXPORT JSON\n",
    "import csv\n",
    "import json\n",
    "reader = csv.DictReader(open('I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\SiteSafetyAutomationOutput.csv', 'rb'))\n",
    "\n",
    "dict_list = []\n",
    "for line in reader:\n",
    "    dict_list.append(line)\n",
    "\n",
    "    \n",
    "with open('I:\\\\BAT\\\\SITE SAFETY AUTOMATION\\\\sourceData\\\\SiteSafetyAutomationOutput.json', 'w') as outfile:\n",
    "    json.dump(dict_list, outfile)\n",
    "\n",
    "print str(datetime.now())\n",
    "\n",
    "print \"DONE\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}