2f:[{"id":"4e74d87867d00d3523e0fc5654fcccafb84af854493b30a46a40db890052cb6409a8eae662b77e7c7feac02f5133a9b43edd4aac52928443a32f0baf467775fc","href":"https://static.observableusercontent.com/files/4e74d87867d00d3523e0fc5654fcccafb84af854493b30a46a40db890052cb6409a8eae662b77e7c7feac02f5133a9b43edd4aac52928443a32f0baf467775fc","name":"characteristics@2.parquet","type":"application/octet-stream","lastModified":1662581994040.368,"size":1817069},{"id":"d4eb8623087470337c522927172ff864db6f8de4bf6884a247a05796bddf122a04f257c09840d6b62be4c2b2591800bd6e0c4d659e6fc12a7506bd9b4b73b6d5","href":"https://static.observableusercontent.com/files/d4eb8623087470337c522927172ff864db6f8de4bf6884a247a05796bddf122a04f257c09840d6b62be4c2b2591800bd6e0c4d659e6fc12a7506bd9b4b73b6d5","name":"cips.parquet","type":"application/octet-stream","lastModified":1662581994040.368,"size":420710},{"id":"f8cb2688789e03b4651ca799b8a865e23c9326e49b0b94bdd5159afc64b5bcd8b45cfb2f8148760a6f9dfe787e91d9cfec4a5b72fd81faf72ecd0056a13f882d","href":"https://static.observableusercontent.com/files/f8cb2688789e03b4651ca799b8a865e23c9326e49b0b94bdd5159afc64b5bcd8b45cfb2f8148760a6f9dfe787e91d9cfec4a5b72fd81faf72ecd0056a13f882d","name":"degrees@4.parquet","type":"application/octet-stream","lastModified":1662581994040.368,"size":9390147},{"id":"431f6cde90e398f6b6543352e58e8936d55719c0afc76d2383e06b78de023c58ddac3d9adb0183b812d2ce246b3e679e1af2c48f1c6e47392e1e89035ef59ea7","href":"https://static.observableusercontent.com/files/431f6cde90e398f6b6543352e58e8936d55719c0afc76d2383e06b78de023c58ddac3d9adb0183b812d2ce246b3e679e1af2c48f1c6e47392e1e89035ef59ea7","name":"fields.parquet","type":"application/octet-stream","lastModified":1662581994040.368,"size":18010}] 2d:{"title":"Where are history majors growing?","cells":[{"id":0,"value":"# Where are history majors growing?\n","pinned":false,"hidden":false,"mode":"md"},{"id":3,"value":"import {DuckDBClient} from '@cmudig/duckdb'\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":1004,"value":"md`DuckDB even intelligently now uses range requests--you can see this if you change from BA to MA degrees below. Only the portions of the parquet file that actually contain BA information are loaded with the first run of this page.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1297,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":156,"value":"viewof discipline_selection = {\n const summary = html`

Choose Discipline(s)

`\n let d = html`

`\n d.appendChild(summary)\n disciplines.sort()\n const input = Inputs.checkbox(disciplines.map(d => d.Discipline), {\n label: \"Discipline\",\n value: [\"Computer Science\", \"English Language and Literature\"],\n placeholder: \"Enter Discipline\"\n })\n d.appendChild(input)\n d3.select(input).on(\"input\", () => {\n d.value = input.value\n summary.innerText = `Change Disciplines`\n d.dispatchEvent(new Event(\"input\", {bubbles: true}))\n })\n summary.innerText = `Change Disciplines`\n d.value = input.value\n return d\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":743,"value":"viewof state_filter = wrap_details(usMap(new Set(default_states)), {title: \"Filter states\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":614,"value":"viewof default_states = Inputs.button([\n [\"Clear\", value => []],\n [\"Northeast\", value => [\"NY\", \"PA\", \"NJ\", \"ME\", \"RI\", \"CT\", \"ME\", \"NH\", \"VT\", \"MA\"]],\n [\"West Coast\", value => [\"CA\", \"OR\", \"WA\", \"HI\"]],\n [\"Great Lakes\", value => [\"MN\", \"WI\", \"IL\", \"IN\", \"MI\", \"OH\"]],\n [\"Louisiana Purchase\", value => [\"LA\", \"AR\", \"OK\", \"MO\", \"IA\", \"MN\", \"MT\", \"ND\", \"SD\", \"NE\", \"KS\", \"WY\"]],\n ], {value: [\"NY\", \"PA\", \"NJ\", \"ME\", \"RI\", \"CT\", \"ME\", \"NH\", \"VT\", \"MA\"], label: \"Quick switch region\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":390,"value":"viewof carnegie_filter = wrap_details(Inputs.checkbox(carnegies), {open: false, title: \"Filter by Carnegie classification (i.e., research universities vs liberal arts colleges)\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":849,"value":"viewof metric = Inputs.radio(new Map([[\"raw count\", \"degrees\"], [\"Percent of degrees\", \"share\"]]), {label: \"y-axis shows:\", value:\"share\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1018,"value":"viewof degree_type = Inputs.radio(degree_levels, {label: \"Degree Type\", value: degree_levels.get(\"Bachelor's\")})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1036,"value":"viewof gender = Inputs.checkbox([\"women\", \"men\"], {value: [\"men\", \"women\"]})","pinned":false,"hidden":false,"mode":"ojs"},{"id":200,"value":"embed({\n data: { values: filtered_by_discipline },\n mark: \"line\",\n width: 580,\n height: 380,\n title: `${discipline_selection.join(\",\")}.`,\n encoding: {\n strokeDash: { field: \"Discipline\", type: \"nominal\" },\n\n x: { field: \"year\", type: \"ordinal\" },\n y:\n metric == \"share\"\n ? { field: metric, type: \"quantitative\", title: \"% of degrees\" }\n : { field: metric, type: \"quantitative\", title: \"number of degrees\" },\n color: { field: \"Discipline\", type: \"nominal\" }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":969,"value":"md`# Filter by school name\n\nA full list of schools displayed in the chart is below. You can use the search box to specific names that you're interested in.\n\n**Note**: if the geography or Carnegie classification doesn't include your chosen school, it won't appear in the list below!\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":889,"value":"viewof t = Inputs.search(possible_schools, {label: \"Filter to specific schools (Use checkbox below to limit further\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":892,"value":"viewof selected_schools = Inputs.table(t)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1168,"value":"selected_schools","pinned":true,"hidden":false,"mode":"ojs"},{"id":323,"value":"md`# Building a view\n\nWe've loaded three tables here:\n\n1. A 5,286,850 central degrees table that includes numeric codes identifying majors and schools for every school in the US going back to 1984, including gender and degree level for BA, MA, and doctoral degrees. (7MB)\n2. A 2,481 row table with information about majors;\n3. A 6,587 row table with dozens of columns of information about each school. (2MB)\n\nSince duckdb supports full SQL, you can do handy things like create views of table relationships that would be far too large to actually materialize. Here I'll join all 2 million rows of data to include the full institutional characteristics for each school and descriptions of each major, as well--this is a table that would probably require, back of the envelope, at least 4 gigabytes of memory if you were to actually materialize it. But since it's real SQL, we create a view instead, which means that it doesn't really exist, but portions of it will be materialized as needed for queries.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":485,"value":"md`Random rows from the main, 5m row table.`","pinned":true,"hidden":false,"mode":"ojs"},{"id":365,"value":"t4 = client.table(`SELECT * FROM \"degrees\" WHERE RANDOM() < .00001 ORDER BY RANDOM() LIMIT 3`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":491,"value":"md`Random rows from the hydrated view that contains all information about degree types and institutions.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":496,"value":"md`Code that creates the initial tables.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1235,"value":"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`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":6,"value":"client = {\n const client = await DuckDBClient.of({\n chars: await FileAttachment(\"characteristics@2.parquet\"),\n fields_raw: await FileAttachment(\"fields.parquet\"),\n cips: await FileAttachment(\"cips.parquet\"),\n degrees1: await FileAttachment(\"degrees@4.parquet\")\n });\n // sort the degrees on ingest to benefit lookups by school\n // client.query(`CREATE TABLE sorted_degrees AS SELECT * FROM \"degrees@1.parquet\" ORDER BY UNITID, CIPCODE, year`)\n await client.query(`DROP TABLE IF EXISTS fields2`);\n await client.query(`CREATE TABLE fields2 AS SELECT * FROM \"fields_raw\"`);\n await client.query(\n `UPDATE \"fields2\" SET \"Discipline\"='Media Studies' WHERE CIPCODE='09.0102';`\n );\n client.query(\n `CREATE view degrees AS SELECT * FROM \"fields2\" NATURAL JOIN \"degrees1\" NATURAL JOIN \"chars\" WHERE AWLEVEL = ${degree_type.join(\n \"OR AWLEVEL = \"\n )}`\n );\n return client;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1288,"value":"degree_type.join(\"OR AWLEVEL = \")","pinned":true,"hidden":false,"mode":"ojs"},{"id":489,"value":"client.table(`SELECT * FROM \"degrees\" WHERE RANDOM() < .00001 ORDER BY RANDOM() LIMIT 3`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1238,"value":"client.table(`SELECT COUNT(*) FROM \"degrees\";`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":592,"value":"md`# Creating filter tables\n\nThe 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.\n\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":1174,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":423,"value":"school_filter_tb = {\n await client.query(\"DROP TABLE IF EXISTS school_filter\");\n await client.table(\n `CREATE TABLE school_filter AS SELECT UNITID, INSTNM FROM \"chars\" WHERE ${all_inst_filters}`\n );\n return (\n await client.queryRow(\"SELECT COUNT(*)::INT as count FROM school_filter\")\n )[\"count\"];\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1305,"value":"SELECT * FROM chars LIMIT 10;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":958,"value":"md`# Handle school-level filters\n\nOK, 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.\n\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":916,"value":"school_filter_including_table = {\n school_filter_tb\n await client.query(`DROP VIEW IF EXISTS school_filter_including_table`)\n .catch(err => client.query(`DROP TABLE IF EXISTS school_filter_including_table`))\n if (selected_schools.length == school_filter_tb) {\n // Use a view of the existing table rather than create a duplicate version\n return await client.query(`CREATE view school_filter_including_table AS SELECT UNITID FROM school_filter`) \n } else {\n const ids = selected_schools.map(d => d.UNITID)\n return await client.query(`CREATE TABLE school_filter_including_table AS \n SELECT UNITID FROM school_filter WHERE\n ${as_sql_in_filter(\"UNITID\", ids)}\n `)\n }\n \n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":120,"value":"filtered_by_discipline = {\n // Ensure we've got the latest versions of these two tables\n totals;\n school_filter_including_table;\n return client.query(`\nSELECT Discipline,\n SUM(degrees) as degrees,\n 100 * SUM(degrees)/FIRST(total) as share,\n year\n FROM degrees\n NATURAL JOIN total_tb\n NATURAL JOIN school_filter_including_table\nWHERE (\n${combine_filters([\n [\"gender\", gender],\n [\"AWLEVEL\", degree_type]\n])}\nAND ${as_sql_in_filter(\"DISCIPLINE\", discipline_selection)})\n \n\nGROUP BY year, Discipline`);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1087,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":844,"value":"md`Turn a key and list of items into a sql IN filter. This is code that could usefully be more complicated--but there's probably some good js libraries for compiling SQL already.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":427,"value":"function as_sql_in_filter(key, data) {\n // spread because I like sets as values, not lists.\n if (data === undefined || [...data].length == 0 || data.length == 0) {\n return ''\n }\n // letters need quotes, numbers don't.\n const mark = [...data][0].slice ? \"'\" : \"\"\n const d = [...data].map(d => mark + d + mark).join(',')\n return `\"${key}\" IN (${d})`\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":841,"value":"md`All the filters on institutions, bundled together.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":786,"value":"all_inst_filters = {\n const filters = [\n \"TRUE\", // to make there be *something* for WHERE to match against\n as_sql_in_filter(\"STABBR\", state_filter),\n as_sql_in_filter(\"CARNEGIE\", carnegie_filter)\n ].filter(d => d && d.length)\n const v = filters.join(\" AND \")\n return v\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1046,"value":"combine_filters = function(filter_parts) {\n filter_parts = filter_parts.filter(([k, v]) => {\n if (k === 'gender' && v.length == 2) {\n return false\n }\n return true\n })\n const filters = [\n \"TRUE\", // to make there be *something* for WHERE to match against\n ...filter_parts.map(([key, options]) => as_sql_in_filter(key, options)),\n ].filter(d => d && d.length)\n const v = filters.join(\" AND \")\n return v\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1052,"value":"combine_filters([[\"STABBR\", state_filter], [\"gender\", gender]])","pinned":true,"hidden":false,"mode":"ojs"},{"id":895,"value":"viewof possible_schools = \n{\n school_filter_tb // This must be previously defined\n return client.table(`SELECT * FROM \"school_filter\"`)\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1138,"value":"viewof selected_cips = client.table(`SELECT * FROM \"fields2\" WHERE Discipline LIKE '%edic%'`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":75,"value":"viewof disciplines = client.table(`SELECT DISTINCT Discipline FROM \"fields2\"`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":670,"value":"inst_totals = {\n await client.query(\"DROP TABLE IF EXISTS inst_totals\");\n return client.query(\n \"CREATE TABLE inst_totals AS SELECT SUM(degrees) degrees, UNITID, year, gender, AWLEVEL from degrees1 GROUP BY UNITID, year, gender, AWLEVEL\"\n );\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":208,"value":"totals = {\n inst_totals;\n school_filter_including_table;\n await client.query(\"DROP TABLE IF EXISTS total_tb\")\n 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`) \n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1026,"value":"md`# Database schema specifics\n\nSome database specific lookups (e.g., codes for fields) needed for lookups\n`\n","pinned":true,"hidden":false,"mode":"ojs"},{"id":1028,"value":"degree_levels = new Map([\n [\"Associate's\", [3]],\n [\"Bachelor's\", [5]],\n [\"Masters\", [7]],\n [\"Doctoral\", [9, 17]]\n])","pinned":true,"hidden":false,"mode":"ojs"},{"id":548,"value":"carnegies = {\nconst codes = `15\n16\n21\n22\n31\n32\n33\n40\n51\n52\n53\n54\n55\n56\n57\n58\n59\n60`.split(\"\\n\")\nconst meanings = `Doctoral/Research Universities--Extensive\nDoctoral/Research Universities--Intensive\nMasters Colleges and Universities I\nMasters Colleges and Universities II\nBaccalaureate Colleges--Liberal Arts\nBaccalaureate Colleges--General\nBaccalaureate/Associates Colleges\nAssociates Colleges\nTheological seminaries and other specialized faith-related institutions\nMedical schools and medical centers\nOther separate health profession schools\nSchools of engineering and technology\nSchools of business and management\nSchools of art, music, and design\nSchools of law\nTeachers colleges\nOther specialized institutions\nTribal colleges`.split(\"\\n\")\n const m = new Map()\n codes.forEach((code, i) => {m.set(meanings[i], +code)})\n return m\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":870,"value":"md`## Wrapping details\n\nThis 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.\n\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":740,"value":"function wrap_details(element, opts = {}) {\n const o = {\n title: \"Choose\",\n open: true\n }\n for (let [k, v] of Object.entries(opts)) {\n o[k] = v\n }\n const summary = html`

${o.title}

`\n let d = html`

`\n d.open = o.open\n d.appendChild(summary)\n d.appendChild(element)\n \n d3.select(element).on(\"input\", () => {\n d.value = element.value\n// summary.innerText = `Change Disciplines`\n d.dispatchEvent(new Event(\"input\", {bubbles: true}))\n })\n d.value = element.value\n return d\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":813,"value":"md`# Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":131,"value":"embed = require(\"vega-embed@6\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":811,"value":"import {usMap} from '@bmschmidt/us-states-input'","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W2f","stdlib":"1","titling":"auto"}