28:null 29:["$","title",null,{"children":"Derwent River data - RiverSense Project - moving average, detrend | Charles Boisvert | Observable"}] 2e:T693,function prepareCorrelPair(collection,fieldX,fieldY) { // create a set with two columns for calculations // inserting null values as appropriate where data is missing (data should be every 12 minutes) const subsetX = collection.filter((o)=>(o.field==fieldX)) const subsetY = collection.filter((o)=>(o.field==fieldY)) const result = [] let indexX=0, indexY=0, valueX, valueY, dateTime, previousDateTime, dateTimeX, dateTimeY while ((indexX(15*60*1000) } function add12minutes(dateTime) { // in progress const d = (new Date(dateTime)).getTime() const dplus12 = (new Date (d + 12*60*1000)).toISOString() return dplus12 } } 2f:T142c,/* sets the data in suitable format for Vega charts and calculate moving average stats one pass to transform each line into objects for: temperature, do2, moving average of each object has the date in ISO format */ function prepare_data(table) { let result = [] // for 24-hr moving averages // moving average indexes and running totals let temperatureResultSubset = [] // subset of result with the recent temperature objects to average let DOResultSubset = [] // ibid. DO2 let countDO = 0 let sumTemperature = 0 let sumDO = 0 table.forEach(processLine) return result function ISODate(date) { // input date DD/MM/YYYY const year = date.substring(6,10) const month = date.substring(3,5) const day = date.substring(0,2) return `${year}-${month}-${day}` } function over24Hours(beginDateTime,endDateTime) { const begin = new Date(beginDateTime) const end = new Date(endDateTime) return (end-begin)>(24*60*60*1000) } function processLine(line) { const temperature = line["Temperature"] const do2 = line["mg/L DO"] const recordTemp = (temperature!="") const recordO2 = (do2!="") if (recordTemp || recordO2 ) { const dateStg = line["Date"] const timeStg = line['Time'] const dateTime = `${ISODate(dateStg)} ${timeStg}` if (recordTemp) { const temp = parseFloat(temperature) const countTemperature = temperatureResultSubset.length if (countTemperature>=minThresholdReadingsCount && over24Hours(temperatureResultSubset[0].DateTime,dateTime)) { temperatureResultSubset = temperatureResultSubset.slice(1) // next avg, all but the first const movingAvgTemp = sumTemperature/countTemperature // use countT/2 to position the mvg average in the middle of the window; countT to position it at the end (average of the past day) //const midRangeTempSubset = temperatureResultSubset[countTemperature]-1 const midRangeTempSubset = temperatureResultSubset[Math.floor(countTemperature/2)] const objMovingAvgTemp = { field: "Temperature - 24hr moving average (C)", DateTime: midRangeTempSubset.DateTime, value: movingAvgTemp, Date: midRangeTempSubset.Date } result.push(objMovingAvgTemp) const detrendedTemp = midRangeTempSubset.value-movingAvgTemp const objDetrendedTemp = { field: "Detrended temperature variation (C)", DateTime: midRangeTempSubset.DateTime, value: detrendedTemp, Date: midRangeTempSubset.Date } result.push(objDetrendedTemp) // debug data: uncomment to add to the result //const objMovingAvgDebug = // { field: "moving avg debug", DateTime: movingAvgTempDateTime, // countTemp: countTemperature, sumTemp: sumTemperature, // startDate:temperatureResultSubset[0].DateTime, // over24Hours: over24Hours(temperatureResultSubset[0].DateTime,dateTime) } //result.push(objMovingAvgDebug) sumTemperature -= temperatureResultSubset[0].value } const objTemp = { field: "Temperature (C)", DateTime: dateTime, value: temp, Date: dateStg } temperatureResultSubset.push(objTemp) sumTemperature += temp result.push(objTemp) } if (recordO2) { const dissO = parseFloat(do2) const countDO = DOResultSubset.length if (countDO>=minThresholdReadingsCount && over24Hours(DOResultSubset[0].DateTime,dateTime)) { DOResultSubset = DOResultSubset.slice(1) // next avg, all but the first const movingAvgDO = sumDO/countDO // use countDO/2 to position the mvg average in the middle of the window; countDO to position it at the end (average of the past day) // const midRangeDOSubset = DOResultSubset[countDO]-1 const midRangeDOSubset = DOResultSubset[Math.floor(countDO/2)] const objMovingAvgDO = { field: "Dissolved O2 - 24hr moving average (mg/l)", DateTime: midRangeDOSubset.DateTime, value: movingAvgDO, Date: midRangeDOSubset.Date } result.push(objMovingAvgDO) const detrendedDO = midRangeDOSubset.value-movingAvgDO const objDetrendedDO = { field: "Detrended dissolved O2 variation (mg/l)", DateTime: midRangeDOSubset.DateTime, value: detrendedDO, Date: midRangeDOSubset.Date } result.push(objDetrendedDO) sumDO -= DOResultSubset[0].value } const objDO = { field:"Dissolved O2 (mg/l)", DateTime: dateTime, value: dissO, Date: dateStg } DOResultSubset.push(objDO) sumDO += dissO result.push(objDO) } } } } 30:T436,// Adapted from: https://stackoverflow.com/questions/15886527/javascript-library-for-pearson-and-or-spearman-correlations function pearsonCorrel(correlPair,shift=0) { // takes data in the form of a "correlPair", array of objects {date:_, fieldX:_, fieldY:_} // dates should be every 12 minutes, and fieldX and fieldY are null when data is missing. // calculations are adapted so that null values are ignored, not treated as 0 (!) const xArray = correlPair.map((item)=>(item.fieldX)) const yArray = correlPair.map((item)=>(item.fieldY)) let sumX = 0, sumY = 0, sumXY = 0, sumX2 = 0, sumY2 = 0, N = 0 const length = xArray.length = yArray.length = Math.min(xArray.length, yArray.length) let index=shift while (indexThis work is licensed under CC BY-SA 4.0 $\"\"$ $\"\"$ $\"\"$

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":1130,"value":"A preliminary analysis of river data, to explore the possibility of detecting anomalies in the dissolved oxygen levels, or of modeling oxygen levels to predict them. Data sensing provided by Aquasensor (https://aquasensor.co.uk/sensor).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1097,"value":"dataSource = dataSource23 // set to datasource23 or datasource24 for the data that year","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":97,"value":"dataSource23 = FileAttachment(\"riverdata2024-01-19@2.csv\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1093,"value":"dataSource24 = FileAttachment(\"sensor22-2024-07-31 to 2024-12-24@2.csv\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":970,"value":"The line graphs of temperature and dissolved oxygen clearly shows daily variations of both, and the correlation between them. There is a physical relationship between temperature and the amount of dissolved oxygen because in water water, molecular movement causes dissolved oxygen to evaporate.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":74,"value":"{\n return vl.markLine()\n .data(filteredData.filter((l)=>((l.field==\"Temperature (C)\")||(l.field==\"Dissolved O2 (mg/l)\"))))\n .encode(\n vl.x().fieldT('DateTime').timeUnit(\"monthdatehoursminutes\").title(\"Date\"),\n vl.y().fieldQ('value').title('Temperature (C), DO (mg/l)'),\n vl.color().fieldN(\"field\")\n ).\n width(600).\n render()\n}\n\n\n\n\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":475,"value":"viewof range = interval([0, datesList.length-1], {\n label: 'Select the dates to graph',\n step: 1,\n format: ([start, end]) => `${datesList[start]} … ${datesList[end]}`\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":985,"value":"The correlation between temperature and DO2 is ${pearsonCorrel(prepareCorrelPair(PData,\"Temperature (C)\",\"Dissolved O2 (mg/l)\"),0)}.\nBut inspecting the graphs carefully, there is a phase shift between temperature and DO changes: the DO changes hours after temperature. The strongest (lowest negative) correlation can be calculated:
\nPhase shift: ${Math.floor(shift/5)} hours ${(shift%5)*12} minutes
\nCorrelation: ${pearsonCorrel(prepareCorrelPair(PData,\"Temperature (C)\",\"Dissolved O2 (mg/l)\"),shift)}","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":997,"value":"viewof shift = Inputs.range([0, 120], {value: 30, step: 1, label:'Adjust phase shift (number of 12 minute intervals)'})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":972,"value":"To better understand the data, we can calculate the trend (the daily moving average). When graphing the trends alongside temperature and DO2 readings, the daily cycle becomes more apparent and so do some sharp temporary drops in oxygen levels:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":949,"value":"{\n return vl.markLine()\n .data(filteredData.filter((l)=>((l.field!=\"Detrended temperature variation (C)\")&&(l.field!=\"Detrended dissolved O2 variation (mg/l)\"))))\n .encode(\n vl.x().fieldT('DateTime').timeUnit(\"monthdatehoursminutes\").title(\"Date\"),\n vl.y().fieldQ('value').title('Value'),\n vl.color().fieldN(\"field\").scale({ range: [\"steelblue\",\"green\",\"orange\",\"red\"] })\n ).\n width(600).\n render()\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1035,"value":"The long-term (over days) trend also exhibits correlation between temperature and oxygen levels. Ignoring phase shift the correlation is stronger than between the raw values, at ${pearsonCorrel(prepareCorrelPair(PData,\"Temperature - 24hr moving average (C)\",\"Dissolved O2 - 24hr moving average (mg/l)\"),0)}. Taking phase shift into account:
\nPhase shift: ${Math.floor(trendShift/5)} hours ${(trendShift%5)*12} minutes
\nCorrelation: ${pearsonCorrel(prepareCorrelPair(PData,\"Temperature - 24hr moving average (C)\",\"Dissolved O2 - 24hr moving average (mg/l)\"),trendShift)}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1037,"value":"viewof trendShift = Inputs.range([0, 120], {value: 59, step: 1, label:'Trend phase shift (number of 12 minute intervals)'})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1049,"value":"Finally, detrending temperature and oxygen readings (by subtracting trend from actual value), we can track the daily variations of temperature and oxygen from the trend:","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":944,"value":"{\n return vl.markLine()\n .data(filteredData.filter((l)=>((l.field==\"Detrended temperature variation (C)\")||(l.field==\"Detrended dissolved O2 variation (mg/l)\"))))\n .encode(\n vl.x().fieldT('DateTime').timeUnit(\"monthdatehoursminutes\").title(\"Date\"),\n vl.y().fieldQ('value').title('Value'),\n vl.color().fieldN(\"field\").scale({ range: [\"lightgreen\",\"yellow\"] })\n )\n .width(600)\n .render()\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1053,"value":"They are also correlated, though without the phase shift it does not appear so (${pearsonCorrel(prepareCorrelPair(PData,\"Detrended temperature variation (C)\",\"Detrended dissolved O2 variation (mg/l)\"),0)}), but with the phase shift there is a strong correlation in the detrended data also:
\nPhase shift: ${Math.floor(detrendShift/5)} hours ${(detrendShift%5)*12} minutes
\nCorrelation: ${pearsonCorrel(prepareCorrelPair(PData,\"Detrended temperature variation (C)\",\"Detrended dissolved O2 variation (mg/l)\"),detrendShift)}","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":1058,"value":"viewof detrendShift = Inputs.range([0, 120], {value: 68, step: 1, label:'Detrended data phase shift (number of 12 minute intervals)'})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1071,"value":"Separating trend and daily variations result in stronger correlations between temperature and oxygen levels. This will enable a model to predict the expected level of dissolved oxygen, based on the water temperature. But the detrended data also shows the drops in oxygen levels much more clearly. Inspecting the data, it appears that the DO levels usually fall by no more than 0.5 ppm below trend. But they exhibit short sharp falls, 1ppm below trend and more, on several occasions. These offer a good opportunity to signal potential pollution events.","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":646,"value":"**Sample of filtered data:**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":48,"value":"printTable(filteredData.slice(229,241))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":259,"value":"md`---------------`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":17,"value":"md `## Below: libraries, functions, boring operations`\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":285,"value":"md`Sample - unprocessed data`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":14,"value":"printTable(data.slice(0, 5))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":901,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":67,"value":"md `Select and transpose the data`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":799,"value":"(prepareCorrelPair(PData,\"Temperature (C)\",\"Dissolved O2 (mg/l)\"))//.filter((o)=>(o.fieldX==null))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":719,"value":"$2e","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":9,"value":"data = dataSource.csv()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":180,"value":"function getDates(table) { // select the country/region column\n let result = [];\n let previousDate = null\n table.forEach(function(line) {\n const date = line[\"Date\"]\n if (date != previousDate) {\n result.push(date)\n previousDate = date\n }\n });\n return result;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":485,"value":"datesList = getDates(data)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":38,"value":"function byDates(min,max) {\n return function (line) {\n const i = datesList.indexOf(line[\"Date\"]);\n return i>=min && i<=max\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":43,"value":"filteredData = PData.filter(byDates(range[0],range[1])) // dates is user selection","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":51,"value":"PData = prepare_data(data)\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":588,"value":"temp_series = []","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":601,"value":"minThresholdReadingsCount = 5","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":57,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":664,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":316,"value":"md`Vega lite: plots`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":2,"value":"import {vl} from '@vega/vega-lite-api'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":317,"value":"md`Data Utils from Uni of Washington State; printTable is great, but unique filter inefficient (https://jsperf.com/unique-through-set-vs-loop/1)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":12,"value":"import {printTable} from '@uwdata/data-utilities'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":324,"value":"md`D3 for csv import`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":8,"value":"d3 = require(\"d3\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":325,"value":"md`Form input functions. Thank you https://observablehq.com/@mootari`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":203,"value":"import {select} from '@jashkenas/inputs'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":471,"value":"import {interval} from '@mootari/range-slider'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"}],"files":"$W31","stdlib":"1","titling":"auto"}} 2d:"$2c:body"