AE 21: Beyond tools
Application exercise
R
Python
25_querychat
library(shiny)
library(bslib)
library(dplyr)
library(ggplot2)
library(leaflet)
library(ellmer)
library(querychat)
library(reactable)
theme_set(theme_minimal(14))
ggplot2::update_geom_defaults("bar", list(fill = "#007BC2"))
ggplot2::update_geom_defaults("boxplot", list(colour = "#007BC2"))
# Load and prepare data
airbnb_data <-
read.csv(here::here("data/airbnb-asheville.csv")) |>
filter(!is.na(price)) |>
mutate(occupancy_pct = (365 - availability_365) / 365)
# Step 1: Set up querychat ----------
# Configure querychat. This is where you specify the dataset and can also
# override options like the greeting message, system prompt, model, etc.
# airbnb_qc_config <- ____
# UI --------------------------------------------------------------------------
ui <- page_sidebar(
title = "Asheville Airbnb Dashboard",
class = "bslib-page-dashboard",
# Step 2: Replace sidebar ----
# Replace the entire sidebar with querychat_sidebar("airbnb")
sidebar = sidebar(
checkboxGroupInput(
"room_type",
"Room Type",
choices = unique(airbnb_data$room_type),
selected = unique(airbnb_data$room_type)
),
selectInput(
"neighborhood",
"Neighborhood",
choices = c("All" = "", unique(airbnb_data$neighborhood)),
multiple = TRUE
),
sliderInput(
"price",
"Price Range",
min = 0,
max = 7000,
value = c(0, 7000),
step = 50,
ticks = FALSE,
pre = "$"
)
),
# Extra UI added when you add in querychat
if (exists("airbnb_qc_config")) {
card(
fill = FALSE,
max_height = "400px",
full_screen = TRUE,
card_body(
padding = 0,
navset_card_underline(
nav_spacer(),
nav_panel(
"SQL",
icon = fontawesome::fa_i("terminal"),
uiOutput("ui_sql")
),
nav_panel(
"Table",
icon = fontawesome::fa_i("table"),
reactableOutput("table")
)
)
)
)
},
# Value boxes ----
layout_columns(
fill = FALSE,
value_box(
title = "Number of Listings",
value = textOutput("num_listings"),
showcase = fontawesome::fa_i("home")
),
value_box(
title = "Average Price per Night",
value = textOutput("avg_price"),
showcase = fontawesome::fa_i("dollar-sign")
),
value_box(
title = "Average Occupancy",
value = textOutput("avg_occupancy"),
showcase = fontawesome::fa_i("calendar-check")
)
),
# Cards ----
layout_columns(
card(
full_screen = TRUE,
card_body(
padding = 0,
leafletOutput("listings_map")
)
),
layout_columns(
col_widths = 12,
card(
full_screen = TRUE,
card_header("Room Types"),
plotOutput("room_type_plot")
),
card(
full_screen = TRUE,
card_header("Availability by Room Type"),
plotOutput("availability_plot")
)
)
)
)
# Server ----------------------------------------------------------------------
server <- function(input, output, session) {
# Step 3: Set up querychat server ----
# Create an `airbnb_qc` querychat object by calling `querychat_server()` with
# the same ID and config from steps 2 and 1.
# airbnb_qc <- ______
# Step 4: Use the querychat-filtered data ----
# Replace all of the logic inside of `filtered_data()` with
# `airbnb_qc$df()`.
filtered_data <- reactive({
data <- airbnb_data
if (length(input$room_type)) {
data <- data |> filter(room_type %in% input$room_type)
}
if (any(nzchar(input$neighborhood))) {
data <- data |> filter(neighborhood %in% input$neighborhood)
}
data |> filter(price >= input$price[1] & price <= input$price[2])
})
# Value boxes
output$num_listings <- renderText({
scales::comma(nrow(filtered_data()))
})
output$avg_price <- renderText({
validate(need(nrow(filtered_data()) > 0, "N/A"))
scales::dollar(mean(filtered_data()$price), accuracy = 1)
})
output$avg_occupancy <- renderText({
validate(need(nrow(filtered_data()) > 0, "N/A"))
scales::percent(mean(filtered_data()$occupancy_pct))
})
# Plots
output$room_type_plot <- renderPlot({
validate(need(nrow(filtered_data()) > 0, "No listings available."))
filtered_data() |>
count(room_type) |>
mutate(room_type = forcats::fct_reorder(room_type, n)) |>
ggplot(aes(x = n, y = room_type)) +
geom_col() +
labs(x = "Number of Listings", y = NULL)
})
output$availability_plot <- renderPlot({
validate(need(nrow(filtered_data()) > 0, "No listings available."))
filtered_data() |>
ggplot(aes(x = availability_365, y = room_type)) +
geom_boxplot() +
labs(x = "Availability (days/year)", y = NULL)
})
# Map
output$listings_map <- renderLeaflet({
validate(need(nrow(filtered_data()) > 0, "No listings available."))
leaflet(filtered_data()) |>
addTiles() |>
# fmt: skip
addMarkers(
~longitude,
~latitude,
clusterOptions = markerClusterOptions(),
popup = ~ paste0(
"<strong>", name, "</strong><br>",
"Price: ", scales::dollar(price), "<br>",
"Room Type: ", room_type, "<br>",
"Neighborhood: ", neighborhood, "<br>",
"Owner: ", host_name, "<br>",
"Reviews: ", scales::comma(n_reviews), "<br>",
"Availability: ", availability_365, " days/year"
)
)
})
# querychat outputs
if (exists("airbnb_qc")) {
output$ui_sql <- renderUI({
sql <- airbnb_qc$sql()
if (!isTruthy(sql)) {
sql <- "SELECT * FROM aibnb_data"
}
HTML(paste0("<pre><code>", sql, "</code></pre>"))
})
output$table <- renderReactable({
reactable(
airbnb_qc$df(),
columns = list(
name = colDef(minWidth = 200),
price = colDef(
align = "right",
format = colFormat(prefix = "$", separators = TRUE, digits = 0)
),
room_type = colDef(),
neighborhood = colDef(),
occupancy_pct = colDef(
format = colFormat(percent = TRUE, digits = 1)
),
description = colDef(show = FALSE),
amenities = colDef(show = FALSE),
url_picture = colDef(show = FALSE),
url_listing = colDef(show = FALSE)
),
defaultPageSize = 20,
highlight = TRUE,
bordered = TRUE,
striped = TRUE,
showPageSizeOptions = TRUE,
details = function(index) {
row <- airbnb_qc$df()[index, ]
htmltools::div(
style = "padding: 16px;",
htmltools::tags$strong("Description:"),
htmltools::tags$p(row$description),
htmltools::tags$strong("Amenities:"),
htmltools::tags$p(row$amenities)
)
}
)
})
}
}
shinyApp(ui, server)# Setup ------------------------------------------------------------------------
import pandas as pd
import plotly.express as px
import querychat
import shinywidgets as sw
from faicons import icon_svg
from pyhere import here
from shiny import App, reactive, render, ui
# Load and prepare data
airbnb_data = (
pd.read_csv(here("data/airbnb-asheville.csv"))
.loc[lambda df: df["price"].notnull()]
.assign(occupancy_pct=lambda df: (365 - df["availability_365"]) / 365)
)
room_type_choices = sorted(airbnb_data["room_type"].dropna().unique().tolist())
neighborhood_choices = airbnb_data["neighborhood"].dropna().unique().tolist()
# Step 1: Set up querychat ----------
# Configure querychat. This is where you specify the dataset and can also
# override options like the greeting message, system prompt, model, etc.
# airbnb_qc_config = ____
# UI ===------------------------------------------------------------------------
app_ui = ui.page_sidebar(
# Step 2: Replace sidebar ----
# Replace the entire sidebar with querychat.sidebar("airbnb")
ui.sidebar(
ui.input_checkbox_group(
"room_type",
"Room Type",
choices=room_type_choices,
selected=room_type_choices,
),
ui.input_selectize(
"neighborhood", "Neighborhood", choices=neighborhood_choices, multiple=True
),
ui.input_slider(
"price",
"Price Range",
min=0,
max=7000,
value=[0, 7000],
step=50,
pre="$",
),
),
# Extra UI added when you add in querychat
ui.card(
ui.card_body(
ui.navset_card_underline(
ui.nav_spacer(),
ui.nav_panel(
"SQL",
ui.output_ui("ui_sql"),
icon=icon_svg("terminal"),
),
ui.nav_panel(
"Table",
ui.output_data_frame("table"),
icon=icon_svg("table"),
),
),
padding=0,
),
fill=False,
max_height="400px",
full_screen=True,
)
if "airbnb_qc_config" in globals()
else None,
# Value boxes ----
ui.layout_columns(
ui.value_box(
"Number of Listings",
ui.output_text("num_listings"),
showcase=icon_svg("house"),
),
ui.value_box(
"Average Price per Night",
ui.output_text("avg_price"),
showcase=icon_svg("dollar-sign"),
),
ui.value_box(
"Average Occupancy",
ui.output_text("avg_occupancy"),
showcase=icon_svg("calendar-check"),
),
fill=False,
),
# Cards ----
ui.layout_columns(
ui.card(
ui.card_body(sw.output_widget("listings_map", fill=True), padding=0),
full_screen=True,
),
ui.layout_columns(
ui.card(
ui.card_header("Room Types"),
sw.output_widget("room_type_plot"),
full_screen=True,
),
ui.card(
ui.card_header("Availability by Room Type"),
sw.output_widget("availability_plot"),
full_screen=True,
),
col_widths=12,
),
min_height="400px",
),
title="Asheville Airbnb Dashboard",
class_="bslib-page-dashboard",
fillable=True,
)
# Server -----------------------------------------------------------------------
def server(input, output, session):
# Step 3: Set up querychat server ----
# Create an `airbnb_qc` querychat object by calling `querychat.server()`
# with the same ID and config from steps 2 and 1.
# airbnb_qc = ____
# Step 4: Use the querychat-filtered data ----
# Replace all of the logic inside of `filtered_data()` with
# `airbnb_qc.df()`
@reactive.calc
def filtered_data():
df = airbnb_data.copy()
# Room type filter
room_type = input.room_type()
if room_type:
df = df[df["room_type"].isin(room_type)]
# Neighborhood filter
neighborhoods = input.neighborhood()
if neighborhoods:
df = df[df["neighborhood"].isin(neighborhoods)]
# Price range filter
pmin, pmax = input.price()
df = df[(df["price"] >= pmin) & (df["price"] <= pmax)]
return df
@render.text
def num_listings():
return f"{len(filtered_data()):,}"
@render.text
def avg_price():
df = filtered_data()
if df.empty:
return "N/A"
mean_price = df["price"].mean()
return f"${mean_price:.2f}"
@render.text
def avg_occupancy():
df = filtered_data()
if df.empty:
return "N/A"
mean_occ = df["occupancy_pct"].mean()
return f"{mean_occ:.1%}"
@sw.render_widget
def room_type_plot():
df = filtered_data()
if df.empty:
return None
df = df[df["price"].notnull()]
fig = px.histogram(
df,
x="price",
color="room_type",
barmode="group",
nbins=10,
labels={"price": "Price", "room_type": "Room Type"},
template="simple_white",
)
fig.update_layout(showlegend=True, font_size=14)
fig.update_yaxes(title_text="Count")
fig.update_xaxes()
fig.update_layout(margin=dict(l=0, r=0, t=0, b=0))
return fig
@sw.render_widget
def availability_plot():
df = filtered_data()
if df.empty:
return None
pdf = df[["availability_365", "room_type"]]
fig = px.box(
pdf,
x="availability_365",
y="room_type",
labels={"availability_365": "Availability (days/year)", "room_type": ""},
template="simple_white",
)
fig.update_layout(showlegend=False, font_size=14)
return fig
@sw.render_widget
def listings_map():
df = filtered_data()
if df.empty:
return None
pdf = df[
[
"latitude",
"longitude",
"name",
"price",
"room_type",
"neighborhood",
"host_name",
"n_reviews",
"availability_365",
]
]
fig = px.scatter_mapbox(
pdf,
lat="latitude",
lon="longitude",
zoom=11,
)
fig.update_traces(
hovertemplate=(
"<b>%{customdata[0]}</b><br>"
"Price: $%{customdata[1]:.2f}<br>"
"Room type: %{customdata[2]}<br>"
"Neighborhood: %{customdata[3]}<br>"
"Host: %{customdata[4]}<br>"
"Reviews: %{customdata[5]:,}<br>"
"Availability: %{customdata[6]} days/yr"
"<extra></extra>"
),
customdata=pdf[
[
"name",
"price",
"room_type",
"neighborhood",
"host_name",
"n_reviews",
"availability_365",
]
].to_numpy(),
)
fig.update_layout(
mapbox_style="open-street-map", margin=dict(l=0, r=0, t=0, b=0)
)
return fig
if "airbnb_qc_config" in globals():
@render.ui
def ui_sql():
sql = airbnb_qc.sql() if airbnb_qc.sql() else "SELECT * FROM airbnb_data"
return ui.pre(ui.code(sql))
@render.data_frame
def table():
return airbnb_qc.df()
app = App(app_ui, server)Acknowledgments
- Materials derived in part from Programming with LLMs and licensed under a Creative Commons Attribution 4.0 International (CC BY) License.