Digital Humanist. Manhattan-based.
Type JavaScript, then Shift-Enter. Ctrl-space for more options. Arrow ↑/↓ to switch modes.
embed({
data: { values: [...filtered_by_discipline].filter((d) => d.year > 2000) },
mark: "line",
width: 580,
height: 380,
title: `${discipline_selection.join(",")}.`,
encoding: {
strokeDash: { field: "Discipline", type: "nominal" },
x: { field: "year", type: "ordinal" },
y: { field: "total", type: "quantitative" },
color: { field: "Discipline", type: "nominal" }
}
})
filtered_by_discipline.get(1)
{
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" JOIN "chars" USING(UNITID) WHERE UNITID = ${selected_schools[0].UNITID} GROUP BY year, CIPCODE, Discipline`
);
return Plot.plot({
height: 2000,
marginLeft: 260,
// facet: { data: majors, y: "Discipline", marginRight: 90 },
marks: [
Plot.dot(majors, {
x: (d) => d.year,
y: (d) => d.CIPCODE + "(" + d.Discipline + ")",
fill: "degrees",
r: (d) => d.degrees
})
]
});
}
{
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" JOIN "chars" USING(UNITID) WHERE UNITID = ${selected_schools[0].UNITID} AND year = 2021 GROUP BY year, CIPCODE, Discipline`
);
return Plot.plot({
height: 2000,
marginLeft: 260,
// facet: { data: majors, y: "Discipline", marginRight: 90 },
marks: [
Plot.barX(majors, {
x: (d) => d.degrees,
y: (d) => d.CIPCODE + "(" + d.Discipline + ")",
sort: {
y: "x"
}
})
]
});
}
md`Random rows from the main, 5m row table.`
SELECT
t5 = client.table(`SELECT CIPCODE, SUM(degrees) degrees FROM "degrees" WHERE Discipline LIKE '%edical%' AND year = 2020 AND AWLEVEL = 5 GROUP BY CIPCODE ORDER BY degrees DESC`)
client = {
const client = await DuckDBClient.of({
chars: await FileAttachment("characteristics@2.parquet"),
fields_raw: await FileAttachment("fields.parquet"),
cips: await FileAttachment("cips.parquet"),
degrees1: await FileAttachment("degrees@4.parquet")
});
// sort the degrees on ingest to benefit lookups by school
// client.query(`CREATE TABLE sorted_degrees AS SELECT * FROM "degrees@1.parquet" 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 * FROM "fields2" NATURAL JOIN cips NATURAL JOIN "degrees1" NATURAL JOIN "chars" WHERE AWLEVEL = ${degree_type.join(
"OR AWLEVEL = "
)}`
);
return client;
}
client.query("SELECT * FROM cips LIMIT 10")
degree_type.join("OR AWLEVEL = ")
client.table(`SELECT COUNT(*) FROM "degrees";`)
md`# Creating filter tables
The filters are set by creating intermediate tables in the database. There may be more elegant or faster ways to do this, since it relies on a chain of observable promises mediating the creation of tables.
`
client
school_filter_tb = {
await client.query("DROP TABLE IF EXISTS school_filter")
await client.table(`CREATE TABLE school_filter AS SELECT UNITID, INSTNM FROM "chars" WHERE ${all_inst_filters}`)
return (await client.queryRow("SELECT COUNT(*)::INT as count FROM school_filter"))["count"]
}
md`# Handle school-level filters
OK, this strategy is a little klutzy. Check for school filters. If there aren't any, just alias a view to the existing table; if there are, create a new table using a WHERE search on all the schools in the region/carnegie bin.
`
school_filter_including_table = {
school_filter_tb;
await client
.query(`DROP VIEW IF EXISTS school_filter_including_table`)
.catch((err) =>
client.query(`DROP TABLE IF EXISTS school_filter_including_table`)
);
if (selected_schools.length == school_filter_tb) {
// Use a view of the existing table rather than create a duplicate version
return await client.query(
`CREATE view school_filter_including_table AS SELECT UNITID FROM school_filter`
);
} else {
const ids = selected_schools.map((d) => d.UNITID);
return await client.query(`CREATE TABLE school_filter_including_table AS
SELECT UNITID FROM school_filter WHERE
${as_sql_in_filter("UNITID", ids)}
`);
}
}
filtered_by_discipline = {
// Ensure we've got the latest versions of these two tables
totals;
school_filter_including_table;
return client.query(`
SELECT Discipline,
SUM(degrees) as degrees,
100 * SUM(degrees)/FIRST(total) as share,
FIRST(total)::FLOAT AS total,
year
FROM degrees
NATURAL JOIN total_tb
NATURAL JOIN school_filter_including_table
WHERE (
${combine_filters([
["gender", gender],
["AWLEVEL", degree_type]
])}
AND ${as_sql_in_filter("DISCIPLINE", discipline_selection)})
GROUP BY year, Discipline`);
}
Type JavaScript, then Shift-Enter. Ctrl-space for more options. Arrow ↑/↓ to switch modes.
function as_sql_in_filter(key, data) {
// spread because I like sets as values, not lists.
if (data === undefined || [...data].length == 0 || data.length == 0) {
return ''
}
// letters need quotes, numbers don't.
const mark = [...data][0].slice ? "'" : ""
const d = [...data].map(d => mark + d + mark).join(',')
return `"${key}" IN (${d})`
}
all_inst_filters = {
const filters = [
"TRUE", // to make there be *something* for WHERE to match against
as_sql_in_filter("STABBR", state_filter),
as_sql_in_filter("CARNEGIE", carnegie_filter)
].filter(d => d && d.length)
const v = filters.join(" AND ")
return v
}
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
}
combine_filters([["STABBR", state_filter], ["gender", gender]])
viewof possible_schools =
{
school_filter_tb // This must be previously defined
return client.table(`SELECT * FROM "school_filter"`)
}
viewof selected_cips = client.table(`SELECT * FROM "fields2" WHERE Discipline LIKE '%edic%'`)
viewof disciplines = client.table(`SELECT DISTINCT Discipline FROM "fields2"`)
inst_totals = {
await client.query("DROP TABLE IF EXISTS inst_totals");
return client.query(
"CREATE TABLE inst_totals AS SELECT SUM(degrees) degrees, UNITID, year, gender, AWLEVEL from degrees1 GROUP BY UNITID, year, gender, AWLEVEL"
);
}
totals = {
inst_totals;
school_filter_including_table;
await client.query("DROP TABLE IF EXISTS total_tb")
return client.query(`CREATE TABLE total_tb AS SELECT SUM(degrees) total, year FROM inst_totals NATURAL JOIN school_filter_including_table WHERE ${combine_filters([["gender", gender], ["AWLEVEL", degree_type]])} GROUP BY year`)
}
md`# Database schema specifics
Some database specific lookups (e.g., codes for fields) needed for lookups
`
degree_levels = new Map([
["Associate's", [3]],
["Bachelor's", [5]],
["Masters", [7]],
["Doctoral", [9, 17]]
])
md`## Wrapping details
This is probably worth a separate notebook, but: it can be nice to wrap an input in a toggleable 'detail' element that can still be used as a view. This is a re-useable function to do that.
`
function wrap_details(element, opts = {}) {
const o = {
title: "Choose",
open: true
}
for (let [k, v] of Object.entries(opts)) {
o[k] = v
}
const summary = html`<summary>${o.title}</h2></summary>`
let d = html`<details></details>`
d.open = o.open
d.appendChild(summary)
d.appendChild(element)
d3.select(element).on("input", () => {
d.value = element.value
// summary.innerText = `Change Disciplines`
d.dispatchEvent(new Event("input", {bubbles: true}))
})
d.value = element.value
return d
}
embed = require("vega-embed@6")
import {usMap} from '@bmschmidt/us-states-input'
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.