2f:T726,q = `WITH word_counts AS (SELECT boroname || ': ' || nta_name category, spc_common as word, COUNT(*) count FROM trees 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 ), pmi_part2 AS ( SELECT *, greatest(word_total - "count", 0.2) AS count_excluding, -- 0.2 is a fudge here--you could call it a 'prior,', I guess. If a word doesn't appear, we pretend that it appeared *just a little* to make the logarithms work. 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::INT || ')' AS label, count, word_total, PMI, llr, CASE WHEN PMI < 0 THEN 'under-represented' ELSE 'over-represented' END AS direction, RANK() OVER (PARTITION BY category, PMI < 0 ORDER BY llr DESC) ordering, FROM pmi_part4 ) SELECT category, direction, list(label) terms FROM ranked_ordered WHERE ordering < 6 GROUP BY direction, category ORDER BY direction, category`30:[] 2d:{"title":"177,293 trees grow in Brooklyn","cells":[{"id":0,"value":"# 177,293 trees grow in Brooklyn\n\nData from the [2015 New York Trees census.](https://data.cityofnewyork.us/Environment/2015-Street-Tree-Census-Tree-Data/pi5s-9p35)\n\nThe goal is to test out two new-ish features in [deepscatter](https://github.com/nomic-ai/deepscatter) side by side:\n\n1. Ingesting an arrow table (here as the output of a duckdb query on a parquet file).\n2. Setting foreground and background colors dynamically.\n\nTry zooming around the map to your neighborhood, or filtering by tree species in the search box below the map.","pinned":false,"hidden":false,"mode":"md"},{"id":138,"value":"viewof selected = Inputs.radio([\"common\", \"TPCondition\", \"TPStructure\"], {\n value: \"common\",\n label: \"Color field\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":104,"value":"

","pinned":false,"hidden":false,"mode":"html"},{"id":254,"value":"viewof results = Inputs.search(species1, { label: \"Search by species\" })","pinned":true,"hidden":false,"mode":"ojs"},{"id":262,"value":"viewof species = Inputs.table(results)","pinned":true,"hidden":false,"mode":"ojs"},{"id":101,"value":"scatterplot = {\n const plot = new deepscatter.default(\"#plot\", 900, 600);\n await plot.plotAPI({\n arrow_table: trees2_arrow,\n point_size: 1,\n max_points: 1e6,\n zoom_balance: 0.35,\n alpha: 40,\n background_color: \"#FFFAF2\",\n encoding: {\n x: {\n field: \"x\",\n transform: \"literal\"\n },\n y: {\n field: \"y\",\n transform: \"literal\"\n }\n }\n });\n\n invalidation.then(() => plot.destroy());\n return plot;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":383,"value":"viewof species1 = db.table(\n `\n FROM trees2\n SELECT common,\n COUNT(*) AS count \n GROUP BY common\n ORDER by count DESC\n`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":448,"value":"(scatterplot.tooltip_html = function (datum) {\n console.log({ ...datum });\n let html = `

${datum.common}

${datum.latin}, ${datum.latin}

`;\n if (![\"common\"].includes(selected)) {\n html += `

${selected}: ${datum[selected]}

`;\n }\n return html;\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":170,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":315,"value":"{\n results;\n d3.select(\"#label\")\n .style(\"font-size\", \"48px\")\n .style(\"font-family\", \"sans-serif\")\n .style(\"background\", \"none\")\n .text(viewof results[0].value);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":177,"value":"scatterplot.plotAPI({\n background_options: {\n opacity: 0.05,\n size: 0.1\n },\n encoding: {\n foreground: {\n field: \"common\",\n lambda\n }\n }\n // tooltip_html: `return \"${selected}: \" + datum['${selected}']`\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":199,"value":"lambda = `d => new Set(${JSON.stringify(selected_species)}).has(d)`","pinned":true,"hidden":false,"mode":"ojs"},{"id":297,"value":"lambda_size = `d => (new Set(${JSON.stringify(\n selected_species\n)}).has(d) ? 2 : 0.5)`","pinned":true,"hidden":false,"mode":"ojs"},{"id":181,"value":"selected_species = species.map((d) => d.common)","pinned":true,"hidden":false,"mode":"ojs"},{"id":411,"value":"img = fetch(\"https://maps1.nyc.gov/tms/1.0.0/photo/2018/21/617539/1308820.png8\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":379,"value":"

\n","pinned":true,"hidden":false,"mode":"html"},{"id":168,"value":"{\n scatterplot.plotAPI({\n encoding: {\n color: { field: selected, range: \"category10\" }\n }\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":414,"value":"html`\n\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":96,"value":"deepscatter = import(\"https://benschmidt.org/deepscatter@2.8.0\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":64,"value":"categorical = [\n \"status\",\n \"health\",\n \"steward\",\n \"guards\",\n \"sidewalk\",\n \"user_type\",\n \"boroname\",\n \"spc_common\",\n \"curb_loc\",\n \"root_stone\",\n \"root_grate\",\n \"root_other\",\n \"trnk_wire\",\n \"trnk_light\",\n \"trnk_other\",\n \"brnch_shoe\",\n \"brnch_ligh\",\n \"brnch_othe\"\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":8,"value":"db = {\n const db = await DuckDBClient.of({});\n await db.query(\n \"CREATE TABLE trees2 AS SELECT * FROM 'https://benschmidt.org/treesbig.parquet'\"\n );\n return db;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":9,"value":"trees2_arrow = {\n const categorical = [\"TPStructure\", \"TPCondition\", \"latin\", \"common\"];\n const sels = categorical.map((d) => ` ${d}::${d} AS ${d}`).join(\",\");\n\n const enums_made = await Promise.all(\n categorical.map((k) => {\n db.query(\"DROP TYPE \" + k);\n return db.query(\n `CREATE TYPE ${k} AS ENUM (SELECT \"${k}\" FROM trees2 WHERE \"${k}\" IS NOT NULL)`\n );\n })\n );\n return db.query(\n `SELECT x::FLOAT x, (y * -1.5)::FLOAT y, ${sels}, row_number() OVER () AS ix FROM trees2`\n );\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":472,"value":"# Most over-and under-represented trees\n\nUsing G-Test","pinned":false,"hidden":false,"mode":"md"},{"id":456,"value":"db.table(q)","pinned":true,"hidden":false,"mode":"ojs"},{"id":460,"value":"$2f","pinned":true,"hidden":false,"mode":"ojs"},{"id":57,"value":"trees.url()","pinned":true,"hidden":false,"mode":"ojs"},{"id":4,"value":"import {\n DuckDBClient,\n arrow\n} from \"@bmschmidt/duckdb-client-1-20-0-arrow-10-0-1\"","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}