Senior Data Scientist, MassMutual
Lecturer, UC Berkeley MIDS
--
PhD Applied Math, Computational Story Lab, UVM
MS Applied Math, UVM
BS Math, Virginia Tech
data = [
{
source: 1,
startDate: dayjs("2023-03-10 14:58:00"),
endDate: dayjs("2023-03-10 15:17:00"),
value: 100,
note: "overlaps start and end of time window"
},
{
source: 1,
startDate: dayjs("2023-03-10 15:45:00"),
endDate: dayjs("2023-03-10 15:55:00"),
value: 50,
note: "a within a single window"
},
{
source: 2,
startDate: dayjs("2023-03-10 15:50:00"),
endDate: dayjs("2023-03-10 16:00:00"),
value: 100,
note: "b overlaps a on the left side, overlaps on RHS"
}
]
start = dayjs("2023-03-10 15:00:00")
end = dayjs("2023-03-10 16:00:00")
Inputs.table(agg_times)
agg_time = 15
agg_times = {
var list = new Array();
var tmp = start;
while (tmp <= end) {
list.push(tmp);
tmp = tmp.add(agg_time, "minutes");
}
return list;
}
Array.prototype
.concat(
data.map((d) => d.startDate),
data.map((d) => d.endDate),
agg_times
)
.filter(function (el, i, a) {
return i === a.indexOf(el);
})
all_times[5] == all_times[6]
(all_times[5] - all_times[4]) / 1000
Inputs.table(all_times)
all_times = [
...new Set(
Array.prototype.concat(
data.map((d) => d.startDate),
data.map((d) => d.endDate.add(1, "second")),
agg_times
)
)
].sort()
Inputs.table(data)
Inputs.table(split)
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);
}
Math.min(0, -1)
split
{
// filter to >= start time, <= end time (cut off extra segments
// can just use the aggTime is NA
// group by the start time
// take the first value within ranking
// group by the interval time
// sum up values
}
dt = aq.from(split)
dt
.groupby("startDate")
.orderby(aq.desc("source"))
.filter((d) => op.rank() === 1)
.view()
dt
.groupby("startDate")
.orderby(aq.desc("source"))
.filter((d) => op.rank() === 1)
.groupby("aggTime")
.rollup({
value: (d) => op.sum(d.value),
count: (d) => op.count()
})
.orderby("aggTime")
.view()
dt
.groupby("startDate")
.orderby(aq.desc("source"))
.filter((d) => op.rank() === 1)
.groupby("aggTime")
.rollup({
value: (d) => op.sum(d.value),
count: (d) => op.count()
})
.orderby("aggTime")
.objects()
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;
}
getAggByInterval(start, end, data)
import { aq, op } from "@uwdata/arquero"
import { dayjs } from "@andyreagan/dayjs-loader"
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
