Afghanistan data graphic — Wikileaks Afghanistan data: click image for the graphic. Illustration: Paul Scruton for the Guardian

Wikileaks' Afghanistan war logs: how our datajournalism operation worked

This article is more than 13 years old

The Wikileaks disclosure of thousands of Afghanistan war records was datajournalism in action. This is how we did it
Get the overview data
Guardian selection of key events from the Wikileaks Afghanistan files: the data

Well, we always wanted stories from data: now we've got it. In spades. With bells on. The Wikileaks ' Afghanistan war logs are a fantastic victory for investigative data basedjournalism, not only here at the Guardian but at the New York Times and Der Spiegel too.

It's also datajournalism in action. What we wanted to do was enable our team of specialist reporters to get great human stories from the information ? and we wanted to analyse it to get the big picture, to show how the war really is going.

It's been a busy month for those of us who work with data at the Guardian; this is how we got here.

It was central to what we would do quite early on that we would not publish the full database. Wikileaks was already going to do that and we wanted to make sure that we didn't reveal the names of informants or unnecessarily endanger Nato troops. At the same time, we needed to make the data easier to use for our team of investigative reporters: David Leigh , Nick Davies , Declan Walsh , Simon Tisdall , Richard Norton-Taylor . We also wanted to make it simpler to access key information for you, out there in the real world ? as clear and open as we could make it.

The data came to us as a huge excel file ? over 92,201 rows of data, some with nothing in at all or were the result of poor formatting. Anything over 60,000 rows or so brings excel down in dramatic fashion ? saving takes a painfully long period of time (tip number one ? turn automatic saving off in preferences…). It doesn't help reporters trying to trawl through the data for stories and it's too big to run meaningful reports on.

Fortunately, after COINS , huge datasets hold no fear for us. Harold Frayman , who with John Houston regularly wrestles data from PDFs and other formats for the Datablog ? built a simple internal database. Reporters could now search stories for key words or events. Suddenly the dataset became accessible and generating great stories became easier.

The data was well structured (you can read more about how that structure worked here) ie, events were categorized, sometimes more reliably than others.

We also started filtering the data to help us tell one of the key stories of the war: the rise in IED (improvised explosive device) attacks ? home-made roadside bombs which are unpredictable and impossible to fight. This dataset was still massive ? but easier to manage. There were around 7,500 IED explosions or ambushes (an ambush is where the attack is combined with, for example, small arms fire or rocket grenades) between 2004 and 2009. There were another 8,000 IEDs which were found and cleared. We wanted to see how they changed over time ? and how they compared.

The result is the data below ? which shows us:

? IED attacks over time
? Where they happened by region
? Casualties data recorded in the database

The casualties data brought its own challenges ? it was often inaccurately compiled and incomplete ? we've added Nato-recorded casualties too, to test the veracity of the data and you can see how they vary.

But this overview data doesn't convey the enormity of the thousands of explosions. One particular period ? the three days in the run-up to last year's presidential election saw over 100 IEDs explode. Imagine living with that every time you set off in a truck down the road?

This is where developer Daithi O Crualaoich came in. He helped us map the lats and longs of every event ? not only that but produced an editable map (vectored, the designers call that). And then graphic designer Paul Scruton could make that beautiful for the newspaper (you can download it from Scrib'd below).

Conveying that information online is a different skill ? Alastair Dant (with Igor Clark's help) created two interactives for the site:

? An interactive guide to all the IEDs , working from a spreadsheet with the geodata on (with design work from Paddy Allen and Mark McCormick )
? An interactive 'front page' to our selection of 300 key events .

Those key events were selected by the investigations team as being particularly interesting ? Alastair's guide helps you navigate around them, each one clicking through to a page created by Harold (with key developing work by Daithi and Lisa van Gelder ) where users can read the full report.

We wanted to make as much of the raw data available as we could and we've published some large datasets:

? The full set of significant incidents chosen by Guardian specialists
? The IED attacks data

It's inevitably the case that the work the helps shape a story is less interesting than the story itself. But in the future, as more and more of these datasets are released, these are skills that journalists will have to grapple with. As Roy Greenslade wrote yesterday :

The emerging form of disclosure through the internet, pioneered so successfully in the past couple of years by Wikileaks, deserves our praise and needs to be defended against the reactionary forces that seek to avoid exposure.

Have we published enough? Inevitably not. Have we started to make sense of an incredibly complex dataset? We hope so.

Now it's your turn. Can you help us make more sense of the raw info?