Inspection Productivity Report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1: Progress on Targeting B Complaint Backlog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#B COMPLAINT AGING REPORT\n",
    "dfn = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Active B Complaint Aging Report (MMR)\\\\Active B Complaint Aging Report (MMR).csv\")\n",
    "#make sure no column names have leading and trailing edge sppaces\n",
    "dfn = dfn.rename(columns=lambda x: x.strip())\n",
    "\n",
    "\n",
    "df = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Active B Complaint Aging Report (MMR)\\\\BComplaints_Update.csv\")\n",
    "df[\"Complaint Age\"] = df[\"Complaint Age\"].str.replace('20-Oct', '10-20')\n",
    "\n",
    "#df = df.rename(columns={'26-Jun': 'Jun-16', '10-Aug': 'Aug-16', '13-Oct': 'Oct-16', '13-Nov': 'Nov-16', '7-Dec': 'Dec-16', '5-Jan': 'Jan-17'})\n",
    "#df = df.rename(columns={'Jun-16':'6-26-16','Aug-16':'8-10-16', 'Oct-16':'10-13-16', 'Nov-16':'11-13-16', '7-Dec': '12-16-16', '5-Jan': '1-17-17'})\n",
    "date = \"2-6-17\"\n",
    "df[date] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.drop('Unnamed: 0', 1)\n",
    "#df = df.drop('Jan-30-17', 1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.set_option('chained_assignment', None)\n",
    "\n",
    "for i in range(0, len(dfn)):\n",
    "    if dfn[\"age of complaint\"][i] < 10:\n",
    "        df[date][0] = df[date][0] + 1\n",
    "    if (dfn[\"age of complaint\"][i] >= 10 and dfn[\"age of complaint\"][i] < 20):\n",
    "        df[date][1] = df[date][1] + 1\n",
    "    if (dfn[\"age of complaint\"][i] >= 20 and dfn[\"age of complaint\"][i] < 30):\n",
    "        df[date][2] = df[date][2] + 1\n",
    "    if (dfn[\"age of complaint\"][i] >= 30 and dfn[\"age of complaint\"][i] < 40):\n",
    "        df[date][3] = df[date][3] + 1   \n",
    "    if (dfn[\"age of complaint\"][i] >= 40 and dfn[\"age of complaint\"][i] < 50):\n",
    "        df[date][4] = df[date][4] + 1  \n",
    "    if (dfn[\"age of complaint\"][i] >= 50 and dfn[\"age of complaint\"][i] < 60):\n",
    "        df[date][5] = df[date][5] + 1     \n",
    "    if (dfn[\"age of complaint\"][i] >= 60 and dfn[\"age of complaint\"][i] < 70):\n",
    "        df[date][6] = df[date][6] + 1   \n",
    "    if (dfn[\"age of complaint\"][i] >= 70 and dfn[\"age of complaint\"][i] < 80):\n",
    "        df[date][7] = df[date][7] + 1   \n",
    "    if (dfn[\"age of complaint\"][i] >= 80 and dfn[\"age of complaint\"][i] < 90):\n",
    "        df[date][8] = df[date][8] + 1   \n",
    "    if (dfn[\"age of complaint\"][i] >= 90 and dfn[\"age of complaint\"][i] < 100):\n",
    "        df[date][9] = df[date][9] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 100 and dfn[\"age of complaint\"][i] < 110):\n",
    "        df[date][10] = df[date][10] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 110 and dfn[\"age of complaint\"][i] < 120):\n",
    "        df[date][11] = df[date][11] + 1 \n",
    "    \n",
    "    if (dfn[\"age of complaint\"][i] >= 120 and dfn[\"age of complaint\"][i] < 130):\n",
    "        df[date][12] = df[date][12] + 1   \n",
    "    if (dfn[\"age of complaint\"][i] >= 130 and dfn[\"age of complaint\"][i] < 140):\n",
    "        df[date][13] = df[date][13] + 1  \n",
    "        \n",
    "    if (dfn[\"age of complaint\"][i] >= 140 and dfn[\"age of complaint\"][i] < 150):\n",
    "        df[date][14] = df[date][14] + 1  \n",
    "    if (dfn[\"age of complaint\"][i] >= 150 and dfn[\"age of complaint\"][i] < 160):\n",
    "        df[date][15] = df[date][15] + 1 \n",
    "        \n",
    "    if (dfn[\"age of complaint\"][i] >= 160 and dfn[\"age of complaint\"][i] < 170):\n",
    "        df[date][16] = df[date][16] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 170 and dfn[\"age of complaint\"][i] < 180):\n",
    "        df[date][17] = df[date][17] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 180 and dfn[\"age of complaint\"][i] < 190):\n",
    "        df[date][18] = df[date][18] + 1 \n",
    "        \n",
    "    if (dfn[\"age of complaint\"][i] >= 190 and dfn[\"age of complaint\"][i] < 200):\n",
    "        df[date][19] = df[date][19] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 200 and dfn[\"age of complaint\"][i] < 210):\n",
    "        df[date][20] = df[date][20] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 210 and dfn[\"age of complaint\"][i] < 220):\n",
    "        df[date][21] = df[date][21] + 1 \n",
    "        \n",
    "    if (dfn[\"age of complaint\"][i] >= 220 and dfn[\"age of complaint\"][i] < 230):\n",
    "        df[date][22] = df[date][22] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 230 and dfn[\"age of complaint\"][i] < 240):\n",
    "        df[date][23] = df[date][23] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 240 and dfn[\"age of complaint\"][i] < 250):\n",
    "        df[date][24] = df[date][24] + 1 \n",
    "        \n",
    "    if (dfn[\"age of complaint\"][i] >= 250 and dfn[\"age of complaint\"][i] < 260):\n",
    "        df[date][25] = df[date][25] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 260 and dfn[\"age of complaint\"][i] < 270):\n",
    "        df[date][26] = df[date][26] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 270 and dfn[\"age of complaint\"][i] < 280):\n",
    "        df[date][27] = df[date][27] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 280 and dfn[\"age of complaint\"][i] < 290):\n",
    "        df[date][28] = df[date][28] + 1 \n",
    "        \n",
    "    if (dfn[\"age of complaint\"][i] >= 290 and dfn[\"age of complaint\"][i] < 300):\n",
    "        df[date][29] = df[date][29] + 1 \n",
    "    if (dfn[\"age of complaint\"][i] >= 300 and dfn[\"age of complaint\"][i] < 310):\n",
    "        df[date][30] = df[date][30] + 1\n",
    "\n",
    "\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#df = df.rename(columns={'Dec-16':'12-16-16','Jan-17':'1-17-17'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Active B Complaint Aging Report (MMR)\\\\BComplaints_Update.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2: Inspector Productivity Analysis (for inspection unit comparison)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option('chained_assignment', None)\n",
    "\n",
    "#read in inspector combined data, this should have all of the inspector data, including cranes and derricks and time accounting, but probably doesn't\n",
    "dfc = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (Combined by Inspector)\\\\IR Inspector Productivity (COMBINED) (By Inspector).csv\")\n",
    "\n",
    "#read in cranes and derricks inspector data\n",
    "dfcd = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (MAIN-Deck Update Check)\\\\IR Inspector Productivity (MAIN-Deck Update) (CHECK).csv\")\n",
    "\n",
    "dfcd['Inspector Dept'] = dfcd['Inspector Dept'].str.replace('Cranes and Derricks Inspections', 'Cranes and Derricks')\n",
    "\n",
    "#DO NEW INPUT, READ IN THE RAW TIME ACCOUNTING AND ACCELA INSPECTION DATA AND COMBINE\n",
    "print \"inspector combined data length\", len(dfc)\n",
    "print \"cranes and derricks inspector combined data length\", len(dfcd)\n",
    "\n",
    "common_id = list(set(dfc.TIMELOGSEQ_INSPNO) & set(dfcd.INSPECTION_ID))\n",
    "\n",
    "print \"how many do they have in common?\", len(common_id)\n",
    "\n",
    "dfc[\"TIMELOGSEQ_INSPNO\"] = dfc[\"TIMELOGSEQ_INSPNO\"].astype(str)\n",
    "dfcd[\"RECORD_ID\"] = dfcd[\"RECORD_ID\"].astype(str)\n",
    "\n",
    "dfcd[\"Bureau\"] = \"\"\n",
    "dfcd = dfcd[[\"INSPECTION_ID\", \"INSPECTION_TYPE\", \"Inspector Dept\", \"INSPECTOR NAME\", \"DATE_INSPECTION\", \"Bureau\"]]\n",
    "dfc[\"INSPECTOR NAME\"] = dfc[\"NAME_FIRST\"] + \" \" + dfc[\"NAME_LAST\"]\n",
    "dfc = dfc[[\"TIMELOGSEQ_INSPNO\", \"Inspection Type\", \"DEPARTMENT\", \"INSPECTOR NAME\", \"INSPECTION_DATE\", \"Bureau\"]]\n",
    "\n",
    "dfc = dfc.rename(columns={'TIMELOGSEQ_INSPNO': 'INSPECTION_ID', 'Inspection Type': 'INSPECTION_TYPE', 'DEPARTMENT':'Inspector Dept', 'INSPECTION_DATE':'DATE_INSPECTION'})\n",
    "\n",
    "\n",
    "#DEFINE BUREAU IN DFCD DATAFRAME, ENFORCEMENT OR DEVELOPMENT\n",
    "dfcd[\"Bureau\"] = \"DEVELOPMENT\"\n",
    "\n",
    "for i in range(0, len(dfcd)):\n",
    "    if \"QA\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Sweep\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Access Warrant Execution\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    \n",
    "    if \"Audit/Investigations\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Cease Use Re-Inspection\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Class 1 Violation Re-Inspection\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "\n",
    "    if \"Correspondence Response\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Paired Inspection for Complaints\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Spot Check\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "        \n",
    "    if \"Violation Reinspection\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Monitoring/Follow-up\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "    if \"Complaint\" in dfcd[\"INSPECTION_TYPE\"][i]:\n",
    "        dfcd[\"Bureau\"][i] = \"ENFORCEMENT\"\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#FILTERS FROM JOSH'S ORACLE REPORT\n",
    "\"\"\"\n",
    "QA, Sweep, Access Warrant Execution, Audit/Investigations, \n",
    "Cease Use Re-Inspection, Class 1 Violation Re-Inspection, \n",
    "Correspondence Response, Paired Inspection for Complaints, \n",
    "Spot Check, Violation Reinspection, Monitoring/Follow-up, \n",
    "Complaint Inspection - BL, Complaint Inspection - CD, \n",
    "Complaint Inspection - CN, Complaint Inspection - EL, Complaint Inspection - ERT, \n",
    "Complaint Inspection - EV, Complaint Inspection - MSH, Complaint Inspection - Night ERT, \n",
    "Complaint Inspection - PL, Complaint Inspection - QOL, Complaint Inspection - SS, \n",
    "Complaint Re-Inspection - BL, Complaint Re-Inspection - CD, Complaint Re-Inspection - CN, \n",
    "Complaint Re-Inspection - EL, Complaint Re-Inspection - ERT, Complaint Re-Inspection - EV, \n",
    "Complaint Re-Inspection - MSH, Complaint Re-Inspection - Night ERT, \n",
    "Complaint Re-Inspection - PL, Complaint Re-Inspection - QOL, \n",
    "Complaint Re-Inspection - SS\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#CREATE MONTH, YEAR DATE\n",
    "dfc[\"DATE_INSPECTION\"] = pd.to_datetime(dfc[\"DATE_INSPECTION\"])\n",
    "dfc[\"DATE_INSPECTION\"] = dfc[\"DATE_INSPECTION\"].apply(lambda x: x.date())\n",
    "\n",
    "dfc[\"Date\"] = \"\"\n",
    "\n",
    "for i in range(0, len(dfc)):\n",
    "    if i%50000 == 0:\n",
    "        print i\n",
    "    month = dfc[\"DATE_INSPECTION\"][i].month\n",
    "    year = dfc[\"DATE_INSPECTION\"][i].year\n",
    "    dfc[\"Date\"][i] = str(month) + \"-\" + str(year)\n",
    "\n",
    "    \n",
    "dfcd[\"DATE_INSPECTION\"] = pd.to_datetime(dfcd[\"DATE_INSPECTION\"])\n",
    "dfcd[\"DATE_INSPECTION\"] = dfcd[\"DATE_INSPECTION\"].apply(lambda x: x.date())\n",
    "\n",
    "dfcd[\"Date\"] = \"\"\n",
    "\n",
    "for i in range(0, len(dfcd)):\n",
    "    if i%1000 == 0:\n",
    "        print i\n",
    "    month = dfcd[\"DATE_INSPECTION\"][i].month\n",
    "    year = dfcd[\"DATE_INSPECTION\"][i].year\n",
    "    dfcd[\"Date\"][i] = str(month) + \"-\" + str(year)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#CONCATINATE DATAFRAMES\n",
    "\n",
    "f1 = dfc[[ \"INSPECTION_ID\", \"Inspector Dept\", \"Date\", \"Bureau\"]]\n",
    "f2 = dfcd[[\"INSPECTION_ID\",\"Inspector Dept\", \"Date\", \"Bureau\"]]\n",
    "\n",
    "frames = [f1, f2]\n",
    "\n",
    "print \"length of dfc\", len(dfc)\n",
    "print \"length of dfcd\", len(dfcd)\n",
    "merged = pd.concat(frames, axis=0)\n",
    "print \"length of merged data\", len(merged)\n",
    "\n",
    "merged = merged.drop_duplicates(subset=['INSPECTION_ID']).reset_index(drop=True)\n",
    "merged = merged.reset_index(drop=True)\n",
    "\n",
    "print \"length of dropped dupliates\", len(merged) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "#DO GROUPBY ON DATA TO GET IN FORMAT TO MERGE WITH HISTORICAL DATA\n",
    "\n",
    "merged[\"Inspector Dept\"] = merged[\"Inspector Dept\"].map(str.strip)\n",
    "merged[\"Date\"] = merged[\"Date\"].map(str.strip)\n",
    "merged[\"Bureau\"] = merged[\"Bureau\"].map(str.strip)\n",
    "\n",
    "\n",
    "\n",
    "merged[\"Value\"] = 1\n",
    "mergedG = merged.groupby(['Inspector Dept', 'Date', 'Bureau']).sum()\n",
    "#mergedG = mergedG.reset_index(drop=True)\n",
    "mergedG = mergedG.add_suffix('_Count').reset_index()\n",
    "mergedG = mergedG.rename(columns={'Value_Count': 'Value'})\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#mergedExport = merged[merged[\"Date\"] == '1-2017']\n",
    "\n",
    "#mergedExport.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (Combined by Inspector)\\\\TEST IR Inspector Productivity.csv\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mergedG = mergedG[mergedG[\"Date\"] == '1-2017']\n",
    "\n",
    "mergedG['Date'] = mergedG['Date'].str.replace('1-2017', '17-Jan')\n",
    "\n",
    "mergedG = mergedG.reset_index(drop=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#ADD ADDITIONAL INSPECTIONS, FOR JAN 17 ADD 93 TO CRANES AND DERICKS ENFORCEMENT\n",
    "print mergedG[\"Value\"][8]\n",
    "mergedG[\"Value\"][8] = mergedG[\"Value\"][8] + 93\n",
    "print mergedG[\"Value\"][8]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mergedG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#ADD ABOVE DATA TO HISTORICAL DATA\n",
    "\n",
    "hist = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (Combined by Inspector)\\\\time_series_charts3_JF.csv\")\n",
    "\n",
    "print len(hist)\n",
    "hist = hist[hist.Metric.str.contains(\"Route\") == False]\n",
    "hist = hist[hist.Metric.str.contains(\"No.Access\") == False]\n",
    "hist = hist[hist.Metric.str.contains(\"Complaints.With\") == False]\n",
    "\n",
    "hist = hist.reset_index(drop=True)\n",
    "print len(hist)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hist[\"Value\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "  \n",
    "hist['Metric'] = hist['Metric'].str.replace('Dev.Inspections.Attempted', 'DEVELOPMENT')\n",
    "hist['Metric'] = hist['Metric'].str.replace('Enf.Inspections.Attempted', 'ENFORCEMENT')\n",
    "\n",
    "\n",
    "\n",
    "hist['Unit'] = hist['Unit'].str.replace('Boilers.Dev.and.Enf', 'Boilers')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Borough.Construction.Enforcement', 'Construction (ENF)')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Construction.Development', 'Construction (DEV)')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Cranes.Derricks.Dev.and.Enf', 'Cranes and Derricks')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Electrical.Development', 'Electrical (DEV)')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Electrical.Enforcement', 'Electrical (ENF)')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Elevators.Dev.and.Enf', 'Elevators')\n",
    "hist['Unit'] = hist['Unit'].str.replace('ERT.Enforcement', 'ERT')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Plumbing.Development', 'Plumbing (DEV)')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Plumbing.Enforcement', 'Plumbing (ENF)')\n",
    "hist['Unit'] = hist['Unit'].str.replace('Quality.of.Life.Enforcement', 'QOL')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#hist = hist.sort_values(by = 'Month', ascending=True).reset_index(drop=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mergedG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hist = hist.rename(columns={'Month': 'Date', 'Unit': 'Inspector Dept', 'Metric': 'Bureau'})\n",
    "hist = hist.rename(columns={'Inspector Dept': 'Unit'})\n",
    "\n",
    "\n",
    "hist.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#MERGE HISTORICAL DATA WITH THIS MONTHS DATA\n",
    "mergedG = mergedG[[\"Date\", \"Inspector Dept\", \"Bureau\", \"Value\"]]\n",
    "hist = hist.rename(columns={'Month': 'Date', 'Unit': 'Inspector Dept', 'Metric': 'Bureau'})\n",
    "\n",
    "hist = hist.reset_index(drop=True)\n",
    "\n",
    "\n",
    "frames = [hist, mergedG]\n",
    "\n",
    "final = pd.concat(frames)\n",
    "\n",
    "final = final.reset_index(drop=True)\n",
    "final = final.rename(columns={'Inspector Dept': 'Unit'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "final[\"Value\"] = abs(final[\"Value\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "final[\"Value\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "#in combined data\n",
    "count = 0\n",
    "for i in range(0, len(dfc)):\n",
    "    if \"Xavier\" in  dfc[\"NAME_FIRST\"][i]:\n",
    "        print dfc[\"NAME_FIRST\"][i] + \" \" + dfc[\"NAME_LAST\"][i] + \" \" + dfc[\"INSPECTION_DATE\"][i]\n",
    "        count +=1\n",
    "        \n",
    "print count\n",
    "\n",
    "count=0\n",
    "for i in range(0, len(dfcd)):\n",
    "    if \"Xavier\" in  dfcd[\"INSPECTOR NAME\"][i]:\n",
    "        print dfcd[\"INSPECTOR NAME\"][i] + \" \" + dfcd[\"DATE_INSPECTION\"][i]\n",
    "        count+=1\n",
    "print count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hist.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (Combined by Inspector)\\\\IR Inspector Productivity 2.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#RESTRUCTURE DATA SO CAN GET IN FORMAT TO ADD HISOTRICAL DATA\n",
    "res = merged[[\"Inspector Dept\", \"DATE_INSPECTION\", \"Bureau\"]]\n",
    "\n",
    "#res[\"Date\"] = res[\"DATE_INSPECTION\"]\n",
    "res[\"DATE_INSPECTION\"] = pd.to_datetime(res[\"DATE_INSPECTION\"])\n",
    "res[\"DATE_INSPECTION\"] = res[\"DATE_INSPECTION\"].apply(lambda x: x.date())\n",
    "\n",
    "res[\"DATE_INSPECTION\"] = res[\"DATE_INSPECTION\"].astype(str)\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "res[\"DATE_INSPECTION\"] = res[\"DATE_INSPECTION\"].astype(str)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#res[\"DATE_INSPECTION\"][0]\n",
    "\n",
    "a = res[\"DATE_INSPECTION\"][0]\n",
    "\n",
    "#print a\n",
    "\n",
    "print res[\"DATE_INSPECTION\"][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "res.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "res_list = []\n",
    "\n",
    "for i in range(0, 10):\n",
    "    res_list.append(res[\"DATE_INSPECTION\"][i])\n",
    "\n",
    "print res_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Development and Enforcement Inspections analysis, dumbell charts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db = merged\n",
    "\n",
    "db[\"DATE_INSPECTION\"] = pd.to_datetime(db['DATE_INSPECTION'])\n",
    "\n",
    "db[\"DATE_INSPECTION\"] = db[\"DATE_INSPECTION\"].apply(lambda x: x.date())\n",
    "\n",
    "db[\"DATE_INSPECTION\"] = pd.to_datetime(db['DATE_INSPECTION'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Analysis for Electrical enforcement, two months, december 2016 and jan 2017\n",
    "\n",
    "#get dates\n",
    "\n",
    "print \"length before subset\", len(db)\n",
    "\n",
    "start_time = '12-1-2016'\n",
    "\n",
    "end_time = '1-31-2017'\n",
    "\n",
    "mask = (db['DATE_INSPECTION'] >= start_time) & (db['DATE_INSPECTION'] <= end_time)\n",
    "\n",
    "db_subset = db.loc[mask]\n",
    "\n",
    "print \"length after subset\", len(db_subset)\n",
    "\n",
    "db_subset = db_subset.reset_index(drop=True)\n",
    "\n",
    "\n",
    "db_subset[\"Month\"] = \"\"\n",
    "db_subset[\"Day\"] = \"\"\n",
    "db_subset[\"Year\"] = \"\"\n",
    "\n",
    "for i in range(0, len(db_subset)):\n",
    "    db_subset[\"Month\"][i] = db_subset[\"DATE_INSPECTION\"][i].month\n",
    "    db_subset[\"Day\"][i] = db_subset[\"DATE_INSPECTION\"][i].day\n",
    "    db_subset[\"Year\"][i] = db_subset[\"DATE_INSPECTION\"][i].year\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#ELECTRICAL ENFORCEMENT DECEMBER\n",
    "qn = pd.DataFrame(columns=('Date', 'Inspector Dept', 'INSPECTOR NAME', 'Total Days Worked', 'Total Visits', 'Avg Visits Per Day'))\n",
    "\n",
    "\n",
    "#subset for electrical enforcement\n",
    "db_subsetE = db_subset[db_subset['Inspector Dept'] == 'Electrical (ENF)']\n",
    "\n",
    "db_subsetE = db_subsetE.reset_index(drop=True)\n",
    "\n",
    "#For December\n",
    "db_subsetE12 = db_subsetE[db_subsetE['Month'] == 12]\n",
    "\n",
    "db_subsetE12 = db_subsetE12.reset_index(drop=True)\n",
    "\n",
    "#get inspector names\n",
    "\n",
    "i_names = []\n",
    "\n",
    "for i in range(0, len(db_subsetE12)):\n",
    "    i_names.append(db_subsetE12[\"INSPECTOR NAME\"][i])\n",
    "\n",
    "i_names = set(i_names)\n",
    "i_names = list(i_names)\n",
    "\n",
    "print i_names\n",
    "\n",
    "\n",
    "\n",
    "for i in range(0, len(i_names)):\n",
    "    #get the dataframe for each inspector\n",
    "    df2 =  db_subsetE12[db_subsetE12['INSPECTOR NAME'] == i_names[i]]\n",
    "    \n",
    "    df2 = df2.reset_index(drop=True)\n",
    "    \n",
    "    #total unique days worked\n",
    "    total_days_work = float(len(df2['INSPECTOR NAME'].groupby(df2['DATE_INSPECTION']) ) )\n",
    "    print total_days_work\n",
    "    \n",
    "    df2['DATE_INSPECTION'] = df2['DATE_INSPECTION'].astype(str)\n",
    "    \n",
    "    #get list of dates\n",
    "    df2ListDate = df2['DATE_INSPECTION'].tolist()\n",
    "    df2ListDate = set(df2ListDate)\n",
    "    df2ListDate = list(df2ListDate)\n",
    "    \n",
    "    print df2ListDate\n",
    "    \n",
    "    \n",
    "    total_visits = 0.0\n",
    "    \n",
    "    for j in range(0, len(df2ListDate)):\n",
    "            #print df2[\"INSPECTOR NAME\"][j] + \" \" + str(df2['DATE_INSPECTION'][j] )\n",
    "            num_ins_a_day =  len( df2[df2['DATE_INSPECTION'] == df2ListDate[j]] )\n",
    "            total_visits = total_visits + num_ins_a_day\n",
    "        \n",
    "    date = str(df2[\"Month\"][0]) + \"-\" + str(df2[\"Year\"][0])\n",
    "    qn.loc[i] = [date, df2[\"Inspector Dept\"][0] , i_names[i], total_days_work, total_visits, total_visits/total_days_work]\n",
    "\n",
    "\n",
    "    \n",
    "#ELECTRICAL ENFORCEMENT JANUARY\n",
    "qn2 = pd.DataFrame(columns=('Date', 'Inspector Dept', 'INSPECTOR NAME', 'Total Days Worked', 'Total Visits', 'Avg Visits Per Day'))\n",
    "\n",
    "\n",
    "#subset for electrical enforcement\n",
    "db_subsetE = db_subset[db_subset['Inspector Dept'] == 'Electrical (ENF)']\n",
    "\n",
    "db_subsetE = db_subsetE.reset_index(drop=True)\n",
    "\n",
    "#For December\n",
    "db_subsetE12 = db_subsetE[db_subsetE['Month'] == 1]\n",
    "\n",
    "db_subsetE12 = db_subsetE12.reset_index(drop=True)\n",
    "\n",
    "#get inspector names\n",
    "\n",
    "i_names = []\n",
    "\n",
    "for i in range(0, len(db_subsetE12)):\n",
    "    i_names.append(db_subsetE12[\"INSPECTOR NAME\"][i])\n",
    "\n",
    "i_names = set(i_names)\n",
    "i_names = list(i_names)\n",
    "\n",
    "print i_names\n",
    "\n",
    "\n",
    "\n",
    "for i in range(0, len(i_names)):\n",
    "    #get the dataframe for each inspector\n",
    "    df2 =  db_subsetE12[db_subsetE12['INSPECTOR NAME'] == i_names[i]]\n",
    "    \n",
    "    df2 = df2.reset_index(drop=True)\n",
    "    \n",
    "    #total unique days worked\n",
    "    total_days_work = float(len(df2['INSPECTOR NAME'].groupby(df2['DATE_INSPECTION']) ) )\n",
    "    print total_days_work\n",
    "    \n",
    "    df2['DATE_INSPECTION'] = df2['DATE_INSPECTION'].astype(str)\n",
    "    \n",
    "    #get list of dates\n",
    "    df2ListDate = df2['DATE_INSPECTION'].tolist()\n",
    "    df2ListDate = set(df2ListDate)\n",
    "    df2ListDate = list(df2ListDate)\n",
    "    \n",
    "    print df2ListDate\n",
    "    \n",
    "    \n",
    "    total_visits = 0.0\n",
    "    \n",
    "    for j in range(0, len(df2ListDate)):\n",
    "            #print df2[\"INSPECTOR NAME\"][j] + \" \" + str(df2['DATE_INSPECTION'][j] )\n",
    "            num_ins_a_day =  len( df2[df2['DATE_INSPECTION'] == df2ListDate[j]] )\n",
    "            total_visits = total_visits + num_ins_a_day\n",
    "        \n",
    "    date = str(df2[\"Month\"][0]) + \"-\" + str(df2[\"Year\"][0])\n",
    "    qn2.loc[i] = [date, df2[\"Inspector Dept\"][0] , i_names[i], total_days_work, total_visits, total_visits/total_days_work]\n",
    "\n",
    "    \n",
    "#MERGE DATAFRAMES\n",
    "frames = [qn, qn2]\n",
    "\n",
    "result = pd.concat(frames)\n",
    "\n",
    "\n",
    "#result.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (Combined by Inspector)\\\\Dev Enf Insp Elec Enf.csv\")\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#QUALITY OF LIFE\n",
    "#QOL ENFORCEMENT DECEMBER\n",
    "qn3 = pd.DataFrame(columns=('Date', 'Inspector Dept', 'INSPECTOR NAME', 'Total Days Worked', 'Total Visits', 'Avg Visits Per Day'))\n",
    "\n",
    "\n",
    "#subset for electrical enforcement\n",
    "db_subsetE = db_subset[db_subset['Inspector Dept'] == 'QOL']\n",
    "\n",
    "db_subsetE = db_subsetE.reset_index(drop=True)\n",
    "\n",
    "#For December\n",
    "db_subsetE12 = db_subsetE[db_subsetE['Month'] == 12]\n",
    "\n",
    "db_subsetE12 = db_subsetE12.reset_index(drop=True)\n",
    "\n",
    "#get inspector names\n",
    "\n",
    "i_names = []\n",
    "\n",
    "for i in range(0, len(db_subsetE12)):\n",
    "    i_names.append(db_subsetE12[\"INSPECTOR NAME\"][i])\n",
    "\n",
    "i_names = set(i_names)\n",
    "i_names = list(i_names)\n",
    "\n",
    "print i_names\n",
    "\n",
    "\n",
    "\n",
    "for i in range(0, len(i_names)):\n",
    "    #get the dataframe for each inspector\n",
    "    df2 =  db_subsetE12[db_subsetE12['INSPECTOR NAME'] == i_names[i]]\n",
    "    \n",
    "    df2 = df2.reset_index(drop=True)\n",
    "    \n",
    "    #total unique days worked\n",
    "    total_days_work = float(len(df2['INSPECTOR NAME'].groupby(df2['DATE_INSPECTION']) ) )\n",
    "    print total_days_work\n",
    "    \n",
    "    df2['DATE_INSPECTION'] = df2['DATE_INSPECTION'].astype(str)\n",
    "    \n",
    "    #get list of dates\n",
    "    df2ListDate = df2['DATE_INSPECTION'].tolist()\n",
    "    df2ListDate = set(df2ListDate)\n",
    "    df2ListDate = list(df2ListDate)\n",
    "    \n",
    "    print df2ListDate\n",
    "    \n",
    "    \n",
    "    total_visits = 0.0\n",
    "    \n",
    "    for j in range(0, len(df2ListDate)):\n",
    "            #print df2[\"INSPECTOR NAME\"][j] + \" \" + str(df2['DATE_INSPECTION'][j] )\n",
    "            num_ins_a_day =  len( df2[df2['DATE_INSPECTION'] == df2ListDate[j]] )\n",
    "            total_visits = total_visits + num_ins_a_day\n",
    "    \n",
    "    #print \"length of df2\", len(df2)\n",
    "    #print \"i \", i\n",
    "    date = str(df2[\"Month\"][0]) + \"-\" + str(df2[\"Year\"][0])\n",
    "    #date = '12-2016'\n",
    "    qn3.loc[i] = [date, df2[\"Inspector Dept\"][0] , i_names[i], total_days_work, total_visits, total_visits/total_days_work]\n",
    "\n",
    "\n",
    "    \n",
    "#QOL ENFORCEMENT JANUARY\n",
    "qn4 = pd.DataFrame(columns=('Date', 'Inspector Dept', 'INSPECTOR NAME', 'Total Days Worked', 'Total Visits', 'Avg Visits Per Day'))\n",
    "\n",
    "\n",
    "#subset for electrical enforcement\n",
    "db_subsetE = db_subset[db_subset['Inspector Dept'] == 'QOL']\n",
    "\n",
    "db_subsetE = db_subsetE.reset_index(drop=True)\n",
    "\n",
    "#For December\n",
    "db_subsetE12 = db_subsetE[db_subsetE['Month'] == 1]\n",
    "\n",
    "db_subsetE12 = db_subsetE12.reset_index(drop=True)\n",
    "\n",
    "#get inspector names\n",
    "\n",
    "i_names = []\n",
    "\n",
    "for i in range(0, len(db_subsetE12)):\n",
    "    i_names.append(db_subsetE12[\"INSPECTOR NAME\"][i])\n",
    "\n",
    "i_names = set(i_names)\n",
    "i_names = list(i_names)\n",
    "\n",
    "print i_names\n",
    "\n",
    "\n",
    "\n",
    "for i in range(0, len(i_names)):\n",
    "    #get the dataframe for each inspector\n",
    "    df2 =  db_subsetE12[db_subsetE12['INSPECTOR NAME'] == i_names[i]]\n",
    "    \n",
    "    df2 = df2.reset_index(drop=True)\n",
    "    \n",
    "    #total unique days worked\n",
    "    total_days_work = float(len(df2['INSPECTOR NAME'].groupby(df2['DATE_INSPECTION']) ) )\n",
    "    print total_days_work\n",
    "    \n",
    "    df2['DATE_INSPECTION'] = df2['DATE_INSPECTION'].astype(str)\n",
    "    \n",
    "    #get list of dates\n",
    "    df2ListDate = df2['DATE_INSPECTION'].tolist()\n",
    "    df2ListDate = set(df2ListDate)\n",
    "    df2ListDate = list(df2ListDate)\n",
    "    \n",
    "    print df2ListDate\n",
    "    \n",
    "    \n",
    "    total_visits = 0.0\n",
    "    \n",
    "    for j in range(0, len(df2ListDate)):\n",
    "            #print df2[\"INSPECTOR NAME\"][j] + \" \" + str(df2['DATE_INSPECTION'][j] )\n",
    "            num_ins_a_day =  len( df2[df2['DATE_INSPECTION'] == df2ListDate[j]] )\n",
    "            total_visits = total_visits + num_ins_a_day\n",
    "        \n",
    "    date = str(df2[\"Month\"][0]) + \"-\" + str(df2[\"Year\"][0])\n",
    "    qn4.loc[i] = [date, df2[\"Inspector Dept\"][0] , i_names[i], total_days_work, total_visits, total_visits/total_days_work]\n",
    "\n",
    "    \n",
    "\n",
    "frames = [result, qn3, qn4]\n",
    "\n",
    "result_all = pd.concat(frames)\n",
    "result_all = result_all.reset_index(drop=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "result_all.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\IR Inspector Productivity (Combined by Inspector)\\\\Dev and Enforcement Inspections Dumbbell.csv\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 3: Development Service Levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfs = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Development Service Levels\\\\Records Export for MMR_CPR Wait Time Calculations. JAN 2017.csv\")\n",
    "\n",
    "print \"length of data frame before drop\", len(dfs)\n",
    "\n",
    "dfs['REQUESTEDDATEOFCURRENT'] = pd.to_datetime(dfs['REQUESTEDDATEOFCURRENT'])\n",
    "dfs['REQUESTEDDATEORIGINAL'] = pd.to_datetime(dfs['REQUESTEDDATEORIGINAL'])\n",
    "dfs['INSPECTIONSCHEDULED'] = pd.to_datetime(dfs['INSPECTIONSCHEDULED'])\n",
    "dfs['INSPECTIONCONDUCTED'] = pd.to_datetime(dfs['INSPECTIONCONDUCTED'])\n",
    "\n",
    "dfs[\"Diff\"] =  \"\"\n",
    "for i in range(0, len(dfs)):\n",
    "    d = dfs[\"REQUESTEDDATEOFCURRENT\"][i] - dfs[\"REQUESTEDDATEORIGINAL\"][i]\n",
    "    diff = d.days\n",
    "    diff = int(diff)\n",
    "    dfs[\"Diff\"][i] = diff\n",
    "    \n",
    "dfs[\"Wait Time\"] =  \"\"\n",
    "for i in range(0, len(dfs)):\n",
    "    d = dfs[\"INSPECTIONSCHEDULED\"][i] - dfs[\"REQUESTEDDATEORIGINAL\"][i]\n",
    "    diff = d.days\n",
    "    diff = int(diff)\n",
    "    dfs[\"Wait Time\"][i] = diff*0.68\n",
    "    \n",
    "dfs = dfs[dfs[\"Wait Time\"] >= 0]\n",
    "#dfs = dfs.reset_index(drop=True)\n",
    "\n",
    "\n",
    "\n",
    "print \"length of data frame after drop\", len(dfs)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfs.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Development Service Levels\\\\Records Export for MMR_CPR Wait Time Calculations.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 4 B Complaint Response Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfbc = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\B Complaint Response Time\\\\Average Days to Respond to B Complaints.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfbc = dfbc.rename(columns={'D_DATE': 'INSPECTION_DATE', 'D_DATE.1': 'ENTERED_DATE'})\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfbc['ENTERED_DATE'] = pd.to_datetime(dfbc['ENTERED_DATE'])\n",
    "dfbc['INSPECTION_DATE'] = pd.to_datetime(dfbc['INSPECTION_DATE'])\n",
    "\n",
    "count=0\n",
    "dfbc[\"Work Days\"] =  \"\"\n",
    "for i in range(0, len(dfbc)):\n",
    "    d = dfbc[\"INSPECTION_DATE\"][i] - dfbc[\"ENTERED_DATE\"][i] \n",
    "    diff = d.days\n",
    "    diff = int(diff)\n",
    "    dfbc[\"Work Days\"][i] = diff*0.68\n",
    "    \n",
    "    if diff<0:\n",
    "        count+=1\n",
    "    #    dfbc[\"Work Days\"][i] = \"\"\n",
    "        \n",
    "print \"number less than zero\", count\n",
    "print \"length of dataset\", len(dfbc)\n",
    "\n",
    "print \"drop those values less than zero\"\n",
    "\n",
    "dfbc = dfbc[dfbc[\"Work Days\"] >= 0]\n",
    "\n",
    "dfbc = dfbc.reset_index(drop=True)\n",
    "\n",
    "print \"length after drop\", len(dfbc)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print dfbc[\"ENTERED_DATE\"][22]\n",
    "print dfbc[\"INSPECTION_DATE\"][22]\n",
    "\n",
    "d = dfbc[\"ENTERED_DATE\"][22] - dfbc[\"INSPECTION_DATE\"][22]\n",
    "\n",
    "diff = d.days\n",
    "\n",
    "print diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfbc.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\B Complaint Response Time\\\\Average Days to Respond to B Complaints.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Gas Authorization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfga  = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Gas Authorization Requests\\\\Gas Authorization Requests.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print \"number of records\", len(dfga)\n",
    "\n",
    "count_denied = dfga[dfga[\"APP_STATUS\"] == \"Denied\"]\n",
    "\n",
    "print \"number denied\", len(count_denied)\n",
    "\n",
    "print \"percent\", float(len(count_denied))/len(dfga)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfga[\"APP_STATUS Denied\"] = 0\n",
    "dfga[\"APP_STATUS Approved\"] = 0\n",
    "for i in range(0, len(dfga)):\n",
    "    if \"Denied\" in dfga[\"APP_STATUS\"][i]:\n",
    "        dfga[\"APP_STATUS Denied\"][i] = 1\n",
    "    if \"Approved\" in dfga[\"APP_STATUS\"][i]:\n",
    "        dfga[\"APP_STATUS Approved\"][i] = 1    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfga.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Gas Authorization Requests\\\\Gas Authorization Requests.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfga"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plumbing Sign Offs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dso  = pd.read_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Sign Off Requests\\\\Sign Off Requests.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dso.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dso[\"Rejected\"] = 0\n",
    "dso[\"Granted\"] = 0\n",
    "\n",
    "for i in range(0, len(dso)):\n",
    "    if \"Rejected\" in dso[\"APP_STATUS\"][i]:\n",
    "        dso[\"Rejected\"][i] = 1\n",
    "    if \"Granted\" in dso[\"APP_STATUS\"][i]:\n",
    "        dso[\"Granted\"][i] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dso.to_csv(\"S:\\\\Office of Risk Management\\\\User Folders\\\\Darius Mehri\\\\Inspection Productivity\\\\Sign Off Requests\\\\Sign Off Requests.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}