30:T118c,flowering = `name,flower_start,flower_end,notability,color London planetree,04-25,05-10,2,#A58B74 Honeylocust,05-25,06-10,2,#F8F2D7 Callery pear,04-05,04-20,4,#FFFFFF Pin oak,04-15,05-05,2,#B2A48A Norway maple,04-15,04-30,3,#FFE082 Littleleaf linden,06-25,07-10,3,#F5F5DC Cherry,04-05,04-20,5,#FFB7C5 Japanese zelkova,04-15,04-30,2,#A7927A Ginkgo,04-25,05-10,1,#E0DC89 Sophora,05-25,06-10,4,#FFFFFF Red maple,04-05,04-20,3,#FFA07A Green ash,04-15,04-30,2,#A7B28D American linden,06-25,07-10,3,#F5F5DC Silver maple,03-05,03-20,3,#C6E3FF Sweetgum,04-25,05-10,3,#A8461D Northern red oak,04-15,05-05,2,#B2A48A Silver linden,06-25,07-10,3,#E0FFFF American elm,03-05,03-20,3,#C6E3FF Maple,04-05,04-20,3,#FFA07A purple-leaf plum,04-01,04-20,4,#FF5FD9 swamp white oak,04-25,05-15,2,#B2A48A crimson king maple,04-15,05-05,3,#8B0000 Chinese elm,08-15,09-30,2,#B2A48A 'Schubert' chokecherry,04-20,05-10,4,#8B0000 Japanese tree lilac,06-05,06-25,4,#FFFFFF eastern redbud,04-01,04-20,5,#FF82AB golden raintree,06-05,06-25,4,#FFD700 crab apple,04-15,05-05,5,#FF69B4 Kentucky coffeetree,05-20,06-10,2,#F8F2D7 willow oak,04-20,05-10,2,#B2A48A dawn redwood,04-20,05-10,2,#A7B28D hawthorn,04-20,05-15,4,#FFFFFF sugar maple,04-15,05-05,3,#FFA07A sycamore maple,04-20,05-10,3,#FFE082 ash,04-15,05-05,2,#A7B28D hedge maple,04-20,05-10,3,#FFE082 common hackberry,04-15,05-05,2,#A7B28D sawtooth oak,04-15,05-05,2,#B2A48A Amur maackia,07-01,07-25,3,#FFFFFF European hornbeam,04-15,05-05,2,#B2A48A Amur maple,04-20,05-10,3,#FFA07A serviceberry,04-01,04-20,5,#FFFFFF black locust,05-05,05-25,4,#FFFFFF white oak,04-25,05-15,2,#B2A48A English oak,04-25,05-15,2,#B2A48A Siberian elm,03-25,04-15,3,#C6E3FF flowering dogwood,04-15,05-05,5,#FF83AA American hornbeam,04-20,05-10,2,#B2A48A Schumard's oak,04-25,05-15,2,#B2A48A scarlet oak,04-25,05-15,2,#B2A48A black oak,04-20,05-10,2,#B2A48A bald cypress,03-25,04-15,2,#A7B28D mulberry,04-15,05-05,3,#8B0000 Japanese maple,04-15,05-05,3,#FFA07A white ash,04-15,05-05,2,#A7B28D eastern redcedar,06-10,06-30,2,#B2A48A horse chestnut,05-05,05-25,4,#FFFFFF American hophornbeam,04-15,05-05,2,#B2A48A tulip-poplar,05-01,05-20,4,#FFD700 Cornelian cherry,03-10,03-30,4,#FFB7C5 shingle oak,04-25,05-15,2,#B2A48A hardy rubber tree,08-01,08-20,2,#A7B28D katsura tree,04-20,05-10,2,#B2A48A tree of heaven,05-25,06-15,2,#F8F2D7 magnolia,03-25,04-15,5,#FFFFFF black cherry,04-20,05-10,3,#8B0000 river birch,04-01,04-20,2,#B2A48A catalpa,05-25,06-15,4,#FFFFFF paper birch,04-20,05-10,2,#B2A48A bur oak,04-25,05-15,2,#B2A48A Kentucky yellowwood,05-10,05-30,4,#FFFFFF Chinese tree lilac,06-05,06-25,4,#FFFFFF crepe myrtle,06-15,07-30,5,#FF69B4 Japanese hornbeam,04-20,05-10,2,#B2A48A Japanese snowbell,05-25,06-15,4,#FFFFFF Atlantic white cedar,05-01,05-20,2,#A7B28D Norway spruce,05-10,05-30,2,#B2A48A cockspur hawthorn,04-20,05-15,4,#FFFFFF arborvitae,06-05,06-25,2,#A7B28D Turkish hazelnut,03-20,04-10,3,#FFE082 kousa dogwood,05-20,06-10,5,#FF83AA silver birch,04-20,05-10,2,#B2A48A black walnut,04-20,05-10,2,#B2A48A pine,04-25,05-15,2,#A7B28D blackgum,04-15,05-05,3,#8B0000 weeping willow,04-01,04-20,2,#B2A48A pagoda dogwood,04-15,05-05,3,#FFA Persian ironwood,02-20,03-10,3,#FFA07A eastern cottonwood,04-20,05-10,2,#B2A48A American beech,04-20,05-10,2,#B2A48A empress tree,05-10,05-30,4,#FFD700 Chinese fringetree,05-15,06-05,4,#FFFFFF two-winged silverbell,04-20,05-10,4,#FFFFFF paperbark maple,04-20,05-10,3,#FFA07A Oklahoma redbud,04-01,04-20,5,#FF82AB spruce,05-10,05-30,2,#B2A48A white pine,04-25,05-15,2,#A7B28D Amur cork tree,06-01,06-20,2,#A7B28D pond cypress,03-25,04-15,2,#A7B28D Chinese chestnut,05-15,06-05,3,#FFE082 sassafras,03-20,04-10,3,#FFE082 cucumber magnolia,04-25,05-15,4,#FFD700 mimosa,05-15,06-15,5,#FF69B4 tartar maple,04-20,05-10,3,#FFA07A holly,05-10,05-30,3,#FFFFFF southern magnolia,05-15,06-30,5,#FFFFFF blue spruce,05-10,05-30,2,#B2A48A European beech,04-20,05-10,2,#B2A48A red horse chestnut,05-05,05-25,4,#FF6347 black maple,04-15,05-05,3,#FFA07A trident maple,04-20,05-10,3,#FFA07A false cypress,04-25,05-15,2,#A7B28D red pine,04-25,05-15,2,#A7B28D pignut hickory,04-20,05-10,2,#B2A48A bigtooth aspen,04-01,04-20,2,#B2A48A eastern hemlock,05-10,05-30,2,#B2A48A Atlas cedar,05-01,05-20,2,#A7B28D Douglas-fir,04-25,05-15,2,#A7B28D southern red oak,04-25,05-15,2,#B2A48A quaking aspen,04-01,04-20,2,#B2A48A Ohio buckeye,04-15,05-05,3,#FFA07A Himalayan cedar,05-01,05-20,2,#A7B28D boxelder,04-15,05-05,3,#FFA07A Shantung maple,04-20,05-10,3,#FFA07A `31: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`32:[{"id":"25f9b317ce4d24bb5a02bb906603ad340fef401415c438b0be2f4179d4a651560c6a1eda75b1fa732515ca3c764d2ef595a81a3fe8948df22883097010ea4110","href":"https://static.observableusercontent.com/files/25f9b317ce4d24bb5a02bb906603ad340fef401415c438b0be2f4179d4a651560c6a1eda75b1fa732515ca3c764d2ef595a81a3fe8948df22883097010ea4110","name":"trees@1.parquet","type":"application/octet-stream","lastModified":1676905711492.948,"size":13937209},{"id":"4ab8b4d2201c8bfe73f92657030acfeb0a25f477baaf942f4bd5b30f5403161f4626a4eb089972d6cd998aff9799c6aebb5373124b27f5735863e871f42ac2fa","href":"https://static.observableusercontent.com/files/4ab8b4d2201c8bfe73f92657030acfeb0a25f477baaf942f4bd5b30f5403161f4626a4eb089972d6cd998aff9799c6aebb5373124b27f5735863e871f42ac2fa","name":"trees2@1.parquet","type":"application/octet-stream","lastModified":1676908148061.804,"size":15304409},{"id":"78e2faa8e5c157403fbe139c53daa1d98d54383a4a7b38c09e7599c5083debd7cb032711289fc0a9f092150b02fdbd1168be10d95fc611884623a71f50007cf0","href":"https://static.observableusercontent.com/files/78e2faa8e5c157403fbe139c53daa1d98d54383a4a7b38c09e7599c5083debd7cb032711289fc0a9f092150b02fdbd1168be10d95fc611884623a71f50007cf0","name":"trees2.parquet","type":"application/octet-stream","lastModified":1676906985806.511,"size":10297631},{"id":"e06845291244cebe5d6764d3c412dd2688dd8024dde2cdb4ee1c507cf73a41a8a701668d365c022228b58c1e362ebd41c4d196abb68a93f8ea3323ea53c82c89","href":"https://static.observableusercontent.com/files/e06845291244cebe5d6764d3c412dd2688dd8024dde2cdb4ee1c507cf73a41a8a701668d365c022228b58c1e362ebd41c4d196abb68a93f8ea3323ea53c82c89","name":"trees2@2.parquet","type":"application/octet-stream","lastModified":1676908582808.327,"size":6770147}] 2e:{"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(categorical, {\n value: \"spc_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(\"#plot\", 900, 600);\n await plot.plotAPI({\n arrow_table: enumerated,\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 trees\n SELECT spc_common,\n MEDIAN(tree_dbh::FLOAT) AS median_size,\n COUNT(*) AS count \n GROUP BY spc_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.spc_common}

${datum.address}, ${datum.boroname}

`;\n if (![\"spc_common\", \"address\", \"boroname\"].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":246,"value":"db.table(\"SELECT COUNT(*), boroname FROM trees GROUP BY ALL\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":454,"value":"db.table(\"SELECT COUNT(*) FROM trees\")","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: \"spc_common\",\n lambda\n }\n }\n // tooltip_html: `return \"${selected}: \" + datum['${selected}']`\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":19,"value":"enumerated = {\n enums_made;\n const sels = categorical.map((d) => ` ${d}::${d} AS ${d}`).join(\",\");\n const tb = await db.query(\n `SELECT row_number() OVER () AS ix, x::FLOAT x, y::FLOAT y, stump_diam::FLOAT stump_diam, tree_dbh::FLOAT tree_dbh, address,\n${sels} FROM trees`\n );\n return tb;\n}","pinned":false,"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.spc_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\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":414,"value":"html`\n\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":232,"value":"db.table(\n \"SELECT MAX(tree_dbh::FLOAT) dia, spc_common FROM trees GROUP BY spc_common ORDER by dia DESC\"\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":96,"value":"deepscatter = import(\"https://cdn.skypack.dev/deepscatter@2.13.0\").then(\n (d) => d.default\n)","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":22,"value":"enums_made = {\n return Promise.all(\n categorical.map((k) => {\n db.query(\n `CREATE TYPE ${k} AS ENUM (SELECT \"${k}\" FROM trees WHERE \"${k}\" IS NOT NULL)`\n );\n })\n );\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"trees2 = await db.query(\n \"CREATE TABLE trees2 AS SELECT * FROM 'https://benschmidt.org/treesbig.parquet'\"\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":8,"value":"db = {\n const db = await DuckDBClient.of({});\n await db.query(\n \"CREATE TABLE trees AS SELECT * FROM 'https://benschmidt.org/trees.parquet'\"\n );\n return db;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":495,"value":"db.table(\"SELECT * FROM trees2 LIMIT 1\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":9,"value":"trees2_arrow = {\n trees2;\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(`SELECT x, y * .66, ${sels} FROM trees2`);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":530,"value":"(\n await db.query(\n `SELECT spc_common, COUNT(*) as count FROM trees GROUP BY spc_common HAVING count < 2000 ORDER BY count DESC`\n )\n)\n .getChild(\"spc_common\")\n .toArray()\n .join(\"\\n\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":560,"value":"d3.csvParse(flowering).map((d) => {\n d.flower_start = new Date(\"2023-\" + d.flower_start);\n d.flower_end = new Date(\"2023-\" + d.flower_end);\n d.flower_mid = new Date((d.flower_end + d.flower_start) / 2);\n return d;\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":545,"value":"$30","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":"$31","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"},{"id":590,"value":"","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}