29:null 2a:["$","title",null,{"children":"Split overlapping time objects | Andy Reagan | Observable"}] 2f:T961,split = { var x = new Array(); var first_match = 0; for (var d in data) { // console.log(data[d], first_match, found_first_match); var found_first_match = false; for (var t = first_match; t < all_times.length; t++) { console.log( t, first_match, all_times[t].format("YYYY-MM-DDTHH:mm"), data[d].startDate.format("YYYY-MM-DDTHH:mm"), data[d].endDate.format("YYYY-MM-DDTHH:mm") ); if (data[d].startDate < all_times[t] && all_times[t] <= data[d].endDate) { console.log("found a time"); if (!found_first_match) { console.log("found first match at", t); first_match = t; found_first_match = true; } var startDate = data[d].startDate; var endDate = all_times[t]; if (t > 0) { if (data[d].startDate < all_times[t - 1]) { var startDate = all_times[t - 1]; } } var endDate = all_times[t].subtract(1, "second"); var endDate = all_times[t].subtract(1, "second"); x.push({ ...data[d], startDate: startDate, endDate: endDate, value: (((endDate - startDate) / 1000 + 1) / ((data[d].endDate - data[d].startDate) / 1000 + 1)) * data[d].value }); } else if ( data[d].startDate < all_times[t - 1] && all_times[t - 1] <= data[d].endDate ) { console.log("catching hanging times"); var startDate = all_times[t - 1]; var endDate = data[d].endDate; x.push({ ...data[d], startDate: startDate, endDate: endDate, value: (((endDate - startDate) / 1000 + 1) / ((data[d].endDate - data[d].startDate) / 1000 + 1)) * data[d].value }); } else if (all_times[t] > data[d].endDate) { console.log("breaking"); break; } } } // add the aggTime to each row we just created for (var d in x) { for (var i = 0; i < agg_times.length - 1; i++) { if (Math.abs(x[d].startDate - agg_times[i]) < 1000) { x[d].aggTime = agg_times[i]; } else if ( x[d].startDate > agg_times[i] && x[d].endDate < agg_times[i + 1] ) { x[d].aggTime = agg_times[i]; } } } return x.filter((d) => d.aggTime != null); }30:Te05,getAggByInterval = function (start, end, data) { if (data.length == 0) { return []; } var agg_times = new Array(); var tmp = start; while (tmp <= end) { agg_times.push(tmp); tmp = tmp.add(15, "minutes"); } const all_times = [ ...new Set( Array.prototype.concat( data.map((d) => d.startDate), data.map((d) => d.endDate.add(1, "second")), agg_times ) ) ].sort(); var x = new Array(); var first_match = 0; for (var d in data) { var found_first_match = false; // console.log(data[d], first_match, found_first_match); for (var t = first_match; t < all_times.length; t++) { // console.log( // t, // first_match, // all_times[t].format("YYYY-MM-DDTHH:mm"), // data[d].startDate.format("YYYY-MM-DDTHH:mm"), // data[d].endDate.format("YYYY-MM-DDTHH:mm") // ); if (data[d].startDate < all_times[t] && all_times[t] <= data[d].endDate) { // console.log("found a time"); if (!found_first_match) { // console.log("found first match at", t); first_match = t; found_first_match = true; } var startDate = data[d].startDate; var endDate = all_times[t]; if (t > 0) { if (data[d].startDate < all_times[t - 1]) { var startDate = all_times[t - 1]; } } var endDate = all_times[t].subtract(1, "second"); var endDate = all_times[t].subtract(1, "second"); x.push({ ...data[d], startDate: startDate, endDate: endDate, value: (((endDate - startDate) / 1000 + 1) / ((data[d].endDate - data[d].startDate) / 1000 + 1)) * data[d].value }); } else if ( data[d].startDate < all_times[t - 1] && all_times[t - 1] <= data[d].endDate ) { // console.log("catching hanging times"); var startDate = all_times[t - 1]; var endDate = data[d].endDate; x.push({ ...data[d], startDate: startDate, endDate: endDate, value: (((endDate - startDate) / 1000 + 1) / ((data[d].endDate - data[d].startDate) / 1000 + 1)) * data[d].value }); } else if (all_times[t] > data[d].endDate) { // console.log("breaking"); break; } } } // add the aggTime to each row we just created for (var d in x) { for (var i = 0; i < agg_times.length - 1; i++) { if (Math.abs(x[d].startDate - agg_times[i]) < 1000) { x[d].aggTime = agg_times[i]; } else if ( x[d].startDate > agg_times[i] && x[d].endDate < agg_times[i + 1] ) { x[d].aggTime = agg_times[i]; } } } const y = x.filter((d) => d.aggTime != null); // to compute the rank by hand, first sort like; // grouperArray.sort((a, b) => a.gsize == b.gsize ? a.glow - b.glow : a.gsize - b.gsize // then iterate over, if prev value != current value, set 1 and increment from there // to roll up, loop over and create a fresh data structure // keep rolling sum (and count) adding up, if prev value != current value then append to that // new data structure and reset rolling values const dt = aq.from(y); const obj = dt .groupby("startDate") .orderby("source") .filter((d) => op.rank() === 1) .groupby("aggTime") .rollup({ value: (d) => op.sum(d.value), count: (d) => op.count() }) .orderby("aggTime") .objects(); return obj; }31:[] 2d:{"id":"e377ffff32939316","slug":"split-overlapping-time-objects","title":"Split overlapping time objects","thumbnail":"4acd196ce14eb2160fc2695dd8f6031f3ef455f19ebf88f137913333acf61558","access_level":"public","create_time":"$D2023-03-10T19:56:42.698Z","update_time":"$D2024-04-12T14:26:17.490Z","view_time":null,"first_public_time":"$D2023-04-14T20:10:32.582Z","has_unpublished_edits":null,"fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"a6830e4ed4c8be42","login":"andyreagan","name":"Andy Reagan","avatar_url":"https://avatars-next.observableusercontent.com/ae4a0dadc2cad62edce830181acef0d692999eda9eeaa1d7b64740c51a058cb5","tier":"starter_2024"},"version":319,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"ca9d2332808d4e71","login":"andyreagan","name":"Andy Reagan","avatar_url":"https://avatars-next.observableusercontent.com/ae4a0dadc2cad62edce830181acef0d692999eda9eeaa1d7b64740c51a058cb5","tier":null}],"members":[{"type":"individual","id":"ca9d2332808d4e71","login":"andyreagan","name":"Andy Reagan","avatar_url":"https://avatars-next.observableusercontent.com/ae4a0dadc2cad62edce830181acef0d692999eda9eeaa1d7b64740c51a058cb5","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":{"id":"58c39b2d43063879","slug":"js-utilities","title":"JS Utilities","owner":{"id":"a6830e4ed4c8be42","login":"andyreagan","name":"Andy Reagan"}},"type":"notebook","body":{"title":"Split overlapping time objects","cells":[{"id":0,"value":"# Split overlapping time objects\n\nTo use:\n\n```js\nimport { getAggByInterval } from \"@andyreagan/split-overlapping-time-objects\"\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":10,"value":"data = [\n {\n source: 1,\n startDate: dayjs(\"2023-03-10 14:58:00\"),\n endDate: dayjs(\"2023-03-10 15:17:00\"),\n value: 100,\n note: \"overlaps start and end of time window\"\n },\n {\n source: 1,\n startDate: dayjs(\"2023-03-10 15:45:00\"),\n endDate: dayjs(\"2023-03-10 15:55:00\"),\n value: 50,\n note: \"a within a single window\"\n },\n {\n source: 2,\n startDate: dayjs(\"2023-03-10 15:50:00\"),\n endDate: dayjs(\"2023-03-10 16:00:00\"),\n value: 100,\n note: \"b overlaps a on the left side, overlaps on RHS\"\n }\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":20,"value":"start = dayjs(\"2023-03-10 15:00:00\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":22,"value":"end = dayjs(\"2023-03-10 16:00:00\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":116,"value":"Inputs.table(agg_times)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":222,"value":"agg_time = 15","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"agg_times = {\n var list = new Array();\n var tmp = start;\n while (tmp <= end) {\n list.push(tmp);\n tmp = tmp.add(agg_time, \"minutes\");\n }\n return list;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":48,"value":"Array.prototype\n .concat(\n data.map((d) => d.startDate),\n data.map((d) => d.endDate),\n agg_times\n )\n .filter(function (el, i, a) {\n return i === a.indexOf(el);\n })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":51,"value":"all_times[5] == all_times[6]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":119,"value":"(all_times[5] - all_times[4]) / 1000","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":304,"value":"Inputs.table(all_times)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":38,"value":"all_times = [\n ...new Set(\n Array.prototype.concat(\n data.map((d) => d.startDate),\n data.map((d) => d.endDate.add(1, \"second\")),\n agg_times\n )\n )\n].sort()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":72,"value":"Inputs.table(data)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":70,"value":"Inputs.table(split)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":44,"value":"$2f","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":64,"value":"Math.min(0, -1)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":175,"value":"split","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":135,"value":"{\n // filter to >= start time, <= end time (cut off extra segments\n // can just use the aggTime is NA\n // group by the start time\n // take the first value within ranking\n // group by the interval time\n // sum up values\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":167,"value":"dt = aq.from(split)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":169,"value":"dt\n .groupby(\"startDate\")\n .orderby(aq.desc(\"source\"))\n .filter((d) => op.rank() === 1)\n .view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":180,"value":"dt\n .groupby(\"startDate\")\n .orderby(aq.desc(\"source\"))\n .filter((d) => op.rank() === 1)\n .groupby(\"aggTime\")\n .rollup({\n value: (d) => op.sum(d.value),\n count: (d) => op.count()\n })\n .orderby(\"aggTime\")\n .view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":191,"value":"dt\n .groupby(\"startDate\")\n .orderby(aq.desc(\"source\"))\n .filter((d) => op.rank() === 1)\n .groupby(\"aggTime\")\n .rollup({\n value: (d) => op.sum(d.value),\n count: (d) => op.count()\n })\n .orderby(\"aggTime\")\n .objects()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":193,"value":"$30","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":202,"value":"getAggByInterval(start, end, data)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":164,"value":"import { aq, op } from \"@uwdata/arquero\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"import { dayjs } from \"@andyreagan/dayjs-loader\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W31","stdlib":"1","titling":"auto"}} 2e:"$2d:body"