This story by Fabiano Angélico, who formerly worked at Transparencia Brasil, is about how technology and the help of coders can be used to highlight links between politicians and corrupt entrepreneurs. It is followed by a brief “Behind the News” interview which shows some of the time costs of datawrangling and problems faced when getting the story out.

How can transparency and technology point out connections between politicians and bad entrepreneurs? Well, first of all you will need some information about the politicians and about the entrepreneurs.

In Brazil, in spite of the historical lack of transparency in governments (Brazil’s freedom of information law was sanctioned just late last year), the Electoral Court has been proactively providing information on political candidates since 2002. One piece of info is the financial donation to the candidates, containing info about who is donating to whom and how much. Although this database is released only after the elections — the info would surely be more powerful if it were released DURING the political campaigns –, one must admit this is a rich source of information.

January, 2010. Elections for President and for the Parliament, as well as for State Governors and State Parliaments, would happen in only 9 months time, in October. However, many people were already discussing them.

At that time, 2010 had just begun, I was at work, thinking of how to find rich and useful information on the candidates. Then I was reminded of the so-called “Dirty List” — this is a list regularly published by the Ministry of Labour which indicates the companies and farmers who are caught by government officials using workers in very lousy conditions, similar to slavery.

The list published in the Ministry’s website is in not-so-friendly PDF format, but it has a plus: there is not only the name of the companies or the entrepreneur/farmer, but also their registry numbers within the government. I remembered that in the Electoral Court one can also find the numbers. That was important because having the registry numbers would avoid ambiguities.

I had both lists: the donators to the previous elections (2008, 2006, 2004 and 2002) and the “Dirty” companies. But I had a problem; I did not know how to matchup the datasets. My tech knowledge allowed me to transform the PDFs into CSV, but I could no go further without help.

I then sent the datasets, in CSV format, to Transparencia Hacker, a Google Groups list which now gathers over 800 people interested in the connections between transparency and politics/public administration.

Within 2 days, the guys made the datasets talk, and we found that 16 politicians had been elected with the help of “Dirty” money in the 4 previous elections. Other 13 politicians had received donations from the “Dirty List” but had not succeeded in winning the elections.

A local newspaper told the story.

In October 2012, there are local elections in Brazil. Hope we can shed even more light in the candidates.

Behind the news:

Roughly how long did it take you to extract the data from the PDFs? Do you know how long the guys from Transparencia Hacker spent working on the data?

This was kind of easy. It took me just some minutes. The “Dirty List” is a 20-page PDF. I always use a website to convert it into xls or csv (I like Cometdocs for this work).

Here is the Dirty List, in PDF (last updated on the 8th of November, 2011; the list we used is in CSV but it it very outdated because it was due to January 2010) Here are the Electoral Court pages for the list of donators: 2002, 2004, 2006, 2008 and 2010.

What I asked the Transparencia Hacker community was to check whether the CNPJs (companies register number within the governments) in the CSV would match any item in the Electoral Court webpage. The guys worked on the data for 2 days.

Is sufficient data available to visualise the total amount lobbyists donated to political campaigns, and would it be useful to / no? If you were to visualise the info – what would the priorities be to show? Would any tools be useful to explore the data?

Yes, there is enough data. And YES, it would be very useful to visualize those links. I would prioritise the presidential and governor candidates as well as some Congressmen who hold top-positions in both Houses of Congress. Also, the donations to political parties (not to individual politicians) would be a plus.

A search form would be very useful. The search could have filters for position (Presidential candidate, governor candidate, political party etc), geography (Brazil, states) and donators (with no filters, just a blank for writing)

In your ideal world, in time for the impending elections – what would be done differently from last time? Any additional data you would like to see released?

I’d have to think more carefully to respond that, but concerning additional data: the number which identifies the market (the field) in which the companies work.

Interested in writing a “Behind the News” piece for the OpenSpending blog? Get in touch via our twitter account or email info [at] openspending.org.

Some useful links (mainly in Portuguese):

• Brasil adopts access to information law
• View the Dirty List in Full

This article was originally published on MediaShift Idea Lab and was co-written by Martin Keegan, project lead for Spending Stories and Lucy Chambers, Community Coordinator for OpenSpending.

How public funds should be spent is often controversial. Information about how that money has already been spent should not be ambiguous at all. People arguing about the future will care about the present, and if data about past or present public spending is available, many will certainly look at it. When they do, occasionally they will find errors, or believe themselves to have found errors.

OpenSpending, which aims to track every (public) government and corporate financial transaction across the world, encourages users to:

• augment the existing spending database with additional sources of data
• use that data — e.g., to write evidence-based articles and formulate informed decisions about how their society is financed.

Spending Stories is our effort to make OpenSpending a natural way to do data journalism about public spending.

The Problem

FACT 1: Errors occur in data, no matter how official the source.

FACT 2: Data wrangling (manipulating or restructuring datasets to correct inaccuracies, remix with other datasets to augment the data, or perform calculations on the data), generally improves data quality, for example, through reconciling entities and flagging amounts that are obviously incorrect.

FACT 3: Data wrangling can also introduce errors if not tackled correctly.

Crucial to ensuring the use of this data in articles or ensuring re-use by concerned citizens is the ability to show that the data is valid. In addition, maintaining a good relationship with public bodies who are confident that they are not being misrepresented in the data is vital to ensuring the data continues to be released in the first place. In practice, this means that the provenance of the data has to be clear including:

• where the data originally came from (preferably a URL)
• whether anyone (e.g., government, community data wrangler, or OpenSpending) has worked on the data since it was published, and what steps they took to change the data (i.e., these steps should be reproducible to produce the same result)

The OpenSpending team has gone to lengths to retain enough information to say who was responsible for both of the above.

OpenSpending is a system, somewhat like a wiki, which allows you to track back through the data wrangling process and work out what changes were made to the data, when and by whom.

Error reporting in practice

OpenSpending recently received a pointed inquiry from the U.K. Treasury disputing the claims we were making about the payment of British public money to a private company. Believing that an error had been introduced, we attempted to retrace our steps and find out where this had occurred, and who was responsible.

As we discovered, the payment had actually taken place, but the the OpenSpending descriptions used to label the transaction were not sufficiently detailed to accurately reflect the item in question.

With Spending Stories, we were able to retrace our steps because we had preserved a copy of the software tools we used for collecting the data (the data is published by about 50 public bodies, and must be downloaded, stitched together, and firmly molded into shape). These tools had been also made available to the public, so the Treasury and other concerned citizens could have checked our work themselves; the availability of this kind of check keeps all participants in the fiscal debate honest.

What had gone wrong was a problem of terminology: The transactions existed, but ambiguous language had been used to describe them, glossing over the distinction between the government department reporting what money had been spent and the government agency which actually spent the money. The bodies in question were the Department of Health and a regional health care trust; this distinction is certainly one which a concerned citizen would expect to be made clearly — so we should make sure our system makes it easy to know which question is being asked.

Checkpoints in OpenSpending

In the short term, we are mitigating the problem of data errors as follows:

• Data provenance – is the source identifiable and the process reproducible? OpenSpending encourages people to add modified datasets to a “package” in the Data Hub. This allows other users to see the original document alongside any modified documents and track the chain of changes made to see clearly which points errors could have been introduced.
• Crowdsourcing feedback on spending data.
• Permitting re-use of the structured data we present, so that it can inform decisions in other fact-checking systems.

Ultimately, we will build our part of the ecosystem to provide feedback to the political process, by improving democratic discourse about the public finances.

Lucy Chambers is a community coordinator at the Open Knowledge Foundation. She works on the OKF’s OpenSpending project and coordinates the data-driven-journalism activities of the foundation, including running training sessions and helping to streamline the production of a collaboratively written handbook for data journalists.

Martin Keegan is a software engineer and linguist, currently leading the Open Knowledge Foundation’s OpenSpending project. He is also on the Open Knowledge Foundation’s board, and has worked for SRI, Citrix, University of Cambridge and co-founded and worked for various civil society organizations.

This post is by Lucy Chambers, community coordinator at the Open Knowledge Foundation, and Friedrich Lindenberg, Developer on OpenSpending. They recently attended the Global Investigative Journalism Conference 2011 in Kyiv, Ukraine, and in this post, bring home their thoughts on journalist-programmer collaboration…

The conference

The Global Investigative Journalism Conference must be one of the most intense yet rewarding experiences either of us have attended since joining the OKF. With topics ranging from human trafficking to offshore companies, the meeting highlighted the importance of long-term, investigative reporting in great clarity.

With around 500 participants from all over the globe with plenty of experience in evidence gathering, we used this opportunity to ask many of them how platforms like OpenSpending can contribute, not only to the way in which data is presented, but also to how it is gathered and analyzed in the course of an investigation.

Spending Stories – the brainstorm

As many of you will be aware, earlier this year we won a Knight News Challenge award to help journalists contextualise and build narratives around spending data. Research for the project, Spending Stories, was one of the main reasons for our trip to Ukraine…

During the data clinic session as well as over drinks in the bar of “Hotel President” we asked the investigators what they would like to see in a spend analysis platform targeted at data journalists. Cutting to the chase, they immediately raised the key questions:

How will it support my work?

It was clear that the platform should support the existing journalistic workflow through publishing embargos, private datasets and note making. At the same time, the need for statistical and analytical heuristics to dissect the data, find outliers and visualize distributions was highlighted as a means to enable truly data-driven investigations of datasets. The goal in this is to distinguish anomalies from errors and patterns of corruption from policies.

What’s in it for my readers?

With the data loaded and analyzed, the next question is what value can be added to published articles. Just like DocumentCloud enabled the easy embedding of source documents and excerpts, OpenSpending should allow journalists to visualize distributions of funds, embed search widgets and data links, as well as information about how the data was acquired and cleaned.

What do I need to learn to do it?

Many of those we spoke to were concerned about the complexity required to contribute data. The recurring question was: should I even try myself or hire help? It’s clear that for the platform to be accessible to journalists, a large variety of data cleansing tutorials, examples and tools need to be at their disposal.

We’ve listed the full brainstorm on the OpenSpending wiki

You can also see the mind map with concrete points below:

Hacks & Scrapers – How technical need data journalists be?

In a second session, “Data Camp” we went through the question of how to generate structured data from unstructured sources such as web pages and PDF documents. We tried to emphasize the value of easily machine-readable data over less structured information by pointing to some examples on ScraperWiki.

As we went through basic steps needed to scrape a web page, the questions began turning towards the purpose of the exercise:

“So why do we need to learn how to scrape? Can’t we just hire someone to do this for us?”

Our answer went something like…

“Well, yes – you can actually, but…”

…it may be a good idea to have some understanding of which data can be easily retrieved and what difficulties and errors you might encounter in the extraction process. This includes:

1. Understanding the possibilities and limitations of various data structures on the web to understand how to approach programmers and what to ask for (and importantly, what it is reasonable to pay).
2. Understanding how to quality-check data extracted from the internet and where errors could be introduced.
3. Appreciating that programmers are expensive and that having a basic understanding of some of the principles behind screen scraping yourself could save your organisation quite a lot of money for simpler tasks

The notes from the scraping session are available on this pad

So how do I hire a hacker?

The final thing that became blatantly apparent in sessions such as “Journalist or Programmer? Do Reporters Need to become Coders?” was that there is a huge void that needs to be bridged between the hacker and journalist world. If I had a pound for every time someone at the conference asked me how they could find a hacker, would be mighty happy. We pointed people in the direction of Hacks and Hackers meetings but there is clearly a need for a more extensive ‘address’ book of reliable contacts is obvious.

I will attempt to pull together some of the thoughts we had about how to find (and trust!) your hacker in a separate post to address some of these needs. If you have further advice or anecdotes on this subject, please don’t hesitate to get in contact via the OpenSpending mailing list.