Google Visualization Changing Column Data Type

Question

Currently I am pulling data from a spreadsheet, however some of the columns are listed as strings and some are listed as numbers. Is there a way after I pull all the data to convert the data type of the column to all numbers?

Answer 1

You cannot change the data type of a column once it has been created. There are two ways around this problem: either add a new column to the DataTable of the appropriate type, fill it with data, and delete the old column; or use a DataView to convert the strings to numbers on the fly.

Here's how you would add a new column:

// assumes you want to convert column 0 of DataTable "data" to type "number"
// insert a new column at the desired index; this bumps all columns at or after the chosen index up by 1 (so column 0 becomes column 1, 1 becomes 2, etc)
data.insertColumn(0, 'number', data.getColumnLabel(0));
// copy values from column 1 (old column 0) to column 0, converted to numbers
for (var i = 0; i < data.getNumberOfRows(); i++) {
    var val = data.getValue(i, 1);
    if (val != '' && val != null) {
        data.setValue(i, 0, new Number(val).valueOf());
    }
}
// remove column 1 (the old column 0)
data.removeColumn(1);

And here's an example of using a DataView:

// convert the first column to type "number"
var columns = [{
    type: 'number',
    label: data.getColumnLabel(0),
    calc: function (dt, row) {
        var val = dt.getValue(row, 1);
        return (val != '' && val != null) ?new Number(val).valueOf() : null;
    }
}];
// fill in the rest of the columns
for (var i = 1; i < data.getNumberOfColumns(); i++) {
    columns.push(i);
}
var view = new google.visualization.DataView(data);
view.setColumns(columns);

You would draw your visualization(s) using the DataView instead of the DataTable.