cleaning_stl_crime_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This is currently a work in progress.  Feel free to add to it as it progresses along!!!\n",
    "\n",
    "## This notebook is a gradual work in progress to make this data set more complete and easier for people to import and use for their own data analysis with minimal data cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read in the raw data files and combine into one data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "file_list = os.listdir('raw_data/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "d = pd.concat([pd.read_csv('raw_data/' + f, encoding = 'latin1') for f in file_list], keys=file_list, names = ['FileName','RowNumber'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = d.reset_index().drop('RowNumber', axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load in neighborhood lookup table and join it with the data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Load in neighborhood lookup table\n",
    "nbhds = pd.read_csv('neighborhood_lookup.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2 = pd.merge(df, nbhds, left_on = 'Neighborhood', right_on = 'Neighborhood Number', how = 'left')\n",
    "df2.drop('Neighborhood Number', axis = 1, inplace = True) # Drop duplicate nbhd number column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fixing column names to something more consistent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col_names = list(df2.columns)\n",
    "df2.columns = [name.replace(' ', '') for name in col_names]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df2.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Here we're trying to align the crime codes in the Crime field to the Uniform Crime Reporting codes.\n",
    "First we need to pad out the Crime column to be six digits, since the leading zero was trimmed in the .csv files\n",
    "\n",
    "*Need to figure out the numbering system for these crimes.  See UCR, NIBRS, NCIC crime codes and try to make sense of it.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2['Crime'] = df2['Crime'].astype(str)\n",
    "df2['Crime'] = df2['Crime'].apply(lambda x: x.zfill(6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2['ShortCrimeCode'] = df2['Crime'].apply(lambda x: x[:2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "Bring in UCR CSV file here and merge df2 and it together on 'ShortenedCrime' column\n",
    "'''\n",
    "ucr_codes = pd.read_csv('UCRCodes.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ucr_codes['UCRCode'] = ucr_codes['UCRCode'].astype(str).apply(lambda x: x.zfill(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3 = pd.merge(df2, ucr_codes, left_on='ShortCrimeCode', right_on='UCRCode', how = 'left')\n",
    "df3.drop('UCRCode', axis = 1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working to fill in missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The below code merges two disjoint columns that were renamed at some point.  The merged columns are:\n",
    "### DateOccured/DateOccur\n",
    "### CodedMonth/MonthReportedtoMSHP\n",
    "### FlagUnfounded/UnfoundedCrimeIndicator\n",
    "### FlagAdministrative/AdministrativeAdjustmentIndicator\n",
    "### FlagCrime/NewCrimeIndicator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3['DateOccured'] = pd.concat([df3['DateOccur'].dropna(), df3['DateOccured'].dropna()]).reindex_like(df3)\n",
    "df3['CodedMonth'] = pd.concat([df3['CodedMonth'].dropna(), df3['MonthReportedtoMSHP'].dropna()]).reindex_like(df3)\n",
    "df3['FlagUnfounded'] = pd.concat([df3['FlagUnfounded'].dropna(), df3['UnfoundedCrimeIndicator'].dropna()]).reindex_like(df3)\n",
    "df3['FlagAdministrative'] = pd.concat([df3['FlagAdministrative'].dropna(), df3['AdministrativeAdjustmentIndicator'].dropna()]).reindex_like(df3)\n",
    "df3['FlagCrime'] = pd.concat([df3['FlagCrime'].dropna(), df3['NewCrimeIndicator'].dropna()]).reindex_like(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Now that columns are merged, cleanup missing values appropriately\n",
    "df3['FlagAdministrative'] = df3['FlagAdministrative'].replace(' ', 'N')\n",
    "\n",
    "df3['FlagCrime'] = df3['FlagCrime'].replace(' ', 'N')\n",
    "\n",
    "# This column actually contains no 'Y' flags.  I suspect it's just a Flag column that has yet to be used.\n",
    "df3['FlagCleanup'] = df3['FlagCleanup'].replace(' ', 'N')\n",
    "df3['FlagCleanup'] = df3['FlagCleanup'].replace(pd.np.NaN, 'N')\n",
    "\n",
    "df3['FlagUnfounded'] = df3['FlagUnfounded'].replace(' ', 'N')\n",
    "\n",
    "# Filling out some other fields with NaN's appropriately\n",
    "df3['LocationName'] = df3['LocationName'].replace(' ', pd.np.NaN)\n",
    "df3['LocationComment'] = df3['LocationComment'].replace(' ', pd.np.NaN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Let's change the X/Y to WGS84 lat/lng"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import pyproj to use Proj and transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pyproj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define the convert function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "state_plane = pyproj.Proj(init='EPSG:26996', preserve_units=True) #East MO State Plane Coord\n",
    "wgs = pyproj.Proj(proj='latlong', datum='WGS84', ellps='WGS84') #Want WGS84\n",
    "\n",
    "def convert(x,y):\n",
    "    x *= 0.3048  #Change to meters\n",
    "    y *= 0.3048  #Change to meters\n",
    "    lng, lat = pyproj.transform(state_plane, wgs, x, y) #Get lng/lat\n",
    "    return lat,lng"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Apply the convert function to the X/Y Coord"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a = df3['XCoord'].astype(object).combine(df3['YCoord'], func=convert)\n",
    "y = pd.DataFrame(a,columns = ['LatLong'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adds a Latitude and Longitude column to df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lista = [float(str(item).rstrip(\")\").split(',')[1]) for item in y['LatLong']]\n",
    "listb = [float(str(item).lstrip(\"()\").split(',')[0]) for item in y['LatLong']]\n",
    "df3['Latitude'] = listb\n",
    "df3['Longitude'] = lista"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write the file out to tab delimited file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "keep_columns = [u'FileName', u'CADAddress', u'CADStreet', u'CodedMonth', u'Complaint', u'Count', u'Crime', u'ShortCrimeCode', u'UCRType', u'UCRCrime', u'DateOccured', u'Description', u'District', u'FlagAdministrative', u'FlagCleanup', u'FlagCrime', u'FlagUnfounded', u'ILEADSAddress', u'ILEADSStreet', u'LocationComment', u'LocationName', u'Neighborhood', u'NeighborhoodName', u'NeighborhoodPrimaryDistrict', u'NeighborhoodAddlDistrict', u'Latitude', u'Longitude']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df4 = df3[keep_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df4.to_csv('clean_data/stl_crime_data.txt', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}