I've been going around and around on this problem to produce 2 arrays for a chart.js data source.

let data = [
    { date: '7/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 100 },
    { date: '7/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 200 },
    { date: '7/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 10 },
    { date: '7/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 110 },
    { date: '8/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 120 },
    { date: '8/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 30 },
    { date: '8/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 60 },
    { date: '8/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 70 }
];

Given this input data and told to group by a variable number of columns, e.g. in my examples name and tenant, but could just be on 1, say date, produce label keys and then a sum of count for the dataset data of the graph.

Target Labels output:

let targetLabels = [
    ['7/6/2016', 'BiggeCo'],
    ['7/6/2016', 'SmallCo'],
    ['8/6/2016', 'BiggeCo'],
    ['8/6/2016', 'SmallCo']
];

Target DataSets output:

let targetDataSets = [
    {
        label: 'Count',
        data: [300, 120, 150, 130]
    }
];

So far I've got the follow but it seems clunky and I'm not sure how to then get the sum of the count column and allow variable numbers of columns easily.

let temp = _.transform(data, function(result, value, key) {
    if (!_.some(result, function (r) { return r[0] == value['date'] && r[1] == value['tenant']; })) {
        result.push([ value['date'], value['tenant'] ]);
    }
}, []);

In SQL I'd just write

SELECT name, tenant, sum(count) FROM table GROUP BY name, tenant

Can anyone offer any guidance?

This looks close to what I want to do but I want to do it over multiple properties

How can I reduce array with non-unique elements by summing their second value with lodash/underscore?

I've been going around and around on this problem to produce 2 arrays for a chart.js data source.

let data = [
    { date: '7/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 100 },
    { date: '7/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 200 },
    { date: '7/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 10 },
    { date: '7/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 110 },
    { date: '8/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 120 },
    { date: '8/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 30 },
    { date: '8/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 60 },
    { date: '8/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 70 }
];

Given this input data and told to group by a variable number of columns, e.g. in my examples name and tenant, but could just be on 1, say date, produce label keys and then a sum of count for the dataset data of the graph.

Target Labels output:

let targetLabels = [
    ['7/6/2016', 'BiggeCo'],
    ['7/6/2016', 'SmallCo'],
    ['8/6/2016', 'BiggeCo'],
    ['8/6/2016', 'SmallCo']
];

Target DataSets output:

let targetDataSets = [
    {
        label: 'Count',
        data: [300, 120, 150, 130]
    }
];

So far I've got the follow but it seems clunky and I'm not sure how to then get the sum of the count column and allow variable numbers of columns easily.

let temp = _.transform(data, function(result, value, key) {
    if (!_.some(result, function (r) { return r[0] == value['date'] && r[1] == value['tenant']; })) {
        result.push([ value['date'], value['tenant'] ]);
    }
}, []);

In SQL I'd just write

SELECT name, tenant, sum(count) FROM table GROUP BY name, tenant

Can anyone offer any guidance?

This looks close to what I want to do but I want to do it over multiple properties

How can I reduce array with non-unique elements by summing their second value with lodash/underscore?

• javascript
• underscore.js
• lodash

• Can you explain data part? For BiggeCo its 200 which is Max of 100, 200 but for SmallCo its 120 which is sum of 10 and 110 – Rajesh Commented Jul 15, 2016 at 7:16
• @Rajesh typo that should have been 100+200 for 7/6/2016 for biggeco – Phil Commented Jul 15, 2016 at 7:17
• you can do it using linqjs – miechooy Commented Jul 15, 2016 at 7:19
• And will the data be always sorted? – Rajesh Commented Jul 15, 2016 at 7:22
• @Rajesh cannot guarantee – Phil Commented Jul 15, 2016 at 7:26

5 Answers 5

Here's a lodash solution:

let fields = ['date', 'tenant']; // fields for ordering and picking
let groupFn = v => [v.date, v.tenant]; // grouping date and tenant
let sumBy = v => _.sumBy(v, 'count'); // get sum by `count`
let countBy = group => _(group[0]) 
  .pick(fields) // pick `date` and `tenant` from the first item
  .assign({ count: _.sumBy(group, 'count') }) // assign the total `count`
  .value();

let source = _(data)
  .orderBy(fields) // order data by `date` and `tenant`
  .groupBy(groupFn) // group items by `date` and `tenant`
  .map(countBy); // get count of each group

// get `date` and `tenant` values only
let targetLabels = source.map(groupFn).value();

let targetDataSets = [
  {
    label: 'count',
    // get `count` values only
    data: source.map('count').value()
  }
];

// `date`, `tenant` and total `count` values only
let unified = source.value();

let data = [{
  date: '7/6/2016',
  tenant: 'BiggeCo',
  template: 'Meeting Room',
  count: 100
}, {
  date: '8/6/2016',
  tenant: 'SmallCo',
  template: 'All Hands MR',
  count: 70
}, {
  date: '7/6/2016',
  tenant: 'BiggeCo',
  template: 'All Hands MR',
  count: 200
}, {
  date: '7/6/2016',
  tenant: 'SmallCo',
  template: 'Meeting Room',
  count: 10
}, {
  date: '7/6/2016',
  tenant: 'SmallCo',
  template: 'All Hands MR',
  count: 110
}, {
  date: '8/6/2016',
  tenant: 'BiggeCo',
  template: 'Meeting Room',
  count: 120
}, {
  date: '8/6/2016',
  tenant: 'BiggeCo',
  template: 'All Hands MR',
  count: 30
}, {
  date: '8/6/2016',
  tenant: 'SmallCo',
  template: 'Meeting Room',
  count: 60
}];

let fields = ['date', 'tenant']; // fields for ordering and picking
let groupFn = v => [v.date, v.tenant]; // grouping date and tenant
let sumBy = v => _.sumBy(v, 'count'); // get sum by `count`
let countBy = group => _(group[0]) 
  .pick(fields) // pick `date` and `tenant` from the first item
  .assign({ count: _.sumBy(group, 'count') }) // assign the total `count`
  .value();

let source = _(data)
  .orderBy(fields) // order data by `date` and `tenant`
  .groupBy(groupFn) // group items by `date` and `tenant`
  .map(countBy); // get count of each group

// get `date` and `tenant` values only
let targetLabels = source.map(groupFn).value();

let targetDataSets = [
  {
    label: 'count',
    // get `count` values only
    data: source.map('count').value()
  }
];

// `date`, `tenant` and total `count` values only
let unified = source.value();

document.body.innerHTML = 
  '<strong>Data Sets</strong>' + 
  '<pre>' + JSON.stringify(targetDataSets, 0, 4) + '</pre><hr>' +
  '<strong>Labels</strong>' + 
  '<pre>' + JSON.stringify(targetLabels, 0, 4) + '</pre><hr>' +
  '<strong>Unified Value</strong>' + 
  '<pre>' + JSON.stringify(unified, 0, 4) + '</pre>';

<script src="https://cdn.jsdelivr/lodash/4.13.1/lodash.min.js"></script>

Assuming that data is always sorted, you can try something like this:

Logic

• Loop over data and check for next value. If same, then add their count and continue, else push in temp array.
• If last 2 elements are same, check the length and push in temp again.
• Now use this array to get necessary values.

Sample

var data=[{date:"7/6/2016",tenant:"BiggeCo",template:"Meeting Room",count:100},{date:"7/6/2016",tenant:"BiggeCo",template:"All Hands MR",count:200},{date:"7/6/2016",tenant:"SmallCo",template:"Meeting Room",count:10},{date:"7/6/2016",tenant:"SmallCo",template:"All Hands MR",count:110},{date:"8/6/2016",tenant:"BiggeCo",template:"Meeting Room",count:120},{date:"8/6/2016",tenant:"BiggeCo",template:"All Hands MR",count:30},{date:"8/6/2016",tenant:"SmallCo",template:"Meeting Room",count:60},{date:"8/6/2016",tenant:"SmallCo",template:"All Hands MR",count:70}];

var result = [];
data.reduce(function(c, n, i) {
  if (c) {
    if (c.date === n.date && c.tenant === n.tenant) {
      n.count += c.count;
      if (i === data.length - 1)
        result.push(n);
    } else {
      result.push(c);
    }
  }
  return n;
}, null)

var targetLabels = [];
var targetDataset = [{
  label: "count",
  data: []
}];
result.forEach(function(o) {
  targetLabels.push([o.date, o.tenant]);
  targetDataset[0].data.push(o.count);
})

console.log(targetLabels)
console.log(targetDataset)

For unsorted case, you can try sorting manually,

data.sort(function(a, b) {
  if (a.date === b.date) {
    return a.tenant > b.tenant ? 1 : a.tenant < b.tenant ? -1 : 0;
  } else {
    return a.date > b.date ? 1 : a.date < b.date ? -1 : 0;
  }
})

Sample Fiddle.

As long as I have understand it correctly, you want to group by date (I see no property name) and tennant.

var data = [{ date: '7/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 100 }, { date: '7/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 200 }, { date: '7/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 10 }, { date: '7/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 110 }, { date: '8/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 120 }, { date: '8/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 30 }, { date: '8/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 60 }, { date: '8/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 70 }],
    result = []

data.forEach(function (a) {
    var key = a.date + '|' + a.tenant;
    if (!this[key]) {
        this[key] = { date: a.date, tenant: a.tenant, count: 0 };
        result.push(this[key]);
    }
    this[key].count += a.count;
}, Object.create(null));

console.log(result);

.reduce should do it, i guess. You probably would have to make sure that the data is sorted.

let data = [
  { date: '7/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 100 },
  { date: '7/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 200 },
  { date: '7/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 10 },
  { date: '7/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 110 },
  { date: '8/6/2016', tenant: 'BiggeCo', template: 'Meeting Room', count: 120 },
  { date: '8/6/2016', tenant: 'BiggeCo', template: 'All Hands MR', count: 30 },
  { date: '8/6/2016', tenant: 'SmallCo', template: 'Meeting Room', count: 60 },
  { date: '8/6/2016', tenant: 'SmallCo', template: 'All Hands MR', count: 70 }
];

let targetDataSets  = [{
  label: 'Count',
  data: []
}];

let targetLabels  = [];

data.reduce( (p, c) => {
  let cnt = 0;
  if(p.hasOwnProperty('date')) {
    if(p.date === c.date && p.tenant === c.tenant) {
      cnt = p.count + c.count;
      targetLabels.push([c.date, c.tenant]);
      targetDataSets[0]['data'].push(cnt);
    }
  }
  return c;
},{})

console.log(targetLabels)
console.log(targetDataSets)

Inspired by the answers I sat down and learnt lodash groupBy, map and reduce properly.

My goals and why I asked the question as searching I couldn't find a solution that did the following:

1. As per the question, allows a user defined array of properties to group on
2. Allows user defined "sum" property, could expand this to more features like average etc in the future perhaps.
3. Already have lodash in my application, I did manage to get LINQ.js to do roughly the same but I wasn't a fan of adding another dependency when Lodash does it.

Solution:

function group (data, groupKeys, sumKey) {
    return _.chain(data)
            .groupBy(function (d) {
                let grouping = '';

                for (let groupKey of groupKeys) {
                    grouping += d[groupKey] + '';
                }

                return grouping;
            })
            .map(function (groupedRow) {
                let newRow = {};

                // Extract the grouped properties to the new row
                for (let groupKey of groupKeys) {
                    newRow[groupKey] = groupedRow[0][groupKey];
                }

                // Could use native reduce, browser support?
                // Aggregate the sumKey property to the new row
                newRow[sumKey] = _.reduce(groupedRow, function (sum, r) {
                    return sum + r[sumKey];
                }, 0);

                return newRow;
            })
            .value();
}


let groupKeys = ['date', 'tenant'];

let sumKey = 'count';

let temp4 = group(data, groupKeys, sumKey);

console.log('output', temp4);

console.log('labels', 
    _.map(temp4, function (row) {
        let newRow = {};

        for (let groupKey of groupKeys) {
            newRow[groupKey] = row[groupKey];
        }

        return newRow;
    })
);

console.log('dataSets', 
    _.map(temp4, sumKey)
);

And the output:

phil $ node index.js
output
[ { date: '7/6/2016', tenant: 'BiggeCo', count: 300 },
  { date: '7/6/2016', tenant: 'SmallCo', count: 120 },
  { date: '8/6/2016', tenant: 'BiggeCo', count: 150 },
  { date: '8/6/2016', tenant: 'SmallCo', count: 130 } ]

labels
[ { date: '7/6/2016', tenant: 'BiggeCo' },
  { date: '7/6/2016', tenant: 'SmallCo' },
  { date: '8/6/2016', tenant: 'BiggeCo' },
  { date: '8/6/2016', tenant: 'SmallCo' } ]

dataSets [ 300, 120, 150, 130 ]