Saturday, May 2

Please mind the gap between the software and the platform

Excuse the awful pun. Gapminder.org developed this really neat graphical flash engine back in 2006, Google were quick to snap it up the following year, and have also been so graciously kind as to publish an API for it under the guise of Motion Chart.

You can use this engine freely to include whatever data you want, though getting data in and out can be a pain, if, like me, you want to enable non-programmers to experiment with their own data.

So, the first step might be reconfiguring the source from explicit javascript declarations, to something a little more familiar - xml. I used a structure like this:

<?xml version="1.0" encoding="UTF-8"?>

<bubbleXml>
<title>Hello, this is my first motion chart!</title>
<dataContainer>
<dataRow>
<WeekNo>1</WeekNo>
<Category>Canned Fruit</Category>
<Metrics>Items Sold</Metrics>
<Value>17</Value>
</dataRow>
<dataRow>
<WeekNo>1</WeekNo>
<Category>Canned Fruit</Category>
<Metrics>Sales Value</Metrics>
<Value>23.78</Value>
and so on..

Which gives you a nice extensible data platform, although it is quite verbose (duh.. it's xml!).
Each dataRow captures the time variable (WeekNo), the entity names (Category), along with the name and value of each metric.

You can then write a parser for the xml in your favourite scripting language, and embed the results into the javascript. Here's an example for PHP - verbatim:

<?php

$xmlfile = "dataYouWantToUse.xml";
$data = simplexml_load_file($xmlfile);

$categories = $data->xpath("dataContainer/dataRow/Category");
$timestamp = $data->xpath("dataContainer/dataRow/WeekNo");
$values = $data->xpath("dataContainer/dataRow/Value");
$metrics = $data->xpath("dataContainer/dataRow/Metrics");
$title = $data->xpath("title");

$uCatN = count(array_unique($categories));
$uTimeN = count(array_unique($timestamp));
$uMet = array_unique($metrics);
$uMetN = count(array_unique($metrics));
$rowCount = $uCatN*$uMetN*$uTimeN-1;
$googleCount = $uCatN*$uTimeN;

function listColumns($uMet)
{
echo "\n";
foreach ($uMet as $thisMet) {
echo "\tdata.addColumn('number', \"$thisMet\");\n";
}
}
function generateJs($categories, $timestamp, $values, $rowCount, $uMetN)
{
echo "\n";
$c = 0;
for ($i=0; $i<=$rowCount; $i+=$uMetN) {
if ($timestamp[$i] < 10)
$time = "0".$timestamp[$i];
else
$time = $timestamp[$i];
echo "\tdata.setValue($c, 0, '$categories[$i]'); \n" ;
echo "\tdata.setValue($c, 1, '2009W$time'); \n" ;
for ($j=$i; $j<=$i+$uMetN-1; $j++) {
$n = $j-$i+$uMetN-1;
echo "\tdata.setValue($c, $n, $values[$j]); \n" ;
}
$c++;
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title><?php echo $title[0]; ?></title>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {'packages':['motionchart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();

data.addRows(<?php echo $googleCount; ?>);
data.addColumn('string', 'Category');
data.addColumn('string', 'Time');
<?php listColumns($uMet);
generateJs($categories, $timestamp, $values, $rowCount, $uMetN); ?>

var chart = new google.visualization.MotionChart(document.getElementById('chart_div'));
chart.draw(data, {width: 800, height:460});
}
</script>
<style type="text/css">
body {text-align: center;}
</style>
</head>

<body>
<h3><?php echo $title[0]; ?></h3>
<p> data in motion</p>
<div id="chart_div"></div>
<h5><a href="http://worldofones.blogspot.com">adam marshall 2009</a> | <a href="http://worldofones.blogspot.com">worldofones.blogspot.com</a> | <a href="http://code.google.com">powered by google</a></h5>
</body>
</html>
Notes:
That's set up to process time in the '2009W1' format - haven't got round to generalising that yet, but it's not too hard to modify if you want to use different formats. Likewise for the inclusion of text (colour category) metrics - you just need to modify the listColumns() function, and make sure you've got quotes around their values.
Phew! So, you can load that xml file directly into the page at runtime. Maybe you can get your IT guys to help making a current data source available as xml in this way; if not - here's a little script for converting a csv file to xml courtesy of Chris M over at bytemycode.com, with just a few modifications to create the chosen format.

<?php

// define params
$containerLabel = "dataContainer";
$rowLabel = "dataRow";
$fileLocation = "csvdata/".$_GET["from"].".csv";
$fileDestination = "xmldata/".$_GET["to"].".xml";
$title = $_GET["title"];
/**
* Converts a grid layout CSV to an XML
* Rows are nested within the container variable
* Column headers in the CSV become tags containing the data, within each row
*/
function csv2xml($file, $container = 'data', $rows = 'row')
{
$r = "\t<{$container}>\n";
$row = 0;
$cols = 0;
$titles = array();

$handle = @fopen($file, 'r');
if (!$handle) return $handle;

while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
if ($row > 0) $r .= "\t\t<{$rows}>\n";
if (!$cols) $cols = count($data);
for ($i = 0; $i < $cols; $i++) {
if ($row == 0) {
$titles[$i] = $data[$i];
continue;
}
$r .= "\t\t\t<{$titles[$i]}>";
$r .= $data[$i];
$r .= "</{$titles[$i]}>\n";
}
if ($row > 0) $r .= "\t\t</{$rows}>\n";
$row++;
}
fclose($handle);
$r .= "\t</{$container}>\n";
return $r;
}

$xml = csv2xml($fileLocation, $containerLabel, $rowLabel);
$fullxml = '<?xml version="1.0" encoding="UTF-8"?>'."\n
<bubbleXml>\n\t<title>$title</title>\n".$xml."</bubbleXml>";
file_put_contents($fileDestination,$fullxml);

?>


I've added GET parameters to this, so if you had a csv file caled 'data1' in a folder on the server called 'csvdata', you can call using the parameters:
?from=data1&to=fileYouWantToUse&title=Hello, this is my first motion chart!
to create the xml file. Now, you should be able to use excel/analytics software/business intelligence tools to produce the csv, compile it to xml, and display it in a cool motion chart!

Why not show your CEO his company's history in motion, or your sales team the last few weeks of consumer trends?


1 comment:

  1. Apologies that the code isn't better formatted, escaping it and keeping the tabs seems beyond my capabilities after this long in front of the computer.... :-S

    ReplyDelete