Generate HTML table row containing a value and its sibling and parent values

Question

I have a JSON object representing a set of segments and I'd like to create an HTML table that compares each segment in the following format:

-------------------------------------------------------------------------------
domain group  |    vertical  |       measure       |  Segment 1  |  Segment 2
-------------------------------------------------------------------------------
group 1       |      all     |     users clicking  |        340  |       340
              |              |    % users opening  |        10%  |       10%
               ----------------------------------------------------------------
              |     cars     |     users clicking  |        340  |       340
              |              |    % users opening  |        10%  |       10%
-------------------------------------------------------------------------------
group 2       |      all     |     users clicking  |        340  |       340
              |              |    % users opening  |        10%  |       10%
               ----------------------------------------------------------------
              |     cars     |     users clicking  |        340  |       340
              |              |    % users opening  |        10%  |       10%
-------------------------------------------------------------------------------

How can I create a row for each segment value that will display the value's measure and then group these by vertical and then by domain group?

JSON:

{
  "set": [
    {
      "id": 1,
      "segment_desc": "Segment 1",
      "segment_summary": [
        {
          "section_type": "domain group",
          "section_value": "group 1",
          "children": [
            {
              "children": [
                {
                  "format": "float",
                  "measure": "users clicking",
                  "value": 340
                },
                {
                  "format": "float",
                  "measure": "% users opening",
                  "value": 10
                }
              ],
              "section_type": "vertical",
              "section_value": "all"
            },
            {
              "children": [
                {
                  "format": "float",
                  "measure": "users clicking",
                  "value": 340
                },
                {
                  "format": "float",
                  "measure": "% users opening",
                  "value": 10
                }
              ],
              "section_type": "vertical",
              "section_value": "cars"
            }
          ]
        }
      ]
    },
    {
      "id": 2,
      "segment_desc": "Segment 2",
      "segment_summary": [
        {
          "section_type": "domain group",
          "section_value": "group 2",
          "children": [
            {
              "children": [
                {
                  "format": "float",
                  "measure": "users clicking",
                  "value": 340
                },
                {
                  "format": "float",
                  "measure": "% users opening",
                  "value": 1.24
                }
              ],
              "section_type": "vertical",
              "section_value": "all"
            },
            {
              "children": [
                {
                  "format": "float",
                  "measure": "users clicking",
                  "value": 340
                },
                {
                  "format": "float",
                  "measure": "% users opening",
                  "value": 10
                }
              ],
              "section_type": "vertical",
              "section_value": "cars"
            }
          ]
        }
      ]
    }
  ]
}

EDIT: An acceptable alternative to the above table layout would be to print a row for each compared segment value without it being grouped by domain group and vertical:

    domain group | vertical      |    measure          | segment 1 | segment 2
  -------------------------------------------------------------------------------
    group 1      | all           |    users clicking   | 340       | 340
  -------------------------------------------------------------------------------
    group 1      | all           |    % users opening  | 10%       | 10%
  -------------------------------------------------------------------------------
    group 1      | cars          |    users clicking   | 340       | 340
  -------------------------------------------------------------------------------
    group 1      | cars          |    % users opening  | 10%       | 10%
  -------------------------------------------------------------------------------
    group 2      | all           |    users clicking   | 340       | 340

I have jQuery and lodash libraries installed and either library can be used.

am. you have set of segments which are different columns in your case. how should be filled all the segments columns in the row? — maxlego, Apr 30 '14 at 22:04
@maxlego I'm not sure I understand your question, but to clarify the segments, there may be anywhere from 2 to 6 segments being compared. I'd need to have a row that displays each segments corresponding values. — dagarre, Apr 30 '14 at 22:12
this json doesnt seem to compile. I put it into jsfiddle and it dies — Dave Alperovich, Apr 30 '14 at 22:29
I added children: [...], instead of the actual children in order to simplify it. I'll edit that. — dagarre, Apr 30 '14 at 22:34
I fixed that part by copy the previous children in its place. still dies. having trouble finding where... — Dave Alperovich, Apr 30 '14 at 22:34
@daveA Ah, it was the "%" symbol in some of the values. I've removed them; it should pass now. — dagarre, Apr 30 '14 at 22:41
The data doesn't parse in a hierarchical way that corresponds to your table. You want consolidation by domain group, but segment is a root parent. In your table, Segment should be a property of children. Vertical should be a property, instead it's a property with a corresponding property. This data needs to be transformed, and not in a clearly intuitive way. — Dave Alperovich, May 01 '14 at 00:56
what's the rule to get values of segment2 in the group1 row? There is no group1 in the segement2 object. — emn178, May 02 '14 at 02:53
This might help you - http://stackoverflow.com/questions/17066636/jquery-parsing-json-objects-for-html-table — tushar.dahiwale, May 02 '14 at 10:31
You should modify your JSON structure (ideally on the server, or alternatively on the client side) to something that correlates closely with your desired output or to the JSON table schema http://dataprotocols.org/json-table-schema/ . Then I'd recommend using a templating engine like Handlebars to complete the work. — gfullam, May 07 '14 at 14:46

score 2 · Accepted Answer · edited May 07 '14 at 05:08

Try this (pattern)

html

<table>
    <th>Groups</th>
    <tr class="group-1">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    <tr class="group-1">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    <tr class="group-2">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    <tr class="group-2">
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
</table>

js

   // json, i.e.g., `{ "groups" : [{..}] }`
   var Groups = {
        "groups": [{
            "group": {
                "id": 1,
                "description" : "clicks",
                    "all": [{
                    "segment": 10
                }, {
                    "segment": 20
                }],
                    "cars": [{
                    "segment": 30
                }, {
                    "segment": 40
                }]
            }
        }, {
            "group": {
                "id": 2,
                "description" : "clicks",
                    "all": [{
                    "segment": 50
                }, {
                    "segment": 60
                }],
                    "cars": [{
                    "segment": 70
                }, {
                    "segment": 80
                }]
            }
        }]
    };

        $.each(Groups, function (key, value) {
            var g1all = $("tr.group-1:eq(0) > td");
            var g1cars = $("tr.group-1:eq(1) > td");
            var g2all = $("tr.group-2:eq(0) > td");
            var g2cars = $("tr.group-2:eq(1) > td");
            $(g1all).eq(0).html("Group: " + value[0].group.id);
            $(g1all).eq(1).html("Vertical: " 
              + ("all" in value[0].group ? "All" : "error") 
              +" Measure: "+ value[0].group.description);
            $(g1all).eq(2).html("Segment 1:  " + value[0].group.all[0].segment);
            $(g1all).eq(3).html(" Segment 2: " + value[0].group.all[1].segment);
            $(g1cars).eq(0).html("Group: " + value[0].group.id);
            $(g1cars).eq(1).html("Vertical: " 
              + ("cars" in value[0].group ? "Cars" : "error") 
              +" Measure: "+ value[0].group.description);
            $(g1cars).eq(2).html("Segment 1:  " + value[0].group.cars[0].segment);
            $(g1cars).eq(3).html(" Segment 2: " + value[0].group.cars[1].segment);

            $(g2all).eq(0).html("Group: " + value[1].group.id);
            $(g2all).eq(1).html("Vertical: " 
              + ("all" in value[1].group ? "All" : "error")  
              +" Measure: "+ value[1].group.description);
            $(g2all).eq(2).html("Segment 1:  " + value[1].group.all[0].segment);
            $(g2all).eq(3).html(" Segment 2: " + value[1].group.all[1].segment);
            $(g2cars).eq(0).html("Group: " + value[1].group.id);
            $(g2cars).eq(1).html("Vertical: " 
              + ("cars" in value[1].group ? "Cars" : "error")  
              +" Measure: "+ value[1].group.description);
            $(g2cars).eq(2).html("Segment 1:  " + value[1].group.cars[0].segment);
            $(g2cars).eq(3).html(" Segment 2: " + value[1].group.cars[1].segment);

        })
    })

jsfiddle

score 2 · Answer 2 · edited May 07 '14 at 05:08

Since you have the JSON data organised differently from the structure you want, you need to do some pre-processing on it. The easiest way is to linearise it:

function lineariseData(data) {
    var ret = [];
    $(data.set).each(function() {
        var segment = this;
        $(segment.segment_summary).each(function() {
            var segment_summary = this;
            $(segment_summary.children).each(function() {
                var section = this;
                $(section.children).each(function() {
                    var measure = this;
                    ret.push({
                        segment: segment.segment_desc,
                        group: segment_summary.section_value,
                        vertical: section.section_value,
                        measure: measure.measure,
                        value: measure.value
                    });
                });
            });
        });
    });
    return ret;
}

The above function returns a simple array of objects similar to what you need. After this, since you want to group them by group and vertical, you need to create a hierarchical object:

function groupItems(linearData) {
    var groups = [];
    var groupNames = [];
    $(linearData).each(function() {
        if ($.inArray(this.group, groupNames) < 0)
            groupNames.push(this.group);
    });
    var segmentNames = [];
    $(linearData).each(function() {
        if ($.inArray(this.segment, segmentNames) < 0)
            segmentNames.push(this.segment);
    });
    $(groupNames).each(function() {
        var groupName = this;
        var itemsInGroup = $(linearData).filter(function() {
            return this.group == groupName;
        });
        var currentGroup = { name: groupName, verticals: [], totalItems: 0 };
        groups.push(currentGroup);
        var verticalNames = [];
        $(itemsInGroup).each(function() {
            if ($.inArray(this.vertical, verticalNames) < 0)
                verticalNames.push(this.vertical);
        });
        $(verticalNames).each(function() {
            var verticalName = this;
            var itemsInVertical = $(itemsInGroup).filter(function() {
                return this.vertical == verticalName;
            });
            var currentVertical = { name: verticalName, measures: [], totalItems: 0 };
            currentGroup.verticals.push(currentVertical);
            var measureNames = [];
            $(itemsInVertical).each(function() {
                if ($.inArray(this.measure, measureNames) < 0)
                    measureNames.push(this.measure);
            });
            $(measureNames).each(function() {
                var measureName = this;
                var itemsInMeasure = $(itemsInVertical).filter(function() {
                    return this.measure == measureName;
                });
                var currentMeasure = { name: measureName };
                currentGroup.totalItems++;
                currentVertical.totalItems++;
                $(segmentNames).each(function() {
                    currentMeasure[this] = null;
                });
                currentVertical.measures.push(currentMeasure);
                $(itemsInMeasure).each(function() {
                    currentMeasure[this.segment] = this.value;
                });
            });
        });
    });
    return groups;
}

Now you just need to render the data, so if you have a simple table in your markup:

Here is an example for the rendering function:

function renderGroups(groups) {
    var table = $('#groupTable').empty();
    var header = $("<tr></tr>").appendTo(table);
    $('<th>Group</th>').appendTo(header);
    $('<th>Vertical</th>').appendTo(header);
    $('<th>Measure</th>').appendTo(header);
    var headerUpdated = false;
    $(groups).each(function() {
        var group = this;
        var groupCellAdded = false;
        $(group.verticals).each(function() {
            var vertical = this;
            var verticalCellAdded = false;
            $(vertical.measures).each(function() {
                var measure = this;
                var tr = $("<tr />");
                if (!groupCellAdded) {
                    groupCellAdded = true;
                    $('<td rowspan="' + group.totalItems + '">' + group.name + '</td>').appendTo(tr);
                }
                if (!verticalCellAdded) {
                    verticalCellAdded = true;
                    $('<td rowspan="' + vertical.totalItems + '">' + vertical.name + '</td>').appendTo(tr);
                }
                $('<td>' + measure.name + '</td>').appendTo(tr);
                for (var segment in measure) {
                    if (segment != 'name') {
                        if (!headerUpdated)
                            $('<th>' + segment + '</th>').appendTo(header);
                        $('<td>' + measure[segment] + '</td>').appendTo(tr);
                    }
                }
                headerUpdated = true;
                table.append(tr);
            });
        });
    });
}

Now you just need to call those three functions and you are done:

var linearData = lineariseData(data); // data is your JSON object
var groups = groupItems(linearData);
renderGroups(groups);

Here is a jsfiddle to show the whole thing: jsFiddle

Note: your data only has values for group 1 in segment 1 and for group 2 in segment 2, so a few cells will have a null value.

score 1 · Answer 3 · edited May 07 '14 at 05:08

My method is quite similar to Stefano's, but I would rather use a js html template, such as underscore to do this.

I would first create the HTML table

<table id="table">
    <thead>
        <th>domain group</th>
        <th>vertical</th>
        <th>measure</th>
        <th>Segment 1</th>
        <th>Segment 2</th>
    </thead>
<tbody>
</tbody>
</table>

then I can use template to fill the data into the table, something like this:

var row = '\
<% for(var i=0;i<children.length;i++) { %>\
    <tr>\
        <td><%= group %></td>\
        <td><%= section_value %></td>\
        <td><%= children[i].measure %></td>\
        <td><%= children[i].value %></td>\
        <td><%= children[i].value %></td>\
    </tr>\
<% } %>\
';

items=data.set;
for(var i=0;i<items.length;i++){
    var segmentChildren = items[i].segment_summary[0].children;
    for(var j=0;j<segmentChildren.length;j++){
        var item = segmentChildren[j];
        item.group = items[i].segment_summary[0].section_value;
        var html = _.template(row,item);
        $("#table tbody").append(html);
    }
}

After doing some css, you can got all you want. Here's my demo: jsFiddle

I also noticed your missing Segment 1 value in group 2, Segment 2 value in group 1. Your original table simply write the value twice, so I did the same. Moreover, there is no "%" in "% users opening" value, you can either add it during your JSON process, or add it in js.

If you want to get exactly same table in your first format, you can do some html colspan rowspan in the code.

Generate HTML table row containing a value and its sibling and parent values

3 Answers3