Aggregate values from array of JSON objects based on timestamp

Question

I am relatively new to programming and javascript and i want to achieve the following. Let's assume that we have the following data:

let data =
  [
    {
      "time": 1663163352001,
      "state": "10"
    },
    {
      "time": 1663163352002,
      "state": "20"
    },
    {
      "time": 1663163354002,
      "state": "10"
    },
    {
      "time": 1663163354002,
      "state": "10"
    },
    {
      "time": 1663163355033,
      "state": "30"
    },
    {
      "time": 1663163355035,
      "state": "10"
    },
    {
      "time": 1663163397035,
      "state": "30"
    },
    {
      "time": 1663163397044,
      "state": "50"
    }

  ]

i want to aggregate and calculate the averaged state based on timestamps on time interval (let's say 1 second) so the output should be

let data =
  [
    {
      "time": 1663163352000,
      "state": "15"
    },
    {
      "time": 1663163353000,
      "state": "0"
    },
    {
      "time": 1663163354000,
      "state": "10"
    },
    {
      "time": 1663163355000,
      "state": "20"
    },
    {
      "time": 1663163356000,
      "state": "0"
    },
    {
      "time": 1663163397000,
      "state": "40"
    }

  ]

I know my code is not right but at this is the logic that i have already implement.

//Inputs
var t_interval = 1000;


let aggregatedValues = [];
let count = 0;
let sum = 0;
//starting point converted in seconds
t0 = data[0].time - data[0].time % 1000;
//the timewindow
t_win = t0 + t_interval;

//Just for this case 
for (let i = 0; i < 7; i++) {

  if ( data[i].time < t_win) {
    count += 1;
    sum += parseFloat(data[i].state);
  } 
  else{
    if (sum === 0 || count === 0) {
      avg = NaN;
    }
    avg = sum / count;
    count = 1;
    sum = parseFloat(data[i].state);

    let temp = {
      "time": t_win - t_interval,
      "state": avg
    }
    aggregatedValues.push(temp);
    t_win = t_win + t_interval;
  }
  
}

Could you help me please? Thank you!

Sergey Sosunov · Answer

Pretty simple task, reduce over your collection to create an object with key = timestamp and value = object of total amount of states and count of met items. Apply some rounding operation on timestamp. Then 1 more loop to calculate average values. Convert it back to array and sort (if needed). Gaps filling is up to you.

const data = [
  { time: 1663163352001, state: "10" },
  { time: 1663163352002, state: "20" },
  { time: 1663163354002, state: "10" },
  { time: 1663163354002, state: "10" },
  { time: 1663163355033, state: "30" },
  { time: 1663163355035, state: "10" },
  { time: 1663163397035, state: "30" },
  { time: 1663163397044, state: "50" }
];

function aggregate(items) {
  const step = 1000 * 1; // 1 second

  const resObj = items.reduce((acc, item) => {
    const roundedTimestamp = Math.round(item.time / step) * step;
    const parsedValue = parseInt(item.state, 10);

    const existing = acc[roundedTimestamp];
    if (!existing) {
      acc[roundedTimestamp] = {
        count: 1,
        value: parsedValue
      };
    } else {
      existing.count += 1;
      existing.value += parsedValue;
    }

    return acc;
  }, {});

  const res = Object.entries(resObj)
    .map(([key, value]) => ({
      time: parseInt(key, 10),
      value: (value.value / value.count).toString()
    }))
    .sort((a, b) => a.time - b.time);

  // fill gaps if you really need that.
  //

  return res;
}

console.log(aggregate(data));

Scott Sauyet · Answer

Here I make the assumption mentioned in my comment that the last value in your input was a typo. If not, I don't understand something fundamental.

By breaking out reusable functions to group elements by the result of a function and to create an integer range, we can turn this into a reasonably simple function:

const groupBy = (fn, k) => (xs) => xs .reduce (
  (a, x) => ((k = fn (x)), (a [k] = a[k] || []), (a[k] .push (x)), a), {}
)

const range = (lo, hi) => Array .from ({length: hi - lo + 1}, (_, i) => i + lo)

const group = (interval) => (
  data,
  groups = groupBy (({time}) => Math .floor (time / interval)) (data),  // or Math.round
  keys = Object .keys (groups) .map (Number),
  min = Math .min (...keys), max = Math .max (...keys),
) => range (min, max) 
  .map (k => [k, groups [k] || []]) 
  .map (([k, v]) => [
     interval * k, 
     v .reduce ((t, g) => t + Number(g.state), 0) / Math .max (v .length, 1)
  ])
  .map (([time, state]) => ({time, state}))


const data = [{time: 1663163352001, state: "10"}, {time: 1663163352002, state: "20"}, {time: 1663163354002, state: "10"}, {time: 1663163354002, state: "10"}, {time: 1663163355033, state: "30"}, {time: 1663163355035, state: "10"}, {time: 1663163357044, state: "50"}]

console .log (group (1000) (data))
console .log (group (2000) (data))

.as-console-wrapper {max-height: 100% !important; top: 0}

We use groupBy to turn the input into a structure like this:

{
    "1663163352": [{time: 1663163352001, state: "10"}, {time: 1663163352002, state: "20"}],
    "1663163354": [{time: 1663163354002, state: "10"}, {time: 1663163354002, state: "10"}],
    "1663163355": [{time: 1663163355033, state: "30"}, {time: 1663163355035, state: "10"}],
    "1663163357": [{time: 1663163357044, state: "50"}]
}

then grab the keys of this object, turn them into numbers, find the minimum and maximum values, create the integer range between them, and map them back into entry-style objects, using the lists of matching values to fill them, defaulting to an empty list.

We then convert the keys back to full Date values and average the state values for each, and turn the resulting array of arrays back into an array of objects.

Aggregate values from array of JSON objects based on timestamp

2 Answers