Digital Humanist. Manhattan-based.
Public
Edited
Apr 27, 2023
•
1 fork
2 stars
Type JavaScript, then Shift-Enter. Ctrl-space for more options. Arrow ↑/↓ to switch modes.
selected_schools
md`Random rows from the main, 5m row table.`
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 "degrees1" NATURAL JOIN "chars" WHERE AWLEVEL = ${degree_type.join(
"OR AWLEVEL = "
)}`
);
return client;
}
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.
`
Type JavaScript, then Shift-Enter. Ctrl-space for more options. Arrow ↑/↓ to switch modes.
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"];
}
client
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,
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'
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.
