cmill cmill - 1 year ago 132
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:
enter image description here

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.


  1. Created
    dataArray_input
    with hard coded months and then filled it
    with data from original JSON.

  2. Transposed
    dataArray_input
    using
    transposeArray()
    function into
    dataArray_trans
    .

    function transposeArray(a) {
    return Object.keys(a[0]).map(function (c) {
    return a.map(function (r) {
    return r[c];
    });
    }); }

  3. Append
    dataArray_trans
    using
    appendArray()
    function to final
    dataArray_output
    which then is used by the Google API

    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>




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