Dynamically populating google column charts

Question

I want to populate a dynamic column chart such that the rows as well the columns are dynamic.

Here is a sample json array that I want to transform: {"location":"Chain","item_category":"A","delay":"681"}, {"location":"Chennai ","item_category":"A","delay":"286"},{"location":"Bawal","item_category":"A","delay":"339"},{"location":"Haridwar","item_category":"A","delay":"1256"},{"location":"Ludhiana","item_category":"A","delay":"1048"},{"location":"Bawal","item_category":"B","delay":"1"}

There are 3 parameters namely location, item_category and delay where:

1. Location represents columns. (Varies dynamically)
2. Delay range should be on Y-axis. (Varies dynamically)
3. On x-axis they are mapped with items.

So far, I'm able to fetch all the required data but unable to create a graph out of it. I have referred to various libraries such as Google-visualization, Pchart, JPGraph and many more.

Each time I freeze on populating the graph dynamically.

Here is a sample code for populating Google Column Chart:

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['bar']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Item Category', 'Location 1', 'Location 2', 'Location 3,...'],
          ['item1', 1000, 400, 200,...delay value of location 'n'],
          ['item2', 1170, 460, 250,...],
          ['item3', 660, 1120, 300]...],
          ['item4', 1030, 540, 350]
          .
          . 
        ]);

//I want to populate this data variable using PHP

        var chart = new google.charts.Bar(document.getElementById('my_Chartid'));

        chart.draw(data, google.charts.Bar.convertOptions(options));
      }

These are the links I have referred. I want to populate this data variable in javascript dynamically.

Google column chart dashboard

https://developers.google.com/chart/interactive/docs/gallery/columnchart

Answer 1

first, need to convert json data to normal array

then use a data view to create a column for each category

then aggregate the data and draw the chart

google.charts.load('current', {
  callback: function () {
    var jsonData = [
      {"location":"Chain","item_category":"A","delay":"681"},
      {"location":"Chennai ","item_category":"A","delay":"286"},
      {"location":"Bawal","item_category":"A","delay":"339"},
      {"location":"Haridwar","item_category":"A","delay":"1256"},
      {"location":"Ludhiana","item_category":"A","delay":"1048"},
      {"location":"Bawal","item_category":"B","delay":"100"}
    ];

    // load chart data
    var chartData = [];
    jsonData.forEach(function (row, rowIndex) {
      // column headings
      var columns = Object.keys(row);
      if (rowIndex === 0) {
        chartData.push(columns);
      }

      // row values
      var chartRow = [];
      columns.forEach(function (column, colIndex) {
        var chartCell = row[column];
        if (colIndex > 1) {
          chartCell = parseFloat(chartCell);
        }
        chartRow.push(chartCell);
      });
      chartData.push(chartRow);
    });
    var data = google.visualization.arrayToDataTable(chartData);

    // create data view
    var view = new google.visualization.DataView(data);
    
    // init column arrays
    var aggColumns = [];
    var viewColumns = [0];

    // build view & agg column for each category
    data.getDistinctValues(1).forEach(function (category, index) {
      // add view column
      viewColumns.push({
        calc: function (dt, row) {
          if (dt.getValue(row, 1) === category) {
            return dt.getValue(row, 2);
          }
          return null;
        },
        label: category,
        type: 'number'
      });

      // add agg column
      aggColumns.push({
        aggregation: google.visualization.data.sum,
        column: index + 1,
        label: category,
        type: 'number'
      });
    });

    // set view columns
    view.setColumns(viewColumns);

    // agg view
    var group = google.visualization.data.group(
      view,
      [0],
      aggColumns
    );

    var chart = new google.charts.Bar(document.getElementById('chart_div'));

    // use group data to draw chart
    chart.draw(group);
  },
  packages:['bar']
});

<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="chart_div"></div>