25:["$","title",null,{"children":"Growing Degree Days | AgroPatterns | Observable"}] 49:T44a,viewof fechas = Inputs.form( [ Inputs.select(sensores, { label: "Bloque", format: (x) => x.name }), Inputs.date({ label: "Fecha Incial", value: new Date(new Date().setDate(new Date().getDate() - 60)) }), Inputs.date({ label: "Fecha Final", value: new Date() }) ], { template: (inputs) => htl.html`

${Object.values(inputs)}

` } )4a:T462,viewof rangosTemp = Inputs.form( [ Inputs.range([0, 30], { value: 4, label: "Temp. Base (°C)", step: 1 }), Inputs.range([0, 15], { value: 4, label: "Temp. Min (°C)", step: 1 }), Inputs.range([15, 35], { value: 4, label: "Temp. Max (°C)", step: 1 }) ], { template: (inputs) => htl.html`

${Object.values(inputs)}

` } )4b:T430,WITH daily_gdd AS ( SELECT CAST(timestamp AS DATE) AS day, bloque, device_id, AVG(t) AS avg_temp, MIN(t) AS min_temp, -- Calculate the minimum temperature for the day MAX(t) AS max_temp, -- Calculate the maximum temperature for the day GREATEST(AVG(t) - ${rangosTemp[0]}, 0) AS gradosDia -- Calculate daily GDD based on a defined base temperature FROM sensorReadings WHERE t > ${rangosTemp[1]} -- Filter for temperatures greater than the minimum defined elsewhere AND t < ${rangosTemp[2]} -- Filter for temperatures less than the maximum defined elsewhere AND device_id = ${fechas[0].sensor} -- Filter by specific sensor GROUP BY CAST(timestamp AS DATE), bloque, device_id -- Group by day, bloque, and device_id ) SELECT day AS dia, bloque, device_id, ROUND(avg_temp, 1) AS avg_temp, ROUND(gradosDia, 1) AS gradosDia, ROUND(SUM(gradosDia) OVER (PARTITION BY bloque, device_id ORDER BY day), 1) AS acumuladoGD FROM daily_gdd WHERE day BETWEEN ${fechas[1]} AND ${fechas[2]} -- Filter by date range ORDER BY day DESC; 4c:T4ec,function convertToCSV(data) { if (!data || !data.length) { return ""; } const csvRows = []; // Get the headers from the first object in the array const headers = Object.keys(data[0]); csvRows.push(headers.join(",")); // Loop over the rows and create CSV lines for (const row of data) { const values = headers.map((header) => { let value = row[header]; // Format dates to a shorter version (e.g., YYYY-MM-DD) if (value instanceof Date) { value = value.toISOString().split("T")[0]; // Convert Date to YYYY-MM-DD } else if ( typeof value === "string" && /^\d{4}-\d{2}-\d{2}T/.test(value) ) { // Check if the value is an ISO string resembling a date and shorten it value = value.split("T")[0]; } // Check if the value is a number and has decimals if (typeof value === "number" && !Number.isInteger(value)) { value = value.toFixed(2); } // Handle cases where the value contains commas, quotes, or newlines if (typeof value === "string") { value = value.replace(/"/g, '""'); // Escape double quotes } return `"${value}"`; }); csvRows.push(values.join(",")); } return csvRows.join("\n"); }4d:[{"id":"98cbfe48d4de384ecf51a810242518f2813672304af94b90568cc3dea78ff4ff410d1c3970be7878f07c881b87f801ea0875a9d457a50d437523316099124436","href":"https://static.observableusercontent.com/files/98cbfe48d4de384ecf51a810242518f2813672304af94b90568cc3dea78ff4ff410d1c3970be7878f07c881b87f801ea0875a9d457a50d437523316099124436","name":"sensores@1.csv","type":"text/csv","lastModified":1734379634247.57,"size":548},{"id":"a11b86e3685657edb68dfe08bff8c1a4dd64741779851e4c26e410c575b984def856cfdc9943eb5ba1fc1473bfc5c542274b798b7214587cb2a70e8caa952e78","href":"https://static.observableusercontent.com/files/a11b86e3685657edb68dfe08bff8c1a4dd64741779851e4c26e410c575b984def856cfdc9943eb5ba1fc1473bfc5c542274b798b7214587cb2a70e8caa952e78","name":"sensores.csv","type":"text/csv","lastModified":1734367532045.164,"size":538},{"id":"bd666a3efefc10352c843f4d6bf876292ceb4482bfdd31816b34df1e11bfb1e068ed6b1d2450066732e5015ba9033a81ada356c7c53513f414355a6d165c672e","href":"https://static.observableusercontent.com/files/bd666a3efefc10352c843f4d6bf876292ceb4482bfdd31816b34df1e11bfb1e068ed6b1d2450066732e5015ba9033a81ada356c7c53513f414355a6d165c672e","name":"sensorTempHumidityReadings@3.parquet","type":"application/octet-stream","lastModified":1733795434949.691,"size":1570219},{"id":"82ab7a2233ca620d6a5c1ff62606f4ed9c4eb1fa4fbb75746bbf334258a5a190a0ad8aa4c9d294ffdd932a59ec0c2e34ff09860d6d88e9b1a5e88eaba440df92","href":"https://static.observableusercontent.com/files/82ab7a2233ca620d6a5c1ff62606f4ed9c4eb1fa4fbb75746bbf334258a5a190a0ad8aa4c9d294ffdd932a59ec0c2e34ff09860d6d88e9b1a5e88eaba440df92","name":"sensorTempHumidityReadings@4.parquet","type":"application/octet-stream","lastModified":1733796385138.249,"size":1570083}] 29:{"id":"fa03912ee0fb9a32","slug":null,"title":"Growing Degree Days","thumbnail":"c98e40e436e367cfd73ef24e44feefbbfb008459498cf877028c6d05cf7d4db0","access_level":"unlisted","create_time":"$D2024-12-09T14:36:53.236Z","update_time":"$D2024-12-16T20:07:13.840Z","view_time":null,"first_public_time":"$D2024-12-09T14:36:53.236Z","has_unpublished_edits":null,"fork":true,"stars":0,"starred":false,"owner":{"type":"team","id":"c4dbc581a6dcc8e0","login":"agropatterns","name":"AgroPatterns","avatar_url":"https://avatars-next.observableusercontent.com/bf43ed660cc2c271a795244cd7a6b313e0df26a68c42f0c14b7aed9f5653f769","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":803,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"7ff6bd0b10289728","login":"gcoch","name":"Gabriel Coch","avatar_url":"https://avatars-next.observableusercontent.com/9dd1ebc4f82239794f8c01fe1de0ea2d82a40676208a72285db42c35cfaac423","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"7ff6bd0b10289728","login":"gcoch","name":"Gabriel Coch","avatar_url":"https://avatars-next.observableusercontent.com/9dd1ebc4f82239794f8c01fe1de0ea2d82a40676208a72285db42c35cfaac423","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Growing Degree Days","cells":[{"id":600,"value":"# Growing Degree Days","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":0,"value":"titulo1 = htl.html`

Growing Degree Days

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":374,"value":"$49","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":619,"value":"$4a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":129,"value":"fechasGD = Plot.plot({\n subtitle: htl.html`

Grados Día Acumulados: Bloque: ${fechas[0].name}

`,\n color: { legend: true },\n width: width,\n height: 500,\n marginLeft: 60,\n padding: 0.2,\n marginRight: 20,\n grid: true,\n insetTop: 30,\n insetBottom: 10,\n style: {\n fontSize: 11,\n background: \"#fbffde\",\n color: \"black\"\n },\n x: { interval: \"day\" },\n y: { label: \"Grados Día - Acumulados\" },\n marks: [\n Plot.barY(sensor_data, {\n x: \"dia\",\n y: \"acumuladoGD\",\n fill: \"steelblue\",\n tip: true\n }),\n Plot.tickY(sensor_data, { x: \"dia\", stroke: \"red\", y: \"acumuladoGD\" }),\n Plot.ruleY([0]),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":275,"value":"$4b","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"sensor_data","database":"var:sensorsdb"},{"id":577,"value":"descargarArchivo = {\n let button = Inputs.button(\"Descargar\");\n\n button.addEventListener(\"click\", function () {\n // Assuming `sensor_data` contains the preformatted CSV content\n const blob = new Blob([datosCSV], { type: \"text/csv\" }); // Create a Blob with the CSV content\n const element = document.createElement(\"a\");\n element.href = URL.createObjectURL(blob);\n element.download = \"archivo.csv\"; // Set the filename\n element.style.display = \"none\";\n document.body.appendChild(element);\n element.click();\n document.body.removeChild(element);\n URL.revokeObjectURL(element.href); // Clean up\n });\n\n return button;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":527,"value":"$4c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":515,"value":"datosCSV = convertToCSV(sensor_data)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":534,"value":"function downloadCsvFile(filename, content) {\n // Convert JSON content to CSV\n const rows = Array.isArray(content) ? content : [content]; // Ensure content is an array\n const headers = Object.keys(rows[0]); // Get headers from the first object\n const csvContent = [\n headers.join(\",\"), // Add headers as the first row\n ...rows.map((row) =>\n headers.map((header) => JSON.stringify(row[header] ?? \"\")).join(\",\")\n ) // Add data rows\n ].join(\"\\n\");\n\n // Create a blob for the CSV content\n const blob = new Blob([csvContent], { type: \"text/csv\" });\n\n // Create a download link and trigger the download\n const element = document.createElement(\"a\");\n element.href = URL.createObjectURL(blob);\n element.download = filename.endsWith(\".csv\") ? filename : `${filename}.csv`; // Ensure .csv extension\n element.style.display = \"none\";\n document.body.appendChild(element);\n element.click();\n document.body.removeChild(element);\n URL.revokeObjectURL(element.href);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":29,"value":"sensorsdb = await DuckDBClient.of({\n sensorReadings: sensorTemp\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":710,"value":"SELECT CAST(timestamp AS DATE) AS day, timestamp,\n t AS temperature, \n h AS humidity, bloque\n FROM sensorReadings\nWHERE bloque = ${fechas[0].name}\nAND day BETWEEN ${fechas[1]} AND ${fechas[2]} -- Filter by date range\nORDER BY day DESC;","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:sensorsdb"},{"id":702,"value":"DESCRIBE sensorReadings","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:sensorsdb"},{"id":768,"value":"viewof finca = Inputs.select(\n d3.group(sensores1, (d) => d.group_id),\n { label: \"Grupo\" }\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":798,"value":"data","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":788,"value":"finca[0].group_id","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":426,"value":"sensores = [\n { name: \"Bloque 33\", code: \"sensorReadings\", sensor: \"dev:867730051163834\" },\n { name: \"Bloque 38\", code: \"b38\", sensor: \"dev:867730051164220\" }\n]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":755,"value":"sensores1 = await FileAttachment(\"sensores@1.csv\").csv({typed: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"sensores1","database":"$undefined"},{"id":786,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"sensores@1.csv"},{"id":751,"value":"sensorTemp = FileAttachment(\"sensorTempHumidityReadings@4.parquet\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":782,"value":"data = d3.filter(sensores1, (d) => d.group_id == finca[0].group_id, { sort: true })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4d","stdlib":"1"}} 24:null