Simple Embeddable Twitter Map Mashup

Earlier today, I was pondering the Digital Planet Listeners’ map on open2.net and the #digitalplanet Twitter feed we have running alongside it:

and I started to wonder whether there was a simple way of generating an embeddable map showing the location of people tweeting given a Twitter feed.

I couldn’t find one offhand, so here’s a simple pipe that will do the job: Simple Tweetmap pipe.

Here’s how it works: start off by grabbing a feed from Twitter, such as a Twitter search feed.

Using a Twitter feed URL as an input to the pipe, grab the feed and then find the Twitter username of each individual from the user’s Twitter URL. So for example, map http://twitter.com/psychemedia onto psychemedia.

We now call on another pipe that calls the Twitter API to get personal details for each user who has a Tweet in the feed.

Here’s how that embedded pipe works: Twitter location pipe (it should really be called “Twitter User Details” pipe).

First, construct a URI that ask the Twitter API for the user details associated a particular Twitter username, (e.g. using the construction http://twitter.com/users/show/USERNAME.json) then pull the data back as a JSON feed. Finally, just make sure only a single record is returned (there should only be one anyway).

So the embedded pipe passes back an annotation to the original feed with user details. One of the user details is the user’s location – so let’s geocode it:

Sending the output of the location finder to item.y:location allows the pipe to produce a well formed KML and geoRSS output, that can be displayed in a map, such as the Yahoo! Pipes output preview map:

We can grab the KML URL from the More Options output and display the feed in an embeddable Google map in the normal way (simply enter the KML URI in the Google maps search box and hit Search):

If you want to embed the map in your own page, just grab the embed code…

To summarise the hack, here’s a quick review of the whole pipe:

So now if you want to plot where people who have tagged their tweets in a particular way are tweeting from, you can do :-)

Next step is to persuade the open2 team to start archiving appropriately tagged tweets and displaying them on a cluster map view over time :-) We could maybe even link in a timeline using something like TimeMap, the MIT Simile timeline and Google Maps integration library…?

Experiments in Displaying Google Form/Survey Results in Many Eyes

A couple of weeks ago, I posted a workaround for Creating Your Own Results Charts for Surveys Created with Google Forms. With the release of Many Eyes Wikified, it’s now possible to power Many Eyes visualisations from online data (e.g. as described in Many Eyes Wiki Dashboard – Online Visualisation Tools That Feed From Online Data Sources).

So I was wondering – would it be possible to just pull data from the results spreadsheet for a Google form, and visualise it directly in Many Eyes without having to do any results processing on the spreadsheet side?

Firs step – find a form. I created a test one some time ago doodling ideas for a mobile survey form which contains some data, so that’s a start: Demo Mobile User Form.

Second step – get the results file as CSV: Mobile survey results:

Hmm – Many Eyes Wikified doesn’t see the columns…???

It’s is ok with a different subsets though… e.g. this one:

(Note that I can’t seem to specify “to end of column” in the Google spreadsheet CSV export? e.g. setting the range to A1:J doesn’t work:-( So i need to define an arbitrary final row…)

Trying out the visualisations on this data, I can sort of get the text cloud visualisation to work:

Unfortunately, in many of the chart types, there doesn’t seem to be the ability to plot a count of particular results(?).

For example, in the bubble chart, I can’t seem to plot bubble size as a count of the results in each results category? (Would I expect to be able to do that…? Hmmm… I think so…?!) Instead, I can only plot size according to data values in one of the numerical columns?

In many cases, in order to plot sensible visualisations that process and display the form results data, I need to be able to count the occurrence of different results classes within a results column. A count option is available in the Matrix chart, but not in many of the other visualisation types?

There’s also the issue that many of the results contain multiple items; so for a example, in answer to the question “What do you use your mobile phone for?” we might get the answers Voice calls, Text Messaging/SMS, Web search, Maps/directions, Camera (stills) (selected from a drop down list on the original form).

What would be really nice would be the ability to specify a delimiter/separator to split out the different results in a particular column, then let Many Eyes enumerate the different possible answer choices in that column, and count on each one. So for example, I’d like to select a bubble chart based on the column “What do you use your mobile phone for?” and have Many Eyes identify the different segments, (Voice Calls, Web Search etc), count the occurrence of each of those and plot each segment as a bubble, with size proportional to the counted occurrence of the segment in the results.

In the meantime, I suppose it’s always possible to process the results in the spreadsheet as demonstrated in Creating Your Own Results Charts for Surveys Created with Google Forms and then just export the CSV of the particular question results tables to Many Eyes Wikified? Or alternatively, design questions that work nicely when the raw results are passed to Many Eyes Wikified?

UK Gov Getting into the Web…?!

Last week I posted about An Example Netvibes Dashboard for the Digital Britain Interim Report on WriteToReply on Actually…, the WriteToReply blog.

It seems that there’s also an official (?) Digital Britain Pageflakes dashboard too (nice to see some WTR feeds make it on there;-):

And it seems that the Cabinet Office are also using Netvibes, along with a whole host of feed powered goodness on a public dashboard being used to support the Open Source, Open Standards and Re–Use: Government Action Plan:

Here’s how it’s described:

To help bring together the online debate around this Action Plan, we’ve set up a public page which contains links to blog posts, news stories and tweets about UK government, open source and open standards. If you write about this online, please use the tag #ukgovOSS to help us find your comment.

Although the ukgovOSS Action Plan was only published yesterday, we’ve already re-published it on WriteToReply: WriteToReply: Open Source, Open Standards and Re–Use: Government Action Plan., which means there’s lots of lovely feed goodness, an e-book version of the Action Plan, and so on.

I also popped up a quick Netvibes demo tab:

This includes a full feed (via a feed reverser pipe) of the document content – so you can read it within the Netvibes context:

along with separate section level comment feeds pulled in from the WriteToReply website.

As soon as we’ve settled on some “tab patterns”, we’ll start publishing tabs to an official WriteToReply Netvibes page.

We’ve also started talking to the Cabinet Office folks about how we can work with them as part of their outreach site.

One possible way forward is for us to syndicate comments, possibly directly to the Cabinet Office Netvibes page. We already have a sort of precedent for this – WriteToReply comments on the Digital Britain Interim Report are being syndicated on the official Have Your Say: Digital Britain website:

Not bad progress for a couple of weeks worth, methinks?!

Many Eyes Wiki Dashboard – Online Visualisation Tools That Feed From Online Data Sources

Aren’t blog comments wonderful things? Today, I learned from a comment by Nicola on Visualising Financial Data In a Google Spreadsheet Motion Chart that Many Eyes can now be used to visualise live data via Many Eyes Wikified.

Wikified has apparently been in beta for a month or two (somehow I missed it…) but it was launched as a public service earlier this week: Many Eyes Wikified now open to the public:

Many Eyes Wikified is a “remix” of Many Eyes, using a wiki markup syntax to enable you to easily edit datasets and lay out visualizations side-by-side.

It also functions just like a normal wiki: you can collaboratively edit pages, add explanations or documentation to your visuals, see a page’s edit history, and revert changes.

Unlike a normal wiki, you can embed content from your blog or other data source within Wikified and visualize it. You can also embed the content you make in Wikified elsewhere, just like you can in Many Eyes.

I have to admit to hitting a few, err, issues with Many Eyes wikified whilst playing with it on an old Mac, but the promise is just, like, awesome, dude…

So what’s in store?

First up, you can add data to a page by simply copying and pasting a CSV table into it. So far, so Many Eyes – except that the page where you paste your content is actually a wiki page – so you can have all sorts of explanatory text in the page as well.

What’s really useful, though – and something I’ve been wanting for some time – is the ability to pull live data into the wiki page from another online source.

So far I’ve only tried pulling in CSV data from a Google spreadsheet, but as that seems to work okay, I assume pulling in CSV data from a Yahoo! pipe, or DabbleDB database should work too.

(I’m not sure if Many Eyes Wikified will pull in other data types too, such as TSV? Please add a comment to this post if you find out…)

Once you have a data page defined, you can call on that data from a visualisation within another page. This is where I hit a wobbly… I could create a page, and get a stub for the visualisation okay:

And I got the link that let me fire up the visualisation editor:

And I even got the viz editor:

You’ll notice that the data table has been pulled in, with the ability to set the data type for each column, and a toolbar is provided that lets you select the desired Many Eyes style visualisation type – wtih no typing and no programming required…:-)

(However, when I tried to change the visualisation type on my 10.4 OS/X Mac, I just got thrown back to the wikified home page…:-(

Anyway – the promise is there, and from examples like Nicola’s dashboard, it seems as if other people have been coping fine with the visualisation editor…

…which brings me neatly to the idea of the Wikified dashboards…

Many Eyes Wikified allows you to define “dashboards”, which are essentially URI path namespaces within which you can collect a series of separate pages. I’m not sure if you can assert ownership or edit privileges over dashboards, though? At the moment, it looks as if all pages are editable by anyone, in true public/open wiki style…

So to sum up, Many Eyes visualization tools are now available as endpoints for wholly online data mashups. May the fun begin…

QR Payments

Over dinner one evening at Dev8D, we fell to chatting about payment mechanisms in restaurants, and how the credit card payment model requires you to hand over your card so that it can read in a third party carder reader – that is, a device that is not under your control.

How much easier it would be if you could be handed your bill with a QR-code attached, which, when scanned, created a Paypal style payment that you could pay via a client on your phone. That is, your phone could become the payment appliance; the transaction is exectued on your mobile phone, using your PayPal account. A web-enabled till could then be used to confirm that the payment had been made.

Easy – and probably hack togetherable via the PayPal or Amazon Flexible Payments API?

For example, you could on the fly create a short-lived web page detailing the bill with a PayPal or Amazon “Pay now” button on it (or ideally, a mobile payments site, such as PayPal’s Mobile Checkout); generate a URL to the page in the form of a QR code; let the user grab the URL with their phone and go to the appropriate payment page on Paypal or Amazon Payments. Job done?

PS it seems there’s probably a patent or two out there already trying to lay claim to this sort of idea, such as this one for a Distributed Payment System and Method.

Which raises a question for me. Patents allow invents a period of grace to recoup expenses incurred during a process of invention. So if you can easily hack a solution together using bits of string and RESTful APIs you can find scattered around the web, what is it that actually merits the right to protection?

PS and lo, it came to pass… Now There’s Even an App That Lets You Pay for Coffee at Starbucks. See also Starbucks Launches First Dedicated iPhone App for Stored-Value Cards for screenshots.

Teaching Round the Issues on WriteToReply

No time for blogging properly at the mo – too much crappy crap “work” crappy crap to do – but looking through some recent comments on WriteToReply just now, I saw this one:

a trackback from a blog post – Money Programme – Media Revolution: Tomorrow’s TV – that reviews a recent episode of the Money Programme that “explains the importance of the formula in raising money and the reduced role of direct TV production funding as a proportion of costs”, an issue that is also touched upon in Digital Britain – The Interim Report.

The post links to the actual programme, which you can view (at the moment) on iPlayer: Money Programme – Media_Revolution: Tomorrows TV (on iPlayer)

(I’m not sure if this one is an OU co-pro, too?)

This put me in mind of Trackforward – Following the Consequences with N’th Order Trackbacks and Trackbacks, Tweetbacks and the Conversation Graph, Part I where I’d started thinking about the “link context” around web content.

So I’m wondering – would it make sense to have someone doing some “gardening” around the report, looking to see if there is content – such as the Money Programme episode referred to above – that could be linked to and used to help people make sense of the issues raised by things like the Digital Britain report, or “educate them” in the issues, even?!

That is, as well as using WriteToReply as a place where people can comment back on reports, could we also find ways of using it as a resource that helps people learn about the issues raised by the report, whether or not they want to comment back?

Using Dabble DB in an Online Mashup Context

So it seems no-one really saw why I got so excited by Dabble DB doing the linked data thing with data tables…

…so here’s an example…

First of all, importing some data via copy-and-paste:

…and we commit it:

All so simple, right…

So let’s pull some other data in from somewhere else; as CSV from a Google spreadsheet, perhaps?

(Note that the spreadsheet could have itself imported the data by scraping a table or list from an HTML page, or grabbing it via webservice with a RESTful API.)

So we import it:

…and commit it:

I’m not sure what the cacheing/refresh policy in Dabble DB is? For example, if the Google spreadsheet data changes, will Dabble keep up with the changes, and how often? (Maybe someone from Dabble DB could post a comment to clarify this?;-)

And finally, we grab data for the third table by screenscraping a table from an HTML page – this page:

Give it the URL:

Select the table:

…and commit it:

So now I have the the tables, by different means, that I used in the previous demo.

If I do some table linking in a similar way to the previous demo, I can get a table that lists grants awarded to different HEIs, along with their postcodes. (This doesn’t actually use the HTML table scraped data, but another mashup could…I could have added the Government Office Region(-ish) data to the table, for example.)

So just to be clear, here: this table is made up from columns from two separate tables. The JISC project data comes from one table, the HEI postcode location from another. The HEI homepage URI is common to both original data tables and is used to key the combined table.

And then I can export the data…

…and shove it into a pipe – using CSV, I think?

Then we can filter on just the HEIs that have been awarded grants, and have been geocoded to somewhere in the UK:

And we can get a map:

…and the KML, geo-RSS etc…

… and maybe take the JSON output from the pipe and use it to drive a proportional symbol map, showing the number of projects awarded to each institution, for example…

In the same way that Yahoo Pipes lets you do crazy stuff with lists, so Dabble DB lets you get funky with data tables… What’s not to like (except the lack of regular expressions for data cleaning, maybe…?;-)

So there we have it:

• some cut and pasted data in one table (HE location data), and a CSV imported table from a Google spreadsheet (the JISC project allocation data); (the HTML table scraped data is superfluous in this example);
• linked tables in Dabble DB to reconcile the data in the two tables;
• the mashed data table then gets exported from Dabble DB as CSV into a Yahoo pipe;
• the pipe geocodes the postcode location data for each HEI and exports the geo-coded feed as JSON;
• some Javascript in an HTML page pulls in the JSON, and plots proportional symbols on a Google map where the size of the symbol is proportional to the number of projects awarded to each HEI.

Job done, hopefully ;-)

PS I’ve started reflecting a little on how I pull these mashup things together, and I think it’s a bit like chess… I’ve completely assimilated various patterns or configurations of particular data representations and how they can be processed that let me “see several moves ahead”. And in messing around with Dabble DB, it’s like I’ve just learned a new configuration, like a pin or a fork, that I didn’t really appreciate for; but now it’s something I “get”, and something I can look for, something that may be “several moves ahead”, whenever I get an urge to have a tinker… And that’s why I think this post, and the previous one on the topic, are maybe gonna be important if you want to keep up over the coming months…;-) Does that make sense…?

PPS @dfflanders and Ross, if you’re reading… being able to table or list scrape from HTML (so no embedded tables), or grab *simple* XML feeds into Google spreadsheets is one one way of making data available. Fixing on some canonical URIs in a standard format for HEI homepages would also be a start… (EPSRC uses different – valid, but maybe deprecated? – URIs for homepages compared to the URIs listed in the scraped HERO database, for example? I’m not sure what sort of HEI homepage URIs JISC uses… the one that the HEIs use themselves would be a start?)

Creating Your Own Results Charts for Surveys Created with Google Forms

Just before Christmas, I volunteered to put together a questionnaire for a course on ICT (T209) that would informally and anonymously collect information from students about their mobile phone usage; the idea being that we could run similar surveys in the follow on course (T215 Communication and information technologies) in future years and give a students’ eye view of trends in behaviour around network connectivity and mobile devices; (the courses have large populations, so the results are potentially statistically significant).

The tool I suggested was Google Forms, partly because it’s likely to have some sort of longevity (for the number of respondents we expect, we would have to pay to make use of something like SurveyMonkey), partly because I couldn’t find a nice locally hosted survey tool (I did ask…).

Creating forms is easy, as this mini-tutorial suggests: Introduction to Google Forms.

As you would expect, different question types are possible:

One really nice feature of Google Forms – experimental at the time of writing – is an automatically generated summary results form, that where possible provides chart-based summary views for each question in the original form.

However, to view the summary charts (at the moment at least) you need to sign in with a Google account… which was one step too far for the course team who wanted the survey…

So the workaround I came up with was to create my own charts to display the results, using the data collected from the form, which is stored in a spreadsheet document.

- raw data:

- self-created charts:

(The results differ because the screenshots were captured at different times. The mix of chart styles is to provide groundwork for potential exercises regarding effective presentation and appropriate chart style selection for different sorts of data.)

So – how do you create your own results? I found the trick here:

I have a form on a spreadsheet and have a colum that is populated from some checkboxes in the form. This means I have a “, ” separated list of answers for each cell in this column.

COUNTIF(column,value) seems to only count rows where the only checkbox selected was value, not where other values are also in the list in the cell.

What function(s) can I use to properly count values as produced by the checkbox option from a form in spreadsheets?

Answer: (from user ahab)
Try this method:
B2: apples,grapes,pears [results in cell B2, etc.]
B3: oranges,apples,grapes,pears
B4: apples,pears

To find the number of times “grapes” were among the selected fruits:
=ARRAYFORMULA(COUNT(IFERROR(FIND(“grapes”;B2:B))))
As you may be using this formula on another sheet than the sheet the submitted data is on, don’t forget to prepend the range B2:B with a sheet name like this: ‘Sheet1′!B2:B

[The formula works as follows:]
=ARRAYFORMULA(COUNT(IFERROR(FIND(“grapes”;B2:B))))
ARRAYFORMULA this function allows array expressions and functions -which are cell-only oriented – to be applied to ranges; in this case to enable FIND to be applied to each row in the range B2:B . [That is, from cell B2 to the end of column B.]
COUNT this function counts the elements of the range it is presented with – a bit as one would expect it to do ;)
IFERROR this function evaluates its first parameter – if the first parameter evaluates OK this value is returned – if the first parameter returns an error an -optional- second parameter to IFERROR is evaluated. In this case there is no second parameter so on error nothing is returned.
FIND(“grapes”;B2:B) this function is the first and only parameter to IFERROR FIND looks for “grapes” in each row of the range B2:B – but is only able to do so because of the ARRAYFORMULA wrap. Without it FIND would only be able to search the first element in the range B2:B ; i.e. only in B2 only. When FIND doesn’t find “grapes” it produces an error.
So the range – allowed by the ARRAYFORMULA wrap – FIND finally produces would be something like:
8
16
#NA
Because of the IFERROR function wrap the #NA error is filtered out, producing the range
8
16
The COUNT function counts these elements: 2

The approach I took to counting the results was as follows – itemise the results options by copy and pasting the results options for each question from the survey form, and then count the number of the occurrences using the formula described above, with the filter term pulled in from the corresponding label:

Just by the by, there were a couple of gotchas – for example, for questions with numerical answers, “0″ matched empty cells as well as actual “0″ answers – so I renamed “0″ as “Zero”.215

Mash/Combining Data from Three Separate Sources Using Dabble DB

Over dinner with friends a couple of nights ago, I was asked how I typically approach problem solving tasks. Thinking about it, it’s a bottom-up AND top-down approach where I attack both ends of the problem (the “what I’ve got now” end and the “ultimate vision”) at the same time, in the hope that the tiny steps taken from each end meet up somewhere in the middle…

So for example, in the dev8D Dragon’s Den I mentioned the desire to put together a thematic choropleth map depicting the funding that’s going into different UK Government office regions as a result of JISC or EPSRC project awards. Here’s how I’ve started to work out how to do that…

(What follows gets a little involved at times, so the main trick to look out for is how to create a single data table by mashing together data from three separate data tables.)

At one end, is the output surface. A quick scout around turned up no flash components or KML overlays I could use on Google maps or ThematicMapping (ffs why can’t National Statistics make some free warez available???) so I opted for the amMap interactive map instead.

To plot the map, I need to be able to sum the value of project grants over lead HEIs within particular GORs (got that?;-) So where’s the data?

All over the place, that’s where…

• EPSRC Support By Organisation shows the total amount of current project funding awarded to each HEI by EPSRC;

  

  Hmm, no GOR, no geolocation data… Which means I need a mapping from HEI to GOR…

• …but the closest I can find is a listing of the postcodes of each HEI: HERO screenscraper, and even that’s a scrape of another service…

  

  (Thanks @lesteph;-)

• and finally, here’s a mapping from postcode areas to GORs: postcode area lookup table.

  

  There’s a warning though: please note “regions” were recorded for my own visual aid and are NOT an attempt to tie in with current UK Administrative Regions.. Hmm – okay – add that one to the caveats/risk assessment. If the maps turns out very wrong, that’s EPSRC’s problem, right, for not making the data available in a clean way?!;-)

Okay, so those are the data sources: one contains HEI names and project funding data, one contains HEI names, location data (well, postcodes) and homepage URIs, and one contains mappings from postcode towns to UK regions (which loosely relate, possibly, to GORs).

Now at this point point I’ve already decided that I want to try use Dabble DB to somehow conflate the data from these three separate sources (though I’m not totally sure how… it’s just something I seem to remember from somewhere and somewhen a long time ago that Dabble DB supports if there are common fields – and matching strings – across different data tables).

Getting the data into Dabble DB is a copy and paste operation, but I’m going to take an intermediate step, highlighting and copy the tables from the separate web pages and pasting them into a Google spreadsheet. Why? Because I already know that this works and it’ll also let me cast an eye over the data to make sure it looks about right.

Looking at the HEI names from EPSRC and the HERO screenscrape, they don’t really match though, which means that Dabble DB won’t be able to use HEI names to idenify common rows in the HE location and EPSRC project tables. However, the HERO screenscrape page does have the HEI homepage URI, and a look beneath the “Go to Site” link on the EPSRC page shows that those links point to the HEI homepage…

…which means I should be able to link items in the EPSRC projects listing to items in the HEI location table by virtue of common homepage URIs.

A quick Javascript bookmarklet hack using this bookmarklet:

javascript:(function (){var a=document.getElementsByTagName(‘a’); for (var i=0;i<a.length;i++){if (a[i].firstChild){var n=a[i].firstChild.nodeValue; if (n) if ((n.match(“site”))) a[i].innerHTML=a[i].href;}};})()

and the URIs are exposed, so I can copy and paste the table and drop it into a spreadsheet, with the HERO data and postcode/region data in separate sheets.

A quick look over the URIs from both sources in the spreadsheets shows minor differences though – some URIs end with a “/” and others don’t (there are also a few broken scrapes that I tidy by hand); now if Dabble DB uses strict string matching to relate data in one table to data in another table (which I’d guess is likely) then missed matches will presumably occur?

So just to be safe, we need a data cleaning stage. To do this, I copy the data from the URI column in each spreadsheet, drop it into my TextWrangler text editor, and just clean up all the URIs so they end with a trailing / by searching for \.uk$ and replacing it with .uk/

Then I copy the URIs from the text editor and past them back into the appropriate column in the appropriate spreadsheet.

Looking at the postcode/GOR table, I need to get one or two letter postal town identifiers from the HEI postcodes, so to do this I copy the postcode column from the spreadsheet, and paste it into my text editor. This time I do a regular expression powered search and replace using this regexp: ([A-Z]+).* and replacing with \1

So now I have three spreadsheets on Google docs, which I can scan by eye to make sure they look okay, then easily copy and paste into separate tables (known as separate categories) in the same Dabble DB project, like this:

- the EPSRC data:

- HERO screenscrape data:

- and the postcode/region mapping data:

Now for the fun part; each of the above tables is a separate category, with separate column fields, in a Dabble DB project. It is possible to link a column with a similar column in another category, and consequently “pair” similar items in different tables. (So a column containing a particular URI, for example, in a row in one table/category can be related to a particular row in a particular column in another category/table, if the corresponding cell there contains the same URI (Dabble DB handles the actual pairings, you just have to link the columns).

So playing blind, I linked the URI column in the EPSRC category with a new category, which I called Meta:

This created a new table/category – Meta – with a couple of columns: a ‘Name” column, containing the URIs, and a column that linked back to corresponding entries in the EPSRC project category.

And then I did the same linking for the URI column in the HEI Location table/category, which automatically added another column in the Meta table that linked across to rows in the corresponding HEI Location table:

In the Meta category view, I can now add additional columns that are derived from columns in the other, linked tables. So for example, I can add a derived column corresponding to the value of project grants that is pulled in from the linked EPSRC projects column:

So my Meta table/category now looks like this:

Which is pretty clever I think..? ;-)

But then it gets more so… Suppose I link the Postcode town column from the HEI location table with the Postcode/Regional mapping table:

If you’ve been keeping up, you might now expect the UK HEI to be linked to from the Postcode/Region table, which it is:

But the link is symmetrical… and if one category is linked to a second category that is in turn linked to a third category, the columns from the first category can be used as derived columns in the second and the third category…

…which means in the Meta category, I can pull in columns derived from the Postcode/Region category via the HEI location category, first by grabbing the postcode town column into Meta:

To give this:

Then pull in a further derived field from the postcode town column from the Postcode/Region category:

And so now we have a rather more complete Meta category view containing linked items from all three tables (one of which is actually linked indirectly via one of the others):

Clever, eh??? So now I know how to annotate data in one table using data from another table if the two tables each have a column that contains similar data :-)

Okay, so now I have a table that contains rows that contain both project funds and UK regions info – so now I’m in a position to calculate the total amount of funds flowing into each region and then plot them on the thematic map…

…but this post is already way too long, so that’ll have to be for another day…

(Plus I’m not totally sure how to do it yet… and Mission Impossible is just starting (this is a scheduled post…;-)

What Are JISC’s Funding Priorities?

I’ve just got back home from a rather wonderful week away at the JISC Developer Happiness Days (dev8D), getting a life (of a sort?!;-) so now it’s time to get back to the blog…

My head’s still full of things newly learned from the last few days, so while I digest it, here’s a quick taster of something I hope to dabble a little more with over the next week for the developer decathlon, along with the SplashURL.net idea (which reminds me of my to do list…oops…)

A glimpse of shiny things to do with JISC project data (scraped from Ross’s Simal site… (see also: Prod).

Firstly, a Many Eyes tag cloud showing staffing on projects by theme:

Secondly, a many Eyes pie chart showing the relative number of projects by theme:

As ever, the data may not be that reliable/complete, because I believe it’s a best effort scrape of the JISC website. Now if only they made their data available in a nice way???;-)

Following a session in the “Dragon’s Den”, where I was told by Rachel Bruce that these charts might be used for good as a well as, err, heckling, I guess, Mark van Harmalen that I should probably pay lip service to who potential users might be, and Jim Downing’s suggestion that I could do something similar for research council projects, I also started having a play with data pulled from the the JISC website.

So for example, here’s a treemap showing current EPSRC Chemistry programme area grants >2M UKP by subprogramme area:

And if you were wondering who got the cash in the Chemistry area, here’s a bubble chart showing projects held by named PIs, along with their relative value:

If you try out the interactive visualisation on Many Eyes, you can hover over each person bubble to see what projects they hold and how much they’re worth:

PS thanks to Dave Flanders and all at JISC for putting the dev8D event on and managing to keep everything running so smoothly over the week:-) Happiness 11/10…