Data Processing#

This section outlines how IATI data is processed by IATI Tables.

Flattening#

Top-level elements#

There are two top-level tables in IATI Tables:

  1. The activity table is generated from Activity files, and contains rows of iati-activities/iati-activity elements.

  2. The organisation table is generated from Organisation files, and contains rows of iati-organisations/iati-organisation elements.

All other tables are children of these top-level tables. Tables prefixed with organisation_ contain child elements of the iati-organisations/iati-organisation element, and can be joined back to the organisation table using the column _link_organisation. The remaining tables contain child elements of the iati-activities/iati-activity element, and can be joined back to the activity table using the column _link_activity.

Singular child elements#

Child elements which can appear zero or one times become columns in the parent table.

For example, the iati-activities/iati-activity/iati-identifier element becomes the column iati-identifier in the activity table.

Repeatable child elements#

Child elements which can appear more than once are unnested into a new table.

For example, the iati-activities/iati-activity/transaction element becomes the table transaction.

Narrative elements#

Elements which contain a narrative element are flattened into a single string column in the parent table.

For example, given the following iati-activities/iati-activity/title element:

<title>
    <narrative>Activity title</narrative>
    <narrative xml:lang="fr">Titre de l'activité</narrative>
    <narrative xml:lang="es">Título de la actividad</narrative>
</title>

This element will be transformed into the string: “Activity title, FR: Titre de l’activité, ES: Título de la actividad”, and stored in the column title_narrative in the activity table.

Common columns#

The following columns are present in all tables:

_link

The primary key for each table.

_link_activity or _link_organisation

The foreign key to the activity or organisation table respectively.

dataset

The name of the dataset this row came from. This can be used to find the dataset in the IATI registry, using the URL: https://www.iatiregistry.org/dataset/<DATASET_NAME>.

prefix

The registry publisher ID this row came from. This can be used to find the dataset in the IATI registry, using the URL: https://www.iatiregistry.org/publisher/<PREFIX>.

Codelists#

Codelists are joined to the tables as part of the process.

For example, given the iati-activities/iati-activity/activity-status element, whose attribute @code uses the ActivityStatus codelist:

<activity-status code="2" />

This element will be transformed into two columns in the activity table:

  • The column activitystatus_code with the value 2.

  • The column activitystatus_codename with the value Implementation.

Currency Conversion#

IATI Tables converts the iati-activities/iati-activity/transaction/value element to USD.

This is calculated by pulling the Code for IATI IMF exchange rates dataset, and using the transaction’s @value-date attribute to determine the exchange rate to use.

The result is stored in the transaction (trans) table in a new column value_usd.

Transaction Splitting#

IATI Tables transforms the transaction (trans) table to proportionally split the transaction values across multiple sectors or countries/regions.

It follows the same methodology set out in the CDFD documentation.