Vizualyse: Google Charts

Showing posts with label Google Charts. Show all posts

Thursday, 28 May 2015

18. googleVis - The best of R and GoogleCharts

In a couple of earlier posts, we have seen how Google Charts can be used to generate online charts and graphs but the challenge was that were was a lot of rather messy javascript to play around with. All such mess has been rendered redundant with the R package googleVis and it is now possible to generate charts with the Google Charts API using only R.

The resultant charts need to be viewed in a browser -- and the html code is available for embedding in websites like this. This google docs spreadsheet has some data on milk production in Indian states. It has been downloaded and used for these exercises

Here is the code :


usePackage <- function(p) {
  if (!is.element(p, installed.packages()[,1]))
    install.packages(p, dep = TRUE)
  require(p, character.only = TRUE)
}

usePackage("googleVis")

StateMilk <- read.csv(file = "MilkState.tsv", head=TRUE, sep ='\t')
head(StateMilk)
StateMilkTotal <- StateMilk[, c("State","Total")]
StateName <- StateMilk[, "State"]
head(StateName)
head(StateMilkTotal)

GeoStates <- gvisGeoChart(StateMilk, "State", "Total", options=list(region="IN", displayMode="markers", resolution="provinces", width=1200, height=800))
plot(GeoStates)
GeoStates2 <- gvisGeoChart(StateMilk, "State", "Total", options=list(region="IN", displayMode="regions", resolution="provinces", width=600, height=400))
plot(GeoStates2)
print(GeoStates2)

and here is the chart that is generated by code printed out by the last print command
-----------------

Data: StateMilk • Chart ID: GeoChartIDe315e03b5c4 • googleVis-0.5.8
R version 3.0.2 (2013-09-25) • Google Terms of Use • Documentation and Data Policy
-----------------

In addition to maps, you can also draw bar and column charts like with this code


MilkBar <- gvisBarChart(StateMilk, xvar="State", yvar=c("CowMilkTotal", "BuffaloMilk"),options = list(width=800, height=900))
plot(MilkBar)

MilkColumn <- gvisColumnChart(StateMilk, xvar="State", yvar=c("CowMilkTotal", "BuffaloMilk"), options = list(width=800, height=900) )
plot(MilkColumn)

A complete list of sample code for all possible types of googleVis charts is available at on this page. All the examples can be executed by running the demo program as explained at the top of the reference page.

Wednesday, 23 April 2014

15. Motion Charts

Watch this video

Then make your own motion charts by either writing code, or better still simply using Google Docs.

was created with data on Tata Group stock prices from Quandl and residing on this Google Docs sheet

Tuesday, 31 December 2013

11 RIL stock data from Quandl - Filtered on a range of dates

This is an improvement on the chart shown in the previous post where NSE data was picked up in CSV format from Quandl - an excellent source of global timeseries data -- and shown in a candlestick format. Since the data that we are plotting this time, Reliance share prices, goes back a long time. We will be using a RangeFilter to choose the start and end dates of our plot. The big challenge in this case was to convert the date data obtained from Quandl which was in STRING format into the DATE format that is required by the RangeFilter. This was done by using the code very kindly provide by ASGALLANT.The final chart is seen on this html page and also in this blog page.

Vizualyse PK


<head>
   <title>Vizualyse PK</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","controls"]});
   google.setOnLoadCallback(drawCandlefromQuandlPK);
//-------------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------------
   function drawCandlefromQuandlPK(){
   
   var dashboardPK = new google.visualization.Dashboard(
       document.getElementById('div_dashboardPK'));
//----------------------------------------------------------------------------------------------------------------------------------------       
   var controlPK = new google.visualization.ControlWrapper({
     'controlType': 'ChartRangeFilter',
     'containerId': 'div_controlPK',
     'options': {
       // Filter by the date axis.
       'filterColumnIndex': 0,
       'ui': {
         'chartType': 'LineChart',
         'chartOptions': {
  //         'chartArea': {'width': '90%'},
           'hAxis': {'baselineColor': 'none'}
         },
         // Display a single series that shows the closing value of the stock.
         // Thus, this view has two columns: the date (axis) and the stock value (line series).
         'chartView': {
           'columns': [0, 4]
         },
         // 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000
         'minRangeSize': 86400000
       }
     },
     // Initial range: 2007-11-31 to 2008-09-31.
     'state': {'range': {'start': new Date(2007, 10, 31), 'end': new Date(2008, 8, 31)}}
   });
 //--------------------------------------------------------------------------------------------------------------------------------------------
   var chartPK = new google.visualization.ChartWrapper({
     'chartType': 'CandlestickChart',
     'containerId': 'div_chartPK',
     'options': {
       title: "Reliance Stock Prices at NSE from Quandl",
       // Use the same chart area width as the control for axis alignment.
//       'chartArea': {'height': '80%', 'width': '90%'},
//       'hAxis': {'slantedText': false},
          'vAxis': {'viewWindow': {'min': 0, 'max': 3500}},
       'legend': {'position': 'none'}
     },
   });
//--------------------------------------------------------------------------------------------------------------------------------------------
     // grab the CSV
         //  $.get("https://www.quandl.com/api/v1/datasets/NSE/RELIANCE.csv?&trim_start=1998-03-20&trim_end=2013-12-27&collapse=weekly&
         $.get("https://www.quandl.com/api/v3/datasets/NSE/RELIANCE.csv??&auth_token=xxxxxxxxxxxxxxxxxxxxx&trim_start=1998-03-20&trim_end=2013-12-27&collapse=weekly&sort_order=desc", function(csvStringPK) {
         // transform the CSV string into a 2-dimensional array
            var arrayDataPK = $.csv.toArrays(csvStringPK, {onParseValue: $.csv.hooks.castToScalar});
         // this new DataTable object holds all the data
            var dataPK = new google.visualization.arrayToDataTable(arrayDataPK);
            
         // this view selects only a subset of the data, that is columns 0,3,1,5,2 that is appropriate for the candlestick chart
         // more importantly it converts the 0th column from string to date using the example given in http://jsfiddle.net/asgallant/8RFsB/1/
         // the string to date conversion is very important as otherwise the ControlWrapper will not work
            
            var viewPK = new google.visualization.DataView(dataPK);
            viewPK.setColumns([{
                type: 'date',
                label: dataPK.getColumnLabel(0),
                calc: function (dt, row) {
                    // split the string into date and time
                    var valArr = dt.getValue(row, 0).split(' ');
                    // split the date into year, month, day
                    var dateArr = valArr[0].split('-');
                   // create a new Date object from the data
                   // note that we subtract 1 from the month to convert to javascripts 0-index
                   var date = new Date(dateArr[0], dateArr[1] - 1, dateArr[2]);
                  // return the date and the formatted value as set in the original DataTable
                  return {
                      v: date,
                      f: dt.getFormattedValue(row, 0)
                  };
               }
          }, 3, 1,5,2]);
            
  //-----------------------------------------------------------------------------------------------------------------------------------------------          
    
        dashboardPK.bind(controlPK, chartPK);
        dashboardPK.draw(viewPK);
         });
   }
   </script>
</head>
<body>
<div id="div_dashboardPK">
<div id="div_chartPK" style="height: 600px; width: 900px;">
</div>
<div id="div_controlPK" style="height: 50px; width: 900px;">
</div>
</div>
</body>

an earlier post on dashboards also talks about filters in some detail. however the code is possibly cleaner here.

Sunday, 29 December 2013

10 Plotting NSE stock price data from Quandl

Quandl is a really fantastic source for all kinds of really useful financial data. For example you can see historical data on Tata Steel stock prices here. We will use this data to plot a Google Candlestick chart that shows open, close, high, low data for the Tata Steel stock both in a regular HTML page and in the blog below.

Google Chart Example

There are couple of interesting things in the following code that are interesting to know about.

While pulling the CSV data from Quandl, you can specify the start/end dates and the level of aggregation, that is daily, weekly, monthly
Google Candlestick charts expect the data to be in a certain order. Quandl provides the data in a different order and the columns needs to be rearranged. Hence the data table called "data" is converted into a view called "view" where we select columns 0,1,2,3,5 and reorder them into 0,3,1,5,2
This view and not the data is used to draw the chart


<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(drawCandlefromQuandl);
  
   function drawCandlefromQuandl(){
     // grab the CSV
         $.get("https://www.quandl.com/api/v3/datasets/NSE/TATASTEEL.csv?api_key=xxxxxx.xxxxx&collapse=weekly&start_date=2012-12-31&end_date=2014-12-31", 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,3,1,5,2]);
         var options = {
         title: "Tata Steel NSE Stock Prices from Quandl",
          hAxis: {title: view.getColumnLabel(0), minValue: view.getColumnRange(0).min, maxValue: view.getColumnRange(0).max},
          vAxis: {title: "Price in INR"},
          legend: 'none'
          };
          var chart = new google.visualization.CandlestickChart(document.getElementById('QuandlCandle'));
          chart.draw(view, options);
         });
   }
   </script>
</head>
<body>
   <div id="QuandlCandle" style="width: 900px; height: 500px;"> </div>   
</body>

Quandl provides a huge amount of data and this makes the chart rather clumsy. Our next step would be to see how to provide a control so that you can specify the dates of your data rather than hardcoding the same into the java script.

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.

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.

Friday, 20 December 2013

7 Creating a Dashboard - Part II

Here we take the same data used in the previous post and convert it into a dashboard. We have replaced the chart with a chart wrapper. Added three filters. Added a dashboard component and bound the three filters to the chart wrapper. You can see the result both in this blog as well as on this regular HTML page.

The Dashboard on this blog

Data Source

sOne can choose one or multiple states. Also one can specify the range of cow and buffalo milk production and so select only the states that have this production.


<head>
    <script src="https://www.google.com/jsapi" type="text/javascript"></script>
    <script type="text/javascript">
     google.load('visualization', '1', {'packages': ['corechart','controls']});
     google.setOnLoadCallback(drawDashBoardMQ);
    
    function drawDashBoardMQ() {
            var queryMQ = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&sheet=MilkProduction&range=B2:H37&headers=1');
      queryMQ.setQuery('select B,E,F'); 
      queryMQ.send(handleQueryResponseMQ);
    };
    
    function handleQueryResponseMQ(responseMQ) {
      if (responseMQ.isError()) {
       alert('Error in query: ' + responseMQ.getMessage() + ' ' + responseMQ.getDetailedMessage());
       return;
     }
  
   var dashboardMQ = new google.visualization.Dashboard(document.getElementById('dash_divMQ')); 
 
 // Create a filter, passing some options
 // to filter out some rows based on value of column Cow Milk Total
   var CowMilkRangeSlider = new google.visualization.ControlWrapper({
    'controlType': 'NumberRangeFilter',
    'containerId': 'cowfilter_MQ',
    'options': {
      'filterColumnLabel': 'Cow Milk Total',
      'label' : 'Cow',
      'minValue': 0.0,
      'maxValue': 5000.0
    }
   });
  
  // Create a filter, passing some options
  // to filter out some rows based on value of column Buffalo Milk
   var BuffaloMilkRangeSlider = new google.visualization.ControlWrapper({
    'controlType': 'NumberRangeFilter',
    'containerId': 'buffalofilter_MQ',
    'options': {
      'filterColumnLabel': 'Buffalo Milk',
      'minValue': 0.0,
      'maxValue': 5000.0
    }
   });
  
   // Create a filter, passing some options
   // to filter out rows based on value of column State
   var StateFilter = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'Statefilter_MQ',
    'options': {
      'filterColumnLabel': 'State',
      'ui': {
          'labelStacking': 'vertical',
          'allowTyping': false,
          'allowMultiple': true,
          'caption' : 'Choose State'
        },
     
    }
   });
  
  // Create a chart, passing some options
  // This is a chart wrapper
  var ColChartMQ = new google.visualization.ChartWrapper({
    'chartType': 'ColumnChart',
    'containerId': 'chart_divMQ',
    'options': {
      'width': 900,
      'height': 600,
      'title' : 'Milk Production',
      'hAxis': {title: 'State', titleTextStyle: {color: 'red'}},
    }
  }); 
  // Connect the three filters to the same chart
     dashboardMQ.bind([CowMilkRangeSlider,BuffaloMilkRangeSlider,StateFilter], ColChartMQ);
     
  // Get the data   
    var dataMQ = responseMQ.getDataTable();
    
  // Publish everything through teh dashboard
   dashboardMQ.draw(dataMQ)
   
}

    </script>
  </head>
  <body>
  <h3>
The Dashboard on this blog </h3>
<div id="dash_divMQ">
    <div id="Statefilter_MQ" style="height: 50px; width: 900px;">
</div>
<div id="chart_divMQ" style="height: 600px; width: 900px;">
</div>
<div id="cowfilter_MQ" style="height: 50px; width: 900px;">
</div>
<div id="buffalofilter_MQ" style="height: 50px; width: 900px;">
</div>
</div>
<a href="https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&amp;usp=drive_web#gid=1" target="_blank">Data Source</a>
  </body>

for some reason, the data from the first row, Andhra Pradesh, cannot be seen on the dashboard even though it is visible in previous normal Column Chart !!

A more sophisticated RangeFilter is shown in this post.

7 Creating a Dashboard - Part 1

When you have a lot of data to be shown on a page, it makes sense to give the viewer an opportunity to filter some of the data so that he or she gets a cleaner view. In this case, we will first draw a rather clumsy Column Chart and then in the next section. The data for the chart is drawn from this spreadsheet. The chart shown below can also be seen in this regular HTML page.

Basic Column Chart Showing All Data

Data Source

Note how we have specified

the Google Docs spreadsheet : https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc
the sheet : sheet=MilkProduction
range : range=B2:H37
headers : headers=1
columns : query.setQuery('select B,E,F');
chart type : var chartMQ = new google.visualization.ColumnChart(document.getElementById('chart_divMQ'));


<head>
    <script src="https://www.google.com/jsapi" type="text/javascript"></script>
    <script type="text/javascript">
     google.load('visualization', '1', {'packages': ['corechart','controls']});
     google.setOnLoadCallback(drawColChartMQ);
    
    function drawColChartMQ() {
      var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&sheet=MilkProduction&range=B2:H37&headers=1');
      query.setQuery('select B,E,F'); 
      query.send(handleQueryResponseMQ);
      };
    
    function handleQueryResponseMQ(responseMQ) {
      if (responseMQ.isError()) {
       alert('Error in query: ' + responseMQ.getMessage() + ' ' + responseMQ.getDetailedMessage());
       return;
      }
  
    var options = {
          title: 'Milk Production',
          hAxis: {title: 'State', titleTextStyle: {color: 'red'}}
        };

    var data = responseMQ.getDataTable();
    var chartMQ = new google.visualization.ColumnChart(document.getElementById('chart_divMQ'));
    chartMQ.draw(data, options );
}

    </script>
  </head>
  <body>
  <h3>
Basic Column Chart Showing All Data</h3>
<div id="chart_divMQ" style="height: 500px; width: 900px;">
</div>
<a href="https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedExUbS1xNzBuQVAyNDJTeG1weFQxbXc&amp;usp=drive_web#gid=1" target="_blank">Data Source</a>
  </body>
<br />

In the next post we will convert this into a Dashboard

Wednesday, 18 December 2013

5 State Data in Google GeoChart

In this map, we use data from States ( see the spreadsheet ) and this too can be mapped into a map of India. However state level maps are not available and the data for a particular state is mapped into a point ( or marker) in the state.

Once again we observe the limitations of not being able to show more than two data points for each state.


<head>
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>
    <script type='text/javascript'>
     google.load('visualization', '1', {'packages': ['geochart']});
     google.setOnLoadCallback(drawMarkersMapTR);
    
    function drawMarkersMapTR() {
            var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=xxxxxxxxxxxxxx&sheet=MilkProduction&range=B2:H37&headers=1');
      query.setQuery('select B,C,D'); 
      query.send(handleQueryResponseTR);
    };
    
    function handleQueryResponseTR(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  
  var options = {
        region: 'IN',
        displayMode: 'markers', 
        width : 600,
        height : 400
      };

  var data = response.getDataTable();
  var chart = new google.visualization.GeoChart(document.getElementById('chart_divTR'));
  chart.draw(data, options );
}

    </script>
  </head>
  <body>
    <div id="chart_divTR"></div>
  </body>

Tuesday, 17 December 2013

4 Plotting Cities on a Map along with Data

In this example, we have data about certain of India -- data about number of registered vehicles in different years -- in a Google Docs spreadsheet. We use this data to draw a Google GeoChart.

In the code please observe the following points

The spreadsheet doc is identified as https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AhEX55Pfl1eedE5mUHN4QVRLNzdldEpwZnAyNy1abUE
The sheets as identified by sheet=CityWise2
The range is identified by range=B2:M23
The columns being selected are identified by query.setQuery('select B,D,E');
The region in the map is 'IN' that is India
The displayMode is "markers" .. which means towns and cities, had this been region we would have got full states


<head>
    <script type='text/javascript' src='https://www.google.com/jsapi'></script>
    <script async defer src="https://maps.googleapis.com/maps/api/js?key=myAPIkeyhere&callback=initMap"
  type="text/javascript"></script>
    <script type='text/javascript'>
     google.load('visualization', '1', {'packages': ['geochart']});
     google.setOnLoadCallback(drawMarkersMap02);
    
    function drawMarkersMap02() {
            var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=xxxxxxxxxxxxxxx&sheet=CityWise2&range=B2:M23&headers=1');
      query.setQuery('select B,D,E'); 
      query.send(handleQueryResponse02);
    };
    
    function handleQueryResponse02(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  
  var options = {
        datalessRegionColor : 'FFFF99',
        region: 'IN',
        displayMode: 'markers',
        colorAxis: {colors: ['green', 'blue']}
      };

  var data = response.getDataTable();
  var chart = new google.visualization.GeoChart(document.getElementById('pm_div4'));
  chart.draw(data, options );
}

    </script>
  </head>
  <body>
    <div id="pm_div4" style="width: 600px; height: 500px;"></div>
  </body>

In creating this chart in this blog, we faced an unusual error. When viewed as an independent post, the chart was visible but when seen as a part of the blog, the chart was not drawn ?

What can be the reason ? The reason was that we had the same function name in two different posts and so when two posts were visible together on the same page, the codes were interefering with each other. So we simply placed an arbitrary suffix values to the function names and it worked.

The same code can be embedded in a non-blog web post like this.

A similar map can be created using (a) public website data and (b) R statistical tool.

Google Map API
Subsequent to the creation of these posts, Google has made it mandatory for all new domains ( or new websites, new blogs) to incorporate a Google Maps API into the HTML code :

<script async defer src="https://maps.googleapis.com/maps/api/js?key=myAPIkeyhere&callback=initMap"
  type="text/javascript"></script>

this API key can be obtained from this Google page.

Wednesday, 11 December 2013

3 Specifying Range of Data and Selecting Columns : Google Charts with Google Docs data

In the previous post, we had described how to create a basic Google Chart from data stored in a Google Doc spreadsheet. In that example, the data was picked up from the default first sheet of the spreadsheet and the data was located in the top left corner. In reality, the data could be stored in any of the other sheets and could be located in any portion of the sheet.

In this example, the Google Docs spreadsheet, has four sheets. For the purpose of drawing our chart we would like to specify that

Data to be picked up from sheet named "Demo3"
Within this sheet, from the range C3:I23
Within this range from the columns C, D, G, H
Given the nature of the data we would like to multiply column G by 1000 before it is plotted

If you look at the spreadsheet in a browser, the URL will show up as

https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodDNvMXdPX1NqanlyLVdtbEE1dlJ3LUE&usp=drive_web#gid=3

For our purpose the URL will be truncated as follows and used in the program

https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodDNvMXdPX1NqanlyLVdtbEE1dlJ3LUE

This URL can also be obtained by going to the option File=>Publish to the Web

The chart will look as follows

Sheet 2 Chart - Sheet, Range, Cols

this chart has been generated by embedding the following code in this blog. It can also be embedded in any other HTML page.


<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", '1', {packages:['corechart']});

google.setOnLoadCallback(drawChart01);


function drawChart01() {


 var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodDNvMXdPX1NqanlyLVdtbEE1dlJ3LUE&sheet=Demo3&range=C3:I12&headers=1');
      query.setQuery('select C,D,1000*G,H'); 
  query.send(handleQueryResponse01);
}

function handleQueryResponse01(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  
  var options = {
          title: 'Industrial Production Index',
          vAxis: {title: 'Sector',  titleTextStyle: {color: 'red'}},
          width: 400,
          height : 600
        };


  var data = response.getDataTable();
  var chart = new google.visualization.BarChart(document.getElementById('ChartSpan4'));
  chart.draw(data, options );
}



</script>

<title>Sheet 2 Chart - Sheet, Range, Cols</title>
</head>

<body>

<span id='ChartSpan4'></span>
</body>

Sunday, 8 December 2013

2 Creating a Google Chart using data pulled from Google Docs

I have data lying in this Google Docs spreadsheet. that I want to display in this blog post or in any other html page using Google Charts.

The first task is to publish this data on the web and make it visible to anyone who has the URL.

Next take the following piece of code and insert it into your website.


<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", '1', {packages:['corechart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
  var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodGJBQkRpX0Rrdjc2OXZMZmZmNzk3WGc&usp=drive_web#gid=0');

  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  var options = {'title':'How Much PIZZA I Ate Last Night',
                       'width':400,
                       'height':600};
  var chart = new google.visualization.PieChart(document.getElementById('columnchart'));
  chart.draw(data, options);
}
</script>

<title>Data from a Spreadsheet</title>
</head>

<body>
<span id='columnchart'></span>
</body>

Note the URL of the spreadsheet : https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=0AqawrNPy9RHodGJBQkRpX0Rrdjc2OXZMZmZmNzk3WGc&usp=drive_web#gid=0 and use this in the function drawChart()

and this what you get :
....................................

Data from a Spreadsheet
....................
the same code can be embedded in a no-blog HTML page and loaded into a webserver as seen here.

Pages

Thursday, 28 May 2015

Wednesday, 23 April 2014

Tuesday, 31 December 2013

Sunday, 29 December 2013

Monday, 23 December 2013

Friday, 20 December 2013

The Dashboard on this blog

Basic Column Chart Showing All Data

Wednesday, 18 December 2013

Tuesday, 17 December 2013

Wednesday, 11 December 2013

Sunday, 8 December 2013