How to generate a Google graph from date time timestamps from a csv file

Question

I have a CSV file with datetime timestamps (in milliseconds from 1970) as X axis separed tis a comma ',' and an associated Temperature value as Y axis.

ie :

1485097200000,22.5
1485098100000,23.8
1485099000000,24.2
etc ...

From that kind of CSV file i would like to generate a google graph code like this :

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);


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

  var data = new google.visualization.DataTable();
  data.addColumn('datetime', 'Date');
  data.addColumn('number', 'Temperatures');

  data.addRows([
    [new Date(1485097200000), 22.5],
    [new Date(1485098100000), 23.8],
    [new Date(1485099000000), 24.2]
  ]);


  var options = {
    title: 'Temperature measured every 15 minutes',
    width: 900,
    height: 500,
    hAxis: {

      gridlines: {count: 15}
    },
    vAxis: {
      gridlines: {color: 'none'},
      minValue: 0
    }
  };

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

  chart.draw(data, options);

  var button = document.getElementById('change');



  button.onclick = function () {

    // If the format option matches, change it to the new option,
    // if not, reset it to the original format.
    options.hAxis.format === 'M/d/yy' ?
      options.hAxis.format = 'MMM dd, yyyy' :
    options.hAxis.format = 'M/d/yy';

    chart.draw(data, options);
  };
}

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<button id="change">Click to change the format</button>
<div id="chart_div"></div>

Would you please tell if it is possible and how i could do it so , i'm newbie.

Regards,

Answer 1

Here is an example for how to get the get the data from a CSV file (in this case come from a string but the principal is the same.

The CSV file should look like this:

1486727700000,5\n1486728600000,7\n1486729200000,3\n1486729800000,1\n1486730400000,3\n1486731000000,4\n1486731600000,3\n1486732200000,4\n1486732800000,4

Also you need to extract the data from the file so here is the function.

function getData(csv) {
  var output = [];
  csv.split(/\n/).forEach(function(row) {
    var cells = row.split(','),
        date = new Date(parseInt(cells[0])),
        value = parseFloat(cells[1]);

    output.push([date, value]);
  });

  return output;
}

And live demo here:

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

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

function drawChart() {
  var data = new google.visualization.DataTable();
  data.addColumn('datetime', 'Date');
  data.addColumn('number', 'Temperatures');

  //   data.addRows([
  //     [new Date(2017, 1, 10, 13, 55), 5],  [new Date(2017, 1, 10, 14, 10), 7],  [new Date(2017,1, 10,14,20), 3],
  //     [new Date(2017, 1, 10, 14, 30), 1],  [new Date(2017, 1, 10, 14,40), 3],  [new Date(2017, 1, 10, 14,50), 4],
  //     [new Date(2017, 1, 10,15,00), 3],  [new Date(2017, 1, 10,15,10), 4],  [new Date(2017, 1, 10,15,20), 2]
  //   ]);

  data.addRows(getData(csvFile));

  var options = {
    title: 'Temperature measured every 15 minutes',
    width: 900,
    height: 500,
    hAxis: {

      gridlines: {count: 15}
    },
    vAxis: {
      gridlines: {color: 'none'},
      minValue: 0
    }
  };

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

  chart.draw(data, options);

  var button = document.getElementById('change');

  button.onclick = function () {

    // If the format option matches, change it to the new option,
    // if not, reset it to the original format.
    options.hAxis.format === 'M/d/yy' ? options.hAxis.format = 'MMM dd, yyyy' : options.hAxis.format = 'M/d/yy';

    chart.draw(data, options);
  };
}

var csvFile = '1486727700000,5\n1486728600000,7\n1486729200000,3\n1486729800000,1\n1486730400000,3\n1486731000000,4\n1486731600000,3\n1486732200000,4\n1486732800000,4';

function getData(csv) {
  var output = [];
  csv.split(/\n/).forEach(function(row) {
    var cells = row.split(','),
        date = new Date(parseInt(cells[0])),
        value = parseFloat(cells[1]);

    output.push([date, value]);
  });

  return output;
}

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<button id="change">Click to change the format</button>
<div id="chart_div"></div>

http://output.jsbin.com/coxemay

Update

How to get the CSV from file using ajax

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart(csvData) {
  var data = new google.visualization.DataTable();
  data.addColumn('datetime', 'Date');
  data.addColumn('number', 'Temperatures');

  getData(function(csvData) {
    data.addRows(csvData);

    var options = {
      title: 'Temperature measured every 15 minutes',
      width: 900,
      height: 500,
      hAxis: {

        gridlines: {count: 15}
      },
      vAxis: {
        gridlines: {color: 'none'},
        minValue: 0
      }
    };

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(data, options);
  });


  var button = document.getElementById('change');

  button.onclick = function () {

    // If the format option matches, change it to the new option,
    // if not, reset it to the original format.
    options.hAxis.format === 'M/d/yy' ? options.hAxis.format = 'MMM dd, yyyy' : options.hAxis.format = 'M/d/yy';

    chart.draw(data, options);
  };
}

function getData(callback) {
  $.get('https://gist.githubusercontent.com/moshfeu/e3fd00cb57ae5b5cffbda44422dff112/raw/bcadcdfb5a532cc5711949a60cce639b2da235e6/csv-file', function(csv) {
    var output = [];
    csv.split(/\n/).forEach(function(row) {
      var cells = row.split(','),
          date = new Date(parseInt(cells[0])),
          value = parseFloat(cells[1]);

      output.push([date, value]);
    });

    callback(output);
  });
}

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://code.jquery.com/jquery-3.1.0.js"></script>

<button id="change">Click to change the format</button>
<div id="chart_div"></div>

http://jsbin.com/coxemay/3/edit?html,js,console