What did I learn from APIs?

Sometimes finding data stories when you don’t understand how to use the tools  can feel like you are watching a popular programme but in a different language. Take the Fresh Prince for example, everyone loves watching it. But if it was broadcast in the UK in French I doubt the appeal would be as great.


Fresh prince in French? Picture courtesy of flicker creative commons

So as part of my Pass the Baton of Knowledge on series I decided to look at APIs.
APIs was an interesting and hard task to master. It involves coding as well as other complex bits of data that takes some time to learn. However the output is powerful!

Lesson 1:Dont run before you can walk –As with all data ensure you start with something manageable, start easy with the example given in this blog and than progress from there onto more complex stories.

Lesson 2: Explore- Look for new data sets, I was amazed at the amount of APIs you can find, there is a vast amount of data sources so explore what is out there.

Lesson 3: Have fun-Too be honest at first I found this very difficult, but after re-reading my notes and other data sources I was able to post a step by step guide for all my Data Queen fans.

Thanks for reading!

Application Programme Interface (APIs) How to?

As part of my series on passing on the baton of knowledge, I will be looking at API, which stands for Application Programming Interface.

It is a way a website or service can allow integration of its content into other websites.

News organizations can develop APIs so their content can be customized with additional information at other web sites. It’s one more way for a news organization to participate in and make its content available to a larger online network.

See for example:

As part of the City journalism MA we have been learning how to use APIs with data journalist Paul Bradshaw.

Today I am going to show you how to use APIs using Google refine:

USING GOOGLE REFINE (don’t close the black box, and open using Chrome or Firefox without any other applications running)

We created a simple excel doc with 3 postcodes, and downloaded it into Google refine


Quotation Marks


+value, a command without the quotation marks

Need another plus before remaining string, +.json


Click OK


Edit Json

Go to OJ blog



Add [“geo”]


What’s changed in Json? Focused on geo branch

Add Lat


Copy expressions, call it latitude, click ok


do longitude now




You are able to combine this with the Police API data and find out even more information, such as crimes in a specific location.




Rape reports versus people proceeded against (2008-2013) from the Metropolitan police

rape 222Very interesting set of data showing the number of people who have reported rape versus the number of people who have been charged. This is a useful and challenging set of data as it shows that less than 50% of people have actually been charged, but those people reporting rape or sexual assault over the last five years has increased by 17 per cent.


Exclusive interview with senior data journalist, Claire Miller from Trinity Mirror Group



Claire Miller. Picture courtesy of flicker creative commons


The Data Queen speaks exclusively to Claire Miller, senior data journalist at Trinity Mirror, on her experiences on producing quality journalism with data and her top tips on the best tools of the trade to use.

Data Queen: How important is data journalism in the newsroom?

Claire Miller: In some ways more and more so, there is much more data being gathered on all kinds of things (governments love to measure performance) and more and more of it is available and useable making data journalism skills increasingly useful for finding stories.

It doesn’t replace other kinds of journalism but it is something journalist needs to consider along with things like social media/community management skills and for finding new ways of working with your communities, all of this is part of your skill set.

Data Queen: What has been the most interesting data story you wrote and why?

Claire Miller: It is terrible to say they have all been interesting, that is partly because I cannot think of one in particular, but the reason I love doing data journalism is because I am always finding out interesting things, like where one in 10 pupils have been excluded from school more than once or that two-thirds of non-working parents in parts of the home counties say they are staying home to look after their kids, while their partners work compared to less than half in some places in the North.

That is one of the good things about data, you find out interesting things, which help when you are spending hours cleaning up messy datasets.

Data Queen:  With some data stories journalists sometimes just tend to produce a lot of statistics and call it data journalism, what practical tips can you give to aspiring journalists to help them stand out from the crowed?

Claire Miller: Think like a journalist first – what’s the story?, what’s interesting? No one wants to read lots and lots of statistics (put it in later paragraphs, or an interactive so people can read it if they want and check what you’re saying makes sense), they want to know what is new that you’ve found out from the statistics. The data journalism supports the story rather than being the story. Data journalism is a tool to tell great stories, so tell great stories.

Data Queen: As data journalists, do you think we should be critical of other data stories, if so why?

Claire Miller: With data stories, as a general rule, if you can see what data they’re using, either because it’s publicly available or because they’ve published the background data, you can see how they are making use of the data to tell the story. It should be good practice to do that.  From there I think it’s a good idea to be critical of stories that make unsupported leaps from the data or use very poor data as a source but I’m not sure every data journalism story needs to include high level statistical analysis to have value, knowing the underlying principles of things like significance and confidence intervals are useful, applying them to every story is probably not necessary.

DQ: What recent data story have you read, that didn’t work so well, in your opinion and why?

CM: Where things don’t work is where the data is under-used or misinterpreted, so stories that don’t adjust for population size – yes, there are more ASBOs issued to teenagers in London, there are also more teenagers overall – or don’t common sense check their data – near a police station is probably a hot spot for drug offences because people are searched at the police station when they’re arrested. Things like that are annoying because they should be things you check and they undermine your story (even if there is something there that is worth writing about).

DQ: What are some of your top tips for sourcing data?

CM: Check the obvious sources – The Office of National Statistics (ONS) publishes new datasets frequently and is the best way of keeping track of the latest ones. Parliamentary questions are sometimes useful, you can set up alerts for things you’re interested in through They Work for You, and put in FOI request.

If you want data, think of the best way to get it, either asking for database extracts or asking questions you can easily feed into a spreadsheet, it helps save time when it comes to compiling the data, so you’re not faced with thousands of pages of info.

But also keep an eye out for datasets and ideas. The Government and councils now publish a lot of open data and quite a bit gets missed, it is out there buried on data.gov.uk most likely, so spending time looking at those datasets and thinking about what you could do with them could be a useful exercise.

That and reading news is always a good idea, but I’m a journalist so I would say that, mostly for looking out for ideas for FOIs to put in or topics you could go and find datasets on. Good search skills and you’ll need them for the ONS website are probably an increasingly important tool for modern journalists.

DQ: What advice could you give to current and new data journalists on producing great data journalism and getting it right first time?

 CM: Concentrate on getting the basics right:

  • source data from places where at least some of the limitations will be known, all datasets have problems but places like  the ONS at least tend to have an idea of what the problems with theirs are.
  • if you spend time cleaning data, and you will, make sure you’re not creating more problems that you’re solving, such as      deleting rows or getting things out of sync with sorts.
  • You only need some basic formulas,percentage change, rates per 10,000 etc, percentage of, to get started.
  • visualisations are great for telling a story – but make sure they actually tell the story you want, simpler is better so it’s not confusing.

5 common cosmetic dental procedures, why and how much for?


It seems that not all cosmetic surgery is for vanity. This interesting info graphic shows that a beautiful smile can have a huge impact on a persons confidence and even career.

As part of the passing the baton of knowledge on series, I thought it would be a great idea to look at the positives as well as improvements that can be made within this story.

The key points as provided by Toothpick are as follows:

  • Look out for which dental professionals can perform which treatments, bearing in mind that you can now go straight to the dental hygienist for a teeth cleaning, following new legislation.
  • Note that teeth whitening can now only be performed by dentists following new legislation, rendering treatments in salons illegal.
  • NHS coverage is reserved for treatments that stabilize oral health, therefore cosmetic dentistry procedures qualify only in very rare circumstances.
  • The featured procedures represent our selection of most commonly used in cosmetic dentistry, although other treatment options are available.
  • All costs and timings are estimates, based on data collected by Toothpick.com – your dentist will advice what you need, and can expect, from your particular treatment plan.

I think this is a great informative piece of data journalism. It tells the reader how much the procedure costs, giving details on who can carry it out as well.

This is educational as it means that the reader can than ensure they are being charged correctly and the person carrying out the surgery can actually do it!

The visual representation is also very good, highlighting the procedure details and the costs.

Great data journalism I think would have been even better to have some case studies/testimonials, nevertheless a brilliant informative piece which tells a nice story.

Thanks for reading!


data pic 2

Map of the UK Picture courtesy of IPPR

Immigration in Britain is a  bone of contention for many, with Brits claiming that the number of Europeans in Britain outweighs the number of expats abroad.
I came across a brilliant data map a few weeks ago which highlights the number of Brits in Europe. The source is the Institute of Public Policy and Research (IPPR).

….This got me thinking

Are there any other news outlets that have tackled this story? I hunted around and found two news outlets. The first is  BBC News.

What I liked?

BBC News pulled together great visual tools to really show what the data means, showing the reader the amount of Brits living in particular countries. It offers you a worldview, continent and country view. To really see more on this click on the link here.

BBC News

The BBC data map. Picture courtesy of the BBC

It has used great tools to visualise the data to show the reader in comparison to various countries this is how many people live here. That’s a powerful story one which can not be argued with.

BBC News

Data Mapping. Picture courtesy of the BBC

What I didn’t like so much?

Wasn’t much that I didn’t like to be honest, my only suggestion would have been to compare these numbers to the number of immigrants in the UK, than I think you have an even more compelling story.

Next was Channel 4:

They had taken the story further by comparing the number of expats abroad with the number of Europeans in Britain.

Channel 4 News

Fact Check. Picture courtesy of Channel 4

What I liked?

Loved that they had taken the story further and the news peg was Gordon Browns quote. The data was more recent (2010) than BBC News (2006), which was great. Finally the verdict and commentary from Cathy Newman on the data was great.

What I didn’t like so much?

I didn’t like the lack of visualizations, such a compelling story it would have been brilliant to use a infographics or even a picture of Mr Brown or a map comparing the data. This would have made it much more powerful.

Data Queens verdict:

Although the story from Channel 4 took the news piece that one step further and made the data more news worthy for the reader, the lack of tools used to present the data, in my opinion, makes this a pretty poor piece but a great story.

Winner: BBC News-Good story, great visualisation.

Thanks for reading!