Seeing these two copies of what is apparently the same speech, I started wondering:

a) which is the “best” source to reference?
b) how come the Guardian doesn’t add a disclaimer about the provenance of, and link, to the DfE version? [Note the disclaimer in the DfE version - "Please note that the text below may not always reflect the exact words used by the speaker."]
c) is the Guardian version an actual transcript, maybe? That is, does the Guardian reprint the “exact words” used by the speaker?

And that made me think I should do a diff… About which, more below…

Before that, however, here’s a quick piece of reflection on how these two things – the reinvention of the the IT curriculum, and the provenance of, and value added to, content published on news and tech industry blog sites – collide in my mind…

So for example, I’ve been pondering what the role of journalism is, lately, in part because I’m trying to clarify in my own mind what I think the practice and role of data journalism are (maybe I should apply for a Nieman-Berkman Fellowship in Journalism Innovation to work on this properly?!). It seems to me that “communication” is one important part (raising awareness of particular issues, events, or decisions), and holding governments and companies to account is another. (Actually, I think Paul Bradshaw has called me out on that, before, suggesting it was more to do with providing an evidence base through verification and triangulation, as well as comment, against which governments and companies could be held to account (err, I think? As an unjournalist, I don’t have notes or a verbatim quote against which to check that statement, and I’m too lazy to email/DM/phone Paul to clarify what he may or may not have said…(The extent of my checking is typically limited to what I can find on the web or in personal archives…which appear to be lacking on this point…))

Another thing I’ve been mulling over recently in a couple of contexts relates to the notion of what are variously referred to as digital or information skills.

The first context is “data journalism”, and the extent to which data journalists need to be able to do programming (in the sense of identifying the steps in a process that can be automated and how they should be sequenced or organised) versus writing code. (I can’t write code for toffee, but I can read it well enough to copy, paste and change bits that other people have written. That is, I can appropriate and reuse other people’s code, but can’t write it from scratch very well… Partly because I can’t ever remember the syntax and low level function names. I can also use tools such as Yahoo Pipes and Google Refine to do coding like things…) Then there’s the question of what to call things like URL hacking or (search engine) query building?

The second context is geeky computer techie stuff in schools, the sort of thing covered by Michael Gove’s speech at the BETT show on the national ICT curriculum (or lack thereof), and about which the educational digerati were all over on Twitter yesterday. Over the weekend, houseclearing my way through various “archives”, I came across all manner of press clippings from 2000-2005 or so about the activities of the OU Robotics Outreach Group, of which I was a co-founder (the web presence has only recently been shut down, in part because of the retirement of the sys admin on whose server the websites resided.) This group ran an annual open meeting every November for several years hosting talks from the educational robotics community in the UK (from primary school to HE level). The group also co-ordinated the RoboCup Junior competition in the UK, ran outreach events, developed various support materials and activities for use with Lego Mindstorms, and led the EPSRC/AHRC Creative Robotics Research Network.

At every robotics event, we’d try to involve kids and/or adults in elements of problem solving, mechanical design, programming (not really coding…) based around some sort of themed challenge: a robot fashion show, for example, or a treasure hunt (both variants on edge following/line following;-) Or a robot rescue mission, as used in a day long activity in the “Engineering: An Active Introduction” (TXR120) OU residential school, or the 3 hour “Robot Theme Park” team building activity in the Masters level “Team Engineering” (T885) weekend school. [If you're interested, we may be able to take bookings to run these events at your institution. We can make them work at a variety of difficulty levels from KS3-4 and up;-)]

Given that working at the bits-atoms interface is where the a lot of the not-purely-theoretical-or-hardcore-engineering innovation and application development is likely to take place over the next few years, any mandate to drop the “boring” Windows training ICT stuff in favour of programming (which I suspect can be taught in not only a really tedious way, but a really confusing and badly delivered way too) is probably Not the Best Plan.

Slightly better, and something that I know is currently being mooted for reigniting interest in computing, is the Raspberry Pi, a cheap, self-contained, programmable computer on a board (good for British industry, just like the BBC Micro was…;-) that allows you to work at the interface between the real world of atoms and the virtual world of bits that exists inside the computer. (See also things like the OU Senseboard, as used on the OU course “My Digital Life” (TU100).)

If schools were actually being encouraged to make a financial investment on a par with the level of investment around the introduction of the BBC Micro, back in the day, I’d suggest a 3D printer would have more of the wow factor…(I’ll doodle more on the rationale behind this in another post…) The financial climate may not allow for that (but I bet budget will manage to get spent anyway…) but whatever the case, I think Gove needs to be wary about consigning kids to lessons of coding hell. And maybe take a look at programming in a wider creative context, such as robotics (the word “robotics” is one of the reason why I think it’s seen as a very specialised, niche subject; we need a better phrase, such as “Creative Technologies”, which could combine elements of robotics, games programming, photoshop, and, yex, Powerpoint too… Hmm… thinks.. the OU has a couple of courses that have just come to the end of their life that between them provide a couple of hundred hours of content and activity on robotics (T184) and games programming (T151), and that we delivered, in part, to 6th formers under the OU’s Young Applicants in Schools Scheme.

Anyway, that’s all as maybe… Because there are plenty of digital skills that let you do coding like things without having to write code. Such as finding out whether there are any differences between the text in the DfE copy of Gove’s BETT speech, and the Guardian copy.

Copy the text from each page into a separate text file, and save it. (You’ll need a text editor for that..) Then, if you haven’t already got one, find yourself a good text editor. I use Text Wrangler on a Mac. (Actually, I think MS Word may have a diff function?)

The difference’s all tend to be in the characters used for quotation marks (character encodings are one of the things that can make all sorts of programmes fall over, or misbehave. Just being aware that they may cause a problem, as well as how and why, would be a great step in improving the baseline level understanding of folk IT. Some of the line breaks don’t quite match up either, but other than that, the text is the same.

Now, this may be because Gove was a good little minister and read out the words exactly as they had been prepared. Or it may be the case that the Guardian just reprinted the speech without mentioning provenance, or the disclaimer that he may not actually have read the words of that speech (I have vague memories of an episode of Yes, Minister, here…;-)

Whatever the case, if you know: a) that it’s even possible to compare two documents to see if they are different (a handy piece of folk IT knowledge); and b) know a tool that does it (or how to find a tool that does it, or a person that may have a tool that can do it), then you can compare the texts for yourself. And along the way, maybe learn that churnalism, in a variety of forms, is endemic in the media. Or maybe just demonstrate to yourself when the media is acting in a purely comms, rather than journalistic, role?

PS other phrases in the area: “computational thinking”. Hear, for example: A conversation with Jeannette Wing about computational thinking

PPS I just remembered – there’s a data journalism hook around this story too… from a tweet exchange last night that I was reminded of by an RT:

josiefraser: RT @grmcall: Of the 28,000 new teachers last year in the UK, 3 had a computer-related degree. Not 3000, just 3.
dlivingstone: @josiefraser Source??? Not found it yet. RT @grmcall: 28000 new UK teachers last year, 3 had a computer-related degree. Not 3000, just 3
josiefraser: That ICT qualification teacher stat RT @grmcall: Source is the Guardian http://www.guardian.co.uk/education/2012/jan/09/computer-studies-in-schools

I did a little digging and found the following document on the General Teaching Council of England website – Annual digest of statistics 2010–11 – Profiles of registered teachers in England [PDF] – that contains demographic stats, amongst others, for UK teachers. But no stats relating to subject areas of degree level qualifications held, which is presumably the data referred to in the tweet. So I’m thinking: this is partly where the role of data journalist comes in… They may not be able to verify the numbers by checking independent sources, but they may be able to shed some light on where the numbers came from and how they were arrived at, and maybe even secure their release (albeit as a single point source?)

I’ve dabbled in this area before, though not addressing this question exactly, using Yahoo Pipes to find what hashtags are being used around a particular search term (Searching for Twitter Hashtags and Finding Hashtag Communities) or by members of a particular list (What’s Happening Now: Hashtags on Twitter Lists; that post also links to a pipe that identifies names of people tweeting around a particular search term.).

So what would we need a pipe to do that finds terms surrounding a twitter hashtag?

Firstly, we need to search on the tag to pull back a list of tweets containing that tag. Then we need to split the tweets into atomic elements (i.e. separate words). At this point, it might be useful to count how many times each one occurs, and display the most popular. We might also need to generate a “stop list” containing common words we aren’t really interested in (for example, the or and.

So here’s a quick hack at a pipe that does just that (Popular words round a hashtag).

For a start, I’m going to construct a string tokeniser that just searches for 100 tweets containing a particular search term, and then splits each tweet up in separate words, where words are things that are separated by white space. The pipe output is just a list of all the words from all the tweets that the search returned:

You might notice the pipe also allows us to choose which page of results we want…

We can now use the helper pipe in another pipe. Firstly, let’s grab the words from a search that returns 200 tweets on the same search term. The helper pipe is called twice, once for the first page of results, once for the second page of results. The wordlists from each search query are then merged by the union block. The Rename block relabels the .content attribute as the .title attribute of each feed item.

The next thing we’re going to do is identify and count the unique words in the combined wordlist using the Unique block, and then sort the list accord to the number of times each word occurs.

The above pipe fragment also filters the wordlist so that only words containing alphabetic characters are allowed through, as well as words with four or more characters. (The regular expression .{4,} reads: allow any string of four or more ({4,}) characters of any type (.). An expression .{5,7} would say – allow words through with length 5 to 7 characters.)

I’ve also added a short routine that implements a stop list. The regular expression pattern (?i)\b(word1|word2|word3)\b says: ignoring case ((?i)),try to match any of the words word1, word2, word3. (\b denotes word boundary.) Note that in the filter below, some of the words in my stop list are redundant (the ones with three or fewer characters. Remember, we have already filtered the word list to show only words of length four or more characters.)

I also added a user input that allows additional stop terms to be added (they should be pipe (|) separated, with no spaces between them). You can find the pipe here.

Previously this serialised chapter for the forthcoming book Investigative Journalism: Dead or Alive? looked at new business models surrounding investigative journalism and online investigative journalism as a genre. This third and final part looks at how changing supplies of information change the context within which investigative journalism operates.

What next for investigative journalism in a world of information overload?

But this identity crisis does highlight a final, important, question to be asked: in a world where users have direct access to a wealth of information themselves, what is investigative journalism for? I would argue that it comes down to the concept of “uncovering the hidden”, and in exploring this it is useful to draw an analogy with the general journalistic idea of “reporting the new”.

Trainee journalists sometimes see “new” in limited terms – as simply what is happening today. But what is “new” is not limited to that. It can also be what is happening tomorrow, or what happened 30 years ago. It can be something that someone has said about an “old story” days later, or an emerging anger about something that was never seen as “newsworthy” to begin with. The talent of the journalist is to be able to spot that “newness”, and communicate it effectively.

Journalism typically becomes investigative when that newness involves uncovering the hidden – and that can be anything that our audience couldn’t see before – it could be a victim’s story, a buried report, 250,000 cables accessible to 2.5 million people, or even information that is publicly available but has not been connected before (“the hidden” – like “the new” is, of course, a subjective quality, dependent on the talent of a particular journalist for finding something in it – or a way of seeing it – that is newsworthy).

So what if all of the investigative journalist’s material was public: documents, sources (witnesses, experts, victims, actors in the story), and information? The role of the investigative journalist would perhaps be as follows:

• to make the “hidden” (to their audience) “visible”;
• to hold power to account;
• to make connections;
• to verify;
• to test hypotheses – the why and how of journalism.

This doesn’t sound very different to how we see the role now. Of course, in reality, all of the investigative journalist’s material will most likely not be online, so if we leave that thought experiment behind we can add other roles to acknowledge this:

• to make the invisible visible (i.e. digitising offline material, from paper documents and witness accounts to the “invisible web” of databases);
• to make the disconnected connected: publishing information in such a way that others can make further connections with other sources of data;
• to identify gaps in information – and fill them.

These are all, in fact, “making the hidden visible” in another form, whether they fill those gaps with material that is in the public domain or which only exists in a single witness’s diary.

Narrative and authority

The role of a journalist in creating a narrative comes through strongly here: hypotheses are about narratives; making connections is about making narratives. Narratives are important – they help people find their place in a story – but onlline investigations can have multiple narratives and different users can find different entry points across those.

The other role that comes through strongly is institutional: holding power to account involves (but does not require) being in a position of power to do so; verification involves (but does not require) the stamp of institutional “due process”.

My own experience with Help Me Investigate suggests that these two roles remain important bases for journalism as a profession: in crowdsourced journalism, “writing the story up” did not particularly appeal to non-journalists (the story was in their minds already) – only journalists wanted to do that. And it took an established media outlet to get official reaction.

This is not to suggest that only journalists can “have impact” as was mentioned at the conference – there are plenty of examples of groundswells of opinion online instigating media coverage: Memogate is perhaps the best known example. But this does not mean we need journalists so much as it means that we need publishers and broadcasters. There is a difference.

No excuses

So what does this mean for future investigative journalists online? Firstly, we may have to accept that many parts of investigative journalism will lose their air of mystery: from gathering information to publishing and distributing it, there are now dozens of new opportunities for the aspiring investigator: FoI tools such as WhatDoTheyKnow; free data gathering and interrogation tools such as Yahoo! Pipes and OutWit Hub, leak-hosting sites, and tools to combine and clean data such as Google Refine and SQLite. I see students now able to do work that would baffle many full time journalists.

That’s no bad thing: distinguishing investigative journalism from other types of reporting was always problematic: “All journalism should be investigative” is a near-cliché because it goes to the heart of what we should be doing as journalists. Now we have the opportunity to act on that sentiment.

Journalists will be – and already are – more collaborative, learning how to work with and across networks. The internet has made it possible to separate the “investigative” from the “journalism”: students, bloggers, activists, and anyone else with a burning question can begin to investigate it. They can raise questions openly with thousands of others online, submit FoI requests at the click of a button or analyse datasets and documents with free tools, regardless of whether or not they are employed as a journalist. The vast majority do not want to be a journalist. What they want are answers.

Their efforts can have value regardless of their job title. The role of investigative journalism – online, and then in print and broadcast – will increasingly be to build on their work: to make it visible; to verify it; to connect it to other information; to hold power to account over it. (If that threatens your romantic idea of the “individual genius” and makes you feel somehow less important, then you can take comfort in calling those people mere “sources” and yourself a “proper journalist”. It doesn’t matter what you call it as long as journalism gets done – although you may alienate potential sources by doing so in public.)

Secondly, journalists will need to carefully judge how and when to tell different parts of their story. The medium and channel of presentation is one new element of judgement – but they will also have to balance publicness against privateness at every stage, and judge how either might improve or speed up their work, and increase its impact and reach. There are no easy answers to these questions.

Finally, aspiring investigative journalists will no longer wait for a job title – or even a job – to begin investigating. At the conference that inspired this book two journalism students were asked how they saw the problems facing investigative journalism. The first felt that institutional restrictions on time or money should not be an excuse for journalists failing to investigate important questions in their own time; the second felt that people no longer needed institutional validation to investigate something: they could publish on a blog and build an audience that way.

These are hugely encouraging sentiments for anyone who worries about the state of modern journalism. Of course, some people will always look for excuses, but thanks to the access to information, documents, sources, collaborators and tools that the internet presents, we have fewer excuses to make. Not only that, journalists and aspiring journalists have the opportunity of a generation: to define the shape of investigative journalism to come.

I’ve been meaning to write a post for some time breaking down all the habits and hacks I’ve acquired over the years – so this month’s Carnival of Journalism question on ‘Hacking your journalism workflow’ gave me the perfect nudge.

Picking those habits apart is akin to an act of archaeology. What might on the surface look very complicated is simply the accumulation of small acts over several years. Those acts range from the habits themselves to creating simple shortcuts and automated systems, and learning from experience. So that’s how I’ve broken it down:

1. Shortcuts

Shortcuts are such a basic part of my way of working that it’s easy to forget they’re there: bookmarks in the browser bar, for example. Or using the Chrome browser because its address bar also acts as a search bar for previous pages.

I realise I use Twitter lists as a shortcut of sorts – to zoom in on particular groups of people I’m interested in at a particular time, such as experts in a particular area, or a group of people I’m working with. Likewise, I use folders in Google Reader to periodically check on a particular field – such as data journalism – or group – such as UK journalists.

Getting more specific, when it comes to data journalism tasks I rely on a whole range of tools and shortcuts for cleaning and interrogating datasets: the =TRANSPOSE formula, for example, will swap a spreadsheet’s rows and columns; =VLOOKUP will copy across data from matching cells; and the free tool Google Refine will quickly identify similar entries (which may have been misspelled).

On my desktop I rely on plugins for Firefox and Chrome such as Firebug (check a page’s HTML), OutWit Hub (scrape a page), TinEye (check if an image has been used elsewhere), ErrorZilla (check for cached and older versions of a webpage), and Easy YouTube Downloader (download YouTube videos). Links to these and other useful plugins can be found at http://delicious.com/paulb/firefox

But the most frequently used shortcuts are the bookmarklets that are installed on my mobile phone browser – ‘Read Later’ (Instapaper); ‘Bookmark on Delicious’; ‘Tweet with Echofon’; ‘save on Springpad’ or Evernote; and ‘Blog on Tumblr’. These are made even more powerful through automation.

2. Automation

RSS can be a hugely useful technology when it comes to saving time and automating processes – and Delicious is the king of useful RSS feeds in this respect.

If I want to tweet a useful link as well as bookmark it, for example, I simply add the tag ‘t’ – the RSS feed for which is automatically tweeted to my account by Twitterfeed. If I want to tweet it using the @helpmeinvestig8 account I add the tag ‘hmitwt’. Webpages which I think might be useful to students on the MA in Television and Interactive Content I tag ‘tvi’ – this not only sends them to the @bcumedia_matvic account but also to an email newsletter that students receive (I use Feedburner for this). If I wanted to I could set up a Tumblr blog to automatically pull items from the RSS feed for a particular tag, too. And all of this is triggered by one click, and one tag.

The process works the other way: Packrati.us will bookmark any link you tweet in your Delicious account. And Trunk.ly automatically archives both your Delicious bookmarks and tweeted links, providing a backup search engine.

IFTTT (IF This Then That) is a new service which promises some amazing possibilities for automating processes between (currently) 32 different services, including Delicious, Google Reader, stock performances, times and dates, emails, phone calls and any RSS feed. I’ve been using it to bookmark anything I share on Google Reader, but I’m on the lookout for other uses.

For other tasks the Firefox plugin iMacros can automate web-based actions so you don’t have to repeat them, while Automator on the Mac will do the same for computer-based actions. For links to these and IFTTT see http://www.delicious.com/paulb/automation+tools

3. Habits

For all the above it is ultimately up to you to set balls in motion, and here I think establishing habits is key. In particular, bookmarking is one habit that I find saves me more time than anything else.

Every morning I check my RSS feeds and bookmark items I think may be useful in future. Bookmarking and tagging them builds a resource that I can look to whenever I need to solve a problem, help someone, or write something quickly. So if I decide to write something on data visualisation, I already have an archive of pre-filtered material to refer to. If I need data on health, I already have several health datasets that I’ve bookmarked and tagged. And if I have a Yahoo! Pipes-related problem, I can check my bookmarks first.

Delicious is the main place that I do this – but it’s no longer the only one. My Tumblr blog is essentially a place where I bookmark multimedia and quotes – so if I need some multimedia or a choice quote, that’s where I look first.

And blogging itself is a great habit to have: it makes me remember things better, provides a space where I can re-find them, and helps me (or others) identify gaps.

4. Discipline

The final journalism hack is my most recent one – and I think something that more and more online journalists are learning too as they hit information fatigue. It’s self-discipline.

With so many sources of information, so many things to tweet, blog and bookmark, it’s easy to lose a morning in following links, tweets and feeds, and replying to emails. Having a clear idea of what you need to achieve on a particular day, and sometimes switching off other signals in order to complete it, is a hard skill to build – but an important one.

And so I try to only check email three times per day (start, midday and end). At the end of the day emails that require more time to respond go into my ‘Starred items’, and I check those and respond if I can first thing the next day.

I set limits on the time I spend checking RSS feeds, and on the number of blog posts I write.

I email longer webpages, reports and documents to my Kindle address to be read when I’m travelling.

I use the Springpad app to create ‘To Do’ items that I schedule for future days, taking them out of my head so I can focus on the here and now. And at the start of every day I go through these so that nothing is missed.

Then, I make time to switch off, to remove the phone from my hand, the laptop from my desk (it is set to switch itself off at a particular time every night), and sleep.

Here are a couple of example datasets to import into separate Google Refine projects if you want to play along, both courtesy of the Guardian data blog (pulled through the Google Spreadsheets to Yahoo pipes proxy mentioned here):

- University fees data (CSV via pipes proxy)

- University HESA stats, 2010 (CSV via pipes proxy)

We can now merge data from the two projects by creating a new column from values an existing column within one project that are used to index into a similar column in the other project. Looking at the two datasets, both HESA Code and institution/University look like candidates for merging the data. Which should we go with? I’d go with the unique identifier (i.e. HESA code in the case) every time…

First, create a new column:

Now do the merge, using the cell.cross GREL (Google Refine Expression Language) command. Trivially, and pinching wholesale from the documentation example, we might use the following command to bring in Average Teaching Score data from the second project into the first:

cell.cross("Merge Test B", "HESA code").cells["Average Teaching Score"].value[0]

Note that there is a null entry and an error entry. It’s possible to add a bit of logic to tidy things up a little:

if (value!='null',cell.cross("Merge Test B", "HESA code").cells["Average Teaching Score"].value[0],'')

Here’s the result:

Coping with not quite matching key columns

Another situation that often arises is that you have two columns that almost but don’t quite match. For example, this dataset has a different name representation that the above datasets (Merge Test C):

There are several text processing tools that we can use to try to help us match columns that differ in well-structured ways:

In the above case, where am I creating a new column based on the contents of the Institution column in Merge Test C, I’m using a couple of string processing tricks… The GREL expression may look complicated, but if you build it up in a stepwise fashion it makes more sense.

For example, the command replace(value,"this", "that") will replace occurrences of “this” in the string defined by value with “that”. If we replace “this” with an empty string (” (two single quotes next to each other) or “” (two double quotes next to each other)), we delete it from value: replace(value,"this", "")

The result of this operation can be embedded in another replace statement: replace(replace(value,"this", "that"),"that","the other"). In this case, the first replace will replace occurrences of “this” with “that”; the result of this operation is passed to the second (outer) replace function, which replaces “that” with “the other”). Try building up the expression in realtime, and see what happens. First use:
toLowercase(value)
(what happens?); then:
replace(toLowercase(value),'the','')
and then:
replace(replace(toLowercase(value),'the',''),'of','')

The fingerprint() function then separates out the individual words that are left, orders them, and returns the result (more detail). Can you see how this might be used to transform a column that originally contains “The University of Aberdeen” to “aberdeen university”, which might be a key in another project dataset?

When trying to reconcile data across two different datasets, you may find you need to try to minimise the distance between almost common key columns by creating new columns in each dataset using the above sorts of technique.

Be careful not to create false positive matches though; and also be mindful that not everything will necessarily match up (you may get empty cells when using cell.cross; (to mitigate this, filter rows using a crossed column to find ones where there was no match and see if you can correct them by hand). Even if you don’t completely successful cross data from one project to another, you might manage to automate the crossing of most of the rows, minimising the amount of hand crafted copying you might have to do to tidy up the real odds and ends…

So for example, here’s what I ended up using to create a “Pure key” column in Merge Test C:
fingerprint(replace(replace(replace(toLowercase(value),'the',''),'of',''),'university',''))

And in Merge Test A I create a “Complementary Key” column from the University column using fingerprint(value)

From the Complementary Key column in Merge Test A we call out to Merge Test C: cell.cross("Merge Test C", "Pure key").cells["UCAS ID"].value[0]

Obviously, this approach is far from ideal (and there may be more “correct” and/or efficient ways of doing this!) and the process described above is admittedly rather clunky, but it does start to reveal some of what’s involved in trying to bring data across to one Google Refine project from another using columns that don’t quite match in the original dataset, although they do (nominally) refer to the same thing, and does provide a useful introductory exercise to some of the really quite powerful text processing commands in Google Refine …

Grab Data into Google Refine as CSV from a URL (Proxied Google Spreadsheet Query via Yahoo Pipes)

Firstly, getting data into Google Refine… I had hoped to be able to pull a subset of data from a Google Spreadsheet into Google Refine by importing CSV data obtained from the spreadsheet via a query generated using my Google Spreadsheet/Guardian datastore explorer (see Using Google Spreadsheets as a Database with the Google Visualisation API Query Language for more on this) but it seems that Refine would rather pull the whole of the spreadsheet in (or at least, the whole of the first sheet (I think?!)).

Instead, I had to tweak create a proxy to run the query via a Yahoo Pipe (Google Spreadsheet as a database proxy pipe), which runs the spreadsheet query, gets the data back as CSV, and then relays it forward as JSON:

Here’s the interface to the pipe – it requires the Google spreadsheet public key id, the sheet id, and the query… The data I’m using is a spreadsheet maintained by the Guardian datastore containing UK university fees data (spreadsheet.

You can get the JSON version of the data out directly, or a proxied version of the CSV, as CSV via the More options menu…

Using the Yahoo Pipes CSV output URL, I can now get a subset of data from a Google Spreadsheet into Google Refine…

Here’s the result – a subset of data as defined by the query:

We can now augment this data with data from another source using Google Refine’s ability to import/fetch data from a URL. In particular, I’m going to use the Yahoo Pipe described above to grab data from a different spreadsheet and pass it back to Google Refine as a JSON data feed. (Google spreadsheets will publish data as JSON, but the format is a bit clunky…)

To test out my query, I’m going to create a test query in my datastore explorer using the Guardian datastore HESA returns (2010) spreadsheet URL (http://spreadsheets1.google.com/spreadsheet/ccc?hl&key=tpxpwtyiYZwCMowl3gNaIKQ#gid=0) which also has a column containing HESA numbers. (Ultimately, I’m going to generate a URL that treats the Guardian datastore spreadsheet as a database that lets me get data back from the row with a particular HESA code column value. By using the HESA number column in Google Refine to provide the key, I can generate a URL for each institution that grabs its HESA data from the Datastore HESA spreadsheet.)

Hit “Preview Table Headings”, then scroll down to try out a query:

Having tested my query, I can now try the parameters out in the Yahoo pipe. (For example, my query is select D,E,H where D=21 and the key is tpxpwtyiYZwCMowl3gNaIKQ; this grabs data from columns D, E and H where the value of D (HESA Code) is 21). Grab the JSON output URL from the pipe, and use this as a template for the URL template in Google Refine. Here’s the JSON output URL I obtained:

http://pipes.yahoo.com/pipes/pipe.run?_id=4562a5ec2631ce242ebd25a0756d6381
&_render=json&key=tpxpwtyiYZwCMowl3gNaIKQ
&q=select+D%2CE%2CH+where+D%3D21

Remember, the HESA code I experiment with was 21, so this is what we want to replace in the URL with the value from the HESA code column in Google Refine…

Here’s how we create the URLs built around/keyed by an appropriate HESA code…

Google Refine does its thing and fetches the data…

Now we process the JSON response to generate some meaningful data columns (for more on how to do this, see Tech Tips: Making Sense of JSON Strings – Follow the Structure).

First say we want to create a new column based on the imported JSON data:

Then parse the JSON to extract the data field required in the new column.

For example, from the HESA data we might extract the Expenditure per student /10:

value.parseJson().value.items[0]["Expenditure per student / 10"]

or the Average Teaching Score (value.parseJson().value.items[0]["Average Teaching Score"]):

And here’s the result:

So to recap:

- we use a Yahoo Pipe to query a Google spreadsheet and get a subset of data from it;
- we take the CSV output from the pipe and use it to create a new Google Refine database;
- we note that the data table in Google Refine has a HESA code column; we also note that the Guardian datastore HESA spreadsheet has a HESA code column;
- we realise we can treat the HESA spreadsheet as a database, and further that we can create a query (prototyped in the datastore explorer) as a URL keyed by HESA code;
- we create a new column based on HESA codes from a generated URL that pulls JSON data from a Yahoo pipe that is querying a Google spreadsheet;
- we parse the JSON to give us a couple of new columns.

And there we have it – a clunky, but workable, route for merging data from two different Google spreadsheets using Google Refine.

Why? Two reasons… Firstly, because it demonstrates a “have a go” attitude which you absolutely need to have if you’re going to appropriate technology and turn it to your own purposes. Secondly, because it maybe (or maybe not…) hints at a missed trick or two…

So what trick’s missing?

Here’s an example of the sort of thing you get back from the Google Geocoder:

{ “status”: “OK”, “results”: [ { "types": [ "postal_code" ], “formatted_address”: “Milton Keynes, Buckinghamshire MK7 6AA, UK”, “address_components”: [ { "long_name": "MK7 6AA", "short_name": "MK7 6AA", "types": [ "postal_code" ] }, { “long_name”: “Milton Keynes”, “short_name”: “Milton Keynes”, “types”: [ "locality", "political" ] }, { “long_name”: “Buckinghamshire”, “short_name”: “Buckinghamshire”, “types”: [ "administrative_area_level_2", "political" ] }, { “long_name”: “Milton Keynes”, “short_name”: “Milton Keynes”, “types”: [ "administrative_area_level_2", "political" ] }, { “long_name”: “United Kingdom”, “short_name”: “GB”, “types”: [ "country", "political" ] }, { “long_name”: “MK7″, “short_name”: “MK7″, “types”: [ "postal_code_prefix", "postal_code" ] } ], “geometry”: { “location”: { “lat”: 52.0249136, “lng”: -0.7097474 }, “location_type”: “APPROXIMATE”, “viewport”: { “southwest”: { “lat”: 52.0193722, “lng”: -0.7161451 }, “northeast”: { “lat”: 52.0300728, “lng”: -0.6977000 } }, “bounds”: { “southwest”: { “lat”: 52.0193722, “lng”: -0.7161451 }, “northeast”: { “lat”: 52.0300728, “lng”: -0.6977000 } } } } ] }

The data represents a Javascript object (JSON = JavaScript Object Notation) and as such has a standard form, a hierarchical form.

Here’s another way of writing the same object code, only this time laid out in a way that reveals the structure of the object:

{
  "status": "OK",
  "results": [ {
    "types": [ "postal_code" ],
    "formatted_address": "Milton Keynes, Buckinghamshire MK7 6AA, UK",
    "address_components": [ {
      "long_name": "MK7 6AA",
      "short_name": "MK7 6AA",
      "types": [ "postal_code" ]
    }, {
      "long_name": "Milton Keynes",
      "short_name": "Milton Keynes",
      "types": [ "locality", "political" ]
    }, {
      "long_name": "Buckinghamshire",
      "short_name": "Buckinghamshire",
      "types": [ "administrative_area_level_2", "political" ]
    }, {
      "long_name": "Milton Keynes",
      "short_name": "Milton Keynes",
      "types": [ "administrative_area_level_2", "political" ]
    }, {
      "long_name": "United Kingdom",
      "short_name": "GB",
      "types": [ "country", "political" ]
    }, {
      "long_name": "MK7",
      "short_name": "MK7",
      "types": [ "postal_code_prefix", "postal_code" ]
    } ],
    "geometry": {
      "location": {
        "lat": 52.0249136,
        "lng": -0.7097474
      },
      "location_type": "APPROXIMATE",
      "viewport": {
        "southwest": {
          "lat": 52.0193722,
          "lng": -0.7161451
        },
        "northeast": {
          "lat": 52.0300728,
          "lng": -0.6977000
        }
      },
      "bounds": {
        "southwest": {
          "lat": 52.0193722,
          "lng": -0.7161451
        },
        "northeast": {
          "lat": 52.0300728,
          "lng": -0.6977000
        }
      }
    }
  } ]
}

Making Sense of the Notation

At its simplest, the structure has the form: {“attribute”:”value”}

If we parse this object into the jsonObject, we can access the value of the attribute as jsonObject.attribute or jsonObject["attribute"]. The first style of notation is called a dot notation.

We can add more attribute:value pairs into the object by separating them with commas: a={“attr”:”val”,”attr2″:”val2″} and address them (that is, refer to them) uniquely: a.attr, for example, or a["attr2"].

Try it out for yourself… Copy and past the following into your browser address bar (where the URL goes) and hit return (i.e. “go to” that “location”):

javascript:a={"attr":"val","attr2":"val2"}; alert(a.attr);alert(a["attr2"])

(As an aside, what might you learn from this? Firstly, you can “run” javascript in the browser via the location bar. Secondly, the javascript command alert() pops up an alert box:-)

Note that the value of an attribute might be another object.

obj={ attrWithObjectValue: { “childObjAttr”:”foo” } }

Another thing we can see in the Google geocoder JSON code are square brackets. These define an array (one might also think of it as an ordered list). Items in the list are address numerically. So for example, given:

arr[ "item1", "item2", "item3" ]

we can locate “item1″ as arr[0] and “item3″ as arr[2]. (Note: the index count in the square brackets starts at 0.) Try it in the browser… (for example, javascript:list=["apples","bananas","pears"]; alert( list[1] );).

Arrays can contain objects too:

list=[ "item1", {"innerObjectAttr":"innerObjVal" } ]

Can you guess how to get to the innerObjVal? Try this in the browser location bar:

javascript: list=[ "item1", { "innerObjectAttr":"innerObjVal" } ]; alert( list[1].innerObjectAttr )

Making Life Easier

Hopefully, you’ll now have a sense that there’s structure in a JSON object, and that that (sic) structure is what we rely on if we want to cut down on the “trial an error” when parsing such things. To make life easier, we can also use “tree widgets” to display the hierarchical JSON object in a way that makes it far easier to see how to construct the dotted path that leads to the data value we want.

A tool I have appropriated for previewing JSON objects is Yahoo Pipes. Rather than necessarily using Pipes to build anything, I simply make use of it as a JSON viewer, loading JSON into the pipe from a URL via the Fetch Data block, and then previewing the result:

Another tool (and one I’ve just discovered) is an Air application called JSON-Pad. You can paste in JSON code, or pull it in from a URL, and then preview it again via a tree widget:

Clicking on one of the results in the tree widget provides a crib to the path…

Summary

Getting to grips with writing addresses into JSON objects helps if you have some idea of the structure of a JSON object. Tree viewers make the structure of an object explicit. By walking down the tree to the part of it you want, and “dotting” together* the nodes/attributes you select as you do so, you can quickly and easily construct the path you need.

* If the JSON attributes have spaces or non-alphanumeric characters in them, use the obj["attr"] notation rather than the dotted obj.attr notation…

PS Via my feeds today, though something I had bookmarked already, this Data Converter tool may be helpful in going the other way… (Disclaimer: I haven’t tried using it…)

If you know of any other related tools, please feel free to post a link to them in the comments:-)

I’ve been enjoying The Independent’s individual Facebook feeds for journalists, football teams and other ‘entities’ of their news coverage. So much so that I wanted the work of journalists on other news organisations to be brought to me in the same way.

But other newspapers are not offering the same functionality, so I thought I’d do it myself. Here’s how you can do it too:

Create a Facebook page for the journalist

Go to the Facebook Pages page and click ‘Create Page‘ in the upper right corner.

You then need to choose one of 6 categories. Pick ‘Artist, band or public figure‘ and choose ‘Journalist‘ from the drop-down menu that then appears. Type their name in the next box, and tick the box agreeing to the terms. Then click ‘Get started‘.

You’ll now be presented with your page. You can add an image and make various other customisations. But the main thing we need to do is to set it up so that the page automatically publishes updates whenever the journalist publishes a new article.

Install an RSS app on that page

Click ‘Edit page‘ in the upper right corner of the page. On the left hand side of the page you are taken to will be a series of further options. Click on ‘Apps‘.

At the bottom of the Apps page is an option to ‘Browse more applications‘. Click on this to get to the Apps Directory.

You now need to find an app which will publish an RSS feed to the wall of your Page. Instead of browsing use the search box to look for ‘RSS’.

There’ll be a number of possibilities on the results page. I used RSS Graffiti.

Do not click on ‘Go to app’ – instead, in the left hand column should be an option to ‘Add to my page’. Click on this, and select your page from the list that then appears. The app should now be added to your page – but you will still need to customise the settings.

Find the RSS feed for your journalist – or create one

Some news organisations provide individual RSS feeds for every journalist – try looking on the journalist’s profile page (if they have one) or one of their articles to see if you can find a feed (also look for an orange RSS icon in the address bar).

If that isn’t the case, try Journalisted, which has RSS feeds for most journalists on national newspapers and broadcasters.

And if they’re not featured there, you could try using Google News to generate an RSS feed for you (do the search first then look for the RSS feed at the bottom of the page), or even use Yahoo! Pipes to filter articles by a particular journalist from a general news feed (that’s another tutorial to write).

Edit the page’s RSS app settings

Once you’ve got your RSS feed, copy the address (it will look something like http://journalisted.com/dominic-casciani/rss or http://www.guardian.co.uk/profile/jamesball/rss).

Then go to your application page (either by going back to the Facebook Page you created, clicking on ‘Edit Page’, then ‘Apps’ and then ‘Go to application’ under the appropriate one – or by finding the application page again via the Apps Directory).

On the left hand side it will say which of your Facebook pages it has been activated on. Click on the one you need to edit it for – in the case of RSS Graffiti it will say Action required: Assign missing permissions. Click to activate it, and then ‘Allow‘ to give it permission to post to the page’s wall.

You’ll be taken back to the app where you can now add the RSS feed you want to publish to this page. In RSS Graffiti’s case click ‘Add feed‘.

You can now paste the URL of the RSS feed into the appropriate box, and give it a name if you want. Then click ‘Save‘

Go live

Now, back on your page click ‘Publish this page’. And don’t forget to ‘Like‘ it so you receive updates from it in your Facebook news feed.

Of course this will work for anything with an RSS feed – not just journalists. Here’s the Facebook page for the Online Journalism Blog, for example.

PS: A note of caution – Facebook’s terms say you cannot create a page for a person unless you represent them, so they could take the page down. Equally, the journalist could object themselves – ask them if they mind first.

One of the things I’ve been playing with lately is “community detection”, particularly in the context of people who are using a particular hashtag on Twitter. The recipe in that case runs something along the lines of: find a list of twitter user names for people using a particular hashtag, then grab their Twitter friends lists and look to see what community structures result (e.g. look for clusters within the different twitterers). The first part of that recipe is key, and generalisable: find a list of twitter user names…

So, can we create a list of names based on co-location? Yep – easy: Twitter search offers a “near:” search limit that lets you search in the vicinity of a location.

Here’s a Yahoo Pipe to demonstrate the concept – Twitter hyperlocal search with map output:

[UPDATE: since grabbing that screenshot, I've tweaked the pipe to make it a little more robust...]

And here’s the result:

It’s easy enough to generate a widget of the result – just click on the Get as Badge link to get the embeddable widget code, or add the widget direct to a dashboard such as iGoogle:

(Note that this pipe also sets the scene for a possible demo of a “live pipe”, e.g. one that subscribes to searches via pubsubhubbub, so that whenever a new tweet appears it’s pushed to the pipe, and that makes the output live, for example by using a webhook.)

You can also grab the KML output of the pipe using a URL of the form:
http://pipes.yahoo.com/pipes/pipe.run?_id=f21fb52dc7deb31f5fffc400c780c38d&_render=kml&distance=1&location=YOUR+LOCATION+STRING
and post it into a Google maps search box… like this:

(If you try to refresh the Google map, it may suffer from result cacheing.. in which case you have to cache bust, e.g. by changing the distance value in the pipe URL to 1.0, 1.00, etc…;-)

Something else that could be useful for community detection is to search through the localised/co-located tweets for popular hashtags. Whilst we could probably do this in a separate pipe (left as an exercise for the reader), maybe by using a regular expression to extract hashtags and then the unique block filtering on hashtags to count the reoccurrences, here’s a Python recipe:

import simplejson, urllib

def getYahooAppID():
  appid='YOUR_YAHOO_APP_ID_HERE'
  return appid

def placemakerGeocodeLatLon(address):
  encaddress=urllib.quote_plus(address)
  appid=getYahooAppID()
  url='http://where.yahooapis.com/geocode?location='+encaddress+'&flags=J&appid='+appid
  data = simplejson.load(urllib.urlopen(url))
  if data['ResultSet']['Found']>0:
    for details in data['ResultSet']['Results']:
      return details['latitude'],details['longitude']
  else:
    return False,False

def twSearchNear(tweeters,tags,num,place='mk7 6aa,uk',term='',dist=1):
  t=int(num/100)
  page=1
  lat,lon=placemakerGeocodeLatLon(place)
  while page<=t:
    url='http://search.twitter.com/search.json?geocode='+str(lat)+'%2C'+str(lon)+'%2C'+str(1.0*dist)+'km&rpp=100&page='+str(page)+'&q=+within%3A'+str(dist)+'km'
    if term!='':
      url+='+'+urllib.quote_plus(term)

    page+=1
    data = simplejson.load(urllib.urlopen(url))
    for i in data['results']:
     if not i['text'].startswith('RT @'):
      u=i['from_user'].strip()
      if u in tweeters:
        tweeters[u]['count']+=1
      else:
        tweeters[u]={}
        tweeters[u]['count']=1
      ttags=re.findall("#([a-z0-9]+)", i['text'], re.I)
      for tag in ttags:
        if tag not in tags:
    	  tags[tag]=1
    	else:
    	  tags[tag]+=1

  return tweeters,tags

''' Usage:
tweeters={}
tags={}
num=100 #number of search results, best as a multiple of 100 up to max 1500
location='PLACE YOU WANT TO SEARCH AROUND'
term='OPTIONAL SEARCH TERM TO NARROW DOWN SEARCH RESULTS'
tweeters,tags=twSearchNear(tweeters,tags,num,location,searchTerm)
'''

What this code does is:
- use Yahoo placemaker to geocode the address provided;
- search in the vicinity of that area (note to self: allow additional distance parameter to be set; currently 1.0 km)
- identify the unique twitterers, as well as counting the number of times they tweeted in the search results;
- identify the unique tags, as well as counting the number of times they appeared in the search results.

Here’s an example output for a search around “Bath University, UK”:

Having got the list of Twitterers (as discovered by a location based search), we can then look at their social connections as in the hashtag community visualisations:

And wondering why the likes @pstainthorp and @martin_hamilton appear to be in Bath? Is the location search broken, picking up stale data, or some other error….? Or is there maybe a UKOLN event on today I wonder..?

PS Looking at a search near “University of Bath” in the web based Twitter search, it seems that: a) there arenlt many recent hits; b) the search results pull up tweets going back in time…

Which suggests to me:
1) the code really should have a time window to filter the tweets by time, e.g. excluding tweets that are more than a day or even an hour old; (it would be so nice if Twitter search API offered a since_time: limit, although I guess it does offer since_id, and the web search does offer since: and until: limits that work on date, and that could be included in the pipe…)
2) where there aren’t a lot of current tweets at a location, we can get a profile of that location based on people who passed through it over a period of time?

UPDATE: Problem solved…

The location search is picking up tweets like this:

but when you click on the actual tweet link, it’s something different – a retweet:

So “official” Twitter retweets appear to pass through the location data of the original tweet, rather than the person retweeting… so I guess my script needs to identify official twitter retweets and dump them…