Monday 23 December 2013

8 Google Charts from Public CSV datasets

Quandl is a fantastic source of data collected from various agencies, curated with care and then made available either on their website or as CSV or JSON files that can be downloaded or used directly from your application. On registration you get access to your personal key that allows you to access the data more frequently than what is allowed in unregistered access. Registration is free.

In this example, we see how we can access CSV data about education in India from Quandl and publish it either on an HTML page or in blog. This example is based on the excellent tutorial available at Economistry except that the locations of source data and the JS libraries used have been changed so that one does not need to have a dedicated webserver to serve the files.

Google Chart Example


The Javascript library jquery.csv-0.71.js is required to convert CSV files into a format that is acceptable to the Google Data Table. You can download this from this URL, as explained in the Economistry tutorial and then upload it into your own webserver if you want to be assured of being able to access it whenever. Otherwise, if you are on a blog and cannot upload a file, you can link to it directly over the web as shown in the listing below.


<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=xxxxxxxxxxx..xxx", function(csvString) {
         // transform the CSV string into a 2-dimensional array
            var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
         // 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,2]);
         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(2), minValue: data.getColumnRange(2).min, maxValue: data.getColumnRange(2).max},
          legend: 'none'
          };
          var chart = new google.visualization.LineChart(document.getElementById('csv2chart'));
          chart.draw(view, options);
         });
   }
   </script>
</head>
<body>
   <div id="csv2chart" style="width: 900px; height: 500px;"> </div>   
</body>


In the next example, we will explore how to make these charts more interactive. Stay tuned.

4 comments:

  1. Hi,

    I've managed to successfully use this code to create a live bar chart using an online CSV but I would like to add different colours for the bars.

    Any ideas how to achieve this?

    Each row of data could have a different name so am not sure how easy this could be done?

    ReplyDelete
  2. it is working for only some csv files but not for all.
    for other it is givng a blank output.
    please help me with this...
    for example im unable generate a google table with this type data...

    A,B
    hello,123
    hi,1234

    whats wrong in this.

    Thanks

    ReplyDelete
  3. Nice work, besides, if you need some quality charts for your PowerPoint presentation you can get them from http://charts.poweredtemplate.com/powerpoint-diagrams-charts/ppt-graph-charts/0/index.html it's save a lot of your time and make your presentation better.

    ReplyDelete
  4. CSV or Comma Separated Variables files are commonly used data ones which store data similar to a database. excel reporting dashboard

    ReplyDelete