CODEWITHSUNDEEP
javascripthtmlcsvchartsgoogle-visualization
Adam Lee
Adam Lee

Reputation: 43

Continuous Axis Google Chart Loaded with CSV?

I've managed to implement a continuous axis google chart on my page and got it formatted the way I want it. Now my requirements have changed and I'm trying to load this chart from a CSV as opposed to hard coded and randomly generated data.

I've confused myself and gotten in over my head on how to convert my working chart into pulling from a CSV. I'm going to post a few things here,

  1. One of my other charts that utilizes a CSV, this is what I was trying to recreate
  2. My working continuous axis chart running off hard coded data
  3. My current state of the chart now that I've tried to implement the change.

Here is #1:

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


                  // this new DataTable object holds all the data
                  var data = new google.visualization.arrayToDataTable(arrayData);
                  // CAPACITY - En-route ATFM delay - YY - CHART
                  var pieMain = new google.visualization.ChartWrapper({
                     chartType: 'BarChart',
                     containerId: 'pieMain',
                     dataTable: data,
                     options:{
                         title: 'Bar Chart Test',
                        'vAxis': { title: "Bar Chart Test" },
                        'width': 1100, 
                        'height': 540,
                        'backgroundColor': 'Ivory',
                        'color':'Black',
                        'hAxis': {
                    title: "Date",
                    gridlines: { count: 3, color: '#CCC' },
                    format: 'dd-MMM-yyyy'
                },
                        title: 'Bar Chart Test',
                        titleTextStyle : {color: 'Black', fontSize: 16},
                     }
                  });
                  pieMain.draw();
               });
            }
            google.setOnLoadCallback(drawPieVisualization)

            changeRange = function() {
          pieMain.sort({column: 0, desc: false});
          pieMain.draw();
        };

            changeRangeBack = function() {
          pieMain.sort({column: 0, desc: true});
          pieMain.draw();
        };

Here is #2:

    function drawVisualization() {
 var data = new google.visualization.DataTable();
    data.addColumn('date', 'Date');
    data.addColumn('number', 'Value');

    // add 100 rows of pseudo-random-walk data
    for (var i = 0, val = 50; i < 100; i++) {
        val += ~~(Math.random() * 5) * Math.pow(-1, ~~(Math.random() * 2));
        if (val < 0) {
            val += 5;
        }
        if (val > 100) {
            val -= 5;
        }
        data.addRow([new Date(2014, 0, i + 1), val]);
    }

    var chart = new google.visualization.ChartWrapper({
        chartType: 'ComboChart',
        containerId: 'slider_chart_div',
        options: {
            'title': 'Average Ratings',
                            'vAxis': { title: "Average Rating" },
                            'backgroundColor': 'Ivory',
                            'color':'Black',
            width: 1100,
            height: 400,
            // omit width, since we set this in CSS
            chartArea: {
                width: '75%' // this should be the same as the ChartRangeFilter
            }
        }
    });

    var control = new google.visualization.ControlWrapper({
        controlType: 'ChartRangeFilter',
        containerId: 'control_div',
        options: {
            filterColumnIndex: 0,
            ui: {
                chartOptions: {
                    'backgroundColor': 'Ivory',
                            'color':'Black',
                    width: 1100,
                    height: 50,
                    // omit width, since we set this in CSS
                    chartArea: {
                        width: '75%' // this should be the same as the ChartRangeFilter
                    }
                }
            }
        }
    });

    var dashboard = new google.visualization.Dashboard(document.querySelector('#dashboard_div'));
    dashboard.bind([control], [chart]);
    dashboard.draw(data);

    function zoomLastDay () {
        var range = data.getColumnRange(0);
        control.setState({
            range: {
                start: new Date(range.max.getFullYear(), range.max.getMonth(), range.max.getDate() - 1),
                end: range.max
            }
        });
        control.draw();
    }
    function zoomLastWeek () {
        var range = data.getColumnRange(0);
        control.setState({
            range: {
                start: new Date(range.max.getFullYear(), range.max.getMonth(), range.max.getDate() - 7),
                end: range.max
            }
        });
        control.draw();
    }
    function zoomLastMonth () {
        // zoom here sets the month back 1, which can have odd effects when the last month has more days than the previous month
        // eg: if the last day is March 31, then zooming last month will give a range of March 3 - March 31, as this sets the start date to February 31, which doesn't exist
        // you can tweak this to make it function differently if you want
        var range = data.getColumnRange(0);
        control.setState({
            range: {
                start: new Date(range.max.getFullYear(), range.max.getMonth() - 1, range.max.getDate()),
                end: range.max
            }
        });
        control.draw();
    }

    var runOnce = google.visualization.events.addListener(dashboard, 'ready', function () {
        google.visualization.events.removeListener(runOnce);

        if (document.addEventListener) {
            document.querySelector('#lastDay').addEventListener('click', zoomLastDay);
            document.querySelector('#lastWeek').addEventListener('click', zoomLastWeek);
            document.querySelector('#lastMonth').addEventListener('click', zoomLastMonth);
        }
        else if (document.attachEvent) {
            document.querySelector('#lastDay').attachEvent('onclick', zoomLastDay);
            document.querySelector('#lastWeek').attachEvent('onclick', zoomLastWeek);
            document.querySelector('#lastMonth').attachEvent('onclick', zoomLastMonth);
        }
        else {
            document.querySelector('#lastDay').onclick = zoomLastDay;
            document.querySelector('#lastWeek').onclick = zoomLastWeek;
            document.querySelector('#lastMonth').onclick = zoomLastMonth;
        }
    });

    }

And Here is #3:

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


                  // this new DataTable object holds all the data
                  var data = new google.visualization.arrayToDataTable(arrayData);


var chart = new google.visualization.ChartWrapper({
    chartType: 'ComboChart',
    containerId: 'slider_chart_div',
    dataTable: data,
    options: {
        'title': 'Average Ratings',
                        'vAxis': { title: "Average Rating" },
                        'backgroundColor': 'Ivory',
                        'color':'Black',
        width: 1100,
        height: 400,
        // omit width, since we set this in CSS
        chartArea: {
            width: '75%' // this should be the same as the ChartRangeFilter
        }
    }
});

var control = new google.visualization.ControlWrapper({
    controlType: 'ChartRangeFilter',
    containerId: 'control_div',

    options: {
        filterColumnIndex: 0,
        ui: {
            chartOptions: {
                'backgroundColor': 'Ivory',
                        'color':'Black',
                width: 1100,
                height: 50,
                // omit width, since we set this in CSS
                chartArea: {
                    width: '75%' // this should be the same as the ChartRangeFilter
                }
            }
        }
    }
});

var dashboard = new google.visualization.Dashboard(document.querySelector('#dashboard_div'));
dashboard.bind([control], [chart]);
dashboard.draw(data);

function zoomLastDay () {
    var range = data.getColumnRange(0);
    control.setState({
        range: {
            start: new Date(range.max.getFullYear(), range.max.getMonth(), range.max.getDate() - 1),
            end: range.max
        }
    });
    control.draw();
}
function zoomLastWeek () {
    var range = data.getColumnRange(0);
    control.setState({
        range: {
            start: new Date(range.max.getFullYear(), range.max.getMonth(), range.max.getDate() - 7),
            end: range.max
        }
    });
    control.draw();
}
function zoomLastMonth () {
    // zoom here sets the month back 1, which can have odd effects when the last month has more days than the previous month
    // eg: if the last day is March 31, then zooming last month will give a range of March 3 - March 31, as this sets the start date to February 31, which doesn't exist
    // you can tweak this to make it function differently if you want
    var range = data.getColumnRange(0);
    control.setState({
        range: {
            start: new Date(range.max.getFullYear(), range.max.getMonth() - 1, range.max.getDate()),
            end: range.max
        }
    });
    control.draw();
}

var runOnce = google.visualization.events.addListener(dashboard, 'ready', function () {
    google.visualization.events.removeListener(runOnce);

    if (document.addEventListener) {
        document.querySelector('#lastDay').addEventListener('click', zoomLastDay);
        document.querySelector('#lastWeek').addEventListener('click', zoomLastWeek);
        document.querySelector('#lastMonth').addEventListener('click', zoomLastMonth);
    }
    else if (document.attachEvent) {
        document.querySelector('#lastDay').attachEvent('onclick', zoomLastDay);
        document.querySelector('#lastWeek').attachEvent('onclick', zoomLastWeek);
        document.querySelector('#lastMonth').attachEvent('onclick', zoomLastMonth);
    }
    else {
        document.querySelector('#lastDay').onclick = zoomLastDay;
        document.querySelector('#lastWeek').onclick = zoomLastWeek;
        document.querySelector('#lastMonth').onclick = zoomLastMonth;
    }
});

}


)}

And here is a sample of the CSV Data I'm utilizing 

    Time,Value
    2017/05/22 00:05:00,6710.4305066168
    2017/05/22 00:10:00,6667.5043776631
    2017/05/22 00:15:00,6615.6655550003
    2017/05/22 00:20:00,6554.988194257
    2017/05/22 00:25:00,6532.4164219201
    2017/05/22 00:30:00,6520.8965539932

The bottom part 'runOnce' in both #2 and #3 are to change the slider control on the chart from 1 day - 1 week - or 1 month of range on the chart, for clarification.

My chart is currently giving me the errors:

One or more participants failed to draw(). (Two of these)

And

The filter cannot operate on a column of type string. Column type must be one of: number, date, datetime or timeofday. Column role must be domain, and correlate to a continuous axis.

Upvotes: 2

Views: 806

Answers (1)

WhiteHat
WhiteHat

Reputation: 61222

the second error message reveals that arrayToDataTable
creates the first column as --> type: 'string'
instead of --> type: 'date'

use a DataView to convert the string to a date

you can create calculated columns in a data view using method --> setColumns

use view in place of data when drawing the dashboard

see following snippet... 

$.get("Source7Days.csv", function(csvString) {
  var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar}, {onParseValue: $.csv.hooks.castToScalar});

  // this is a static method, "new" keyword should not be used here
  var data = google.visualization.arrayToDataTable(arrayData);

  // create view
  var view = new google.visualization.DataView(data);
  view.setColumns([
    // first column is calculated
    {
      calc: function (dt, row) {
        // convert string to date
        return new Date(dt.getValue(row, 0));
      },
      label: data.getColumnLabel(0),
      type: 'date'
    },
    // just use index # for second column
    1
  ]);

  var chart = new google.visualization.ChartWrapper({
    chartType: 'ComboChart',
    containerId: 'slider_chart_div',
    options: {
      title: 'Average Ratings',
      vAxis: { title: 'Average Rating' },
      backgroundColor: 'Ivory',
      color: 'Black',
      width: 1100,
      height: 400,
      chartArea: {
        width: '75%'
      }
    }
  });

  var control = new google.visualization.ControlWrapper({
    controlType: 'ChartRangeFilter',
    containerId: 'control_div',
    options: {
      filterColumnIndex: 0,
      ui: {
        chartOptions: {
          backgroundColor: 'Ivory',
          color: 'Black',
          width: 1100,
          height: 50,
          chartArea: {
            width: '75%'
          }
        }
      }
    }
  });

  var dashboard = new google.visualization.Dashboard(document.querySelector('#dashboard_div'));
  dashboard.bind([control], [chart]);
  // use data view
  dashboard.draw(view);
  ...

Upvotes: 1

Related Questions