Sum values in JSON response with jquery

Question

I am trying to sum up a col1, col2, and col3 in my JSON using Jquery and not sure how to go about doing it using the .each loop. Another problem is col1, and col2, and col3 are dynamic so the names will be changing.

This is my JSON string:

 var data = [
{
    "Country": "Spain",
    "Year": "2012",
    "col1": "100.75",
    "col2": "500",
    "col3": "200"
},
{
    "Country": "Spain",
    "Year": "2013",
    "col1": "200",
    "col2": "500",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2011",
    "col1": "100",
    "col2": "100",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2012",
    "col1": "100",
    "col2": "100",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2013",
    "col1": "100",
    "col2": "200",
    "col3": "300"
}
]

http://jsfiddle.net/RHLKa/

I want two results that would look something like this:

1) Totals per country

Spain 300.75, 1000, 500
France 300, 400, 900

2) Grand total:

["col1", 600.75],
["col2", 1400],
["col3", 1400]

Thanks Cheers

Kiril · Accepted Answer · 2014-01-23 18:10:00Z

To store results it's better to use object (or associative array). Also not to mix properties that should be applied and that should not - you may store them in a separate array, which could be a property in a config file or smth like that. Here is the code (jsFiddle):

var countries = {},
    columns = {},
    sumCols = ['col1', 'col2', 'col3'];

$.each(data, function(index, obj) {
    if (!countries[obj['Country']]) {
        countries[obj['Country']] = {};
    }
    $.each(sumCols, function (index, col) {
        if (!countries[obj['Country']][col]) {
            countries[obj['Country']][col] = 0;
        }
        if (!columns[col]) {
            columns[col] = 0;
        }
        var val = parseFloat(obj[col]);
        if (!isNaN(val)) {
            countries[obj['Country']][col] += val;
            columns[col] += val;
        }
    });
});

As the result:

{
    "Spain":{
        "col1":300.75,
        "col2":1000,
        "col3":500},
    "France":{
        "col1":300,
        "col2":400,
        "col3":900
    }
}

{
    "col1":600.75,
    "col2":1400,
    "col3":1400
}

ken.dunnington · Accepted Answer · 2014-01-23 18:02:07Z

1

The main issue you're going to face working with dynamic property names is the fact that JavaScript explicitly does not guarantee ordering. So you can't really match up looping over object property names with an array index. Because of that, you would need to use objects to store your results. Here's a (not very optimized) bit of code that should work. You can manipulate the results afterwards if need be.

var results = {};
var totals = {};

// Loop over the array of data
for (var d = 0; d < data.length; d++) {
    var o = data[d];
    var country = o["Country"];
    var values = results[country] || {};
    // Since the column names may change, we need to loop over the object
    for (var key in o) {
        // Weed out the two known keys
        if (o.hasOwnProperty(key) && key.toLowerCase() != "country" && key.toLowerCase() != "year") {
            // Now find the entry in the values obj that matches
            var column = values[key] || 0;
            // And the corresponding entry in the totals object
            var totalsColumn = totals[key] || 0;
            column += parseFloat(o[key]);
            totalsColumn += parseFloat(o[key]);
            values[key] = column;
            totals[key] = totalsColumn;
        }
    }
    results[country] = values;
}

This yields two objects, results, which looks like this:

France:
    col1: 300
    col2: 400
    col3: 900
Spain:
    col1: 300.75
    col2: 1000
    col3: 500

And totals, which looks like this:

col1: 600.75
col2: 1400
col3: 1400

I posted this into an updated version of your fiddle.

answered Jan 23, 2014 at 18:02

ken.dunnington

9151 gold badge7 silver badges20 bronze badges

1 Comment

Moxie C Over a year ago

@ken.dunnigton thanks for this piece of code.The total needs to be in array with this format as I am passing this into highcharts, how can I get it like this var total = [["col1", 600,75], ["col2",1400], ["col3",1400]]

Collectives™ on Stack Overflow

Sum values in JSON response with jquery

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related