dashboard_helpers.py

import geopandas as gpd
import ipywidgets as widgets
import leafmap

# Trip-volume choropleth
def build_trip_map(con, service, month, metric, cmap, legend_prefix):
    if metric == "pu":
        table, col = "trip_counts_pu", "trips_pu"
    else:
        table, col = "trip_counts_do", "trips_do"

    df = con.sql(f"""
        SELECT tz.zone, t.{col} AS trips,
               ST_AsText(ST_Transform(tz.geometry, 'EPSG:26918', 'OGC:CRS84')) AS geometry
        FROM {table} AS t
        JOIN taxi_zones_utm AS tz ON t.LocationID = tz.LocationID
        WHERE t.service = '{service}' AND t.month = '{month}'
    """).df()

    gdf = gpd.GeoDataFrame(
        df, geometry=gpd.GeoSeries.from_wkt(df["geometry"]), crs="EPSG:4326"
    )
    m = leafmap.Map(center=[40.7, -73.9], zoom=10, draw_control=False)
    m.layout.height = "400px"
    m.add_basemap("CartoDB.DarkMatter")
    m.add_data(
        gdf, column="trips", cmap=cmap,
        legend_title=f"{legend_prefix} ({month})",
    )
    return m

# Demographic baseline choropleth
def build_demo_map(con, column, baseline_val, legend_title):
    df = con.sql(f"""
        SELECT zd.TaxiZone AS zone,
               ROUND(zd.{column} - {baseline_val}, 1) AS deviation,
               ST_AsText(ST_Transform(tz.geometry, 'EPSG:26918', 'OGC:CRS84')) AS geometry
        FROM zone_demographics AS zd
        JOIN taxi_zones_utm AS tz ON zd.LocationID = tz.LocationID
        WHERE zd.TotalPop > 0
    """).df()

    gdf = gpd.GeoDataFrame(
        df, geometry=gpd.GeoSeries.from_wkt(df["geometry"]), crs="EPSG:4326"
    )
    m = leafmap.Map(center=[40.7, -73.9], zoom=10, draw_control=False)
    m.layout.height = "400px"
    m.add_basemap("CartoDB.DarkMatter")
    m.add_data(
        gdf, column="deviation", cmap="RdYlBu",
        legend_title=legend_title,
    )
    return m

# Top-10 stats table
def build_stats(con, service, month, metric, cmap, label):
    if metric == "pu":
        table, col, alias = "trip_counts_pu", "trips_pu", "Pickups"
    else:
        table, col, alias = "trip_counts_do", "trips_do", "Dropoffs"

    df = con.sql(f"""
        SELECT tz.zone AS Neighborhood, t.{col} AS {alias}
        FROM {table} t
        JOIN taxi_zones_utm tz ON t.LocationID = tz.LocationID
        WHERE t.service = '{service}' AND t.month = '{month}'
        ORDER BY t.{col} DESC LIMIT 10
    """).df()

    header = widgets.HTML(
        f"<h3 style='text-align:center;'>{service} Top 10 {label} ({month})</h3>"
    )
    styled = df.style.background_gradient(cmap=cmap, subset=[alias]).hide(axis="index")
    return header, styled