Summarize and Group JSON object in Jquery

Question

I am having problems summarizing and grouping my object in Jquery as I would like to group by country and then have a grand total based on subperiods 4,5,and 6 for values within col1. The col1, col2, col3 are dynamic, but using this is an example.

What I need is to get the total by country which is stored in the country variable, grand total per subperiod stored in the columns variable, and the total count stored in the averages variable.

Here is my code in jsfiddle:

http://jsfiddle.net/8vVK7/8/

My object:

var g_jsonData =

[
{
    "Country": "Spain",
    "Year": "2000",
    "Subperiod": "4",
    "col1": "75",
    "col2": "500",
    "col3": "200"
},
{
    "Country": "Spain",
    "Year": "2001",
    "Subperiod": "4",
    "col1": "50",
    "col2": "500",
    "col3": "300"
},
{
    "Country": "Spain",
    "Year": "2002",
    "Subperiod": "4",      
    "col1": "50",
    "col2": "200",
    "col3": "300"
},
{
    "Country": "Spain",
    "Year": "2003",
    "Subperiod": "4",      
    "col1": "",
    "col2": "200",
    "col3": "300"
},
{
    "Country": "Spain",
    "Year": "2005",
    "Subperiod": "5",
    "col1": "125",
    "col2": "500",
    "col3": "300"
},
{
    "Country": "Spain",
    "Year": "2012",
    "Subperiod": "6",
    "col1": "100.75",
    "col2": "500",
    "col3": "200"
},
{
    "Country": "Spain",
    "Year": "2013",
    "Subperiod": "6",
    "col1": "200",
    "col2": "500",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2000",
    "Subperiod": "4",    
    "col1": "100",
    "col2": "100",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2001",
    "Subperiod": "4",      
    "col1": "100",
    "col2": "100",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2002",
    "Subperiod": "4",      
    "col1": "100",
    "col2": "200",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2005",
    "Subperiod": "5",      
    "col1": "100",
    "col2": "200",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2012",
    "Subperiod": "6",      
    "col1": "100",
    "col2": "100",
    "col3": "300"
},
{
    "Country": "France",
    "Year": "2013",
    "Subperiod": "6",      
    "col1": "100",
    "col2": "100",
    "col3": "300"
}];

Here is the end result which I would like to get and this is based on summarizing col1 per subperiod per country.

countries =    
{
   "Spain":{
      "4":175,
      "5":125,
      "6":300.75
  },    
   "France":{
      "4":300,
      "5":100,
      "6":200,
  }
}

Grand total for the subperiods 4,5,6 should show as:

columns = 
{
    "4":475,
    "5":225,
    "6":500.75
}

I would also like to get the total count per country, per subperiod:

averages = 
{
   "Spain":{
      "4":3,
      "5":1,
      "6":2
  },    
   "France":{
      "4":3,
      "5":1,
      "6":2
  }
}

I would like to try to stick to my code as close as possible. Thanks, appreciate the help.

@Cerbrus I will use Spain as example for subperiod 4 for col1 it has 75 + 50 + 50 + "". So that is a total count of 4. However, because in Subperiod 4 Year 2003 has a blank value, we don't count it. So the total count is 3. Does that make sense. — Moxie C
– Moxie C, Commented Feb 19, 2014 at 9:44
@Cerbrus maybe averages isn't a good variable name as it should probably be called Count. — Moxie C
– Moxie C, Commented Feb 19, 2014 at 9:45

Cerbrus · Accepted Answer · 2014-02-19 10:02:32Z

1

This should do the trick:

g_jsonData.reduce(function(out, curr){
    // Make sure the current country exists on the output object.
    // Then add the current object's col1 to the current country's subperiod,
    //   checking to see if the current subperiod exists. Also, cast the `col1` to int `(+curr.col1)`
    out.countries[curr.Country] = out.countries[curr.Country] || {};
    out.countries[curr.Country][curr.Subperiod] = (out.countries[curr.Country][curr.Subperiod] || 0) + (+curr.col1);

    // And add it to the total, too.
    out.columns[curr.Subperiod] = (out.columns[curr.Subperiod] || 0) + (+curr.col1);

    // Count occurences
    out.count[curr.Country] = out.count[curr.Country] || {};
    out.count[curr.Country][curr.Subperiod] = (out.count[curr.Country][curr.Subperiod] || 0) + 1;
    return out;
},
{'columns':{},'countries':{},'count':{}}); // second parameter to `reduce` is a default object, in this case: `{'columns':{},'countries':{},'count':{}}}`

Returns:

{
    "columns": {
        "4": 475,
        "5": 225,
        "6": 500.75
    },
    "countries": {
        "Spain": {
            "4": 175,
            "5": 125,
            "6": 300.75
        },
        "France": {
            "4": 300,
            "5": 100,
            "6": 200
        }
    },
    "count": {
        "Spain": {
            "4": 3,
            "5": 1,
            "6": 2
        },
        "France": {
            "4": 3,
            "5": 1,
            "6": 2
        }
    }
}

Please note this doesn't change g_jsonData, it only returns the new object.
To use the new object, you have to assign it to a variable, of course:

var output = g_jsonData.reduce(func....);

You could make the code a little shorter (/easier to look at) like this:

g_jsonData.reduce(function(out, curr){
    var c = curr.Country,
        s = curr.Subperiod,
        v = +curr.col1; //value cast to int already
    out.countries[c]    =  out.countries[c]    || {};
    out.countries[c][s] = (out.countries[c][s] || 0 ) + v;
    out.count[c]        =  out.count[c]        || {};
    out.count[c][s]     = (out.count[c][s]     || 0 ) + 1;
    out.columns[s]      = (out.columns[s]      || 0 ) + v;
    return out;
},
{'columns':{},'countries':{},'count':{}});

Now it's relatively easy to make col1 a variable name.
Replace:

v = +curr.col1;

With:

v = +curr['col1'];

Or:

v = +curr[someVariable];

edited Feb 19, 2014 at 10:02

answered Feb 19, 2014 at 8:15

Cerbrus

73.3k19 gold badges138 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Cerbrus Over a year ago

Edited to change the data format a little. I'm not quite sure how you are getting the numbers in your "averages".

Moxie C Over a year ago

I will use Spain as example for subperiod 4 for col1 it has 75 + 50 + 50 + "". So that is a total count of 4. However, because in Subperiod 4 Year 2003 has a blank value, we don't count it. So the total count is 3. Does that make sense.

Cerbrus Over a year ago

@MoxieC: Added count.

Moxie C Over a year ago

Suppose I don't know the name of col1 as the name could be dynamic, how would you code it. Also, say I want to group it by Year instead of Country, is there a more generic way of doing it?

Cerbrus Over a year ago

@MoxieC: take a look at the last part of my answer.

Collectives™ on Stack Overflow

Summarize and Group JSON object in Jquery

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related