cat-oa-by-trade.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\"\"\" Categorize CAT inspection into Trades according to keywords contained in the assessment finding text \"\"\"\n",
    "\n",
    "import login\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlalchemy as sa\n",
    "import mysql.connector\n",
    "\n",
    "try:\n",
    "    engine = sa.create_engine('mysql+mysqlconnector://{user}:{pw}@{hostname}/flbwmass'\\\n",
    "    .format(user=login.username, pw=login.password, hostname=login.mysql_host))\n",
    "except mysql.connector.Error as e:\n",
    "    print(e)\n",
    "finally:\n",
    "    engine.dispose()\n",
    "\n",
    "qry = \"select cat_mass_inspection_items.*, period, contract, wo_type from cat_mass_inspection_items left join cat_mass_inspections using (report_serial_num)\"\n",
    "df = pd.read_sql(qry, con=engine)\n",
    "df['aspect'] = np.where(df['subaspect'].isnull(), df['aspect'], df['subaspect'])\n",
    "df['item'] = df.item_description.str[:3]\n",
    "df = df.loc[(df['grade'] != 'N') & (df['aspect'] != 'JP'), ['report_serial_num', 'period', 'contract', 'wo_type', 'aspect', 'item', 'grade', 'grade_description']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "brickwork = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Bb]rick|[Yy][\\W_]*[Tt]ong'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Bb]rick')))]\n",
    "brickwork['trade_assignment'] = 'Brickwork'\n",
    "carpentry = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Dd]oor[\\W_]*[Hh]inge|[dD]oor[\\W_]*[Ll]ock|[Bb]arrel[\\W_]*[Bb]olt'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Aa]rchitrave|[Ww]ooden[\\W_]*[Dd]oor|[Tt]imb[er][er][\\W_]*[Dd]oor|[Mm]itre[|W_]*[Jj]oint')))]\n",
    "carpentry['trade_assignment'] = 'Carpentry'\n",
    "screeding = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Ss]creed(ing)*'))]\n",
    "screeding['trade_assignment'] = 'Cement Sand screeding'\n",
    "hotline = df.loc[df.grade_description.str.contains('[Hh]otline')]\n",
    "hotline['trade_assignment'] = 'Customer Services-Hotline'\n",
    "notice = df.loc[df.grade_description.str.contains('([Cc]ommencement|[Cc]ompletion)[\\W_]*[Dd]ate')]\n",
    "notice['trade_assignment'] = 'Customer Services-Notice of works'\n",
    "worker = df.loc[df.grade_description.str.contains('[Uu]niform|[Ll][Rr][Ii][Ss][\\W_]*[Cc]ard')]\n",
    "worker['trade_assignment'] = 'Customer Services-Worker'\n",
    "environment = df.loc[df.grade_description.str.contains('[Ff]loor[\\W_]*[Dd]rain')]\n",
    "environment['trade_assignment'] = 'GSM-Environment'\n",
    "housekeeping = df.loc[df.grade_description.str.contains('[Hh]ouse[\\W_]*[Kk]eep(ing)*')]\n",
    "housekeeping['trade_assignment'] = 'GSM-Housekeeping'\n",
    "procedure = df.loc[df.grade_description.str.contains('[Pp]rocedure|A[.]*S[.]*A|[Aa]ppointment')]\n",
    "procedure['trade_assignment'] = 'GSM-Procedure'\n",
    "tested_worker = df.loc[df.grade_description.str.contains('[Tt]rade')]\n",
    "tested_worker['trade_assignment'] = 'GSM-Trade tested worker'\n",
    "burgler_grille = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Gg]rille'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Gg]rille')))]\n",
    "burgler_grille['trade_assignment'] = 'Metal Works-burglar grille'\n",
    "metal_door = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Mm]etal[\\W_]*[Dd]oor|[Cc]ollapsible[\\W_]*[Gg]ate'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Mm]etal[\\W_]*[Dd]oor|[Cc]ollapsible[\\W_]*[Gg]ate')))]\n",
    "metal_door['trade_assignment'] = 'Metal Works-metal door & gate'\n",
    "window_louvre = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Ww]indow|[Ll]ouvre'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Ww]indow|[Ll]ouvre')))]\n",
    "window_louvre['trade_assignment'] = 'Metal Works-window & louvre'\n",
    "barrier = df.loc[df.grade_description.str.contains('[Bb]arrier|[Ww]arning|[Ss]ign|[Ff]ence')]\n",
    "barrier['trade_assignment'] = 'Safety-Barrier & warning sign'\n",
    "scaffolding = df.loc[df.grade_description.str.contains('[Ff]orm[\\W_]*5|[Ss]caffold(ing)*|[Ll]adder|[Mm]etal[\\W_]*[Pp]latform|[Ww]orking[\\W_]*[Pp]latform|[Bb]amboo|[Ee][Nn]131|[Gg]ondola|[Pp]lanking|[Nn]et')]\n",
    "scaffolding['trade_assignment'] = 'Safety-Scaffolding & Ladder'\n",
    "electricity = df.loc[df.grade_description.str.contains('[Ww][.]*[Rr][.]*1|[Ee]lectricity[\\W_]*[Ss]upply|[Gg]as|[Ww]ire|[Ll]oose[\\W_]*[Pp]articles|[Ss]afety[\\W_]*[Hh]elmet|[Cc]igarette')]\n",
    "electricity['trade_assignment'] = 'Safety-Temporary Electricity & PPE'\n",
    "sanitary = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Bb]asin|[Cc]istern|[Cc]loset|[Ww]ater[\\W_]*[Pp]ipe|[Ss]ink'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Bb]asin|[Cc]istern|[Cc]loset|[Ww]ater[\\W_]*[Pp]ipe|[Pp]ipe[\\W_]*[Bb]racket|[Ss]ink')))]\n",
    "sanitary['trade_assignment'] = 'Sanitary Fittings'\n",
    "fire_collar = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Ff]ire[\\W_]*[Cc]ollar'))]\n",
    "fire_collar['trade_assignment'] = 'Sanitary Fittings-Fire collar'\n",
    "bench = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('([Ss]ink|[Cc]ooking)[\\W_]*[Bb]ench'))]\n",
    "bench['trade_assignment'] = 'Sanitary Fittings-Sink & cooking bench'\n",
    "spalling = df.loc[(df.aspect == 'M') & (df.grade_description.str.contains('[Uu]nicell[\\W_]*40|[Ss]palling[\\W_]*[Rr]epair[\\W_][Mm]ortar'))]\n",
    "spalling['trade_assignment'] = 'Spalling Repair'\n",
    "finishes = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Rr]epair(ed)*[\\W_]*[Pp]atch(es)*'))]\n",
    "finishes['trade_assignment'] = 'Spalling Repair-Finishes'\n",
    "saw_cut = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Bb]reakout|[Cc]learance'))]\n",
    "saw_cut['trade_assignment'] = 'Spalling Repair-Saw cut'\n",
    "touchup = df.loc[((df.aspect == 'M') & (df.grade_description.str.contains('[Pp]aint'))) | ((df.aspect == 'W') & (df.grade_description.str.contains('[Ss]ealer|[Pp]aint')))]\n",
    "touchup['trade_assignment'] = 'Touch-up'\n",
    "finishing = df.loc[(df.aspect == 'M') & (df.grade_description.str.contains('[Cc]ement|[Tt]ile'))]\n",
    "finishing['trade_assignment'] = 'Wall & Floor Finishing'\n",
    "tile = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Tt]ile'))]\n",
    "tile['trade_assignment'] = 'Wall & Floor Finishing-Finishes'\n",
    "plastering = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Pp]lastering'))]\n",
    "plastering['trade_assignment'] = 'Wall & Floor Finishing-Plastering'\n",
    "td = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Kk]eys'))]\n",
    "td['trade_assignment'] = 'Wall & Floor Finishing-T/D, Saw Cut'\n",
    "waterproofing = df.loc[(df.aspect == 'M') & (df.grade_description.str.contains('[Ww]aterproofing[\\W_]*[Mm]aterial'))]\n",
    "waterproofing['trade_assignment'] = 'Waterproofing works'\n",
    "angle_fillet = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Aa]ngle[\\W_]*[Ff]illet|[Ss]kirting'))]\n",
    "angle_fillet['trade_assignment'] = 'Waterproofing works-Angle fillet'\n",
    "membrane = df.loc[(df.aspect == 'W') & (df.grade_description.str.contains('[Ww]aterproofing[\\W_]*[Mm]embrane'))]\n",
    "membrane['trade_assignment'] = 'Waterproofing works-Finishes'\n",
    "\n",
    "classified = pd.concat([brickwork, carpentry, screeding, hotline, notice, worker, environment, housekeeping, procedure, tested_worker, burgler_grille, metal_door, window_louvre, barrier, scaffolding, electricity, sanitary, fire_collar, bench, spalling, finishes, saw_cut, touchup, finishing, tile, plastering, td, waterproofing, angle_fillet, membrane, ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "unclassified = df.loc[df.index.difference(classified.index)].copy()\n",
    "unclassified['trade_assignment'] = 'Unclassified'\n",
    "\n",
    "df.sort_index().to_csv('./raw.csv', encoding='utf-8')\n",
    "pd.concat([classified, unclassified]).sort_index().to_csv('./output/classified-findings.csv', encoding='utf-8')"
   ]
  }
 ],
 "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}