examples/Chicago/Check large datasets agree.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Allow us to load `open_cp` without installing\n",
    "import sys, os.path\n",
    "sys.path.insert(0, os.path.abspath(os.path.join(\"..\", \"..\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data\n",
    "\n",
    "The data can be downloaded from https://catalog.data.gov/dataset/crimes-2001-to-present-398a4 (see the module docstring of `open_cp.sources.chicago`  See also https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2\n",
    "\n",
    "The total data sets (for all crime events 2001 onwards) give different files between these two sources.  We check that they do contain the same data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys, os, csv, lzma\n",
    "import open_cp.sources.chicago as chicago\n",
    "\n",
    "filename = os.path.join(\"..\", \"..\", \"open_cp\", \"sources\", \"chicago.csv\")\n",
    "filename1 = os.path.join(\"..\", \"..\", \"open_cp\", \"sources\", \"chicago1.csv\")\n",
    "filename_all = os.path.join(\"..\", \"..\", \"open_cp\", \"sources\", \"chicago_all.csv.xz\")\n",
    "filename_all1 = os.path.join(\"..\", \"..\", \"open_cp\", \"sources\", \"chicago_all1.csv.xz\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the total data sets agree\n",
    "\n",
    "The files `filename` and `filename1` were downloaded from, respectively, the US Gov website, and the Chicago site.  They are slightly different in size, but appear to contain the same data.  (This can be checked!)\n",
    "\n",
    "The files `filename_all` and `filename_all1` were also downloaded from, respectively, the US Gov website, and the Chicago site.  While they are the same size (uncompressed), and have the same headers, the data appears, at least naively, to be different."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID,Case Number,Date,Block,IUCR,Primary Type,Description,Location Description,Arrest,Domestic,Beat,District,Ward,Community Area,FBI Code,X Coordinate,Y Coordinate,Year,Updated On,Latitude,Longitude,Location\n",
      "\n",
      "ID,Case Number,Date,Block,IUCR,Primary Type,Description,Location Description,Arrest,Domestic,Beat,District,Ward,Community Area,FBI Code,X Coordinate,Y Coordinate,Year,Updated On,Latitude,Longitude,Location\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with lzma.open(filename_all, \"rt\") as file:\n",
    "    print(next(file))\n",
    "with lzma.open(filename_all1, \"rt\") as file:\n",
    "    print(next(file))    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4652043,HL594701,09/06/2005 12:06:44 PM,004XX E 61ST ST,1811,NARCOTICS,POSS: CANNABIS 30GMS OR LESS,OTHER,true,false,0313,003,20,42,18,1180151,1864661,2005,04/15/2016 08:55:02 AM,41.783897141,-87.61504023,\"(41.783897141, -87.61504023)\"\n",
      "\n",
      "7806704,HS617397,11/15/2010 07:05:00 PM,040XX W WILCOX ST,2024,NARCOTICS,POSS: HEROIN(WHITE),SIDEWALK,true,false,1115,011,28,26,18,1149516,1899031,2010,02/04/2016 06:33:39 AM,41.87886043,-87.726470068,\"(41.87886043, -87.726470068)\"\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with lzma.open(filename_all, \"rt\") as file:\n",
    "    next(file); print(next(file))\n",
    "with lzma.open(filename_all1, \"rt\") as file:\n",
    "    next(file); print(next(file))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compare the actual contents of the files.\n",
    "\n",
    "This is rather memory intensive, so we go to a little effort to use less RAM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# NB: These methods encode a missing geometry and (-1, -1)\n",
    "\n",
    "def yield_tuples(f):\n",
    "    for feature in chicago.generate_GeoJSON_Features(f, type=\"all\"):\n",
    "        props = feature[\"properties\"]\n",
    "        if props[\"crime\"] == \"HOMICIDE\":\n",
    "            continue\n",
    "        coords = feature[\"geometry\"]\n",
    "        if coords is None:\n",
    "            coords = (-1, -1)\n",
    "        else:\n",
    "            coords = coords[\"coordinates\"]\n",
    "        event = (props[\"case\"], props[\"crime\"], props[\"type\"], props[\"location\"],\n",
    "                 props[\"timestamp\"], props[\"address\"], coords[0], coords[1])\n",
    "        yield event\n",
    "\n",
    "def load_as_tuples(f):\n",
    "    events = list(yield_tuples(f))\n",
    "\n",
    "def load_as_dict_to_lists(f):\n",
    "    events = dict()\n",
    "    for event in yield_tuples(f):\n",
    "        case = event[0]\n",
    "        if case not in events:\n",
    "            events[case] = []\n",
    "        events[case].append(event[1:])\n",
    "    return events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def compare_one_other(file1, file2):\n",
    "    in_only1 = []\n",
    "    in_only2 = []\n",
    "    with lzma.open(file1, \"rt\") as file:\n",
    "        events = load_as_dict_to_lists(file)\n",
    "    \n",
    "    with lzma.open(file2, \"rt\") as file:\n",
    "        for event in yield_tuples(file):\n",
    "            case, e = event[0], event[1:]\n",
    "            if case not in events or e not in events[case]:\n",
    "                in_only2.append(event)\n",
    "                continue\n",
    "            events[case].remove(e)\n",
    "            if len(events[case]) == 0:\n",
    "                del events[case]\n",
    "                \n",
    "    for case, e in events.items():\n",
    "        in_only1.append( (case,) + e )\n",
    "    \n",
    "    return in_only1, in_only2    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "([], [])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compare_one_other(filename_all, filename_all1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Check that the data is encoded in the expected way\n",
    "\n",
    "\n",
    "    ID,Case Number,Date,Block,IUCR,Primary Type,Description,Location Description,Arrest,Domestic,Beat,District,Ward,Community Area,FBI Code,X Coordinate,Y Coordinate,Year,Updated On,Latitude,Longitude,Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pyproj, numpy\n",
    "proj = pyproj.Proj({'init': 'epsg:3435'}, preserve_units=True)\n",
    "\n",
    "def check_file(file):\n",
    "    reader = csv.reader(file)\n",
    "    header = next(reader)\n",
    "    assert header[15] == \"X Coordinate\"\n",
    "    assert header[16] == \"Y Coordinate\"\n",
    "    assert header[19] == \"Latitude\"\n",
    "    assert header[20] == \"Longitude\"\n",
    "    assert header[21] == \"Location\"\n",
    "    \n",
    "    for row in reader:\n",
    "        x, y = row[15], row[16]\n",
    "        lat, lon, latlon = row[19], row[20], row[21]\n",
    "        if x == \"\":\n",
    "            assert y == \"\"\n",
    "            assert lat == \"\"\n",
    "            assert lon == \"\"\n",
    "            assert latlon == \"\"\n",
    "        else:\n",
    "            assert latlon == \"(\" + lat + \", \" + lon + \")\"\n",
    "            xx, yy = proj(float(lon), float(lat))\n",
    "            assert int(x) == numpy.round(xx)\n",
    "            assert int(y) == numpy.round(yy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with lzma.open(filename_all, \"rt\") as file:\n",
    "    check_file(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with lzma.open(filename_all1, \"rt\") as file:\n",
    "    check_file(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compare the full dataset with the extract\n",
    "\n",
    "Let us compare the last 12 months data with the full dataset.\n",
    "\n",
    "There are a few differences, but they really are \"few\" compared to the size of the complete dataset.  There appears to be no pattern in the differences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with lzma.open(filename_all, \"rt\") as file:\n",
    "    all_events = load_as_dict_to_lists(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address</th>\n",
       "      <th>case</th>\n",
       "      <th>crime</th>\n",
       "      <th>geometry</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010XX N CENTRAL PARK AVE</td>\n",
       "      <td>HZ560767</td>\n",
       "      <td>OTHER OFFENSE</td>\n",
       "      <td>POINT (-87.71645415899999 41.899712716)</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>2016-12-22T02:55:00</td>\n",
       "      <td>VIOLATE ORDER OF PROTECTION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>051XX S WASHTENAW AVE</td>\n",
       "      <td>HZ561134</td>\n",
       "      <td>BATTERY</td>\n",
       "      <td>POINT (-87.691539994 41.800445234)</td>\n",
       "      <td>RESIDENTIAL YARD (FRONT/BACK)</td>\n",
       "      <td>2016-12-22T11:17:00</td>\n",
       "      <td>AGGRAVATED: OTHER FIREARM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>059XX W DIVERSEY AVE</td>\n",
       "      <td>HZ565584</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>POINT (-87.774165121 41.931166274)</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>2016-12-09T12:00:00</td>\n",
       "      <td>FINANCIAL IDENTITY THEFT $300 AND UNDER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001XX N STATE ST</td>\n",
       "      <td>HZ561772</td>\n",
       "      <td>THEFT</td>\n",
       "      <td>POINT (-87.62787669799999 41.883500187)</td>\n",
       "      <td>DEPARTMENT STORE</td>\n",
       "      <td>2016-12-22T18:50:00</td>\n",
       "      <td>RETAIL THEFT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>008XX N MICHIGAN AVE</td>\n",
       "      <td>HZ561969</td>\n",
       "      <td>THEFT</td>\n",
       "      <td>POINT (-87.624095634 41.897982937)</td>\n",
       "      <td>SMALL RETAIL STORE</td>\n",
       "      <td>2016-12-22T19:20:00</td>\n",
       "      <td>RETAIL THEFT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    address      case               crime  \\\n",
       "0  010XX N CENTRAL PARK AVE  HZ560767       OTHER OFFENSE   \n",
       "1     051XX S WASHTENAW AVE  HZ561134             BATTERY   \n",
       "2      059XX W DIVERSEY AVE  HZ565584  DECEPTIVE PRACTICE   \n",
       "3          001XX N STATE ST  HZ561772               THEFT   \n",
       "4      008XX N MICHIGAN AVE  HZ561969               THEFT   \n",
       "\n",
       "                                  geometry                       location  \\\n",
       "0  POINT (-87.71645415899999 41.899712716)                      APARTMENT   \n",
       "1       POINT (-87.691539994 41.800445234)  RESIDENTIAL YARD (FRONT/BACK)   \n",
       "2       POINT (-87.774165121 41.931166274)                      RESIDENCE   \n",
       "3  POINT (-87.62787669799999 41.883500187)               DEPARTMENT STORE   \n",
       "4       POINT (-87.624095634 41.897982937)             SMALL RETAIL STORE   \n",
       "\n",
       "             timestamp                                     type  \n",
       "0  2016-12-22T02:55:00              VIOLATE ORDER OF PROTECTION  \n",
       "1  2016-12-22T11:17:00                AGGRAVATED: OTHER FIREARM  \n",
       "2  2016-12-09T12:00:00  FINANCIAL IDENTITY THEFT $300 AND UNDER  \n",
       "3  2016-12-22T18:50:00                             RETAIL THEFT  \n",
       "4  2016-12-22T19:20:00                             RETAIL THEFT  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = chicago.load_to_geoDataFrame()\n",
    "frame.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "known_diffs = {\"JA233208\", \"JA228951\", \"JA249656\", \"JA256373\", \"JA256594\", \"JA256838\"}\n",
    "\n",
    "not_found = []\n",
    "\n",
    "for index, row in frame.iterrows():\n",
    "    if row.crime == \"HOMICIDE\":\n",
    "        continue\n",
    "    if row.case in known_diffs:\n",
    "        continue\n",
    "    if row.case not in all_events:\n",
    "        not_found.append(row.case)\n",
    "        continue\n",
    "    event = all_events[row.case]\n",
    "    if len(event) > 1:\n",
    "        print(\"Doubled, skipping:\", row.case)\n",
    "        continue\n",
    "    event = event[0]\n",
    "    assert(row.address == event[4])\n",
    "    assert(row.crime == event[0])\n",
    "    assert(row.location == event[2])\n",
    "    assert(row.timestamp == event[3])\n",
    "    assert(row.type == event[1])\n",
    "    if row.geometry is not None:\n",
    "        assert(row.geometry.coords[0][0] == event[5])\n",
    "        assert(row.geometry.coords[0][1] == event[6])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address</th>\n",
       "      <th>case</th>\n",
       "      <th>crime</th>\n",
       "      <th>geometry</th>\n",
       "      <th>location</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36555</th>\n",
       "      <td>051XX N DAMEN AVE</td>\n",
       "      <td>JA233208</td>\n",
       "      <td>KIDNAPPING</td>\n",
       "      <td>POINT (-87.679374655 41.975099794)</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>2017-04-21T07:25:00</td>\n",
       "      <td>UNLAWFUL RESTRAINT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44245</th>\n",
       "      <td>086XX S DORCHESTER AVE</td>\n",
       "      <td>JA228951</td>\n",
       "      <td>MOTOR VEHICLE THEFT</td>\n",
       "      <td>POINT (-87.590285761 41.737308941)</td>\n",
       "      <td>GAS STATION</td>\n",
       "      <td>2017-04-17T21:45:00</td>\n",
       "      <td>AUTOMOBILE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45752</th>\n",
       "      <td>051XX N KILDARE AVE</td>\n",
       "      <td>JA249656</td>\n",
       "      <td>BURGLARY</td>\n",
       "      <td>POINT (-87.735603868 41.974363533)</td>\n",
       "      <td>RESIDENCE-GARAGE</td>\n",
       "      <td>2017-05-03T09:00:00</td>\n",
       "      <td>FORCIBLE ENTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45776</th>\n",
       "      <td>010XX W 14TH ST</td>\n",
       "      <td>JA256373</td>\n",
       "      <td>MOTOR VEHICLE THEFT</td>\n",
       "      <td>None</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>2017-04-25T17:00:00</td>\n",
       "      <td>AUTOMOBILE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45903</th>\n",
       "      <td>071XX S CORNELL AVE</td>\n",
       "      <td>JA256594</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>None</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>2016-06-10T06:00:00</td>\n",
       "      <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46028</th>\n",
       "      <td>029XX N SHEFFIELD AVE</td>\n",
       "      <td>JA256838</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>None</td>\n",
       "      <td>RESTAURANT</td>\n",
       "      <td>2017-04-06T10:00:00</td>\n",
       "      <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      address      case                crime  \\\n",
       "36555       051XX N DAMEN AVE  JA233208           KIDNAPPING   \n",
       "44245  086XX S DORCHESTER AVE  JA228951  MOTOR VEHICLE THEFT   \n",
       "45752     051XX N KILDARE AVE  JA249656             BURGLARY   \n",
       "45776         010XX W 14TH ST  JA256373  MOTOR VEHICLE THEFT   \n",
       "45903     071XX S CORNELL AVE  JA256594   DECEPTIVE PRACTICE   \n",
       "46028   029XX N SHEFFIELD AVE  JA256838   DECEPTIVE PRACTICE   \n",
       "\n",
       "                                 geometry          location  \\\n",
       "36555  POINT (-87.679374655 41.975099794)          SIDEWALK   \n",
       "44245  POINT (-87.590285761 41.737308941)       GAS STATION   \n",
       "45752  POINT (-87.735603868 41.974363533)  RESIDENCE-GARAGE   \n",
       "45776                                None         RESIDENCE   \n",
       "45903                                None         APARTMENT   \n",
       "46028                                None        RESTAURANT   \n",
       "\n",
       "                 timestamp                                 type  \n",
       "36555  2017-04-21T07:25:00                   UNLAWFUL RESTRAINT  \n",
       "44245  2017-04-17T21:45:00                           AUTOMOBILE  \n",
       "45752  2017-05-03T09:00:00                       FORCIBLE ENTRY  \n",
       "45776  2017-04-25T17:00:00                           AUTOMOBILE  \n",
       "45903  2016-06-10T06:00:00  FINANCIAL IDENTITY THEFT OVER $ 300  \n",
       "46028  2017-04-06T10:00:00  FINANCIAL IDENTITY THEFT OVER $ 300  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame[frame.case.map(lambda x : x in known_diffs)]"
   ]
  },
  {
   "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}