Open Journalism & the Open Web

When can data help you find — or tell — a story?
We're used to relying on data to tell governmental and crime stories, but more and more structured information is being gathered every day. As more public transit systems start tracking the location of their trains and buses, we'll be able to measure their performance better (and we can tell people when the bus is really coming). Social media is providing all kinds of new data for us to filter; by working with structured elements of online social interaction, we can see how people react to the World Cup or an election. Some of this data will provide us with new stories; others will provide us with new ways of telling stories.

What are the risks inherent in relying on databases to inform journalism? How do you mitigate those risks?
You run the risk of thinking there is a story when there isn't one, or of simply misunderstanding the story. You mitigate that by understanding your data as deeply as you can -- what is supposed to go in each field, where the data comes from and how it's all entered and processed. This risk is compounded when you start crunching numbers and linking unrelated databases; a single bad formula could throw everything off. You can avoid this by vetting your process -- starting with the originating data and ending with your data-based conclusions -- with a third party, perhaps the subject of a story.

What can you do using databases that you couldn't otherwise do?
By filtering and sorting the data, you can, relatively quickly, look at volumes of information from a variety of different angles. With the right queries, you can dig for information that meets certain criteria -- perhaps to test a hypothesis. And you can use a database to give you a broad look at the information at hand, which frees you from relying on anecdotal information.

What can't you do using databases that you could through more conventional means?
Conventional reporting methods such as interviewing and developing sources can give you the "why" behind the data. Knowledgeable sources can also tell you what story the data should illustrate, so that you know what to look for. The example in Paul Bradshaw's post about the drop in certain kinds of parking offenses in London is a great example of the limitations of data -- it was a simple change in how the offenses were categorized. It was also an example of dirty data, which shows that data is only as good as the data entry.

Potential lecture discussion: I would be interested in discussing different ways to fact-check large data sets.

I wish this course would focus less on old style databases and more on emerging linked open data, which today is already practical - governments around the world are releasing their data. Sure, there are times you need to normalize data and use ACID properties, but I'm not linking these terms becase they're not that important for everyone. Computers are so powerful these days, programs so sophisticated, that I'd suggest it's more worthwhile to explore semantic databases that use link relationships, which whether ad-hoc or formalized let you codify not just your own data, but data from around the web (and please do think about giving back, the rewards will be monumental).

As for the role of data, every day I run across people who are talking about journalism's role today, and it's clear it's vitally important, to tell an informed, connected, responsible, comprehensible story with trustworthy facts. But I think things will become more complicated and involved, and data plays a part in that, like a puzzle the more data there is the more missing pieces become evident. If data gathering can become like a game we can step away from corporate control and into a more complex yet individually responsible reality that features journalism as a pinion.

For a multitude of projects I've developed ways to import data into a semantic database that can link people, places, times, etc (http://canbudget.zooid.org is an example but it needs more explanation). If anyone wants to try it out write me a note (simple spreadsheets are the easiest to import).

Thanks for your response Judd. One of the main principals of formal Linked Open Data (LOD) is any data can be joined to any other data (as long as you can find an identifying key). But I should have been more clear that it's still quite useful to understand and engage low level methods around importing, storing and accessing data using "traditional" tools, I just don't think everyone needs to get down to the often rather arbitrary and non transferrable nitty gritty. Understanding the basics of coding (variables, keys, if/then, querying, regular expressions, and now inference) will always be a huge benefit. But clearly opening data is happening at a breakneck pace, I bet there is an open data initiative for the cities and countries of everyone on the list (here's an example — http://www.toronto.ca/open/catalogue.htm ). When we start adding inferences to these massive connected data sets, we're at a next level of distributed comprehension. So there are many opportunities there, and in six months to two years people can understand how to find, navigate, organize or request data (which is not particularly a technical problem). I'm working on some examples of tying together different sources of data, otherwise there are lots of examples of useful (unformalized) "mash ups" on the Web. http://mashupguide.net/1.0/html/ch01s02.xhtml is an outstanding example.

Inference is of particular interest, it is artificial intelligence that can be used to augment discovery and build agents. When journalists start adding inferences to these large data sets, for example (I'm not a journalist!) an unusual number of new restaurant licenses and health problems in an area, a lot of the groundwork could be done without any effort.

I think it's also worth mentioning that data is a way to engage with readers. Whether it's providing a bare fact about their neighbourhood, enlisting their help in collecting it, or talking about the emerging data phenomenon. Participatory Web and the evolution of journalism (I presume :]) is also a huge topic to be covered for hacks and hackers. Most of the news sites I visit treat the people who comment like riff-raff, which is very disappointing to me since there are some very informed opinions and it is representative of interested persons.

David —

The identifying key is the trickiest part! Help me walk the class through how to best address these dilemmas from a hacker's perspective.

I hope this gets everyone's gears turning.

Here's what a "201"-level response to my assignment might look like, based on joining data. My apologies to anyone who might have thought of this to fulfill the assignment:

Take the White House visitor logs, the OpenSecrets.org database of lobbyists, and the OpenSecrets.org database of lobbying as received and cleaned from the Senate Office of Public Records.

Suppose you want to see all the lobbyists who have been to the White House since the Obama administration started releasing these records.

In theory, you join the lobbyists database to the lobbying database:

The lobbying database only contains information about each lobbying firm and its clients, and the lobbyists database only contains information about lobbyists, but you can join them because each record of a lobbyist seems to pertain to a single document number that is also pertinent to only one lobbying firm.

What's more, because the database is so large, you have to output the result of that union to a temporary table and create an index on it -- otherwise queries to that database would take forever to resolve, or would never return results. Hacks: This does exactly what you'd think it does -- it allows the RDBMS you're using to more quickly search the database for relevant records. But to do this quickly, the "page number" side of the index might only be generated based on the first few characters of the column (think "subject") you're using to create the index. The fewer characters you use the more quickly the index will work, but when you run a query and the RDBMS uses that index to find your results, you're also more likely to get the wrong result.

A hacker friend of mine fixes this somewhat by generating an additional column on both tables to serve as the source material for the index. Rather than the often similar business of a single column with first and last names, he fills that column with the output of an algorithm called MD5, which generates what's called a "hash" -- a sequence of letters and numbers -- based on each name. This should allow an index that's both reasonably fast and accurate.

Thus you can connect individual lobbyists to possible clients, and then compare the names on that list -- through your "hash" -- with the names on the White House visitor logs.

What you have at the end of this is a list of lobbyists who have the same names as people who visited the White House. Not immediately useful with lobbyists that have names like "John Smith."

With additional drilling on each name, you can then get a better guess if this person or that person really is a lobbyist.

For example, say you go out and scrape the White House phonebook so you have a list of the names, contact numbers and departments for everyone in the administration.

(Extra credit assignment!!)

Then, for each name, assume the visitor is a lobbyist, look at his or her client, and see if the name of the client makes sense based on the department of the visitee.

If the visitor's client is, for example, a Native American reservation, and the visitee is in the Department of the Interior (which is responsible for Native American affairs), you may be in business.

But that requires the bulk generation of inferences. You're talking about doing that for each of hundreds of names. And even if you get those names, what is the utility to you? You have evidence suggesting that a lobbyist paid by a reservation has visited someone in a position to influence policy.

Unless the date(s) of that visit(s) can be juxtaposed against, for example, the dates of important policy decisions — in this case from Interior — or against the issuance of a grant or contract award, the utility is minimal.

But wait! The Federal Register, which lists announcements of administration decisions, is now released daily in XML. And you can get data dumps from USASpending.gov, which tracks federal spending.

... And now you're generating a new table, one that lists the visits from probably lobbyists within, say, three months of a contract award to the client or a mention of the client in the Federal Register. How many different joins have you made at this point? And how many possible points of failure?

By the time you figure out how to juxtapose these things in a meaningful way, how much time have you invested in this project? And what's your likelihood of success?

This is what I was talking about in lecture when I suggested that you be careful how much time you invest in projects involving large and complex databases.

Now, hackers: What was wrong with my approach? How could I streamline it? And what tools could you build to streamline this type of process for future hacks?

And hacks: Am I asking the right questions? What would you do with the same type of information?

If if were doing a one-off, I'd probably use http://en.wikipedia.org/wiki/Denormalization or store the data in separate 'tables,' and write a program to associate and analyze them. For the results and extra/ongoing aspects, I'd import interesting data into an editable semantic system (eg SMW) and use an agent approach to periodically update it, although it could only store millions of linked entities.

First off I should say I didn't really mean denormalization, more like unnormalized. Store the data as convenient blob of fields, which means there's duplication but querying is easier. So instead of having to create a join on a key and doing complex queries because your design is so efficient each item is only represented once, you're querying on a list of items, maybe using 'distinct' to narrow down as required. Basically treating an RDBMS as a big spreadsheet, duplicating each combination, but for a purpose rather than generalized system it works. The table could be

Visitor|Organization|Department visited|Visit Date
John Smith|Ciggies Inc.|Dept of Health|June 1, 2010
Johnathan Smith|Tell It To The Hand, LLC|Depth of Health|June 5, 2010
Johnathan Smith|Tell It To The Hand, LLC|Depth of Health|June 15, 2010
John R. Smith|Ciggies are Healthy! Research|Depth of Health|July 15, 2010

and I would use a program to compare these to other tables...

1. for each $org, $dept in "select organization, department visited" from visits
1.1 for each $clientInterest in "select clientInterest from orgclients where org = $org" do
1.2 "select true from govDept where dept = $dept an deptInterest is not $clientInterest"
1.2.1 if not true, print "What the heck is $org meeting with $dept about $clientInterest for?"

Output might be:

What the heck is Ciggies are Healthy! Research meeting with Dept of Health about Letting the public know about the benefits of ciggies for?

Since it's a program I can also do things like find different varieties of names, companies, etc.

Of course this is simplified but I hope it makes sense.

For the canbudget project, I used semantic mediawiki. It couldn't easily handle tens of millions of entries, but for hundreds of thousands of entries (a narrowed down list?) can be useful. I entered the items in a spreadsheet, but any data source can be used. Once you have a data source, it's imported and can be viewed, queried and updated.

Say http://canbudget.zooid.org/wiki/2010/G20 is "White house visits." say the first Cost item is a visit. They're ordered by number (cost), let's say it's by number of visits.

The query for this single item is

{{ #ask: [[Topic::White house visits]]
|?Visitor name
|?Organization
|sort=Visits
|order=Desc
|limit=1}}

which would yield one item, also represented as http://canbudget.zooid.org/wiki/Special:Ask?title=Special%3AAsk&q=[[Topic%3A%3A2010%2FG20]]%0D%0A&po=Supplier&sort_num=&order_num=ASC&sort[0]=Canadian+dollar+cost+2010&order[0]=DESC&eq=yes&p[format]=broadtable&p[limit]=1&p[headers]=&p[mainlabel]=&p[link]=&p[intro]=&p[outro]=&p[default]=&eq=yes (long result directly into a query, check the bottom for the result)

but we're in a semantic wiki, so

http://canbudget.zooid.org/wiki/2010/G20_and_G8_Budget/License_for_use_o...

Click "edit to form" to see what updating looks like.

Or a clickable graph http://canbudget.zooid.org/wiki/Graph_of_procurement_process_and_topic , timeline drill-down (facet) browser http://canbudget.zooid.org/wiki/Procurement_exhibit , timeline http://canbudget.zooid.org/wiki/Dates etc

We have canned queries on each data type; procurement process:

http://canbudget.zooid.org/wiki/Sole_Source

Supplier:

http://canbudget.zooid.org/wiki/GTAA

You can also add inline annotations; "class of Smallville 1995" could be a subproperty of "went to school together" could be a subproperty of "probably knows." Then you can do broad to narrow queries on "probably knows" or any other annotations.

You can add inferences to a template, for example, if the address within 40 miles of a Ciggies Inc, add it to the category "Close to ciggies inc." These categories can be queried.

Since its a semantic system marked text has meaning and can be reused and constantly built on.

Because it's a wiki with a front end, you can open it up to a group of people to query or edit with forms. It's standard open source software so you don't have to "invent" or maintain anything, though you can extend it as needed.

We're starting to see ways to use open linked data (LOD), so your data can include data from sites like Freebase, and "official" sources. Here's a sample list today.http://esw.w3.org/SparqlEndpoints http://www.data.gov/ might be of particular interest to Usonians. With LOD the "back end" doesn't matter as long as it conforms to the data/query standard, so the data sets can be very large an distributed.

Enjoy! (but stay away from the ciggies)

I made a couple of errors and it's hard to read there's a version at http://canbudget.zooid.org/wiki/Churnalism

"Churnalism" refers news generated based on stats, of course the point here is people would work together to make real stories out of this.

"Potential lecture discussion: I would be interested in discussing different ways to fact-check large data sets."

Great idea, Steve. Wonder if it would be worth posting that over on help.hackshackers.com to see what wisdom the crowd has to offer on the topic?

Phillip.

I hope this gets everyone's gears turning.

Love it, Nick. :)