Google Charts API

Transpose Data

Introduction

Sometimes a chart looks a bit odd or doesn't convey all the information it could. Sometimes this is due to the way the original data is presented to make the chart.

Rows and Columns

The following two charts were created from the same data. For the first chart the data is arranged vertically. It consists of 3 columns, the semester, then the number of male then female students. In the second chart, the data is arranged horizontally with 3 rows, the semester, then the number of female then male students.

The data is the same but because of the way it is presented very different charts are produced.

Original data arranged vertically:

Original data arranged horizontally:

The above charts were drawn using the simple drawChart() method, and the code used is:


    <script type="text/javascript">
	var hvChart = (function() {
    google.charts.load(['current']);
    var myString = []; 
	myString[0] = '{"containerId": "verticalColumn_div",' + '"dataSourceUrl": "https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1",' + '"query":"SELECT B,C,D ORDER BY A",' + '"chartType": "ColumnChart"' + '}';
    myString[1] = '{"containerId": "horizontalColumn_div",' + '"dataSourceUrl": "https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=707970259&headers=1",' + '"query":"SELECT A,B,C,D,E,F,G,H,I,J,K,L ORDER BY A",' + '"chartType": "ColumnChart"' + '}';
	
	var strLength = myString.length;
	for (i=0; i < strLength; i++){
		processChart(i);
	}
		
    function processChart(i) { 
  		setTimeout(function() { 
  			function drawVisualization() {
				var newString = myString[i]
  				google.visualization.drawChart(newString);
  			}
  			google.charts.setOnLoadCallback(drawVisualization); 
  		}, 	0); 
	}
		
	})();
	</script>

If the data comes from a spreadsheet then both Microsoft Excel and Google Sheets have a transpose function to rotate the rows and columns.

Another method is to use the Pivot clause in the Query Language if that is being used.

Transpose Programmatically

Suppose you want to do it programmatically. This is not part of the API but has been asked about several times; Google Charts dataview manipulation, Switch axis in google.visualization.Query() object and Inverting rows and columns on Google Area Chart

Bhuman Soni in Transpose of a Google Visualization data table object, Google Visualization library and transpose a DataTable, and Transpose of a Google Visualization data table object has provided a solution to this. Asgall created one in Switch axis in google.visualization.Query() object.

As far as I know, DrawChart() does not use variables to hold the data table or any views created, bur both chartdraw() and ChartWrapper do.

In the following example the chartdraw() method is used. The data comes from a single Google Sheet, is drawn, rotated and then drawn again.

Original Data

Rotated Data

The code for the above example is:


    <script type="text/javascript">
	var rotateChart = (function() {
    google.charts.load('current', {packages: ['corechart']});
    google.charts.setOnLoadCallback(drawChart);

    function drawChart() {
      var queryString = encodeURIComponent('SELECT A,B,C,D,E,F,G,H,I,J,K,L ORDER BY A');

      var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=707970259&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var originalData = response.getDataTable();
      var originalChart = new google.visualization.ColumnChart(document.getElementById('originalData_div'));
      originalChart.draw(originalData);
		
	  //Rotate originalData to rotatedData
	  var rotatedData = new google.visualization.DataTable();
	  rotatedData.addColumn('string', 'data label'); // change this label to whatever is appropriate for you
      for (var i = 0; i < originalData.getNumberOfRows(); i++) {
         rotatedData.addColumn('number', originalData.getValue(i, 0));
      }
	  for (var i = 1; i < originalData.getNumberOfColumns(); i++) {
         var row = [originalData.getColumnLabel(i)];
      for (var j = 0; j < originalData.getNumberOfRows(); j++) {
         row.push(originalData.getValue(j, i));
      }
      rotatedData.addRow(row);
		  
	  var rotatedChart = new google.visualization.ColumnChart(document.getElementById('rotatedData_div'));
      rotatedChart.draw(rotatedData);
	  }
    }
	})();
	</script>

In Switch axis in google.visualization.Query() object a user pointed out a problem when transposing data like this. The code only deals with the values of the cells (v) but not the formatting (f). The simple solution is to change the line

row.push(originalData.getValue(j, i));

row.push({v: originalData.getValue(j, i), f: originalData.getFormattedValue(j, i)});

Of course it's always possible to setp back through the created table and add new formatting.

Let the User Choose!

Some data drawn as some chart types makes no sense at all, but sometimes it's useful to allow the user to choose to rotate the data. The following example uses a simple checkbox to toggle the rotation on or off.

In the above example there are two data tables, originalData and rotatedData. The state of the checkbox determines which one is used and an event handler determines when the state of that is changed.

One gotcha is that a new visualization must be created whenever the charts are redrawn or they do not get redrawn properly.

Toggle the data rotation

The HTML for the checkbox is:


	<input type="checkbox" id="toggleRotate">
    <label for="toggleRotate">Toggle the data rotation</label>