30:T63c,embed({ mark: "line", title: `${ filters.includes("brand = 'Tenure Track'") ? "Tenure track" : "" } history jobs in North America, by time of year, on H-Net. Ben Schmidt.`, width: (width * 4) / 5, height: (width * 1) / 2, layer: [ { data: { values: windowedcounts.filter( (d) => d.era, (d) => d.comparable, (d) => year.includes(d["Academic Year"]) ) }, mark: { type: "area", // "extent": "maxmin", borders: true // fill: "opaque" }, encoding: { x: { field: "normalized_date", type: "temporal", timeunit: "monthdate" }, y: { title: "Jobs Listed", aggregate: "max", field: "cumulative" }, color: { field: "era", type: "nominal" }, y2: { aggregate: "min", field: "cumulative" }, color: { field: "era", type: "nominal" } } }, { data: { values: windowedcounts.filter( (d) => [2009, 2020, 2024].includes(d["Academic Year"]) && year.includes(d["Academic Year"]) ) }, mark: { type: "line", strokeWidth: 5, color: "black" }, encoding: { x: { field: "normalized_date", type: "temporal", timeunit: "monthdate" }, y: { field: "cumulative", type: "quantitative" }, strokeDash: { field: "Academic Year", type: "nominal" } } } ] })31:T74b,WITH titles AS (SELECT LOWER(job) AS text, 2 * FLOOR("academic year" / 2) AS category FROM tb WHERE brand = 'Tenure Track' AND History = TRUE AND "North America" = TRUE AND "academic year" >= 2010), word_counts AS (SELECT category, word, COUNT(*) count FROM -- This should be more unicode safe (SELECT category, UNLIST(string_split_regex("text", '[^A-Za-zé]+')) word FROM titles) GROUP BY category, word ), -- I created the counts as separate subqueries because I was unable to make PARTITION BY work correctly. word_totals AS ( SELECT word, sum("count")::FLOAT AS word_total FROM word_counts GROUP BY word ), category_totals AS ( SELECT category, sum("count")::FLOAT AS category_size FROM word_counts GROUP BY category ), pmi_part1 AS ( SELECT SUM("count") OVER ()::FLOAT as corpus_size, * FROM word_counts NATURAL JOIN word_totals NATURAL JOIN category_totals ), -- A few steps to define all the variables we need. pmi_part2 AS ( SELECT *, greatest(word_total - "count", 0.2) AS count_excluding, corpus_size - category_size AS size_excluding_category FROM pmi_part1 ), pmi_part3 AS ( SELECT *, category_size * (word_total / corpus_size) AS expected_here, size_excluding_category * (word_total / corpus_size) AS expected_elsewhere, FROM pmi_part2 ), pmi_part4 AS ( SELECT *, 2 * ( "count" * log("count" / expected_here) + "count_excluding" * log(count_excluding / expected_elsewhere) ) AS llr, log("count" / expected_here) AS pmi, FROM pmi_part3), ranked_ordered AS ( SELECT category, *, word, count, word_total, PMI, llr, RANK() OVER (PARTITION BY category ORDER BY llr DESC) ordering, FROM pmi_part4 WHERE PMI > 0 ) SELECT category, list(word) terms FROM ranked_ordered WHERE ordering < 25 GROUP BY category ORDER BY category32:[{"id":"8f746da5b8a50c60443cd3bdc9c0839fb307f1d7c174562f19a0596c5e76edbe6d044850754dac88e911099e20ef258e7ae856427ed08984533597e4b1a12d7b","href":"https://static.observableusercontent.com/files/8f746da5b8a50c60443cd3bdc9c0839fb307f1d7c174562f19a0596c5e76edbe6d044850754dac88e911099e20ef258e7ae856427ed08984533597e4b1a12d7b","name":"nc_hnet@14.parquet","type":"application/octet-stream","lastModified":1712420712685.152,"size":33236152},{"id":"a0b24ec877202895a7ebaca849ae7485ed92b3b4b880e399d9ff22a7e1a1dd78c7b97854a9b31f5bcb78373d083a5b4f1d8c55499745e523a7136f708c110a51","href":"https://static.observableusercontent.com/files/a0b24ec877202895a7ebaca849ae7485ed92b3b4b880e399d9ff22a7e1a1dd78c7b97854a9b31f5bcb78373d083a5b4f1d8c55499745e523a7136f708c110a51","name":"nc_hnet@15.parquet","type":"application/octet-stream","lastModified":1723941623097.075,"size":33669468},{"id":"7a1b9e6e7c442e4b5b39af7f0794db5c495cf38899f4b0d7d9dbadc053e950b8ed15fe68d8f45be5c5578c1d07852a4fdaf0172727b0a7c4a5453dea370d9d1f","href":"https://static.observableusercontent.com/files/7a1b9e6e7c442e4b5b39af7f0794db5c495cf38899f4b0d7d9dbadc053e950b8ed15fe68d8f45be5c5578c1d07852a4fdaf0172727b0a7c4a5453dea370d9d1f","name":"nc_hnet@16.parquet","type":"application/octet-stream","lastModified":1733023054263.813,"size":34660767},{"id":"a782dafc1b753f175c4d337659d1d134f6b029acc028016ab42311be43032b4e2a52484a9b0a7f10d99d237fa2b2de2af401deb14a45c2c5c79cf5bac359ce6a","href":"https://static.observableusercontent.com/files/a782dafc1b753f175c4d337659d1d134f6b029acc028016ab42311be43032b4e2a52484a9b0a7f10d99d237fa2b2de2af401deb14a45c2c5c79cf5bac359ce6a","name":"nc_hnet@13.parquet","type":"application/octet-stream","lastModified":1703615020256.167,"size":33175996}] 2e:{"title":"H-Net Jobs Data","cells":[{"id":0,"value":"# H-Net Jobs Data","pinned":false,"hidden":false,"mode":"md"},{"id":668,"value":"H-Net is a widely used site for sharing job ads in history. It provides the best up-to-date view of circumstances on the history job market.","pinned":false,"hidden":false,"mode":"md"},{"id":429,"value":"First, some filters. Do we want to have only tenure track jobs? Only 'history' jobs? Only those in North America? All other charts will reflect these filters.","pinned":false,"hidden":false,"mode":"md"},{"id":648,"value":"md`Data here was last updated ${max_date.slice(5, 12)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":141,"value":"viewof filters = Inputs.checkbox(new Map([[\"Tenure Track-jobs only\", \"brand = 'Tenure Track'\"], [\"Non-TT only\", \"brand != 'Tenure Track'\"], [\"History\", \"History = TRUE\"], [ \"North America only\", '\"North America\" = TRUE']]), {label: \"Filters\", value: [\"brand = 'Tenure Track'\", \"History = TRUE\", '\"North America\" = TRUE']})","pinned":false,"hidden":false,"mode":"ojs"},{"id":297,"value":"viewof year = Inputs.checkbox(years, { value: years.filter((d) => d !== 2009) })","pinned":false,"hidden":false,"mode":"ojs"},{"id":264,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":1018,"value":"embed({\n mark: \"line\",\n title: `${\n filters.includes(\"brand = 'Tenure Track'\") ? \"Tenure track\" : \"\"\n } history jobs in North America, by time of year, on H-Net. Ben Schmidt.`,\n width: (width * 4) / 5,\n height: (width * 1) / 2,\n layer: [\n {\n data: {\n values: windowedcounts.filter(\n (d) => d.normalized_date < new Date(max_date),\n (d) => d.comparable,\n (d) => year.includes(d[\"Academic Year\"])\n )\n },\n\n mark: {\n type: \"bar\",\n // \"extent\": \"maxmin\",\n borders: true\n // fill: \"opaque\"\n },\n encoding: {\n x: {\n field: \"Academic Year\"\n },\n y: {\n title: \"Jobs Listed\",\n aggregate: \"max\",\n field: \"cumulative\"\n },\n color: { field: \"era\", type: \"nominal\" },\n y2: {\n aggregate: \"min\",\n field: \"cumulative\"\n },\n color: { field: \"era\", type: \"nominal\" }\n }\n }\n ]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":321,"value":"# Jobs by Field","pinned":false,"hidden":false,"mode":"md"},{"id":602,"value":"viewof regionfield = Inputs.radio(['Region', 'Primary Category'], {value: \"Region\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":691,"value":"\"Comparable\" means--were they listed by the most recent date (that is, ${max_date.slice(5)}) in the most current year?","pinned":false,"hidden":false,"mode":"md"},{"id":324,"value":"embed({\n title: `Jobs posted on AHA by my idiosyncratic aggregation of 'primary category.' Orange are through ${max_date.slice(\n 5\n )}`,\n data: {\n values: regioncounts.filter((d) => d[\"academic year\"] > 2002)\n },\n height: 200,\n width: 250,\n mark: \"bar\",\n encoding: {\n facet: {\n field: regionfield,\n type: \"ordinal\",\n columns: 3,\n sort: { op: \"median\", field: \"count\", order: \"descending\" }\n },\n\n y: { field: \"count\", type: \"quantitative\", scale: { type: \"linear\" } },\n x: { field: \"academic year\", type: \"nominal\" },\n fill: { field: \"comparable\", type: \"nominal\" }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":991,"value":"regioncounts.filter((d) => d[\"academic year\"] == 2023)","pinned":true,"hidden":false,"mode":"ojs"},{"id":725,"value":"viewof filter = {\n const regions = (await client.query(`SELECT DISTINCT Region FROM filtered`))\n .getChild(\"Region\")\n .toArray();\n return Inputs.radio(regions, {\n label: \"See samples of a job type from 2023\",\n value: \"Methodological\"\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":707,"value":"{\n const rows = [\n ...(await client.query(\n `SELECT * FROM filtered WHERE Region='${filter}' AND \"academic year\" == 2023 ORDER BY RANDOM() LIMIT 30`\n ))\n ]\n .map((d) => `${d.job} (${d.inst})`)\n .map((d) => `* ${d}\\n`);\n return md`${rows}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":941,"value":"viewof sample = await client.table(\n `SELECT string_split(\"nc:text\", ' ')[8][1:750] AS incipit FROM filtered WHERE Region='${filter}' AND \"academic year\" == 2023 ORDER BY RANDOM()`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1031,"value":"await client.table(\n `SELECT Region, string_split(\"nc:text\", ' ')[8][1:750] AS incipit FROM filtered WHERE Region='${filter}' AND \"academic year\" == 2023 AND Region != 'Pre-1789' ORDER BY RANDOM() LIMIT 10`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":964,"value":"html`${sample.map((d) => d.incipit).join(\"
\")}`","pinned":true,"hidden":false,"mode":"ojs"},{"id":294,"value":"# Jobs by year","pinned":false,"hidden":false,"mode":"md"},{"id":650,"value":"max_date = client\n .query(\n `SELECT MAX(normalized_date)::STRING v FROM filtered WHERE \"Academic Year\" == 2024`\n )\n .then((d) => d.get(0)[\"v\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":240,"value":"viewof windowedcounts = {\n filtered;\n year_groups;\n return client.table(`WITH\n-- cross join to fill in empty dates\nay_tb AS (SELECT * FROM (SELECT DISTINCT(normalized_date) FROM tb) t1 CROSS JOIN (SELECT DISTINCT \"academic year\" FROM tb)),\n-- get counts for each individual day.\nannual AS (SELECT \"Academic Year\", \"normalized_date\", (COUNT(*) FILTER (WHERE Position IS NOT NULL))::FLOAT count FROM filtered FULL OUTER JOIN ay_tb USING (normalized_date, \"academic year\") WHERE \"Academic Year\" > 2004 GROUP BY ALL) \n\nSELECT \"Academic Year\", \"normalized_date\",\n SUM(count) OVER (\n PARTITION BY \"Academic Year\"\n ORDER BY \"normalized_date\" ASC\n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative,\n era\nFROM annual LEFT JOIN year_groups USING (\"Academic Year\") WHERE (\"Academic Year\" < 2024) OR (normalized_date < '${max_date}')`);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":518,"value":"year_groups = client.insertCSV(\n \"year_groups\",\n new TextEncoder().encode(`Academic Year,era\n2003,2003-2008\n2004,2003-2008\n2005,2003-2008\n2006,2003-2008\n2007,2003-2008\n2008,2003-2008\n2010,2010-2013\n2011,2010-2013\n2012,2010-2013\n2013,2010-2013\n2014,2014-2023\n2015,2014-2023\n2016,2014-2023\n2017,2014-2023\n2018,2014-2023\n2019,2014-2023\n2021,2014-2023\n2022,2014-2023\n2023,2014-2023\n`),\n { header: true }\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":395,"value":"viewof form = Inputs.form({\n word_1: Inputs.text({label: \"Term 1\", value: \"[Dd]igital [hH](istory|Humanities)\"}),\n word_2: Inputs.text({label: \"Term 2\", value: \"Japanese ([hH]istory|[Ss]tudies)\"}),\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":417,"value":"Plot.plot({\n stroke: {\n legend: true\n },\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(comparisoncounts, {\n x: \"academic year\",\n y: \"match_1\",\n stroke: \"red\"\n }),\n Plot.lineY(comparisoncounts, {\n x: \"academic year\",\n y: \"match_2\",\n stroke: \"blue\"\n })\n ]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":753,"value":"{\n const div = DOM.element(\"div\");\n\n const sel = d3\n .select(div)\n .attr(\n \"style\",\n \"display:grid;grid-template-columns: 1fr 1fr 1fr; padding:5px; grid-gap: 20px;\"\n )\n .style(\"max-width\", width)\n .selectAll(\"div\")\n .data(top_vals)\n .join(\"div\");\n sel\n .attr(\"style\", \"outline: 2px dotted gray; padding: 5px; font-size:12px\")\n .append(\"h4\")\n .text((d) => d.category);\n\n const words = sel\n .selectAll(\"span\")\n .data((d) => d.terms.toArray())\n .join(\"span\")\n .style(\"margin-left\", \"3px\")\n .on(\"mouseover\", (event, d) => {\n console.log(d);\n return sel\n .selectAll(\"span\")\n .style(\"text-decoration\", (e) => (e === d ? \"underline\" : \"none\"));\n });\n\n words.text((d) => d + \" \");\n return div;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":825,"value":"where_terms.join(\" AND \")","pinned":true,"hidden":false,"mode":"ojs"},{"id":877,"value":"html`${v[0].text.split(\" \").join(\"<>\")}`","pinned":true,"hidden":false,"mode":"ojs"},{"id":834,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"v","database":"var:client"},{"id":738,"value":"$31","pinned":true,"hidden":false,"mode":"sql","output":"top_vals","database":"var:client"},{"id":393,"value":"viewof comparisoncounts = {\n filtered\n const query = `\n\nSELECT \"Academic Year\",\n COUNT(*) FILTER (WHERE \"nc:text\" SIMILAR TO '.*(${form.word_1}).*')::FLOAT match_1,\n COUNT(*) FILTER (WHERE \"nc:text\" SIMILAR TO '.*(${form.word_2}).*')::FLOAT match_2\nFROM filtered GROUP BY ALL`\n return client.table(query)\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":300,"value":"years = {\n const years = [];\n for (let i = 2001; i <= 2024; i++) {\n years.push(i);\n }\n return years;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":314,"value":"viewof regioncounts = {\n filtered;\n return client.table(`\nSELECT \"${regionfield}\", \"Academic Year\",\nCOUNT(*)::FLOAT count, comparable FROM filtered\nGROUP BY ALL`);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":224,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"windowedcounts","database":"var:client"},{"id":502,"value":"client.table(`SELECT \"nc:text\" FROM filtered WHERE \"Academic Year\" = 2016`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":206,"value":"filtered = {\n client.table(`CREATE OR REPLACE TABLE filtered AS SELECT * FROM tb WHERE TRUE AND ${where_terms.join(\" AND \")}`)\n return client.table(\"SELECT * FROM filtered\")\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":432,"value":"viewof counts = {\n await client.query(`\n\n\nCREATE OR REPLACE TABLE typecounts AS\nWITH totalcounts AS (SELECT position, COUNT(*) c FROM tb GROUP BY position HAVING c > 1000)\nSELECT Position, count(*)::FLOAT count, \"Academic Year\" FROM tb NATURAL JOIN totalcounts WHERE TRUE AND ${where_terms.filter(d => !d.match(/tt/)).join(\" AND \")} GROUP BY ALL ORDER BY Position, \"Academic Year\"`)\n return client.table(\"SELECT * FROM typecounts\")\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1043,"value":"{\n const rows = [\n ...(await client.query(\n `SELECT * FROM filtered WHERE job LIKE '%Digital Humanities%' AND \"academic year\" <= 2026 ORDER BY \"academic year\" ASC LIMIT 300`\n ))\n ]\n .map((d) => `${d.job} (${d.inst}) ${d['academic year']}`)\n .map((d) => `* ${d}\\n`);\n return md`${rows}`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":492,"value":"counts","pinned":true,"hidden":false,"mode":"ojs"},{"id":441,"value":"Plot.plot({\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(\n counts.filter(\n (d) =>\n d[\"academic year\"] <= 2023 &&\n d[\"academic year\"] > 2003 &&\n d.brand !== \"Tenure Track\"\n ),\n { x: \"academic year\", y: \"count\", stroke: \"position\" }\n )\n ],\n y: {\n domain: [0, 400]\n },\n color: {\n reverse: true,\n legend: true\n }\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":203,"value":"where_terms = [...filters, \"normalized_date > '2003-01-01'\"]","pinned":true,"hidden":false,"mode":"ojs"},{"id":178,"value":"SELECT * FROM (SELECT DISTINCT(normalized_date) FROM tb) t1 CROSS JOIN (SELECT DISTINCT \"academic year\" FROM tb)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":10,"value":"SELECT COUNT(*), \"academic year\", COUNT(*) FILTER(WHERE \"nc:text\" SIMILAR TO '.*([Dd]igital [Hh]istory|[Dd]igital [Hh]umanities).*')::FLOAT DH, COUNT(*) FILTER (WHERE \"nc:text\" SIMILAR TO '.*(German|France|French|Italy|Italian|Japan).*')::FLOAT H FROM tb WHERE \"North America\" AND \"academic year\" > 2010 AND \"history\" AND \"academic year\" <= 2023 AND \"Institution Type\" = 'College / University' GROUP BY ALL ORDER BY \"academic year\"","pinned":true,"hidden":false,"mode":"sql","output":"diff","database":"var:client"},{"id":785,"value":"Plot.plot({\n color: {\n legend: true\n },\n y: {\n tickFormat: \"s\"\n },\n marks: [\n Plot.areaY(data, { x: \"academic year\", y: \"count\", fill: \"category\" }),\n Plot.ruleY([0])\n ]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":758,"value":"SELECT\n \"academic year\",\n CASE WHEN category = '' THEN 'UNKNOWN' ELSE category END AS category,\n COUNT(*)::FLOAT count\n FROM (SELECT\n \"academic year\",\n-- \"Primary Category\" category, \n ((string_split(\"Secondary Categories\", '--') ))[1] category,\n FROM tb WHERE (\"Region\" = 'US/Canada' OR \"Region\" = 'Black/Af-Am') AND \"North America\" = TRUE AND \"academic year\" > 2005) t1\nGROUP BY \"academic year\", \"category\" \n HAVING count > 5\n ORDER BY category, \"academic year\" ASC;","pinned":true,"hidden":false,"mode":"sql","output":"data","database":"var:client"},{"id":24,"value":"SELECT brand, COUNT(*) FROM tb GROUP BY ALL","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":617,"value":"SELECT Position, \"Primary Category\", \"Secondary Categories\", \"job\", \"inst\" FROM filtered WHERE \"Posting Date\" > '2020-03-14' AND \"Secondary Categories\" LIKE '%Early%' AND NOT tt","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":887,"value":"client.table(`SELECT * FROM tb USINg Sample 5`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":7,"value":"client = {\n const client = await DuckDBClient.of({\n tb_raw: FileAttachment(\"nc_hnet@16.parquet\")\n });\n await client.query(`CREATE TABLE tb AS SELECT * FROM tb_raw`);\n\n await client.query(\n `UPDATE tb SET history = false WHERE \"Primary Category\" LIKE '%Art%'`\n );\n return client;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":123,"value":"embed = require(\"vega-embed@6\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":4,"value":"import {DuckDBClient} from '@cmudig/duckdb'\n","pinned":true,"hidden":false,"mode":"ojs"},{"id":364,"value":"import { serialize } from '@palewire/saving-csv'","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}