R and JavaScript

R

install.packages(c("duckdb", "DBI"))

library(DBI)
con <- dbConnect(duckdb::duckdb())
dbExecute(con, "ATTACH 'https://data.transitlab.nyc/v1/transitlab.duckdb' AS transitlab (READ_ONLY)")

zones <- dbGetQuery(con, "
  SELECT segment, routes, start_date, rider_hours_lost
  FROM transitlab.subway_slow_zones
  WHERE kind = 'slow zone'
  ORDER BY rider_hours_lost DESC
  LIMIT 20
")

With dbplyr you can write dplyr instead of SQL: tbl(con, I("transitlab.subway_slow_zones")).

Node

npm install @duckdb/node-api

import { DuckDBInstance } from "@duckdb/node-api"

const db = await DuckDBInstance.create()
const con = await db.connect()
await con.run("ATTACH 'https://data.transitlab.nyc/v1/transitlab.duckdb' AS transitlab (READ_ONLY)")

const reader = await con.runAndReadAll(`
  SELECT route_id, sum(meters) / sum(seconds) * 2.23694 AS mph
  FROM transitlab.bus_route_speeds
  GROUP BY route_id
  ORDER BY mph
  LIMIT 10
`)
console.table(reader.getRowObjects())

In the browser

DuckDB-Wasm runs the same SQL in a web page. Our SQL console is built on it. For a chart on your own page, the JSON feeds are often simpler: one fetch and you have the data.

const res = await fetch("https://transitlab.nyc/data/bus-speeds.json")
const { monthly } = await res.json()
// monthly: [{ month: "2019-01", citywide: 8.27, Bronx: 7.7, ... }, ...]