krz/omaha-metro-blotter

  1{
  2 "cells": [
  3  {
  4   "cell_type": "markdown",
  5   "metadata": {},
  6   "source": [
  7    "# Omaha Incidents"
  8   ]
  9  },
 10  {
 11   "cell_type": "markdown",
 12   "metadata": {},
 13   "source": [
 14    "## Data Exploration\n",
 15    "\n",
 16    "Let\"s explore the data a little bit to see what kind of analysis and visualizations we want to implement."
 17   ]
 18  },
 19  {
 20   "cell_type": "markdown",
 21   "metadata": {},
 22   "source": [
 23    "### Set up environment\n",
 24    "\n",
 25    "Start by installating and importing the necessary packages. "
 26   ]
 27  },
 28  {
 29   "cell_type": "code",
 30   "execution_count": null,
 31   "metadata": {},
 32   "outputs": [],
 33   "source": [
 34    "# Install packages, if needed\n",
 35    "# !pip3 install ipykernel\n",
 36    "# !pip3 install --upgrade pandas plotly dash \"nbformat>=4.2.0\""
 37   ]
 38  },
 39  {
 40   "cell_type": "code",
 41   "execution_count": null,
 42   "metadata": {},
 43   "outputs": [],
 44   "source": [
 45    "# Import packages\n",
 46    "import pandas as pd\n",
 47    "import numpy as np\n",
 48    "import sqlite3\n",
 49    "import plotly.express as px\n",
 50    "import plotly.graph_objects as go\n",
 51    "import plotly.io as pio"
 52   ]
 53  },
 54  {
 55   "cell_type": "markdown",
 56   "metadata": {},
 57   "source": [
 58    "### Load Data\n",
 59    "\n",
 60    "To load the data, we need to connect to the SQLite3 database file and query it for the data we want."
 61   ]
 62  },
 63  {
 64   "cell_type": "code",
 65   "execution_count": null,
 66   "metadata": {},
 67   "outputs": [],
 68   "source": [
 69    "# Connect to the database\n",
 70    "connection = sqlite3.connect(\"../raw_data/ingress.db\")\n",
 71    "cursor = connection.cursor()"
 72   ]
 73  },
 74  {
 75   "cell_type": "code",
 76   "execution_count": null,
 77   "metadata": {},
 78   "outputs": [],
 79   "source": [
 80    "# If exists, delete extra header rows\n",
 81    "# delete_headers = \"DELETE FROM incidents WHERE rb = 'RB Number'\"\n",
 82    "# cursor.execute(delete_headers)"
 83   ]
 84  },
 85  {
 86   "cell_type": "code",
 87   "execution_count": null,
 88   "metadata": {},
 89   "outputs": [],
 90   "source": [
 91    "# Grab all data\n",
 92    "select_all = \"SELECT * FROM incidents\"\n",
 93    "df = pd.read_sql_query(select_all, connection)\n",
 94    "df.head()"
 95   ]
 96  },
 97  {
 98   "cell_type": "markdown",
 99   "metadata": {},
100   "source": [
101    "### Data Cleaning\n",
102    "\n",
103    "We will clean up the data before we use: inserting NaN, converting types, etc."
104   ]
105  },
106  {
107   "cell_type": "code",
108   "execution_count": null,
109   "metadata": {},
110   "outputs": [],
111   "source": [
112    "# Replace empty cells in [lat, lon] with NaN\n",
113    "df = df.replace(r'^\\s*$', np.nan, regex=True)\n",
114    "df.head()"
115   ]
116  },
117  {
118   "cell_type": "code",
119   "execution_count": null,
120   "metadata": {},
121   "outputs": [],
122   "source": [
123    "# Convert date col to datetime format\n",
124    "df[\"date\"] = pd.to_datetime(df[\"date\"])\n",
125    "df"
126   ]
127  },
128  {
129   "cell_type": "markdown",
130   "metadata": {},
131   "source": [
132    "### Plotting\n",
133    "\n",
134    "Let's test a plot that will show us the top categories of incidents."
135   ]
136  },
137  {
138   "cell_type": "code",
139   "execution_count": null,
140   "metadata": {},
141   "outputs": [],
142   "source": [
143    "# test plotting by sorting & plotting top 5 crime categories\n",
144    "s = dff.value_counts(subset=[\"description\"])\n",
145    "t = s.nlargest(5)\n",
146    "t.head()\n",
147    "t.plot(kind=\"bar\", title=\"Top 5 Incident Categories\")"
148   ]
149  },
150  {
151   "cell_type": "markdown",
152   "metadata": {},
153   "source": [
154    "### Data Filtering\n",
155    "\n",
156    "To reduce the workload in this rest of this notebook, I am filtering just for one description and a range of dates.\n",
157    "\n",
158    "If you are doing a lot of analysis, I recommend modifying the query at the beginning to only the pull the data you need instead of filtering after querying."
159   ]
160  },
161  {
162   "cell_type": "code",
163   "execution_count": null,
164   "metadata": {},
165   "outputs": [],
166   "source": [
167    "# Create a smaller dataframe based on a selected date and description\n",
168    "start_date = \"2023-01-01\"\n",
169    "end_date = \"2023-12-31\"\n",
170    "description = \"INJURY\"\n",
171    "\n",
172    "dff = df[(df['date'] > start_date) & (df['date'] < end_date)]\n",
173    "dff = dff.reset_index()\n",
174    "dff = dff[dff.description == description]\n",
175    "\n",
176    "dff_grouped = dff.groupby(by=\"date\").count()\n",
177    "dff_grouped = dff_grouped.reset_index()\n",
178    "\n",
179    "print(dff.head())\n",
180    "print(dff_grouped.head())"
181   ]
182  },
183  {
184   "cell_type": "code",
185   "execution_count": null,
186   "metadata": {},
187   "outputs": [],
188   "source": [
189    "dff.size"
190   ]
191  },
192  {
193   "cell_type": "code",
194   "execution_count": null,
195   "metadata": {},
196   "outputs": [],
197   "source": [
198    "dff.info()"
199   ]
200  },
201  {
202   "cell_type": "markdown",
203   "metadata": {},
204   "source": [
205    "### Mapping\n",
206    "\n",
207    "Let's create a geo map of the crime data."
208   ]
209  },
210  {
211   "cell_type": "code",
212   "execution_count": null,
213   "metadata": {},
214   "outputs": [],
215   "source": [
216    "fig = px.scatter_mapbox(\n",
217    "    dff,\n",
218    "    lat=\"lat\",\n",
219    "    lon=\"lon\",\n",
220    "    color=\"description\",\n",
221    "    hover_name=\"description\",\n",
222    "    hover_data=[\"date\", \"time\"],\n",
223    "    title=\"Incident Count by Coordinates\",\n",
224    "    center={\"lat\": 41.257160, \"lon\": -95.995102},\n",
225    "    zoom=10\n",
226    ")\n",
227    "\n",
228    "# fig.update_layout(showlegend=False)\n",
229    "fig.update_layout(mapbox_style=\"open-street-map\")\n",
230    "fig.update_layout(margin={\"r\": 0, \"t\": 0, \"l\": 0, \"b\": 0})\n",
231    "fig.update_layout(mapbox_bounds={\"west\": -180, \"east\": -50, \"south\": 20, \"north\": 90})\n",
232    "fig.show()"
233   ]
234  },
235  {
236   "cell_type": "code",
237   "execution_count": null,
238   "metadata": {},
239   "outputs": [],
240   "source": [
241    "# Optionally, save the figure to an HTML file\n",
242    "# pio.write_html(fig, file=\"test.html\", auto_open=True)"
243   ]
244  },
245  {
246   "cell_type": "markdown",
247   "metadata": {},
248   "source": [
249    "## Wrapping Up\n",
250    "\n",
251    "To finish, remember to close your database connections and save any data you need."
252   ]
253  },
254  {
255   "cell_type": "code",
256   "execution_count": null,
257   "metadata": {},
258   "outputs": [],
259   "source": [
260    "# clean up and close out the database\n",
261    "connection.commit()\n",
262    "connection.close()"
263   ]
264  }
265 ],
266 "metadata": {
267  "kernelspec": {
268   "display_name": "Python 3 (ipykernel)",
269   "language": "python",
270   "name": "python3"
271  },
272  "language_info": {
273   "codemirror_mode": {
274    "name": "ipython",
275    "version": 3
276   },
277   "file_extension": ".py",
278   "mimetype": "text/x-python",
279   "name": "python",
280   "nbconvert_exporter": "python",
281   "pygments_lexer": "ipython3",
282   "version": "3.11.7"
283  }
284 },
285 "nbformat": 4,
286 "nbformat_minor": 4
287}