Monday 23 December 2013

9 Interactive Dashboard with Google Charts with external CSV data

As an extension of the previous post where we used to data in CSV format available from an externally hosted source like Quandl to draw charts, we now see to inject a measure of interactivity into the same chart. The original CSV file containing data on education in India has lots of data columns. In this example, we will see how we can choose any one column of data and draw the corresponding chart either in an HTML page or in this blog. This example is based on the tutorial given in Economistry.


Google Chart Example


Please do note that in the URL pointing to the CSV file, we have added a sorting criteria as otherwise the graph is drawn in a reversed time scale, with newer data in the left and older data in the right.


<head>
   <title>Google Chart Example</title>
   <script src="https://www.google.com/jsapi"></script>
   <script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
   <script src="http://prithwis.x10.bz/charts/jquery.csv-0.71.js"></script> 
<!--   <script src="https://jquery-csv.googlecode.com/files/jquery.csv-0.71.js"></script> -->
   <script type="text/javascript">
   // load the visualization library from Google and set a listener
   google.load("visualization", "1", {packages:["corechart"]});
   google.setOnLoadCallback(drawChartfromCSV);
  
   function drawChartfromCSV(){
     // grab the CSV
         $.get("https://www.quandl.com/api/v3/datasets/MOSPI/EDU_INST_SCHOOL_EXPENDTR_29_1.csv?api_key=xxxxxx...xxx&trim_start=2001-06-30&trim_end=2011-06-30&sort_order=asc", function(csvString) {
         // transform the CSV string into a 2-dimensional array
            var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
         // use arrayData to load the select elements with the appropriate options
              for (var i = 1; i < arrayData[0].length; i++) {       // i starts from 1, not 0 because the first column is text
            // this adds the given option to both select elements
              $("select").append("<option value='" + i + "'>" + arrayData[0][i] + "</option");
              }
              
         // set the default selection
            $("#range option[value='1']").attr("selected","selected");
         // this new DataTable object holds all the data
            var data = new google.visualization.arrayToDataTable(arrayData);
         // this view can select a subset of the data at a time
            var view = new google.visualization.DataView(data);
            view.setColumns([0,1]);
         var options = {
         title: "EDUCATIONAL INSTITUTIONS, SCHOOLS AND EXPENDITURE - INDIA",
          hAxis: {title: data.getColumnLabel(0), minValue: data.getColumnRange(0).min, maxValue: data.getColumnRange(0).max},
          vAxis: {title: data.getColumnLabel(1), minValue: data.getColumnRange(1).min, maxValue: data.getColumnRange(1).max},
          legend: 'none'
          };
          var chart = new google.visualization.LineChart(document.getElementById('csv2chart'));
          chart.draw(view, options);
          // this part makes the chart interactive
          
          $("select").change(function(){
          
          // determine selected  range - Vertical Axis
          
           var range = +$("#range option:selected").val();
          // update the view
             view.setColumns([0,range]);
          // update the options
          
             options.vAxis.title = data.getColumnLabel(range);
             options.vAxis.minValue = data.getColumnRange(range).min;
             options.vAxis.maxValue = data.getColumnRange(range).max;
             
          // update the chart
             chart.draw(view, options);
          });
         });
   }
   </script>
</head>
<body>
   <div id="csv2chart" style="height: 500px; width: 900px;">
 </div>
<select id="range">Choose your chart</select>
</body>



The original tutorial in Economistry allows one to choose both the domain (horizontal axis) and the range (vertical axis) but given the nature of the data, having anything other than year on the horizontal axis would make no sense.

No comments:

Post a Comment