Group and aggregate array of objects by key names
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])
}
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; }