30:T75c,/* this VegaLite area chart one is probably better to aim for https://observablehq.com/@uwdata/multi-view-composition */ { const data_ = weatherFetch .filter((d) => +d.date > earliestDateFromAranet /*+utc(2022, 11, 3)*/) .map((d) => ({ ...d, source: "noaa", temperatureF: (9 * d.air_temp + 160) / 5 })); const o = Object.assign( {}, { thresholds: [400, 900, 1100, 2000, 2500], width: width, height: 300, ticks: null, co2domain: [430, 1500], timeDomain: null } ); const gradientId = DOM.uid(); return multiLayerX( { marginLeft: 70, marginRight: 50, marginBottom: 50, width: Math.min(width), height: 400, x: { tickRotate: -45, grid: true, ticks: 24 }, y: { axis: "left", domain: [400, 2000], label: "CO₂ (ppm) ↑" } }, // base mark Plot.lineY(dataSourceQueryChosen, { stroke: `url(#${gradientId.id})`, x: "date", y: "co2" }), // second layer (x) => [ /* TODO combine data_ and dataSourceQueryChosen to use: Plot.areaY(sftemp, Plot.windowY({ x: "date", y1: "low", y2: "high", fill: "#ccc", k: 14 })), */ Plot.lineY(data_, { x: "date", y: "temperatureF", stroke: "rgba(0, 0, 0, 0.25)" }), Plot.line(dataSourceQueryChosen, { x: "date", y: "temperatureF", stroke: "rgba(0, 0, 0, 0.25)", strokeWidth: 1 }), Plot.frame({ stroke: "#CCC" }), colorGradient(dataSourceQueryChosen, o.thresholds, gradientId.id, true) // Plot.dot(dataSourceQueryChosen, { // x: "date", // y: "temperatureF", // stroke: "steelblue", // r: 0.01, // strokeWidth: 2, // fill: "white" // }) ] ); }31:T460,{ const data_ = weatherFetch .filter((d) => +d.date > earliestDateFromAranet /*+utc(2022, 11, 3)*/) //&& +d.date < +utc(2022, 9, 10)) // .filter( // (d) => // +d.date > +liveObjectWithRealDate[0].date && // +d.date < // +liveObjectWithRealDate[liveObjectWithRealDate.length - 1].date // ) .map((d) => ({ ...d, atmosphericPressure: d.sea_level_pressure, source: "noaa" })); const pl = Plot.plot({ width: width, y: { grid: true, domain: [980, 1040], label: "sea level pressure ↑" // type: "categorical", // scheme: "accent" }, color: { legend: true }, marks: [ Plot.ruleY([0]), Plot.lineY(data_, { x: "date", y: "atmosphericPressure", stroke: "source" }), Plot.line(dataSourceQueryChosen, { x: "date", // y: "temperatureFChange", y: "atmosphericPressure", stroke: "source" // stroke: "#000", // strokeWidth: 1, // stroke: `url(#${gradientId.id})` }) ] }); return pl; }32:T4c3,{ const data_ = weatherFetch .filter( (d) => +d.date > earliestDateFromAranet /*+utc(2022, 11, 3)*/ /*&& +d.date < +utc(2022, 11, 18)*/ ) // .filter( // (d) => // +d.date > +liveObjectWithRealDate[0].date && // +d.date < // +liveObjectWithRealDate[liveObjectWithRealDate.length - 1].date // ) .map((d) => ({ ...d, source: "noaa", temperatureF: (9 * d.air_temp + 160) / 5, /* https://www.omnicalculator.com/physics/relative-humidity */ relativeHumidity: Math.exp((17.625 * d.dew_temp) / (243.04 + d.dew_temp)) / Math.exp((17.625 * d.air_temp) / (243.04 + d.air_temp)) })); const pl = Plot.plot({ width: width, y: { grid: true, domain: [0, 1], label: "Humid ↑" }, color: { legend: true }, marks: [ Plot.ruleY([0]), Plot.lineY(data_, { x: "date", y: "relativeHumidity", stroke: "source" }), Plot.line(dataSourceQueryChosen, { x: "date", y: "relativeHumidity", stroke: "source" // stroke: "#000", // strokeWidth: 1, // stroke: `url(#${gradientId.id})` }) ] }); return pl; }33:T454,co2Chart = (data, opts) => { /* start co2domain at ambient atmospheric */ const o = Object.assign( {}, { thresholds: [400, 900, 1100, 2000, 2500], width: width, height: 300, ticks: null, co2domain: [414, 1500], timeDomain: null }, opts ); const data_ = o.timeDomain ? data.filter( (d) => +d.date > +o.timeDomain[0] && +d.date < +o.timeDomain[1] ) : data; const gradientId = DOM.uid(); return Plot.plot({ width: o.width, height: o.height, x: { scale: "utc", grid: true, domain: o.timeDomain || d3.extent(data, (d) => d.date) }, y: { ticks: o.ticks || [0].concat(o.thresholds), grid: true, domain: o.co2domain, label: "CO₂ (ppm) ↑" }, marks: [ Plot.line(data_, { x: "date", y: "co2", stroke: "#000", strokeWidth: 1, title: (d) => "CO2", stroke: `url(#${gradientId.id})` }), Plot.frame({ stroke: "#CCC" }), colorGradient(data_, o.thresholds, gradientId.id, true) ] }); }34:[{"id":"a112d1d244fcc25804db7f162453f01541c9a66b3401ba6e7b0a67ab3fcb4db4588bb07be50a35f68db2a342afdb84488a2fa8d41d2a7ca29bea49f4abbf3c1b","href":"https://static.observableusercontent.com/files/a112d1d244fcc25804db7f162453f01541c9a66b3401ba6e7b0a67ab3fcb4db4588bb07be50a35f68db2a342afdb84488a2fa8d41d2a7ca29bea49f4abbf3c1b","name":"watermelon.db","type":"application/x-sqlite3","lastModified":1664847754525.076,"size":5697536},{"id":"942561fbdc115ac184e198b6e155657c91bf4cc572e19030b72ee588c971d12f6274ec9de94274052e89567c557d3a88d62da58487453d1d77b77574b42052bd","href":"https://static.observableusercontent.com/files/942561fbdc115ac184e198b6e155657c91bf4cc572e19030b72ee588c971d12f6274ec9de94274052e89567c557d3a88d62da58487453d1d77b77574b42052bd","name":"watermelon-dec24.db","type":"application/x-sqlite3","lastModified":1672149894420.138,"size":5984256}] 2e:{"title":"Aranet4 explorer","cells":[{"id":0,"value":"# Aranet4 explorer\n

[Docs](https://observablehq.com/@observablehq/sqlite)

\n\n- Buy Aranet Home or Pro CO2 sensor. Bring it near computer for bluetooth connection.\n- `pip install datasette`\n- Then, on Apple Silicon mac, install Aranet4 iPad app from Apple App Store, open the app and press a metric to load the graph data\n- Then load the `watermelon.db` file from the app with `datasette`:\n\n`datasette $(find ~/Library/Containers/*/Data/Documents/watermelon.db) --cors --setting max_returned_rows 20000`\n\n\n- Remember not to try to access `localhost` in Safari with iCloud Privacy Shield turned on.\n- See also https://observablehq.com/@harrislapiroff/aranet\n- Note the app only retains your data for the past 15 days or so, so make backups of the db file if you want to retain it.\n","pinned":false,"hidden":false,"mode":"md"},{"id":645,"value":"viewof dataSourceQueryChosen = {\n function sleep(ms) {\n return new Promise((resolve) => {\n setTimeout(resolve, ms);\n });\n }\n await sleep(\n 500\n ); /* wait for localhost data to load into window.liveObjectWithRealDate */\n const liveObjectIfRunning = sqliteRunning\n ? eval(\"window.liveObjectWithRealDate\")\n : \"n/a\";\n return Inputs.radio(\n new Map([\n [\"Demo data (attached by data pane)\", demoObjectWithRealDate],\n [\n \"Live Datasette Data from localhost (see details above)\",\n liveObjectIfRunning\n ]\n ]),\n {\n label: \"Select a data source\",\n format: (x) => x[0],\n value:\n sqliteRunning === false ? demoObjectWithRealDate : liveObjectIfRunning\n }\n );\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"demodb = FileAttachment(\"watermelon-dec24.db\").sqlite()","pinned":false,"hidden":false,"mode":"ojs"},{"id":73,"value":"livedb = new DatasetteClient(\"http://127.0.0.1:8001/watermelon\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":775,"value":"sqliteRunning = {\n let meh = {};\n try {\n await livedb.describe();\n return true;\n } catch (e) {\n return false;\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1000,"value":"{\n dataSourceQueryChosen;\n return liveObjectWithRealDate;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":712,"value":"### Quick Demo SQLite data format preview","pinned":false,"hidden":false,"mode":"md"},{"id":33,"value":"viewof dbTable = Inputs.select(\n (await demodb.describe()).value.map((d) => d.name).reverse(),\n { label: \"Table\" }\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":9,"value":"data = Inputs.table(demodb.query(`SELECT * FROM ${dbTable} LIMIT 2`))","pinned":false,"hidden":false,"mode":"ojs"},{"id":715,"value":"### Databases queried for first derivative of each value","pinned":false,"hidden":false,"mode":"md"},{"id":152,"value":"Select -- a.timestamp as StartTime, b.timestamp as EndTime\n datetime(a.timestamp/1000, 'unixepoch', 'localtime') as start,\n strftime('%Y-%m-%dT%H:%M:%fZ',datetime(a.timestamp/1000, 'unixepoch', 'localtime')) as date,\n a.co2 as co2,\n --a.temperature as temperatureC,\n (9*a.temperature + 160) / 5 as temperatureF,\n -- b.timestamp-a.timestamp as TimeChange,\n b.co2-a.co2 as co2Change,\n -- b.temperature-a.temperature as temperatureCChange,\n ((9*b.temperature + 160) / 5)-((9*a.temperature + 160) / 5) as temperatureFChange,\n a.pressure as atmosphericPressure,\n b.pressure-a.pressure as pressureChange,\n a.humidity/100 as relativeHumidity\nFROM logs a, logs b\nWHERE b.timestamp = (Select MIN(c.timestamp) FROM logs c WHERE c.timestamp>a.timestamp) and a.temperature is NOT NULL\norder by start desc\n-- LIMIT 10000","pinned":false,"hidden":false,"mode":"sql","output":"demoObject","database":"var:demodb"},{"id":1239,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":58,"value":"Select -- a.timestamp as StartTime, b.timestamp as EndTime\n datetime(a.timestamp/1000, 'unixepoch', 'localtime') as start,\n strftime('%Y-%m-%dT%H:%M:%fZ',datetime(a.timestamp/1000, 'unixepoch', 'localtime')) as date,\n a.co2 as co2,\n --a.temperature as temperatureC,\n (9*a.temperature + 160) / 5 as temperatureF,\n -- b.timestamp-a.timestamp as TimeChange,\n b.co2-a.co2 as co2Change,\n -- b.temperature-a.temperature as temperatureCChange,\n ((9*b.temperature + 160) / 5)-((9*a.temperature + 160) / 5) as temperatureFChange,\n a.pressure as atmosphericPressure,\n b.pressure-a.pressure as pressureChange,\n a.humidity/100 as relativeHumidity\nFROM logs a, logs b\nWHERE b.timestamp = (Select MIN(c.timestamp) FROM logs c WHERE c.timestamp>a.timestamp)\norder by start desc\n-- LIMIT 10000\n-- OFFSET 10000","pinned":false,"hidden":false,"mode":"sql","output":"liveObject","database":"var:livedb"},{"id":193,"value":"Query via https://stackoverflow.com/questions/3986889/discrete-derivative-in-sql","pinned":false,"hidden":false,"mode":"md"},{"id":261,"value":"co2Chart(dataSourceQueryChosen, {\n thresholds: [450, 850, 1100, 2500, 2500],\n ticks: [0, 450, 850, 1100, 2000]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":929,"value":"### Weather outside v inside","pinned":false,"hidden":false,"mode":"md"},{"id":1102,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":479,"value":"//alternative data source with perhaps better relativeHumidity data\nstationGlobalHourlyToYesterday = (\n await d3.csv(\n `https://www.ncei.noaa.gov/data/global-hourly/access/${new Date().getFullYear()}/${\n station.id\n }.csv`\n )\n).reverse()","pinned":false,"hidden":false,"mode":"ojs"},{"id":510,"value":"station = (// ({\n// id: \"72509014739\",\n// USAF: \"725090\",\n// WBAN: \"14739\",\n// name: \"GEN E L LOGAN INTERNATIONAL AIRPORT\",\n// country: \"US\",\n// state: \"MA\",\n// lat: 42.361,\n// lon: -71.01,\n// elevation: 3.3,\n// begin: \"1943-11-21T04:00:00.000Z\",\n// end: \"2022-10-04T04:00:00.000Z\"\n// })\n{\n id: \"72505914702\",\n USAF: \"725059\",\n WBAN: \"14702\",\n name: \"LAURENCE G HANSCOM FLD APT\",\n country: \"US\",\n state: \"MA\",\n lat: 42.47,\n lon: -71.289,\n elevation: 40.5,\n begin: \"2006-01-01T05:00:00.000Z\",\n end: \"2022-10-04T04:00:00.000Z\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":494,"value":"weatherFetch = processISDLite(isd, station).reverse()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1165,"value":"earliestDateFromAranet = new Date(\n Math.min.apply(\n null,\n dataSourceQueryChosen.map((n) => n.date)\n )\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":520,"value":"{\n const data_ = weatherFetch\n .filter((d) => +d.date > earliestDateFromAranet /*+utc(2022, 11, 3)*/) //&& +d.date < +utc(2022, 9, 10))\n // .filter(\n // (d) =>\n // +d.date > +liveObjectWithRealDate[0].date &&\n // +d.date <\n // +liveObjectWithRealDate[liveObjectWithRealDate.length - 1].date\n // )\n .map((d) => ({\n ...d,\n source: \"noaa\",\n temperatureF: (9 * d.air_temp + 160) / 5\n }));\n const pl = Plot.plot({\n width: width,\n y: {\n grid: true,\n domain: [30, 74],\n label: \"temp F ↑\"\n },\n color: { legend: true },\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(data_, { x: \"date\", y: \"temperatureF\", stroke: \"source\" }),\n Plot.line(dataSourceQueryChosen, {\n x: \"date\",\n // y: \"temperatureFChange\",\n y: \"temperatureF\",\n stroke: \"source\"\n // stroke: \"#000\",\n // strokeWidth: 1,\n // stroke: `url(#${gradientId.id})`\n })\n ]\n });\n return pl;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1097,"value":"import { multiLayerX } from \"@observablehq/dual-axis-bar-line-chart\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1038,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":576,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":608,"value":"data_ = weatherFetch\n .filter((d) => +d.date > +utc(2022, 8, 22) && +d.date < +utc(2022, 9, 10))\n // .filter(\n // (d) =>\n // +d.date > +liveObjectWithRealDate[0].date &&\n // +d.date <\n // +liveObjectWithRealDate[liveObjectWithRealDate.length - 1].date\n // )\n .map((d) => ({\n ...d,\n temperatureF: (9 * d.air_temp + 160) / 5,\n relativeHumidity:\n 100 *\n (Math.exp((17.625 * d.dew_temp) / (243.04 + d.dew_temp)) /\n Math.exp((17.625 * d.air_temp) / (243.04 + d.air_temp)))\n }))\n .reverse()","pinned":false,"hidden":false,"mode":"ojs"},{"id":957,"value":"### Experiments with Change and Rate of Change","pinned":false,"hidden":false,"mode":"md"},{"id":161,"value":"{\n const gradientId = DOM.uid();\n const pl = Plot.plot({\n marks: [\n Plot.ruleY([0]),\n Plot.line(dataSourceQueryChosen, {\n x: \"date\",\n // y: \"temperatureFChange\",\n y: \"co2Change\"\n // stroke: \"#000\",\n // strokeWidth: 1,\n // stroke: `url(#${gradientId.id})`\n })\n // Plot.line(liveObjectWithRealDate, {\n // x: \"date\",\n // y: \"temperatureFChange\"\n // stroke: `url(#${gradientId.id})`\n\n // y: \"co2Change\"\n // }),\n // colorGradient(data, [400, 800, 1000, 5000, 2070], gradientId.id, true)\n ]\n });\n\n return pl;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":122,"value":"Plot.plot({\n width: width,\n marks: [\n Plot.linearRegressionY(dataSourceQueryChosen.slice(-10), {\n x: \"start\",\n y: \"co2Change\",\n stroke: \"temperatureFChange\"\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":976,"value":"Wrangler(dataSourceQueryChosen)","pinned":true,"hidden":false,"mode":"ojs"},{"id":978,"value":"// To use copied code replace \"data\" with your own variable\naq\n .from(dataSourceQueryChosen)\n .groupby(\"date\")\n .rollup({ mean: (d) => op.mean(d[\"co2Change\"]), count: (d) => op.count() })\n// .objects() // Uncomment to return an array of objects","pinned":true,"hidden":false,"mode":"ojs"},{"id":953,"value":"### Code","pinned":false,"hidden":false,"mode":"md"},{"id":272,"value":"/* NOTE because of demo-data detection trick, you have to refresh the page after changing this */\nliveObjectWithRealDate = {\n let live;\n try {\n live = liveObject.map((d) => ({\n ...d,\n date: new Date(d.date),\n source: \"aranet\"\n }));\n } catch (e) {\n console.log(\"localhost not running\");\n return true;\n }\n window.liveObjectWithRealDate = live;\n return live;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":210,"value":"import { colorGradient, utc } from \"@harrislapiroff/aranet\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":463,"value":"import {\n processISDLite,\n fetchISDLite,\n cityCollection,\n filteredCities,\n usCitiesMinPop\n} from \"@observablehq/noaa-weather-data-by-major-u-s-city\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":356,"value":"demoObjectWithRealDate = demoObject.map((d) => ({\n ...d,\n date: new Date(d.date)\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":208,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":75,"value":"import { DatasetteClient } from \"@ambassadors/datasette-client\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":967,"value":"import { Wrangler, op } from \"@observablehq/data-wrangler\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":501,"value":"isd = fetchISDLite(station, 2022)","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W34","stdlib":"1","titling":"auto"}