Visualization Tutorial: Number of living languages

This was my ‘Hello World’ program to data visualization. I started looking for some interesting data to visualize. After many days of lazy search, I decided start with small set of data. Finally locked on showing number of languages alive (Currently spoken) in each country. I’ll take you through the steps I followed. If you find something wrong in my steps, please give me suggestions to improve.

Step 1: Data Collection

Data. That’s free on net! Five minutes of search lead me to Ethnologue; where all of the world’s 6,909 known living languages are cataloged. I don’t need the whole data, only took the name of the country, ISO code for the country and count of the languages alive, and made into an Excel Sheet. You can download it from here.

Step 2: Method for Visualization

I googled for a easier way to represent this data. Went through Processing.js, Google Visualization APIs, Open source flash maps. I finally settled with Google Visualization API’s Geomap. Main reason, its is damn simple to visualize tabular data.

It’s a javascript API, so we can easily fed data by the help of a few lines of code.

Step 3: Setting up the data as a ‘Data source’ for the Google Visualization API

As you can see in the example, we can supply data using hardcoded values. But in my case there is lots of values, which I can’t hard code. So, I decided on passing the data to the API through JSON/XML. I exported the data sheet into as CSV file. Then imported CVS into a MySQL table (How-to put CVS into MySQL), where I can access using PHP to generate JSON or XML if required.

With this piece of PHP code I generated JSON from the table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

<?
$username="database_user";
$password="database_password";
$database="database_name";
 
$link =  mysql_connect("database_host",$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
//echo 'Connected successfully';
 
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM country_list";
 
$result = mysql_query($query);
 
$country_json = array();
 
while($data_item = mysql_fetch_array( $result ))
{
	$country_json[] = array(
		'country_name' =>$data_item['country'],
		'iso_code' => $data_item['iso_code'],
		'speaking' => $data_item['speaking'],
		'total' => $data_item['total'],
		);
} 
//print json_encode($country_json);
 
header('Content-Type: text/javascript; charset=utf8');
$json= '('.json_encode($country_json).');'; //must wrap in parens and end with semicolon
print_r($_GET['callback'].$json); //callback is prepended for json-p
 
mysql_close();
?>

The generated JSON can be seen here.

Step 4: Setting Up the Google Visualization API

Here is the code for the HTML page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

<html>
<head>
  <script type='text/javascript' src='http://www.google.com/jsapi'></script>
  <script type='text/javascript'>
    google.load("jquery", "1.4.2");
    google.load('visualization', '1', {'packages': ['geomap']});
 
    google.setOnLoadCallback(init_map);
 
    function init_map(){
      $.ajax({
        url: 'country_list_json.php',
        success: drawMap
      });      
    }
 
    function drawMap(incomingdata) {
      json_data = eval(incomingdata);
      var data = new google.visualization.DataTable();
 
      data.addRows(json_data.length);
      data.addColumn('string', 'Country');
      data.addColumn('number', 'Number of Languages');
      data.addColumn('string','HOVER', 'Details');
      for(i=0;i<json_data.length;i++){
        data.setValue(i, 0, json_data[i]['iso_code']);
        data.setValue(i, 1, parseInt(json_data[i]['speaking']));
        data.setValue(i, 2, json_data[i]['country_name']);
      }
 
 
      var options = {};
      options['dataMode'] = 'regions';
      //options['width'] = '605px';
      //options['height'] = '362px';
      options['colors'] = [0xffe4da,0xcd3700];
 
      var container = document.getElementById('map_canvas');
      var geomap = new google.visualization.GeoMap(container);
 
			google.visualization.events.addListener(
	      geomap, 'regionClick', function(e) {
	      //alert(e['region']);
	      window.open ("http://www.ethnologue.com/show_country.asp?name="+e['region']); 
	    }); 
 
      geomap.draw(data, options);
  };
  </script>
</head>
 
<body>
  <div id='map_canvas'></div>
</body>
</html>

Here, I uses jQuery AJAX [ $.ajax() ] to get the JSON and fed one by one to Data Table of Geomap API [ data.setValue() ]. Once the values are loaded, call the draw function [ geomap.draw() ] . Tada! Here we have an density map of the world based on number of living languages in each country; plus hovering over country gives the number of languages in that country and when clicked will takes us to the country page in Ethnologue.com.

Data Source: Lewis, M. Paul (ed.), 2009. Ethnologue: Languages of the World, Sixteenth edition. Dallas, Tex.: SIL International. Online version: http://www.ethnologue.com/.

|