Group and aggregate array of objects by key names

Question

I would like to write a function in JS that takes lists of names as arguments and is able to group by and aggregate by the specified column names. For example, my data might look like:

const SALES = [
  { lead: 'Mgr 1', revenue: 49.99, repName: 'Rep 1', forecast: 81.00 },
  { lead: 'Mgr 1', revenue: 9.99, repName: 'Rep 1', forecast: 91.00 },
  { lead: 'Mgr 1', revenue: 9.99, repName: 'Rep 13', forecast: 82.00 },
  { lead: 'Mgr 2', revenue: 99.99, repName: 'Rep 3', forecast: 101.00 },
  { lead: 'Mgr 2', revenue: 9.99, repName: 'Rep 5', forecast: 89.00 },
  { lead: 'Mgr 3', revenue: 199.99, repName: 'Rep 6', forecast: 77.00 }
];

I can group and aggregate this data like this:

let grouped = {};
SALES.forEach(({lead, repName, revenue}) => {
  grouped[[lead, repName]] = grouped[[lead, repName]] || { lead, repName, revenue: 0 };
  grouped[[lead, repName]].revenue = +grouped[[lead, repName]].revenue + (+revenue);
});
grouped = Object.values(grouped);

console.warn('Look at this:\n', grouped);

However, I would like this to be more dynamic so that I don't have to write an if-else statement for all the possible combinations of groupings and aggregations. The following code shows something that I would like to get working, but currently does not.

function groupByTotal(arr, groupByCols, aggregateCols) {
  let grouped = {};
  arr.forEach(({ groupByCols, aggregateCols }) => {
    grouped[groupByCols] = grouped[groupByCols] || { groupByCols, aggregateCols: 0 };
    grouped[groupByCols].aggregateCols = +grouped[groupByCols].aggregateCols + (+aggregateCols);
  });
  grouped = Object.values(grouped);
  return grouped;
}

groupByTotal(SALES,['lead','repName'],'revenue')

Expected output might look like this:

[
  { lead: "Mgr 1", repName: "Rep 1", revenue: 59.98 },
  { lead: "Mgr 1", repName: "Rep 13", revenue: 9.99 },
  { lead: "Mgr 2", repName: "Rep 3", revenue: 99.99 },
  { lead: "Mgr 2", repName: "Rep 5", revenue: 9.99 },
  { lead: "Mgr 3", repName: "Rep 6", revenue: 199.99 }
]

Ideally, I would like to be able to pass in any number of column names to group by or to be aggregated. Any help would be greatly appreciated.

Answer 1

Currently you are creating a key based on the stringified value of [lead, repName]. You could get this dynamically based on groupByCols

// gets the values for "groupByCols" seperated by `|` to create a unique key
const values = groupByCols.map(k => o[k]).join("|");

You'd also need to get a subset of the object based on groupByCols

const subSet = (o, keys) => keys.reduce((r, k) => (r[k] = o[k], r), {})

// OR if fromEntries() is supported
const subSet = (o, keys) => Object.fromEntries(keys.map(k => [k, o[k]))

Rest of the logic would be similar to what you're already doing. Use the unique in grouped. Get the subset of the object and add/update aggregateCols key based on whether the key already exists or not

const SALES = [
  { lead: 'Mgr 1', revenue: 49.99, repName: 'Rep 1', forecast: 81.00 },
  { lead: 'Mgr 1', revenue: 9.99, repName: 'Rep 1', forecast: 91.00 },
  { lead: 'Mgr 1', revenue: 9.99, repName: 'Rep 13', forecast: 82.00 },
  { lead: 'Mgr 2', revenue: 99.99, repName: 'Rep 3', forecast: 101.00 },
  { lead: 'Mgr 2', revenue: 9.99, repName: 'Rep 5', forecast: 89.00 },
  { lead: 'Mgr 3', revenue: 199.99, repName: 'Rep 6', forecast: 77.00 }
];

const subSet = (o, keys) => keys.reduce((r, k) => (r[k] = o[k], r), {})

function groupByTotal(arr, groupByCols, aggregateCols) {
  let grouped = {};

  arr.forEach(o => {
    const values = groupByCols.map(k => o[k]).join("|");
    if (grouped[values])
      grouped[values][aggregateCols] += o[aggregateCols]
    else
      grouped[values] = { ...subSet(o, groupByCols), [aggregateCols]: o[aggregateCols] }
  })

  return Object.values(grouped);
}

console.log("Sum revenue based on lead and repName")
console.log(groupByTotal(SALES, ['lead', 'repName'], 'revenue'))

console.log("Sum forecast based on lead: ")
console.log(groupByTotal(SALES, ['lead'], 'forecast'))

If you want to pass a array of columns to sum, you can loop through aggregateCols and sum each property in grouped:

if (grouped[values]) {
    aggregateCols.forEach(col => grouped[values][col] += o[col])
    grouped[values].Count++
} else {
    grouped[values] = subSet(o, groupByCols);
    grouped[values].Count = 1
    aggregateCols.forEach(col => grouped[values][col] = o[col])
}

Answer 2

You can implement a similar algorithm using pure JavaScript.

Just know how to create your key and aggregate the data as in the following scenario.

const SALES = [
  { lead: 'Mgr 1', revenue:  49.99, repName: 'Rep 1',  forecast: 81.00 },
  { lead: 'Mgr 1', revenue:   9.99, repName: 'Rep 1',  forecast: 91.00 },
  { lead: 'Mgr 1', revenue:   9.99, repName: 'Rep 13', forecast: 82.00 },
  { lead: 'Mgr 2', revenue:  99.99, repName: 'Rep 3',  forecast: 101.00 },
  { lead: 'Mgr 2', revenue:   9.99, repName: 'Rep 5',  forecast: 89.00 },
  { lead: 'Mgr 3', revenue: 199.99, repName: 'Rep 6',  forecast: 77.00 }
];

console.log(aggregate(SALES, ['lead', 'repName'], 'revenue'));

function aggregate(data, keyFields, accumulator) {
  var createNewObj = (ref, fields) => {
    return fields.reduce((result, key) => {
      return Object.assign(result, { [key] : ref[key] });
    }, {});
  }
  return Object.values(data.reduce((result, object, index, ref) => {
    let key = keyFields.map(key => object[key]).join('');
    let val = result[key] || createNewObj(object, keyFields);
    val[accumulator] = (val[accumulator] || 0) + object[accumulator];
    return Object.assign(result, { [key] : val });
  }, {}));
}

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

Result

[
  {
    "lead": "Mgr 1",
    "repName": "Rep 1",
    "revenue": 59.98
  },
  {
    "lead": "Mgr 1",
    "repName": "Rep 13",
    "revenue": 9.99
  },
  {
    "lead": "Mgr 2",
    "repName": "Rep 3",
    "revenue": 99.99
  },
  {
    "lead": "Mgr 2",
    "repName": "Rep 5",
    "revenue": 9.99
  },
  {
    "lead": "Mgr 3",
    "repName": "Rep 6",
    "revenue": 199.99
  }
]

---

Alternative with custom accumulator function

The following example uses an accumulator object that contains a reference field and a function that applies a mathematical expression.

{
  key: 'revenue',
  fn : (total, value) => total + value
}

const SALES = [
  { lead: 'Mgr 1', revenue:  49.99, repName: 'Rep 1',  forecast: 81.00 },
  { lead: 'Mgr 1', revenue:   9.99, repName: 'Rep 1',  forecast: 91.00 },
  { lead: 'Mgr 1', revenue:   9.99, repName: 'Rep 13', forecast: 82.00 },
  { lead: 'Mgr 2', revenue:  99.99, repName: 'Rep 3',  forecast: 101.00 },
  { lead: 'Mgr 2', revenue:   9.99, repName: 'Rep 5',  forecast: 89.00 },
  { lead: 'Mgr 3', revenue: 199.99, repName: 'Rep 6',  forecast: 77.00 }
];

console.log(aggregate(SALES, ['lead', 'repName'], {
  key: 'revenue',
  fn : (total, value) => total + value
}));

function aggregate(data, keyFields, accumulator) {
  var createNewObj = (ref, fields) => {
    return fields.reduce((result, key) => {
      return Object.assign(result, { [key] : ref[key] });
    }, {});
  }
  return Object.values(data.reduce((result, object, index, ref) => {
    let key = keyFields.map(key => object[key]).join('');
    let val = result[key] || createNewObj(object, keyFields);
    val[accumulator.key] = accumulator.fn(val[accumulator.key] || 0, object[accumulator.key]);
    return Object.assign(result, { [key] : val });
  }, {}));
}

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

Another functional approach

If you want to accumulate multiple fields, you will need to drop the field to reference and just modify the entire object, but this is typically more dangerous.

const SALES = [
  { lead: 'Mgr 1', revenue:  49.99, repName: 'Rep 1',  forecast: 81.00 },
  { lead: 'Mgr 1', revenue:   9.99, repName: 'Rep 1',  forecast: 91.00 },
  { lead: 'Mgr 1', revenue:   9.99, repName: 'Rep 13', forecast: 82.00 },
  { lead: 'Mgr 2', revenue:  99.99, repName: 'Rep 3',  forecast: 101.00 },
  { lead: 'Mgr 2', revenue:   9.99, repName: 'Rep 5',  forecast: 89.00 },
  { lead: 'Mgr 3', revenue: 199.99, repName: 'Rep 6',  forecast: 77.00 }
];

console.log(aggregate(SALES, ['lead', 'repName'], (prev, curr) => {
  return Object.assign(prev, {
    revenueTotal : (prev['revenueTotal'] || 0) + curr['revenue'],
    forecastMax : Math.max((prev['forecastMax'] || -Number.MAX_VALUE), curr['forecast']),
    forecastMin : Math.min((prev['forecastMin'] || +Number.MAX_VALUE), curr['forecast'])
  });
}));

function aggregate(data, keyFields, accumulatorFn) {
  var createNewObj = (ref, fields) => {
    return fields.reduce((result, key) => {
      return Object.assign(result, { [key] : ref[key] });
    }, {});
  }
  return Object.values(data.reduce((result, object, index, ref) => {
    let key = keyFields.map(key => object[key]).join('');
    let val = result[key] || createNewObj(object, keyFields);
    return Object.assign(result, { [key] : accumulatorFn(val, object) });
  }, {}));
}

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