CODEWITHSUNDEEP
javascriptunderscore.jsgrouping
James Oshomah
James Oshomah

Reputation: 224

Grouping Json Data By Multiple Field

I have a JSON data that contains the list of payment made by some members as club dues. I am trying to use underscore.js library to group the data and also to sum up the data.

My sample JSON data is provided below.

var dues = [{
    memberid: 194,
    payment: [
      { month: 'January', amount: 2500, year: 2015 },
      { month: 'February', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2015 },
      { month: 'April', amount: 2500, year: 2015 },
      { month: 'May', amount: 2500, year: 2015 },
      { month: 'June', amount: 2500, year: 2015 },
      { month: 'July', amount: 2500, year: 2015 },
      { month: 'August', amount: 2500, year: 2015 },
      { month: 'September', amount: 2500, year: 2015 },
      { month: 'October', amount: 2500, year: 2015 },
      { month: 'November', amount: 2500, year: 2015 },
      { month: 'December', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2016 },
      { month: 'May', amount: 2500, year: 2016 },
      { month: 'September', amount: 2500, year: 2016 }
    ],
    name: 'Makey Trashey'
  }, {
    memberid: 156,
    payment: [
      { month: 'January', amount: 2500, year: 2015 },
      { month: 'February', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2015 },
      { month: 'April', amount: 2500, year: 2015 },
      { month: 'May', amount: 2500, year: 2015 },
      { month: 'June', amount: 2500, year: 2015 },
      { month: 'July', amount: 2500, year: 2015 },
      { month: 'August', amount: 2500, year: 2015 },
      { month: 'September', amount: 2500, year: 2015 },
      { month: 'October', amount: 2500, year: 2015 },
      { month: 'November', amount: 2500, year: 2015 },
      { month: 'December', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2016 },
      { month: 'May', amount: 2500, year: 2016 },
      { month: 'July', amount: 2500, year: 2016 }
    ],
    name: 'Makey Johnny'
  }
]

Please, I want to sum the total of the amount key in the payment key array by month.

I want to be able to group by month while summing the amount paid each month of the year. I want my data in this format.

[{
  month: 'January',
  total: 'to be the amount of all amount paid in January'
  year: 1995
}, {
  month: 'February',
  total: 'to be the amount of all amount paid in January'
  year: 1995
}, {
  month: 'March',
  total: 'to be the amount of all amount paid in January'
  year: 1995
}]

Thanks for helping out.

Upvotes: 0

Views: 2278

Answers (2)

Ashwin Sadeep
Ashwin Sadeep

Reputation: 145

Since the question is tagged with underscore:

var dues = [{memberid:194,payment:[{month:'January',amount:2500,year:2015},{month:'February',amount:2500,year:2015},{month:'March',amount:2500,year:2015},{month:'April',amount:2500,year:2015},{month:'May',amount:2500,year:2015},{month:'June',amount:2500,year:2015},{month:'July',amount:2500,year:2015},{month:'August',amount:2500,year:2015},{month:'September',amount:2500,year:2015},{month:'October',amount:2500,year:2015},{month:'November',amount:2500,year:2015},{month:'December',amount:2500,year:2015},{month:'March',amount:2500,year:2016},{month:'May',amount:2500,year:2016},{month:'September',amount:2500,year:2016}],name:'Makey Trashey'},{memberid:156,payment:[{month:'January',amount:2500,year:2015},{month:'February',amount:2500,year:2015},{month:'March',amount:2500,year:2015},{month:'April',amount:2500,year:2015},{month:'May',amount:2500,year:2015},{month:'June',amount:2500,year:2015},{month:'July',amount:2500,year:2015},{month:'August',amount:2500,year:2015},{month:'September',amount:2500,year:2015},{month:'October',amount:2500,year:2015},{month:'November',amount:2500,year:2015},{month:'December',amount:2500,year:2015},{month:'March',amount:2500,year:2016},{month:'May',amount:2500,year:2016},{month:'July',amount:2500,year:2016}],name:'Makey Johnny'}];

// Get all the payments from each members & flatten it to a 1-d array
var grouped = _.chain(dues).pluck('payment').flatten().groupBy(function (payment) {
    return [payment.month, payment.year].join('_'); // group payments with month+year as the grouping key
}).map(function (monthlyPayments) {
    // Calculate total for each group
    var totalMonthlyPayment = _.reduce(monthlyPayments, function (memo, payment) {
        return payment.amount + memo;
    }, 0);
    return {month: monthlyPayments[0].month, year: monthlyPayments[0].year, total: totalMonthlyPayment};
}).value();

console.log(grouped);
<script src="http://underscorejs.org/underscore-min.js"></script>

Upvotes: 0

Mr. Polywhirl
Mr. Polywhirl

Reputation: 48610

The easiest thing to do, is to create your own grouping and hashing function.

var dues = retrieveData();

var grouped = dues.reduce(function(groupedData, member, index, members) {
  return member.payment.reduce(function(memberData, payment) {
    var key = payment.month + '_' + payment.year;
    if (memberData[key] == null) {
      memberData[key] = {
        month : payment.month,
        total: payment.amount || 0,
        year: payment.year
      };
    } else {
      memberData[key].total += payment.amount; // Update payment.
    }
    return memberData;
  }, groupedData);
}, {});

var valueArr = Object.keys(grouped).map(function(key) {
  return grouped[key];
});

console.log(valueArr);


function retrieveData() {
  return [ {
    memberid: 194,
    payment: [
      { month: 'January', amount: 2500, year: 2015 },
      { month: 'February', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2015 },
      { month: 'April', amount: 2500, year: 2015 },
      { month: 'May', amount: 2500, year: 2015 },
      { month: 'June', amount: 2500, year: 2015 },
      { month: 'July', amount: 2500, year: 2015 },
      { month: 'August', amount: 2500, year: 2015 },
      { month: 'September', amount: 2500, year: 2015 },
      { month: 'October', amount: 2500, year: 2015 },
      { month: 'November', amount: 2500, year: 2015 },
      { month: 'December', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2016 },
      { month: 'May', amount: 2500, year: 2016 },
      { month: 'September', amount: 2500, year: 2016 }
    ],
    name: 'Makey Trashey'
  }, {
    memberid: 156,
    payment: [
      { month: 'January', amount: 2500, year: 2015 },
      { month: 'February', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2015 },
      { month: 'April', amount: 2500, year: 2015 },
      { month: 'May', amount: 2500, year: 2015 },
      { month: 'June', amount: 2500, year: 2015 },
      { month: 'July', amount: 2500, year: 2015 },
      { month: 'August', amount: 2500, year: 2015 },
      { month: 'September', amount: 2500, year: 2015 },
      { month: 'October', amount: 2500, year: 2015 },
      { month: 'November', amount: 2500, year: 2015 },
      { month: 'December', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2016 },
      { month: 'May', amount: 2500, year: 2016 },
      { month: 'July', amount: 2500, year: 2016 }
    ],
    name: 'Makey Johnny'
  } ];
};

Output

[
  { "month": "January",    "total": 5000,  "year": 2015 },
  { "month": "February",   "total": 5000,  "year": 2015 },
  { "month": "March",      "total": 5000,  "year": 2015 },
  { "month": "April",      "total": 5000,  "year": 2015 },
  { "month": "May",        "total": 5000,  "year": 2015 },
  { "month": "June",       "total": 5000,  "year": 2015 },
  { "month": "July",       "total": 5000,  "year": 2015 },
  { "month": "August",     "total": 5000,  "year": 2015 },
  { "month": "September",  "total": 5000,  "year": 2015 },
  { "month": "October",    "total": 5000,  "year": 2015 },
  { "month": "November",   "total": 5000,  "year": 2015 },
  { "month": "December",   "total": 5000,  "year": 2015 },
  { "month": "March",      "total": 5000,  "year": 2016 },
  { "month": "May",        "total": 5000,  "year": 2016 },
  { "month": "September",  "total": 2500,  "year": 2016 },
  { "month": "July",       "total": 2500,  "year": 2016 }
]

Generic Approach

Below, I have decoupled the data from the code above and put the logic into its own function. It is far from perfect, but it's a start.

var dues = retrieveData();
var valueArr = groupData(dues, 'payment', ['month', 'year'], 'amount', 'total');

console.log(valueArr);

function groupData(data, targetProp, keys, accumFrom, accumTo) {
  var grouped = data.reduce(function(groupedData, item, index, items) {
    return item[targetProp].reduce(function(obj, record) {
      var key = keys.map(function(key) { return record[key]; }).join('_');
      if (obj[key] == null) {
        obj[key] = keys.reduce(function(data, key) {
          data[key] = record[key];
          return data;
        }, {});
      }
      obj[key][accumTo] = (obj[key][accumTo] || 0) + record[accumFrom];
      return obj;
    }, groupedData);
  }, {});

  return Object.keys(grouped).map(function(key) {
    return grouped[key];
  });
}

function retrieveData() {
  return [ {
    memberid: 194,
    payment: [
      { month: 'January', amount: 2500, year: 2015 },
      { month: 'February', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2015 },
      { month: 'April', amount: 2500, year: 2015 },
      { month: 'May', amount: 2500, year: 2015 },
      { month: 'June', amount: 2500, year: 2015 },
      { month: 'July', amount: 2500, year: 2015 },
      { month: 'August', amount: 2500, year: 2015 },
      { month: 'September', amount: 2500, year: 2015 },
      { month: 'October', amount: 2500, year: 2015 },
      { month: 'November', amount: 2500, year: 2015 },
      { month: 'December', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2016 },
      { month: 'May', amount: 2500, year: 2016 },
      { month: 'September', amount: 2500, year: 2016 }
    ],
    name: 'Makey Trashey'
  }, {
    memberid: 156,
    payment: [
      { month: 'January', amount: 2500, year: 2015 },
      { month: 'February', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2015 },
      { month: 'April', amount: 2500, year: 2015 },
      { month: 'May', amount: 2500, year: 2015 },
      { month: 'June', amount: 2500, year: 2015 },
      { month: 'July', amount: 2500, year: 2015 },
      { month: 'August', amount: 2500, year: 2015 },
      { month: 'September', amount: 2500, year: 2015 },
      { month: 'October', amount: 2500, year: 2015 },
      { month: 'November', amount: 2500, year: 2015 },
      { month: 'December', amount: 2500, year: 2015 },
      { month: 'March', amount: 2500, year: 2016 },
      { month: 'May', amount: 2500, year: 2016 },
      { month: 'July', amount: 2500, year: 2016 }
    ],
    name: 'Makey Johnny'
  } ];
};

Upvotes: 4

Related Questions