initial commit

2 files changed, 149 insertions, 0 deletions

diff --git a/scripts/dashboard.py b/scripts/dashboard.py
new file mode 100644
index 0000000..23e72c4
--- /dev/null
+++ b/scripts/dashboard.py
@@ -0,0 +1,76 @@
+from dash import Dash, html, dcc, callback, Output, Input
+import plotly.express as px
+import pandas as pd
+import sqlite3
+
+# Connect to database and query all incidents
+connection = sqlite3.connect("../raw_data/ingress.db")
+cursor = connection.cursor()
+query = "SELECT * FROM incidents;"
+df = pd.read_sql_query(query, connection).sort_values(by="description")
+
+# Create custom YEAR column to use in dropdown
+df['year'] = df['date'].str[-4:]
+
+# Configure HTML layout
+app = Dash(__name__)
+app.layout = html.Div(children = [
+    html.Div([
+        html.H1(children="Omaha Police Invidents", style={"textAlign":"center"}),
+        dcc.Dropdown(df.sort_values("description").description.unique(), "INJURY", id="bar-dropdown"),
+        dcc.Dropdown(df.sort_values("year").year.unique(), "2023", id="bar-year-dropdown"),
+        dcc.Graph(id="bar-graph")
+    ]),
+    html.Div([
+        html.H2(children="Map Coordinates", style={"textAlign":"center"}),
+        dcc.Dropdown(df.sort_values("description").description.unique(), "INJURY", id="map-dropdown"),
+        dcc.Dropdown(df.sort_values("year").year.unique(), "2023", id="map-year-dropdown"),
+        dcc.Graph(id="map-graph")
+    ])
+])
+
+# Create bar graph
+@callback(
+    Output("bar-graph", "figure"),
+    Input("bar-dropdown", "value"),
+    Input("bar-year-dropdown", "value")
+)
+def update_bar_graph(description, year):
+    dff = df[df.year == year]
+    dff = dff.value_counts(subset=["description"])
+    dff = dff.reset_index()
+    dff = dff[dff.description == description]
+    return px.bar(dff, x="description", y="count")
+
+# Create map
+@callback(
+    Output("map-graph", "figure"),
+    Input("map-dropdown", "value"),
+    Input("map-year-dropdown", "value")
+)
+def update_map(description, year):
+    dff = df[df.year == year]
+    dff = dff.reset_index()
+    dff = dff[dff.description == description]
+
+    fig = px.scatter_mapbox(
+        dff,
+        lat="lat",
+        lon="lon",
+        color="description",
+        hover_name="description",
+        hover_data=["date", "time"],
+        title="Incident Count by Coordinates",
+        center={"lat": 41.257160, "lon": -95.995102},
+        zoom=10
+    )
+    
+    fig.update_layout(showlegend=False)
+    fig.update_layout(mapbox_style="open-street-map")
+    fig.update_layout(margin={"r": 0, "t": 0, "l": 0, "b": 0})
+    fig.update_layout(mapbox_bounds={"west": -180, "east": -50, "south": 20, "north": 90})
+
+    return fig
+
+if __name__ == "__main__":
+    app.run(debug=True)
diff --git a/scripts/load.py b/scripts/load.py
new file mode 100644
index 0000000..ea362cb
--- /dev/null
+++ b/scripts/load.py
@@ -0,0 +1,73 @@
+# Import required modules
+import csv
+import sqlite3
+import os
+
+# Create the database file
+connection = sqlite3.connect('../raw_data/ingress.db')
+
+# Creating a cursor object to execute SQL queries
+cursor = connection.cursor()
+
+# Table Definition
+# rb          = RB Number
+# date        = Reported Date
+# time        = Reported Time
+# description = Statute/Ordinance Description
+# location    = Occurred Location
+# district    = Occurred District
+# lat         = Occurred Block LAT
+# lon         = Occurred Block LON
+create_table = '''CREATE TABLE incidents(
+				id INTEGER PRIMARY KEY AUTOINCREMENT,
+                rb TEXT NOT NULL,
+				date TEXT NOT NULL,
+				time TEXT NOT NULL,
+                description TEXT NOT NULL,
+                location TEXT NOT NULL,
+                district TEXT NOT NULL,
+                lat REAL NOT NULL,
+                lon REAL NOT NULL);
+				'''
+
+# Create the table
+cursor.execute(create_table)
+
+# Point to the data directory
+directory = os.fsencode("../raw_data/")
+
+# Loop through all raw data files
+for file in os.listdir(directory):
+    filename = os.fsdecode(file)
+    if filename.endswith(".csv"): 
+        # Opening the file
+        file = open("../raw_data/" + filename)
+
+        # Reading the contents of the file
+        contents = csv.reader(file)
+
+        # SQL query to insert data into the
+        # table
+        insert_records = "INSERT INTO incidents (rb, date, time, description, location, district, lat, lon) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
+
+        # Importing the contents of the file 
+        # into our table
+        cursor.executemany(insert_records, contents)
+        print("Inserted data from: ", filename)
+        continue
+    else:
+        continue
+
+# Test query to see if the data loaded
+select_all = "SELECT * FROM incidents"
+rows = cursor.execute(select_all).fetchall()
+
+# Output to the console screen
+for r in rows:
+	print(r)
+
+# Commit the changes
+connection.commit()
+
+# Close the database connection
+connection.close()