ColumnChart move month to x axis considering provided JSON

Question

ORIGINAL POST:

I've created a fiddle to demonstrate how my data comes from JSON and renders in a ColumnChart.

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 dataArray_input with hard coded months and then filled it with data from original JSON.

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];
    });
});  }

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()

WhiteHat · Accepted Answer

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);
});

ColumnChart move month to x axis considering provided JSON

Answers (1)

Related Questions