Instantly share code, notes, and snippets.

Embed
What would you like to do?
Self-referencing table to Hierarchical JSON converter
code parent amount
ROOT
A1 ROOT
A2 ROOT
A1.1 A1
A1.2 A1 89
A2.1 A2 43
A2.2 A2
A1.1.1 A1.1 32
A2.2.1 A2.2 12
A2.2.2 A2.2 45
<!DOCTYPE html>
<html><head>
<title>Self-referencing table to Hierarchical JSON converter</title>
<meta charset="utf-8">
<link rel="stylesheet" href="lib/bootstrap.min.css" orig="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<style>
label { font-weight: normal; }
</style>
</head><body>
<header class="text-center">
<h1>Self-referencing table to Hierarchical JSON converter</h1>
<h2>
<div class="col-sm-4"><div class="well well-sm"><i>Convert your data from this...<br><img src="http://i.imgur.com/YCcOcnh.jpg"></div></div>
<div class="col-sm-4"><div class="well well-sm"><img src="http://i.imgur.com/UtWMKpZ.jpg"><br>...into this </div></div>
<div class="col-sm-4"><div class="well well-sm">>> so you can do things like this with it !</i><br><img src="http://i.imgur.com/2V401B2m.jpg"></div></div>
</h2>
</header>
<div class="container">
<div class="row">
<div class="col-sm-4">
<h3>Main</h3>
CSV File: <input id="url" title="URL path to your CSV file" placeholder="click below for example">
<br>
<small><a onclick="$('#url').val('data/sample-parent-child.csv')">sample</a> |
<a onclick="$('#url').val('data/punebudget2016-17simplified.csv')">pune.2016-17.draft.budget</a></small>
<br>
startKey: <input id="startKey" placeholder="default: ROOT" title="where do you want to start from. You can specify a unique id other than the topmost root if you want to only do a sub-tree"><br>
maxDepth: <input id="maxDepth" value="3" title="This is to limit the size and complexity of your output JSON. Cutoff after which the details of further sub-items will not be printed, but the quantitative values will still be added up. For example, 3 means your JSON will only be upto 3 levels deep, even though the CSV data has items deeper. Thedeeper items' values will be added up to their parent nodes. Set it blank or 0 to cover all levels." placeholder="default: no limits">
</div>
<div class="col-sm-4">
<h3>Columns</h3>
keyField: <input id="keyField" title="the unique id column" placeholder="deafult: code"><br>
parentField: <input id="parentField" title="where the parent item's unique id is written" placeholder="default: parent"><br>
amountField: <input id="amountField" title="column having the main quantitative value that is to be aggregated" placeholder="default: amount"><br>
nameField: <input id="nameField" title="which column has the main title of each item. Can be same as keyField, or another column having a longer title for example. In output, this value will be stored in 'name' variable" placeholder="default: code">
</div>
<div class="col-sm-4">
<h3>Other</h3>
amountVar: <input id="amountVar" value="value" title="output variable name for the calculated quantity. Some visualization scripts need 'value', some need 'size'" placeholder="default: value"><br>
<input type="checkbox" id="printAllColumns" checked><label for="printAllColumns">&nbsp;&nbsp;Print all columns</label><br>
<input type="checkbox" id="extraMeta"><label for="extraMeta">&nbsp;&nbsp;Print metadata</label>
</div>
</div>
<br>
<center>
<a type=button class="btn btn-info" onclick="CSVpreview()">Preview CSV</a>
<span style="display:inline-block; width:5%;"></span>
<input type=button class="btn btn-primary btn-lg" value="Create Hierarchical JSON" onclick="buttonlaunch()">
<span style="display:inline-block; width:5%;"></span>
<input type=button class="btn btn-danger btn-sm" value="clear" onclick="$('#destination').val('')">
<br>
<small>Warning: script may take time for large datasets. If browser shows a stop/continue prompt, please press Continue till the work is done.</small><br>
<textarea id="destination" rows="15" style="width:80%" placeholder="output appears here"></textarea>
</center>
<br>
<!-- ########## FOOTER ############ -->
<div class="row">
<div class="col-sm-4">
<p>Tip: verify and format your JSON code here: <a href="http://jsonformatter.org/">jsonformatter.org</a></p>
<p>Warning: If you choose the "Print all columns" option, those columns that have the same title as 'name', or the amountVar(default: 'value'), ( or 'level' or 'subitems' if you have chosen to print metadata )...<br>
will be <i>overwritten</i>. So try to name your columns differently</p>
</div>
<div class="col-sm-4">
<div class="well">
<a href="https://gist.github.com/answerquest/e669903672396a6394d7">Get the Code on Github</a><br>
See some outputs of this wizard in action:<br>
<a href="http://sahbhag.in/files/pune-budget-viz-1.html">Pune Draft 2016-17 Budget Visualized</a>
</div>
<hr>
<p>Limitations : this aggregates only one specified column of quantitative values. What if we have data having multiple sets of values, like yearly amounts? How to aggregate quanities in multiple columns? If you can solve that, let's co-create! Send me an email!</p>
</div>
<div class="col-sm-4">
<p><a rel="license" href="http://creativecommons.org/licenses/by-sa/3.0/deed.en_US"><img alt="Creative Commons License" style="border-width:0" src="http://i.creativecommons.org/l/by-sa/3.0/88x31.png" /></a></p>
<p>by Nikhil VJ, Pune, India.<br>
Created during a project with <a href="http://ceeindia.org">Centre for Environment Education, Pune</a><br>
Using <a href="http://papaparse.com/">Papa Parse</a>, <a href="https://jquery.com/">jQuery</a>, <a href="http://www.w3schools.com/bootstrap">Bootstrap</a> and a ton of snippets from <a href="http://stackoverflow.com">Stack Overflow</a> network.<br>
Send feedback on nikhil.js [at] gmail [dot] com.</p>
</div>
</div>
<hr>
</div>
<script src="lib/jquery-2.1.3.min.js" orig="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script src="lib/papaparse.min.js"></script><!-- from http://papaparse.com -->
<script src="lib/selfrefCSV_2_hierarchJSON.js"></script><!-- from -->
<script>
function buttonlaunch() {
selfrefCSV_2_hierarchJSON( $('#url').val(), {
destination: 'destination',
keyField: $('#keyField').val(),
parentField: $('#parentField').val(),
amountField: $('#amountField').val(),
nameField: $('#nameField').val(),
amountVar: $('#amountVar').val(),
startKey: $('#startKey').val(),
maxDepth: parseInt($('#maxDepth').val()),
printAllColumns: $("#printAllColumns").is(":checked"),
extraMeta: $("#extraMeta").is(":checked")
});
}
function CSVpreview() {
if ( ! $('#url').val() ) {
console.log("Where's the CSV data file??");
destination.value = "Where's the CSV data file??";
destination.focus();
return;
}
Papa.parse($('#url').val(), {
download: true,
header: true,
dynamicTyping: true,
error: function(results) {
console.log('Error: Invalid CSV file : "'+ $('#url').val() +'". Give me something real, please.');
$('#destination').val('Error: Invalid CSV file : "'+ $('#url').val() +'". Give me something real, please.');
$('#destination').focus();
return;
},
complete: function(results) {
var csv = Papa.unparse(results.data.slice(0,10));
var columns = Object.keys(results.data[0]).length;
var rows = results.data.length ;
$('#destination').val('File: ' + $('#url').val() + '\n' + 'Number of columns: ' + columns + '\nNumber of rows: ' + rows + '\nShowing first 10 lines:\n##############\n' + csv);
}
});
}
</script></body></html>
function selfrefCSV_2_hierarchJSON(csvFile, params) {
/*
Requires: papa.parse, jquery
sample usage:
selfrefCSV_2_hierarchJSON( 'file.csv', {
destination: 'destination', //id of the textarea object that you want to populate with the json output
keyField: 'code',
parentField: 'parent',
amountField: 'amount',
nameField: 'code',
amountVar: 'value',
startKey: 'ROOT',
maxDepth: 3,
printAllColumns: true,
extraMeta: true
});
*/
//defaults in case they're empty
var keyField = params.keyField? params.keyField : 'code';
var parentField = params.parentField? params.parentField : 'parent';
var amount = params.amountField? params.amountField : 'amount';
var amountVar = params.amountVar? params.amountVar : 'value';
var nameField = params.nameField? params.nameField : 'code';
var startKey = params.startKey? params.startKey : 'ROOT';
var maxDepth = params.maxDepth? params.maxDepth : Infinity;
var printAllColumns = params.printAllColumns == false ? false : true;
var extraMeta = params.extraMeta == false ? false : true;
var alt_dest = document.getElementById('destination') ? document.getElementById('destination') : 0
var destination = params.destination ? ( document.getElementById(params.destination) ? document.getElementById(params.destination) : alt_dest) : alt_dest ;
if(! destination) {
//If you don't find a valid textarea, make a new one ;) from http://www.w3schools.com/js/js_htmldom_nodes.asp and http://stackoverflow.com/a/9423014/4355695
var outputWindow = document.createElement("textarea");
outputWindow.setAttribute("id", "destination");
outputWindow.setAttribute("style", "width:80%");
outputWindow.setAttribute("rows", "15");
document.body.appendChild(outputWindow);
destination = document.getElementById('destination');
}
if (!csvFile) {
console.log("Where's the CSV data file??");
destination.value = "Where's the CSV data file??";
destination.focus();
return;
} else
destination.value = 'loading, please wait...';
Papa.parse(csvFile, {
download: true,
header: true,
dynamicTyping: true,
error: function(results) {
console.log('Error: Invalid CSV file : "'+csvFile+'". Give me something real, please.');
destination.value = 'Error: Invalid CSV file : "'+csvFile+'". Give me something real, please.';
destination.focus();
return;
},
complete: function(results) {
destination.value = '';
var level = 0;
var finalOutput = ''; // this will collect and hold the created JSON
function csvIterator(key, notlast) {
var sum=0, childrenCount=0;
// filtering this particular row
var thisrow = results.data.filter( function(data){return data[keyField] == key;} );
//does it have any kids?
var children = results.data.filter( function(data){return data[parentField] == key;} );
var numkids = children.length;
if (! thisrow.length) {
console.log('No entry found for code ' + key);
return 0;
} else if (thisrow.length > 1) {
console.log('Warning: Multiple entries found for code ' + key + ':\n' + JSON.stringify(thisrow) + '\nTaking first one only.');
}
var output = '{ ';
//### PRINT ALL COLUMNS ######
// warning: what happens if any of the columns are titled name, level, subitems or the amountVar(value) ??
// >> checked.. in http://jsonformatter.org/, it said valid json, and upon beautifying, it deleted the earlier instance and kept only the latest one. So for safety it might be better to keep the print-all at top and the metadata below!
if(printAllColumns) {
for ( var i in thisrow[0]) {
output += '"'+i+'": "'+thisrow[0][i] + '", ';
}
}
//###### WRITING THE METADATA #########
output += '"name": "' + thisrow[0][nameField] + '"'
if(extraMeta) output += ', "level": "' + level + '", "subitems": ' + numkids;
if(numkids) if(level <= maxDepth-1) output += ', "children": [';
if(level <= maxDepth) finalOutput += output;
level++;
for ( var n in children ) {
holder = parseFloat( csvIterator(children[n][keyField], (numkids - n - 1)) ); // recursive : a function called from within the same function...
var thisamount = parseFloat(children[n][amount]) ? parseFloat(children[n][amount]) : 0;
sum += parseFloat( holder ? holder : thisamount );
}
level--;
if(numkids) {
if(level <= maxDepth-1) finalOutput += ']';
if(level <= maxDepth) finalOutput += ', "' + amountVar + '": ' + sum ;
} else {
var publishedamount = parseFloat(thisrow[0][amount])?parseFloat(thisrow[0][amount]):0 ;
if(level <= maxDepth) finalOutput += ', "' + amountVar + '": ' + publishedamount + ' ';
}
if(level <= maxDepth) finalOutput += '}';
if(notlast) if(level <= maxDepth) finalOutput += ',';
return sum;
} // csvIterator function over
if(! csvIterator(startKey,0) )
destination.value = 'Something is wrong. Either the startKey, "'+startKey+'" is not being found in the given file under "' + keyField + '" column, or the numbers under (column "'+ amount + '") are invalid or adding up to zero. Please check and set parameters properly.';
else destination.value = JSON.stringify( JSON.parse( finalOutput ), null, 2); //formatting the JSON data, from http://stackoverflow.com/a/3515761/4355695
destination.focus();
} // oncomplete function over
}); // papa parse function over
} // selfrefCSV_2_hierarchJSON function over
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment