Zak Shaky Zak Shaky - 14 days ago 9
Javascript Question

Automatically Combine Values using Google Charts

I currently have a dataset called test.csv with the columns:

location time


It consists of a number of records, which may be for the same location. ie. there may be two records where the location is Paris but the time is different and I wish to add them and then plot them as one.

I wish to combine the values of time for all where the value of location is the same when plotting to a bar chart using Google Chart. Currently I have written this, but am unsure how to continue:

<!DOCTYPE html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="HandheldFriendly" content="True">
<meta name="MobileOptimized" content="320">

<title>Google Graph and CSV</title>
<meta name="description" content="test">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="jquery.csv.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript"> // load the visualisation API
google.load('visualization', '1', { packages: ['corechart', 'controls'] });
</script>

<script type="text/javascript">
function drawVisualization() {
$.get("test.csv", function(csvString) {
var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
var data = new google.visualization.arrayToDataTable(arrayData);
var crt_ertdlyYY = new google.visualization.ChartWrapper({
chartType: 'BarChart',
containerId: 'crt_ertdlyYY',
dataTable: data,
options:{
title: 'Room Occupancy for',
titleTextStyle : {color: 'grey', fontSize: 16},
bar: {groupWidth: "95%"},
legend: { position: "none" },
width:400,
height:600,
}
});
crt_ertdlyYY.draw();
});
}
google.setOnLoadCallback(drawVisualization)
</script>

<div id="crt_ertdlyYY"></div>


This produces the following visualisation: Output of code

Answer

use the group() method to aggregate the total for each location

var aggData = google.visualization.data.group(
  data,
  [0],
  [{
    column: 1,
    aggregation: google.visualization.data.sum,
    type: 'number',
    label: 'Total'
  }]
);

then use the grouped data on the chart

var crt_ertdlyYY = new google.visualization.ChartWrapper({
   chartType: 'BarChart',
   containerId: 'crt_ertdlyYY',
   dataTable: aggData,  // <-- use agg data
   options:{
      title: 'Room Occupancy for',
      titleTextStyle : {color: 'grey', fontSize: 16},
      bar: {groupWidth: "95%"},
      legend: { position: "none" },
      width:400,
      height:600,
   }
});

EDIT

a modifier function can be used to combine locations with different cases

var aggData = google.visualization.data.group(
  data,
  [{
    column: 0,
    modifier: function (value) {
      return value.toUpperCase();
    },
    type: 'string',
    label: 'Location'
  }],
  [{
    column: 1,
    aggregation: google.visualization.data.sum,
    type: 'number',
    label: 'Total'
  }]
);