Last year, we developed the Food Issues Census explorer site, to go alongside the Food Ethics Council‘s report on the work that over 300 organisations are doing on food and farming. (You can download the report here.)
The main objective in developing the site was to create a flexible interface presenting a variety of data – and types of data – in a number of ways, using simple technology that could be updated quickly. We thought it’d be useful – and fun – to take a quick look at how we turned the results from the survey into the charts you can see on the site.
To do this, we used a bit of PHP, jQuery, and the Highcharts Javascript library. These are all tools that can be downloaded by anyone for free. What’s more, by using jQuery and Highcharts, the website is viewable across most modern browsers – including on tablets and mobile phones – without having to develop separate code.
We started by putting the survey results into a MySQL database, with a few extra fields calculated from existing columns to make it easier to group indicators together and filter them. This is always a useful step if the data won’t be changing much, as it makes the code that follows simpler to write and easier to understand.
While the aim of the site is to let users explore the data as they want to, it’s important to present useful and interesting indicators as the main choices, to avoid overwhelming a user. To make the options clearer, we grouped indicators into three types: a) totals to display, b) categories to break the data down by, and c) categories to filter by.1 Each group of indicators can then be shown in a different menu, letting the user focus on each group, one at a time.
After fetching the user-requested data from the database, we turned the results into a table using basic PHP-to-HTML code. It’s always useful to have access to the data behind a visualisation, so we decided to give users the option to show the table of data being used on a page. You can see this by viewing any indicator and clicking the “Show/hide data table” link at the bottom of the page. For instance, the data for Source of income broken down by Theme looks like this:
| Global | Local | Inclusion | Education | Farming | Health | Environment | |
|---|---|---|---|---|---|---|---|
| Public sector | 7 | 21 | 10 | 13 | 8 | 11 | 12 |
| Private sector | 3 | 5 | 1 | 2 | 4 | 2 | 3 |
| Third sector | 12 | 8 | 7 | 9 | 8 | 7 | 10 |
| Individuals | 9 | 13 | 4 | 5 | 12 | 5 | 11 |
| Other | 2 | 6 | 4 | 3 | 4 | 5 | 3 |
Now that the data for every page gets output as an HTML table, we can create our visualisation by adapting the Highchart demo to turn tables into charts. This varied slightly between pages, as the table format depended on the indicator and breakdown selected, but was largely a case of cycling through table headers and data in Javascript, and setting up values for Highcharts based on each.
For example, to set up the groups of data in the table above, we used:
// Use jQuery's each() function to loop through the table's
// header cells, adding each to the list of categories
var table = document.getElementById('data_table');
$('thead th', table).each( function(i) {
options.xAxis.categories.push($("<div/>").html(this.innerHTML).text());
});
To add the data from each row into a new series, with the name taken from the left-most cell, we used:
// Set up options.series as an empty array
options.series = [];
// Use jQuery's each() function to loop through the rows in the table
$('tr', table).each( function(i) {
var tr = this;
// Skip the first row (index = 0) as this contains group names
if (i > 0) {
// Set up some empty defaults for this series
options.series[i - 1] = { name: '', data: [] };
// Loop through the TH and TD cells on this row
$('th, td', tr).each( function(j) {
if (j == 0) {
// If on the first, left-most cell, set the series name to
// this cell's value
options.series[i - 1].name =
$("<div/>").html(this.innerHTML).text();
} else {
// Otherwise add this cell's value as data to the current series
options.series[i - 1].data.push(parseFloat(this.innerHTML));
}
});
}
});
Everything after this is fairly standard Highcharts functionality, namely:
$(document).ready(function() {
// Set up options
options = {
chart: {
// Choose which HTML element to render the chart in
renderTo: 'hc_container',
// Choose the type of chart to show
defaultSeriesType: 'bar'
},
// ... more settings here
};
// ... Set up options.series as described above here
// Show chart
chart = Highcharts.Chart(options);
});
And below is one that we made earlier. Hopefully this demonstrates how quick and simple visualising data in the browser can be, when the right content and the right tools are available.
1. [return] In technical terms, this basically gives us our SQL statement: SELECT [measure a] WHERE [measure c is filtered] GROUP BY [selected breakdown b]
We are proud to announce that we have successfully redeveloped and launched…
More
OCSI created the first version of the Community Needs Index in partnership…
More
