Upload 6 files

data/Dockerfile

@@ -0,0 +1,38 @@
+FROM rocker/shiny:4.5.2
+
+# System dependencies for querychat/duckdb
+RUN apt-get update && apt-get install -y \
+    libcurl4-openssl-dev \
+    libssl-dev \
+    libxml2-dev \
+    && rm -rf /var/lib/apt/lists/*
+
+WORKDIR /app
+
+# Install remotes for version-pinned installation
+RUN R -e "install.packages('remotes', repos='https://cloud.r-project.org')"
+
+# Install CRAN packages with pinned versions
+RUN R -e "remotes::install_version('shiny', version = '1.12.1', repos = 'https://cloud.r-project.org')"
+RUN R -e "remotes::install_version('bslib', version = '0.10.0', repos = 'https://cloud.r-project.org')"
+RUN R -e "remotes::install_version('DT', version = '0.34.0', repos = 'https://cloud.r-project.org')"
+RUN R -e "remotes::install_version('dplyr', version = '1.2.0', repos = 'https://cloud.r-project.org')"
+RUN R -e "remotes::install_version('RSQLite', version = '2.4.5', repos = 'https://cloud.r-project.org')"
+RUN R -e "remotes::install_version('bsicons', version = '0.1.2', repos = 'https://cloud.r-project.org')"
+
+# Install r-universe packages with pinned versions (querychat and dependencies)
+RUN R -e "remotes::install_version('ellmer', version = '0.4.0', repos = c('https://posit-dev.r-universe.dev', 'https://cloud.r-project.org'))"
+RUN R -e "remotes::install_version('shinychat', version = '0.3.0', repos = c('https://posit-dev.r-universe.dev', 'https://cloud.r-project.org'))"
+RUN R -e "remotes::install_version('querychat', version = '0.2.0', repos = c('https://posit-dev.r-universe.dev', 'https://cloud.r-project.org'))"
+
+# Copy application code
+COPY app.R app.R
+COPY data/ data/
+
+# Verify critical packages are available
+RUN R -e "stopifnot(requireNamespace('shiny', quietly = TRUE))" \
+    && R -e "stopifnot(requireNamespace('querychat', quietly = TRUE))"
+
+EXPOSE 7860
+
+CMD ["R", "--quiet", "-e", "shiny::runApp('/app', host='0.0.0.0', port=7860)"]

data/README.md

@@ -0,0 +1,117 @@
+---
+sdk: docker
+app_port: 7860
+title: ISA 401 OEWS Jobs Explorer
+emoji: 📊
+colorFrom: yellow
+colorTo: yellow
+pinned: false
+license: mit
+short_description: Explore U.S. occupational employment and wage data
+---
+
+# ISA 401 OEWS Jobs Explorer
+
+**Your AI-Powered Assistant for Exploring Occupational Employment and Wage Statistics**
+
+[![Live App](https://img.shields.io/badge/Live_App-Hugging_Face-yellow)](https://huggingface.co/spaces/fmegahed/querychat_demo)
+
+Explore the U.S. Bureau of Labor Statistics' Occupational Employment and Wage Statistics (OEWS) dataset using natural language queries.
+
+---
+
+## What is this app?
+
+This Shiny application uses AI-powered natural language processing to help you explore the OEWS dataset. Instead of writing SQL queries, simply ask questions in plain English and get instant results.
+
+**Example queries:**
+- "What are the top 10 highest paying occupations in Ohio?"
+- "Show me employment by industry for software developers"
+- "What is the median wage for nurses nationally?"
+- "Compare wages between California and Texas for data scientists"
+
+---
+
+## Dataset Information
+
+**Dataset:** Occupational Employment and Wage Statistics (OEWS) Survey (May 2024 Estimates)
+**Publisher:** U.S. Bureau of Labor Statistics (BLS), Department of Labor
+**Website:** https://www.bls.gov/oes/
+
+The OEWS program produces employment and wage estimates annually for over 800 occupations. These estimates are available for the nation as a whole, for individual states, and for metropolitan and nonmetropolitan areas.
+
+### Key Fields
+
+| Field | Description |
+|-------|-------------|
+| `occ_title` | Occupation title |
+| `area_title` | Geographic area name |
+| `tot_emp` | Total employment |
+| `a_mean` | Mean annual wage |
+| `a_median` | Median annual wage |
+| `h_mean` | Mean hourly wage |
+
+---
+
+## Features
+
+- **Natural Language Queries**: Ask questions in plain English
+- **SQL Transparency**: See the generated SQL for each query
+- **Interactive Data Table**: Sort, filter, and export results
+- **Miami University Theming**: Branded for ISA 401 course use
+
+---
+
+## Running Locally
+
+**With R:**
+```r
+# Install dependencies
+renv::restore()
+
+# Run the app
+shiny::runApp('.', host = '0.0.0.0', port = 7860)
+```
+
+**With Docker:**
+```bash
+# Build the image
+docker build -t oews-explorer .
+
+# Run with OpenAI API key
+docker run --rm -p 7860:7860 -e OPENAI_API_KEY=$OPENAI_API_KEY oews-explorer
+```
+
+---
+
+## Required Environment Variable
+
+This app requires an OpenAI API key to function:
+
+```bash
+export OPENAI_API_KEY="your-api-key-here"
+```
+
+On Hugging Face Spaces, set this as a secret in your Space settings.
+
+---
+
+## Technology Stack
+
+- **[Shiny](https://shiny.posit.co/)** - Web application framework for R
+- **[querychat](https://github.com/posit-dev/querychat)** - Natural language data querying
+- **[bslib](https://rstudio.github.io/bslib/)** - Bootstrap theming for Shiny
+- **[DT](https://rstudio.github.io/DT/)** - Interactive data tables
+
+---
+
+## Course Information
+
+This application was developed for **ISA 401** at **Miami University** to help students explore and understand labor market data using modern AI-powered tools.
+
+---
+
+## Data Source
+
+Bureau of Labor Statistics, U.S. Department of Labor. *Occupational Employment and Wage Statistics (OEWS), May 2024 Estimates.*
+https://www.bls.gov/oes/

data/app.R

@@ -0,0 +1,261 @@
+# ISA 401 OEWS Jobs Explorer
+# A Shiny app for exploring U.S. Occupational Employment and Wage Statistics
+# Built with querychat for natural language data querying
+
+library(shiny)
+
+# ---------------------------------------------------------------------------
+# Data & QueryChat setup
+# ---------------------------------------------------------------------------
+
+oews <- as.data.frame(readRDS("data/oews.rds"))
+
+# Pre-compute static stats for the Home tab
+n_occupations <- length(unique(oews$occ_title[oews$o_group == "detailed"]))
+n_areas       <- length(unique(oews$area_title))
+data_period   <- "May 2024"
+
+client <- ellmer::chat_openai(
+  model = "gpt-5-mini-2025-08-07",
+  credentials = function() { return(Sys.getenv("OPENAI_API_KEY")) }
+)
+
+qc <- querychat::querychat(
+  oews,
+  client = client,
+  greeting = "Welcome to Our ISA 401 Assistant for Understanding the OEWS Dataset",
+  extra_instructions = "data/extra_instructions.md",
+  data_description = "data/data_desc.md"
+)
+
+# ---------------------------------------------------------------------------
+# UI
+# ---------------------------------------------------------------------------
+
+ui <- bslib::page_navbar(
+  id = "navbar",
+  title = "OEWS Jobs Explorer",
+  theme = bslib::bs_theme(primary = "#C3142D"),
+  navbar_options = bslib::navbar_options(bg = "#C3142D"),
+  fillable = "Explorer",
+
+  sidebar = bslib::sidebar(
+    id = "sidebar",
+    width = 325,
+    shiny::conditionalPanel(
+      "input.navbar == 'Explorer'",
+      qc$ui()
+    ),
+    shiny::conditionalPanel(
+      "input.navbar == 'Home'",
+      tags$div(
+        class = "p-2",
+        tags$h5("Welcome!"),
+        tags$p("Use the tabs above to explore the data. The ",
+               tags$strong("Explorer"), " tab lets you query the OEWS dataset
+               using natural language."),
+        tags$hr(),
+        tags$p("Try asking things like:"),
+        tags$ul(
+          tags$li("Show me the top 10 highest paying occupations"),
+          tags$li("Filter to software developers in California"),
+          tags$li("What are the jobs with more than 1 million employees?")
+        )
+      )
+    ),
+    fillable = TRUE
+  ),
+
+  # ---------- Home Tab ----------
+  bslib::nav_panel(
+    title = "Home",
+    value = "Home",
+    icon = bsicons::bs_icon("house"),
+
+    # Hero card
+    bslib::card(
+      class = "bg-dark text-white border-0",
+      style = "background: linear-gradient(135deg, #C3142D 0%, #8B0E20 100%);",
+      bslib::card_body(
+        class = "text-center py-5",
+        tags$h1("OEWS Jobs Explorer", class = "display-4 fw-bold"),
+        tags$p(
+          class = "lead mt-3 mb-4",
+          "Explore U.S. Occupational Employment and Wage Statistics using natural language queries powered by AI"
+        ),
+        tags$p(class = "text-white-50 mb-4", "A Business Intelligence and Data Visualization Assignment for ISA 401 at Miami University"),
+        shiny::actionButton(
+          "go_explorer", "Start Exploring",
+          class = "btn btn-outline-light btn-lg px-4",
+          icon = shiny::icon("magnifying-glass")
+        )
+      )
+    ),
+
+    # App metadata row
+    bslib::layout_column_wrap(
+      width = 1 / 4,
+      fill = FALSE,
+      bslib::value_box(
+        title = "Version",
+        value = "0.1.0",
+        showcase = bsicons::bs_icon("tag"),
+        theme = "light"
+      ),
+      bslib::value_box(
+        title = "Last Updated",
+        value = "Feb 2026",
+        showcase = bsicons::bs_icon("calendar-check"),
+        theme = "light"
+      ),
+      bslib::value_box(
+        title = "By",
+        value = tags$span("Fadel M. Megahed", style = "font-size: 1em;"),
+        p("Miami University"),
+        showcase = bsicons::bs_icon("person"),
+        theme = "light"
+      ),
+      bslib::value_box(
+        title = "Data Period",
+        value = data_period,
+        p(format(n_occupations, big.mark = ","), " occupations \u00B7 ",
+          format(n_areas, big.mark = ","), " areas"),
+        showcase = bsicons::bs_icon("bar-chart-line"),
+        theme = "light"
+      )
+    ),
+
+    # Developer / About + Tutorial row
+    bslib::layout_columns(
+      col_widths = c(6, 6),
+      fill = FALSE,
+
+      bslib::card(
+        bslib::card_header(class = "fw-bold", bsicons::bs_icon("info-circle"), " About"),
+        bslib::card_body(
+          tags$p(
+            "This app uses the ",
+            tags$a("querychat", href = "https://posit-dev.github.io/querychat/",
+                   target = "_blank"),
+            " R package to let you query the ",
+            tags$strong("OEWS May 2024"),
+            " dataset from the ",
+            tags$a("Bureau of Labor Statistics",
+                   href = "https://www.bls.gov/oes/tables.htm", target = "_blank"),
+            " using plain English. Under the hood, your questions are translated
+            to SQL by an OpenAI language model."
+          ),
+          tags$hr(),
+          tags$h6(class = "fw-bold mb-2", "Developer"),
+          tags$div(
+            class = "d-flex align-items-start gap-3",
+            bsicons::bs_icon("person-circle", size = "2em"),
+            tags$div(
+              tags$div(class = "fw-semibold", "Fadel M. Megahed"),
+              tags$small(class = "text-muted d-block",
+                         "Raymond E. Glos Professor, Farmer School of Business"),
+              tags$small(class = "text-muted d-block mb-2", "Miami University"),
+              tags$div(
+                class = "d-flex flex-wrap gap-2",
+                tags$a(class = "btn btn-outline-secondary btn-sm",
+                       href = "mailto:fmegahed@miamioh.edu",
+                       bsicons::bs_icon("envelope"), " Email"),
+                tags$a(class = "btn btn-outline-secondary btn-sm",
+                       href = "https://www.linkedin.com/in/fadel-megahed-289046b4/",
+                       target = "_blank",
+                       bsicons::bs_icon("linkedin"), " LinkedIn"),
+                tags$a(class = "btn btn-outline-secondary btn-sm",
+                       href = "https://miamioh.edu/fsb/directory/?up=/directory/megahefm",
+                       target = "_blank",
+                       bsicons::bs_icon("globe"), " Website"),
+                tags$a(class = "btn btn-outline-secondary btn-sm",
+                       href = "https://github.com/fmegahed/",
+                       target = "_blank",
+                       shiny::icon("github"), " GitHub")
+              )
+            )
+          ),
+          tags$hr(),
+          tags$h6(class = "fw-bold mb-2", "Data Source"),
+          tags$div(
+            class = "d-flex flex-wrap gap-2",
+            tags$a(class = "btn btn-outline-primary btn-sm",
+                   href = "https://www.bls.gov/oes/tables.htm", target = "_blank",
+                   bsicons::bs_icon("table"), " OEWS Tables"),
+            tags$a(class = "btn btn-outline-primary btn-sm",
+                   href = "https://www.bls.gov/oes/special-requests/oesm24all.zip",
+                   target = "_blank",
+                   bsicons::bs_icon("download"), " Raw Data (ZIP)")
+          )
+        )
+      ),
+
+      bslib::card(
+        bslib::card_header(class = "fw-bold", bsicons::bs_icon("play-circle"), " Video Tutorial"),
+        bslib::card_body(
+          fillable = FALSE,
+          tags$div(
+            class = "ratio ratio-16x9",
+            tags$iframe(
+              src = "https://www.youtube.com/embed/y98ssTHK4RQ",
+              allowfullscreen = NA,
+              allow = "accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture"
+            )
+          )
+        )
+      )
+    )
+  ),
+
+  # ---------- Explorer Tab ----------
+  bslib::nav_panel(
+    title = "Explorer",
+    value = "Explorer",
+    icon = bsicons::bs_icon("search"),
+
+    bslib::card(
+      bslib::card_header(shiny::textOutput("explorer_table_title")),
+      DT::DTOutput("table")
+    ),
+
+    bslib::accordion(
+      open = FALSE,
+      bslib::accordion_panel(
+        title = "SQL Query",
+        icon = bsicons::bs_icon("code-square"),
+        shiny::verbatimTextOutput("sql")
+      )
+    )
+  )
+)
+
+# ---------------------------------------------------------------------------
+# Server
+# ---------------------------------------------------------------------------
+
+server <- function(input, output, session) {
+  qc_vals <- qc$server()
+
+  # Navigate to Explorer tab on button click
+  shiny::observeEvent(input$go_explorer, {
+    bslib::nav_select("navbar", "Explorer")
+  })
+
+  output$explorer_table_title <- shiny::renderText({
+    qc_vals$title() %||% "OEWS Data"
+  })
+
+  output$table <- DT::renderDT({
+    DT::datatable(
+      qc_vals$df(),
+      fillContainer = TRUE,
+      options = list(scrollX = TRUE, pageLength = 15)
+    )
+  })
+
+  output$sql <- shiny::renderText({
+    qc_vals$sql() %||% "SELECT * FROM oews"
+  })
+}
+
+shiny::shinyApp(ui, server)