1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
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")
cursor = connection.cursor()
query = "SELECT * FROM incidents;"
df = pd.read_sql_query(query, connection).sort_values(by="description")
# 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 Crime Mapping & Analysis", style={"textAlign":"center"}),
html.Div([
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 map
@callback(
Output("map-graph", "figure"),
Input("dropdown", "value"),
Input('date-picker-range', 'start_date'),
Input('date-picker-range', 'end_date')
)
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]
fig = px.scatter_mapbox(
dff,
lat="lat",
lon="lon",
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
# 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)
|
