# 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")) } ) options(querychat.DataFrameSource.engine = "duckdb") 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)