Grouping and adding JSON Object for multiple keys

Question

I have this JSON data which has structure similar to

data =[{"name":"A","Date":"10/19/2015","OnTime":"0.1","Purpose":"x","OffTime":"0"},
       {"name":"A","Date":"10/19/2015","OnTime":"0.3","Purpose":"x","OffTime":"0"},
       {"name":"B","Date":"10/19/2015","OnTime":"0.01","Purpose":"y","OffTime":"0"},
       {"name":"C","Date":"10/19/2015","OnTime":"0.02","Purpose":"z","OffTime":"0"},
       {"name":"C","Date":"10/19/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"}
       {"name":"A","Date":"10/20/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"}]

I am trying to group and add the OnTime value for name and Date. For example the total Ontime value for A on date 10/19/2015 Expected O/P :

dataout = [{"name":"A","Date":"10/19/2015","OnTime":"0.4"},
           {"name":"A","Date":"10/20/2015","OnTime":"0.01"},
           {"name":"B","Date":"10/19/2015","OnTime":"0.01"},
           {"name":"C","Date":"10/19/2015","OnTime":"0.03"}]

I tried using searching for the solution but found the solution for single Key addition but not for multiple keys. I also tried using underscore js but was unable to get the expected output. Please help, thanks.

If this data is retrieved from a server, say using SQL, then it'd be far far easier to do the grouping there. Javascript is not really designed for that sort of thing. It can be done but it's a right faff. — NickJ, Oct 19 '15 at 15:00
Thanks for your help but sadly i am stuck with javascript here. I am trying for other options, was hoping this could be done in javascript. — hitech0101, Oct 19 '15 at 15:06
there's a typo, the very 2 last items should be separated by comma — zobidafly, Nov 24 '17 at 18:34

score 2 · Accepted Answer · edited May 23 '17 at 11:45

Ok, I have something that works for you.

See Fiddle here:

http://jsfiddle.net/chrislewispac/nw0bberb/1

//make new sorted array for future map loop which goes in order
//using underscore.js you can chain methods. The sortBy method
//allows chaining and by multiple attributes. First sort by name
//then sort by date and return the value to new array 'sorted'

var sorted = _.chain(data)
  .sortBy(function(d) {return d.name})
  .sortBy(function(d) { return d.Date })
  .value()

//declare vars for loop
var prevObj = {};
var indexToChange = 0;
var reformattedArray = [];

//map loop over newly sorted array. I use map here which creates a new 
//array with the results of calling a provided function on every 
//element in this array.

var reformattedArray = sorted.map(function (obj, index) {

    //if the previous objects date is the same as the current objects 
    //date then take index of array which contains previous object
    //which had same name and date properties and combine the OnTime
    //values after converting them to float values using parseFloat()

    if (prevObj.Date == obj.Date && prevObj.name == obj.name) {
        reformattedArray[indexToChange].OnTime = parseFloat(prevObj.OnTime) + parseFloat(obj.OnTime);
    } 

    //if they don't match then add current object to the end of 
    //the new array

    else {
        reformattedArray.push(obj);
    }

    //at the end of the loop assign the current object to value prevObj
    //to be used in the next iteration
    prevObj = obj;

    //get current length of array (which is being built in our loop)
    //and subtract one to get index of previous object
    indexToChange = reformattedArray.length - 1;

    //return the newly reformatted array

    return reformattedArray;

});

console.log(reformattedArray[0]);

The output is:

[ 
   {"name":"A","Date":"10/19/2015","OnTime":0.4,"Purpose":"x","OffTime":"0"},
{"name":"B","Date":"10/19/2015","OnTime":"0.01","Purpose":"y","OffTime":"0"},
{"name":"C","Date":"10/19/2015","OnTime":0.03,"Purpose":"z","OffTime":"0"},
{"name":"A","Date":"10/20/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"}
]

Refs:

Underscore: sortBy() based on multiple attributes

http://underscorejs.org/

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/parseFloat

"if it works" combined with no explanation of code, what is this code doing? -1 — Stephan Bijzitter, Oct 19 '15 at 18:26
@StephanBijzitter you're joking right? It is commented and there is a fiddle showing that it works — Chris L, Oct 19 '15 at 18:27
@StephanBijzitter Aside from providing a working fiddle, and commented code, what else would you like me to do buddy?? — Chris L, Oct 19 '15 at 18:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/92763/discussion-between-chris-l-and-stephan-bijzitter). — Chris L, Oct 19 '15 at 18:49

score 0 · Answer 2 · answered Nov 24 '17 at 18:49

Assuming there's no typo, OnTime has to be a number, not a string as defined in the example, we can do it like this:

Group by the desired properties e.g. "Date" & ""name"
Results given in step 1 returns an array where each item is an array of at least 1 item => map this result and reduce the values to sum up.

Doing Step 1 with a groupBy instead of a sort avoids a sequence rupture inside your code, and allows you to call the reduce.

var data = [{"name":"A","Date":"10/19/2015","OnTime":"0.1","Purpose":"x","OffTime":"0"},
   {"name":"A","Date":"10/19/2015","OnTime":"0.3","Purpose":"x","OffTime":"0"},
   {"name":"B","Date":"10/19/2015","OnTime":"0.01","Purpose":"y","OffTime":"0"},
   {"name":"C","Date":"10/19/2015","OnTime":"0.02","Purpose":"z","OffTime":"0"},
   {"name":"C","Date":"10/19/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"},
   {"name":"A","Date":"10/20/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"}];
console.log(_(data).chain()
  .groupBy(function(itm){return itm.Date  + itm.name;}) // group by date & name
  .map(function(itm,key){ // for every grouped item
    // we sum up the values OnTime
    var sum=_(itm).chain()
              .pluck("OnTime")
              .reduce(function(memo, value){
                  return memo =memo+ Number(value);
               },0.0)
               .value();
     return _.object(["name","Date", "OnTime"],[itm[0].name,itm[0].Date,sum])})
  .value()
  );

<script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.8.3/underscore-min.js"></script>

Is it elegant enough? I hope so!

score 0 · Answer 3 · edited May 25 '20 at 15:11

const data = [{"name":"A","Date":"10/19/2015","OnTime":"0.1","Purpose":"x","OffTime":"0"},
   {"name":"A","Date":"10/19/2015","OnTime":"0.3","Purpose":"x","OffTime":"0"},
   {"name":"B","Date":"10/19/2015","OnTime":"0.01","Purpose":"y","OffTime":"0"},
   {"name":"C","Date":"10/19/2015","OnTime":"0.02","Purpose":"z","OffTime":"0"},
   {"name":"C","Date":"10/19/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"},
   {"name":"A","Date":"10/20/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"}];

var resultArr = [];
for (let fetchData of data) {
  var indx = data.indexOf(fetchData);
  for (var i = indx + 1; i < data.length; i++) {
    if (fetchData.name == data[i].name && fetchData.Date == data[i].Date) {
      let onTimeSum = parseFloat(fetchData.OnTime) +
        parseFloat(data[i].OnTime);
      fetchData.OnTime = JSON.stringify(onTimeSum);
      resultArr.push(fetchData);
    }
  }
  //Find Objects which is already pushed having same name and date as current object
  var findDuplicates = resultArr.filter(function(data) {
    if (data.name == fetchData.name && data.Date == fetchData.Date) {
      return data;
    }
  });
  //We will not push same object having same name and date again
  if (findDuplicates.length == 0) {
    resultArr.push(fetchData);
  }
}
console.log('resultArr' + JSON.stringify(resultArr));

Output: [{"name":"A","Date":"10/19/2015","OnTime":"0.4","Purpose":"x","OffTime":"0"},{"name":"B","Date":"10/19/2015","OnTime":"0.01","Purpose":"y","OffTime":"0"},{"name":"C","Date":"10/19/2015","OnTime":"0.03","Purpose":"z","OffTime":"0"},{"name":"A","Date":"10/20/2015","OnTime":"0.01","Purpose":"x","OffTime":"0"}] — Navdeep Singh, May 25 '20 at 15:04

Simon Farrugia · Answer 4 · 2015-10-20T07:28:40.213

Something like this maybe:

function GroupBy(array){
  var fields = Array.prototype.slice.call(arguments, 1);
  var field = fields.shift();

  if(field){

    var groupedArray = [];
    for(var i in array){
      var index = array[i][field];
      groupedArray[index] = groupedArray[index] || [];
      groupedArray[index].push(array[i]);
    }

    for(var i in groupedArray)
      groupedArray[i] = GroupBy.bind(null, groupedArray[i]).apply(null, fields);

    return groupedArray;
  }

  return array;
}

Have not tested it, so don't expect it to work. But it should be a good start.

This is how you use it.

var array = [...]; //your array
var groupedArray = GroupBy(array, "name", "Date");

//Then to get records by a specific fields just:
var A_10192015 = groupedArray["A"]["10/19/2015"];

Not tested, "dont expect it to work", no explanation of code, what is this code doing? -1 — Stephan Bijzitter, Oct 19 '15 at 18:32

Grouping and adding JSON Object for multiple keys

4 Answers4