{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "

\"FT

\n", "

Frogtown DataLoad Workbook, 05/06/18

\n", "

By Frogtown Crusader (Abu Nayeem)

" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This is just clean coded process going over the uploading steps assuming all the preliminary steps after decoder are made\n", "\n", "\n", "## Table of contents \n", "* [Data Setup](#setup)\n", "* [Create Variables](#create)\n", "* [Intersection Table: Preparation](#intersection)\n", "* [Address Table: Preparation](#address)\n", "* [Preliminary Data Cleaning](#pre)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Data Setup " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create New Variables " ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n" ] }, { "ename": "Exception", "evalue": "Unknown response format: text/html", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mException\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[0;32m 27\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 28\u001b[0m \u001b[1;31m#Easier to bulk upload\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 29\u001b[1;33m \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mclient\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"gppb-g9cg\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlimit\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1000000\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 30\u001b[0m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfrom_records\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresults\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 31\u001b[0m \u001b[1;31m# Find Max Date Value\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sodapy\\__init__.py\u001b[0m in \u001b[0;36mget\u001b[1;34m(self, dataset_identifier, content_type, **kwargs)\u001b[0m\n\u001b[0;32m 360\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 361\u001b[0m response = self._perform_request(\"get\", resource, headers=headers,\n\u001b[1;32m--> 362\u001b[1;33m params=params)\n\u001b[0m\u001b[0;32m 363\u001b[0m \u001b[1;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 364\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sodapy\\__init__.py\u001b[0m in \u001b[0;36m_perform_request\u001b[1;34m(self, request_type, resource, **kwargs)\u001b[0m\n\u001b[0;32m 498\u001b[0m \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 499\u001b[0m raise Exception(\"Unknown response format: {0}\"\n\u001b[1;32m--> 500\u001b[1;33m .format(content_type))\n\u001b[0m\u001b[0;32m 501\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 502\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[1;31mException\u001b[0m: Unknown response format: text/html" ] } ], "source": [ "import pandas as pd\n", "import numpy as np\n", "import warnings\n", "warnings.filterwarnings('ignore')\n", "import matplotlib as mpl\n", "import matplotlib.pyplot as plt\n", "%matplotlib inline \n", "from IPython.display import HTML\n", "from IPython.display import display\n", "import requests # library to handle requests\n", "import json\n", "from urllib.request import urlopen\n", "\n", "\n", "#Load Data\n", "#df_crime = pd.read_csv('Datasets/Crime_Incident_Report.csv')\n", "\n", "#rename columns\n", "#cols= ['Case','Date','Time','Code','IncType','Incident','Grid','NNum','Neighborhood','Block','CallDispCode','CallDisposition', 'Count']\n", "#df_crime.columns= cols\n", "\n", "#selection for Frogtown and nearby area\n", "#df=df_crime.query('Grid in [66,67, 68, 86, 87,88,89, 90, 91, 92,106,107,108,109,110]')\n", "\n", "#Socarata upload Method\n", "from sodapy import Socrata\n", "\n", "#New Upload Method Get Information from Socrata API\n", "client = Socrata(\"information.stpaul.gov\", None)\n", "\n", "#Easier to bulk upload\n", "results = client.get(\"gppb-g9cg\", limit=1000000)\n", "df = pd.DataFrame.from_records(results)\n", "# Find Max Date Value\n", "results = client.get(\"gppb-g9cg\", limit=1)\n", "r_max = pd.DataFrame.from_records(results)\n", "\n", "\n", "#Load from API\n", "json_file = \"https://services1.arcgis.com/9meaaHE3uiba0zr8/arcgis/rest/services/Crime_Incident_Report_-_Dataset/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json\"\n", "data = urlopen(json_file).read()\n", "raw_json = json.loads(data)\n", "formatted_json = [feature['attributes'] for feature in raw_json['features']]\n", "\n", "df = pd.DataFrame(formatted_json)\n", "\n", "#change order\n", "p=['longitude','latitude', 'apn','category','status','licenseNumber','milestone','tier','issueDate', 'expirationDate', 'address', 'ownerName', 'ownerAddress1', \n", " 'ownerAddress2', 'ownerPhone', 'ownerEmail', 'applicantName', 'applicantAddress1', 'applicantPhone', 'applicantEmail', 'licensedUnits'] \n", "ren=ef[p]\n", "cols= ['longitude','latitude', 'TID','category','status','licenseNumber','milestone','tier','issueDate', 'expirationDate', 'address', 'ownerName', 'ownerAddress1',\n", " 'ownerAddress2', 'ownerPhone', 'ownerEmail', 'applicantName', 'applicantAddress1', 'applicantPhone', 'applicantEmail', 'licensedUnits']\n", "ren.columns= cols\n", "\n", "\n", "#rename columns [Note the order of Columns have changed]\n", "cols= ['Block','CallDispCode','CallDisposition','Case','Code', 'Count','Date','Grid','Incident','IncType','Neighborhood','NNum','Time']\n", "df.columns= cols\n", "df=df.dropna()\n", "df = df.astype({\"Case\": int, \"Code\": int, \"Grid\":float, \"NNum\":int,\"Count\":int})\n", "#select respective Grids of interest\n", "df=df.query(\"Grid in (66,67, 68, 86, 87,88,89, 90, 91, 92,106,107,108,109,110)\")\n", "\n", "df.head(4)" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BLOCKCALL_DISPOSITIONCALL_DISPOSITION_CODECODECount_DATEINCIDENTINCIDENT_TYPENEIGHBORHOOD_NAMENEIGHBORHOOD_NUMBERPOLICE_GRID_NUMBERTIME
061X UNIVERSITY AV WGone on ArrivalG60011408249140000TheftTheft, Except Auto Theft8 - Summit/University8109None
1FARRINGTON ST & TOPPINGReport WrittenRR71111408159800000Auto TheftMotor Vehicle Theft, Trucks and Buses6 - North End670None
2212X NORTONIA AVAdvisedA64011408130280000TheftTheft, From Auto2 - Greater East Side280None
3FRONT AV & WESTERNReport WrittenRR141011408248120000VandalismCriminal Damage to Property (Misdemeanor, Unde...6 - North End650None
4127X MONTREAL AVReport WrittenRR51511408248000000BurglaryBurglary, Forced Entry, Night, Commercial15 - Highland15225None
591X MARION STGone on ArrivalG180011408247280000NarcoticsNarcotics6 - North End670None
6202X 4 ST EReport WrittenRR86111408246200000Simple Asasult Dom.Assault, Domestic, Opposite Sex1 - Conway/Battlecreek/Highwood1100None
798X PAYNE AVAdvisedA60011408245660000TheftTheft, Except Auto Theft5 - Payne/Phalen554None
832X COOK AV EReport WrittenRR86211408245480000Simple Asasult Dom.Assault, Domestic, Family/Child5 - Payne/Phalen553None
944X LAFOND AVGone on ArrivalG180011408244640000NarcoticsNarcotics7 - Thomas/Dale(Frogtown)789None
10UNIVERSITY AV W & VICTORIAReport WrittenRR31411408244520000RobberyRobbery, Highway, By Strong Arm7 - Thomas/Dale(Frogtown)788None
11GRAND AV & OXFORDReport WrittenRR69111408244400000TheftTheft, All Other, Under $50016 - Summit Hill16147None
1294X MINNEHAHA AV EReport WrittenRR55311408243260000BurglaryAtt. Burglary, Forced Entry, Night, Garage4 - Dayton's Bluff495None
13212X OLDHUDSON RDAdvisedA60011408050240000TheftTheft, Except Auto Theft1 - Conway/Battlecreek/Highwood1120None
14184X OLDHUDSON RDGone on ArrivalG180011408239420000NarcoticsNarcotics1 - Conway/Battlecreek/Highwood1119None
15205X DAYTON AVReport WrittenRR143011408237200000VandalismCriminal Damage to Property (Felony, Over $500)13 - Union Park13123None
1642X RICE STReport WrittenRR63111408057440000TheftTheft, Shoplifting, Under $50017 - Capitol River17110None
17121X PACIFIC STReport WrittenRR42211408259160000Agg. Assault Dom.Aggravated Assault, Domestic4 - Dayton's Bluff4116None
18215X WILSON AVReport WrittenRR53311408381200000BurglaryBurglary, Forced Entry, Day, Garage1 - Conway/Battlecreek/Highwood1120None
19127X SARGENT AVAdvisedA64011408296600000TheftTheft, From Auto14 - Macalester-Groveland14146None
20107X MINNEHAHA AV WAdvisedA140111408021800000GraffitiGraffiti7 - Thomas/Dale(Frogtown)787None
2118X WAYZATA STAdvisedA70011408116180000Auto TheftMotor Vehicle Theft6 - North End670None
2247X HAZEL ST NAdvisedA60011408415760000TheftTheft, Except Auto Theft1 - Conway/Battlecreek/Highwood1119None
23142X PARK STReport WrittenRR45111408175100000Agg. Assault Dom.Aggravated Assault, Domestic6 - North End631None
2432X COOK AV EAdvisedA180011408070220000NarcoticsNarcotics5 - Payne/Phalen553None
25115X COMO AVReport WrittenRR141011408159800000VandalismCriminal Damage to Property (Misdemeanor, Unde...10 - Como1046None
26131X PAYNE AVGone on ArrivalG64011408127160000TheftTheft, From Auto5 - Payne/Phalen534None
2788X CARROLL AVReport WrittenRR71011408302000000Auto TheftMotor Vehicle Theft, Automobile8 - Summit/University8107None
28211X ROBLYN AVAdvisedA60011408021500000TheftTheft, Except Auto Theft13 - Union Park13102None
29158X RANDOLPH AVReport WrittenRR69211408136280000TheftTheft, All Other, $501 to $100014 - Macalester-Groveland14164None
.......................................
97069X OHIO STReport WrittenRR69111409258340000TheftTheft, All Other, Under $5003 - West Side3212None
97162X RICE STAdvisedA60011409211900000TheftTheft, Except Auto Theft7 - Thomas/Dale(Frogtown)790None
97224X 7 ST WAdvisedA60011409183460000TheftTheft, Except Auto Theft9 - West Seventh9151None
973CYPRESS ST & PACIFICReport WrittenRR69211408981680000TheftTheft, All Other, $501 to $10004 - Dayton's Bluff4115None
974138X MINNEHAHA AV EReport WrittenRR51311408856400000BurglaryBurglary, Forced Entry, Night, Garage2 - Greater East Side277None
97572X MARYLAND AV EAdvisedA50011409211780000BurglaryBurglary5 - Payne/Phalen554None
976UNIVERSITY AV W & VICTORIAAdvisedA60011409271600000TheftTheft, Except Auto Theft7 - Thomas/Dale(Frogtown)788None
977184X OLDHUDSON RDReport WrittenRR51111408950120000BurglaryBurglary, Forced Entry, Night, Residence, Occu...1 - Conway/Battlecreek/Highwood1119None
978100X EDGERTON STGone on ArrivalG180011409200080000NarcoticsNarcotics5 - Payne/Phalen554None
97981X SMITH AV SAdvisedA60011409353440000TheftTheft, Except Auto Theft3 - West Side3211None
98049X SNELLING AV NAdvisedA60011409211000000TheftTheft, Except Auto Theft11 - Hamline/Midway1184None
981140X MARYLAND AV EAdvisedA60011409271480000TheftTheft, Except Auto Theft2 - Greater East Side237None
982150X ALBANY AVAdvisedA140011409162040000VandalismCriminal Damage to Property10 - Como1025None
98384X 7 ST EReport WrittenRR55511409123760000BurglaryAtt. Burglary, Forced Entry, Night, Commercial4 - Dayton's Bluff495None
984147X ALBANY AVReport WrittenRR71011409257800000Auto TheftMotor Vehicle Theft, Automobile10 - Como1025None
98551X PRIOR AV NGone on ArrivalG50011409275800000BurglaryBurglary13 - Union Park13103None
9864 ST E & WALLReport WrittenRR31411409210700000RobberyRobbery, Highway, By Strong Arm17 - Capitol River17133None
98761X UNIVERSITY AV WReport WrittenRR69111409269440000TheftTheft, All Other, Under $5008 - Summit/University8109None
98817X OXFORD ST SAdvisedA64011409149620000TheftTheft, From Auto16 - Summit Hill16147None
989140X MONTREAL AVReport WrittenRR71211409364000000Auto TheftMotor Vehicle Theft, All Other Vehicles15 - Highland15205None
99042X RICE STReport WrittenR63011409179860000TheftTheft, Shoplifting17 - Capitol River17110None
991145X WHITEBEAR AV NGone on ArrivalG60011408917300000TheftTheft, Except Auto Theft2 - Greater East Side219None
9929X LANGFORD PKReport WrittenRR71211409364000000Auto TheftMotor Vehicle Theft, All Other Vehicles12 - St. Anthony1222None
993MACKUBIN ST & UNIVERSITYAdvisedA60011409112180000TheftTheft, Except Auto Theft7 - Thomas/Dale(Frogtown)789None
994160X GRAND AVReport WrittenR60011409195580000TheftTheft, Except Auto Theft14 - Macalester-Groveland14144None
995146X UNIVERSITY AV WGone on ArrivalG60011409362920000TheftTheft, Except Auto Theft13 - Union Park13105None
99616X 7 ST EReport WrittenRR71011408982280000Auto TheftMotor Vehicle Theft, Automobile17 - Capitol River17133None
99771X UNIVERSITY AV WAdvisedA180011408922280000NarcoticsNarcotics8 - Summit/University8108None
99837X WINTHROP ST NReport WrittenRR42211408916580000Agg. Assault Dom.Aggravated Assault, Domestic1 - Conway/Battlecreek/Highwood1120None
999112X EDMUND AVAdvisedA64011408995960000TheftTheft, From Auto11 - Hamline/Midway1186None
\n", "

1000 rows × 12 columns

\n", "
" ], "text/plain": [ " BLOCK CALL_DISPOSITION CALL_DISPOSITION_CODE CODE \\\n", "0 61X UNIVERSITY AV W Gone on Arrival G 600 \n", "1 FARRINGTON ST & TOPPING Report Written RR 711 \n", "2 212X NORTONIA AV Advised A 640 \n", "3 FRONT AV & WESTERN Report Written RR 1410 \n", "4 127X MONTREAL AV Report Written RR 515 \n", "5 91X MARION ST Gone on Arrival G 1800 \n", "6 202X 4 ST E Report Written RR 861 \n", "7 98X PAYNE AV Advised A 600 \n", "8 32X COOK AV E Report Written RR 862 \n", "9 44X LAFOND AV Gone on Arrival G 1800 \n", "10 UNIVERSITY AV W & VICTORIA Report Written RR 314 \n", "11 GRAND AV & OXFORD Report Written RR 691 \n", "12 94X MINNEHAHA AV E Report Written RR 553 \n", "13 212X OLDHUDSON RD Advised A 600 \n", "14 184X OLDHUDSON RD Gone on Arrival G 1800 \n", "15 205X DAYTON AV Report Written RR 1430 \n", "16 42X RICE ST Report Written RR 631 \n", "17 121X PACIFIC ST Report Written RR 422 \n", "18 215X WILSON AV Report Written RR 533 \n", "19 127X SARGENT AV Advised A 640 \n", "20 107X MINNEHAHA AV W Advised A 1401 \n", "21 18X WAYZATA ST Advised A 700 \n", "22 47X HAZEL ST N Advised A 600 \n", "23 142X PARK ST Report Written RR 451 \n", "24 32X COOK AV E Advised A 1800 \n", "25 115X COMO AV Report Written RR 1410 \n", "26 131X PAYNE AV Gone on Arrival G 640 \n", "27 88X CARROLL AV Report Written RR 710 \n", "28 211X ROBLYN AV Advised A 600 \n", "29 158X RANDOLPH AV Report Written RR 692 \n", ".. ... ... ... ... \n", "970 69X OHIO ST Report Written RR 691 \n", "971 62X RICE ST Advised A 600 \n", "972 24X 7 ST W Advised A 600 \n", "973 CYPRESS ST & PACIFIC Report Written RR 692 \n", "974 138X MINNEHAHA AV E Report Written RR 513 \n", "975 72X MARYLAND AV E Advised A 500 \n", "976 UNIVERSITY AV W & VICTORIA Advised A 600 \n", "977 184X OLDHUDSON RD Report Written RR 511 \n", "978 100X EDGERTON ST Gone on Arrival G 1800 \n", "979 81X SMITH AV S Advised A 600 \n", "980 49X SNELLING AV N Advised A 600 \n", "981 140X MARYLAND AV E Advised A 600 \n", "982 150X ALBANY AV Advised A 1400 \n", "983 84X 7 ST E Report Written RR 555 \n", "984 147X ALBANY AV Report Written RR 710 \n", "985 51X PRIOR AV N Gone on Arrival G 500 \n", "986 4 ST E & WALL Report Written RR 314 \n", "987 61X UNIVERSITY AV W Report Written RR 691 \n", "988 17X OXFORD ST S Advised A 640 \n", "989 140X MONTREAL AV Report Written RR 712 \n", "990 42X RICE ST Report Written R 630 \n", "991 145X WHITEBEAR AV N Gone on Arrival G 600 \n", "992 9X LANGFORD PK Report Written RR 712 \n", "993 MACKUBIN ST & UNIVERSITY Advised A 600 \n", "994 160X GRAND AV Report Written R 600 \n", "995 146X UNIVERSITY AV W Gone on Arrival G 600 \n", "996 16X 7 ST E Report Written RR 710 \n", "997 71X UNIVERSITY AV W Advised A 1800 \n", "998 37X WINTHROP ST N Report Written RR 422 \n", "999 112X EDMUND AV Advised A 640 \n", "\n", " Count_ DATE INCIDENT \\\n", "0 1 1408249140000 Theft \n", "1 1 1408159800000 Auto Theft \n", "2 1 1408130280000 Theft \n", "3 1 1408248120000 Vandalism \n", "4 1 1408248000000 Burglary \n", "5 1 1408247280000 Narcotics \n", "6 1 1408246200000 Simple Asasult Dom. \n", "7 1 1408245660000 Theft \n", "8 1 1408245480000 Simple Asasult Dom. \n", "9 1 1408244640000 Narcotics \n", "10 1 1408244520000 Robbery \n", "11 1 1408244400000 Theft \n", "12 1 1408243260000 Burglary \n", "13 1 1408050240000 Theft \n", "14 1 1408239420000 Narcotics \n", "15 1 1408237200000 Vandalism \n", "16 1 1408057440000 Theft \n", "17 1 1408259160000 Agg. Assault Dom. \n", "18 1 1408381200000 Burglary \n", "19 1 1408296600000 Theft \n", "20 1 1408021800000 Graffiti \n", "21 1 1408116180000 Auto Theft \n", "22 1 1408415760000 Theft \n", "23 1 1408175100000 Agg. Assault Dom. \n", "24 1 1408070220000 Narcotics \n", "25 1 1408159800000 Vandalism \n", "26 1 1408127160000 Theft \n", "27 1 1408302000000 Auto Theft \n", "28 1 1408021500000 Theft \n", "29 1 1408136280000 Theft \n", ".. ... ... ... \n", "970 1 1409258340000 Theft \n", "971 1 1409211900000 Theft \n", "972 1 1409183460000 Theft \n", "973 1 1408981680000 Theft \n", "974 1 1408856400000 Burglary \n", "975 1 1409211780000 Burglary \n", "976 1 1409271600000 Theft \n", "977 1 1408950120000 Burglary \n", "978 1 1409200080000 Narcotics \n", "979 1 1409353440000 Theft \n", "980 1 1409211000000 Theft \n", "981 1 1409271480000 Theft \n", "982 1 1409162040000 Vandalism \n", "983 1 1409123760000 Burglary \n", "984 1 1409257800000 Auto Theft \n", "985 1 1409275800000 Burglary \n", "986 1 1409210700000 Robbery \n", "987 1 1409269440000 Theft \n", "988 1 1409149620000 Theft \n", "989 1 1409364000000 Auto Theft \n", "990 1 1409179860000 Theft \n", "991 1 1408917300000 Theft \n", "992 1 1409364000000 Auto Theft \n", "993 1 1409112180000 Theft \n", "994 1 1409195580000 Theft \n", "995 1 1409362920000 Theft \n", "996 1 1408982280000 Auto Theft \n", "997 1 1408922280000 Narcotics \n", "998 1 1408916580000 Agg. Assault Dom. \n", "999 1 1408995960000 Theft \n", "\n", " INCIDENT_TYPE \\\n", "0 Theft, Except Auto Theft \n", "1 Motor Vehicle Theft, Trucks and Buses \n", "2 Theft, From Auto \n", "3 Criminal Damage to Property (Misdemeanor, Unde... \n", "4 Burglary, Forced Entry, Night, Commercial \n", "5 Narcotics \n", "6 Assault, Domestic, Opposite Sex \n", "7 Theft, Except Auto Theft \n", "8 Assault, Domestic, Family/Child \n", "9 Narcotics \n", "10 Robbery, Highway, By Strong Arm \n", "11 Theft, All Other, Under $500 \n", "12 Att. Burglary, Forced Entry, Night, Garage \n", "13 Theft, Except Auto Theft \n", "14 Narcotics \n", "15 Criminal Damage to Property (Felony, Over $500) \n", "16 Theft, Shoplifting, Under $500 \n", "17 Aggravated Assault, Domestic \n", "18 Burglary, Forced Entry, Day, Garage \n", "19 Theft, From Auto \n", "20 Graffiti \n", "21 Motor Vehicle Theft \n", "22 Theft, Except Auto Theft \n", "23 Aggravated Assault, Domestic \n", "24 Narcotics \n", "25 Criminal Damage to Property (Misdemeanor, Unde... \n", "26 Theft, From Auto \n", "27 Motor Vehicle Theft, Automobile \n", "28 Theft, Except Auto Theft \n", "29 Theft, All Other, $501 to $1000 \n", ".. ... \n", "970 Theft, All Other, Under $500 \n", "971 Theft, Except Auto Theft \n", "972 Theft, Except Auto Theft \n", "973 Theft, All Other, $501 to $1000 \n", "974 Burglary, Forced Entry, Night, Garage \n", "975 Burglary \n", "976 Theft, Except Auto Theft \n", "977 Burglary, Forced Entry, Night, Residence, Occu... \n", "978 Narcotics \n", "979 Theft, Except Auto Theft \n", "980 Theft, Except Auto Theft \n", "981 Theft, Except Auto Theft \n", "982 Criminal Damage to Property \n", "983 Att. Burglary, Forced Entry, Night, Commercial \n", "984 Motor Vehicle Theft, Automobile \n", "985 Burglary \n", "986 Robbery, Highway, By Strong Arm \n", "987 Theft, All Other, Under $500 \n", "988 Theft, From Auto \n", "989 Motor Vehicle Theft, All Other Vehicles \n", "990 Theft, Shoplifting \n", "991 Theft, Except Auto Theft \n", "992 Motor Vehicle Theft, All Other Vehicles \n", "993 Theft, Except Auto Theft \n", "994 Theft, Except Auto Theft \n", "995 Theft, Except Auto Theft \n", "996 Motor Vehicle Theft, Automobile \n", "997 Narcotics \n", "998 Aggravated Assault, Domestic \n", "999 Theft, From Auto \n", "\n", " NEIGHBORHOOD_NAME NEIGHBORHOOD_NUMBER POLICE_GRID_NUMBER \\\n", "0 8 - Summit/University 8 109 \n", "1 6 - North End 6 70 \n", "2 2 - Greater East Side 2 80 \n", "3 6 - North End 6 50 \n", "4 15 - Highland 15 225 \n", "5 6 - North End 6 70 \n", "6 1 - Conway/Battlecreek/Highwood 1 100 \n", "7 5 - Payne/Phalen 5 54 \n", "8 5 - Payne/Phalen 5 53 \n", "9 7 - Thomas/Dale(Frogtown) 7 89 \n", "10 7 - Thomas/Dale(Frogtown) 7 88 \n", "11 16 - Summit Hill 16 147 \n", "12 4 - Dayton's Bluff 4 95 \n", "13 1 - Conway/Battlecreek/Highwood 1 120 \n", "14 1 - Conway/Battlecreek/Highwood 1 119 \n", "15 13 - Union Park 13 123 \n", "16 17 - Capitol River 17 110 \n", "17 4 - Dayton's Bluff 4 116 \n", "18 1 - Conway/Battlecreek/Highwood 1 120 \n", "19 14 - Macalester-Groveland 14 146 \n", "20 7 - Thomas/Dale(Frogtown) 7 87 \n", "21 6 - North End 6 70 \n", "22 1 - Conway/Battlecreek/Highwood 1 119 \n", "23 6 - North End 6 31 \n", "24 5 - Payne/Phalen 5 53 \n", "25 10 - Como 10 46 \n", "26 5 - Payne/Phalen 5 34 \n", "27 8 - Summit/University 8 107 \n", "28 13 - Union Park 13 102 \n", "29 14 - Macalester-Groveland 14 164 \n", ".. ... ... ... \n", "970 3 - West Side 3 212 \n", "971 7 - Thomas/Dale(Frogtown) 7 90 \n", "972 9 - West Seventh 9 151 \n", "973 4 - Dayton's Bluff 4 115 \n", "974 2 - Greater East Side 2 77 \n", "975 5 - Payne/Phalen 5 54 \n", "976 7 - Thomas/Dale(Frogtown) 7 88 \n", "977 1 - Conway/Battlecreek/Highwood 1 119 \n", "978 5 - Payne/Phalen 5 54 \n", "979 3 - West Side 3 211 \n", "980 11 - Hamline/Midway 11 84 \n", "981 2 - Greater East Side 2 37 \n", "982 10 - Como 10 25 \n", "983 4 - Dayton's Bluff 4 95 \n", "984 10 - Como 10 25 \n", "985 13 - Union Park 13 103 \n", "986 17 - Capitol River 17 133 \n", "987 8 - Summit/University 8 109 \n", "988 16 - Summit Hill 16 147 \n", "989 15 - Highland 15 205 \n", "990 17 - Capitol River 17 110 \n", "991 2 - Greater East Side 2 19 \n", "992 12 - St. Anthony 12 22 \n", "993 7 - Thomas/Dale(Frogtown) 7 89 \n", "994 14 - Macalester-Groveland 14 144 \n", "995 13 - Union Park 13 105 \n", "996 17 - Capitol River 17 133 \n", "997 8 - Summit/University 8 108 \n", "998 1 - Conway/Battlecreek/Highwood 1 120 \n", "999 11 - Hamline/Midway 11 86 \n", "\n", " TIME \n", "0 None \n", "1 None \n", "2 None \n", "3 None \n", "4 None \n", "5 None \n", "6 None \n", "7 None \n", "8 None \n", "9 None \n", "10 None \n", "11 None \n", "12 None \n", "13 None \n", "14 None \n", "15 None \n", "16 None \n", "17 None \n", "18 None \n", "19 None \n", "20 None \n", "21 None \n", "22 None \n", "23 None \n", "24 None \n", "25 None \n", "26 None \n", "27 None \n", "28 None \n", "29 None \n", ".. ... \n", "970 None \n", "971 None \n", "972 None \n", "973 None \n", "974 None \n", "975 None \n", "976 None \n", "977 None \n", "978 None \n", "979 None \n", "980 None \n", "981 None \n", "982 None \n", "983 None \n", "984 None \n", "985 None \n", "986 None \n", "987 None \n", "988 None \n", "989 None \n", "990 None \n", "991 None \n", "992 None \n", "993 None \n", "994 None \n", "995 None \n", "996 None \n", "997 None \n", "998 None \n", "999 None \n", "\n", "[1000 rows x 12 columns]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import json\n", "from urllib.request import urlopen\n", "\n", "\n", "json_file = \"https://services1.arcgis.com/9meaaHE3uiba0zr8/arcgis/rest/services/Crime_Incident_Report_-_Dataset/FeatureServer/0/query?where=1%3D1&outFields=DATE,TIME,CODE,INCIDENT_TYPE,INCIDENT,POLICE_GRID_NUMBER,NEIGHBORHOOD_NUMBER,NEIGHBORHOOD_NAME,BLOCK,CALL_DISPOSITION_CODE,CALL_DISPOSITION,Count_&outSR=4326&f=json\"\n", "data = urlopen(json_file).read()\n", "raw_json = json.loads(data)\n", "formatted_json = [feature['attributes'] for feature in raw_json['features']]\n", "\n", "df = pd.DataFrame(formatted_json)\n", "\n", "#cols= ['Block','CallDisposition','CallDispCode','Case','Code', 'Count','Date','Incident','IncType','Neighborhood','NNum','ID','Grid','Time']\n", "#df.columns= cols\n", "#df=df.dropna()\n", "#df = df.astype({\"Case\": int, \"Code\": int, \"Grid\":float, \"NNum\":int,\"Count\":int})\n", "\n", "#kc= df.query(\"Grid in (84,85)\")\n", "\n", "#kc\n", "\n", "\n", "df" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BlockCount
17159X CHARLES AV19
31160X CHARLES AV21
36160X UNIVERSITY AV W36
37160X VANBUREN AV22
39161X CHARLES AV30
43161X SHERBURNE AV28
45161X UNIVERSITY AV W22
52162X SHERBURNE AV24
64164X CHARLES AV28
69164X UNIVERSITY AV W16
82166X SHERBURNE AV19
104169X EDMUND AV17
11116XX BLAIR AVE18
11216XX CHARLES AVE24
11316XX EDMUND AVE16
11916XX SHERBURNE AVE23
12016XX THOMAS AVE19
12116XX UNIVERSITY AVE W30
139173X THOMAS AV41
144174X THOMAS AV24
150175X THOMAS AV31
158176X UNIVERSITY AV W313
18117XX THOMAS AVE43
18217XX UNIVERSITY AVE W36
191181X MINNEHAHA AV W40
20949X SNELLING AV N449
21951X SNELLING AV N22
22754X FAIRVIEW AV N22
22954X SNELLING AV N99
23054X WHEELER ST N22
23456X ALDINE ST21
23656X SNELLING AV N63
23857X ALDINE ST18
2475XX ALDINE ST18
2515XX SNELLING AVE N169
26464X SNELLING AV N66
27768X SNELLING AV N22
29171X SNELLING AV N89
324CHARLES AV & SNELLING32
329EDMUND AV & SNELLING18
336FAIRVIEW AV N & UNIVERSITY28
347FRY ST & UNIVERSITY26
366SHERBURNE AV & SNELLING24
385UNIVERSITY AV W & FAIRVIEW18
386UNIVERSITY AV W & FRY17
\n", "
" ], "text/plain": [ " Block Count\n", "17 159X CHARLES AV 19\n", "31 160X CHARLES AV 21\n", "36 160X UNIVERSITY AV W 36\n", "37 160X VANBUREN AV 22\n", "39 161X CHARLES AV 30\n", "43 161X SHERBURNE AV 28\n", "45 161X UNIVERSITY AV W 22\n", "52 162X SHERBURNE AV 24\n", "64 164X CHARLES AV 28\n", "69 164X UNIVERSITY AV W 16\n", "82 166X SHERBURNE AV 19\n", "104 169X EDMUND AV 17\n", "111 16XX BLAIR AVE 18\n", "112 16XX CHARLES AVE 24\n", "113 16XX EDMUND AVE 16\n", "119 16XX SHERBURNE AVE 23\n", "120 16XX THOMAS AVE 19\n", "121 16XX UNIVERSITY AVE W 30\n", "139 173X THOMAS AV 41\n", "144 174X THOMAS AV 24\n", "150 175X THOMAS AV 31\n", "158 176X UNIVERSITY AV W 313\n", "181 17XX THOMAS AVE 43\n", "182 17XX UNIVERSITY AVE W 36\n", "191 181X MINNEHAHA AV W 40\n", "209 49X SNELLING AV N 449\n", "219 51X SNELLING AV N 22\n", "227 54X FAIRVIEW AV N 22\n", "229 54X SNELLING AV N 99\n", "230 54X WHEELER ST N 22\n", "234 56X ALDINE ST 21\n", "236 56X SNELLING AV N 63\n", "238 57X ALDINE ST 18\n", "247 5XX ALDINE ST 18\n", "251 5XX SNELLING AVE N 169\n", "264 64X SNELLING AV N 66\n", "277 68X SNELLING AV N 22\n", "291 71X SNELLING AV N 89\n", "324 CHARLES AV & SNELLING 32\n", "329 EDMUND AV & SNELLING 18\n", "336 FAIRVIEW AV N & UNIVERSITY 28\n", "347 FRY ST & UNIVERSITY 26\n", "366 SHERBURNE AV & SNELLING 24\n", "385 UNIVERSITY AV W & FAIRVIEW 18\n", "386 UNIVERSITY AV W & FRY 17" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Load Data\n", "df= pd.read_csv('Datasets/Crime_Incident_Report.csv')\n", "\n", "#rename columns\n", "cols= ['Case','Date','Time','Code','IncType','Incident','Grid','NNum','Neighborhood','Block','CallDispCode','CallDisposition', 'Count','ID']\n", "df.columns= cols\n", "\n", "#selection for Frogtown and nearby area\n", "df=df.query('Grid in [84]')\n", "#df['Block'].str.contains('SNELLING')\n", "df.query('Block in [\"54X SNELLING AV N\"]')\n", "\n", "p=['Block','Count']\n", "a=df[p].groupby(['Block']).sum().reset_index()\n", "a.query('Count>15')\n" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BlockCallDispCodeCallDispositionCaseCodeCountDateGridIncidentIncType...DayofWeekWeekendMonthDayDayYearDay_MaxTimeHourHourLateNightIntersection
1vanburen av & comoAAdvised21165906261912021-08-11T23:47:34.00090.0DischargeWeapons, Discharging a Firearm in the City Limits...208112232232021-08-11 23:47:342311
2thomas av & grottoAAdvised21165903995412021-08-11T23:46:19.00088.0Proactive Police VisitProactive Police Visit...208112232232021-08-11 23:46:192311
515x charles avAAdvised21165885180012021-08-11T23:13:25.00090.0NarcoticsNarcotics...208112232232021-08-11 23:13:252310
655x central av wAAdvised21165880995412021-08-11T23:01:57.000109.0Proactive Police VisitProactive Police Visit...208112232232021-08-11 23:01:572310
1264x central av wRRReport Written2116586266112021-08-11T21:54:00.000108.0TheftTheft, Bicycle Theft, Under $500...208112232232021-08-11 21:54:002100
\n", "

5 rows × 25 columns

\n", "
" ], "text/plain": [ " Block CallDispCode CallDisposition Case Code Count \\\n", "1 vanburen av & como A Advised 21165906 2619 1 \n", "2 thomas av & grotto A Advised 21165903 9954 1 \n", "5 15x charles av A Advised 21165885 1800 1 \n", "6 55x central av w A Advised 21165880 9954 1 \n", "12 64x central av w RR Report Written 21165862 661 1 \n", "\n", " Date Grid Incident \\\n", "1 2021-08-11T23:47:34.000 90.0 Discharge \n", "2 2021-08-11T23:46:19.000 88.0 Proactive Police Visit \n", "5 2021-08-11T23:13:25.000 90.0 Narcotics \n", "6 2021-08-11T23:01:57.000 109.0 Proactive Police Visit \n", "12 2021-08-11T21:54:00.000 108.0 Theft \n", "\n", " IncType ... DayofWeek Weekend \\\n", "1 Weapons, Discharging a Firearm in the City Limits ... 2 0 \n", "2 Proactive Police Visit ... 2 0 \n", "5 Narcotics ... 2 0 \n", "6 Proactive Police Visit ... 2 0 \n", "12 Theft, Bicycle Theft, Under $500 ... 2 0 \n", "\n", " Month Day DayYear Day_Max TimeHour Hour LateNight \\\n", "1 8 11 223 223 2021-08-11 23:47:34 23 1 \n", "2 8 11 223 223 2021-08-11 23:46:19 23 1 \n", "5 8 11 223 223 2021-08-11 23:13:25 23 1 \n", "6 8 11 223 223 2021-08-11 23:01:57 23 1 \n", "12 8 11 223 223 2021-08-11 21:54:00 21 0 \n", "\n", " Intersection \n", "1 1 \n", "2 1 \n", "5 0 \n", "6 0 \n", "12 0 \n", "\n", "[5 rows x 25 columns]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Add Time Variables\n", "df= df[df.Case != 18254093] #messed up time variable\n", "\n", "#Convert Date to Datetime!\n", "from datetime import datetime\n", "\n", "df['DateTime']= pd.to_datetime(df['Date']) # Create new column called DateTime\n", "df['Year']= df['DateTime'].dt.year #create year column\n", "df['DayofWeek']=df['DateTime'].dt.dayofweek #create day of the week column where default 0=Monday\n", "df['Weekend'] = df['DayofWeek'].apply(lambda x: 1 if (x>4) else 0) #Create a weekend category\n", "df['Month'] = df['DateTime'].dt.month # Create Month Category\n", "df['Day'] = df['DateTime'].dt.day #Create Day of the Current month\n", "df['DayYear'] = df['DateTime'].dt.dayofyear #Create Day of the year (0-365)\n", "df['Day_Max'] = df.iloc[0,-1] #selects uptodate day; NOTE: the data is sorted chronologically\n", "\n", "#Hour Data\n", "df['TimeHour']= pd.to_datetime(df['Time'])\n", "df['Hour'] = df['TimeHour'].dt.hour.astype(int) #Create Hour Colum\n", "df['LateNight'] = df['Hour'].apply(lambda x: 1 if (x>21 or x<5) else 0) #Latenight designation from 10Pm to 6PM\n", "\n", "#Creating the intersection Column. Note: the Block column has the address information\n", "df.Block = df.Block.astype(str) #first change the type to string\n", "df['Block']= df['Block'].str.lower() #lowercase string to create uniformity\n", "\n", "#While scanning the data I noticed that all intersections had \"&\" \n", "df['Intersection'] = df['Block'].apply(lambda x: 1 if '&' in x else 0) #intersection\n", "\n", "df.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Prepare Intersection table " ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The intersection table dimension are (13128, 25)\n" ] } ], "source": [ "#Load clean intersections key\n", "df_Intersection_key = pd.read_csv('Datasets/Intersection_key_clean.csv')\n", "\n", "# Create a new dateframe specifying only intersections from primary dataset\n", "dfI=df.query('Intersection ==1')\n", "print('The intersection table dimension are ' + str(dfI.shape))\n", "#print(dfI.Block.head(10))\n", "\n", "\n", "#Split the strings\n", "new=dfI['Block'].str.split(\"& \", n = 1, expand = True) \n", "dfI['Inter2']= new[1]\n", "new=dfI['Block'].str.split(\" \", n = 1, expand = True) #Note the code specifies the first time a space occured\n", "dfI['Inter1']=new[0]\n", "\n", "#Create the IndexKey; recall we prepared the IntersectionKey where it considers any order\n", "dfI['IndexKey']= dfI['Inter1']+ '_' + dfI['Inter2']\n", "dfI.reset_index()\n", "dfI=pd.merge(dfI, df_Intersection_key, on='IndexKey', how='left')\n", "\n" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BlockCallDispCodeCallDispositionCaseCodeCountDateGridIncidentIncType...MonthDayDayYearDay_MaxTimeHourHourLateNightIntersectionLatitudeLongitude
1grotto_thomasAAdvised21165903995412021-08-11T23:46:19.00088.0Proactive Police VisitProactive Police Visit...8112232232021-08-11 23:46:19231144.959356-93.131431
3rice_sherburneAAdvised21165585995412021-08-11T17:03:05.00091.0Proactive Police VisitProactive Police Visit...8112232232021-08-11 17:03:05170144.956848-93.105964
4hamline_universityRRReport Written21165556182012021-08-11T16:43:00.00086.0NarcoticsNarcotics, Possession of Synthetic Narcotic, D......8112232232021-08-11 16:43:00160144.955811-93.156832
5rice_sherburneAAdvised21165552995412021-08-11T16:39:05.00091.0Proactive Police VisitProactive Police Visit...8112232232021-08-11 16:39:05160144.956848-93.105964
6jackson_universityRRReport Written2116559471012021-08-11T15:41:00.00092.0Auto TheftMotor Vehicle Theft, Automobile...8112232232021-08-11 15:41:00150144.955956-93.096982
\n", "

5 rows × 27 columns

\n", "
" ], "text/plain": [ " Block CallDispCode CallDisposition Case Code Count \\\n", "1 grotto_thomas A Advised 21165903 9954 1 \n", "3 rice_sherburne A Advised 21165585 9954 1 \n", "4 hamline_university RR Report Written 21165556 1820 1 \n", "5 rice_sherburne A Advised 21165552 9954 1 \n", "6 jackson_university RR Report Written 21165594 710 1 \n", "\n", " Date Grid Incident \\\n", "1 2021-08-11T23:46:19.000 88.0 Proactive Police Visit \n", "3 2021-08-11T17:03:05.000 91.0 Proactive Police Visit \n", "4 2021-08-11T16:43:00.000 86.0 Narcotics \n", "5 2021-08-11T16:39:05.000 91.0 Proactive Police Visit \n", "6 2021-08-11T15:41:00.000 92.0 Auto Theft \n", "\n", " IncType ... Month Day DayYear \\\n", "1 Proactive Police Visit ... 8 11 223 \n", "3 Proactive Police Visit ... 8 11 223 \n", "4 Narcotics, Possession of Synthetic Narcotic, D... ... 8 11 223 \n", "5 Proactive Police Visit ... 8 11 223 \n", "6 Motor Vehicle Theft, Automobile ... 8 11 223 \n", "\n", " Day_Max TimeHour Hour LateNight Intersection Latitude \\\n", "1 223 2021-08-11 23:46:19 23 1 1 44.959356 \n", "3 223 2021-08-11 17:03:05 17 0 1 44.956848 \n", "4 223 2021-08-11 16:43:00 16 0 1 44.955811 \n", "5 223 2021-08-11 16:39:05 16 0 1 44.956848 \n", "6 223 2021-08-11 15:41:00 15 0 1 44.955956 \n", "\n", " Longitude \n", "1 -93.131431 \n", "3 -93.105964 \n", "4 -93.156832 \n", "5 -93.105964 \n", "6 -93.096982 \n", "\n", "[5 rows x 27 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Drop rows with missing coordinates\n", "dfI=dfI[dfI['Coordinates'].notnull()]\n", "\n", "# Separate Latitude and Longitude \n", "new=dfI['Coordinates'].str.split(\",\", n = 1, expand = True) \n", "# making seperate first name column from new data frame \n", "dfI['Latitude']= pd.to_numeric(new[0]) #pd.to_numeric convert it to float\n", "dfI['Longitude']= pd.to_numeric(new[1])\n", "\n", "#Renaming columns\n", "\n", "dfI['Block']=dfI['OutputKey'] #for practical purposes it makes sense\n", "Drop_col=['Inter2','Inter1', 'IndexKey', 'Coordinates', 'OutputKey']\n", "dfI_Final=dfI.drop(Drop_col, axis=1,)\n", "dfI_Final.head(5)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Prepare Address Decoder Table \n", "\n", "It is so remarkably short" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "#Split Data\n", "dfW=df.query('Intersection==0')\n", "\n", "#Load Complete Decoder Key\n", "df_C= pd.read_csv('Datasets/SemiKey.csv')\n", "df_C= df_C[['Block','Latitude','Longitude']]\n", "\n", "# Merge with the dataset and remove missing values\n", "dC=pd.merge(dfW, df_C, on='Block', how='left')\n", "dC=dC.fillna('Mi')\n", "dC=dC.query('Latitude != \"Mi\"')\n", "\n", "#Bringing the data back together \n", "fg= dfI.append(dC, ignore_index=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Preliminary Pre-Cleaning steps \n", "\n", "It is good to do the data pre-cleaning steps here to reduce clutter on a working notebook. A few edits include renaming some values, clustering crimes together and creating some dummies. It is saved in a csv, which is used for execution" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "#Few Quick Edits\n", "fg.CallDisposition.loc[(fg['CallDisposition'] == 'G - Gone on Arrival')] = 'Gone on Arrival'\n", "fg.CallDisposition.loc[(fg['CallDisposition'] == 'A - Advised')] = 'Advised'\n", "fg.CallDisposition.loc[(fg['CallDisposition'] == 'RR - Report Written')] = 'Report Written'\n", "fg.Incident.loc[(fg['Incident'] == 'Simple Asasult Dom.')] = 'Simple Assault Dom.'\n", "fg.Incident.loc[(fg['Incident'] == 'Graffiti')] = 'Vandalism'\n", "fg.Incident.loc[fg[\"Incident\"].isin([ \"Rape\",\"Agg. Assault\",'Homicide'])]= 'Violent'\n", "fg.Incident.loc[fg[\"Incident\"].isin([\"Simple Assault Dom.\",\"Agg. Assault Dom.\"])]= 'Domestic Assault'\n", "\n", "\n", "#[fg[\"Incident\"].isin([\"Simple Assault Dom.\", \"Rape\"])\n", "\n", "\n", "#Create a dummy for each crime category\n", "fg= pd.concat([fg,pd.get_dummies(fg['Incident'])], axis=1)\n", "fg= pd.concat([fg,pd.get_dummies(fg['CallDisposition'])], axis=1)\n", "\n", "fg.to_csv('Datasets/FGCrime_Final.csv', encoding='utf-8', index=False)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BlockCallDispCodeCallDispositionCaseCodeCoordinatesCountDateDateTimeDay...VandalismViolent10 - Como11 - Hamline/Midway13 - Union Park7 - Thomas/Dale(Frogtown)8 - Summit/UniversityAdvisedGone on ArrivalReport Written
0grotto_thomasAAdvised21165903995444.959356, -93.13143112021-08-11T23:46:19.0002021-08-11 23:46:1911...0000000100
1rice_sherburneAAdvised21165585995444.956848, -93.10596412021-08-11T17:03:05.0002021-08-11 17:03:0511...0000000100
2hamline_universityRRReport Written21165556182044.955811, -93.15683212021-08-11T16:43:00.0002021-08-11 16:43:0011...0000000001
3rice_sherburneAAdvised21165552995444.956848, -93.10596412021-08-11T16:39:05.0002021-08-11 16:39:0511...0000000100
4jackson_universityRRReport Written2116559471044.955956, -93.09698212021-08-11T15:41:00.0002021-08-11 15:41:0011...0000000001
5dale_thomasAAdvised21165391995444.959342, -93.12637112021-08-11T12:57:32.0002021-08-11 12:57:3211...0000000100
6rice_universityAAdvised21165325995444.955847, -93.10595912021-08-11T11:20:31.0002021-08-11 11:20:3111...0000000100
7linden_mtairyAAdvised21165189995444.959025, -93.09511112021-08-11T08:13:58.0002021-08-11 08:13:5811...0000000100
8rice_sherburneAAdvised21164616995444.956848, -93.10596412021-08-10T14:01:49.0002021-08-10 14:01:4910...0000000100
9rice_charlesAAdvised21164500995444.957614, -93.10595112021-08-10T10:53:19.0002021-08-10 10:53:1910...0000000100
10arundel_vanburenAAdvised21164358995444.962067, -93.11867612021-08-10T05:31:00.0002021-08-10 05:31:0010...0000000100
11victoria_vanburenRRReport Written21164180261944.962078, -93.13649812021-08-09T21:38:00.0002021-08-09 21:38:009...0000000001
12rice_charlesAAdvised21164176995444.957614, -93.10595112021-08-09T21:36:53.0002021-08-09 21:36:539...0000000100
13victoria_minnehahaRRReport Written2116414131144.962988, -93.13650312021-08-09T20:40:00.0002021-08-09 20:40:009...0000000001
14grotto_lafondAAdvised21163523995444.960253, -93.13143612021-08-09T00:10:34.0002021-08-09 00:10:349...0000000100
15linden_mtairyAAdvised21163303995444.959025, -93.09511112021-08-08T16:53:43.0002021-08-08 16:53:438...0000000100
16rice_comoAAdvised21163263995444.958975, -93.10594112021-08-08T15:32:19.0002021-08-08 15:32:198...0000000100
17rice_sherburneAAdvised21163208995444.956848, -93.10596412021-08-08T13:39:44.0002021-08-08 13:39:448...0000000100
18rice_sherburneAAdvised21163179995444.956848, -93.10596412021-08-08T12:39:58.0002021-08-08 12:39:588...0000000100
19rice_sherburneAAdvised21163172995444.956848, -93.10596412021-08-08T12:21:17.0002021-08-08 12:21:178...0000000100
20victoria_charlesRRReport Written2116318021044.957542, -93.13649512021-08-07T23:00:00.0002021-08-07 23:00:007...0100000001
21kent_edmundAAdvised21162826995444.958457, -93.12379912021-08-07T20:24:28.0002021-08-07 20:24:287...0000000100
22mackubin_sherburneAAdvised21162822995444.956661, -93.12123712021-08-07T20:12:54.0002021-08-07 20:12:547...0000000100
23arundel_universityAAdvised21162803995444.955853, -93.11867912021-08-07T19:42:00.0002021-08-07 19:42:007...0000000100
24dale_fullerAAdvised21162789995444.953949, -93.12637212021-08-07T19:21:15.0002021-08-07 19:21:157...0000000100
25western_thomasAAdvised21162739995444.959379, -93.11617512021-08-07T18:04:24.0002021-08-07 18:04:247...0000000100
26western_thomasAAdvised21162730995444.959379, -93.11617512021-08-07T17:49:37.0002021-08-07 17:49:377...0000000100
27dale_universityAAdvised21162704995444.955828, -93.12637612021-08-07T17:05:15.0002021-08-07 17:05:157...0000000100
28stanthony_ravouxRRReport Written2181509464344.951390, -93.11333812021-08-07T16:30:00.0002021-08-07 16:30:007...0000000001
29griggs_thomasAAdvised21162656995444.959361, -93.15172912021-08-07T15:53:23.0002021-08-07 15:53:237...0000000100
..................................................................
1670727x lexington pa nAAdvised210010499954NaN12021-01-02T18:48:53.0002021-01-02 18:48:532...0000000100
1670852x sherburne avRRReport Written21001046651NaN12021-01-02T18:17:00.0002021-01-02 18:17:002...0000000001
1670958x dale st nAAdvised210010269954NaN12021-01-02T18:16:14.0002021-01-02 18:16:142...0000000100
1671027x lexington pa nAAdvised210008539954NaN12021-01-02T14:17:00.0002021-01-02 14:17:002...0000000100
1671146x charles avAAdvised210007089954NaN12021-01-02T08:28:39.0002021-01-02 08:28:392...0000000100
1671228x ravoux stAAdvised210006789954NaN12021-01-02T04:28:08.0002021-01-02 04:28:082...0000000100
1671395x lexington pa nAAdvised210006249954NaN12021-01-02T01:07:36.0002021-01-02 01:07:362...0000000100
16714109x university av wAAdvised210005779954NaN12021-01-01T23:43:49.0002021-01-01 23:43:491...0000000100
1671553x thomas avAAdvised210005769954NaN12021-01-01T23:41:15.0002021-01-01 23:41:151...0000000100
1671658x edmund avAAdvised210005679954NaN12021-01-01T23:30:53.0002021-01-01 23:30:531...0000000100
1671758x university av wAAdvised210005649954NaN12021-01-01T23:29:38.0002021-01-01 23:29:381...0000000100
1671895x lexington pa nAAdvised210005639954NaN12021-01-01T23:28:44.0002021-01-01 23:28:441...0000000100
1671954x dale st nAAdvised210005629954NaN12021-01-01T23:27:39.0002021-01-01 23:27:391...0000000100
1672067x arundel stAAdvised210005609954NaN12021-01-01T23:21:29.0002021-01-01 23:21:291...0000000100
1672158x dale st nAAdvised210005339954NaN12021-01-01T22:32:41.0002021-01-01 22:32:411...0000000100
16722100x thomas avRRReport Written21000767651NaN12021-01-01T22:30:00.0002021-01-01 22:30:001...0000000001
16723130x university av wAAdvised210004099954NaN12021-01-01T18:07:56.0002021-01-01 18:07:561...0000000100
1672458x dale st nAAdvised210003899954NaN12021-01-01T17:33:48.0002021-01-01 17:33:481...0000000100
1672561x thomas avAAdvised210003589954NaN12021-01-01T16:44:38.0002021-01-01 16:44:381...0000000100
1672617x charles avGGone on Arrival210003771800NaN12021-01-01T16:32:14.0002021-01-01 16:32:141...0000000010
1672740x minnehaha av wGGone on Arrival210003251400NaN12021-01-01T15:25:45.0002021-01-01 15:25:451...1000000010
16728130x university av wAAdvised210003119954NaN12021-01-01T14:56:58.0002021-01-01 14:56:581...0000000100
1672962x rice stAAdvised210002969954NaN12021-01-01T14:26:21.0002021-01-01 14:26:211...0000000100
1673056x sherburne avAAdvised210002741800NaN12021-01-01T12:36:17.0002021-01-01 12:36:171...0000000100
1673137x marion stGGone on Arrival21000273700NaN12021-01-01T11:57:10.0002021-01-01 11:57:101...0000000010
1673221x como avAAdvised210002199954NaN12021-01-01T10:33:37.0002021-01-01 10:33:371...0000000100
16733130x university av wAAdvised210002189954NaN12021-01-01T10:33:31.0002021-01-01 10:33:311...0000000100
1673450x rice stAAdvised210002109954NaN12021-01-01T10:14:26.0002021-01-01 10:14:261...0000000100
16735121x charles avAAdvised210000982619NaN12021-01-01T03:11:57.0002021-01-01 03:11:571...0000000100
16736119x university av wAAdvised210000439954NaN12021-01-01T01:08:02.0002021-01-01 01:08:021...0000000100
\n", "

6074 rows × 53 columns

\n", "
" ], "text/plain": [ " Block CallDispCode CallDisposition Case Code \\\n", "0 grotto_thomas A Advised 21165903 9954 \n", "1 rice_sherburne A Advised 21165585 9954 \n", "2 hamline_university RR Report Written 21165556 1820 \n", "3 rice_sherburne A Advised 21165552 9954 \n", "4 jackson_university RR Report Written 21165594 710 \n", "5 dale_thomas A Advised 21165391 9954 \n", "6 rice_university A Advised 21165325 9954 \n", "7 linden_mtairy A Advised 21165189 9954 \n", "8 rice_sherburne A Advised 21164616 9954 \n", "9 rice_charles A Advised 21164500 9954 \n", "10 arundel_vanburen A Advised 21164358 9954 \n", "11 victoria_vanburen RR Report Written 21164180 2619 \n", "12 rice_charles A Advised 21164176 9954 \n", "13 victoria_minnehaha RR Report Written 21164141 311 \n", "14 grotto_lafond A Advised 21163523 9954 \n", "15 linden_mtairy A Advised 21163303 9954 \n", "16 rice_como A Advised 21163263 9954 \n", "17 rice_sherburne A Advised 21163208 9954 \n", "18 rice_sherburne A Advised 21163179 9954 \n", "19 rice_sherburne A Advised 21163172 9954 \n", "20 victoria_charles RR Report Written 21163180 210 \n", "21 kent_edmund A Advised 21162826 9954 \n", "22 mackubin_sherburne A Advised 21162822 9954 \n", "23 arundel_university A Advised 21162803 9954 \n", "24 dale_fuller A Advised 21162789 9954 \n", "25 western_thomas A Advised 21162739 9954 \n", "26 western_thomas A Advised 21162730 9954 \n", "27 dale_university A Advised 21162704 9954 \n", "28 stanthony_ravoux RR Report Written 21815094 643 \n", "29 griggs_thomas A Advised 21162656 9954 \n", "... ... ... ... ... ... \n", "16707 27x lexington pa n A Advised 21001049 9954 \n", "16708 52x sherburne av RR Report Written 21001046 651 \n", "16709 58x dale st n A Advised 21001026 9954 \n", "16710 27x lexington pa n A Advised 21000853 9954 \n", "16711 46x charles av A Advised 21000708 9954 \n", "16712 28x ravoux st A Advised 21000678 9954 \n", "16713 95x lexington pa n A Advised 21000624 9954 \n", "16714 109x university av w A Advised 21000577 9954 \n", "16715 53x thomas av A Advised 21000576 9954 \n", "16716 58x edmund av A Advised 21000567 9954 \n", "16717 58x university av w A Advised 21000564 9954 \n", "16718 95x lexington pa n A Advised 21000563 9954 \n", "16719 54x dale st n A Advised 21000562 9954 \n", "16720 67x arundel st A Advised 21000560 9954 \n", "16721 58x dale st n A Advised 21000533 9954 \n", "16722 100x thomas av RR Report Written 21000767 651 \n", "16723 130x university av w A Advised 21000409 9954 \n", "16724 58x dale st n A Advised 21000389 9954 \n", "16725 61x thomas av A Advised 21000358 9954 \n", "16726 17x charles av G Gone on Arrival 21000377 1800 \n", "16727 40x minnehaha av w G Gone on Arrival 21000325 1400 \n", "16728 130x university av w A Advised 21000311 9954 \n", "16729 62x rice st A Advised 21000296 9954 \n", "16730 56x sherburne av A Advised 21000274 1800 \n", "16731 37x marion st G Gone on Arrival 21000273 700 \n", "16732 21x como av A Advised 21000219 9954 \n", "16733 130x university av w A Advised 21000218 9954 \n", "16734 50x rice st A Advised 21000210 9954 \n", "16735 121x charles av A Advised 21000098 2619 \n", "16736 119x university av w A Advised 21000043 9954 \n", "\n", " Coordinates Count Date \\\n", "0 44.959356, -93.131431 1 2021-08-11T23:46:19.000 \n", "1 44.956848, -93.105964 1 2021-08-11T17:03:05.000 \n", "2 44.955811, -93.156832 1 2021-08-11T16:43:00.000 \n", "3 44.956848, -93.105964 1 2021-08-11T16:39:05.000 \n", "4 44.955956, -93.096982 1 2021-08-11T15:41:00.000 \n", "5 44.959342, -93.126371 1 2021-08-11T12:57:32.000 \n", "6 44.955847, -93.105959 1 2021-08-11T11:20:31.000 \n", "7 44.959025, -93.095111 1 2021-08-11T08:13:58.000 \n", "8 44.956848, -93.105964 1 2021-08-10T14:01:49.000 \n", "9 44.957614, -93.105951 1 2021-08-10T10:53:19.000 \n", "10 44.962067, -93.118676 1 2021-08-10T05:31:00.000 \n", "11 44.962078, -93.136498 1 2021-08-09T21:38:00.000 \n", "12 44.957614, -93.105951 1 2021-08-09T21:36:53.000 \n", "13 44.962988, -93.136503 1 2021-08-09T20:40:00.000 \n", "14 44.960253, -93.131436 1 2021-08-09T00:10:34.000 \n", "15 44.959025, -93.095111 1 2021-08-08T16:53:43.000 \n", "16 44.958975, -93.105941 1 2021-08-08T15:32:19.000 \n", "17 44.956848, -93.105964 1 2021-08-08T13:39:44.000 \n", "18 44.956848, -93.105964 1 2021-08-08T12:39:58.000 \n", "19 44.956848, -93.105964 1 2021-08-08T12:21:17.000 \n", "20 44.957542, -93.136495 1 2021-08-07T23:00:00.000 \n", "21 44.958457, -93.123799 1 2021-08-07T20:24:28.000 \n", "22 44.956661, -93.121237 1 2021-08-07T20:12:54.000 \n", "23 44.955853, -93.118679 1 2021-08-07T19:42:00.000 \n", "24 44.953949, -93.126372 1 2021-08-07T19:21:15.000 \n", "25 44.959379, -93.116175 1 2021-08-07T18:04:24.000 \n", "26 44.959379, -93.116175 1 2021-08-07T17:49:37.000 \n", "27 44.955828, -93.126376 1 2021-08-07T17:05:15.000 \n", "28 44.951390, -93.113338 1 2021-08-07T16:30:00.000 \n", "29 44.959361, -93.151729 1 2021-08-07T15:53:23.000 \n", "... ... ... ... \n", "16707 NaN 1 2021-01-02T18:48:53.000 \n", "16708 NaN 1 2021-01-02T18:17:00.000 \n", "16709 NaN 1 2021-01-02T18:16:14.000 \n", "16710 NaN 1 2021-01-02T14:17:00.000 \n", "16711 NaN 1 2021-01-02T08:28:39.000 \n", "16712 NaN 1 2021-01-02T04:28:08.000 \n", "16713 NaN 1 2021-01-02T01:07:36.000 \n", "16714 NaN 1 2021-01-01T23:43:49.000 \n", "16715 NaN 1 2021-01-01T23:41:15.000 \n", "16716 NaN 1 2021-01-01T23:30:53.000 \n", "16717 NaN 1 2021-01-01T23:29:38.000 \n", "16718 NaN 1 2021-01-01T23:28:44.000 \n", "16719 NaN 1 2021-01-01T23:27:39.000 \n", "16720 NaN 1 2021-01-01T23:21:29.000 \n", "16721 NaN 1 2021-01-01T22:32:41.000 \n", "16722 NaN 1 2021-01-01T22:30:00.000 \n", "16723 NaN 1 2021-01-01T18:07:56.000 \n", "16724 NaN 1 2021-01-01T17:33:48.000 \n", "16725 NaN 1 2021-01-01T16:44:38.000 \n", "16726 NaN 1 2021-01-01T16:32:14.000 \n", "16727 NaN 1 2021-01-01T15:25:45.000 \n", "16728 NaN 1 2021-01-01T14:56:58.000 \n", "16729 NaN 1 2021-01-01T14:26:21.000 \n", "16730 NaN 1 2021-01-01T12:36:17.000 \n", "16731 NaN 1 2021-01-01T11:57:10.000 \n", "16732 NaN 1 2021-01-01T10:33:37.000 \n", "16733 NaN 1 2021-01-01T10:33:31.000 \n", "16734 NaN 1 2021-01-01T10:14:26.000 \n", "16735 NaN 1 2021-01-01T03:11:57.000 \n", "16736 NaN 1 2021-01-01T01:08:02.000 \n", "\n", " DateTime Day ... Vandalism Violent 10 - Como \\\n", "0 2021-08-11 23:46:19 11 ... 0 0 0 \n", "1 2021-08-11 17:03:05 11 ... 0 0 0 \n", "2 2021-08-11 16:43:00 11 ... 0 0 0 \n", "3 2021-08-11 16:39:05 11 ... 0 0 0 \n", "4 2021-08-11 15:41:00 11 ... 0 0 0 \n", "5 2021-08-11 12:57:32 11 ... 0 0 0 \n", "6 2021-08-11 11:20:31 11 ... 0 0 0 \n", "7 2021-08-11 08:13:58 11 ... 0 0 0 \n", "8 2021-08-10 14:01:49 10 ... 0 0 0 \n", "9 2021-08-10 10:53:19 10 ... 0 0 0 \n", "10 2021-08-10 05:31:00 10 ... 0 0 0 \n", "11 2021-08-09 21:38:00 9 ... 0 0 0 \n", "12 2021-08-09 21:36:53 9 ... 0 0 0 \n", "13 2021-08-09 20:40:00 9 ... 0 0 0 \n", "14 2021-08-09 00:10:34 9 ... 0 0 0 \n", "15 2021-08-08 16:53:43 8 ... 0 0 0 \n", "16 2021-08-08 15:32:19 8 ... 0 0 0 \n", "17 2021-08-08 13:39:44 8 ... 0 0 0 \n", "18 2021-08-08 12:39:58 8 ... 0 0 0 \n", "19 2021-08-08 12:21:17 8 ... 0 0 0 \n", "20 2021-08-07 23:00:00 7 ... 0 1 0 \n", "21 2021-08-07 20:24:28 7 ... 0 0 0 \n", "22 2021-08-07 20:12:54 7 ... 0 0 0 \n", "23 2021-08-07 19:42:00 7 ... 0 0 0 \n", "24 2021-08-07 19:21:15 7 ... 0 0 0 \n", "25 2021-08-07 18:04:24 7 ... 0 0 0 \n", "26 2021-08-07 17:49:37 7 ... 0 0 0 \n", "27 2021-08-07 17:05:15 7 ... 0 0 0 \n", "28 2021-08-07 16:30:00 7 ... 0 0 0 \n", "29 2021-08-07 15:53:23 7 ... 0 0 0 \n", "... ... ... ... ... ... ... \n", "16707 2021-01-02 18:48:53 2 ... 0 0 0 \n", "16708 2021-01-02 18:17:00 2 ... 0 0 0 \n", "16709 2021-01-02 18:16:14 2 ... 0 0 0 \n", "16710 2021-01-02 14:17:00 2 ... 0 0 0 \n", "16711 2021-01-02 08:28:39 2 ... 0 0 0 \n", "16712 2021-01-02 04:28:08 2 ... 0 0 0 \n", "16713 2021-01-02 01:07:36 2 ... 0 0 0 \n", "16714 2021-01-01 23:43:49 1 ... 0 0 0 \n", "16715 2021-01-01 23:41:15 1 ... 0 0 0 \n", "16716 2021-01-01 23:30:53 1 ... 0 0 0 \n", "16717 2021-01-01 23:29:38 1 ... 0 0 0 \n", "16718 2021-01-01 23:28:44 1 ... 0 0 0 \n", "16719 2021-01-01 23:27:39 1 ... 0 0 0 \n", "16720 2021-01-01 23:21:29 1 ... 0 0 0 \n", "16721 2021-01-01 22:32:41 1 ... 0 0 0 \n", "16722 2021-01-01 22:30:00 1 ... 0 0 0 \n", "16723 2021-01-01 18:07:56 1 ... 0 0 0 \n", "16724 2021-01-01 17:33:48 1 ... 0 0 0 \n", "16725 2021-01-01 16:44:38 1 ... 0 0 0 \n", "16726 2021-01-01 16:32:14 1 ... 0 0 0 \n", "16727 2021-01-01 15:25:45 1 ... 1 0 0 \n", "16728 2021-01-01 14:56:58 1 ... 0 0 0 \n", "16729 2021-01-01 14:26:21 1 ... 0 0 0 \n", "16730 2021-01-01 12:36:17 1 ... 0 0 0 \n", "16731 2021-01-01 11:57:10 1 ... 0 0 0 \n", "16732 2021-01-01 10:33:37 1 ... 0 0 0 \n", "16733 2021-01-01 10:33:31 1 ... 0 0 0 \n", "16734 2021-01-01 10:14:26 1 ... 0 0 0 \n", "16735 2021-01-01 03:11:57 1 ... 0 0 0 \n", "16736 2021-01-01 01:08:02 1 ... 0 0 0 \n", "\n", " 11 - Hamline/Midway 13 - Union Park 7 - Thomas/Dale(Frogtown) \\\n", "0 0 0 0 \n", "1 0 0 0 \n", "2 0 0 0 \n", "3 0 0 0 \n", "4 0 0 0 \n", "5 0 0 0 \n", "6 0 0 0 \n", "7 0 0 0 \n", "8 0 0 0 \n", "9 0 0 0 \n", "10 0 0 0 \n", "11 0 0 0 \n", "12 0 0 0 \n", "13 0 0 0 \n", "14 0 0 0 \n", "15 0 0 0 \n", "16 0 0 0 \n", "17 0 0 0 \n", "18 0 0 0 \n", "19 0 0 0 \n", "20 0 0 0 \n", "21 0 0 0 \n", "22 0 0 0 \n", "23 0 0 0 \n", "24 0 0 0 \n", "25 0 0 0 \n", "26 0 0 0 \n", "27 0 0 0 \n", "28 0 0 0 \n", "29 0 0 0 \n", "... ... ... ... \n", "16707 0 0 0 \n", "16708 0 0 0 \n", "16709 0 0 0 \n", "16710 0 0 0 \n", "16711 0 0 0 \n", "16712 0 0 0 \n", "16713 0 0 0 \n", "16714 0 0 0 \n", "16715 0 0 0 \n", "16716 0 0 0 \n", "16717 0 0 0 \n", "16718 0 0 0 \n", "16719 0 0 0 \n", "16720 0 0 0 \n", "16721 0 0 0 \n", "16722 0 0 0 \n", "16723 0 0 0 \n", "16724 0 0 0 \n", "16725 0 0 0 \n", "16726 0 0 0 \n", "16727 0 0 0 \n", "16728 0 0 0 \n", "16729 0 0 0 \n", "16730 0 0 0 \n", "16731 0 0 0 \n", "16732 0 0 0 \n", "16733 0 0 0 \n", "16734 0 0 0 \n", "16735 0 0 0 \n", "16736 0 0 0 \n", "\n", " 8 - Summit/University Advised Gone on Arrival Report Written \n", "0 0 1 0 0 \n", "1 0 1 0 0 \n", "2 0 0 0 1 \n", "3 0 1 0 0 \n", "4 0 0 0 1 \n", "5 0 1 0 0 \n", "6 0 1 0 0 \n", "7 0 1 0 0 \n", "8 0 1 0 0 \n", "9 0 1 0 0 \n", "10 0 1 0 0 \n", "11 0 0 0 1 \n", "12 0 1 0 0 \n", "13 0 0 0 1 \n", "14 0 1 0 0 \n", "15 0 1 0 0 \n", "16 0 1 0 0 \n", "17 0 1 0 0 \n", "18 0 1 0 0 \n", "19 0 1 0 0 \n", "20 0 0 0 1 \n", "21 0 1 0 0 \n", "22 0 1 0 0 \n", "23 0 1 0 0 \n", "24 0 1 0 0 \n", "25 0 1 0 0 \n", "26 0 1 0 0 \n", "27 0 1 0 0 \n", "28 0 0 0 1 \n", "29 0 1 0 0 \n", "... ... ... ... ... \n", "16707 0 1 0 0 \n", "16708 0 0 0 1 \n", "16709 0 1 0 0 \n", "16710 0 1 0 0 \n", "16711 0 1 0 0 \n", "16712 0 1 0 0 \n", "16713 0 1 0 0 \n", "16714 0 1 0 0 \n", "16715 0 1 0 0 \n", "16716 0 1 0 0 \n", "16717 0 1 0 0 \n", "16718 0 1 0 0 \n", "16719 0 1 0 0 \n", "16720 0 1 0 0 \n", "16721 0 1 0 0 \n", "16722 0 0 0 1 \n", "16723 0 1 0 0 \n", "16724 0 1 0 0 \n", "16725 0 1 0 0 \n", "16726 0 0 1 0 \n", "16727 0 0 1 0 \n", "16728 0 1 0 0 \n", "16729 0 1 0 0 \n", "16730 0 1 0 0 \n", "16731 0 0 1 0 \n", "16732 0 1 0 0 \n", "16733 0 1 0 0 \n", "16734 0 1 0 0 \n", "16735 0 1 0 0 \n", "16736 0 1 0 0 \n", "\n", "[6074 rows x 53 columns]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "fg.query('Year==2021')" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "ename": "SyntaxError", "evalue": "unexpected EOF while parsing (, line 1)", "output_type": "error", "traceback": [ "\u001b[1;36m File \u001b[1;32m\"\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m Ward1.query('ElectionDescription in (\"11/08/2016 - STATE GENERAL\", \"08/09/2016 - STATE PRIMARY\",\"11/07/2017 - MUNICIPAL GENERAL\",\"11/07/2017 - SCHOOL DISTRICT GENERAL\" ,\"11/08/2016 - STATE GENERAL\",\"08/09/2016 - STATE PRIMARY\",\"11/03/2015 - MUNICIPAL GENERAL\",\"11/03/2015 - SCHOOL DISTRICT GENERAL\",\"11/08/2011 - MUNICIPAL GENERAL\",\"11/06/2018 - STATE GENERAL\",\"08/14/2018 - STATE PRIMARY\")'\u001b[0m\n\u001b[1;37m ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m unexpected EOF while parsing\n" ] } ], "source": [ "Ward1.query('ElectionDescription in (\"11/08/2016 - STATE GENERAL\", \"08/09/2016 - STATE PRIMARY\",\"11/07/2017 - MUNICIPAL GENERAL\",\"11/07/2017 - SCHOOL DISTRICT GENERAL\" ,\"11/08/2016 - STATE GENERAL\",\"08/09/2016 - STATE PRIMARY\",\"11/03/2015 - MUNICIPAL GENERAL\",\"11/03/2015 - SCHOOL DISTRICT GENERAL\",\"11/08/2011 - MUNICIPAL GENERAL\",\"11/06/2018 - STATE GENERAL\",\"08/14/2018 - STATE PRIMARY\")' " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.12.3" } }, "nbformat": 4, "nbformat_minor": 4 }