See below for details of how to get the data you need.
The IATI XML standard is hard to represent in a simple ‘flat’ spreadsheet structure. Aid activities can have multiple dimensions (sectors; policy markers and locations), and are made up of multiple components (descriptions; transactions; budgets and documents) – so any spreadsheet which included a row for each could include 100s or rows for a single project, with a lot of duplication. However, having a CSV version of IATI data is really important for policy markers, researchers, journalists, activists and others to be able to explore the data in familiar software such as spreadsheets and statistical packages.
- The IATI Explorer Toolkitnow includes two default CSV versions of IATI Data for use in spreadsheets and other software. The ‘Activities CSV’ option includes all the data from an activity in a single spreadsheet row. Multiple components and dimensions are joined together in single cells. The ‘Transactions CSV’ option includes transactions separated out onto their own rows, linked to an IATI-Identifier.You can generate these CSV files from the /csv/ tool (which will take one donor file and output it in Activities or Transactions CSV), or, if you want to fetch activities from across different files (perhaps all the activities for a given country or a given funder), you can use the /query/ tool and choose the relevant type of CSV output. (You can also find the relevant code to run these transformations on your own computer in the IATI XSLT Library)
- The Open Data Cook Book now includes a recipe for using the Google Refine tool to reshape IATI CSV datato get at the particular dimensions you want: for example, expanding out activities ready for analysing spend or commitments by sector.The Open Data Cook Book recipe also highlights how you can use Google Refine for basic analysis of IATI Data – setting up faceted browsing over the data. Whilst the IATI Data Explorer can only cope with a few hundred records at a time in it’s faceted browsing, Google Refine lets you look over thousands of records at a time.
If you are using Google Refine to do particular things with IATI Data – get in touch. Refine makes it easy to share workflows – so you might find the steps you have taken to work with data could be useful to someone else too.
If you’ve got questions about how to use refine to explore the data, you can ask them here also, or ask questions over on the IATI Standard Support Site.