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"
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.