Andrej Andrej - 1 month ago 5
SQL Question

Impute missing values in JavaScript array

I have the following JavaScript code which uses sql.js library to communicate with remote SQL database.

var xhr = new XMLHttpRequest();
xhr.open('GET', 'https://dl.dropboxusercontent.com/u/540963/test.sqlite', true);
xhr.responseType = 'arraybuffer';
var output = [];

xhr.onload = function(e) {
var uInt8Array = new Uint8Array(this.response);
var db = new SQL.Database(uInt8Array);
var data = db.exec("SELECT year, weight FROM freq_data WHERE doi = 'D007266'");

$.each(data[0].values, function(key, value) {
output.push({'year': value[0], 'weight': value[1]});
});
};
xhr.send();


The output is stored in the
output
array. For example, for the given array the data is the following:

{ year=1996, weight=3}
{ year=1999, weight=2}
{ year=2004, weight=5}
{ year=2005, weight=3}
{ year=2006, weight=2}


There are some gaps in the data; for example there is no entry for years 1997, 1998, 2000-2003. I wonder how to impute the data to fill the missing years with 0 values to end up with:

{ year=1996, weight=3}
{ year=1997, weight=0}
{ year=1998, weight=0}
{ year=1999, weight=2}
...
{ year=2006, weight=2}


What is the most appropriate way to do that?

Answer

Assuming the data will be sorted by the year, you can use .reduce() like this:

var data = [ { year: 1996,  weight: 3},
    { year: 1999,  weight: 2},
    { year: 2004,  weight: 5},
    { year: 2005,  weight: 3},
    { year: 2006,  weight: 2} ];
    
var final = data.reduce(function(prev, curr, index, arr) {
  prev.push(curr);
  if (index < arr.length - 1) {
    var nextYearInData = arr[index + 1].year;
    var yearToAdd = curr.year + 1;
    while (yearToAdd < nextYearInData) {
      prev.push({year: yearToAdd, weight: 0});
      yearToAdd++;
    }
  }
  return prev;
}, []);

console.log(final)