{"cells": [{"cell_type": "markdown", "id": "302934307671667531413257853548643485645", "metadata": {}, "source": ["# Gradio Demo: chicago-bikeshare-dashboard"]}, {"cell_type": "code", "execution_count": null, "id": "272996653310673477252411125948039410165", "metadata": {}, "outputs": [], "source": ["!pip install -q gradio psycopg2 matplotlib SQLAlchemy pandas "]}, {"cell_type": "code", "execution_count": null, "id": "288918539441861185822528903084949547379", "metadata": {}, "outputs": [], "source": ["import os\n", "import gradio as gr\n", "import pandas as pd\n", "\n", "DB_USER = os.getenv(\"DB_USER\")\n", "DB_PASSWORD = os.getenv(\"DB_PASSWORD\")\n", "DB_HOST = os.getenv(\"DB_HOST\")\n", "PORT = 8080\n", "DB_NAME = \"bikeshare\"\n", "\n", "connection_string = (\n", "    f\"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}?port={PORT}&dbname={DB_NAME}\"\n", ")\n", "\n", "def get_count_ride_type():\n", "    df = pd.read_sql(\n", "        \"\"\"\n", "        SELECT COUNT(ride_id) as n, rideable_type\n", "        FROM rides\n", "        GROUP BY rideable_type\n", "        ORDER BY n DESC\n", "    \"\"\",\n", "        con=connection_string,\n", "    )\n", "    return df\n", "\n", "def get_most_popular_stations():\n", "\n", "    df = pd.read_sql(\n", "        \"\"\"\n", "    SELECT COUNT(ride_id) as n, MAX(start_station_name) as station\n", "    FROM RIDES\n", "    WHERE start_station_name is NOT NULL\n", "    GROUP BY start_station_id\n", "    ORDER BY n DESC\n", "    LIMIT 5\n", "    \"\"\",\n", "        con=connection_string,\n", "    )\n", "    return df\n", "\n", "with gr.Blocks() as demo:\n", "    gr.Markdown(\n", "        \"\"\"\n", "    # Chicago Bike Share Dashboard\n", "    \n", "    This demo pulls Chicago bike share data for March 2022 from a postgresql database hosted on AWS.\n", "    This demo uses psycopg2 but any postgresql client library (SQLAlchemy)\n", "    is compatible with gradio.\n", "    \n", "    Connection credentials are handled by environment variables\n", "    defined as secrets in the Space.\n", "\n", "    If data were added to the database, the plots in this demo would update\n", "    whenever the webpage is reloaded.\n", "    \n", "    This demo serves as a starting point for your database-connected apps!\n", "    \"\"\"\n", "    )\n", "    with gr.Row():\n", "        bike_type = gr.BarPlot(\n", "            x=\"rideable_type\",\n", "            y='n',\n", "            title=\"Number of rides per bicycle type\",\n", "            y_title=\"Number of Rides\",\n", "            x_title=\"Bicycle Type\",\n", "            vertical=False,\n", "            tooltip=['rideable_type', \"n\"],\n", "            height=300,\n", "            width=300,\n", "        )\n", "        station = gr.BarPlot(\n", "            x='station',\n", "            y='n',\n", "            title=\"Most Popular Stations\",\n", "            y_title=\"Number of Rides\",\n", "            x_title=\"Station Name\",\n", "            vertical=False,\n", "            tooltip=['station', 'n'],\n", "            height=300,\n", "            width=300\n", "        )\n", "\n", "    demo.load(get_count_ride_type, inputs=None, outputs=bike_type)\n", "    demo.load(get_most_popular_stations, inputs=None, outputs=station)\n", "\n", "if __name__ == \"__main__\":\n", "    demo.launch()\n"]}], "metadata": {}, "nbformat": 4, "nbformat_minor": 5}