Filling gaps in time-series data

Question

I have an API endpoint returning time-series data, where all records with the measure being 0 - gaps, are ignored. It looks like:

[
  {
     "EventsStaging.timestamp.hour":"2022-07-31T20:00:00.000",
     "EventsStaging.timestamp":"2022-07-31T20:00:00.000",
     "EventsStaging.userCount":"1"
  },
  {
     "EventsStaging.timestamp.hour":"2022-07-31T21:00:00.000",
     "EventsStaging.timestamp":"2022-07-31T21:00:00.000",
     "EventsStaging.userCount":"1"
  },
  {
     "EventsStaging.timestamp.hour":"2022-08-01T04:00:00.000",
     "EventsStaging.timestamp":"2022-08-01T04:00:00.000",
     "EventsStaging.userCount":"1"
  },
  {
     "EventsStaging.timestamp.hour":"2022-08-01T16:00:00.000",
     "EventsStaging.timestamp":"2022-08-01T16:00:00.000",
     "EventsStaging.userCount":"1"
  },
  ...
]

I want to trasform my data on the client it has records where the value is 0 so we need to add records into gaps as:

[
  {
     "EventsStaging.timestamp.hour":"2022-07-31T20:00:00.000",
     "EventsStaging.timestamp":"2022-07-31T20:00:00.000",
     "EventsStaging.userCount":"1"
  },
  {
     "EventsStaging.timestamp.hour":"2022-07-31T21:00:00.000",
     "EventsStaging.timestamp":"2022-07-31T21:00:00.000",
     "EventsStaging.userCount":"1"
  },
  {
     "EventsStaging.timestamp.hour":"2022-07-31T22:00:00.000",
     "EventsStaging.timestamp":"2022-07-31T22:00:00.000",
     "EventsStaging.userCount":"0"
  },
  {
     "EventsStaging.timestamp.hour":"2022-07-31T23:00:00.000",
     "EventsStaging.timestamp":"2022-07-31T23:00:00.000",
     "EventsStaging.userCount":"0"
  },
  {
     "EventsStaging.timestamp.hour":"2022-08-01T00:00:00.000",
     "EventsStaging.timestamp":"2022-08-01T00:00:00.000",
     "EventsStaging.userCount":"0"
  },
  ...
  {
     "EventsStaging.timestamp.hour":"2022-08-01T04:00:00.000",
     "EventsStaging.timestamp":"2022-08-01T04:00:00.000",
     "EventsStaging.userCount":"1"
  },
  ...
]

I wrote the below to fill the gaps:

const fillMissingEntries = (series, granularity) => {
  const step = granularity === 'hour' ? 60 * 60 * 1000 : 0;
  const key = Object.keys(series[0])[0];
  const countKey = Object.keys(series[0])[2];
  return series.reduce((acc, entry) => {
    const prev = acc[acc.length - 1] || {};
    for (
      let i = 1;
      new Date(entry[key]) <
      new Date(new Date(entry[key]).getTime() + step * i);
      i++
    ) {
      acc.push({
        [key]: new Date(new Date(entry[key]).getTime() + step * i),
        [countKey]: 0,
        ...prev
      });
    }
    return acc;
  }, []);
};

I want to call as const transformed = fillMissingEntries(data, 'hour');, and I understand that reduce should allow me to do this in a single pass, but something is wrong with my code.

user_11901089 · Answer

you have to change the for loop and add a couple pushes to acc. The code (you might need to change it a bit, but at least it works and hope you will understand, how):

const fillMissingEntries = (series, granularity) => {
  const step = granularity === 'hour' ? 60 * 60 * 1000 : 0;
  /* the date I keep getting is bound to the timezone, so let's offset the date manually. */
  const timezoneOffset = (new Date().getTimezoneOffset()/-60) * 60 * 60 * 1000;
  const [key, _, countKey] = Object.keys(series[0]); // some nice destructuring going on here.
  return series.reduce((acc, entry) => {
    const prev = acc[acc.length - 1];
    /* we won't have anything in acc on first iteration, so let's push the first entry we have. */
    if (!prev) {
      acc.push(entry);
      return acc;
    }

    /* the idea of the loop is to keep pushing dates if they are less than the next date entry we have. */
    for (
      let i = 1;
      new Date(prev[key]).getTime() + step * i < new Date(entry[key]).getTime();
      i++
    ) {
      const date = (new Date(new Date(prev[key]).getTime() + step * i + timezoneOffset)).toISOString().slice(0,-1);
      acc.push({
        [key]: date,
        [countKey]: "0",
        /* why would you like to push ...prev ? */
        // ...prev
      });
    }
    /* since the for loop has just filled missing entries, let's add an entry we have next. */
    acc.push(entry);
    return acc;
  }, []);
};

const res = fillMissingEntries(json, 'hour');
console.log({ res });

The result it produces:

[
{
  'EventsStaging.timestamp.hour': '2022-07-31T20:00:00.000',
  'EventsStaging.timestamp': '2022-07-31T20:00:00.000',
  'EventsStaging.userCount': '1'
},
{
  'EventsStaging.timestamp.hour': '2022-07-31T21:00:00.000',
  'EventsStaging.timestamp': '2022-07-31T21:00:00.000',
  'EventsStaging.userCount': '1'
},
{
  'EventsStaging.timestamp.hour': '2022-07-31T22:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-07-31T23:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T00:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T01:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T02:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T03:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T04:00:00.000',
  'EventsStaging.timestamp': '2022-08-01T04:00:00.000',
  'EventsStaging.userCount': '1'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T05:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T06:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T07:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T08:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T09:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T10:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T11:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T12:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T13:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T14:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T15:00:00.000',
  'EventsStaging.userCount': '0'
},
{
  'EventsStaging.timestamp.hour': '2022-08-01T16:00:00.000',
  'EventsStaging.timestamp': '2022-08-01T16:00:00.000',
  'EventsStaging.userCount': '1'
}
]

Filling gaps in time-series data

1 Answers