preprocess_crime_by_category.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format file 'Crime_Statistics_-_Incidents_and_rates_by_offence_category.csv'\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "crimeStats = pd.read_csv('data/Crime_Statistics_-_Incidents_and_rates_by_offence_category.csv')\n",
    "\n",
    "# Extract first geography string\n",
    "geo = crimeStats.GEOGRAPHY.str.split(',').values\n",
    "crimeStats['GEOGRAPHY2'] = [row[0] for row in geo]\n",
    "\n",
    "# Format Year\n",
    "from datetime import datetime\n",
    "crimeStats['year'] = [datetime.strptime(y, '%m/%d/%Y %I:%M:%S %p').year for y in crimeStats.YEAR]\n",
    "crimeStats.set_index(['GEOGRAPHY', 'year'], inplace=True)\n",
    "\n",
    "rates = crimeStats.pivot_table(values='RATES', index=crimeStats.index, columns='VIOLATIONS', dropna=False)\n",
    "ratesTransp = rates.values.transpose()\n",
    "rateKeys = rates.keys()\n",
    "newCrimeStats = crimeStats.pivot_table(values='INCIDENTS', index=crimeStats.index, columns='VIOLATIONS', dropna=False)\n",
    "\n",
    "for i, key in enumerate(rateKeys):\n",
    "    newCrimeStats['Rate ' + key] = ratesTransp[i]\n",
    "\n",
    "idx = newCrimeStats.index.values\n",
    "idx = np.array([np.array(row) for row in idx])\n",
    "idx = idx.transpose()\n",
    "\n",
    "newCrimeStats['geography'] = idx[0]\n",
    "newCrimeStats['year'] = idx[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "counties = pd.read_csv('data/proc/ns_counties.csv')\n",
    "newCrimeStats['CO_CODE'] = ['' for row in newCrimeStats.values]\n",
    "geo = newCrimeStats.geography.str.split(',').values\n",
    "line = -1\n",
    "\n",
    "def getCounty(name):\n",
    "    if name == 'Amherst':\n",
    "        return 'CU'\n",
    "    elif name == 'Barrington':\n",
    "        return 'SH'\n",
    "    elif name == 'Bridgewater':\n",
    "        return 'LU'\n",
    "    elif name == 'Eskasoni':\n",
    "        return 'CB'\n",
    "    elif name == 'Indian Brook':\n",
    "        return 'HN'\n",
    "    elif name == 'Kentville':\n",
    "        return 'KI'\n",
    "    elif name == 'Meteghan (Clare)':\n",
    "        return 'DI'\n",
    "    elif name == 'Millbrook':\n",
    "        return 'CO'\n",
    "    elif name == 'New Glasgow Region':\n",
    "        return 'PI'\n",
    "    elif name == 'Oxford County':\n",
    "        return 'CU'\n",
    "    elif name == 'Parrsboro':\n",
    "        return 'CU'\n",
    "    elif name == 'Port Hawkesbury':\n",
    "        return 'CB'\n",
    "    elif name == 'Springhill':\n",
    "        return 'CU'\n",
    "    elif name == 'Stellarton':\n",
    "        return 'PI'\n",
    "    elif name == 'Trenton':\n",
    "        return 'PI'\n",
    "    elif name == 'Truro':\n",
    "        return 'CO'\n",
    "    elif name == 'Westville':\n",
    "        return 'PI'\n",
    "    elif name == 'Windsor':\n",
    "        return 'HN'\n",
    "    return ''\n",
    "\n",
    "for i, row in newCrimeStats.iterrows():\n",
    "    line += 1\n",
    "    for j, county in counties.iterrows():\n",
    "        name = county['CO_DESC'].lower()\n",
    "        code = county['CO_CODE']\n",
    "\n",
    "        if name in geo[line][0].lower():\n",
    "            newCrimeStats.loc[i, 'CO_CODE'] = code\n",
    "\n",
    "line = -1\n",
    "\n",
    "for i, row in newCrimeStats.iterrows():\n",
    "    line += 1\n",
    "    if row['CO_CODE'] != '':\n",
    "        continue\n",
    "    newCrimeStats.loc[i, 'CO_CODE'] = getCounty(geo[line][0])\n",
    "\n",
    "newCrimeStats.drop(newCrimeStats[newCrimeStats['CO_CODE'] == ''].index, inplace=True)\n",
    "# newCrimeStats.loc[newCrimeStats['CO_CODE'] == '',]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "newCrimeStats = newCrimeStats.fillna(0)\n",
    "\n",
    "newCrimeStats.drop(['geography'], axis=1, inplace=True)\n",
    "newCrimeStats = newCrimeStats.groupby(['CO_CODE', 'year']).agg('sum')\n",
    "\n",
    "# Normalization\n",
    "from sklearn import preprocessing\n",
    "\n",
    "for key in newCrimeStats.keys():\n",
    "    newCrimeStats[key] = newCrimeStats[key] / newCrimeStats[key].max()\n",
    "\n",
    "idx = newCrimeStats.index.values\n",
    "idx = np.array([np.array(row) for row in idx])\n",
    "idx = idx.transpose()\n",
    "\n",
    "newCrimeStats['CO_CODE'] = idx[0]\n",
    "newCrimeStats['CO_DESC'] = [counties.loc[counties['CO_CODE'] == code, 'CO_DESC'].values[0] for code in idx[0]]\n",
    "newCrimeStats['YEAR'] = idx[1]\n",
    "\n",
    "newCrimeStats.to_csv('data/proc/crime_stats_by_offence_category.csv', index=False)"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}