openlylocal – OUseful.Info, the blog…

Sketching Substantial Council Spending Flows to Serco Using OpenlyLocal Aggregated Spending Data

An article in today’s Guardian (Serco investigated over claims of ‘unsafe’ out-of-hours GP service) about services provided by Serco to various NHS Trusts got me thinking about how much local councils spend with Serco companies. OpenlyLocal provides a patchy(?) aggregating service over local council spending data (I don’t think there’s an equivalent aggregator for NHS organisations’ spending, or police authority spending?) so I thought I’d have a quick peek at how the money flows from councils to Serco.

If we search the OpenlyLocal Spending Dashboard, we can get a summary of spend with various Serco companies from local councils whose spending data has ben logged by the site:

Using the local spend on corporates scraper I used to produce Inter-Council Payments Network Graph, I grabbed details of payments to companies returned by a search on OpenlyLocal for suppliers containing the keyword serco, and then generated a directed graph with edges defined: a) from council nodes to company nodes; b) from company nodes to canonical company nodes. (Where possible, OpenlyLocal tries to reconcile companies identified for payment by councils with canonical company identifiers so that we can start to get a feeling for how different councils make payments to the same companies.)

I then exported the graph as a json node/edge list so that it could be displayed by Mike Bostock’s d3.js Sankey diagram library:

(Note that I’ve filtered the edges to only show ones above a certain payment amount (£10k).)

As a presentation graphic, it’s really tatty, doesn’t include amount labels (though they could be added) and so on. But as a sketch, it provides an easy to digest view over the data as a starting point for a deeper conversation with the data. We might also be able to use the diagram as a starting point for a data quality improvement process, by identifying the companies that we really should try to reconcile.

Here are flows associated with speend to g4s identified companies:

I also had a quick peek at which councils were spending £3,500 and up (in total) with the OU…

Digging into OpenlyLocal spending data a little more deeply, it seems we can get a breakdown of how total payments from council to supplier are made up, such as by spending department.

Which suggests to me that we could introduce another “column” in the Sankey diagram that joins councils with payees via spending department (I suspect the Category column would result in data that’s a bit too fine grained).

Inter-Council Payments and the Google Fusion Tables Network Graph

One of the great things about aggregating local spending data from different councils in the same place – such as on OpenlyLocal – is that you can start to explore structural relations in the way different public bodies of a similar type spend money with each other.

On the local spend with corporates scraper on Scraperwiki, which I set up to scrape how different councils spent money with particular suppliers, I realised I could also use the scraper to search for how councils spent money with other councils, by searching for suppliers containing phrases such as “district council” or “town council”. (We could also generate views to to see how councils wre spending money with different police authorities, for example.)

(The OpenlyLocal API doesn’t seem to work with the search, so I scraped the search results HTML pages instead. Results are paged, with 30 results per page, and what seems like a maximum of 1500 (50 pages) of results possible.)

The publicmesh table on the scraper captures spend going to a range of councils (not parish councils) from other councils. I also uploaded the data to Google Fusion tables (public mesh spending data), and then started to explore it using the new network graph view (via the Experiment menu). So for example, we can get a quick view over how the various county councils make payments to each other:

Hovering over a node highlights the other nodes its connected to (though it would be good if the text labels from the connected nodes were highlighted and labels for unconnected nodes were greyed out?)

(I think a Graphviz visualisation would actually be better, eg using Canviz, because it can clearly show edges from A to B as well as B to A…)

As with many exploratory visualisations, this view helps us identify some more specific questions we might want to ask of the data, rather than presenting a “finished product”.

As well as the experimental network graph view, I also noticed there’s a new Experimental View for Google Fusion Tables. As well as the normal tabular view, we also get a record view, and (where geo data is identified?) a map view:

What I’d quite like to see is a merging of map and network graph views…

One thing I noticed whilst playing with Google Fusion Tables is that getting different aggregate views is rather clunky and relies on column order in the table. So for example, here’s an aggregated view of how different county councils supply other councils:

In order to aggregate by supplied council, we need to reorder the columns (the aggregate view aggregates columns as thet appear from left to right in the table view). From the Edit column, Modify Table:

(In my browser, I then had to reload the page for the updated schema to be reflected in the view). Then we can get the count aggregation:

It would be so much easier if the aggregation view allowed you to order the columns there…

PS no time to blog this properly right now, but there are a couple of new javascript libraries that are worth mentioning in the datawrangling context.

In part coming out of the Guardian stable, Misoproject is “an open source toolkit designed to expedite the creation of high-quality interactive storytelling and data visualisation content”. The initial dataset library provides a set of routines for: loading data into the browser from a variety of sources (CSV, Google spreadsheets, JSON), including regular polling; creating and managing data tables and views of those tables within the browser, including column operations such as grouping, statistical operations (min, max, mean, moving average etc); playing nicely with a variety of client side graphics libraries (eg d3.js, Highcharts, Rickshaw and other JQuery graphics plugins).

Recline.js is a library from Max Ogden and the Open Knowledge Foundation that if its name is anything to go by is positioning itself as an alternative (or complement?) to Google Refine. To my mind though, it’s more akin to a Google Fusion Tables style user interface (“classic” version) wherever you need it, via a Javascript library. The data explorer allows you to import and preview CSV, Excel, Google Spreadsheet and ElasticSearch data from a URL, as well as via file upload (so for example, you can try it with the public spend mesh data CSV from Scraperwiki). Data can be sorted, filtered and viewed by facet, and there’s a set of integrated graphical tools for previewing and displaying data too. Refine.js views can also be shared and embedded, which makes this an ideal tool for data publishers to embed in their sites as a way of facilitating engagement with data on-site, as I expect we’ll see on the Data Hub before too long.

More reviews of these two libraries later…

PPS These are also worth a look in respect of generating visualisations based on data stored in Google spreadsheets: DataWrapper and Freedive (like my old Guardian Datastore explorer, but done properly… Wizard led UI that helps you create your own searchable and embeddable database view direct from a Google Spreadsheet).

Tracking Down Local Government Consultation Web Pages

One of the things I have on my to do list for this year is to try to get a joint paper out with Danilo Rothberg on public consultation platforms at local, national and European level.

In the UK, many local councils have an area of their website dedicated to local consultations, so my first hacky thought for a way to track them down was to scrape something together around a Google search of the form: site:gov.uk intitle:consultation intitle:council.

By chance, I stumbled across page on OpenlyLocal linking to the services offered by a particular council, which made me wonder if I could actually pull down a list of the URLs of consulation pages by council directly from OpenlyLocal.

A quick Twitter exchange with that site’s maestro, Chris Taggart/@countculture, suggested that OpenlyLocal “[s]piders the Localgov redirect urls every week… …trick is knowing the LGD service id code, and then you can get all URLs for councils with URL for it”. In addition, “It’s the OL key you need (that maps to the ldg native uid). Something like this: http://openlylocal.com/services?ldg_service_id=370“.

So, decoding that, and with a bit of extra Googling, here’s where I’m at:

from the esd/effective service delivery toolkit (“Facilitated by the Local Government Association (LGA) working for local government improvement so councils can serve people and places better. esd-toolkit is owned and led by the local government sector”), we can find the LGD service ID codes for services relating to consultations:
- Council – consultation – service delivery (867): All councils are expected to consult on specific areas of their service delivery. This allows service users and other interested parties to have to opportunities to be involved in planning, prioritising and monitoring of services. It also gives customers an opportunity to see all consultation activity, both current and in the past, and a mechanism for customers to research satisfaction with service delivery, opinions about specific projects and looks at lifestyle profiles which helps us design better local services.
- Council – consultation and community engagement (366): The local authority uses various means to consult and engage with local communities including development of community and citizens’ forums and panels, consultation events, public events, young people’s participation.
- Council – spending plans – consultation (658): Arrangement of public meetings or other means by which citizens can be consulted on budget plans for the forthcoming year. Previous consultations may be published or available for view on request.
- Education – consultations (49): The education authority consult with all interested parties (schools, teachers, parents, pupils) on all issues concerning education provision and in particular on any proposed changes to education within schools run by the authority.
- Equalities and diversity – assessment and consultation (861): The LA is responsible for ensuring that equality and diversity is considered at all times both in employment policy and in the provision of services. Every authority should assess, and consult on, the impact of policy in relation to equality and diversity within their community
- Planning – consultation (855): The involvement of the public in the planning process. When planning applications are submitted there is a comprehensive system in place which ensures that proposals are publicised in order to invite comments from the local community.
To pull down the URL associated with each service for each council from OpenlyLocal (URLs of the form http://openlylocal.com/services?ldg_service_id=370), we need to know the mapping from Local Service ID codes shown above to the corresponding OpenlyLocal service codes (link???)
The DirectGov A-Z Directory of Local Services page links to alphabetical listings of service related pages presumably keyed on the Local Gov Service ID (LGSL= in the URL?), though on a quick skim through the listings I couldn’t find any consultation related services? [Ah, I should probably have tried from here: Directgov: Find out about local consultations]
From the Local Directgov on the Dept for Communities and Local Government website, I found a newsletter link to Local Directgov: open datasets
On data.gov.uk, there’s a handy CSV data file referred to as the Local directgov services list: “This dataset is held on the Local Directgov platform which provides the deep links into Local council websites for a number of services in Directgov. The Local Authority Service details holds the local council URLS for over 240 services where the customer can directly transfer to the appropriate service page on any council in England.” The CSV data is organised as follows:
Authority Name,SNAC,LAid,Service Name,LGSL,LGIL,Service URL ... Adur District Council,45UB,1,Find out about local consultations,867,8,http://www.adur.gov.uk/consultation/index.htm ...

So, that’s where I’m at… I now have a CSV file from data.gov.uk with a list of deep link URLs in to local gov websites, and a set of Local Gov Service IDs from esd that allow me to identify the links corresponding to various sorts of consultation.

If I run those URLs through an RSS/Atom feed autodiscovery service, how many open/current consultation feeds do you think I’ll find?!

PS One of of the things OpenlyLocal is managing to do is provide an abstraction/normalisation layer over the myriad local council websites. It’s interesting to compare this with the JISC funded Linking You Toolkit that surveyed URL patterns across various UK university websites and made a series of recommendations about a normalised URL scheme that could potentially be used (via URL rewrites) to provide a common URL interface over common areas of UK HE websites (a simplification that I think also fits into the spirit of normalised data presentation approach being taken with the Key Information Sets). It strikes me that an alternative scheme, at least for the purposes of building services that can map from a central service to deep links related to particular services or content areas of a university website, would be to follow the Local Gov Service ID model and come up with a set of university related services or content areas (potentially reusing those identified by the Linking You project), and then request that universities publish site maps relating deeplink URLs to the appropriate identifier.

PPS as to why I bothered with this post: I’m just trying to document/model an example of the sort of search process I go through whenever I try to find anything out… Which as you can see, is still messed up and informal, starting with Google, then moving to tapping folk I suspect might know the answer to questions I’m trying to articulate, and finally ending up by checking out data.gov.uk…

PPPS Given the full list of government consultation websites for departmental and agency consultations, I wonder: is there a service/content area coding scheme used to identify common areas of central gov department websites?

Work In Progress – Voting Treemaps

I’ve posted before about plotting the make-up of local council committees as treemaps using data from OpenlyLocal (e.g. Council Committee Treemaps From OpenlyLocal) to generate views such as this:

I also posted recently about scraping election data from Lichfield Council (Screenscraping With Google Spreadsheets App Script and the =importHTML Formula), again prompted by work @countculture is doing around local government (Introducing the Open Election Project: tiny steps towards opening local elections).

Anyway, here’s how the votes were placed according to that most recent batch of Lichfield elections:

The size of the blocks is determined by the number of votes cast in each ward for that party. Where several candidates stood from the same party, their votes in that ward are totalled for the treemap view. Where there was no need for an election, I used a notional 100 votes for the co-opted candidates.

Comparing the treemaps allows you to compare(?!) the votes cast by citizens per party per ward with the number of votes each party has in each local council committee… That is, the charts show a mapping from how votes cast by citizens across a council area get translated to votes castable by elected representatives in local council committees.

Next step is to do a view that shows votes by candidate in each ward. To get a feeling for what that would look like (though without the party related colouring), see the following Many Eyes Wikified generated treemap:

Now if only the Guardian Politics API included data about all the votes cast in each constituency at the last election….. @jaggeree…?;-)

Council Committee Treemaps From OpenlyLocal

As a great fan of treemaps, I’ve posted a couple of times about the various ways in which they may be used to provide glanceable views over the makeup of local council committees (e.g. Glanceable Committee Memberships with Treemaps, Treemapping Council Committees Using OpenlyLocal Data.

Prompted by Dev8D hackery last week, I spent a little bit of time getting a version of the JIT treemap working with data feeds from the OpenlyLocal website, as hinted at in Some of My Dev8D Tinkerings – Yahoo Pipes Quick Start Guide, Cross-Domain JSON with JQuery and Council Committee Treemaps from OpenlyLocal.

If you want to grab a treemap for a particular council, you need to find it’s Openlylocal ID number. This appears in the various URLs for council pages – can you guess the ID number for the following council?

– http://openlylocal.com/councils/298-Isle-of-Wight-Council
– http://openlylocal.com/committees?council_id=298

Plugging this number into the following test URL should raise the treemap:
http://ouseful.open.ac.uk/councils/demo-embed.php?id=ID

For example: http://ouseful.open.ac.uk/councils/demo-embed.php?id=298

(I haven’t got colours set for every party type yet… Chris – any chance of a list of all party types across all councils??? ;-)

You should be able to embed the treemap in your own page within an iframe:

<iframe src="http://ouseful.open.ac.uk/councils/demo-embed.php?id=298" width="800" height="600" scrolling="no"></iframe>

To get the JSON data into the page from Openlylocal, I run it through a Yahoo Pipe so that I can then grab a JSON-P feed from the pipe. (Currently, Openlylocal just exposes a JSON object. To load this directly into a web page, we need to wrap it in a callback function – that’s all the pipe does – provide a callback…)

Here’s how I get the council committee data into the page with a JQuery call:

function get_ol(id){
 var url="http://openlylocal.com/committees.json?council_id="+id;
  url="http://pipes.yahoo.com/ouseful/jsonproxy?url="+encodeURIComponent(url)+"&_render=json&_callback=?";
 $.getJSON(
   url,
   function(data) {ol_cttees(data.value.items[0]); }
 )
}

The pipe simply grabs the JSON data and then re-exposes it:

To find out how the Openlylocal data is transformed, you can try and dig through:
http://ouseful.open.ac.uk/councils/ccl-treemap_files/example2-dyn.js
The function that kicks everything off is init2(id), with id set to the Openly local council id number. (Reader beware: the js file may contain unused and/or broken legacy code as well as the operational stuff…;-)

Note: thanks to Chris/@countculture for annotating the Openlylocal JSON feeds for council committees with council name data over the weekend so I could create these visualisations more easily :-)

PS for an alternative take on how to use the same data, but in XML form, from the Openlylocal site, see@kitwallace’s The party make-up of council committees – doing it the XML way. Very nice :-)

Some of My Dev8D Tinkerings – Yahoo Pipes Quick Start Guide, Cross-Domain JSON with JQuery and Council Committee Treemaps from OpenlyLocal

One of the goals I set myself for this year’s Dev8D was to get round to actually using some of the things I’ve been meaning to try out for ages, particularly Google App Store and JQuery, and also to have a push on some of the many languishing “projects” I’ve started over the last year, tidying up the code, making the UIs a little more presentable, and so on…

Things never turn out that way, of course. Instead, I did a couple of presentations, only one of which I was aware of beforehand!;-) a chance remark highlighting me to the fact I was down to do a lightning talk yesterday…

I did start looking at JQuery, though, and did manage to revisit the Treemapping Council Committees Using OpenlyLocal Data idea I’d done a static proof of concept for some time ago…

On the JQuery front, I quickly picked up how easy it is to grab JSON feeds into a web page if you have access to JSON-P (that is, the ability to attach a callback function to a JSON URL so you can call a function in the web page with the object as soon as it loads), but I also ran into a couple of issues. Firstly, if I want to load more than one JSON feed into a page, and then run foo(json1, json2, json3, json4, json5), how do I do it? That is, how do I do a “meta-callback” that fires when all the separate JSON calls have loaded content into the page. (Hmm – I just got a payoff from writing this para and then looking at it – it strikes me I could do a daisy chain – use the callback from the first JSON call to call the second JSON object, use the callback from that to call the third, and so on; but that’s not very elegant…?) And secondly, how do I get a JSON object into a page if there is no callback function available (i.e. no JSON-P support)?

I’m still stuck on the first issue (other than the daisy chain/bucket brigade hack), but I found a workaround for the second – use a Yahoo pipe as a JSON-P proxy. I’ll be writing more about this in a later post, but in the meantime, I popped a code snippet up on github.

On the Openlylocal/council treemap front, I’d grabbed some sample JSON files from the Openlylocal site as I left Dev8D last night for the train home, and managed to hack the resulting objects into a state that could be used to generate the treemap from them.

A couple of hours fighting with getting the Openlylocal JSON into the page (solved as shown above with the Pipes hack) and I now have a live demo – e.g. http://ouseful.open.ac.uk/test/ccl/index-dyn.php?id=111. The id is the openlylocal identifier used to identify a particular council on the Openlylocal site.

If you’re visiting Openlylocal council pages, the following bookmarklet will (sometimes*;-) display the corresponding council committee treemap:

javascript:var s=window.location.href;s=s.replace(/.*=/,””);window.location.href=”http://ouseful.open.ac.uk/test/ccl/index-dyn.php?id=”+s;

(It works for pages with URLs that end =NNN;-)
Council committee treemap

The code is still a bit tatty, and I need to tidy up the UI, (and maybe also update to a newer JIT visualisation library), so whilst the URI shown above will persist, I’ll be posting an updated version to somewhere else (along with a longer post about how it all works) when I get round to making the next set of tweaks… Hopefully, this will be before Dev8D next year!;-)

PS I also had a huge win in discovering a javascript function that works at least on Firefox: .toSource(). Apply it to a javascript object (e.g. myobj.toSource() and then if you do things like alert(myobj.toSource()) you can get a quick preview of the contents of that object without having to resort to a debugger or developer plugin tool:-)

PPS can you tell my debugging expertise is limited to: alert(“here”); all over the place ;-) Heh heh…

Treemapping Council Committees Using OpenlyLocal Data

Some time ago, I started exploring how treemap representations might be used to provide a quick overview of the make-up of the membership of local council committees (Glanceable Committee Memberships with Treemaps).

Following a lazyweb request to @countculture about expressing complete council committee membership data from Openly Local (Topical Versions of Local Council Websites… Give Us the Data and We’ll Work It For You), and the rapid fulfilment of that request ;-), here’s a proof of concept about how to use that data to power a treemap from the Javascript InfoViz Toolkit (JIT) to provide a glanceable display of the make-up of Isle of WIght Council committees, colour coded by party:

Each committee is represented as follows:

{children: [
 {children: [],
  data: {
   $area: "3", $pcolor: "purple",members:"Jonathan Francis Bacon, Paul Fuller, Heather Humby, "}, id :"ccl12_0", name: "Independent"
 },
 {
  children: [],
  data: {
   $area: "5", $pcolor: "blue",members:"Ivan Bulwer, Susan Jane Scoccia, Albert Taylor, Jerry White, David G Williams, "},
   id :"ccl12_1",
   name: "Conservative"},
{
  children: [],
  data: {
   $area: "1", $pcolor: "darkorange",members:"Colin Richards, "},
   id :"ccl12_2",
   name: "Liberal-Democrat Group"}
], data: {$area: 9}, id: "ccl12", name: "Licensing and General Purposes Committee"}

With another quick bash at the code, I should be able generate these views on the fly from the JSON data feeds provided on the OpenlyLocal site. (At the moment, the route I take to generate the Javscript object that powers the treemap is a really clunky one:-(

What never ceases to amaze me, though, is how a tweak from one representation of a dataset (that is, the JSON data published by OpenlyLocal), to another (the JIT treemap representation) allows the creation of interactive visuliastions as if by magic :-)

If you want to play with your own treemaps in the meantime, this bit of Javascript will produce a simple representation of committee. member and party data that can be visualised within Many Eyes WIkified:

for (var i=0;i< c.committees.length;i++){
  for (var j=0;j< c.committees[i].members.length; j++){
  	document.write("\""+c.committees[i].title.replace("&", "and")+"\""+","+"\""+c.committees[i].members[j]["first_name"]+" "+c.committees[i].members[j]["last_name"]+"\""+","+"\""+c.committees[i].members[j]["party"]+"\""+"<br/>");
  }
}

(where c is the javascript object that is published as the JSON feed from a committee page on OpenlyLocal, such as this one for the Isle of Wight).

Here’s an example:

Whilst this treemap doesn’t allow you to fix the colours in the way that the JIT component does:

TM.Squarified.implement({  
   'setColor': function(json) {  
     return json.data.$pcolor; 
   }  
 });

($pcolor is a variable I set for each committee member saying what colour should be displayed for them…), the Many Eyes Wikified does allow you to manipulate the tree representation that powers the treemap, e.g. by reordering the way in which the different elements are displayed: