Filtered JSON data in mulitple HTML tables with Javascript

Question

I have a JSON file and I am trying to return multiple HTML tables depending on the values of some columns in the JSON.

At the moment I have the following working. More specifically, it loads the JSON values into a table without any filters applying.

var data = {"headers":["plat","chan","group","03","cat","05","06","07","08","09","10","11","12","13","14","15","16","17"],"rows":[["plat1","chan1","aaaa","xxxxx","cat1","yyyy",133,236,0,"656",0,956,37,36,12,67,1],
["plat1","chan1","aaaa","xxxxx","cat2","yyyy",178,867,0,"656",0,647,39,12,18,65,2],
["plat1","chan1","bbb","xxxxx","cat1","yyyy",378,867,0,"656",0,350,32,97,19,64,3],
["plat1","chan1","bbb","xxxxx","cat2","yyyy",137,167,0,"656",0,523,46,86,32,62,4],
["plat2","chan1","aaaa","xxxxx","cat1","yyyy",133,236,0,"656",0,956,37,36,12,67,1],
["plat2","chan1","aaaa","xxxxx","cat2","yyyy",178,867,0,"656",0,647,39,12,18,65,2],
["plat2","chan1","bbb","xxxxx","cat1","yyyy",378,867,0,"656",0,350,32,97,19,64,3],
["plat3","chan1","bbb","xxxxx","cat2","yyyy",137,167,0,"656",0,523,46,86,32,62,4]]};
string = JSON.stringify(data);


//AAA Table + cat1

	var table1 = '<div class="row"><div class="col-lg-6" style="background-color: #e90649; width: 117px;">&nbsp;</div><div class="col-lg-6" style="max-width: 100px; padding-left: 10px; font-size: 2vw;">AAAA<br/><br/><span style="font-size: 1vw;">Cat1</span><br/><span style="font-size: 0.8vw; color: #ccc; font-weight: normal;">07 Jul 2017 - 18 Jul 2017</span></div><div class="col-lg-6"><div class="container"><table><thead><tr><th>plat</th><th>chan</th><th>03</th><th>05</th><th>06</th><th>07</th><th>08</th></tr></thead><tbody>';

	$.each(data.rows, function(i) {

		table1 +="<tr><td>" + data.rows[i][0] + "</td><td>" + data.rows[i][1] + "</td><td>" + data.rows[i][6] + "</td><td>" + data.rows[i][7] + "</td><td>" + data.rows[i][8] + "</td><td>" + data.rows[i][10] + "</td><td>" + data.rows[i][9] + "</td></tr>";
	});
	table1 += '</tbody></table></div></div></div>';
	$("#one").html(table1);

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<div id="one"></div>

Combining the values of three columns, I have three different categories. For each category, JS will return an HTML table but with summed rows. For example, we have 2 rows for a combo of plat1 && chan1 && bbb but in the filtered table, I want to see 1 row with the numbers of the columns summed. As a start, I am trying to filter the JSON and create one table and I am stuck (I will update the question with any problems related to the summing of the numbers etc).

var data = {"headers":["plat","chan","group","03","cat","05","06","07","08","09","10","11","12","13","14","15","16","17"],"rows":[["plat1","chan1","aaaa","xxxxx","cat1","yyyy",133,236,0,"656",0,956,37,36,12,67,1],
["plat1","chan1","aaaa","xxxxx","cat2","yyyy",178,867,0,"656",0,647,39,12,18,65,2],
["plat1","chan1","bbb","xxxxx","cat1","yyyy",378,867,0,"656",0,350,32,97,19,64,3],
["plat1","chan1","bbb","xxxxx","cat2","yyyy",137,167,0,"656",0,523,46,86,32,62,4],
["plat2","chan1","aaaa","xxxxx","cat1","yyyy",133,236,0,"656",0,956,37,36,12,67,1],
["plat2","chan1","aaaa","xxxxx","cat2","yyyy",178,867,0,"656",0,647,39,12,18,65,2],
["plat2","chan1","bbb","xxxxx","cat1","yyyy",378,867,0,"656",0,350,32,97,19,64,3],
["plat3","chan1","bbb","xxxxx","cat2","yyyy",137,167,0,"656",0,523,46,86,32,62,4]]};
string = JSON.stringify(data);

var table1 = '';
$.each(data.rows, function(i) {
	//JSON to string for the Regex check
	if(JSON.stringify(data.rows[i][0]).match(/plat1/) && JSON.stringify(data.rows[i][4]).match(/cat1/) && JSON.stringify(data.rows[i][2]).match(/bbb/))
	{
	    table1 += '<div class="row"><div class="col-lg-6" style="background-color: #e90649; width: 117px;">&nbsp;</div><div class="col-lg-6" style="max-width: 100px; padding-left: 10px; font-size: 2vw;">BBB<br/><br/><span style="font-size: 1vw;">Cat1</span><br/><span style="font-size: 0.8vw; color: #ccc; font-weight: normal;">07 Jul 2017 - 18 Jul 2017</span></div><div class="col-lg-6"><div class="container"><table><thead><tr><th>plat</th><th>chan</th><th>03</th><th>05</th><th>06</th><th>07</th><th>08</th></tr></thead><tbody>';
		
		$.each(data.rows, function(i) 
		{
			table1 +="<tr><td>" + data.rows[i][0] + "</td><td>" + data.rows[i][1] + "</td><td>" + data.rows[i][6] + "</td><td>" + data.rows[i][7] + "</td><td>" + data.rows[i][8] + "</td><td>" + data.rows[i][10] + "</td><td>" + data.rows[i][9] + "</td></tr>";
		});
	
		table1 += '</tbody></table></div></div></div>';
	};
});

	$("#one").html(table1);

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<div id="one"></div>

I am trying to think of the best way doing that. I will definitely need a loop in order to check a) How many different combinations I have, b) To create the table in the HTML, c) To create the subtable that shows the JSON data.

What is the relation between the headers and the data in the row arrays? Are the headers not in the same order as the datapoints? For example, the third header 03 should correspond to the third datapoint in your row arrays? (Values like aaa, bbb etc).. or that would make sense to me. But that's not how your first example works. — jonahe
– jonahe, Commented Sep 3, 2017 at 11:59
Where do you get the values from? Is this exported CSV from Exel or something? Because the structure of the data makes this problem a lot harder than it would be otherwise. — jonahe
– jonahe, Commented Sep 3, 2017 at 12:10
@jonahe I am using this static JSON at the moment. The JSON will be loaded with a link afterwards. — Datacrawler
– Datacrawler, Commented Sep 3, 2017 at 12:27
If you can get all the data as JSON then it's almost always easier to manipulate the raw data until it looks the way you want to, and THEN worry about how you would make a table from that. That way you separate the viewing logic (how things are presented) from the calculation / statistics / manipulation logic. I'm working on an example that I will post in an answer soon. — jonahe
– jonahe, Commented Sep 3, 2017 at 12:44

jonahe · Accepted Answer · 2017-09-03 15:10:05Z

1

Like I said in the comments I think a nicer approach is to transform the raw data to a more usable format, and then have separate logic to display the tables. Here I have an example of how to transform the data so that each row knows about the association between the header name and the data.

When the data is in the right format, it becomes easier to see how you would, for example, sum the values in one column (sum values associated with a specific header name). I've included an example of that as well.

You can also see how I divide a big problem like printing a table, in smaller steps (getting the table header, getting ONE table row). Then I combine these smaller functions to get the wanted behavior.

For data transformation it's very useful to get to know the Array methods, like .map, .forEach and .reduce. I have examples of each of them. Mozilla have good documentation of these methods (see above).

I hope this helps you see how you could continue breaking down the problem until you have all the functionality you wanted. Feel free to ask me if you have further questions!

EDIT: The integrated code here in SO is bit hard to work with, so I have a JSFiddle demo you could see here: https://jsfiddle.net/jonahe/jLnph69s/

EDIT 2: New JSFiddle demo Now with a printed example of sums:
https://jsfiddle.net/jonahe/ntmg68dp/ As you can see, it relies heavily on reusing the smaller functions from before.

var data = {"headers":["plat","chan","group","03","cat","05","06","07","08","09","10","11","12","13","14","15","16","17"],"rows":[["plat1","chan1","aaaa","xxxxx","cat1","yyyy",133,236,0,"656",0,956,37,36,12,67,1],
["plat1","chan1","aaaa","xxxxx","cat2","yyyy",178,867,0,"656",0,647,39,12,18,65,2],
["plat1","chan1","bbb","xxxxx","cat1","yyyy",378,867,0,"656",0,350,32,97,19,64,3],
["plat1","chan1","bbb","xxxxx","cat2","yyyy",137,167,0,"656",0,523,46,86,32,62,4],
["plat2","chan1","aaaa","xxxxx","cat1","yyyy",133,236,0,"656",0,956,37,36,12,67,1],
["plat2","chan1","aaaa","xxxxx","cat2","yyyy",178,867,0,"656",0,647,39,12,18,65,2],
["plat2","chan1","bbb","xxxxx","cat1","yyyy",378,867,0,"656",0,350,32,97,19,64,3],
["plat3","chan1","bbb","xxxxx","cat2","yyyy",137,167,0,"656",0,523,46,86,32,62,4]]};

// transform the data to get a clear connection between the
// heading and the corresponding data
const transformedData = data.rows.map((row) => {
	const emptyRowDataObject = {};
	return row.reduce((dataObjSoFar, rowData, rowDataIndex) => {
  	// for each of the values in the row, add a property
    // with the name of the corresponding header
    const correspondingHeader = data.headers[rowDataIndex];
  	dataObjSoFar[correspondingHeader] = rowData;
    return dataObjSoFar;
  }, emptyRowDataObject);
});

const headersOfInterest = ['plat',	'chan',	'03',	'05',	'06',	'07',	'08'];
printTable(headersOfInterest, transformedData);
console.log('sum of column 06 is: ' , getSumOfValuesInColumn('06', transformedData));
console.log('transformed data looks like', transformedData);

function printTable(headers, rowDataWithHeaders) {
	let tableHeader = createTableHeaders(headers);
  let tableRows = rowDataWithHeaders.map(row => createTableRow(headers, row));
  let table = `<table>${ tableHeader }<tbody>${ tableRows }</tbody></table>`;
  $("#one").html(table);
}

function createTableHeaders(headers) {
	let headersHTML = '<thead><tr>';
	headers.forEach(header => {
  	headersHTML += `<th>${ header }</th>`;
  });
  headersHTML += '</tr></thead>';
  return headersHTML;
}

function createTableRow(headers, dataRow) {
	let tr = '<tr>';
  // go through all the headers we are interested in
  // and get the corresponding value from this data row
  headers.forEach(header => {
  	tr += `<td>${ dataRow[header] }</td>`;
  });
  tr += '</tr>';
  return tr;
}

function getSumOfValuesInColumn(headerName, rowDataWithHeaders) {
	// this could be done with Array.reduce as well
  let sum = 0;
	for(let i = 0; i < rowDataWithHeaders.length; i++) {
  	sum += rowDataWithHeaders[i][headerName]
  }
  return sum;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="one"></div>

edited Sep 3, 2017 at 15:10

answered Sep 3, 2017 at 14:28

jonahe

5,0101 gold badge17 silver badges20 bronze badges

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

9 Comments

Datacrawler Over a year ago

Good start :) I getting 8 rows like in my example. How can I group them (summing the numbers fields) after altering your code? I got some error :P

jonahe Over a year ago

I edited the answer with a JSFiddle link. There you should be able to see a sum printed in the console. The functions in the code are made to be pretty general, so if you want a table only showing plat and chan, then you change the parameters (headersOfInterest) to the function. Same if you want to sum the values of a different field.

Datacrawler Over a year ago

My question was a bit generic. Too many things happening :P I agree that I have to manipulate the JSON before I load it into tables. I can use your code and I just need to find a clever way to add an in statement somewhere (e.g. if plat1, chan1, group1 should return one line and the SUM of the last column) : jsfiddle.net/radomer/2yfw7y1n/1

jonahe Over a year ago

Yeah, it's hard to do fully general solution. I'm not sure exactly which combinations you might need. But I edited the answer again with a new JSFiddle demo. Added the same new code to your fiddle example also. jsfiddle.net/jonahe/2yfw7y1n/3

Datacrawler Over a year ago

I will test it when I am back. I want to see the first table with one line instead of two (because the Plat, Chan, Cat have the same values in the 2 rows). And the Num should show the SUM. I will test it and come back. It might be tomorrow :P I will have to alter the code a bit I guess.

|

Collectives™ on Stack Overflow

Filtered JSON data in mulitple HTML tables with Javascript

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related