Public
Edited
Jul 29
degrees4 = FileAttachment("degrees-4.parquet")
// Contains address, city, zip code, etc.
characteristics2 = FileAttachment("characteristics-2.parquet")
fields = FileAttachment("fields.parquet")
client = {
const client = await DuckDBClient.of({
chars: await characteristics2, // FileAttachment("characteristics@2.parquet"),
fields_raw: await FileAttachment("fields.parquet"),
// cips: await FileAttachment("cips.parquet"),
degrees1: await degrees4
});
// sort the degrees on ingest to benefit lookups by school
client.query(`CREATE TABLE sorted_degrees AS SELECT * FROM "degrees1" ORDER BY UNITID, CIPCODE, year`)
await client.query(`DROP TABLE IF EXISTS fields2`);
await client.query(`CREATE TABLE fields2 AS SELECT * FROM "fields_raw"`);
await client.query(
`UPDATE "fields2" SET "Discipline"='Media Studies' WHERE CIPCODE='09.0102';`
);
client.query(
`CREATE view degrees AS SELECT d.year, d.gender, d.CIPCODE, d.UNITID, d.AWLEVEL, d.degrees, c.INSTNM, c.IALIAS,c.ADDR, c.CITY, c.STABBR, c.ZIP, C.LONGITUD,c.LATITUDE, f.Discipline, f.Bachelors, f.Doctoral FROM "degrees1" d NATURAL JOIN fields2 f NATURAL JOIN chars c`
);
return client;
}
viewof degreesSummary = client.summarize(`SELECT * FROM "degrees"`)
client
Type Table, then Shift-Enter. Ctrl-space for more options.
client
// Total number of rows in table `degrees`
client.table(`SELECT COUNT(*) AS num_rows FROM "degrees";`)
client_genders = await client.query(`SELECT DISTINCT gender FROM "degrees" ORDER BY gender`)
client_years = await client.query(`SELECT DISTINCT year FROM "degrees" ORDER BY year DESC`)
client_states = await client.query(`SELECT DISTINCT STABBR FROM "degrees" ORDER BY STABBR`)
client_yearRange = await client.query(`SELECT MIN(year) AS year_min, MAX(year) AS year_max FROM "degrees"`)
client_awa = await client.query(`SELECT DISTINCT AWLEVEL FROM "degrees" ORDER BY AWLEVEL`);
//viewof awlevel_arrObj = client.table(`SELECT DISTINCT AWLEVEL FROM "degrees" ORDER BY AWLEVEL`);
//viewof states = client.table(`SELECT DISTINCT STABBR FROM "degrees"`)
// Adds font family class
viewof state_filter = {
const summary = html`<summary>Choose State(s)</h2></summary>`
summary.className = "ds_font_family";
let d = html`<details open=true></details>`
d.appendChild(summary)
const input = Inputs.checkbox(client_states.toArray().map(d => d.STABBR), {
label: "State",
value: ["FL", "TX"]
})
d.appendChild(input)
d3.select(input).on("input", () => {
d.value = input.value
summary.innerText = `Select state`
summary.class = "ds_font_family"
d.dispatchEvent(new Event("input", {bubbles: true}))
})
summary.innerText = `Select state`
d.value = input.value
return d
}
// Schools matching search text, or all schools if search box empty
viewof t = Inputs.search(possible_schools, {label: "School filter:"})
viewof selected_schools = Inputs.table(t, {rows: 6})
// viewof date = Inputs.date({label: "Date", value: "2021-09-21", min: "1980-01-01", max: "2024-12-31"})
viewof yearRange_filter = myrangeInput({
min: client_yearRange.toArray().map(d => d.year_min)[0],
max: client_yearRange.toArray().map(d => d.year_max)[0],
step: 1,
value: [2010, 2021],
enableTextInput: true,
label: "Select year range:"
})
// viewof yearRange_filter = rangeInput({
// min: client_yearRange.toArray().map(d => d.year_min)[0],
// max: client_yearRange.toArray().map(d => d.year_max)[0],
// step: 1,
// value: [2010, 2021],
// enableTextInput: true,
// label: "Select year range: "
// })
viewof gender_filter = Inputs.checkbox(client_genders.toArray().map(d => d.gender), {
label: "Select gender:",
value: ["women"]
})
viewof awlevel_filter = Inputs.radio(client_awa.toArray().map(d => d.AWLEVEL), {
label: "AW Level",
value: 5
});
viewof disciplines = client.table(`SELECT DISTINCT Discipline FROM "fields2"`)
disciplines.map(d => d.Discipline)
// Final lookup data
schools_lookupData = {
const unitIds = selected_schools.map(d => d.UNITID);
// If the UNITID list is empty, return an empty table immediately.
// This prevents the query from running unnecessarily and correctly
// reflects that no schools are selected.
if (unitIds.length === 0) {
return Inputs.table([]);
}
const majors = await client.query(
`SELECT * FROM degrees WHERE ${combine_filters([
["STABBR", state_filter],
["gender", gender_filter],
["UNITID", unitIds]
])} AND year >= ${yearRange_filter[0]} AND year <= ${yearRange_filter[1]}`
);
//return Inputs.table(majors);
return majors;
}
viewof hrLine = {
return html`<hr>`;
}
schools_lookupTable = {
return Inputs.table(await schools_lookupData,{rows: 30});
}
// For spark lines see: https://observablehq.com/@observablehq/input-table
viewof recordCount = {
const result = await schools_lookupData;
let numRows;
if (
typeof result === 'object' &&
result !== null &&
'schema' in result
) {
// Result is an arrow object
//numRows = result.toArray().length;
numRows = new Intl.NumberFormat().format(result.toArray().length);
} else {
// Result is an empty HTML table
numRows = 0;
}
return html`<h3 class="ds_font_family" style="font-weight: 600; font-size: 20px;padding-top:22px;">Record count: ${numRows}</h3>`;
}
combine_filters = function(filter_parts) {
filter_parts = filter_parts.filter(([k, v]) => {
if (k === 'gender' && v.length == 2) {
return false
}
return true
})
const filters = [
"TRUE", // to make there be *something* for WHERE to match against
...filter_parts.map(([key, options]) => as_sql_in_filter(key, options)),
].filter(d => d && d.length)
const v = filters.join(" AND ")
return v
}
// List of all schools
viewof possible_schools =
{
return client.table(`SELECT UNITID, INSTNM FROM "chars" WHERE ${all_inst_filters}`)
}
dvStyles = html`
<style>
.ds_font_family {
font-family: "Raleway", "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
}
.ontario-label, label {
cursor: pointer;
color: #1a1a1a;
font-family: "Raleway", "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 1.0rem;
font-weight: 700;
line-height: 1.5;
*/
}
</style>`
numDegrees_timeSeries = {
if (selected_schools.length !== 1) {
return html`<h3>If you select a single school, all its majors should show here.`;
}
const majors = await client.query(
`SELECT CIPCODE, Discipline, year, sum(degrees) as degrees FROM "degrees" WHERE UNITID = ${selected_schools[0].UNITID} AND ${combine_filters([
["gender", gender_filter]
])} GROUP BY year, CIPCODE, Discipline`
);
return Plot.plot({
height: 2.5 * majors.toArray().length, //2000
marginLeft: 260,
x: {label: "Year",
tickFormat: "d" // ensure year is displayed with no comma
},
marks: [
Plot.dot(majors, {
x: (d) => d.year,
y: (d) => d.CIPCODE + "(" + d.Discipline + ")",
fill: "degrees",
stroke: "gray",
strokeWidth: 0.5,
r: (d) => d.degrees,
title: (d) => `${d.Discipline}\nYear: ${d.year}\nDegrees: ${d.degrees}` // Tooltip
})
]
});
}
2000/140
2000/913
viewof summaryStatsLabel = {
return html`<h3 class="ds_font_family" style="font-weight: 600; font-size: 20px;">Summary Statistics: schools data</h3>`;
}
//viewof schools_summary_stats = SummaryTable(schools_lookupData, {label: "Summary Statistics: schools Data"})
viewof schools_summary_stats = SummaryTable(schools_lookupData, {label: ""})
tripdata = FileAttachment("yellow_tripdata_2024-07-first-2m.parquet")
tripdata_arrowTable = {
const parquetBytes = new Uint8Array(
await tripdata.arrayBuffer()
);
// Set large batchSize to ensure input data stays as a single Arrow RecordBatch
const wasmTable = readParquet(parquetBytes, {batchSize: Math.pow(2, 31)});
const arrowTable = arrow.tableFromIPC(wasmTable.intoIPCStream());
return arrowTable;
}
// viewof factedSearch = FacetedSearch(db_getRemoteFile, { filter: duckDBFilter })
tripdata_arrowTable.numRows // 2,000,000
Inputs.table(tripdata_arrowTable)
tripdata_arrowTable.filter( arrowES5.predicate.col('passenger_count').eq(10) )
aq.fromArrow(tripdata_arrowTable)
// viewof geoType = Inputs.form({
// metric: Inputs.select(
// ["househumber","interpolation","locality","municipality", "street","village"],
// { label: "Choose a geo type" }
// )
// })
viewof geoType = Inputs.select(["househumber","interpolation","locality","municipality", "street","village"],
{value: "municipality", label: "Choose a geo type", multiple: true})
geoType
geoType[0]
viewof address = Inputs.text(
{label: "Address", placeholder: "Enter an address", value: "123 Street"}
)
db
import {DuckDBClient} from '@cmudig/duckdb'
// https://observablehq.com/@kasivisu4/duckdb-filter
import {duckDBFilter} from '@kasivisu4/duckdb-filter'
import {aq, op} from "@uwdata/arquero"
import {getDataTable} from '@huggingface/parquet-quick-view'
// https://observablehq.com/@kasivisu4/duckdb-filter
import { FacetedSearch } from "@kasivisu4/faceted-search-filter"
// from: https://github.com/kylebarron/parquet-wasm
// Load the parquet-wasm library
readParquet = {
const parquetModule = await import(
"https://unpkg.com/parquet-wasm@0.6.0/esm/parquet_wasm.js"
);
// Need to await the default export first to initialize the WebAssembly code
await parquetModule.default();
return parquetModule.readParquet;
}
import {rangeInput} from '@kasivisu4/range-slider'
import {interval} from '@mootari/range-slider'
import {theme_Flat} from '@mootari/range-slider'
cssLength = v => v == null ? null : typeof v === 'number' ? `${v}px` : `${v}`
function randomScope(prefix = 'scope-') {
return prefix + (performance.now() + Math.random()).toString(32).replace('.', '-');
}
// Modified from original rangeInput fn to include a text label
// uses class="ds_font_family" for label
function myrangeInput(options = {}) {
const {
min = 0,
max = 100,
step = "any",
value: defaultValue = [min, max],
color,
width,
theme = theme_Flat,
enableTextInput = false,
label // <--- Add this line
} = options;
// ... (rest of your existing code)
const controls = {};
const scope = randomScope();
const clamp = (a, b, v) => (v < a ? a : v > b ? b : v);
const html = htl.html;
const inputMin = html`<input type="number" id="min-input" min=${min} max=${defaultValue[1]} step=${step} value=${defaultValue[0]} />`;
inputMin.style = "width:5em";
const inputMax = html`<input type="number" id="max-input" min=${defaultValue[0]} max=${max} step=${step} value=${defaultValue[1]} />`;
inputMax.style = "width:5em";
// Will be used to sanitize values while avoiding floating point issues.
const input = html`<input type=range ${{ min, max, step }}>`;
const dom = html`${
label ? html`<label class="ds_font_family">${label} </label>` : "" // <--- Add this line to render the label
}${
enableTextInput ? inputMin : ""
}<div class=${`${scope} range-slider`} style=${{
color,
width: cssLength(width)
}}>
${(controls.track = html`<div class="range-track">
${(controls.zone = html`<div class="range-track-zone">
${(controls.range = html`<div class="range-select" tabindex=0>
${(controls.min = html`<div class="thumb thumb-min" tabindex=0>`)}
${(controls.max = html`<div class="thumb thumb-max" tabindex=0>`)}
`)}
`)}
`)}
${html`<style>${theme.replace(/:scope\b/g, "." + scope)}`}
</div>${enableTextInput ? inputMax : ""}`;
// ... (rest of your existing code)
let value = [],
changed = false;
Object.defineProperty(dom, "value", {
get: () => [...value],
set: ([a, b]) => {
value = sanitize(a, b);
updateRange();
}
});
const sanitize = (a, b) => {
a = isNaN(a) ? min : ((input.value = a), input.valueAsNumber);
b = isNaN(b) ? max : ((input.value = b), input.valueAsNumber);
return [Math.min(a, b), Math.max(a, b)];
};
const updateRange = () => {
const ratio = (v) => (v - min) / (max - min);
dom.style.setProperty("--range-min", `${ratio(value[0]) * 100}%`);
dom.style.setProperty("--range-max", `${ratio(value[1]) * 100}%`);
};
const dispatch = (name) => {
dom.dispatchEvent(new Event(name, { bubbles: true }));
};
const setValue = (vmin, vmax) => {
const [pmin, pmax] = value;
value = sanitize(vmin, vmax);
updateRange();
// Only dispatch if values have changed.
if (pmin === value[0] && pmax === value[1]) return;
inputMin.value = value[0];
inputMax.value = value[1];
dispatch("input");
changed = true;
};
inputMin.addEventListener("input", () => {
if (+inputMin.value > +inputMax.value || +inputMin.value < min) {
dom.appendChild(html`<please enter less>`);
return;
}
inputMax.min = inputMin.value;
setValue(inputMin.value, dom.value[1]);
});
inputMax.addEventListener("input", () => {
if (+inputMax.value < +inputMin.value || +inputMax.value > max) {
dom.appendChild(html`<please enter above>`);
return;
}
inputMin.max = inputMax.value;
setValue(dom.value[0], inputMax.value);
});
setValue(...defaultValue);
// Mousemove handlers.
const handlers = new Map([
[
controls.min,
(dt, ov) => {
const v = clamp(min, ov[1], ov[0] + dt * (max - min));
setValue(v, ov[1]);
}
],
[
controls.max,
(dt, ov) => {
const v = clamp(ov[0], max, ov[1] + dt * (max - min));
setValue(ov[0], v);
}
],
[
controls.range,
(dt, ov) => {
const d = ov[1] - ov[0];
const v = clamp(min, max - d, ov[0] + dt * (max - min));
setValue(v, v + d);
}
]
]);
// Returns client offset object.
const pointer = (e) => (e.touches ? e.touches[0] : e);
// Note: Chrome defaults "passive" for touch events to true.
const on = (e, fn) =>
e
.split(" ")
.map((e) => document.addEventListener(e, fn, { passive: false }));
const off = (e, fn) =>
e
.split(" ")
.map((e) => document.removeEventListener(e, fn, { passive: false }));
let initialX,
initialV,
target,
dragging = false;
function handleDrag(e) {
// Gracefully handle exit and reentry of the viewport.
if (!e.buttons && !e.touches) {
handleDragStop();
return;
}
dragging = true;
const w = controls.zone.getBoundingClientRect().width;
e.preventDefault();
handlers.get(target)((pointer(e).clientX - initialX) / w, initialV);
}
function handleDragStop(e) {
off("mousemove touchmove", handleDrag);
off("mouseup touchend", handleDragStop);
if (changed) dispatch("change");
}
invalidation.then(handleDragStop);
dom.ontouchstart = dom.onmousedown = (e) => {
dragging = false;
changed = false;
if (!handlers.has(e.target)) return;
on("mousemove touchmove", handleDrag);
on("mouseup touchend", handleDragStop);
e.preventDefault();
e.stopPropagation();
target = e.target;
initialX = pointer(e).clientX;
initialV = value.slice();
};
controls.track.onclick = (e) => {
if (dragging) return;
changed = false;
const r = controls.zone.getBoundingClientRect();
const t = clamp(0, 1, (pointer(e).clientX - r.left) / r.width);
const v = min + t * (max - min);
const [vmin, vmax] = value,
d = vmax - vmin;
if (v < vmin) setValue(v, v + d);
else if (v > vmax) setValue(v - d, v);
if (changed) dispatch("change");
};
return dom;
}
import { SummaryTable } from "@observablehq/summary-table"
arrow = require('apache-arrow')
arrowES5 = require('@apache-arrow/es5-umd@0.15.1')
client
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
