This page was written because José asked in the in the API user-supported group how to do calculations on an entire column.
For this page, several types of calculations that will be discussed. Row calculations, that is taking a row of data and putting the the result of a calculation on values in that row into a new column. There are several methods I can think of for doing this, specific to this type of calculation is to use the built-in calc function
Then there are calculations to be done on an entire column such as sum, average, mode, median, etc. There are several methods I can think of for doing this, specific to this type of calculation is to use the built-in aggregator function. To use this you must have a column which contains the key to group the data by, even if the key is the same for all data rows to be able to include them all, and that sometimes has to be added to the datatable while or after the data is being imported
Common to both row and column calculations is a second method and that is to step through the datatable using a FOR loop and doing the calculation yourself using JavaScript. A common third method is to alter the data before it gets imported into the API using whatever language you are most comfortable with, PHP, SQL, and so on.
Dataviews are read only and so there are no set data methods available to them, datatables have various methods to both read and write to them. So, this method can be used get get data from a datatable or dataview, but only write it to a datatable.
Suppose you have a datatable named dataTable and just need the total of a column of numbers to use outside the API and it does not need to be written back to the datatable. In this example, the numbers to total are in column 3.
var totalRows = dataTable.getNumberOfRows();
var dataCount = 0;
for (i = 0; i < totalRows; i++) {
var dataCount += dataTable.getValue(i, 3);
}
The variables dataRows and dataCount now be used in further calculations, written to the page and so on.
For the pruposes of this page the data comes from a Google Sheet which shows the sex of students in a small college for various semesters. There is documentation for using Google Sheets and column charts in the API.
The code to draw the above chart is:
<script type="text/javaScript">
var students = (function() {
// create namespace global variable to hold the data
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawCharts);
function drawCharts() {
var queryString = encodeURIComponent('SELECT B,C,D ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var studentData = response.getDataTable();
var studentChart = new google.visualization.ColumnChart(document.getElementById('studentData_div'));
studentChart.draw(studentData);
}
})();
</script>
What the calc function can be used for is to add a calculated column while a is dataview is being created from a datatable using setColumns. Suppose just the total amount of students for each semester are needed instead of being split into sex. As the data for both is in the original datatable, the dataview just needs the sum of those two columns and this can be calculated using:
<script type="text/javaScript">
var allStudentView = new google.visualization.DataView(studentData);
allStudentView.setColumns([0,{calc:allStudents, type:'number', label:'All Students'}]);
function allStudents(studentData, rowNum){
return studentData.getValue(rowNum, 1) + studentData.getValue(rowNum, 2);
}
</script>
The return from the function can be anything, not just a calculation from the original datatable. It could be a variable calculated elsewhere, a number, or a piece of text. The calc function help is in the API documentation - see under setColumns(columnIndexes) in Dataview Contructors
The new dataview, allStudentView, can be drawn instead of the original datatable to produce:
The code to draw the above chart is:
<script type="text/javaScript">
var students = (function() {
// create namespace global variable to hold the data
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawCharts);
function drawCharts() {
var queryString = encodeURIComponent('SELECT B,C,D ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var studentData = response.getDataTable();
var allStudentView = new google.visualization.DataView(studentData);
allStudentView.setColumns([0,{calc:allStudents, type:'number', label:'All Students'}]);
function allStudents(studentData, rowNum){
return studentData.getValue(rowNum, 1) + studentData.getValue(rowNum, 2);
}
var allStudentsChart = new google.visualization.ColumnChart(document.getElementById('allStudents_div'));
allStudentsChart.draw(allStudentView);
}
})();
</script>
A trendline cannot be used on this chart because the major axis of the chart is discrete and not continuous. The API recognizes the columns types with a number, date, datetime and timeofday as continuous. As this data is using semesters, which the API does not recognize as a continuous data type, it is treated as discrete text.
Grouping and Aggregation functions work on groups of data. The data the rows are grouped on needs to be included in the data. In order to group all the rows the grouping data must be the same for all of them. The current data does not have a column to group the data by. In order to do this the following must be completed:
After doing all of the above, the following chart is produced:
The code to draw the above chart is:
<script type="text/javaScript">
var students = (function() {
// create namespace global variable to hold the data
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawCharts);
function drawCharts() {
var queryString = encodeURIComponent('SELECT B,C,D ORDER BY A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1786091632&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var studentData = response.getDataTable();
var allStudentView = new google.visualization.DataView(studentData);
allStudentView.setColumns([0,{calc:allStudents, type:'number', label:'All Students'}]);
function allStudents(studentData, rowNum){
return studentData.getValue(rowNum, 1) + studentData.getValue(rowNum, 2);
}
//Create the min, max and average using the aggregation function in a new view
var studentAggView = google.visualization.data.group(
allStudentView,
// The first column is what to group on, in this case an empty string
[{column: 0, type: 'string', modifier: function () {return '';}}],
[
{column: 1, type: 'number', aggregation: google.visualization.data.max},
{column: 1, type: 'number', aggregation: google.visualization.data.avg},
{column: 1, type: 'number', aggregation: google.visualization.data.min},
]
);
// Put the calculated aggregates into variables
var studendMax = studentAggView.getValue(0, 1);
var studentAve = studentAggView.getValue(0, 2);
var studentMin = studentAggView.getValue(0, 3);
// Add the aggregate variables using the calc function
// The student totals have to be recalculated because the views are just a reference to the tables
allStudentView.setColumns([0, {
label:'Student #',
type:'number',
calc: function (dt, row) {
return dt.getValue(row,1) + dt.getValue(row,2);
}
},{
label: 'Max Value',
type: 'number',
calc: function (dt, row) {
return studendMax;
}
}, {
label: 'Avg Value',
type: 'number',
calc: function (dt, row) {
return studentAve;
}
}, {
label: 'Min Value',
type: 'number',
calc: function (dt, row) {
return studentMin;
}
}]);
//Create the various options for the data to be drawn
var options = {
series: {
0:{type: 'column'},
1:{type: 'line'},
2:{type: 'line'},
3:{type: 'line'}
}
};
var aggChart = new google.visualization.ColumnChart(document.getElementById('aggStudents_div'));
aggChart.draw(allStudentView,options);
}
})();
</script>