Py.Cafe

import pandas as pd
from dash import Dash, html, Input, Output, dcc
import dash_bootstrap_components as dbc
import plotly.express as px
import dash_ag_grid as dag

# US state abbreviation mapping
us_state_abbrev = {
    'AL': 'Alabama', 'AK': 'Alaska', 'AZ': 'Arizona', 'AR': 'Arkansas',
    'CA': 'California', 'CO': 'Colorado', 'CT': 'Connecticut', 'DE': 'Delaware',
    'FL': 'Florida', 'GA': 'Georgia', 'HI': 'Hawaii', 'ID': 'Idaho',
    'IL': 'Illinois', 'IN': 'Indiana', 'IA': 'Iowa', 'KS': 'Kansas',
    'KY': 'Kentucky', 'LA': 'Louisiana', 'ME': 'Maine', 'MD': 'Maryland',
    'MA': 'Massachusetts', 'MI': 'Michigan', 'MN': 'Minnesota', 'MS': 'Mississippi',
    'MO': 'Missouri', 'MT': 'Montana', 'NE': 'Nebraska', 'NV': 'Nevada',
    'NH': 'New Hampshire', 'NJ': 'New Jersey', 'NM': 'New Mexico', 'NY': 'New York',
    'NC': 'North Carolina', 'ND': 'North Dakota', 'OH': 'Ohio', 'OK': 'Oklahoma',
    'OR': 'Oregon', 'PA': 'Pennsylvania', 'RI': 'Rhode Island', 'SC': 'South Carolina',
    'SD': 'South Dakota', 'TN': 'Tennessee', 'TX': 'Texas', 'UT': 'Utah',
    'VT': 'Vermont', 'VA': 'Virginia', 'WA': 'Washington', 'WV': 'West Virginia',
    'WI': 'Wisconsin', 'WY': 'Wyoming', 'DC': 'District of Columbia'
}

# Load and clean data
df = pd.read_csv("violations_filtered.csv")
df = df.drop_duplicates()

# Use "in progress" instead of "unknown"
df['Violation Status'] = df['Violation Status'].fillna("in progress")
df['Violation Status'] = df['Violation Status'].replace("unknown", "in progress")

df['Issue Date'] = pd.to_datetime(df['Issue Date'])
df['Day of Week'] = df['Issue Date'].dt.day_name()

def extract_hour(time_str):
    try:
        if pd.isna(time_str):
            return None
        t = time_str.strip().upper()
        if t[-1] in ['A', 'P']:
            hour = int(t.split(':')[0])
            if t[-1] == 'P' and hour != 12:
                hour += 12
            if t[-1] == 'A' and hour == 12:
                hour = 0
            return hour
        return int(t.split(':')[0])
    except Exception:
        return None

df['Violation Hour'] = df['Violation Time'].apply(extract_hour)

# Add full state name column
state_col = 'Plate State' if 'Plate State' in df.columns else 'State'
if state_col not in df.columns:
    raise Exception("No state column found! Please add a 'State' or 'Plate State' column.")
df['State Full'] = df[state_col].map(us_state_abbrev).fillna(df[state_col])

def dbc_select_options(col):
    opts = [{"label": "All", "value": "ALL"}]
    opts += [{"label": str(val), "value": str(val)} for val in sorted(df[col].dropna().unique())]
    return opts

def state_dropdown_options(col):
    opts = [{"label": "All", "value": "ALL"}]
    opts += [
        {
            "label": us_state_abbrev.get(val, val),
            "value": val
        }
        for val in sorted(df[col].dropna().unique())
    ]
    return opts

state_options = state_dropdown_options(state_col)
violation_options = dbc_select_options('Violation')

tab_style = {
    "padding": "12px 24px",
    "fontWeight": "bold",
    "background": "transparent",
    "color": "#fff",
    "border": "none",
    "borderBottom": "3px solid transparent",
    "fontSize": "1.1rem",
    "marginRight": "8px",
    "marginBottom": "0px",
    "textAlign": "center",
    "transition": "border-bottom 0.2s"
}
tab_selected_style = {
    **tab_style,
    "color": "#98ACED",
    "borderBottom": "3px solid #98ACED",
    "background": "transparent"
}

YELLOW_PALETTE = [
    "#fde725", "#ffd600", "#ffe066", "#fff59d", "#fffde7",
    "#ffe082", "#ffd54f", "#ffecb3", "#fff9c4", "#fffde7"
]

def make_aggrid(dataframe):
    # Choose your important columns here (adjust names as needed)
    important_cols = []
    for col in [
        "Plate", "Plate Number", "Plate State", "State", "Violation",
        "Violation Status", "Fine Amount", "Issue Date", "Amount Due",
        "Violation Time", "Day of Week", "Issuing Agency", "Street Name",
        "Vehicle Body Type", "Vehicle Make", "Vehicle Expiration Date"
    ]:
        if col in dataframe.columns:
            important_cols.append(col)
    # Pick the first 10 unique ones
    important_cols = list(dict.fromkeys(important_cols))[:10]
    if not important_cols:
        important_cols = dataframe.columns[:10]
    return dag.AgGrid(
        rowData=dataframe[important_cols].to_dict("records"),
        columnDefs=[
            {
                "field": col,
                "filter": True,
                "sortable": True,
                "hide": False,
                "checkboxSelection": True if i == 0 else False,
                "headerCheckboxSelection": True if i == 0 else False
            }
            for i, col in enumerate(important_cols)
        ],
        dashGridOptions={
            "pagination": True,
            "domLayout": "autoHeight",
            "sideBar": {
                "toolPanels": [
                    {
                        "id": "columns",
                        "labelDefault": "Columns",
                        "labelKey": "columns",
                        "iconKey": "columns",
                        "toolPanel": "agColumnsToolPanel",
                        "toolPanelParams": {
                            "suppressRowGroups": True,
                            "suppressValues": True,
                            "suppressPivots": True,
                            "suppressPivotMode": True
                        }
                    }
                ],
                "defaultToolPanel": "columns",
                "position": "left"
            },
            "rowSelection": "multiple"
        },
        className="ag-theme-alpine-dark",
        style={"height": "600px", "width": "100%"}
    )

def kpi_card(title, value, icon, color):
    return dbc.Card(
        dbc.CardBody([
            html.Div([
                html.I(
                    className=f"bi {icon}",
                    style={
                        "fontSize": "2rem",
                        "color": color,
                        "position": "absolute",
                        "top": "12px",
                        "left": "16px"
                    }
                ),
                html.Div(title, style={
                    "fontWeight": "bold",
                    "fontSize": "1.1rem",
                    "color": "#fff",
                    "marginTop": "8px",
                    "marginLeft": "0px",
                    "textAlign": "center"
                }),
                html.H3(value, className="card-title", style={
                    "color": "#fff",
                    "fontWeight": "bold",
                    "margin": 0,
                    "marginTop": "8px",
                    "textAlign": "center"
                }),
            ],
            style={
                "position": "relative",
                "height": "100%",
                "display": "flex",
                "flexDirection": "column",
                "alignItems": "center",
                "justifyContent": "center"
            })
        ]),
        style={
            "backgroundColor": "#23272b",
            "borderRadius": "18px",
            "border": "none",
            "boxShadow": "none",
            "minWidth": "220px",
            "margin": "0 8px",
            "textAlign": "center"
        },
        className="mx-2"
    )

app = Dash(__name__, external_stylesheets=[
    dbc.themes.CYBORG,
    "https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css"
])

app.layout = dbc.Container([
    html.H2("Parking and Camera Violations Dashboard", className="mt-4 mb-4", style={"color": "white"}),
    dbc.Row([
        dbc.Col(width=3),
        dbc.Col([
            html.Label("Filter by State:", style={"color": "white"}),
            dbc.Select(
                id="state-select",
                options=state_options,
                value="ALL",
                style={
                    "backgroundColor": "#222831",
                    "color": "white",
                    "border": "1px solid #444",
                    "fontWeight": "bold",
                    "fontSize": "0.9rem"
                },
                className="mb-2"
            )
        ], width=3),
        dbc.Col([
            html.Label("Filter by Violation Type:", style={"color": "white"}),
            dbc.Select(
                id="violation-select",
                options=violation_options,
                value="ALL",
                style={
                    "backgroundColor": "#222831",
                    "color": "white",
                    "border": "1px solid #444",
                    "fontWeight": "bold",
                    "fontSize": "0.9rem"
                },
                className="mb-2"
            )
        ], width=3),
    ], justify="end"),
    html.Div(style={"height": "34px"}),
    dbc.Row([
        dbc.Col(
            dcc.Tabs(
                id="tabs",
                value="tab-1",
                children=[
                    dcc.Tab(
                        label="Violation Types & Times",
                        value="tab-1",
                        style=tab_style,
                        selected_style=tab_selected_style
                    ),
                    dcc.Tab(
                        label="Status & Amounts",
                        value="tab-2",
                        style=tab_style,
                        selected_style=tab_selected_style
                    ),
                    dcc.Tab(
                        label="Data Table",
                        value="tab-3",
                        style=tab_style,
                        selected_style=tab_selected_style
                    ),
                ],
                className="mb-0"
            ),
            width=12
        )
    ], className="mb-0"),
    html.Div(style={"height": "24px"}),
    dbc.Row([
        dbc.Col(
            dcc.Loading(
                id="loading-kpi",
                type="circle",
                color="#3FD927",
                children=html.Div(id="kpi-cards")
            ),
            width=12,
            className="mb-4"
        )
    ]),
    dbc.Row([
        dbc.Col(
            dcc.Loading(
                id="loading-tab",
                type="circle",
                color="#3FD927",
                children=html.Div(id="tab-content", className="mt-2")
            ),
            width=12
        ),
    ])
], fluid=True, style={"backgroundColor": "#181a1b", "minHeight": "100vh"})

def filter_all(df, state, violation):
    dff = df.copy()
    # Only show rows where Violation Status is not "in progress"
    dff = dff[dff['Violation Status'] != "in progress"]
    if state and state != "ALL":
        dff = dff[dff[state_col] == state]
    if violation and violation != "ALL":
        dff = dff[dff['Violation'] == violation]
    return dff

@app.callback(
    Output("kpi-cards", "children"),
    Input("state-select", "value"),
    Input("violation-select", "value"),
)
def update_kpis(state, violation):
    filtered = filter_all(df, state, violation)
    total_viol = len(filtered)
    total_fine = filtered['Fine Amount'].sum() if 'Fine Amount' in filtered.columns else 0
    unpaid = filtered['Amount Due'].sum() if 'Amount Due' in filtered.columns else 0

    # Unique Plates as FIRST KPI
    plate_col = 'Plate' if 'Plate' in filtered.columns else (
        'Plate Number' if 'Plate Number' in filtered.columns else None
    )
    if plate_col:
        unique_plates = filtered[plate_col].nunique()
    else:
        unique_plates = "N/A"

    return dbc.Row([
        dbc.Col(kpi_card("Unique Plates", f"{unique_plates:,}", "bi-car-front-fill", "#98ACED"), width=3),
        dbc.Col(kpi_card("Total Violations", f"{total_viol:,}", "bi-exclamation-triangle-fill", "#d6e80d"), width=3),
        dbc.Col(kpi_card("Total Fine Amount", f"${total_fine:,.0f}", "bi-cash-coin", "#3FD927"), width=3),
        dbc.Col(kpi_card("Unpaid Amount", f"${unpaid:,.0f}", "bi-credit-card-2-front-fill", "#246FDF"), width=3),
    ], className="justify-content-center")

@app.callback(
    Output("tab-content", "children"),
    Input("state-select", "value"),
    Input("violation-select", "value"),
    Input("tabs", "value")
)
def update_tab_charts(state, violation, tab):
    filtered = filter_all(df, state, violation)
    card_style = {
        "backgroundColor": "transparent",
        "borderRadius": "18px",
        "border": "none",
        "boxShadow": "none"
    }
    if tab == "tab-1":
        top_viol = filtered['Violation'].value_counts().nlargest(8)
        bar = px.bar(
            x=top_viol.index,
            y=top_viol.values,
            labels={'x': 'Violation Type', 'y': 'Count'},
            title='Violation Types by Count',
            template='plotly_dark',
            color_discrete_sequence=YELLOW_PALETTE
        )
        # Reverse days order for heatmap
        days_order = [
            'Sunday', 'Saturday', 'Friday', 'Thursday', 'Wednesday', 'Tuesday', 'Monday'
        ]
        heatmap_data = (
            filtered
            .dropna(subset=['Day of Week', 'Violation Hour'])
            .groupby(['Day of Week', 'Violation Hour'])
            .size()
            .reset_index(name='Count')
        )
        heatmap_data['Day of Week'] = pd.Categorical(
            heatmap_data['Day of Week'], categories=days_order, ordered=True
        )
        heatmap_data = heatmap_data.sort_values(['Day of Week', 'Violation Hour'])
        heatmap = px.density_heatmap(
            heatmap_data,
            x='Violation Hour',
            y='Day of Week',
            z='Count',
            color_continuous_scale='Viridis',
            title='Violations Heatmap by Day and Hour',
            nbinsx=24,
            template='plotly_dark'
        )
        heatmap.update_xaxes(dtick=1)
        return dbc.Row([
            dbc.Col(
                dbc.Card(
                    dcc.Graph(figure=bar),
                    body=True,
                    style=card_style
                ), width=6
            ),
            dbc.Col(
                dbc.Card(
                    dcc.Graph(figure=heatmap),
                    body=True,
                    style=card_style
                ), width=6
            ),
        ])
    elif tab == "tab-2":
        pie = px.pie(
            filtered,
            names='Violation Status',
            title='Violation Status Distribution',
            hole=0.8,
            template='plotly_dark'
        )
        if 'Fine Amount' in filtered.columns and 'Amount Due' in filtered.columns:
            scatter = px.scatter(
                filtered,
                x='Fine Amount',
                y='Amount Due',
                color='Violation Status',
                title='Fine Amount vs. Amount Due',
                template='plotly_dark'
            )
            scatter.update_traces(marker=dict(size=14))
        else:
            scatter = px.scatter(
                pd.DataFrame({'x': [], 'y': []}),
                x='x', y='y',
                title='Fine Amount vs. Amount Due',
                template='plotly_dark'
            )
        return dbc.Row([
            dbc.Col(
                dbc.Card(
                    dcc.Graph(figure=pie),
                    body=True,
                    style=card_style
                ), width=6
            ),
            dbc.Col(
                dbc.Card(
                    dcc.Graph(figure=scatter),
                    body=True,
                    style=card_style
                ), width=6
            ),
        ])
    elif tab == "tab-3":
        return make_aggrid(filtered)
    else:
        return html.Div("Select a tab.", style={"color": "white"})

if __name__ == "__main__":
    app.run()