Category Archives: online journalism

Quick and Dirty Recipe: Merging (Concatenating) Multiple CSV files (ODA Spending)

There’s been a flurry of tweets over the last few days about LOCOG’s exemption from FOI (example LOCOG response to an FOI request), but the Olympic Delivery Authority (ODA, one of the owner stakeholders) is rather more open, and publishes its spends over £25k: ODA Finance: Transparency Reports.

CSV files containing spend on a monthly basis are available from the site, using a consistent CSV file format each time (I think…). For what it’s worth, I thought it might be worth sharing a pragmatic, though not ideal, Mac/Linux/unixtools commandline recipe for generating a single file containing all this data.

  1. Right-click and download each of the CSV files on the page to the same directory (eg odaSpending) on your local machine. (There are easier ways of doing this – I tried wget on the command line, but got an Access Denied response (workaround anyone?); there are probably more than a few browser extensions/plugins that will also download all the files linked to from a page. If so, you just want to grab the csv files; if you get them all, from the command line, just copy the csv files to a new directory: eg mkdir csvfiles;cp *.csv csvfiles)
  2. On the commandline, change directory to the files directory – eg cd odaSpending/csvfiles; then join all the files together: files=*; cat $files > odaspending.csv
  3. You should now have a big file, odaspending.csv, containing all the data, although it will also contain multiple header rows (each csv file had its own header row). Open the file in a text editor (I use TextWrangler), copy from the start of the first line to the start of the second (ie copy the header row, including the end of line/carriage return), then do a Find on the header and global Replace with nothing replacing the search string. Then, depending where you started the replace, maybe paste the header (if required) back into the first row

To turn the data file into something you can explore more interactively, upload it to something like Google Fusion Tables, as I did here (data to May 2012): ODA Spending in Google Fusion Tables

Note that this recipe is a pragmatic one. Unix gurus would surely be able to work out far more efficient scripts that concatenate the files after stripping out the header in all but the first file, for example, or that maybe even check the columns are the same etc etc. But if you want something quick and dirty, this is one way of doing it… (Please feel free to add alternative recipes for achieving the same thing in the comments…)

PS here’s an example of one sort of report you can then create in Fusion Tables – ODA spend with G4S; here’s another: Seconded staff

Hyperlocal Voices: Ed Walker and Ryan Gibson, Blog Preston

1 Reply

For the third in our new series of Hyperlocal Voices we head North to the city of Preston in Lancashire, UK. Damian Radcliffe spoke to Blog Preston‘s Ed Walker and Ryan Gibson about some of the lessons they have learned over the last three and a half years.

1. Who were the people behind the blog?

Ed: There’s me, Ed, who used to live in Preston but now lives in London – studied and lived in Preston for five years. Plus Ryan Owen Gibson who is Preston born and bred, he’s co-editor. James Duffell a local web developer and designer is the technical brains behind the site. We’ve recently said goodbye to co-editor Joseph Stashko who was studying at the University of Central Lancashire but will be departing Preston soon after joining Blog Preston in April 2010. We also had co-editor Andy Halls on board from April 2010 to May 2011 before he joined The Sun. We also have some excellent guest contributors including Holly Sutton, Paul Swarbrick, Lisa McManus Paul Melling and many others!

2. What made you decide to set up the blog?

It was a cold January afternoon in 2009, the Preston Citizen (weekly free newspaper for the city) had recently shut down and there was a chance to create something new.

3. When did you set up the blog and how did you go about it?

Ed: Sunday 11th January 2009, started out as a wordpress.com blog to test the water and after a couple of months I recruited the help of James Duffell and he made an ace site and helped me move it to a proper domain. Just started posting local news and events, and build it up from there – lots of Freedom of Information requests, local photos, events coverage and nostalgia.

4. What other blogs, bloggers or websites influenced you?

Ed: I saw the St Albans Blog, and thought, hey, this could happen here.

5. How did – and do – you see yourself in relation to a traditional news operation?

Ryan: I don’t think Blog Preston can compete with a traditional news operation, and I don’t think we would want to. What makes a hyperlocal blog such as ours so great is that we have the freedom, both editorially and strategically, to change our course very quickly. This means we that can adapt to our readership much faster than a traditional news operation can. I also like to think we listen to our readers more, and we try to engage with them through social media channels and on the blog itself.

6. What have been the key moments in the blog’s development editorially?

Ed: May 2010 – we covered the general election and we’ll touch on why that was so important. July 2009 was a big moment, we moved to a hosted solution with a proper domain and really started to accelerate the amount of content going on the site. 2011 was big as we teamed up with NESTA to train community reporters and we recruited a lot of guest contributors, plus Ryan came onboard and has really excelled at live event coverage.

7. What sort of traffic do you get and how has that changed over time?

Ed: We now average around 10,000 unique visitors a month, with 24,000 page impressions. In October 2010 the site was averaging 10,000 page impressions a month and 4,000 unique visitors.

8. What is / has been your biggest challenge to date?

Ed: Just keeping the momentum going, it’s easy to set a site up but when you move away from an area it’s a tough decision, do you shut the site or down to try to keep it going? Fortuntely there’s a great team of people who have stuck their hand up and got involved, and well, we’re still producing great community news for Preston.

9. What story, feature or series are you most proud of?

Ryan: Blog Preston has been lucky enough to break a number of stories that weren’t being picked up by the mainstream media at the time, such as an announcement that the BBC would be coming to Preston to film a series of short dramas, dubbed the Preston Passion, as part of its Easter output.

…I think the live coverage of the May 2010 electionsreally defines what we are about. The mechanics of that series was very simple – it was just a team of guys with a laptop and a mobile phone each, but the level of coverage they managed to achieve went above and beyond what any of the other news operations were doing at the time.

We were the first to interview Preston MP Mark Hendrick after his re-election.

Perhaps this was the moment that people began to take us seriously.

10. What are your plans for the future?

Ryan: 2012 is very important for Preston due to its unique significance as a Guild year, which is only celebrated once every twenty years. So editorially, we are being kept busy covering local events and breaking new stories.

We are also working closely with a number of organisations to collaborate and increase our readership through joint ventures. We are in talks with lots of important people, which is exciting. Our main aim going forward is to grow the editorial team, to put us in a position where we can call on some of the best local writers and reporters to deliver the best content for Blog Preston readers.

Hyperlocal Voices: Rachel Howells, Port Talbot MagNet

2 Replies

The second in a new series of Hyperlocal Voices looks at the Port Talbot MagNet, a not-for-profit community co-operative which has been set up to provide a local news and information service to the people and communities of Port Talbot.

Board Member Rachel Howells took time out to reflect on developments since their launch in 2010 and to tell Damian Radcliffe about some plans for the future.

1. Who were the people behind the blog?

Port Talbot Magnet was started in 2010 by seven professional journalists from South Wales who had all been casualties of redundancy or cuts in freelance budgets in established magazines and newspapers. First and foremost, we are a workers’ co-operative, but we are also a social enterprise and so we are keen to ensure we a force for good in the community. Two and half years on, we still have seven directors, as well as around 20 co-op members and lots of volunteers.

2. What made you decide to set up the blog?

As NUJ members, we found ourselves sitting in so many meetings talking about cuts and closures and it felt sometimes like the local media industry was falling down around our ears. When redundancy hit most of our local Union branch committee we decided that we would do something proactive about the situation to try to ensure good quality journalism was still a viable, sustainable career.

As we were setting up the co-operative, we heard that the weekly newspaper in the town of Port Talbot was closing and it seemed an obvious gap for us to try to fill – here was a town of 35,000 people without a dedicated newspaper and here were seven out-of-work journalists who could supply news. Making the one pay for the other was, and in many ways still is, the problem.

3. When did you set up the blog and how did you go about it?

The blog came along much later. Our first ambition was to go into print and we spent about a year applying for funding and trying to get the project off the ground in some way. The funding applications weren’t successful unfortunately, and we had a crisis meeting where we decided to change tack and concentrate on what we did best – journalism. This turned out to be a good move, because we could show what we were capable of; people suddenly understood what we were trying to achieve.

In a more practical sense, we had no capital apart from donations from the directors and so we set up a WordPress blog, paying a modest amount for a theme, and we got in touch with local companies and the council and asked them to put us on their mailing lists for press releases. Then we spent lots of time learning the patch and making contacts. Facebook has been a particularly good way to reach the online community in Port Talbot (not many are using Twitter yet), and drives about half our website traffic.

4. What other blogs, bloggers or websites influenced you?

We set up our own crowdfunding model called Pitch-in! which was hugely influenced by Spot.Us, although we changed the idea a bit to suit a more hyperlocal audience. I love what Spot.Us has done to empower freelance journalists and as this was at the heart of our enterprise we have been really keen to offer this as a service to our members.

5. How did – and do – you see yourself in relation to a traditional news operation?

We would like to be more like one, I think, but we don’t have the resources at the moment. As we are so reliant on volunteers we don’t have the consistency that a traditional newsroom can offer – for example we can’t always cover local council meetings because our volunteers have other commitments as well. But I think we all believe in the principles behind traditional newsrooms and the power they have to be a force for good in the community as a watchdog or a voice.

For right or wrong, journalists can ask the questions that perhaps get ignored when members of the public ask them, and even with our limitations we are able to perform this aspect of newsroom journalism.

In future we hope we will become more sustainable so we can pay journalists and operate a more professional service, but this will always be in co- operation with the local community. We always have a day every week where people can call in to the office and speak to us, which is what all local newsrooms used to do.

6. What have been the key moments in the blog’s development editorially?

Aside from launching the website in the first place, a successful system has been our ‘editor of the week’ rota, which has seen a team of five journalists taking it in turns to supervise the website, commission volunteers and respond to emails. This has meant there’s always been a clear point of contact every week and that things don’t get missed. Another big milestone has also been paying journalists for their skills, which we have started to do in the last few months. So far we’ve only been able to pay for court reports but we plan to do more of this as finances allow.

7. What sort of traffic do you get and how has that changed over time?

We get a consistent 3,500 unique visitors every month now, which has more than trebled in a year. We have seen some great peaks around some of our coverage, too – notably stories about The Passion, a landscape theatre production which took place in Port Talbot in 2011 and starred locally-raised Hollywood star Michael Sheen. We have also had great responses to our coverage of protests and campaigns, crime and local elections.

8. What is / has been your biggest challenge to date?

The lack of funding and the lack of resources. Three of our seven directors have full time jobs, one has failing health and the other three have freelance or other commitments, and so progress can sometimes be frustratingly slow as we try to recruit or train volunteers and manage the website, finances and keep our contacts live. But we are still here, and the project continues to chalk up successes.

9. What story, feature or series are you most proud of?

I think our coverage of The Passion was pretty impressive.

We had twelve volunteers covering the three days of live theatre and we produced a hugely comprehensive mix of written reporting, photography, video and audio – some of which we still haven’t had time to edit and upload to the website more than a year on.

It was a unique production that took place all over the town in both scheduled and unscheduled performances, and therefore a unique challenge to cover it all. I think our archive shows how daunting a task it was and how well we worked as a team to do it. I don’t think any other media outlet managed the comprehensive coverage we produced. I look back at it now and wonder how on earth we managed it.

10. What are your plans for the future?

There was an anniversary exhibition over Easter which commemorated The Passion and, in partnership with National Theatre Wales, we produced the official souvenir programme for it. This was our first foray into print, and we made a modest profit from advertising. It showed us that going into print would be an obvious move in the future, and so now we are developing ways we could make the website work alongside a printed news-sheet.

More generally, we would like to keep growing, pay journalists and establish a sustainable model that could benefit other communities who are facing similar ‘news black holes’ following the death of a local newspaper.

And we’d really like to persuade the local council to let us film their council meetings…

Hyperlocal Voices: Zoe Jewell and Tim Dickens, Brixton Blog

1 Reply

We haven’t any Hyperlocal Voices interviews for a while, so Damian Radcliffe has agreed to remedy that. To kickstart this new series he talks to Zoe Jewell and Tim Dickens from the Brixton Blog in South West London.

The site relaunched earlier this year, with an aspiration for the “website to perform the role of the now all but defunct traditional local paper.” Interestingly, Tim left his job at a local paper (run by Archant) to concentrate on a venture which seeks to be: “a focus point for all section[s] of the community to come to and read, share and discuss. A voice that you can trust amid the frenetic tumult that is Brixton.”

Here the Editor’s answer ten quick questions about the history of the blog and outline some plans for the future.

1. Who were the people behind the blog?

Zoe Jewell, who works in television production, originally set up the Brixton Blog in 2010 to cover some of the interesting stuff happening in the area where she lives and grew up. In January 2012 she relaunched the website with local newspaper journalist Tim Dickens as a comprehensive online news resource for people in Brixton. We are now helped by a team of more than a dozen regular talented contributors.

2. What made you decide to set up the blog?

We felt there was a massive gap in the provision of news in Brixton, an area where news is made all the time and where lots of change is happening that was previously unreported. We wanted to hold the local authorities to account by covering council meetings.

It is vital that the community can come together to discuss big issues like gentrification and housing, so we wanted the site to report facts and stimulating discussion on them.

3. When did you set up the blog and how did you go about it?

We relaunched the Blog in January 2012. We met and came up with the idea in November 2011 and very quickly got the idea up and running. First we spoke to local business owners, bloggers, community leaders and councillors about the idea and what was needed in the area from a resource like this.

We also spoke to hyperlocal experts and bloggers from across the country to find out about their own experiences and ask for advice. We tweeted that we needed help from graphic designers and website designers and managed to find people who felt strongly about contributing to the brixton community and would, very kindly, help us out for free.

4. What other blogs, bloggers or websites influenced you?

Se1, Herne Hill Forum, The Londonist, Hackney Citizen

5. How did/do you see yourself in relation to a traditional news operation?

As trained journalists and media professionals we see our role as a replacement to traditional local newspapers. We aim to report timely news accurately and in a balanced manner, giving all points of view and enabling discussion through the comments thread. The launch of our printed paper, the Brixton Bugle, this month will take this one step further.

At the same time, the nature of web and the relationship we have built up with our local readers means we can have more of a personal relationship with our readers than most traditional media and be much more flexible – we can very much have our own personality online, especially on Twitter. That also means we are, quite rightly, held to account too – when you might to meet your reader in the local shop buying a pint of milk or having a pint in the pub you want to be sure you’ve done good!

6. What have been the key moments in the blog’s editorial development?

Other than our campaign victory regarding the Lambeth Country Show, we are proud of the time we began “live blogging” council meetings. We had three reporters in the council chamber all tweeting, writing and blogging simultaneously during the budget setting.

7. What sort of traffic do you get and how has that changed over time?

We currently have an average of about 1,500 page views per day from just under 1,000 unique views. This has been steadily rising.

8. What is / has been your biggest challenge to date?

By far our biggest challenge has been monetising the site and trying to find advertisers to support it. It is also a challenge to prioritise our time: There is so much to write about, and so many potential projects, but so little time!

9. What story, feature or series are you most proud of?

Two weeks after our relaunch we learned that the local council, Lambeth, planned to cancel a popular country show, which attracts 250,000 annually. Although they blamed the Olympics we felt there was more to it and began a petition to reinstate the popular event.

We began a community campaign to reinstate the show that attracted almost 1,000 signatures in just a few days. It prompted responses from councilors, MPs and the police borough commander. A few days later they announced that the show would in fact go ahead. We believe that it was the pressure exerted by the Blog’s campaign that led to the u-turn. (Ed: see this report from the Evening Standard.)

10. What are your plans for the future?

Later this month we plan to launch the Brixton Bugle, an 8-page hyperlocal newspaper on newsprint. It will be distributed to about 5,000 commuters at Brixton tube station and across the town centre in shops, cafes and libraries.

We are also in the process of organising a series of “offline” debates about issues we have raised on the site.

We have ambitions to create a mobile app edition of the site (NESTA funding allowing) as well as developing the listings side and in

The 3 forces changing journalism education part 3: the relationship between education and industry

Leave a reply

In the first part of this series, I talked about how changes in the news industry were reflected in changing journalism education. In the second I looked at how education itself is reacting to changes in information. The third change is a result of the first two:

Part 3: The relationship between the news industry and education is changing

In the UK the relationship between journalism schools and news organisations has largely been a supply chain of newsroom-ready graduates. As universities and training bodies like the NCTJ, BJTC, PTC and Skillset expanded, news organisations scaled back on their own training budgets.

But the changes in the industry outlined above threw a spanner into that supply chain, in two key ways.

Firstly, the chain was based on an assumption that the news industry – and specifically senior figures within it – was best placed to know what skills it was going to need in the future – but it quickly became apparent that they didn’t.

Accreditation documents that barely mentioned online skills – or that reduced it to web design or broadcast-style video – didn’t help.

Cultural battles that dragged on for years – over the validity of blogging, citizen journalism, SEO, and innumerable other innovations – didn’t help.

And the fact that the industry itself didn’t appear to have the skills to solve its problems – or know what skills would – didn’t help.

Universities faced an uncomfortable truth. It was increasingly clear that to serve their students properly they needed to teach skillsets that were in demand in the growing online media industries. But those industries were not the ones formally shaping their curricula – nor the ones that students even knew existed when they applied for the course.

Should they continue to teach the courses that sold – knowing that there was a good chance that graduates would be less employable in a few years’ time?

Supplier of cheap labour – or incubator?

Alongside the issue of curricula was another development. Student work experience, when scaled up to the numbers at which journalism courses were now operating, had become a significant part of the way the media industry worked.

As the workforce thinned there was the potential for that contribution to reduce the number of jobs available to graduates of the same courses.

In other words, while work experience may be useful for the individual student, it would also reduce the opportunities open to the same student in the jobs market.

The whole concept of work experience was also based on the same supply chain logic. It wasn’t designed to adapt to a situation where graduates were ‘expected to teach the news industry’ (which isn’t to imply that the exchange is only in that direction either).

It was designed largely on the basis that students would either perform an existing role while the occupant was on leave, or fill the gaps in another role – either of which may be redundant in a year.

Was this the best way to serve both journalism students and the news industry? Again, it may have been when the numbers were lower and the industry better defined.

But could that student workforce be used in a way that would better benefit both the student and the news industry?

What benefits both student and industry most?

In my own course I tackled this by designing the ‘work experience’ element as a consultancy, tackling a specific problem for a client (media organisation, or other organisation with a media element) through the research and practical skills that a student develops in higher education.

This helps the organisation explore possible new streams of revenue or content which could help create new jobs.

It helps the student develop unique knowledge and skills that can help them stand apart from others, in partnership with experienced journalists and publishers who can provide valuable insights too.

This process could be scaled up to see groups of students exploring industry problems.

There are other possibilities. At UCLAN Francois Nel has been developing the MADE project to help develop media and digital enterprise. At the Oxford Institute they are working on a range of industry problems (although it’s not clear how involved journalism students are). At Birmingham City University the university provides facilities to students launching their own enterprise. At City University London and at CUNY students pitch entrepreneurial ideas to potential investors.

Could universities – with their access to funding, infrastructure and business networks – do a lot more in this regard? Might it be one of the new areas where they can claim a gatekeeping role (although Kickstarter and other fundraising platforms still offer alternatives)?

In the final part of this series I will try to list some of the ways that journalism training is trying to change – can you add any?

The 3 forces changing journalism education – and why we’re ignoring 2 of them (part 1)

10 Replies

On Monday I spoke about the future of journalism education at the EJC’s 20th anniversary event. It strikes me that while most of the discussion around journalism education centres on changes in the ‘news industry’, there are other significant forces which are too often overlooked.

In a series of posts this week I want to try to map out three areas where journalism education is facing changes and how they’re being tackled – or, in most cases, not.

Continue reading →

Now available by distance learning: my MA in Online Journalism

7 Replies

The MA in Online Journalism which I established at Birmingham City University in 2009 is now available via distance learning.

The MA in Online Journalism by distance learning is primarily aimed at people who are already working in a content- or technology-related role.

Students can use their current work as part of their studies, or use their studies to explore ideas and skills that they have been unable to explore as part of their role.

The course requires self-discipline and motivation, and I look for evidence of that in the application process. You will be communicating regularly both with myself and other students on both the distance learning and ‘with attendance’ versions of the course, so there will be plenty of support, but like any Masters level course you will be expected to learn independently with guidance to develop your own areas of expertise.

I’ve actually been teaching the distance learning version of the course since last September, but hadn’t publicised the fact (I wanted to ‘soft-launch’ the first year with a small group first, and use agile principles to continue to develop it).

But now the secret’s out: The Guardian reported on the course last month, and student Robyn Bateman has written about her experience of studying via distance learning in Wannabe Hacks this week.

I’ll be blogging further about how the distance learning course has changed how I teach the MA as a whole, and changes in education more generally, but that’s for another post. In the meantime, I’m particularly welcoming applications from individuals with good experience as a working journalist, or as a web developer, or who are running or considering launching their own journalism enterprise.

Online journalism jobs – from the changing subeditor to the growth of data roles

1 Reply

The Guardian’s Open Door column today describes the changes to the subeditor’s role in a multiplatform age in some detail:

“A subeditor preparing an article for our website will, among other things, be expected to write headlines that are optimised for search engines so the article can be easily seen online, add keywords to make sure it appears in the right places on the website, create packages to direct readers to related articles, embed links, attach pictures, add videos and think about how the article will look when it is accessed on mobile phones and other digital platforms. Continue reading →

F1 Championship Points as a d3.js Powered Sankey Diagram

d3.js crossed my path a couple of times yesterday: firstly, in the form of an enquiry about whether I’d be interested in writing a book on d3.js (I’m not sure I’m qualified: as I responded, I’m more of a script kiddie who sees things I can reuse, rather than have any understanding at all about how d3.js does what it does…); secondly, via a link to d3.js creator Mike Bostock’s new demo of Sankey diagrams built using d3.js:

Hmm… Sankey diagrams are good for visualising flow, so to get to grips myself with seeing if I could plug-and-play with the component, I needed an appropriate data set. F1 related data is usually my first thought as far as testbed data goes (no confidences to break, the STEM/innovation outreach/tech transfer context, etc etc) so what things flow in F1? What quantities are conserved whilst being passed between different classes of entity? How about points… points are awarded on a per race basis to drivers who are members of teams. It’s also a championship sport, run over several races. The individual Driver Championship is a competition between drivers to accumulate the most points over the course of the season, and the Constructor Chanmpionship is a battle between teams. Which suggests to me that a Sankey plot of points from races to drivers and then constructors might work?

So what do we need to do? First up, look at the source code for the demo using View Source. Here’s the relevant bit:

Data is being pulled in from a relatively addressed file, energy.json. Let’s see what it looks like:

Okay – a node list and an edge list. From previous experience, I know that there is a d3.js JSON exporter built into the Python networkx library, so maybe we can generate the data file from a network representation of the data in networkx?

Here we are: node_link_data(G) “[r]eturn data in node-link format that is suitable for JSON serialization and use in Javascript documents.”

Next step – getting the data. I’ve already done a demo of visualising F1 championship points sourced from the Ergast motor racing API as a treemap (but not blogged it? Hmmm…. must fix that) that draws on a JSON data feed constructed from data extracted from the Ergast API so I can clone that code and use it as the basis for constructing a directed graph that represents points allocations: race nodes are linked to driver nodes with edges weighted by points scored in that race, and driver nodes are connected to teams by edges weighted according to the total number of points the driver has earned so far. (Hmm, that gives me an idea for a better way of coding the weight for that edge…)

I don’t have time to blog the how to of the code right now – train and boat to catch – but will do so later. If you want to look at the code, it’s here: Ergast Championship nodelist. And here’s the result – F1 Chanpionship 2012 Points as a Sankey Diagram:

See what I mean about being a cut and paste script kiddie?!;-)

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).