Group And Average Array Values Based On Index

I have a two-dimensional array that contains strings and integers. In GAS I want to create a new array that averages the integers of arrays that have common strings. I've looked f

Solution 1:

let array = [
  ["House1", 1.0, 2.0, 5.0, 1.0],
  ["House1", 1.0, 4.0, 2.0, 3.0],
  ["House2", 2.0, 3.0, 3.0, 4.0],
  ["House2", 5.0, 4.0, 3.0, 4.0],
  ["House2", 4.0, 5.0, 2.0, 2.0],
  ["House3", 2.0, 1.0, 4.0, 5.0]];

letaverageArrays = arrays =>
    arrays.reduce((sum, a) => {
      a.forEach((v, i) => sum[i] = (sum[i] || 0) + v / arrays.length);
      return sum;
    }, []);

let grouped = array.reduce((acc, a) => {
  acc[a[0]] = acc[a[0]] || [];
  acc[a[0]].push(a.slice(1));
  return acc;
}, {});

let averages = Object.entries(grouped).map(([name, arrays]) => [name, ...averageArrays(arrays)]);

console.log(averages);

Solution 2:

You could take a hash table for same groups and get the averages by storing the sums and the count.

The result is an array of averages for every group.

functionbuildAverages(array) {
    var hash = {},
        result = [],
        i, j, item, key;
        
    for (i = 0; i < array.length; i++) {
        item = array[i];
        key = item[0];
        if (!hash[key]) {
            hash[key] = { avg: array[i].slice(), sums: array[i].slice(), count: 1 };
            result.push(hash[key].avg);
            continue;
        }
        hash[key].count++;
        for (j = 1; j < item.length; j++) {
            hash[key].sums[j] += item[j];
            hash[key].avg[j] = (hash[key].sums[j] / hash[key].count);
        }
    }
    return result;
}

var array = [["House1", 1.0, 2.0, 5.0, 1.0], ["House1", 1.0, 4.0, 2.0, 3.0], ["House2", 2.0, 3.0, 3.0, 4.0], ["House2", 5.0, 4.0, 3.0, 4.0], ["House2", 4.0, 5.0, 2.0, 2.0], ["House3", 2.0, 1.0, 4.0, 5.0]]
  
console.log(buildAverages(array));

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

Solution 3:

The code below was tested in GAS environment.

functionmyFunction() {
  vararray = [
    ["House1", 1.0, 2.0, 5.0, 1.0], 
    ["House1", 1.0, 4.0, 2.0, 3.0], 
    ["House2", 2.0, 3.0, 3.0, 4.0], 
    ["House2", 5.0, 4.0, 3.0, 4.0],
    ["House2", 4.0, 5.0, 2.0, 2.0], 
    ["House3", 2.0, 1.0, 4.0, 5.0]
  ];

  var avg = {}, count = {};
  array.forEach(function(a) {
    var key = a.shift();
    if (avg[key]) {
      for (var i = 0; i < 4; i++) { 
        avg[key][i] += a[i];
      }
      count[key]++;
    } else {
      avg[key] = a;
      count[key] = 1;
    }
  });

  var result = [];
  for (var key in avg) {
    for (var i = 0; i < 4; i++) { 
      avg[key][i] /= count[key];
    }
    avg[key].unshift(key);
    result.push(avg[key]);
  }

  Logger.log(result);
}

May be, it is long, but each step is clear. We accumulate intermediate results in avg and count objects, which are converted to the result array as desired.

Solution 4:

Create a grouped object of arrays for each house. Take each group object and Sum them and then average them individually for each object.

functiongroupByAndAvg(array) {
  var out = {};
  array.forEach(function(row) {
    var house = row[0];
    out[house] = out[house] || [];
    out[house].push(row);
  });
  returnObject.keys(out).map(function(thisHouse) {
    var house = out[thisHouse];
    var l = house.length;
    var sumHouse = house.reduce(function(acc, row) {
      return acc.map(function(e, i) {
        returntypeof e === 'number' ? e + row[i] : e;
      });
    }, house.pop());
    return sumHouse.map(function(e) {
      returntypeof e === 'number' ? e / l : e;
    });
  });
}
var array = [
  ['House1', 1.0, 2.0, 5.0, 1.0],
  ['House1', 1.0, 4.0, 2.0, 3.0],
  ['House2', 2.0, 3.0, 3.0, 4.0],
  ['House2', 5.0, 4.0, 3.0, 4.0],
  ['House2', 4.0, 5.0, 2.0, 2.0],
  ['House3', 2.0, 1.0, 4.0, 5.0],
];
console.info(groupByAndAvg(array));