How to plot a line chart given a data table using google chart?

Question

If I have the following definition of a data table in google charts

var data = new google.visualization.DataTable();
data.addColumn('string', 'Month');
data.addColumn('string','Team');
data.addColumn('number', 'Total Score');

And the data in that table looks like:

Dec,Team A,68
Dec,Team B,75
Dec,Team C,23
Dec,Team D,158
Dec,Team E,27
Jan,Team A,68
Jan,Team E,27

How can I can produce a line chart as follows using google charts:

I have tried using

var lineChart = new google.charts.Line(document.getElementById('LineChart'));
lineChart.draw(data);

But that produces a single line with repeated months along the h-axis

Thank you in advance

WhiteHat WhiteHat · Accepted Answer · 2020-01-03T22:34:20

each team needs to have its own series, or column in the data table,
as follows...

Month, Team A, Team B, Team C, Team D, Team E
Dec, 68, 75, 23, 158, 27

we can use the group function and a data view to transform the original data table.

first, group the data by team.

// group data table
var groupData = google.visualization.data.group(
  data,
  [0, 1],
  [{
    column: 2,
    aggregation: google.visualization.data.sum,
    type: 'number'
  }]
);

then we can use a data view to create the separate columns / series

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

// sum column array
var aggColumns = [];

// use month as first view column
var viewColumns = [0];

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

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

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

then we can aggregate again, to collapse all the rows...

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

see following working snippet...

google.charts.load('current', {
  packages: ['line']
}).then(function () {
  // create data table
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Month');
  data.addColumn('string','Team');
  data.addColumn('number', 'Total Score');
  data.addRows([
    ['Dec', 'Team A', 68],
    ['Dec', 'Team B', 75],
    ['Dec', 'Team C', 23],
    ['Dec', 'Team D', 158],
    ['Dec', 'Team E', 27],
    ['Jan', 'Team A', 68],
    ['Jan', 'Team E', 27]
  ]);

  // group data table
  var groupData = google.visualization.data.group(
    data,
    [0, 1],
    [{
      column: 2,
      aggregation: google.visualization.data.sum,
      type: 'number'
    }]
  );

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

  // sum column array
  var aggColumns = [];

  // use month as first view column
  var viewColumns = [0];

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

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

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

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

  // draw chart
  var lineChart = new google.charts.Line(document.getElementById('LineChart'));
  lineChart.draw(aggData);
});

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

How to plot a line chart given a data table using google chart?

1 Answers