google-visualizationjquery-csvtimeofday

How may I explicitly define the datatype of a Google Charts DataTable column after it has been created?


I am using jquery-csv toArrays function to populate a google visualization DataTable like this:

function drawChart() {
  // Load the CSV file into a string 
  $.get("Book1.csv", function(csvString) {

  // transform the CSV string into a 2-dimensional array
  var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});

  // Create new DataTable object from 2D array
  var data = new google.visualization.arrayToDataTable(arrayData);

  // Set which columns we will be using
  var view = new google.visualization.DataView(data);
  view.setColumns([0,1,5,9,13,17,21,25,29]);
...

The first column in the CSV file contains a list of times which are used as the horizontal axis for the chart.

Google visualization's arrayToDataTable function attempts to automatically determine the appropriate data type for each column but it fails with the first column assigning it the String type instead of the required TimeOfDay type.

I know I can determine a columns datatype when populating it manually like so:

var dt = new google.visualization.DataTable({
    cols: [{id: 'time', label: 'Time', type: 'timeofday'},
           {id: 'temp', label: 'Temperature', type: 'number'}],
...

But can I change a column's data type after it has already been populated by the arrayToDataTable function?

EDIT:

Here is a CSV file similar to those which I'm currently using.

When I change the column heading to object notation before creating the DataTable as suggested below and force it to TimeOfDay, the first column gets converted to a series of NaN:NaN:NaN.NaN. Here is a simplified example similar to the one in the suggested answer.

google.load('visualization', '1', {packages: ['controls', 'charteditor']});
google.setOnLoadCallback(drawChart);

function drawChart() {
    // Load the CSV file into a string 
    $.get("Book1.csv", function(csvString) {

    // transform the CSV string into a 2-dimensional array
    var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});

    // Create new DataTable object from 2D array
    var data = new google.visualization.arrayToDataTable(arrayData);

    // Show datatable in grid to see what is happening before the data type change
    var chart1 = new google.visualization.Table(document.getElementById('chart_div0'));
    chart1.draw(data);      

    // Here we explicitly define type of first column in table
    arrayData[0][0] = {type: 'timeofday', label: arrayData[0][0]};

    // Create new DataTable object from 2D array
    var data = new google.visualization.arrayToDataTable(arrayData);

    // Show datatable in grid to see what is happening after the data type change
    var chart2 = new google.visualization.Table(document.getElementById('chart_div1'));
    chart2.draw(data);
    });
 }

Thanks!


Solution

  • change the column heading to object notation before creating the DataTable
    and use a DataView to convert the first column to 'timeofday'

    google.charts.load('current', {
      callback: function () {
        csvString = 'TIME,TEMP0,HUM0\n12:00:04 AM,24.7,50\n12:01:05 AM,24.7,50';
        var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
    
        var data = new google.visualization.arrayToDataTable(arrayData);
        
        var columns = [];
        for (var i = 0; i < data.getNumberOfColumns(); i++) {
          columns.push(i);
        }
        
        var view = new google.visualization.DataView(data);
        columns[0] = {
          calc: function(dt, row) {
            var thisDate = new Date('1/1/2016 ' + dt.getValue(row, 0));
            return [thisDate.getHours(), thisDate.getMinutes(), thisDate.getSeconds(), thisDate.getMilliseconds()];
          },
          label: arrayData[0][0],
          type: 'timeofday'
        };
        view.setColumns(columns);
    
        var chart = new google.visualization.Table(document.getElementById('chart_div'));
        chart.draw(view);
      },
      packages: ['corechart', 'table']
    });
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-csv/0.71/jquery.csv-0.71.min.js"></script>
    <div id="chart_div"></div>