From 38515b5c270ec9a668ff912878530d7aa180b697 Mon Sep 17 00:00:00 2001 From: Christian Cleberg Date: Wed, 24 Jan 2024 11:01:36 -0600 Subject: fix: restructure project around the dash app --- README.md | 10 +++++++ app.py | 77 ++++++++++++++++++++++++++++++++++++++++++++++++++++ assets/styles.css | 8 ++++++ ingest.py | 73 +++++++++++++++++++++++++++++++++++++++++++++++++ scripts/dashboard.py | 76 --------------------------------------------------- scripts/load.py | 73 ------------------------------------------------- 6 files changed, 168 insertions(+), 149 deletions(-) create mode 100644 app.py create mode 100644 assets/styles.css create mode 100644 ingest.py delete mode 100644 scripts/dashboard.py delete mode 100644 scripts/load.py diff --git a/README.md b/README.md index 72d36f9..6610791 100644 --- a/README.md +++ b/README.md @@ -10,3 +10,13 @@ Data from the Omaha police department, used to analyze and visualize statistics. - [ ] Analysis script - [~] Visualization script - [ ] Build API to connect to database? + +## API + +For the time being, you can explore the database via API with +[sqlite2rest](https://github.com/nside/sqlite2rest): + +```sh +pip3 install sqlite2rest +sqlite2rest serve ./raw_data/ingress.db +``` diff --git a/app.py b/app.py new file mode 100644 index 0000000..15a0a44 --- /dev/null +++ b/app.py @@ -0,0 +1,77 @@ +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.H1(children="Omaha Police Invidents", style={"textAlign":"center"}), + html.Div([ + html.H2(children="Totals per Category and Year", 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 of Incidents per Category and Year", 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/assets/styles.css b/assets/styles.css new file mode 100644 index 0000000..dd887e6 --- /dev/null +++ b/assets/styles.css @@ -0,0 +1,8 @@ +body { + font-family: -apple-system, BlinkMacSystemFont, avenir next, avenir, segoe ui, helvetica neue, helvetica, Cantarell, Ubuntu, roboto, noto, arial, sans-serif; +} + +#_dash-app-content { + max-width: 80vw; + margin: 2rem auto; +} \ No newline at end of file diff --git a/ingest.py b/ingest.py new file mode 100644 index 0000000..daa7041 --- /dev/null +++ b/ingest.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() diff --git a/scripts/dashboard.py b/scripts/dashboard.py deleted file mode 100644 index 23e72c4..0000000 --- a/scripts/dashboard.py +++ /dev/null @@ -1,76 +0,0 @@ -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 deleted file mode 100644 index ea362cb..0000000 --- a/scripts/load.py +++ /dev/null @@ -1,73 +0,0 @@ -# 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() -- cgit v1.2.3-70-g09d2