javascript sum array values in multidimensional array with conditions

0
votes

I'm trying to use javascript to iterate over an object and sum the values of a property grouping by the value of another property. Here is my example data that I am iterating over:

63.450,     2013-01-01 00:00:00,    63.450,     2013,   Amex Residuals
3.980,      2013-01-01 00:00:00,    3.980,      2013,   Gift Cards on Demand
-16.000,    2013-01-01 00:00:00,    -16.000,    2013,   Month End Fee Rejects
67.140,     2013-02-01 00:00:00,    67.140,     2013,   Amex Residuals
-600.000,   2013-02-01 00:00:00,    -600.000,   2013,   Bonus Take Back - Closed Less Than 6 Months
-400.000,   2013-02-01 00:00:00,    -400.000,   2013,   Bonus Take Back - Did Not Activate
8.910,      2013-02-01 00:00:00,    8.910,      2013,   Checks On Demand
13997.770,  2013-02-01 00:00:00,    13997.770,  2013,   Global Report
-15.000,    2013-02-01 00:00:00,    -15.000,    2013,   Merchant Adjustments
-34.500,    2013-02-01 00:00:00,    -34.500,    2013,   Month End Fee Rejects

The data continues to include other months (through october) in the second column. I need to sum all the values in the first column together for each distinct month, as well as create a javascript date from the 4th column year, so the result should be something like this:

var data = [ [Date.UTC('2013', i, 1), parseFloat('51.43')], [Date.UTC('2013', i, 1), parseFloat(13024.32)] ];

Essentially I should end up with a 2 element array for each month total tupled with a date object from the 4th column. I'm just not sure how to do the iterations for summing on the conditional grouping of the 2nd column (date).

2
javascriptarraysmultidimensional-arraysum
You mention multidimensional arrays. Is this from splitting the above text by lines and then splitting on ,+whitespace? Also, why do you need information from the forth column when it's available in more detail in the second column? - Scott Mermelstein
@ScottMermelstein I couldn't post the javascript object the way it appears in my browser console inspector so I posted the raw data before it gets split and json encoded. Essentially, the data the javascript will be operating on is a standard javascript object with inner properties. And you are correct about column 4 vs. column 2. I suppose I could split the contents of column 2, or convert that string to a javascript date object rather than the current method... - sadmicrowave

2 Answers

2
votes

The general process is:

  • create an empty object to hold your answer
  • go through each line
    • extract the month and year from the second element.
    • set value to 0
    • if month and year are already in your object, set value to month and year's value
    • Add the newest value to the current one
    • associate the month and year for the object with the value

Without knowing your exact structures, here's some semi-pseudo-code

var data = {}
for (var i = 0; i < lines.length; ++i) {
    var monthYear = lines[i][1].substring(0, 6);
    var value = 0;
    if (data[monthYear]) {
        value = data[monthYear];
    }
    value += +lines[i][0]; // unary + to make sure the value is a number, not a string
    data[monthYear] = value;
}

(Obviously, if you have objects instead of arrays, you can access them appropriately.)

1
votes

Based on @scottmermelstein's answer (which I will accept because it led me to this) I created the following, which gives me the results I'm looking for:

 var objCalc    = {'unCalc':{}, 'reCalc':{}},
     objUnCalc  = {},
     objReCalc  = {},
     arrUnCalc  = [],
     arrReCalc  = [];
 //lets sum up the month totals first, and create the date object that is needed for the graph
 $.each(data.TableContainer, function(i,e){                      
    objCalc.unCalc[ e.ActualDate ] = ( objCalc.unCalc[ e.ActualDate ] ? objCalc.unCalc[ e.ActualDate ] + parseFloat(e.GrpTotal) : parseFloat(e.GrpTotal) );
    objCalc.reCalc[ e.ActualDate ] = ( objCalc.reCalc[ e.ActualDate ] ? objCalc.reCalc[ e.ActualDate ] + parseFloat(e.GrpBonusAmt) : parseFloat(e.GrpBonusAmt) );
 });

 //now we iterate over the summed values from above and push them to usable highcharts arrays
 $.each(objCalc, function(i,e){
        $.each(e, function(y,el){
            var arrDate     = y.substring(0,y.indexOf(' ')).split('-'), //renders something like ['2013','02','01']
                UTC         = Date.UTC(arrDate[0], parseInt(arrDate[1])-1, parseInt(arrDate[2])), //subtract 1 from month to make UTC date 0-based
                arr         = [ UTC, parseFloat( parseFloat( el ).toFixed( 2 ) ) ];

            if( i == "unCalc" ) arrUnCalc.push( arr );
            if( i == "reCalc" ) arrReCalc.push( arr );
        });
 });