Since i spend a lot of time at my day job working with the Google Visualization tools for the charting needs of my work, i figured it is inevitable that i will be requested to draw a chart with transposed data.

ok…let us backtrack a little. So to draw a chart using the Google Visualization tools, the first step is to prepare a DataTable, which is a data structure provided by Google. The DataTable as the name suggests is simple table of rows and columns, hence it can be thought of a 2D matrix. Obviously now once you start to perceive your data as a matrix, what you can do with a matrix is only limited by the type of data stored in it.

In this case the matrix operation of interest to me, was the transpose. Transpose is one of the simpler concepts of linear algebra, where by in a given matrix , rows become columns and columns become rows.

Ok so now onto business? FYI, this is a quick and dirty solution and is in no way optimal. Since my need was to transpose a table that has the first row as a string type and the rest numbers, this solution was written only to handle that.

For a given dataTable object, the function transpose(dataTable) would start as follows. first  extract the row values of the data table.

var rows = [];//the row tip becomes the column header and the rest become
for (var rowIdx=0; rowIdx < dataTable.getNumberOfRows(); rowIdx++) {
    var rowData = [];
    for( var colIdx = 0; colIdx < dataTable.getNumberOfColumns(); colIdx++) {
        rowData.push(dataTable.getValue(rowIdx, colIdx));
    }
    rows.push( rowData);
}

Next , create a new dataTable object, i.e. newTB , add the rows to it and then populate it with the values in its first column.

var newTB = new google.visualization.DataTable();
//in this case the first column is the same across both the tables
newTB.addColumn('string', dataTable.getColumnLabel(0));
newTB.addRows(dataTable.getNumberOfColumns()-1);
var colIdx = 1;
for(var idx=0; idx < (dataTable.getNumberOfColumns() -1);idx++) {
    var colLabel = dataTable.getColumnLabel(colIdx);
    newTB.setValue(idx, 0, colLabel);
    colIdx++;
}

Now that the rest of the table is ready to be populated, let us get the values from the rows array

for (var i=0; i< rows.length; i++) {
    var rowData = rows[i];
    console.log(rowData[0]);
    //assuming the first one is always a header
    newTB.addColumn('number',rowData[0]); 
    var localRowIdx = 0;
    for(var j=1; j< rowData.length; j++) {
        newTB.setValue(localRowIdx, (i+1), rowData[j]);
        localRowIdx++;
    }
}

There you go, you have it, a transposed version of the original dataTable. Obviously one of the first improvements to the code above would be to remove the part where we extract the data into rows array. We really dont need it anyway, we already have all the data stored in the table.

Anyway once again, a working example and a visualization of the transpose can be found here

 

As usual, if you find any of my posts useful support us by  buying or even trying one of our products and leave us a review on the app store.

‎My Day To-Do - Smart Task List
‎My Day To-Do - Smart Task List
‎My Day To-Do Lite - Task list
‎My Day To-Do Lite - Task list
‎Snap! I was there
‎Snap! I was there
Developer: Bhuman Soni
Price: $3.99

Leave a Reply

Your email address will not be published. Required fields are marked *