JSON Question
ColumnChart move month to x axis considering provided JSON
ORIGINAL POST:
I've created a fiddle to demonstrate how my data comes from JSON and renders in a ColumnChart.
https://jsfiddle.net/w4dokdt9/3/
My JSON comes over like this:
[{"lPlusScoreID":1,"jan":60.03,"feb":43.57,"mar":48.55},
{"lPlusScoreID":2, "jan":89.42,"feb":85.71,"mar":90.46},
{"lPlusScoreID":3,"jan":86.22,"feb":90.61,"mar":89.53}]
The column chart comes out like this:
My goal is month across the x-axis and product as column/bar.
How can I accomplish this? I prefer a Google Visualization chart, method, or configuration option.
Coding to transform the data would be OK if that is the only way, but I new to developing.
Thank you so much, as always!
EDITED POST:
I finally used the below code.
- Created with hard coded months and then filled it
dataArray_input
with data from original JSON. - Transposed using
dataArray_inputfunction intotransposeArray()
.dataArray_trans
function transposeArray(a) {
return Object.keys(a[0]).map(function (c) {
return a.map(function (r) {
return r[c];
});
}); }
- Append using
dataArray_transfunction to finalappendArray()which then is used by the Google APIdataArray_output
function appendArray(a, b) {
for (var i = 0; i < a.length; i++) {
b.push(a[i]);
} }
Below is the final code.
I would welcome some feedback to make sure I'm learning in the proper way and direction. New to this stuff.
Thank you also to @WhiteHat for answering so quickly, also with a good solution.
// Load the Visualization API and the corechart package.
google.charts.load('current', {
'packages': ['corechart', 'table', 'gauge', 'controls']
});
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(gChart0);
function gChart0() {
drawChartXYZ();
};
/*-----------------------------------------------------------------------------*/
// Remove Duplicates in singleDimensionalArray
//-----------------------------------------------------------------------------*/
function onlyUnique(value, index, self) {
return self.indexOf(value) === index;
}
/*-----------------------------------------------------------------------------*/
// Transpose Array
/* Source1: https://jsfiddle.net/w4dokdt9/6/
Source2: http://geniuscarrier.com/transpose-in-javascript/
Call: a = array to transpose
-------------------------------------------------------------------------------*/
function transposeArray(a) {
return Object.keys(a[0]).map(function (c) {
return a.map(function (r) {
return r[c];
});
});
}
/*-----------------------------------------------------------------------------*/
// Append Array
/* Source1: https://davidwalsh.name/combining-js-arrays
Call: a = source, b = destination "a onto b"
-------------------------------------------------------------------------------*/
function appendArray(a, b) {
for (var i = 0; i < a.length; i++) {
b.push(a[i]);
}
}
/*-----------------------------------------------------------------------------*/
// Google Function
//-----------------------------------------------------------------------------*/
function drawChartXYZ() {
//var urlString = '../mps/forecastVols' + urlParameter1 + urlParameter1_Value;
//var urlString_temp = 'https://jsonplaceholder.typicode.com/users';
var urlString_temp = 'https://httpbin.org/get'; //source: https://resttesttest.com/
$.ajax({
type: 'GET',
dataType: 'json',
contentType: "application/json",
//url: urlString,
url: urlString_temp,
success: function (result) {
//Manually loaded "result" with JSON that normally comes from "urlString".
result = [{"name":"Sensor","techName":"GS_SI","p1":305901.00,"p2":343653.00,"p3":414684.00,"p4":324323.00,"p5":366401.00,"p6":369596.00,"p7":273567.00,"p8":407767.00,"p9":396620.00,"p10":434000.00,"p11":392000.00,"p12":336000.00,"p13":420000.00,"p14":378000.00,"p15":434000.00,"p16":420000.00,"p17":420000.00,"p18":434000.00,"p19":336000.00,"p20":434000.00,"p21":406000.00,"p22":0.00,"p23":0.00,"p24":0.00,"p25":358884.00,"p26":335730.00,"p27":358884.00,"p28":347307.00,"p29":358884.00,"p30":347307.00,"p31":361409.00,"p32":361409.00,"p33":349751.00,"p34":361409.00,"p35":349751.00,"p36":361409.00,"p37":0.00,"p38":0.00,"p39":0.00,"p40":0.00,"p41":0.00,"p42":0.00,"p43":0.00,"p44":0.00,"p45":0.00,"p46":0.00,"p47":0.00,"p48":0.00,"id":2,"b1":null,"b2":null}];
//Declare Array Variables
var dataArray_ID = []; //ID List
var dataArray_input = []; //Input
var dataArary_trans = []; //Transpose
var dataArray_output = []; //Output
//Unique product ID's from JSON result
$.each(result, function (j, obj) {
dataArray_ID.push([
obj.id
]);
});
dataArray_ID.filter(onlyUnique);
//Loop for each ID: Input, Transpose, Output
for (var j = 0; j < dataArray_ID.length; ++j) {
dataArray_input = [];
dataArray_input.push([
'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
]);
//Input Array
$.each(result, function (i, obj) {
if (obj.id == dataArray_ID[j]) {
dataArray_input.push([
//"Line ID",
obj.id,
obj.id, obj.id, obj.id, obj.id, obj.id, obj.id,
obj.id, obj.id, obj.id, obj.id, obj.id
]);
dataArray_input.push([
//"Prod CY",
obj.p1, //JAN_CY
obj.p2, obj.p3, obj.p4, obj.p5, obj.p6, obj.p7,
obj.p8, obj.p9, obj.p10, obj.p11, obj.p12
]);
dataArray_input.push([
//"BP CY",
obj.p25, //JAN_CY_BP
obj.p26, obj.p27, obj.p28, obj.p29, obj.p30, obj.p31,
obj.p32, obj.p33, obj.p34, obj.p35, obj.p36
]);
dataArray_input.push([
//"Prod CY+1",
obj.p13, //JAN_CYPLUS1
obj.p14, obj.p15, obj.p16, obj.p17, obj.p18, obj.p19,
obj.p20, obj.p21, obj.p22, obj.p23, obj.p24
]);
dataArray_input.push([
//"BP CY+1",
obj.p37, //JAN_CYPLUS1_BP
obj.p38, obj.p39, obj.p40, obj.p41, obj.p42, obj.p43,
obj.p44, obj.p45, obj.p46, obj.p47, obj.p48
]);
} //END if
}); //END $.each(result, function (i, obj) {
}; //END for (var j = 0; j < dataArray_ID.length; ++j) {
//Transpose Array
dataArray_trans = transposeArray(dataArray_input);
//Append Array to Output
appendArray(dataArray_trans, dataArray_output);
//Create DataTable
var data = new google.visualization.DataTable();
//Add Columns
data.addColumn('string', 'Month');//0
data.addColumn('number', 'Line ID');//1
data.addColumn('number', 'Prod CY');//2
data.addColumn('number', 'BP CY');//3
data.addColumn('number', 'Prod CY+1');//4
data.addColumn('number', 'BP CY+1');//5
data.addRows(dataArray_output);
//Create Data View
var viewFilter = new google.visualization.DataView(data);
viewFilter.setColumns([0, 1, 2, 3, 4, 5]);
viewFilter.setRows(viewFilter.getFilteredRows([{ column: 1, value: 2 }]));
var viewTable = new google.visualization.DataView(viewFilter);
viewTable.setColumns([0, 2, 3, 4, 5]);
var viewChart1 = new google.visualization.DataView(viewFilter);
viewChart1.setColumns([0, 2, 3]);
//Options
var optionsTable = {
};
var optionsChart1 = {
title: 'Volume Development - CY',
vAxis: {
title: 'Volume',
minValue: '0'
},
series: {
0: { type: 'bars' }, //Prod CY
1: { type: 'line' }, //BP CY
}
};
//Instantiate and draw chart, passing in options.
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(viewTable, optionsTable);
var chart1 = new google.visualization.ComboChart(document.getElementById('div_chart1'));
chart1.draw(viewChart1, optionsChart1);
} //END success: function (result) {
}); //END $.ajax({
} //END function drawChart()
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="table_div"></div>
<hr />
<div id="div_chart1"></div>
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
with google charts, it's all in how the data is fed to the chart
there are data manipulation methods for group() and join()
but not any to pivot from columns to rows, etc...
for the requested chart, the data will need to be structured as follows...
['month', 'Product1', 'Product2']
['jan', 60.03, 89.42]
['feb', 43.57, 85.71]
see following working snippet...
the data table is created with a single column for month
a new column is added for each "lPlusScoreID"
data table method getFilteredRows is used to see if the month has already been added as row
if so, the row is updated
otherwise, a new row is added
google.charts.load('current', {
packages: ['corechart']
}).then(function () {
var jsonData = [
{"lPlusScoreID":1, "jan":60.03, "feb":43.57, "mar":48.55},
{"lPlusScoreID":2, "jan":89.42, "feb":85.71, "mar":90.46},
{"lPlusScoreID":3, "jan":86.22, "feb":90.61, "mar":89.53}
];
var data = new google.visualization.DataTable();
data.addColumn('string', 'Month');
// build data
jsonData.forEach(function (row) {
var colIndex;
var rowIndex;
// process each key in the row object
Object.keys(row).forEach(function (key) {
// add column for ID
if (key === 'lPlusScoreID') {
colIndex = data.addColumn('number', 'Product' + row.lPlusScoreID.toString());
return;
}
// find / add row for month
rowIndex = getRowIndex(key);
if (rowIndex === null) {
rowIndex = data.addRow();
data.setValue(rowIndex, 0, key);
}
data.setValue(rowIndex, colIndex, row[key]);
});
});
function getRowIndex(rowMonth) {
var rowIndex = data.getFilteredRows([{
column: 0,
value: rowMonth
}]);
if (rowIndex.length > 0) {
return rowIndex[0];
}
return null;
}
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data);
});<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download