This is the first part of a multi-part series designed to share with readers an inspiring collection of the most important, effective, useful and practical data visualisation resources. The series will cover visualisation tools, resources for sourcing and handling data, online learning tutorials, visualisation blogs, visualisation books and academic papers. Your feedback is most welcome to help capture any additions or revisions so that this collection can live up to its claim as the essential list of resources.

This first part presents the data visualisation tools associated with conducting analysis, creating effective graphs and implementing business intelligence operations.

Please note, I may not have personally used all tools presented but have seen sufficient evidence of their value from other sources. Also, to avoid re-inventing the wheel, descriptive text may have been reproduced from the native websites for some resources.

Microsoft Excel

Microsoft Excel is the most popular spreadsheet tool in the world with over 400 million users and therefore the most accessible tool for conducting analysis and presenting data in graphical format. The package receives a great deal of justified criticism within the visualisation field for the appalling default and range of bad practice graph designs it promotes, yet in the right hands it can be an incredibly powerful and effective visualisation tool.

Find out more information | Cost: Trial > under £100/$150 per license | Tags: Spreadsheet, Office, Graphing

Good examples and references: Peltier Tech | Excel Charts Blog | Chandoo

Status: Ongoing (July 7, 2011)

Open Office Calc

For those who cannot afford or get access to a Microsoft Excel license, OpenOffice.org is an open-source project providing an online office platform mirroring much of the functionality provided by Microsoft Office. The aim is “to create the best possible office suite that all can use”. The Excel equivalent is Calc and although some of the graphing features are limited it is an ever-evolving tool that is being used by many and is only going to improve.

Find out more information | Cost: Free | Tags: Spreadsheet, Office, Graphing

Examples and references: Gallery of chart types

Status: Ongoing (July 7, 2011)

Tableau Desktop

Tableau Desktop is based on breakthrough technology from Stanford University that lets you drag & drop to analyse data rapidly and fluidly, connect to data in a few clicks, then visualise and create interactive dashboards in an instant. Tableau have based their product on years of research to build a system that supports people’s natural ability to think visually providing a tool that lets you easily build beautiful, effective, rich data visualisations.

Find out more information | Cost: Trial > £600/$999 Personal, £1200/$1999 Professional | Tags: Statistical Analysis, Business Intelligence, Dashboard

Examples and references: The Information Lab | The Data Studio | Freakalytics

Status: Ongoing (July 7, 2011)

Tableau Public

Tableau Public is the web-based, publicly accessible version of Tableau Desktop which enables you to create interactive visualisations and embed them into your website, publish them on the Tableau Public Gallery or share within the Tableau Public community. Note, the visualisations cannot be saved locally, that is the ‘public’ essence of this free tool.

Find out more information | Cost: Free | Tags: Statistical Analysis, Business Intelligence, Community

Examples and references: The Information Lab | Joe Mako | OECD Factblog

Status: Ongoing (July 7, 2011)

TIBCO Spotfire

TIBCO Spotfire Professional aims to make it easier to build and deploy analytic applications over the web or perform ad-hoc analytics on-the-fly by letting you interactively query, visualise, aggregate, filter, and drill into datasets of virtually any size.

Find out more information | Cost: Trial > £/$ unknown | Tags: Statistical Analysis, Business Intelligence

Examples and references: Demo Gallery

Status: Ongoing (July 7, 2011)

QlikView

The QlikView platform aims to bridge the gap between traditional BI solutions and standalone office productivity applications, enabling users to forge new paths and make new discoveries. QlikView infuses a broad set of new capabilities, analysis, insight, and value to existing data stores with user interfaces that are clean, simple, and straightforward.

Find out more information | Cost: Trial > £/$ unknown | Tags: Statistical Analysis, Business Intelligence, Dashboard

Examples and references: Demo Gallery

Status: Ongoing (July 7, 2011)

Grapheur

Grapheur is a reactive Business Intelligence tool integrating data mining, modeling, multi-variate analysis and interactive visualisation into an end-to-end discovery and continuous innovation process powered by creativity and curiosity.

Find out more information | Cost: Trial > £/$ unknown | Tags: Statistical Analysis, Business Intelligence, Multi-Variate

Examples and references: Demo Gallery

Status: Ongoing (July 7, 2011)

Gephi

Gephi is an open-source, free interactive visualisation and exploration platform for all kinds of networks and complex systems, dynamic and hierarchical graphs. It claims to be “like Photoshop but for data”, allowing the user to interact with the data representation, manipulate structures, shapes and colors to reveal hidden properties.

Find out more information | Cost: Free | Tags: Statistical Analysis, Business Intelligence, Complex Systems

Examples and references: Demo Gallery | The VIZoSPHERE

Status: Ongoing (July 7, 2011)

Visokio Omniscope

Visokio Omniscope is a versatile, multi-tab and multi-view interactive data analysis, filtering and presentation tool. It offers a powerful new way to visualise, explore and report on large tables of data – with related images, maps, links, and more – then lets you share your file with others using the free Viewer.

Find out more information | Cost: Trial > £/$ unknown | Tags: Statistical Analysis, Business Intelligence, Multi-Format

Examples and references: Demo Gallery

Status: Ongoing (July 7, 2011)

Panopticon

Panopticon data visualisation software supports rapid analysis of fast-changing and historical time series data sets. You can deploy it on the desktop or over the web — or embed it into your own enteprise applications. Originally focusing on real-time treemap visualisations, the product suite is now much broader encompassing traditional options such as bar charts, line graphs and an innovative time-series solution termed ‘horizon graphs’ and more contemporary solutions such as Stephen Few’s bullet graph and Edward Tufte’s sparklines. This creates a great variety of innovative and effective visualisations that can be combined into a single powerful, interactive dashboard display. Most importantly they employ best practice visual principles throughout their offering which stands them apart from other competitors.

Find out more information | Cost: Trial > £/$ unknown | Tags: Statistical Analysis, Business Intelligence, Dashboard

Good examples and references: Gallery | Review from Stephen Few | White papers

Status: Ongoing (July 7, 2011)

Notable Others…

Here are some additional suggestions you may wish to consider within this category of visualisation resources:

Wolfram Mathematica | Bring in your data, combine it with Wolfram Alpha’s ever-increasing store of knowledge, apply sophisticated symbolic and numeric analysis, and create state-of-the-art visualizations—all in one system, with one integrated workflow.

Data Graph | DataGraph is a simple and powerful graphing application for Mac OS X – a companion for Excel, Numbers or any of the big statistical packages.

OmniGraphSketcher | OmniGraphSketcher helps you make elegant and precise graphs in seconds, whether you have specific data to visualise or you just have a concept to explain.

PLOT | PLOT is a scientific 2D plotting program for Mac OS X designed for everyday plotting – it is easy to use, to create high quality plots, it allows easy and powerful manipulations and calculations of data and it is free.

MATLAB | All the graphics features that are required to visualise engineering and scientific data are available in MATLAB®, including 2-D and 3-D plotting functions, 3-D volume visualization functions, tools for interactively creating plots, and the ability to export results to all popular graphics formats.

SPSS Visualisation Designer | Easily develop and build new visualisations that enable new ways to portray and communicate analytics to others. No extensive programming skills are required to conceive, create and share compelling visualizations.

STATA | Stata is a complete, integrated statistical package that provides everything you need for data analysis, data management, and graphics – you get everything you need in one package.

Visualize Free | Visualize Free is a free visual analysis tool, providing the perfect solution for visually exploring and presenting data that standard office charting software cannot handle.

Dundas | Dundas Dashboard brings together all of the tools you need to build meaningful, interactive and fully customized dashboards in one easy to use platform.

Wondergraphs | Wondergraphs strives to be the best way to get and share insights from your data, offering free > premium set of online, graphical report design tools for analysts and businesses.

This is the fourth part of a multi-part series designed to share with readers an inspiring collection of the most important, effective, useful and practical data visualisation resources. The series will cover visualisation tools, resources for sourcing and handling data, online learning tutorials, visualisation blogs, visualisation books and academic papers. Your feedback is most welcome to help capture any additions or revisions so that this collection can live up to its claim as the essential list of resources.

This fourth part presents a broad range of visualisation resources that can be used for representing data via maps. This is a rapidly evolving subset of the population of visualisation resources, one that seems to be constantly introducing us to new tools and clever technologies to bring innovation to the representation of geographical data. There are many tools for creating maps and plotting location data, but this collection focuses on the those tools that provide the means of overlaying data to represent a visualisation within a geographical context.

Please note, I may not have personally used all the tools presented here but have seen sufficient evidence of their value from other sources. Also, to avoid re-inventing the wheel, descriptive text may have been reproduced from native websites for some resources.

Google Maps & Google Earth

Google Maps is undoubtedly the most commonly used mapping technology on the web allowing users to explore the world with incredible detail. Google Earth essentially provides a 3D interface view of the globe, letting you pan and zoom to explore the Earth. The real power of these tools from a visualisation sense comes particularly through the features of accompanying APIs (eg. Google Maps API and Google Visualization AP) and through the process of combining KML data, which enables you to overlay your own visual data onto the foundation 2D or 3D mapped views.

Find out more information | Cost: Free | Tags: Google, KML, 3D

Good examples and references: Google Earth Tutorials | Google Maps Tutorial | Thematic Mapping | GMapCreator

Status: Ongoing (7th July, 2011)

ArcGIS

ArcGIS is a range of powerful and versatile mapping tools from ESRI. The products allow you to integrate data layers onto maps, globes, and models on the desktop and serve them out for use on a desktop, in a browser, or in the field via mobile devices. For developers, ArcGIS gives you APIs for building rich, interactive applications using JavaScript, Flex, or Silverlight, embedding your applications into Web pages or launch stand-alone Web applications.

Find out more information | Cost: Free > Paid Licenses | Tags: Desktop, web-based, APIs

Good examples and references: ArcGIS Desktop | ArcGIS Web Mapping | ArcGIS Explorer | Gallery

Status: Ongoing (7th July, 2011)

GeoCommons

GeoCommons enables everyone to create rich interactive visualisations to solve problems without any experience using traditional mapping tools. Map real-time social data and the over 50,000 open-source data sets in GeoCommons then share your interactive maps and analysis with others by embedding them in websites, blogs or sharing via Facebook or Twitter. API’s allow developers to enhance the scope of the GeoCommons visualisations. GeoIQ extends the functionality of GeoCommons by delivering advanced security, large enterprise data support and robust location analytics.

Find out more information | Cost: GeoCommons = Free, GeoIQ = Paid Licenses | Tags: Interactive, web-based, datasets, APIs

Good examples and references: GeoIQ | User Manual | IssueMap

Status: Ongoing (7th July, 2011)

OpenHeatMap

OpenHeatMap is a straightforward and accessible way for non-specialists to upload data and create maps that communicate information. Developed by Pete Warden, and incorporating the OpenStreetMap map data, it transforms data from sources such as a Google Spreadsheet into an interactive, animated view of a geographical area, which you can then share online. For developers, it’s a JQuery plugin that makes it easy to create a completely open-source mapping component on any web page, using either Flash or HTML5’s Canvas element.

Find out more information | Cost: Free | Tags: Spreadsheet, interactive, JQuery

Good examples and references: Gallery | Documentation

Status: Ongoing (7th July, 2011)

Indiemapper

Indiemapper describes itself as a smarter, easier, more elegant way to make thematic maps from digital data, closing the gap between data and map by taking a visual approach to map-making. It is a web-based app that loads geo-data, allows custom control over mapmaking, and exports static maps in vector and raster formats. With Indiemapper you have the tools you need to make beautiful thematic maps without overwhelming you with hundreds of obscure GIS functions, with nothing being any more than 2 clicks away to keep mapmaking simple, fast, and fun.

Find out more information | Cost: Trial > $30 per month license | Tags: Portable, KML, interactive

Good examples and references: Gallery | System information | FlowingData Review

Status: Ongoing (7th July, 2011)

InstantAtlas

InstantAtlas is premium data presentation software for location-based statistical data enabling information analysts and researchers to create highly-interactive dynamic and profile reports that combine statistics and map data to improve data visualization, enhance communication, and engage people in more informed decision making. With a range of products, features and reporting options it represents a powerful option for this particular type of visualisation challenge.

Find out more information | Cost: Trial > $1,000+ license plus add-ons | Tags: Desktop & server, interactive, collaborative

Good examples and references: Products | Tutorials and Support Zone |  Single Map Examples | FlowingData Review

Status: Ongoing (7th July, 2011)

Target Map

Developed by MapGenia of Barcelona, TargetMap aims to provide an easy way to create and share customised data maps on line, allowing everyone from individuals to large organisations to be able represent their data on maps of any country in the world and to share their knowledge through an online community and gallery of creations.

Find out more information | Cost: Free | Tags: Community, Embeddable

Good examples and references: Blog/Help

Status: Ongoing (7th July, 2011)

TileMill

TileMill is a modern map design studio powered by open source technology. It is a tool for cartographers to quickly and easily design maps for the web using custom geographical data and then integrate layers of data for visual representation. Maps created with TileMill can be displayed using the Google Maps API, OpenLayers and a number of other projects.

Find out more information | Cost: Free | Tags: Open Source, KML, Mac/Ubuntu

Good examples and references: Manual | TileMill Announcement | MapBox

Status: Ongoing (7th July, 2011)

TileMill: Open Source Map Design from Development Seed on Vimeo.

Polymaps

Polymaps is a project from SimpleGeo and Stamen which offers a free JavaScript library for making dynamic, interactive maps in modern web browsers. It provides speedy display of multi-zoom datasets over maps, and supports a variety of visual presentations for tiled vector data. Because Polymaps can load data at a full range of scales, it’s ideal for showing information from country level on down to states, cities, neighborhoods, and individual streets.

Find out more information | Cost: Free | Tags: JavaScript, interactive, embeddable

Good examples and references: Examples | Documentation

Status: Ongoing (7th July, 2011)

Color Brewer

Color Brewer offers a slightly different utility to those listed above. It is a wonderfully useful diagnostic tool that helps you evaluate the effectiveness of individual colour schemes for the use on map designs that represent data, such as choropleth maps. Utilisation of the recommended colour classes will aid map legibility and enhance the potential for accurate interpretation and general insights taken from map-based visualisations.

Find out more information | Cost: Free | Tags: Colour use, map design

Good examples and references: Academic Paper | Cynthia Brewer | TypeBrewer

Status: Ongoing (7th July, 2011)

Notable others…

Dotspotting | Dotspotting is the first project Stamen is releasing as part of Citytracking, a project funded by the Knight News Challenge, making tools to help people gather data about cities and make that data more legible.

DataMaps.eu | DataMaps.eu offers a free visualization tool that can convert your complex location-related data, without any programming effort, in to appealing, easy to understand visualisations.

GeoTime | This award-winning visual analysis tool places an emphasis on visual presentations, introducing new ways to visualize events over time, including the ability to run statistical functions on numerical attributes within your data.

Picking up on Paul Bradshaw’s post A quick exercise for aspiring data journalists which hints at how you can use Google Spreadsheets to grab – and explore – a mortality dataset highlighted by Ben Goldacre in DIY statistical analysis: experience the thrill of touching real data, I thought I’d describe a quick way of analysing the data using R, a very powerful statistical programming environment that should probably be part of your toolkit if you ever want to get round to doing some serious stats, and have a go at reproducing the analysis using a bit of judicious websearching and some cut-and-paste action…

R is an open-source, cross-platform environment that allows you to do programming like things with stats, as well as producing a wide range of graphical statistics (stats visualisations) as if by magic. (Which is to say, it can be terrifying to try to get your head round… but once you’ve grasped a few key concepts, it becomes a really powerful tool… At least, that’s what I’m hoping as I struggle to learn how to use it myself!)

I’ve been using R-Studio to work with R, a) because it’s free and works cross-platform, b) it can be run as a service and accessed via the web (though I haven’t tried that yet; the hosted option still hasn’t appeared yet, either…), and c) it offers a structured environment for managing R projects.

So, to get started. Paul describes a dataset posted as an HTML table by Ben Goldacre that is used to generate the dots on this graph:

The lines come from a probabilistic model that helps us see the likely spread of death rates given a particular population size.

If we want to do stats on the data, then we could, as Paul suggests, pull the data into a spreadsheet and then work from there… Or, we could pull it directly into R, at which point all manner of voodoo stats capabilities become available to us.

As with the =importHTML formula in Google spreadsheets, R has a way of scraping data from an HTML table anywhere on the public web:

#First, we need to load in the XML library that contains the scraper function library(XML) #Scrape the table cancerdata=data.frame( readHTMLTable( 'http://www.guardian.co.uk/commentisfree/2011/oct/28/bad-science-diy-data-analysis',which=1, header=c('Area','Rate','Population','Number')))

The format is simple: readHTMLTable(url,which=TABLENUMBER) (TABLENUMBER is used to extract the N’th table in the page.) The header part labels the columns (the data pulled in from the HTML table itself contains all sorts of clutter).

We can inspect the data we’ve imported as follows:

#Look at the whole table cancerdata #Look at the column headers names(cancerdata) #Look at the first 10 rows head(cancerdata) #Look at the last 10 rows tail(cancerdata) #What sort of datatype is in the Number column? class(cancerdata$Number)

The last line – class(cancerdata$Number) – identifies the data as type ‘factor’. In order to do stats and plot graphs, we need the Number, Rate and Population columns to contain actual numbers… (Factors organise data according to categories; when the table is loaded in, the data is loaded in as strings of characters; rather than seeing each number as a number, it’s identified as a category.)

#Convert the numerical columns to a numeric datatype cancerdata$Rate=as.numeric(levels(cancerdata$Rate)[as.integer(cancerdata$Rate)]) cancerdata$Population=as.numeric(levels(cancerdata$Population)[as.integer(cancerdata$Population)]) cancerdata$Number=as.numeric(levels(cancerdata$Number)[as.integer(cancerdata$Number)]) #Just check it worked… class(cancerdata$Number) head(cancerdata)

We can now plot the data:

#Plot the Number of deaths by the Population plot(Number ~ Population,data=cancerdata)

If we want to, we can add a title:

#Add a title to the plot plot(Number ~ Population,data=cancerdata, main='Bowel Cancer Occurrence by Population')

We can also tweak the axis labels:

plot(Number ~ Population,data=cancerdata, main='Bowel Cancer Occurrence by Population',ylab='Number of deaths')

The plot command is great for generating quick charts. If we want a bit more control over the charts we produce, the ggplot2 library is the way to go. (ggpplot2 isn't part of the standard R bundle, so you'll need to install the package yourself if you haven't already installed it. In RStudio, find the Packages tab, click Install Packages, search for ggplot2 and then install it, along with its dependencies...):

require(ggplot2) ggplot(cancerdata)+geom_point(aes(x=Population,y=Number))+opts(title='Bowel Cancer Data')+ylab('Number of Deaths')

Doing a bit of searching for the "funnel plot" chart type used to display the ata in Goldacre's article, I came across a post on Cross Validated, the Stack Overflow/Statck Exchange site dedicated to statistics related Q&A: How to draw funnel plot using ggplot2 in R?

The meta-analysis answer seemed to produce the similar chart type, so I had a go at cribbing the code... This is a dangerous thing to do, and I can't guarantee that the analysis is the same type of analysis as the one Goldacre refers to... but what I'm trying to do is show (quickly) that R provides a very powerful stats analysis environment and could probably do the sort of analysis you want in the hands of someone who knows how to drive it, and also knows what stats methods can be appropriately applied for any given data set...

Anyway - here's something resembling the Goldacre plot, using the cribbed code which has confidence limits at the 95% and 99.9% levels. Note that I needed to do a couple of things:

1) work out what values to use where! I did this by looking at the ggplot code to see what was plotted. p was on the y-axis and should be used to present the death rate. The data provides this as a rate per 100,000, so we need to divide by 100, 000 to make it a rate in the range 0..1. The x-axis is the population.

#TH: funnel plot code from: #TH: http://stats.stackexchange.com/questions/5195/how-to-draw-funnel-plot-using-ggplot2-in-r/5210#5210 #TH: Use our cancerdata number=cancerdata$Population #TH: The rate is given as a 'per 100,000' value, so normalise it p=cancerdata$Rate/100000

p.se <- sqrt((p*(1-p)) / (number)) df <- data.frame(p, number, p.se)

## common effect (fixed effect model) p.fem <- weighted.mean(p, 1/p.se^2)

## lower and upper limits for 95% and 99.9% CI, based on FEM estimator #TH: I'm going to alter the spacing of the samples used to generate the curves number.seq <- seq(1000, max(number), 1000) number.ll95 <- p.fem - 1.96 * sqrt((p.fem*(1-p.fem)) / (number.seq)) number.ul95 <- p.fem + 1.96 * sqrt((p.fem*(1-p.fem)) / (number.seq)) number.ll999 <- p.fem - 3.29 * sqrt((p.fem*(1-p.fem)) / (number.seq)) number.ul999 <- p.fem + 3.29 * sqrt((p.fem*(1-p.fem)) / (number.seq)) dfCI <- data.frame(number.ll95, number.ul95, number.ll999, number.ul999, number.seq, p.fem)

## draw plot #TH: note that we need to tweak the limits of the y-axis fp <- ggplot(aes(x = number, y = p), data = df) + geom_point(shape = 1) + geom_line(aes(x = number.seq, y = number.ll95), data = dfCI) + geom_line(aes(x = number.seq, y = number.ul95), data = dfCI) + geom_line(aes(x = number.seq, y = number.ll999, linetype = 2), data = dfCI) + geom_line(aes(x = number.seq, y = number.ul999, linetype = 2), data = dfCI) + geom_hline(aes(yintercept = p.fem), data = dfCI) + scale_y_continuous(limits = c(0,0.0004)) + xlab("number") + ylab("p") + theme_bw()

fp

As I said above, it can be quite dangerous just pinching other folks' stats code if you aren't a statistician and don't really know whether you have actually replicated someone else's analysis or done something completely different... (this is a situation I often find myself in!); which is why I think we need to encourage folk who release statistical reports to not only release their data, but also show their working, including the code they used to generate any summary tables or charts that appear in those reports.

In addition, it's worth noting that cribbing other folk's code and analyses and applying it to your own data may lead to a nonsense result because some stats analyses only work if the data has the right sort of distribution...So be aware of that, always post your own working somewhere, and if someone then points out that it's nonsense, you'll hopefully be able to learn from it...

Given those caveats, what I hope to have done is raise awareness of what R can be used to do (including pulling data into a stats computing environment via an HTML table screenscrape) and also produced some sort of recipe we could take to a statistician to say: is this the sort of thing Ben Goldacre was talking about? And if not, why not?

[If I've made any huge - or even minor - blunders in the above, please let me know... There's always a risk in cutting and pasting things that look like they produce the sort of thing you're interested in, but may actually be doing something completely different!]

Overview is a project to create an open-source document-mining system for investigative journalists and other curious people. We've written before about the goals of the project, and we're developing some new technology, but mostly we're stealing it from other fields.

The following are some of the best ideas we saw in 2011, the data-mining work that we found most inspirational. Many of these links are educational resources for learning about specific technology. Some of this work illuminates how algorithms and humans treat information differently. Other are just amazing, mind-bending work.

1. What do your connections say about you? A lot. It is possible to accurately predict your political orientation solely on the basis of your network on Twitter. You can also work out gender and other things from public information.

2. Free textbooks from Stanford University. "Introduction to Information Retrieval" teaches you how a search engine works, in great detail. "Mining Massive Data Sets" covers a variety of big-data principles that apply to different types of information.

3. We're not above having a list of lists. Here's the Data Mining Blog's top 5 articles. Most of these are foundational, covering basic philosophy and technique such as choosing variables, finding clusters, and deciding what you're looking for.

4. The MINE technique looks for patterns between hundreds or thousands of variables -- say, patterns of gene expression inside a single cell. It's very general, and finds not only individual relationships but networks of cause and effect. Here's a nifty video, here's the original paper, and here's one statistician's review.

5. This is one of those papers that really changed the way I look at things. How do we know when a data visualization shows us something that is "actually there," as opposed to an artifact of the numbers? "Graphical Inference for Infovis" provides one excellent answer, based on a clever analogy with numerical statistics.

6. Lots of text-mining work uses "clustering" or "classification" techniques to sort documents into topics. But doesn't a categorization algorithm impose its own preconceptions? This is a deep issue, which you might think of as "framing" in code. To explore this question Justin Grimmer and Gary King went meta with a system that visualizes all possible categorizations of a document set, and how they relate.

7. A few years ago Google showed that the number of searches for "flu" was a great predictor of the actual number of outbreaks in a given location -- faster and more specific than the Center for Disease Control's own surveillance data. The team has now expanded the technique into Google Correlate, which instantly scans through petabytes of data to find search terms which follow any user-supplied time series. Here's New Scientist taking it for a test drive.

8. Not content with free professional textbooks, Stanford has created two free online courses for machine learning and natural language processing. Both are live-streamed lecture series taught by experts, with homework. Learning these intricate technologies has never been easier.

9. Lots of people have speculated about the role of social media in protest movements. A team of researchers looked at the data, analyzing a huge set of tweets from the "May 20" protests in Spain last year. How do protests spread from social media? Now we have at least one solid answer.

10. And the craziest data-mining link we ran across in 2011: IBM's DeepQA project, which beat human Jeopardy champions. This project looks into an unstructured database to correctly answer about 80% of all general questions posed to it, in just a few seconds. Here's a TED talk, and here's the technical paper that explains how it works. I can't tell you how badly I want one of these in the newsroom. If enough journalist hackers build on each other's work, maybe one day ...

Happy data mining! We'll be releasing our own prototype document-mining system, and the source, at the NICAR conference next month. If these are the sorts of algorithms you like to play with, we're also hiring programmers who want to bring these sorts of advanced techniques within everyone's reach.

There is something extraordinarily rich in the intersection of computer science and journalism. It feels like there’s a nascent field in the making, tied to the rise of the internet. The last few years have seen calls for a new class of  “programmer journalist” and the birth of a community of hacks and hackers. Meanwhile, several schools are now offering joint degrees. But we’ll need more than competent programmers in newsrooms. What are the key problems of computational journalism? What other fields can we draw upon for ideas and theory? For that matter, what is it?

I’d like to propose a working definition of computational journalism as the application of computer science to the problems of public information, knowledge, and belief, by practitioners who see their mission as outside of both commerce and government. This includes the journalistic mainstay of “reporting” — because information not published is information not known — but my definition is intentionally much broader than that. To succeed, this young discipline will need to draw heavily from social science, computer science, public communications, cognitive psychology and other fields, as well as the traditional values and practices of the journalism profession.

“Computational journalism” has no textbooks yet. In fact the term barely is barely recognized. The phrase seems to have emerged at Georgia Tech in 2006 or 2007. Nonetheless I feel like there are already important topics and key references.

Data journalism

Data journalism is obtaining, reporting on, curating and publishing data in the public interest. The practice is often more about spreadsheets than algorithms, so I’ll suggest that not all data journalism is “computational,” in the same way that a novel written on a word processor isn’t “computational.” But data journalism is interesting and important and dovetails with computational journalism in many ways.

• The Nieman Journalism Lab’s interview with Guardian Data Blog editor Simon Rogers remains a solid introduction to (one kind of) contemporary practice.
• The best practical guides I know are Rogers’ “How to: get to grips with data journalism” and Dan Nguyen’s series of data-scraping tutorials at ProPublica.
• Stanford’s Journalism in the Age of Data is an hour-long documentary on data journalism and visualization.
• The web is a linked system of human-readable documents. Now Tim Berners-Lee wants to create a web of machine-readable linked data. The full potential is unclear, but it’s a big idea that may come to be the backbone of semantic web visions. The New York Times, The Guardian, and others are experimenting with open data APIs.
• Everyblock creator Adrian Holovaty seems to have been the first to suggest that reporters file structured data in his 2006 “A Fundamental Way Newspaper Websites Need to Change.” This idea is beautifully expanded in Stijn Debrouwere’s “Information Architecture for News Websites” series.

Visualization

Big data requires powerful exploration and storytelling tools, and increasingly that means visualization. But there’s good visualization and bad visualization, and the field has advanced tremendously since Tufte wrote The Visual Display of Quantitative Information. There is lots of good science that is too little known, and many open problems here.

• Tamara Munzner’s chapter on visualization is the essential primer. She puts visualization on rigorous perceptual footing, and discusses all the major categories of practice. Absolutely required reading for anyone who works with pictures of data.
• Ben Fry invented the Processing language and wrote his PhD thesis on “computational information design,” which is his powerful conception of the iterative, interactive practice of designing useful visualizations.
• How do we make visualization statistically rigorous? How do we know we’re not just fooling ourselves when we see patterns in the pixels? This amazing paper by Wickham et. al. has some answers.
• Is a visualization a story? Segal and Heer explore this question in “Narrative Visualization: Telling Stories with Data.”

Computational linguistics

Data is more than numbers. Given that the web is designed to be read by humans, it makes heavy use of human language. And then there are all the world’s books, and the archival recordings of millions of speeches and interviews. Computers are slowly getting better at dealing with language.

• Word frequency techniques like tf-idf and the vector space document model are very simple and very useful. See also stemming. Lots more in the wonderful (and free!) Introduction to Information Retrieval. This book explains how search engines are built, and  discusses tf-idf etc. in great technical detail.
• Statistical language models are increasingly important for all kinds of applications. Michael Nielsen has a great introduction to statistical machine translation. Google’s Peter Norvig discusses how he implemented statistical spelling correction on his laptop during a long plane flight. For the full deal, see the book Foundations of Statistical Natural Language Processing.
• On a related note, Google N-gram viewer lets you look at the frequency of short phrases within 4% of all books published, ever. The excellent paper gives examples of how to use this for cultural research. Dan Cohen has important criticisms.
• Speech-to-text algorithms enable automated transcription, and Matt Thompson explores the huge implications for journalism.
• Reuters maintains the OpenCalais entity extraction service, which parses text to contextually determine who and what is referenced.
• IBM’s Watson project built a question-answering system that reads reference books and wins at Jeopardy. Imagine how useful to journalists and curious readers this could be! This paper on the DeepQA system describes how they did it.

Communications technology and free speech

Code is law. Because our communications systems use software, the underlying mathematics of communication lead to staggering political consequences — including whether or not it is possible for governments to verify online identity or remove things from the internet. The key topics here are networks, cryptography, and information theory.

• The Handbook of Applied Cryptography is a classic, and free online. But despite the title it doesn’t really explain how crypto is used in the real world, like Wikipedia does.
• It’s important to know how the internet routes information, using TCP/IP and BGP, or at a somewhat higher level, things like the BitTorrent protocol. The technical details determine how hard it is to do things like block websites, suppress the dissemination of a file, or remove entire countries from the internet.
• Anonymity is deeply important to online free speech, and very hard. The Tor project is the outstanding leader in anonymity-related research.
• Information theory is stunningly useful across almost every technical discipline. Pierce’s short textbook is the classic introduction, while Tom Schneider’s Information Theory Primer seems to be the best free online reference.

Tracking the spread of information (and misinformation)

What do we know about how information spreads through society? Very little. But one nice side effect of our increasingly digital public sphere is the ability to track such things, at least in principle.

• Memetracker was (AFAIK) the first credible demonstration of whole-web information tracking, following quoted soundbites through blogs and mainstream news sites and everything in between. Zach Seward has cogent reflections on their findings.
• The Truthy Project aims for automated detection of astro-turfing on Twitter. They specialize in covert political messaging, or as I like to call it, computational propaganda.
• We badly need tools to help us determine the source of any given online “fact.” There are many existing techniques that could be applied to the problem, as I discussed in a previous post.
• If we had information provenance tools that worked across a spectrum of media outlets and feed types (web, social media, etc.) it would be much cheaper to do the sort of information ecosystem studies that Pew and others occasionally undertake. This would lead to a much better understanding of who does original reporting.

Filtering and recommendation

With vastly more information than ever before available to us, attention becomes the scarcest resource. Algorithms are an essential tool in filtering the flood of information that reaches each person. (Social media networks also act as filters.)

• The paper on preference networks by Turyen et. al. is probably as good an introduction as anything to the state of the art in recommendation engines, those algorithms that tell you what articles you might like to read or what movies you might like to watch.
• Before Google News there was Columbia News Blaster, which incorporated a number of interesting algorithms such as multi-lingual article clustering, automatic summarization, and more as described in this paper by McKeown et. al.
• Anyone playing with clustering algorithms needs to have a deep appreciation of the ugly duckling theorem, which says that there is no categorization without preconceptions. King and Grimmer explore this with their technique for visualizing the space of clusterings.
• Any digital journalism product which involves the audience to any degree — that should be all digital journalism products — is a piece of social software, well defined by Clay Shirky in his classic essay, “A Group Is Its Own Worst Enemy.” It’s also a “collective knowledge system” as articulated by Chris Dixon.

Measuring public knowledge

If journalism is about “informing the public” then we must consider what happens to stories after publication — this is the “last mile” problem in journalism. There is almost none of this happening in professional journalism today, aside from basic traffic analytics. The key question here is, how does journalism change ideas and action? Can we apply computers to help answer this question empirically?

• World Public Opinion’s recent survey of misinformation among American voters solves this problem in the classic way, by doing a randomly sampled opinion poll. I discuss their bleak results here.
• Blogosphere maps and other kinds of visualizations can help us understand the public information ecosystem, such as this interactive visualization of Iranian blogs. I have previously suggested using such maps as a navigation tool that might broaden our information horizons.
• UN Global Pulse is a serious attempt to create a real-time global monitoring system to detect humanitarian threats in crisis situations. They plan to do this by mining the “data exhaust” of entire societies — social media postings, online records, news reports, and whatever else they can get their hands on. Sounds like key technology for journalism.
• Vox Civitas is an ambitious social media mining tool designed for journalists. Computational linguistics, visualization, and more.

Research agenda

I know of only one work which proposes a research agenda for computational journalism.

• “Computational Journalism: A Call to Arms for Database Researchers” by Sarah Cohen et. al. raises the very intriguing possibility of building systems that automatically or semi-automatically scan databases for stories, document the rationale for believing certain facts, etc.

This paper presents a broad vision and is really a must-read. However, it deals almost exclusively with reporting, that is, finding new knowledge and making it public. I’d like to suggest that the following unsolved problems are also important:

• Tracing the source of any particular “fact” found online, and generally tracking the spread and mutation of information.
• Cheap metrics for the state of the public information ecosystem. How accurate is the web? How accurate is a particular source?
• Techniques for mapping public knowledge. What is it that people actually know and believe? How polarized is a population? What is under-reported? What is well reported but poorly appreciated?
• Information routing and timing: how can we route each story to the set of people who might be most concerned about it, or best in a position to act, at the moment when it will be most relevant to them?

This sort of attention to the health of the public information ecosystem as a whole, beyond just the traditional surfacing of new stories, seems essential to the project of making journalism work.

Teaser image: based on the visual representation of the Iranian blogoshpere.

“The world of data has exploded in the past few years without a corresponding increase in the people or tools to efficiently make sense of it. And so I’ve had a hankering to create a more cohesive, useful programming guide aimed at not just journalists, but for anyone in any field,” explains Dan Nguyen, the author of the book, and journalist at ProPublica.

The book is a work in progress. At this moment it contains four sections and about 30 chapters, in various stages of completion. For an overview of the chapters, check out the full table of contents. The four sections are:

• The Fundamentals: Ruby installation guide and basics of computer science
• Supplementals: useful concepts of programming and how to scrape the Web
• Design and Theory: more useful computer science and software engineering concepts
• The Projects: examples of data projects, the rationale behind them and step by step guides to producing them

A random sampling of the mugshots collected from the Putnam County Sheriff's jail history, the subject of one of the chapters. Image credit: Dan Nguyen.

The book is freely available at: ruby.bastardsbook.com.

All the openings are in the United States. Most of them are with news publishers, including New York Times, Boston Globe, Huffington Post, the Guardian and the Washington Post. 

. 

To see and edit the list of programmer-journalist jobs please click here. Do you know of data journalism jobs that are not in the list? Please add them to the list or leave a comment.

To see and edit a list of data journalism and graphics internships please click here. Do you know of any interships that are not in the list? Please add them to the list or leave a comment.

In the previous guide, we describe several methods for turning PDFs into data usable for spreadsheets. However, those only handle PDFs that have actual text embedded within them. When a PDF contains just images of text, as they do in scanned documents, then the problem isn't just how to convert them into neat tabular data, but how to extract any text, period.

In this tutorial, we'll explain how to write a program to extract the data into tabular format. Here's an overview of the basic steps:

1. Determine the positions of the lines that divide the rows and columns on a page.

2. Break the image apart along those lines to create (hundreds of) individual image files, one for each cell.

3. Perform optical character recognition on each cell to translate the image into a textfile.

4. Reassemble these (hundreds of) text files in the same order that you divided the main image, creating a (text) spreadsheet of the data.

A caveat: The code examples provided here are specific to the FlashPaper version of Eli Lilly's doctor payment disclosures, such as the black outlines of its table cells.

Background

Eli Lilly became the first major drug company to post its physician payments online in July 2009. However, Lilly was criticized in an April 2010 New York Times article for using a proprietary and discontinued format -- Adobe's "FlashPaper" -- which made the data virtually impossible to download or copy. In fact,  PharmaShine, a company that maintains a commercial database of physician payments, said it had to manually retype the entire list.

Eli Lilly disagrees with the Times' story's characterization that it had "purposely made its report impossible to download." In an e-mail to ProPublica, Lilly spokesman J. Scott MacGregor called the characterization "misleading, as it was never our intention to make it difficult for people to access information" and said that preserving integrity of the data was the reason for not making it downloadable initially.

In any event, Lilly now provides their data as a PDF, with copyable text. However, when we began work on Dollars for Docs, Lilly was only providing data in the FlashPaper format. We came up a way to download the file and programmatically to extract tabular data from it.

Software to Get

• Ruby
• RMagick - a library that provides Ruby methods for image processing. Warning: installing it can be laborious.
• Tesseract - An optical character recognition engine maintained by Google. It's used to do the computerized translation of images to text.
• Firefox and the Firebug plugin - read my tutorial on Flash data-scraping for a primer on how to use Firebug to discover the files sent to your web browser.
• Adobe Acrobat Pro (or just Distiller?) -- we used this to convert FlashPaper to a regular PDF
• PDF-Splitter - My colleague Jeff Larson's command-line utility to split a PDF into the TIFF image format (requires Mac OS X Snow Leopard). This can be done (though not as easily) with Adobe Acrobat Pro.

Downloading the FlashPaper Document

Visit the Lilly faculty registry and observe your browser's traffic through Firebug (see my previous tutorial). You should see a file called data.swf with a size of more than 1MB.

The Safari browser's Activity window also will reveal the files downloaded to your browser; data.swf is what we want.

Download the file and open it in your web browser. There's a printer icon in the top-right. Click on it and choose PDF as the output. You'll end up with a PDF weighing over 200MB. When you open it up, you'll see that it appears to be a normal table of text. But you won't be able to highlight-and-copy (which is also the case for secure PDFs), and saving as text will create an empty file.

So let's convert it to the TIFF image format, the only format Tesseract can read. You can do this either with Adobe Acrobat Pro's Export function, or my colleague Jeff Larson's pdf-splitter:

Click the printer icon, circled in red, and print the file as a PDF.

This will result in .tif files for every page in the original PDF.

Step 1: Reading Lines With RMagick

RMagick is a library for Ruby that allows us do a variety of graphics operations in our program, such as changing an image's color. In fact, that's the first thing we want to do because Tesseract works best with black-and-white images. This is done using RMagick's quantize method. We also can reduce the gray boosting the image's contrast.

We had limited success doing this programmatically and so just used a Photoshop batch operation to get the desired black-and-white contrast. For the purposes of this tutorial, you can use this sample black-and-white excerpt: table-to-ocr.tif

Detecting the lines in the .tif file simply involves finding the lines in which all the pixels are non-white (some may be gray, which you'll see if you zoom in at the pixel level). This can be done with using RMagick's get_pixels method on every horizontal and vertical line; get_pixels returns an array of pixels within the boundaries we specify.

RMagick's Pixel class has red, blue, and green attributes. Examining the red, blue, and green values of white pixel should give you 65535 for each; a black pixel will return the value 0. Gray pixels are anywhere in between.

So first we crop the image to remove the white space surrounding the table (using bounding_box). Then we examine each pixel of a line and record the positions where every pixel in that line had color values less than a dark gray (63000 seems to be enough tolerance):

Step 2: Breaking It Into Pieces

Now that we have two arrays defining rows and columns, we iterate through each one and call RMagick's constitute method, which creates new images based on the dimensions we provided it. We then write each image to a file named column_numberxrow_number.tif:

You should end up with a directory called cell-files with nearly 500 TIFF files in it.

Step 3: Tesseract Each Image

Tesseract is a free optical character recognition program, first developed by HP and now maintained as open-source software by Google. Its operation is simple: point it to an image file, and it produces a text file with what it interprets as text from that image. So, in the above code, we simply run Tesseract on each TIFF as it is created. Add this to the above code, after the constitute call:

Now you should have nearly 500 text files in cell-files.

Step 4: All Together Now

In the previous code, we're essentially stepping through the image column by column, line by line. While we're in this loop, we might as well record each of the text files' content into one master text file. If we add a delimiting character each time, we end up with tabular data.

So, in the previous block of code, open a text file called "1-table.txt" and after the tesseract call, write the contents of that tiny text file into "1-table.txt." The combined code for Steps 2-4 is:

Cleanup Time

If that seemed too easy, it was. Open the resulting text file in a spreadsheet:

Tesseract's imperfect translation of the images we sent it.

Tesseract isn't perfect, and on the first pass it may mistranslate many characters, especially ones that look similar to another, such as 'O' and '0' (zero). You can train the Tesseract engine, though it's tedious and involves giving it a character-by-character correction of a sample test.

Even then it won't be perfect. Your next step will be to determine the best way to clean this data. You could start by validating each column against what you know it should contain (such as a currency format). Or you could design a Mechanical Turk task in which you send an individual text file and TIFF for each cell and ask workers to perform a simple verification. You could even write your own Rails application to display the images and text-values side by side, so that your co-workers can collaboratively do the verification (this is what we did until Lilly released their data as PDFs).

Again, the code here is specific to Lilly's format and may not be as successful on a scanned document where, for example, the lines aren't as easy to determine.

We hope to craft a more generalized version of this guide in the near future. We'll call the project "Tableract" for now.

The Dollars for Docs Data Guides

Introduction: The Coder's Cause – Public records gathering as a programming challenge.

1. Using Google Refine to Clean Messy Data – Google Refine, which is downloadable software, can quickly sort and reconcile the imperfections in real-world data.
2. Reading Data from Flash Sites – Use Firefox's Firebug plugin to discover and capture raw data sent to your browser.
3. Parsing PDFs – Convert made-for-printer documents into usable spreadsheets with third-party sites or command-line utilities and some Ruby scripting.
4. Scraping HTML – Write Ruby code to traverse a website and copy the data you need.
5. Getting Text Out of an Image-only PDF – Use a specialized graphics library to break apart and analyze each piece of a spreadsheet contained in an image file (such as a scanned document).

Update (1/18/2011): We originally wrote that we had promising results with the commercial product deskUNPDF's trial mode. We have since ordered the full version of deskUNPDF and tried using it on some of the latest payments data.

Adobe’s Portable Document Format is a great format for digital documents when it’s important to maintain the layout of the original format. However, it’s a document format and not a data format.

Unfortunately, it seems to be treated like a data transfer format, especially by some government agencies and others, who use it to release data that would be much more useful for journalists and researchers as a spreadsheet or even as a plain text file.

In our Dollars for Docs project, companies provided their data in PDF format.

Wikipedia has a good list of PDF tools and converters. However, we didn’t find a one-click-does-it-all solution for converting PDFs into spreadsheets while gathering the Dollars for Docs data.

We recently tested the commercial product deskUNPDF on several of the latest payment lists. In the vast majority of entries, deskUNPDF does an accurate conversion. But like the other methods described in this guide, it does not work perfectly for all the sets of data. For example, with the most recent Johnson & Johnson PDF, deskUNPDF omitted some of the text within some cells that contained long strings (like the names of the payees), This required us to manually verify each cell for accuracy.

Here are three other conversion methods we used for Dollars for Docs that involve a mix of software and coding. However, they still require some manual clean-up, which can be time-consuming for 50+ page documents.

Note: The following guide is for PDFs that actually have embedded text in them. Can you highlight the text to copy and paste it? Then this is the right guide. Otherwise, for PDFs that are secure, or PDFs that are essentially images of text – such as scanned documents, visit this tutorial.

Method 1: Third-Party Sites

Cometdocs and Zamzar are web-based services that convert PDF files that you upload. After a short turnaround time, you’ll receive an e-mail with a download link (as well as an advertisement for their enterprise services).

We’ve had good results from CometDocs. For the Johnson & Johnson (Ortho-Mcneil-Janssen division) file, which you can download here, we still had to manually clean up entries that were split across several lines.

However, the mistakes in conversion can be more than superficial. For example, using CometDocs on the Eli Lilly PDF yielded this conversion:

Left: The PDF translated to spreadsheet format; the numbers in red are in the wrong column.
Right: The original PDF.

On this page, it appears that an entire column of numbers was shifted over. This is an error that would be difficult to catch without comparing the output to the original PDFs.

Method 2: Convert to HTML in Acrobat

As it turns out, Lilly’s PDF has some structure behind it, which we can take advantage of by converting the PDF to HTML. We don’t know of any free PDF to HTML tools, so hopefully your shop already has a copy of Adobe Acrobat Pro.

After downloading the Lilly report, open it with Acrobat. Then select Save As, then select HTML 3.2 as the format.

Optional programming

At this point, you are pretty much done. You can use your web browser to open up the gigantic HTML file that was just created, Select All, Copy, and then Paste into Excel. You’ll spend a little time deleting the header rows and finding anomalies, but Excel generally does a good job of automatically converting HTML tables into spreadsheet form.

With a little programming, you can parse through the file and do some cleanup at the same time (we go into more explanatory detail about the Ruby parsing library, Nokogiri, in the Flash and web scraping tutorials):

The above code will print out all the PDF contents, including the header row and narrative description text. So, assuming that actual data fits in a specified format (a table row with nine columns), we can alter the script to separate the rows into different files. Rows with three columns, for example, outputs to a file called 'pdf-columns-3.txt'

When you do this, you'll find that all valid data rows have nine columns. But there is one more issue with this particular PDF: some rows have each column value repeated twice:

In the highlighted row, the values are repeated twice in each column.

So, for data rows in which there are nine columns, we can check to see if the third column (state initials) contains exactly two capital letters. If not, then the column has the duplicated-data error. In this special case, we can print the corrected data (by splitting the duplicated-data values in half) next to the erroneous columns and then go into a spreadsheet program to compare the results. Here is the code for the entire process:

Method 3: Convert to Text, Measure Column Widths

Unfortunately, not all PDF tables convert to nice HTML. Try the above method on the GSK file, for example. Converting it to HTML results in this mess:

http://www.propublica.org/images/ngen/gypsy_big_image/gsk-html-mess-columns.png

One possible strategy is to analyze the whitespace between columns. This requires the use of regular expressions. If you don't know about them, they’re worth learning. Even without programming experience, you'll find regular expressions extremely useful when doing data cleaning or even advanced document searches.

The first step is to convert the PDF to plain text. You can use the aptly named pdftotext, which is part of the free xpdf package. We're using a Mac to do this. Linux instructions are pretty similar. Under Windows, your best bet would be to use Cygwin.

For this example, we will use the GSK disclosure PDF, which you can download here.

This produces hcp-fee-disclosure-2q-4q2009.txt. The -layout flag preserves the spacing of the words as they were in the original PDF. This is what the GSK file looks like in text form:

Let's look at the easiest scenario of text-handling, where every cell has a value:

There's no special character, such as a comma or tab, that defines where each column ends and begins.

However, values in separate columns appear to have two or more spaces separating them. So, we can just use our text editing program to find and replace those to a special character of our choosing.

Regular expressions allow us to specify a match of something like "one space or more." In this case, we want to convert every set of two-or-more consecutive spaces into a pipe character ("|").

Many major text-editors allow the use of regular expressions. We use TextMate. For Mac users, TextWrangler is a great free text editor that supports find-and-replace operations with regular expressions. Notepad++ is a free Windows text-editor; here's a tutorial on how to use regular expressions in it.

In regular expression syntax, curly brackets {x,y} denote a range between x and y occurrences of the character preceding the brackets. So e{1,2} will match 1 to 2 'e' characters. So the regular expression to find "bet" and "beet" is: be{1,2}t.

Leaving off the second number, as in e{1,}, means we want to match at least one 'e', and any number of that character thereafter. So, to capture two-or-more whitespaces, we simply do: " {2,}".

So entering " {2,}" into the "Find:" field and "|" into "Replace:", we get:

Easy enough. But a common problem is when a cell is left blank. This causes two empty columns to be seen as just one empty column, according to our regular expression:

If you've worked with older textfile databases or mainframe output, you probably have come across tables with fixed-width columns, where the boundaries of columns is a pre-determined length.

Looking at the above table, we can see that even if there are blanks in the column, the actual data falls within a certain space. So, using regular expressions with a little Ruby scripting, we can programatically determine these columns.

We first delimit each row with the " {2,}" regular expression. As we saw in the example above, we'll end up with lines of varying number of columns.

If we then iterate through each column and find the farthest-left and the farthest-right position per column on the page, according to each word’s position and length, we should be able to produce on-the-fly a fixed-width format for this table.

This is easier to explain with a diagram. Here's a sparsely populated table of four columns.

If we delimit the above with " {2,}", we'll find that the first row will have 2 columns; the second row, 3 columns; and the third, 1 column.

Programmatically, we're going to store each of these lines of text as an array, so Row_1 would be ["Banana", "Current"], for instance. This is just an intermediary step, though. What we really want is where each word begins and ends on that line. If the very first position is 0, then "Banana" begins at position 13 and ends at position 19, that is, 19 spaces from the beginning of the line. Doing this for each line gets us:

So as we read the values for each line, let's keep a master list of the farthest-left and farthest-right positions of each column.

Reading through the first line, this list will be: [13,19], [24,31], where “Banana” and “Currant” are positioned, respectively.

When our script reads through the second line, it finds a word (Alaska) at position 4 and ending at 10.

Since it ends before the starting position (10 < 13) of what the program previously thought was the starting boundary of the first column, it stands to reason that the space containing "Alaska" is actually the table's first column.

When the script reads "Colorado", it sees that it intersects with "Currant"'s position in the first line. It assumes that the two share the same column (now the third), and changes the definition of that column from [24,31] to [24,33], since "Colorado" is a slightly longer word.

The list of columns is now: [4,10], [13,19], [24,33], [36,44].

In the third line, the only word is "Bear" and its dimensions fall within the previously defined second column's positions [13,19]

So now with our master list of positions, we can read each line again and break it apart by these column definitions, getting us a four-column table as expected.

Splitting the PDF

When converting the PDF to text, sometimes the columns won't be positioned the same across every page. So let’s begin by splitting the PDF into separate pages by calling pdftotext within Ruby:

And then iterate through each page to calculate its fixed-width format with the algorithm described above. Here's the commented code for the entire program:

You’ll note that in the section where we output the results to compiled_file, we’ve also included the page number, line number, and number of columns in that page. When we try this program on Lilly’s PDF, there are some columns in which the data is spread out enough to be considered separate columns by our program. So keeping track of the columns found per page allows us to quickly identify problem pages and fix them manually.

Because of the wide spacing in this particular PDF-to-text translation, our program would mistakenly create two columns where the original PDF only had one.

PDF-to-Text Anomalies

Almost every conversion ends up with some strange artifacts. For example, in the above conversion of the GSK document, we get some entries in the last column that are repeated over several lines.

I don't know enough about how PDFs are generated to prevent this. But after any conversion, you'll need to use Excel, Google Refine, or some custom code to check that all the fields have values in an expected range.

Regular expressions are pretty much essential to this, allowing you to determine which cells don't fit a certain format, such as an exact length of characters, or a currency format like $xx,xxx.00.

Conclusions

There is no single method we could find that does PDF translation perfectly. We recommend trying one of the web services first. If the result isn’t as accurate as you like, it’s not too much work to write some text-processing code.

With any method, you may end up spending lots of time cleaning up the occasionally mistranslated cell, but at least it won't be as arduous as manually retyping the entire PDF.

The Dollars for Docs Data Guides

Introduction: The Coder's Cause – Public records gathering as a programming challenge.

1. Using Google Refine to Clean Messy Data – Google Refine, which is downloadable software, can quickly sort and reconcile the imperfections in real-world data.
2. Reading Data from Flash Sites – Use Firefox's Firebug plugin to discover and capture raw data sent to your browser.
3. Parsing PDFs – Convert made-for-printer documents into usable spreadsheets with third-party sites or command-line utilities and some Ruby scripting.
4. Scraping HTML – Write Ruby code to traverse a website and copy the data you need.
5. Getting Text Out of an Image-only PDF – Use a specialized graphics library to break apart and analyze each piece of a spreadsheet contained in an image file (such as a scanned document).

This article was originally published by Dan Nguyen on ProPublica, 30 December, 2010. This article is republished with permission.

Google Refine (the program formerly known as Freebase Gridworks) is described by its creators as a “power tool for working with messy data” but could very well be advertised as “remedy for eye fatigue, migraines, depression, and other symptoms of prolonged data-cleaning.”

Even journalists with little database expertise should be using Refine to organize and analyze data; it doesn't require much more technical skill than clicking through a webpage. For skilled programmers, and journalists well-versed in Access and Excel, Refine can greatly reduce the time spent doing the most tedious part of data-management.

Other reasons why you should try Google Refine:

• It’s free.
• It works in any browser and uses a point-and-click interface similar to Google Docs.
• Despite the Google moniker, it works offline. There’s no requirement to send anything across the Internet.
• There’s a host of convenient features, such as an undo function, and a way to visualize your data’s characteristics. For example, check out this guide on how to use it for geocoding addresses.

Download and installation instructions for Refine are here.

This tutorial covers the same ground as this screencast by Refine’s developer David Huynh (the other two videos are here):

The Basics of "Messy Data"

“Messy data” refers to data that’s riddled with inconsistencies, either because of human error or poorly designed record systems. So, a column that contains dates may hold values such as “12-10-2004”, “May 9, 1989”, and “12/4/10.”

These consistences can wreak havoc when trying to perform analysis on the data, so they have to be addressed before starting any analysis.

Badly formatted dates are such a common problem that most modern software, such as Excel, has been programmed to (usually) handle all the different date formats.

But there’s no easy conversion standard for other kinds of data, such as names. If you wanted to get all rows with the name “Tina Fey,” you’d miss the rows that had “TINA FEY” and “Fey, Tina.“ Even differences in capitalization throws off a computer’s basic comparison routine.

Photo by daniel.gene

It’s easy enough to write a piece of software that ignores capitalization and punctuation. It takes a little more thinking to have that software ignore middle initials and names if they exist in certain rows but not others.

To handle nicknames, you could write up a long list of translations, such as “Jake”=”Jacob” and “Sam”=”Samuel” (or “Samantha,” for that matter).

You could also deal with typos by having your code allow for differences of one or two letters when matching names. But to be certain the computer doesn’t match “Dan Smith” and “Don Smith,” you’d have to write a way to flag ambiguous matches.

You can see how doing all of this would be tedious. Enter Google Refine, which does all of the above and more.

How We Used Refine in “Dollars for Docs”

For the Dollars for Doctors project, one of our initial inquiries was to find out who the top-paid doctors and why they were held in high regard by the industry. This became the crux of our first stories, as we discovered problems in how companies screened doctors they hired for promotional work.

In our series of technical guides, we detail how we converted the companies’ payment records from unsortable PDFs to spreadsheets. While this allowed us to find top-paid doctors per company, we wanted to know the total they earned from all the companies.

But even within the same company’s records, the names of payees varied. Some companies included middle names and suffixes, others didn’t. Born names also made simple comparisons tricky.

So before attempting any calculations or analysis in Excel or Access, we used Google Refine.

Starting a Project

You can download Refine’s one-step installation package here. After Refine is installed, clicking on its application icon will pop open your default web browser (I’ve had the best performance with Google Chrome). Start a new project by opening a delimited-text file or Excel spreadsheet.

Your data as viewed through Refine

Right away you’ll see that the data is arranged in a familiar spreadsheet format. Clicking on an individual cell lets you edit it. Clicking on a column header brings up a submenu of operations, including sorting.

Faceting

Refine’s faceting feature allows us to summarize the unique values in a column. The easiest way to see its effect is to try it. Clicking on the companies.name column header brings up a pop-up menu, from which we choose Facet -> Text Facet.

Click on the column-header to bring up submenus.

Now check out the left panel. Refine has listed the seven different company names found in that column, as well as the number of records per company. This is a very convenient way to see if there are any unexpected values in a column.

Faceting the companies column lets us see the different companies that exist in our data and how many records belong to each.

If some of the entries had the company “Merck” misspelled as “Merk”, both would’ve shown up in the left panel. Clicking Remove All remove the company name faceting.

We will be faceting the records’ doctor names in the full_name column to see how many variations exist. We will then use Refine’s clustering feature to condense all the variations (and misspellings) of a name into a single identity, which allows us to find all the records associated with that name, and then add up the payment amounts.

Read more about clustering from Refine developer David Huynh.

Refine can automate the clustering process, and make it easy for you to do it by hand, but there’s always room for error. For proofreading purposes, we want to keep track of what was originally in the full_name column, so let’s duplicate the column by clicking on the column header, then Edit Column->Add column based on this column.

Add a column that duplicates (and upper-cases) the values in the full_name column

Refine supports the several programming languages for transforming or calculating values in a column. For our purposes, we’ll just populate our new column, common_name, with the results of toUppercase(value), which uppercases the text in the corresponding full_name cell.

The new column, common_name

Now we facet the common_name with the Facet->Text Facet command. Refine’s left panel is now populated with all the variations of names found in the records.

The common_name column faceted.

Notice (circled in red) how the number of total rows (1,238) differs from the number of choices (938). The latter number represents the unique names found among the 1,238. You can see in the list, for instance, two names (circled in yellow) that have two repetitions each.

Now, we click on the Cluster button to begin the relatively painless process of clustering these names.

Clustering

Refine gives you five algorithms for guessing the similarity of names. It starts you off with the fingerprint function, which uses the strictest – and safest – formula. Fingerprint assumes that two names have identical alphabetical characters and spacing, regardless of capitalization and punctuation.

So, “Johnny R. Cash,” “JOHNNY R. CASH,” and “Cash, Johnny R,” when translated by the fingerprint function, all end up being equivalent to “cash johnny r."

Refine conveniently lets us click on which of the three variations you want to settle on. Or you can choose Select All, and Merge Selected and Re-Cluster, and Refine will make the choices for you. With the fingerprint function, you can feel pretty confident that the names it clusters together are indeed equivalent.

The fingerprint clustering function

You can read the nitty gritty on all of Refine’s clustering functions here. They progress from stricter to looser. For example, the double-metaphone function groups names by how they sound. This catches variations caused by typos, such as “Bobb Woodword” and “Bob Woodward” that stricter formulas would consider to be different. However, it also considers “Samir” and “Semir” to be equivalent. So clustering isn’t an automatic process, but at least Refine makes it as painless as possible.

The most lax algorithm, PPM (short for Prediction by Partial Matching), can match up particularly different names, though this means you have to put more effort in weeding out false positives.

First pass with PPM clustering

The first pass with PPM appears to be all real matches, matching names where either the doctor’s middle name or just middle initial were included.

It’s pretty easy to write a database command to match entries where first name, last name, and the first letter of the middle name are equivalent.

But since we were dealing with seven different companies with seven different ways of recording names, we didn’t know that beforehand. Some companies may include the middle name in the first name field. Others may have a separate column for it, and others may omit the middle name entirely.

So Refine not only gives us a quick way to cluster names, but does it without needing to know beforehand the actual details of the data format. The full_name column, from which the common_name column is derived from, could consist of entries that are “LAST_NAME, FIRST_NAME” or “FIRST_NAME MIDDLE_NAME LAST_NAME”; Refine doesn’t care.

The Net Size

If we increase the radius of the PPM function to 2 (the larger the number, the wider the net; read the technical details here), we start to see where Refine’s algorithm makes inaccurate guesses:

Looser PPM clustering

The looser algorithm catches the likely match of Cathleen Mullarkey and Cathleen J. Mullarkey-Desapio. However, it also guesses that “Joseph N Gritzzanti” and “Joseph N Ranieri” are the same person.

In cases where it’s ambiguous, Refine makes it easy to examine the cluster yourself. In the example below, Refine has grouped “Edward Julie” and “Ed Julie” together. Is “Ed” short for “Edward,” or could it be an “Edmund”, and thus, a completely different person?

Move the mouse pointer over the row brings up the Browse this cluster option.

Hover your mouse over a clustered entry to bring up the option to get a closer look.

Clicking it pops up a new browser tab with just the entries from the potential cluster. Here, we can see that Dr. “Ed” Julie shares the same city and consulting firm as another record that lists his name as “Edward,” so it seems likely the records refer to the same person.

Inside the cluster

Refine allows you to export to a delimited format, including Excel spreadsheet, where you can do your math and graphing.

And to answer our original question – which doctors made $100,000 or more through drug company work – we simply group entries by the common_name column and sum up the amounts. You can see our findings here.

It’s important to point out that even after using Refine, we researched each of these identities to confirm that it was a single person. In at least one case, there was a father and son, with identical names, who were both doctors in the same geographical area. This research requires time and labor beyond what computer analysis can provide. The rest of the Dollars of Docs database keeps the payment records separate, as originally listed by the companies.

Refined Journalism

A major time sink in data cleaning is designing a way to easily pore through the results list and eliminate false positives and find missed matches. Refine has this built in, and reporters with no programmingexperience can jump in and help clean and proofread.

Real-world data never comes as clean as we’d like. And the tedium and difficulty of poring through messy data stifles our ability to see trends and leads. Refine’s a great tool for speeding up the cleaning process and for getting a clear view of your data, no matter your technical aptitude.

You can download Google Refine here.

For a more detailed tutorial, watch Refine developer David Huynh’s excellent screencast tutorials. Also, check out Refine’s Google Group, where Refine developers respond quickly to bug reports and feature requests.

*Photo (right) by Marcin Wichery

The Dollars for Docs Data Guides

Introduction: The Coder's Cause – Public records gathering as a programming challenge.

1. Using Google Refine to Clean Messy Data – Google Refine, which is downloadable software, can quickly sort and reconcile the imperfections in real-world data.
2. Reading Data from Flash Sites – Use Firefox's Firebug plugin to discover and capture raw data sent to your browser.
3. Parsing PDFs – Convert made-for-printer documents into usable spreadsheets with third-party sites or command-line utilities and some Ruby scripting.
4. Scraping HTML – Write Ruby code to traverse a website and copy the data you need.
5. Getting Text Out of an Image-only PDF – Use a specialized graphics library to break apart and analyze each piece of a spreadsheet contained in an image file (such as a scanned document).