data_clean_merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import math\n",
    "from collections import Counter\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "tqdm.pandas()\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Gently clean pothole data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_clean(fname, nrows=None, usecols=None, header=0, ftype='csv'):\n",
    "    if ftype == 'csv':\n",
    "        df = pd.read_csv('data/raw/'+fname, nrows=nrows, usecols=usecols, header=header)\n",
    "    elif ftype == 'xlsx':\n",
    "        df = pd.read_excel('data/raw/'+fname, nrows=nrows, usecols=usecols, header=header)\n",
    "    cols = df.columns\n",
    "    new_cols = []\n",
    "    for c in cols:\n",
    "        new_cols.append(c.lower().replace(' ', '_').replace(',', ''))\n",
    "    df.columns = new_cols\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes = read_clean('311_Service_Requests_-_Pot_Holes_Reported.csv')\n",
    "potholes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes = potholes[potholes.community_area.notna()]\n",
    "potholes['community_area'] = potholes.community_area.astype(int)\n",
    "potholes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes = potholes[(potholes.status == 'Completed') | (potholes.status == 'Completed - Dup')] \n",
    "potholes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes.status.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outcomes = {'pothole':['Pothole Patched', \n",
    "                       'Complete Upon Arrival',\n",
    "                       'Completed Upon Arrival',\n",
    "                       'Completed',       \n",
    "                       'Follow-on Pot Hole in Street Created',\n",
    "                       'Create Work Order',\n",
    "                       'Street Resurfaced',],\n",
    "            'no_problem':[' No Problem Found',\n",
    "                          'No Potholes Found',\n",
    "                          'No Problem Found',],\n",
    "            'transferred':['CDOT Pavement Cave-In Survey Transfer Outcome',\n",
    "                           'WM Water Management General Investigation Transfer Outcome',\n",
    "                           'CDOT Street Cut Complaints Transfer Outcome',\n",
    "                           'CDOT Pavement Buckle or Speed Hump Transfer Outcome',\n",
    "                           'GAS Peoples Gas Transfer Outcome',\n",
    "                           'CDOT Alley Grading - Unimproved Transfer Outcome',\n",
    "                           'Street Under Construction - Transfer to Inspect Public Way Construction',\n",
    "                           'WM Sewer Cave In Inspection Transfer Outcome',\n",
    "                           'WM CDOT Recommended Restoration Transfer Outcome',\n",
    "                           'CDOT Inspect Public Way Survey Transfer Outcome',\n",
    "                           'CDOT Alley Pot Hole Transfer Outcome',\n",
    "                           'CDOT Asphalt Top Off Restoration Transfer Outcome',\n",
    "                           'CDOT Electrical Operations Construction Complaints Transfer Outcome',\n",
    "                           'Referred',],\n",
    "            'bad_request':['No Such Address Found', \n",
    "                           'Area Inspected, no such address found',\n",
    "                           'No Jurisdiction',\n",
    "                           \"Owner's Responsibility\",\n",
    "                           \"Private Property - Owner's Responsibility\", \n",
    "                           'Not Within CDOT Jurisdiction',\n",
    "                           'No Survey Required',           \n",
    "                           'No Action - See Remarks in Description',\n",
    "                           'Survey Completed - No Citation Issued', \n",
    "                           'Debris Picked Up'],\n",
    "           }\n",
    "\n",
    "def simplify(value, outcomes):\n",
    "    s = None\n",
    "    for o in outcomes.keys():\n",
    "        li = outcomes[o]\n",
    "        if s is None:\n",
    "            for l in li:\n",
    "                if value == l:\n",
    "                    s = o\n",
    "                    break\n",
    "    return s\n",
    "\n",
    "potholes['outcome'] = potholes.most_recent_action.apply(simplify, outcomes=outcomes)\n",
    "potholes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes.outcome.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(potholes.shape)\n",
    "dates = pd.to_datetime(potholes.creation_date, format=\"%m/%d/%Y\")\n",
    "start = pd.datetime(year=2011, month=1, day=1)\n",
    "potholes = potholes[dates >= start]\n",
    "potholes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes = potholes[['creation_date', 'completion_date', 'service_request_number',\n",
    "                     'outcome','number_of_potholes_filled_on_block', 'street_address', \n",
    "                     #'zip', 'ward', 'police_district',\n",
    "                     'community_area', 'ssa']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_blocks(addr):\n",
    "    parts = addr.upper().split(' ')\n",
    "    try:\n",
    "        num = int(parts[0])\n",
    "        num = math.floor(num/100)*100\n",
    "        parts[0] = str(num)\n",
    "        addr = ' '.join(parts)\n",
    "        \n",
    "    except:\n",
    "        print('error with:', addr)\n",
    "        pass\n",
    "    \n",
    "    return addr\n",
    "\n",
    "potholes['street_address'] = potholes.street_address.progress_apply(make_blocks) #\n",
    "potholes['street_address'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### traffic data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = read_clean('Average_Daily_Traffic_Counts.csv')\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(sname):\n",
    "    sname = sname.upper().strip()\n",
    "    sname = sname.replace('STREET', 'ST')\n",
    "    return sname\n",
    "\n",
    "df['street'] = df.street.apply(clean)\n",
    "df = df.groupby('street')[['total_passing_vehicle_volume']].mean().round(0)#.reset_index()\n",
    "df.columns = ['traffic_volume']\n",
    "df['traffic_volume'] = df.traffic_volume.astype(int)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic = df.to_dict(orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_traffic(addr, traffic=traffic):\n",
    "    t = None\n",
    "    addr = str(addr)\n",
    "    for k in traffic.keys():\n",
    "        if k in addr:\n",
    "            t = traffic[k]['traffic_volume']\n",
    "            break\n",
    "    \n",
    "    return t\n",
    "\n",
    "potholes['avg_traffic'] = potholes.street_address.progress_apply(find_traffic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes[['avg_traffic', 'community_area']].describe().round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes.columns = ['creation_date', 'completion_date', 'request_number', 'outcome',\n",
    "       'n_potholes_on_block', 'address', 'community_area_number', 'ssa', 'avg_traffic']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "potholes.to_csv('data/clean/potholes.tsv', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Gather 311 reporting data by area units"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = read_clean('311_Service_Requests.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.sr_type != '311 INFORMATION ONLY CALL']\n",
    "#df = df[['sr_number', 'sr_type', 'owner_department', 'created_date', 'street_address',\n",
    "#         'zip_code', 'duplicate', 'community_area', 'ward',  \n",
    "#         'police_sector', 'police_district', 'police_beat', 'precinct']]\n",
    "#df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data = dict()\n",
    "groupings = ['community_area',# 'ward', 'police_sector', 'police_district', \n",
    "             #'police_beat', 'zip', 'zip_code', 'precinct', 'ssa'\n",
    "            ]\n",
    "for g in groupings:\n",
    "    tmp = pd.DataFrame()\n",
    "    tmp[g] = None\n",
    "    grouped_data[g] = tmp "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_groups(df, existing, cname, idcol='sr_number'):\n",
    "    for g in existing.keys():\n",
    "        if g in df.columns:\n",
    "            tmp = df[[g, idcol]].copy()\n",
    "            tmp[g] = pd.to_numeric(tmp[g], errors='coerse')\n",
    "            tmp = tmp.groupby(g).count()\n",
    "            tmp.columns = [cname]\n",
    "            tmp.reset_index(inplace=True)\n",
    "            existing[g] = existing[g].merge(tmp, on=g, how='outer').fillna(0).astype(int)\n",
    "    return existing\n",
    "\n",
    "grouped_data = get_groups(df, grouped_data, cname='total_311_calls', idcol='sr_number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "types = ['Graffiti Removal Request','Street Light Out Complaint', 'Weed Removal Request',\n",
    "         'Pothole in Street Complaint','Aircraft Noise Complaint',\n",
    "         'Rodent Baiting/Rat Complaint','Tree Trim Request','Sign Repair Request - All Other Signs',\n",
    "         'Alley Light Out Complaint','Traffic Signal Out Complaint',\n",
    "         'Tree Removal Request','Building Violation','Tree Debris Clean-Up Request',\n",
    "         'Snow - Uncleared Sidewalk/Bike Lane Complaint']\n",
    "\n",
    "for t in types:\n",
    "    grouped_data = get_groups(df[df.sr_type == t], grouped_data, cname=t.replace(' ', '_'), idcol='sr_number')\n",
    "grouped_data['community_area'].head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for g in grouped_data.keys():\n",
    "    print(g)\n",
    "    grouped_data[g].to_csv('data/clean/311_complaints_by_'+g+'.tsv', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(grouped_data['community_area'].community_area.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = read_clean('Census_Data_-_Selected_socioeconomic_indicators_in_Chicago__2008___2012.csv')\n",
    "df['community_area_number'].fillna(-1, inplace=True)\n",
    "df['community_area_number'] = df.community_area_number.astype(int)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ah = read_clean('Affordable_Rental_Housing_Developments.csv')\n",
    "\n",
    "tmp = ah[['community_area_number', 'units']].groupby('community_area_number')\n",
    "s = tmp.sum()\n",
    "s.columns = ['n_affordable_housing_units']\n",
    "s.reset_index(inplace=True)\n",
    "\n",
    "df = df.merge(s, on='community_area_number', how='outer')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "health = read_clean('Public_Health_Statistics-_Life_Expectancy_By_Community_Area.csv')\n",
    "health = health[['community_area_number', '2010_life_expectancy']]\n",
    "health['community_area_number'] = health.community_area_number.fillna(-1)\n",
    "df = df.merge(health, on='community_area_number', how='outer')\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "health = read_clean('Public_Health_Statistics-_Selected_public_health_indicators_by_Chicago_community_area.csv')\n",
    "health['community_area_number'] = health.community_area.fillna(-1).astype(int)\n",
    "health.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "health = health[['community_area_number', 'low_birth_weight', 'assault_(homicide)',\n",
    "                 'cancer_(all_sites)', 'firearm-related', 'infant_mortality_rate', \n",
    "                 'childhood_lead_poisoning']]\n",
    "\n",
    "df = df.merge(health, on='community_area_number', how='outer')\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints = read_clean('COPA_Cases_-_Summary.csv')\n",
    "complaints = complaints[complaints.beat.notnull()]\n",
    "complaints.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints = complaints.beat.str.split('|').values\n",
    "\n",
    "beat_list = []\n",
    "for c in complaints:\n",
    "    for b in c:\n",
    "        beat_list.append(int(b.strip()))\n",
    "        \n",
    "complaints = pd.DataFrame(Counter(beat_list).most_common(), columns=['beat', 'n_complaints'])\n",
    "complaints.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['ID', 'Date', 'Block', 'Primary Type', 'Arrest', 'Beat',\n",
    "       'District', 'Ward', 'Community Area']\n",
    "crime = read_clean('Crimes_-_2001_to_present.csv', usecols=cols)\n",
    "crime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bca = crime.groupby(['beat', 'community_area'])[['id']].count()\n",
    "bca = bca.reset_index()\n",
    "bca.columns = ['beat', 'community_area_number', 'n_bca']\n",
    "\n",
    "b = crime.groupby(['beat'])[['id']].count()\n",
    "b.columns = ['n_beat']\n",
    "b = b.reset_index()\n",
    "\n",
    "bca = bca.merge(b, on='beat', how='left')\n",
    "bca['pct_beat_in_ca'] = bca.n_bca/bca.n_beat\n",
    "bca = bca[['beat', 'community_area_number', 'pct_beat_in_ca']]\n",
    "\n",
    "bca['community_area_number'] = bca['community_area_number'].astype(int)\n",
    "\n",
    "bca.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints = complaints.merge(bca, on='beat', how='left')\n",
    "complaints = complaints.dropna(subset=['community_area_number'])\n",
    "complaints.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints = complaints[complaints.community_area_number > 0]\n",
    "tmp = pd.DataFrame([{'community_area_number': -1, \n",
    "                     'n_complaints': complaints.n_complaints.sum(), \n",
    "                     'pct_beat_in_ca':1}])\n",
    "tmp\n",
    "complaints = pd.concat([complaints, tmp])\n",
    "complaints.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "complaints['police_complaints'] = complaints.n_complaints * complaints.pct_beat_in_ca\n",
    "complaints = complaints.groupby('community_area_number')[['police_complaints']].sum().reset_index()\n",
    "complaints.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(complaints, on='community_area_number', how='outer')\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['geogkey', 'total_population',\n",
    "       'not_hispanic_or_latino_white_alone',\n",
    "       'median_age', \n",
    "       'total_housing_units', \n",
    "       'vacant_housing_units', \n",
    "       'renter_occupied']\n",
    "\n",
    "demog = read_clean('CCASF12010CMAP.xlsx', ftype='xlsx', header=1)\n",
    "demog=demog[cols]\n",
    "demog.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demog.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demog['pct_white'] = demog.not_hispanic_or_latino_white_alone / demog.total_population * 100\n",
    "demog['pct_vacant_housing'] = demog.vacant_housing_units / demog.total_housing_units * 100\n",
    "demog['pct_rental_housing'] = demog.renter_occupied / demog.total_housing_units * 100\n",
    "demog.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demog = demog[['geogkey', 'total_population','median_age','pct_white', \n",
    "              'pct_vacant_housing', 'pct_rental_housing', 'total_housing_units']]\n",
    "demog.columns = ['community_area_number', 'total_population','median_age','pct_white', \n",
    "              'pct_vacant_housing', 'pct_rental_housing', 'total_housing_units']\n",
    "demog.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(demog, on='community_area_number', how='outer')\n",
    "df['pct_affordable_housing'] = df.n_affordable_housing_units / df.total_housing_units * 100\n",
    "df['police_complaints_per_thousand'] = df.police_complaints / df.total_population * 100\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['community_area_number', 'community_area_name',\n",
    "       'percent_of_housing_crowded', 'percent_households_below_poverty',\n",
    "       'percent_aged_16+_unemployed',\n",
    "       'percent_aged_25+_without_high_school_diploma','per_capita_income_',\n",
    "       'hardship_index', '2010_life_expectancy',\n",
    "       'low_birth_weight', 'assault_(homicide)', 'cancer_(all_sites)',\n",
    "       'firearm-related', 'infant_mortality_rate', 'childhood_lead_poisoning',\n",
    "       'police_complaints_per_thousand', 'total_population', 'median_age', 'pct_white',\n",
    "       'pct_vacant_housing', 'pct_rental_housing', 'total_housing_units',\n",
    "       'pct_affordable_housing']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/clean/community_area_stats.tsv', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}