Google Charts Data Grouping for column chart

Question

I am trying to achieve this kind of chart (column chart)

but I can't seem to group my data correctly, Please help. Here's my sample json data.

[
   [
     'male',
     '05-09'
   ],
   [
     'female',
     '05-09'
   ],
   [
     'male',
     '05-09'
   ],
]

and I am creating my datatable using

       function createDataTable(rawData, $tableHeader) {
            try {
                return google.visualization.arrayToDataTable([
                    $tableHeader,
                    ...rawData
                ]);
            } catch (e) {
                return null;
            }
        }

Then here's how I am grouping it currently

      function groupData(columnIndex) {
            return google.visualization.data.group(data, [columnIndex], [{
                'column': columnIndex,
                'aggregation': google.visualization.data.count,
                'type': 'number'
            }])
        }

Here's how I draw the chart

function drawBarChart() {
            let ageData = groupData(1);

            let options = {
                title: '',
                width: '810',
                height: '500',
                chartArea: {width: '50%'},
                colors: ['#FF7300', '#383A38', '#FFC799'],
                hAxis: {
                    title: 'Age Groups',
                    minValue: 0
                },
                vAxis: {
                    title: 'Hits'
                },
                orientation: 'horizontal',
                legend: { position: 'none' }
            };

            let chart = new google.visualization.BarChart(document.getElementById('age-graph'));
            chart.draw(ageData, options);
        }

Thank you so much!

WhiteHat WhiteHat · Accepted Answer · 2017-10-30T11:56:28

first, when using arrayToDataTable,
need to set second argument to --> true,
if the first row is data and not column headings...

return google.visualization.arrayToDataTable([
  [
    'male',
    '05-09'
  ],
  [
    'female',
    '05-09'
  ],
  [
    'male',
    '05-09'
  ],
], true);  // first row is data = true

next, you will actually need to group the data twice,
once to get the count of the distinct values,
and again to sum by each gender

to get count of distinct values,
group on both columns and count the second

return google.visualization.data.group(
  data,
  [1, 0],  // group on both columns, age group first
  [{
    column: 1,
    aggregation: google.visualization.data.count,
    type: 'number'
  }]
);

then create a data view with columns for each gender,
and then group again to sum each column

see following working snippet...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  var data = google.visualization.arrayToDataTable([
    [
      'male',
      '05-09'
    ],
    [
      'female',
      '05-09'
    ],
    [
      'male',
      '05-09'
    ],
    [
      'male',
      '10-14'
    ],
    [
      'female',
      '10-14'
    ],
    [
      'male',
      '10-14'
    ],
  ], true);  // first row is data = true

  var groupData = google.visualization.data.group(
    data,
    [1, 0],  // group on both columns, age group first
    [{
      column: 1,
      aggregation: google.visualization.data.count,
      type: 'number'
    }]
  );

  // create data view
  var view = new google.visualization.DataView(groupData);

  // init column arrays
  // use age group as first column
  var viewColumns = [0];
  var aggColumns = [];

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

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

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

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

  // draw chart
  var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
  chart.draw(aggData);
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

Google Charts Data Grouping for column chart

1 Answers