add line graph and date range selector

6 files changed, 100 insertions, 31 deletions

diff --git a/README.md b/README.md
index 1ba973d..7289ae9 100644
--- a/README.md
+++ b/README.md
@@ -1,4 +1,4 @@
-# Omaha Incidents
+# Omaha Crime Mapping & Analysis
 
 Data from the Omaha police department, used to analyze and visualize statistics.
 
@@ -22,4 +22,5 @@ sqlite2rest serve ./raw_data/ingress.db
 
 ## Screenshots
 
-![](./screenshots/dashboard.png)
+![](./screenshots/dashboard_01.png)
+![](./screenshots/dashboard_02.png)
diff --git a/app.py b/app.py
index ec05fcf..099c317 100644
--- a/app.py
+++ b/app.py
@@ -1,7 +1,9 @@
 from dash import Dash, html, dcc, callback, Output, Input, dash_table
 import plotly.express as px
 import pandas as pd
+import numpy as np
 import sqlite3
+from datetime import datetime, date
 
 # Connect to database and query all incidents
 connection = sqlite3.connect("./raw_data/ingress.db")
@@ -9,44 +11,51 @@ 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:]
+# Replace empty cells with NaN
+df = df.replace(r'^\s*$', np.nan, regex=True)
+
+# Convert date column to datetime
+# TODO: Create a combined datetime column in the db to load/convert here
+df["date"] = pd.to_datetime(df["date"])
 
 # Configure HTML layout
 app = Dash(__name__)
 app.layout = html.Div(children = [
-    html.H1(children="Omaha Police Incidents", style={"textAlign":"center"}),
+    html.H1(children="Omaha Crime Mapping & Analysis", 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="dropdown"),
-        dcc.Dropdown(df.sort_values("year").year.unique(), "2023", id="year-dropdown"),
-        html.Hr(),
-        dash_table.DataTable(data=df.to_dict("records"), page_size=5, id="table"),
-        html.H2(children="Map of Incidents per Category and Year", style={"textAlign":"center"}),
-        dcc.Graph(id="map-graph")
+        html.Div(className="flex",
+            children = [
+                html.P("Crime:"),
+                dcc.Dropdown(df.sort_values("description").description.unique(), "INJURY", id="dropdown"),
+            ]
+        ),
+        html.Div(className="flex",
+            children = [
+                html.P("Date Range:"),
+                dcc.DatePickerRange(
+                    id='date-picker-range',
+                    min_date_allowed=date(2015, 1, 1),
+                    max_date_allowed=date(2023, 12, 31),
+                    start_date=date(2015, 1, 1),
+                    end_date=date(2023,12,31)
+                ),
+            ]
+        ),
+        dcc.Graph(id="map-graph"),
+        dcc.Graph(id="line-graph"),
+        dash_table.DataTable(data=df.to_dict("records"), page_size=5, id="table")
     ])
 ])
 
-# Create table
-@callback(
-    Output("table", "data"),
-    Input("dropdown", "value"),
-    Input("year-dropdown", "value")
-)
-def update_table(description, year):
-    dff = df[df.year == year]
-    dff = dff.reset_index()
-    dff = dff[dff.description == description]
-    return dff.to_dict("records")
-
 # Create map
 @callback(
     Output("map-graph", "figure"),
     Input("dropdown", "value"),
-    Input("year-dropdown", "value")
+    Input('date-picker-range', 'start_date'),
+    Input('date-picker-range', 'end_date')
 )
-def update_map(description, year):
-    dff = df[df.year == year]
+def update_map(description, start_date, end_date):
+    dff = df[(df['date'] > start_date) & (df['date'] < end_date)]
     dff = dff.reset_index()
     dff = dff[dff.description == description]
 
@@ -54,7 +63,6 @@ def update_map(description, year):
         dff,
         lat="lat",
         lon="lon",
-        color="description",
         hover_name="description",
         hover_data=["date", "time"],
         title="Incident Count by Coordinates",
@@ -69,5 +77,40 @@ def update_map(description, year):
 
     return fig
 
+# Create line graph
+@callback(
+    Output("line-graph", "figure"),
+    Input("dropdown", "value"),
+    Input('date-picker-range', 'start_date'),
+    Input('date-picker-range', 'end_date')
+)
+def update_line(description, start_date, end_date):
+    dff = df[(df['date'] > start_date) & (df['date'] < end_date)]
+    dff = dff.reset_index()
+    dff = dff[dff.description == description]
+    dff = dff.groupby(by="date").count()
+    dff = dff.reset_index()
+
+    fig = px.line(
+        dff,
+        x="date",
+        y="description",
+    )
+
+    return fig
+
+# Create table
+@callback(
+    Output("table", "data"),
+    Input("dropdown", "value"),
+    Input('date-picker-range', 'start_date'),
+    Input('date-picker-range', 'end_date')
+)
+def update_table(description, start_date, end_date):
+    dff = df[(df['date'] > start_date) & (df['date'] < end_date)]
+    dff = dff.reset_index()
+    dff = dff[dff.description == description]
+    return dff.to_dict("records")
+
 if __name__ == "__main__":
     app.run(debug=True)
diff --git a/assets/styles.css b/assets/styles.css
index d014936..97fd316 100644
--- a/assets/styles.css
+++ b/assets/styles.css
@@ -4,6 +4,31 @@ body {
     margin: 2rem auto;
 }
 
-hr {
-    margin: 1rem auto;
-}
\ No newline at end of file
+.dash-table-container {
+    overflow: scroll;
+}
+
+.dash-table-container,
+.dash-graph {
+    margin: 2rem 0;
+    max-width: 100%;
+}
+
+.flex {
+    display: flex;
+    flex-direction: row;
+    align-items: center;
+}
+
+.flex p {
+    margin-right: 1rem;
+    flex-shrink: 0;
+}
+
+.flex .dash-dropdown {
+    width: 100%;
+}
+
+.flex #date-picker-range {
+    width: 100%;
+}
diff --git a/screenshots/dashboard.png b/screenshots/dashboard.png
deleted file mode 100644
index 5a7c6aa..0000000
--- a/screenshots/dashboard.png
+++ /dev/null
diff --git a/screenshots/dashboard_01.png b/screenshots/dashboard_01.png
new file mode 100644
index 0000000..b0587e4
--- /dev/null
+++ b/screenshots/dashboard_01.png
diff --git a/screenshots/dashboard_02.png b/screenshots/dashboard_02.png
new file mode 100644
index 0000000..620f2fb
--- /dev/null
+++ b/screenshots/dashboard_02.png