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?!;-)

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

In a guest post Alexandra Stark, Swiss journalist and Head of Studies at MAZ – the Swiss School of Journalism, argues that it’s time for journalists to take action on business models for supporting journalism. Stark proposes a broadened set of skills and a new structure to enable greater involvement from journalists, while also fostering further teaching of such skills.

Ask a journalist if his or her job will remain important in the future: “Of course,” he or she will answer while privately thinking, “What a stupid question!” Try changing this stupid question just a bit, asking: “How will it be possible that you’ll still be able to do a good job in the future?” It’s likely you won’t receive an answer at all.

Most journalists have never really thought about it. And if they have, they’ll probably tell you it’s not their job – it’s the task of someone else: perhaps the media owners, readers, foundations or even the state.

For the most part, journalists don’t count “thinking about the future” among their responsibilities. Yet is it really wise to leave our future to others – many of whom have interests in different directions? Shall we leave it to the media executives who’ve promised shareholders a 20 percent return on investments? To the audiences, who’ve grown accustomed to receiving everything for free, or simply taking what is presented? Shall we leave it to foundations or the state, which may change positions or run out of funding?

No. If we journalists want to be able to do a good job in the future, we should stop reacting – adapting – to what happens and start taking action ourselves.

Let me make this clear: basic journalistic skills – for example research, selection and presentation – remain crucial. We’re still talking about journalism.

But as the world becomes more complex, it is no longer sufficient to simply know how to write nice articles or to use a video camera.

Our potential to do what is now considered a “good job” has dramatically decreased due to technological changes, reduced pay and transformations in user behaviour.

Journalists need to take responsibility

Until recently, most of us journalists didn’t especially care about these catalysts of change. Nor did our bosses, as a recent study* from the University of Applied Sciences of Winterthur shows. The study is based on the Tartu-Declaration, which lists 50 skills for journalists, accepted Europe-wide. When asked about the most important skills, 360 editors in chief from across Europe ranked the competencies as shown below:

Source: Koch, Carmen; Wyss, Vinzenz (2010)*

This ranking indicates that the skills editors in chief consider important are those closely related to the heart of journalistic work ­­­– the day-to-day-routine of creating content. The Tartu-Declaration does not mention many skills dealing with the aforementioned drivers of change, and the few which are not considered important by the editors in chief are as follows:

Source: Koch, Carmen; Wyss, Vinzenz (2010)*

It is of course understandable that the focus of editors in chief will be on day-to-day business. Many newsrooms were forced to reduce staff, leaving remaining journalists the task of filing more stories. Overall, the situation is growing increasingly difficult, and changing circumstances continue to restrict our possibility to produce good journalism.

Yet this is wrong: we should stop getting used to the pie shrinking. We need to help make the pie grow again.

This is – of course – much easier said than done. No one knows the “correct” way to go about it, and while a few have tried problem-solving, the majority simply scrutinize their efforts and indulge in schadenfreude when they fail.

Most initiatives are driven by the business-side, with journalists rarely taking action. Why is that so? Because we still think that it’s not our business. If we really want to do our job in the future, we journalists should not only provide content, but also be involved in securing the possibility to create good journalism.

In a world where it is not clear where we are going, we need completely new skills. We should know about and be interested in the drivers of change and how they’ll affect journalism, that is: how the economy evolves, how technology develops and how our users change their habits.

A new set of competencies is required

In my Master’s thesis (only in German, sorry!) I worked out additional sets of competencies, based on the Tartu-Declaration (left side of the graph). Along the three drivers of change I developed three groups of additional competencies (right side of the graph): technological, economic competencies and competencies related to audience (for a larger file please click the illustration).

Journalism Reloaded: Extended competencies for the future of journalism.

We journalists need much more than basic day-to-day-skills. We need to develop deeper competencies to help us to influence the future of journalism.

For example, with regard to technological competencies (for a detailed description as well as the detailed list of competencies related to the economy and audience please refer to my thesis) this means journalists:

• are interested in the technological evolution in the media sector and the opportunities this evolution poses (for production and usage of content)
• accept that both tasks and work-processes change because of technical development
• can help, and thus want to shape and develop change
• know that not everything that is technologically possible makes sense, and may even be ethically problematic
• can use research, communications and production tools (hard- as well as software) efficiently

How do journalists acquire these competencies?

Not so long ago there were still many voices saying, “You can’t learn to be a journalist. You either are one, or you’re not.” That has changed.

Most people today agree that training helps make better journalists. But the old voices were not all wrong, there is a part of being a journalist one can’t learn. For example everything to do with willingness. You might be surprised there are so many competencies related to willingness. But they are crucial – if journalists are unwilling, you won’t be able to make them change.

Despite the fact that they’ll tell you the opposite, journalists don’t really like to change – this I learned in several change-projects I was involved with. As a colleague once told me, “Journalists love it when worlds collide, because that’s action. But pull their table ten centimetres over and they’ll get really annoyed!”

Therefore, I propose structuring competencies the following way:

Where do journalists get these competencies?

Since willingness is about attitude and cannot be taught, competencies in this category must play a crucial role in the selection process (of editorial departments hiring staff as well as schools/universities recruiting students).

To be able is meant both on an intellectual level and on the level of skills. While skills can be taught, intellectual ability can only be trained (in schools as well as on the job) and therefore has to be an element in the selection process as well.

To know refers to professional knowledge as well as general education and expert knowledge. While professional knowledge can be taught, broad interest and expert knowledge has to be a precondition and therefore should also be part of the selection process:

Let’s go for it!

If we as journalists really want to be able to do what we do well – informing the audience, telling stories, uncovering scandals – we must become active and journalism schools must teach us how to do so.

Not so much for the newspapers of today, but certainly for the media we will be working for tomorrow.

Alexandra Stark (mail@alexandrastark.ch, Twitter: @alexandrastark) is Head of Studies at MAZ, the Swiss School of Journalism. She is a trained teacher and holds Masters degrees in International Relations (University St.Gallen) and New Media Journalism (University Leipzig). She was a freelance Moscow-correspondent, and continues to work as a freelancer specialising in economic coverage.

* no download available. Koch, Carmen; Wyss, Vinzenz (2010): The weighting of different journalistic competences: A survey with European editors in chief. Winterthur: Projektbericht

PHP Freelancer

In a guest post for OJB, cross-posted from her blog, Franzi Baehrle reviews a new German TV show which operates across broadcast, web and mobile.

There’s a big experiment going on in German television. And I have to admit that I was slightly surprised that the rather conservative “Bayerischer Rundfunk” (BR, a public service broadcaster in Bavaria), would be the one to start it.

Blogger and journalist Richard Gutjahr was approached by BR to develop a format merging internet and TV. On Monday night the “Rundshow” was aired for the first time at 11pm German time, and will be running Mondays-Thursdays for the next four weeks.

Before going live on air, you could follow the team’s afternoon meeting in a live stream, and be part of it via Google+ hangout. You were informed about the show’s topic (“15M and success/fail of the occupy movement”) and the development of its research on Twitter, Facebook and Google+. And until the big day of the first show, you could also follow the preparations on those social networks and bring in your own ideas.

The team were explicitly asking for the audience’s opinion (and also at this stage invited them to hangouts). Moreover, they developed an app with which you can send comments, upload videos or pictures, respond to polls (the results then being included in the show) and rate the show live.

Obviously, the show wasn’t only broadcast on TV, but also via live stream on the web.

You could also join a hangout during the show, with  participants included in the show and interviewed (as well as experts via Skype). Comments and tweets were  also shown on screen.

The live stream started a few minutes before the show and also lasted longer than the TV broadcast.

A few minutes after the start, #rundshow was already the trending topic on Twitter Germany.

Did it work?

Bottom line: I really liked watching it. It was interesting to follow the discussion (even people from Spain and Greece joined the hangout and actually talked more than the German guys there), and the aired videos. A few of them explained the show itself, which was nice for the first episode, but I would have liked to see more topic-related content.

Moreover, the talks with the Google hangout group became even more interesting after the broadcast ended.

Some people were ranting about the show on Twitter. And yes, it wasn’t perfect. But it’s a huge experiment and no one can expect it to be perfect from the start (it would actually be odd if it was).

If it’s further developed and continued after those four weeks, it could become a milestone (if it isn’t already one).

I simply love the interactivity here: the audience is not an audience anymore, but a part of the show, even before it starts (Axel Bruns’ term “produser” would definitely fit here I think). You’re not just switching on the TV or clicking the play button in your browser, you can interact before, during and after the show, help to shape and develop it, share your opinion and content and even easily be on the show.

It’s great that Richard Gutjahr and his team took the step to develop a format like that, and I’m already looking forward to tonight’s episode.

The first night’s show can we watched here.

PHP Freelancer

Consider these two unrelated events:

1. A bill is proposed to record every contact (and possibly search) made by every UK citizen, to be available to law enforcement agencies and stored by communication service providers
2. An inquiry into press standards and a leaked Home Office report both uncover the ease with which private investigators can access personal records through law enforcement and other agencies

I’m worried about 1. because of 2. And tonight’s Dispatches: Watching the Detectives does a particularly good job of illustrating why. It is “the ease and extent to which the unregulated private investigation industry is willing to acquire personal data for a price” – not just from the police services, but the health services, benefits system, and other bodies, including commercial ones such as communications service providers (for an illustration of the data security of private companies, witness the Information Commissioner’s Office targeting them after a series of data protection breaches).

If you’re a journalist, student journalist or blogger with any interest in protecting your sources, you should be watching the Communications Data Bill closely and understanding how it affects your job.

In the meantime, it’s also worth developing some good habits to protect your stories and your sources against unwanted snooping. More on my Delicious bookmarks under ‘security’.

PHP Freelancer

As part of the ongoing series of questions answered in public, here are another bunch:

1) What inspired you to become a blogger? Have you ever found it difficult to keep up regular posts/ stay dedicated to the same topic area?

As someone teaching online journalism, I felt I should be exploring the medium myself. What inspired me to continue, however, was the community I found along the way.

Yes, I sometimes find it difficult to post, but the great thing about blogging is that you have no deadlines to hit or boss to please, so if I can’t post for a while, I don’t – but as long as I have something to share, I can.

Sometimes I’ve started blogs and then stopped after a while. Again, this is just part of the process. I learn along the way. There’s an audit summing up some of that here.

2) Do you believe blogs are crucial for a journalist trying to establish him/her self in the online world?

Yes. You need a space to collect and share your knowledge, build your reputation, act as a space for communication, and force you to stick to something.

3) As a journalist, which do you find more useful: Twitter, Facebook or blogs. Why?

That’s like asking which is more useful: a hammer, a screwdriver, or a nail. Each is useful for different purposes.

4) Which do you believe allowed you to best connect with your audience in terms of interactivity and views? (Facebook, Twitter or Blogs).

That’s a similar question with a similar answer. It depends on the type of interactivity: blogs are good for in-depth comments and linking; Twitter is faster and more mobile but lacks the opportunity for depth (although it’s still possible). Facebook plugs in better to more people’s lives, but is more ephemeral (hard to archive or link to) and less public.

5) Were there any significant changes in the blogging world that you’ve noticed from when you started blogging to now?

Blogging has changed frequently. Some of the activity that used to take place on blogs, like linkblogging, now tends to happen on Twitter or Facebook. The style of blogging has changed as more journalists started blogging, and celebs, and so on. Every new group that adopted it has introduced new styles, etc.

6) There are two arguments related to blogs: the first is that many believe blogs are dying because of new social medias replacing it (going with the new trend). The second argument is that rather then dying, it is transforming. For example, the fact that most bloggers link their Facebook/ Twitter/ blogs and Youtube accounts together to create a more wide array doesn’t mean that blogs are dying but rather that it is taking on another form.

Which argument do you think is more reasonable and why?

‘Blogging’ has expanded beyond ‘the blog’. Any social network launched after blogs included a blogging facility, whether that was called ‘updates’ or whatever. Twitter is ‘microblogging’. YouTube is often used for videoblogging, and so on.

But blogs aren’t ‘dying’ – they’re just being added to by other media. And we’re also getting new types of blog, like Posterous, Tumblr, etc. To use the tool analogy again, it’s like saying that hammers are dying because of the invention of mallets.

7) What is your view on Twitter’s character limit and Facebook (as a mini-blog) being used by professional journalists looking for information on a story? Do you believe the sacrifice in information for speed and availability is worth it?

Again, different tools work better for different jobs. Sometimes you need speed and availability, sometimes depth and focus.

Do you believe traditional blogs (that are long-form) are able to compete with short blogs/ Twitter posts online, where readers prefer bite-size information and quick access? What do you believe are the pros and cons of each type.

Readers ‘prefer’ different things at different times. So blogs compete when they meet those preferences, and not when they don’t. And they work together with Twitter.

Just as people prefer radio at some times (driving, painting, cooking) and TV at others (eating, relaxing), newspapers other still (travelling, relaxing), we need to use the right platforms at the right times for the right content.

9) Because of Twitter and Blogs, citizen journalism is on the rise. Do you see this as good or bad thing?

Anything that gives more people a voice is a good thing. I’m amazed at any journalist who doesn’t want to hear more voices – if they complain there are too many then they are effectively saying that they would prefer that someone else filter information for them, rather than doing the job they themselves are supposed to be paid for.

It also forces journalists to raise our game. If there’s more competition, if there are more versions of events, if there’s more information then we have to work harder to communicate clearly and compellingly; to verify what actually happened; and to combine that information.

10) What do you think is the future of online journalist? Do you believe it will become more integrated with social media or will it follow a different path and create a more separate identity?

Predicting the future is always going to be difficult because you cannot anticipate new developments. But those aside, I think economic demands will spur more collaborative journalism, which will mean more use of social media. Imagine asking someone 80 years ago if they thought the future of journalism would see it ‘more integrated with the telephone’. Communications technologies are going to be used by media industries: it’s a natural fit.

PHP Freelancer

Online multimedia production has for a few years now come with the guidance to ‘chunk’ content: instead of producing linear content, as you would for a space in a linear broadcast schedule, you split your content into specific chunks of material that each tackles a different aspect of the issue or story being covered. Interfaces like these show the idea in practice best:

The concept is particularly well explained by Mindy McAdams (on text), and Andy Dickinson (on video, below):

“[T]ake an existing package and break it in to its key parts. Write a description of each chunk on to a card or post-it note. Lay them out in a line and then for each card add another for content you didn’t use at that point or expands on the content.

“Then try moving the content around in to sections that fit together. Pretty soon you will have the bare bones of a possible multimedia package.

Talking with some students recently about their own multimedia projects, however, I realised a weakness with the approach: we tend to assume that everyone comes to the story through the same interface.

And this is wrong.

While the practice of chunking multimedia was becoming semi-conventional, another convention was forming: every page is a homepage.

But in multimedia interactives, there’s only one homepage: the interface.

Rethinking the interface

When most multimedia interactives were Flash-based, this wasn’t a problem, because Flash doesn’t allow you to go ‘back’ or ‘forward’ between URLs so there was no need to consider the possibility of a user entering the interactive at different points (unless you split it into separate movies on different webpages). The whole movie sits on one URL, and you start at… the start.

With more and more interactive work using HTML5 or Javascript, however, that becomes a problem. Or rather: an opportunity.

As I’ve worked with students on investigations which were ’chunked’ into different elements (data; multimedia; explainers; case studies) I’ve noticed the same opportunity: each ‘chunk’ is its own homepage: a possible entry point for users into the investigation as a whole.

And that means being clear about the angle on each chunk – not just the product as a whole.

So if your multimedia interactive allows users to browse through a series of interviews, ask: what’s most newsworthy about each? What’s my headline to this, if I assume they haven’t seen any of the other related material? What other material might they want to see next? Will they want to share this individual element? Indeed, should it be published elsewhere too, if it isn’t already? How can it be best optimised for search engines?

In short, the interface is just our choice of arrangement for a set of multimedia elements. Our homepage: not, necessarily, everyone else’s.

PHP Freelancer

With local elections looming this is a great 20-30 minute project for any journalist wanting to create an interactive Google map of council ward boundaries.

For this you will need:

• A Google account with Docs
• A Scraperwiki account
• Access to webspace to host an html file

Firstly we want to scrape the council ward geometry data held by MaPit by mysociety.org and spit it out into a CSV file format that is compatible with Google’s mapping tools.

Getting the ID for the council ward

Go to the MaPit homepage and use the postcode search for a point in the town/city you want the ward data for. In the example I’ve searched using a Preston postcode.

Then in the results page find the council you want data for and note down the id number next to it.

Adapting a scraper to scrape that council ward’s geometry

Now login to your Scraperwiki and visit this page for reclosedev’s council ward scraper. Click ‘Copy’ and you’ll be taken to a code editor page.

On Line 10 of the code you should see:

10: PARENT_ID = 2366  # Preston City Council

Change the 2366 value to the MaPit id of your council and change Preston City Council (anything after # is a comment and isn’t important to the code but it is useful to keep track of what you’re scraping).

Once you’d done this hit ‘Save Scraper’  the ‘Back to scraper overview’. This will take you to your own Scraperwiki page where the scraper is saved. It would be useful at this point to click on the pen symbol next to the scraper name and rename it ‘Your Council Ward geometry data’.

Then click RUN (or CTRL+R) to run your scraper, and wait a while for it to complete (usually no more than a couple of minutes).

When it has completed running, click Back to scraper overview (upper right) and on the section titled This scraper’s datastore click the swdata tab and you should see something similar to this:

The first column contains the shape/geometry data of the council ward and the third column contains its name. Does it look right? Then hit download (in the upper right of this image) and choose CSV as the format.

CSV or comma separate values is an open table format readable by Excel, OpenOffice and Google Docs and any text editor.

We’re going to use Google Fusion Tables to convert the data to a map.

Mapping the data

Head over to your Google Docs account (or Drive if you’ve been switched over) and hit Create > Table. When it asks you to import a new table choose and upload the CSV file you downloaded. On the next page you’ll be asked to specify the columns to import, leave this page as default and click Next.

On the next page name your table and attribute the data to the MaPit. Click Finish. We’re nearly there!

Under the Visualize menu item click Map and you’ll probably see a red blob similar to this.

If you want to make it prettier you can style this clicking ‘Configure styles’  and changing the settings for ‘Polygons‘.

When you’re happy with the settings for that click inside one of your council wards. You should get a window like this:

Not all that informative is it? To make it a bit more useful click ‘Configure info window’ then select the ‘Custom’ tab, delete what’s there and enter this code.

<div class='googft-info-window' style='font-family: sans-serif'>
 <h2><a href="http://mapit.mysociety.org/area/{id}.html" target="_blank">{name}</a></h2><br>
 </div>

Which will give you a link to the MapIt within the information box:

The final step is to embed this map in your webpage.

First click ‘Share‘ in the top right corner of the table page (not the Google+ sharebox) and change to either Public or Unlisted. Then hit File > About and note down the Numeric ID.

Go to the FusionTables LayerWizard and enter this id in the first box. For location column select ‘kml‘ then hit ‘Put layer on map‘.

Optional: Click ‘Add a search feature‘ and ‘select based search‘. For ‘Select Label‘ enter Search by ward name and for ‘Column to query‘ select ‘name’. Then add. What this allows you to do is to easily select the ward you want to view.

Zoom in to the Preview map to how you want the map displayed. Then copy and paste the code generated below into an html editor or plain text editor of your choice.

Replace lines 4-6

<style>
 #map-canvas { width:500px; height:400px; }
 </style>

with this

<style type="text/css">
 html { height: 100% }
 body { height: 100%; margin: 0; padding: 0 }
 #map-canvas { height: 100% }
 </style>

Then cut line 43

<div id="map-canvas"></div>

and paste it at the end of the page between </div> and </body> like so…

Save the file with the .html extension and try opening it in your browser. It should look a little like this.

Upload this page to your webspace. Now you can either link to it or embed it in an iframe. This sample code should work for most purposes.

<iframe id="ifrm" src="http://your.map.page/here.html" width="NUMBEROFPIXELS" height="NUMBER OF PIXELS">Your browser does not support iframes.</iframe>

Ta da!

This is really just scratching the surface of what Scraperwiki and Fusion Tables can do but I hope it served as an easy-ish introduction to them both. If this tutorial did not work for you or if you have any questions then leave a comment and I’ll help out as much I can.

PHP Freelancer

Whether you need an image for your blog post, a soundtrack to your video or that YouTube clip for your documentary, if you’re dealing with multimedia it’s likely you’ll end up using – or wanting to use – someone else’s work as part of your own.

Here are some basic tips on finding and using multimedia across the web in a way that won’t (hopefully) land you in hot water.

The public domain myth

One of the mistakes that has repeatedly landed journalists and their employers in trouble is confusion over the term “public domain“.

Public domain has two possible meanings. In copyright terms, public domain refers to work whose copyright has expired, meaning that anyone can use it without having to ask the copyright holder. Disney – a fierce lobbyist itself for extending copyright – has used ‘public domain’ material as the basis for most of its cartoons, from the work of the Grimm Brothers to a host of other fairy tales, myths and legends.

But sometimes you will hear journalists talk about something being “in the public domain“, in other words ‘public’. For instance, when the Irish Daily Mail published photos of an air traffic controller from her website, they defended the decision on the grounds that the image was “in the public domain”.

But this is not the same.

For example, pretty much every piece of media, almost by definition, is “in the public domain”. Newspapers and magazines sit on the newsstands; television and radio reports are broadcast on huge city centre screens and speakers.

But if you take that content and reproduce it in its entirety without permission, you are breaking copyright law.

It seems odd that media organisations so used to protecting their own, very public, content, should think that another person’s photo, or video, or report, should be fair game because it is “in the public domain”. But they do.

If you want public domain (in the sense of ‘copyright expired’) content, there are some useful sources. The Public Domain Review, for example, publishes a range of public domain work and has this guide to finding them. And Angela Grant writes here about finding public domain video, among other things (note that Angela refers to US law, not that of other countries).

But never assume something is public domain because it is “in public”.

One point to make: while an image, story, or composition may be out of copyright, its performance, re-design or re-telling may not.

Just ask Disney.

Creative Commons – making UGC copyright explicit

If you’re dealing with content that’s been published on a platform like Flickr or YouTube, you may be able to find out the copyright status of that content relatively easily.

Both allow users to easily establish copyright through the Creative Commons licence. You can either look for that licence in the relevant part of the page hosting the content.

On YouTube it is under the video:

On Flickr this is on the right hand side under License:

Make sure you click on that licence to find out what terms it requires.

Creative Commons, for example, has a number of elements:

• Whether the material can be used only in noncommercial contexts, or for commercial use as well
• Whether the material can be adapted and changed, or must be left unchanged
• Whether you must use the same CC licence if you use this material (e.g. you cannot use a noncommercial licence but then allow your work to be used commercially)
• Whether you must attribute the work (this is where many people breach the licence)

If you’re unsure of where your work fits against those criteria (for example, whether it’s considered as “commercial”), then approach the copyright holder for clarity. Remember that the CC licence is only a default position, and can be negotiated. Also, if you cannot get any response and decide to publish anyway, your attempts to contact the copyright holder will be important if there are any legal proceedings.

If you want others to publish their content under a CC licence, it helps if you publish at least some of your own work under a CC licence too. Indeed, if it contains other CC material, their licences may require you to.

Embedding versus re-broadcasting

If the media is hosted on a platform like YouTube, you may be able to embed it on a webpage without seeking permission at all: if the creator* has enabled embedding then they would have little argument in suing for breach of copyright because a) by enabling embedding they have given an ‘implied’ right; and b) they could stop you publishing it instantly by disabling embedding. Also, your embedding of their media would not lead to any loss of revenue (as advertising is embedded too), so it is unlikely that there would be any damages to sue for.

*note: this does not apply to video created by other people and uploaded by someone other than the copyright holder.

Reality bites

Of course, it’s one thing to talk about the strict legal position, and another to talk about what actually happens. Journalists regularly publish content that breaks the law – but make a judgement about the likelihood of ending up in court over that. For example, I can say that the Queen is corrupt (a defamatory statement) and be almost certain that the Queen is not going to sue me (because she has a history of not doing so).

Media lawyers are not just there to advise publishers on their strict legal position, but on the balance of risk involved, and how to reduce those risks. While you cannot always avoid risks, you can avoid them in simple ways:

• Always try to establish the copyright situation regarding any media you use: who holds the copyright (there may be more than one copyright owner: for example, performer and composer), and what are the terms of the licence?
• Try to contact the copyright holder if you’re in any doubt – even if you can’t contact them your efforts to do so will help you if you do end up in court.
• Always attribute authorship and link to the source (this can be done in title credits, captions and/or links on the host webpage). Copyright claims normally revolve around loss of earnings: anything that may have contributed to that (i.e. not linking to the source) will likely add to damages.

For more on these issues, and for related tools and links, see my bookmarks at http://delicious.com/paulb/creativecommons

PHP Freelancer

There are other desktop office suites that can open spreadsheet files, of course, such as Open Office. As long as they’re not too big, spreadsheet files can also be uploaded to and then opened using a variety of online services, such as Google Spreadsheets, Google Fusion Tables or Zoho Sheet. But spreadsheet applications aren’t the only data wrangling tools that can be used to open xls files… Here are a couple more that should be part of every data wrangler’s toolbox…

(If you want to play along, the file I’m going to play with is a spreadsheet containing the names and locations of GP practices in England. The file can be found on the NHS Indicators portal – here’s the actual spreadsheet.)

Firstly, Google Refine. Google Refine is a cross-platform, browser based tool that helps with many of the chores relating to getting a dataset tidied up so that you can use it elsewhere, as well as helping out with data reconcilation or augmenting rows with annotations provided by separate online services. You can also use it as a quick-and-dirty tool for opening an xls spreadsheet from a URL, knocking the data into shape, and dumping it to a CSV file that you can use elsewhere. To start with, choose the option to create a project by importing a file from a web address (the XLS spreadsheet URL):

Once loaded, you get a preview view..

You can tidy up the data that you are going to use in your project via the preview panel. In this case, I’m going to ignore the leading lines and just generate a dataset that I can export directly as a CSV file once I’ve got the data into my project.

If I then create a project around this dataset, I can trivially export it again using a format of my own preference:

So that’s one way of using Google Refine as a simple file converter service that allows you to preview and to a certain extent shape the data in XLS spreadsheet, as well as converting it to other file types.

The second approach I want to mention is to use a really handy Python software library (xlrd – Excel Reader) in Scraperwiki. The Scraperwiki tutorial on Excel scraping gives a great example of how to get started, which I cribbed wholesale to produce the following snippet.

import scraperwiki
import xlrd

#cribbing https://scraperwiki.com/docs/python/python_excel_guide/
def cellval(cell):
    if cell.ctype == xlrd.XL_CELL_EMPTY:    return None
    return cell.value

def dropper(table):
    if table!='':
        try: scraperwiki.sqlite.execute('drop table "'+table+'"')
        except: pass

def reGrabber():
    #dropper('GPpracticeLookup')
    url = 'https://indicators.ic.nhs.uk/download/GP%20Practice%20data/summaries/demography/Practice%20Addresses%20Final.xls'
    xlbin = scraperwiki.scrape(url)
    book = xlrd.open_workbook(file_contents=xlbin)

    sheet = book.sheet_by_index(0)        

    keys = sheet.row_values(8)           
    keys[1] = keys[1].replace('.', '')
    print keys

    for rownumber in range(9, sheet.nrows):           
        # create dictionary of the row values
        values = [ cellval(c) for c in sheet.row(rownumber) ]
        data = dict(zip(keys, values))
        #print data
        scraperwiki.sqlite.save(table_name='GPpracticeLookup',unique_keys=['Practice Code'], data=data)

#Uncomment the next line if you want to regrab the data from the original spreadsheet
reGrabber()

You can find my scraper here: UK NHS GP Practices Lookup. What’s handy about this approach is that having scraped the spreadsheet data into a Scraperwiki database, I can now query it as database data via the Scraperwiki API.

(Note that the Google Visualisation API query language would also let me treat the spreadsheet data as a database if I uploaded it to Google Spreadsheets.)

So, if you find yourself with an Excel spreadsheet, but no Microsoft Office to hand, fear not… There are plenty of other tools other there you can appropriate to help you get the data out of the file and into a form you can work with:-)

PS R is capable of importing Excel files, I think, but the libraries I found don’t seem to compile onto Max OS/X?

PPS ***DATA HEALTH WARNING*** I haven’t done much testing of either of these approaches using spreadsheets containing multiple workbooks, complex linked formulae or macros. They may or may not be appropriate in such cases… but for simple spreadsheets, they’re fine…