la-vacant-building-complaints-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# L.A. vacant building complaints analysis\n",
    "\n",
    "By Emily Alpert Reyes and Ben Welsh\n",
    "\n",
    "A quick analysis conducted for [\"Fire officials were concerned about Westlake building where 5 died in a blaze\"](http://www.latimes.com/local/lanow/la-me-ln-westlake-fatal-fire-arrest-20160615-snap-story.html), a June 15, 2016, story by the Los Angeles Times.\n",
    "\n",
    "Lists of [open](https://data.lacity.org/A-Prosperous-City/Building-and-Safety-Customer-Service-Request/bsvt-chkv) and [closed](https://data.lacity.org/A-Prosperous-City/Building-and-Safety-Customer-Service-Request-Close/8qjh-sbs9) complaints filed with the Los Angeles Department of Building and Safety were downloaded from the city's data portal.\n",
    "\n",
    "The two files were combined into a single spreadsheet. A new column called \"Year Received\" was generated from the existing \"Date Received\" field using LibreOffice's ``YEAR()`` function. The new file was named ``combined_complaints.csv``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import agate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|----------------------------------------+------------|\n",
      "|  column                                | data_type  |\n",
      "|----------------------------------------+------------|\n",
      "|  CSR Number                            | Number     |\n",
      "|  LADBS Inspection District             | Number     |\n",
      "|  Address House Number                  | Number     |\n",
      "|  Address House Fraction Number         | Date       |\n",
      "|  Address Street Direction              | Text       |\n",
      "|  Address Street Name                   | Text       |\n",
      "|  Address Street Suffix                 | Text       |\n",
      "|  Address Street Suffix Direction       | Text       |\n",
      "|  Address Street Zip                    | Number     |\n",
      "|  Date Received                         | Date       |\n",
      "|  Year Received                         | Number     |\n",
      "|  Date Closed                           | Date       |\n",
      "|  Due Date                              | Date       |\n",
      "|  Case Flag                             | Boolean    |\n",
      "|  CSR Priority                          | Text       |\n",
      "|  GIS Parcel Identification Number(PIN) | Text       |\n",
      "|  CSR Problem Type                      | Text       |\n",
      "|  Area Planning Commission (APC)        | Text       |\n",
      "|  Case Number Related to CSR            | Number     |\n",
      "|  Response Days                         | Number     |\n",
      "|  ...                                   | ...        |\n",
      "|----------------------------------------+------------|\n",
      "\n"
     ]
    }
   ],
   "source": [
    "table = agate.Table.from_csv(\"./combined_complaints.csv\")\n",
    "print table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total complaints: 114243\n"
     ]
    }
   ],
   "source": [
    "print \"Total complaints: %s\" % len(table.rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|-------------------------------------------+--------+--------------------------------|\n",
      "|  CSR Problem Type                         |  count |                       percent  |\n",
      "|-------------------------------------------+--------+--------------------------------|\n",
      "|  ABANDONED OR VACANT BUILDING LEFT OPE... |  4,005 |  3.50568524986213597332002836  |\n",
      "|  ANY CHANGES DONE TO A PROPERTY IN AN ... |    867 |  0.75890864210498673879362412  |\n",
      "|  ANY ISSUE REGARDING RECYCLING CENTERS    |    353 |  0.30899048519384119814780774  |\n",
      "|  ANY ISSUES WITH HOTELS AND MOTELS        |    143 |  0.12517178295387901228083996  |\n",
      "|  ANY PROBLEMS THAT ONLY OCCUR FROM 5:0... |  2,428 |  2.12529432875537232040475128  |\n",
      "|  AUTOMOBILE REPAIR CONDUCTED ON RESIDE... |  1,546 |  1.35325577934753113976348660  |\n",
      "|  BLOCKED EXITS PASSAGEWAYS OR WINDOWS     |    355 |  0.31074113950088845706082648  |\n",
      "|  BUILDING OR FENCE NEEDS PAINT            |    147 |  0.12867309156797353010687745  |\n",
      "|  BUILDING OR PROPERTY CONVERTED TO ANO... |  7,434 |  6.50718205929466137969065938  |\n",
      "|  BUILDING OR WALL THAT COULD FALL DOWN    |  1,752 |  1.53357317297339880780441690  |\n",
      "|  BUILDINGS IN NEED OF GENERAL REPAIR      |  4,584 |  4.01249967175231742863895381  |\n",
      "|  BUSINESS OPERATED FROM A HOUSE OR GARAGE |  3,789 |  3.31661458470103201071400436  |\n",
      "|  COMMERCIAL JUNK YARDS                    |     32 |  0.02801046891275614260829985  |\n",
      "|  CONSTRUCTION DONE WITHOUT PERMITS OR ... | 13,402 | 11.73113451152368197613858179  |\n",
      "|  CONSTRUCTION IN PROGRESS WITHOUT PERM... | 12,978 | 11.35999579842966308657860876  |\n",
      "|  CUB OR CUX VIOLATIONS                    |     18 |  0.01575588876342533021716867  |\n",
      "|  DISABLED ACCESS ISSUES                   |    151 |  0.13217440018206804793291493  |\n",
      "|  EARTHQUAKE DAMAGE                        |      1 |  0.00087532715352362945650937  |\n",
      "|  EMERGENCY ASSESSMENT                     |      5 |  0.00437663576761814728254685  |\n",
      "|  FENCES WALLS AND HEDGES THAT ARE TOO ... |  2,974 |  2.60322295457927400365886750  |\n",
      "|  GARAGE CONVERTED TO A DWELLING OR ANY... |  8,529 |  7.46566529240303563456841995  |\n",
      "|  GATE OR FENCE AROUND POOL MISSING OR ... |    170 |  0.14880561609901700760659296  |\n",
      "|  GRAFFITI ON THE BUILDING, FENCES OR W... |    651 |  0.56983797694388277618760012  |\n",
      "|  IRON BARS OVER WINDOWS OR DOORS          |     59 |  0.05164430205789413793405285  |\n",
      "|  ISSUES REGARDING ADULT ENTERTAINMENT ... |    107 |  0.09366000542702835184650263  |\n",
      "|  ISSUES REGARDING AUTOMOBILE REPAIR BU... |  1,487 |  1.30161147728963700182943375  |\n",
      "|  ISSUES REGARDING CARGO CONTAINER STOR... |     38 |  0.03326243183389791934735607  |\n",
      "|  ISSUES REGARDING TENNIS COURT OR FLOO... |    366 |  0.32036973818964838108242956  |\n",
      "|  ISSUES REGARDING USED CAR LOTS.          |     69 |  0.06039757359313043249914656  |\n",
      "|  ISSUES REGARDING USED EQUIPMENT OPEN ... |     61 |  0.05339495636494139684707159  |\n",
      "|  LOCATIONS HAVING YARD SALES ALL THE TIME |    787 |  0.68888246982309638227287449  |\n",
      "|  MEDICAL MARIJUANA DISPENSARY             |     23 |  0.02013252453104347749971552  |\n",
      "|  MISCELLANEOUS COMPLAINTS                 | 18,707 | 16.37474506096653624292079165  |\n",
      "|  NUISANCE ABATEMENT REVOCATION GROUP ONLY |      1 |  0.00087532715352362945650937  |\n",
      "|  OPEN EXCAVATIONS ACCESSIBLE TO THE PU... |    174 |  0.15230692471311152543263045  |\n",
      "|  OPEN STORAGE OF VEHICLES THAT CANNOT ... |  1,415 |  1.23858792223593568096075908  |\n",
      "|  OUTDOOR ADVERTISEMENTS (SIGNS) FOR GO... |    705 |  0.61710564323415876683910612  |\n",
      "|  OUTDOOR ADVERTISMENTS (SIGNS) OF GOOD... |  3,826 |  3.34900168938140630060485106  |\n",
      "|  OVERGROWN OR EXCESSIVE VEGETATION        |  6,154 |  5.38676330278441567535866530  |\n",
      "|  PARKING AUTOMOBILES IN THE FRONT YARD... |  1,414 |  1.23771259508241205150424971  |\n",
      "|  PARKING SPACES BLOCKED OR REMOVED FRO... |    462 |  0.40440114492791680890732911  |\n",
      "|  PEOPLE SELLING PRODUCTS OUTSIDE OF AN... |    303 |  0.26522412751765972532233922  |\n",
      "|  POOL EQUIPMENT OR AIR CONDITIONER NOI... |  1,419 |  1.24208923085003019878679656  |\n",
      "|  POOL WATER NOT KEPT CLEAN                |  1,919 |  1.67975280761184492704148175  |\n",
      "|  PRIVATE SEWAGE DISPOSAL SYSTEMS          |    175 |  0.15318225186663515488913982  |\n",
      "|  STORAGE OF ITEMS OUTDOORS                |    818 |  0.71601761158232889542466497  |\n",
      "|  STORM DAMAGE                             |     11 |  0.00962859868875992402160307  |\n",
      "|  TOO MANY ITEMS STORED ON PROPERTY (JUNK) |  2,426 |  2.12354367444832506149173254  |\n",
      "|  TRASH OR DEBRIS ON PRIVATE PROPERTY      |  4,338 |  3.79716919198550458233764870  |\n",
      "|  VACANT LOT WITH TRASH AND DEBRIS         |    665 |  0.58209255709321358857873130  |\n",
      "|-------------------------------------------+--------+--------------------------------|\n"
     ]
    }
   ],
   "source": [
    "by_type = table.group_by(\"CSR Problem Type\")\n",
    "by_type = by_type.aggregate([('count', agate.Count())])\n",
    "by_type = by_type.compute([('percent', agate.Percent(\"count\"))])\n",
    "by_type.order_by(\"CSR Problem Type\").print_table(max_column_width=40, max_rows=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of vacant complaints: 4005\n"
     ]
    }
   ],
   "source": [
    "vacants = table.where(\n",
    "    lambda row: row['CSR Problem Type'] == 'ABANDONED OR VACANT BUILDING LEFT OPEN TO THE PUBLIC'\n",
    ")\n",
    "print \"Total number of vacant complaints: %s\" % len(vacants.rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|---------------------------------+-------+--------------------------------|\n",
      "|  Area Planning Commission (APC) | count |                       percent  |\n",
      "|---------------------------------+-------+--------------------------------|\n",
      "|  Central                        |   511 | 12.75905118601747815230961298  |\n",
      "|  East Los Angeles               |   428 | 10.68664169787765293383270911  |\n",
      "|  Harbor                         |   192 |  4.79400749063670411985018727  |\n",
      "|  North Valley                   |   579 | 14.45692883895131086142322097  |\n",
      "|  South Los Angeles              | 1,266 | 31.61048689138576779026217228  |\n",
      "|  South Valley                   |   784 | 19.57553058676654182272159800  |\n",
      "|  West Los Angeles               |   242 |  6.04244694132334581772784020  |\n",
      "|                                 |     3 |  0.07490636704119850187265918  |\n",
      "|---------------------------------+-------+--------------------------------|\n"
     ]
    }
   ],
   "source": [
    "vacants_by_area = vacants.group_by(\"Area Planning Commission (APC)\")\n",
    "vacants_by_area = vacants_by_area.aggregate([('count', agate.Count())])\n",
    "vacants_by_area = vacants_by_area.compute([('percent', agate.Percent(\"count\"))])\n",
    "vacants_by_area.order_by(\"Area Planning Commission (APC)\").print_table(max_column_width=40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|----------------+-------+--------------------------------|\n",
      "|  Year Received | count |                       percent  |\n",
      "|----------------+-------+--------------------------------|\n",
      "|          2,011 |   775 | 19.35081148564294631710362047  |\n",
      "|          2,012 |   712 | 17.77777777777777777777777778  |\n",
      "|          2,013 |   604 | 15.08114856429463171036204744  |\n",
      "|          2,014 |   625 | 15.60549313358302122347066167  |\n",
      "|          2,015 |   841 | 20.99875156054931335830212235  |\n",
      "|          2,016 |   448 | 11.18601747815230961298377029  |\n",
      "|----------------+-------+--------------------------------|\n"
     ]
    }
   ],
   "source": [
    "vacants_by_year = vacants.group_by(\"Year Received\")\n",
    "vacants_by_year = vacants_by_year.aggregate([('count', agate.Count())])\n",
    "vacants_by_year = vacants_by_year.compute([('percent', agate.Percent(\"count\"))])\n",
    "vacants_by_year.order_by(\"Year Received\").print_table(max_column_width=40)"
   ]
  }
 ],
 "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
}