Open Contracting for Infrastructure Data Standard Field-Level Mapping Template

Use this template to document your sources of infrastructure project data and to document how that data maps to the Open Contracting for Infrastructure Data Standard (OC4IDS) - that is, identifying which data elements within your data sources match which OC4IDS fields.

To learn more about OC4IDS, read its documentation.

Important

Trying to map your procurement data to the Open Contracting Data Standard (OCDS)? Use the OCDS Field-Level Mapping Template instead.

Access the template

The template is available in the following versions and languages:

• Microsoft Excel (2010+) (English, Spanish)
• Google Sheets (English, Spanish)

How to use the template

Throughout the template, yellow-shaded cells are for data entry and green-shaded cells are calculated values.

1. Use the (Source) 1. Data sources sheet to list your sources of infrastructure project data.
2. Use the (Source) 2. Data elements sheet to list all of the data elements within your data sources.
3. Optionally, use the (CoST) IDS Elements sheet to select the elements of the CoST IDS that you want to map.
4. Use the (OC4IDS) sheets to match OC4IDS fields to your data elements, to identify gaps in the data that you collect, and to identify data elements that do not match any fields or codes in OC4IDS.

To learn more about using the template, see the OC4IDS Field-Level Mapping Template Tutorial.

Tip

Need help? The Open Contracting Partnership and CoST - the Infrastructure Transparency Initiative can provide free-of-charge support.

Developer documentation

The template is generated from the OC4IDS schema and documentation using manage.py create-template.

Tip

Not comfortable with using a local development environment? You can use the Google Colab notebook to update the template for a new version of the OC4IDS schema.

Set up your development environment

Clone the repository

git clone git@github.com:OpenDataServices/oc4ids-mapping-template.git
cd oc4ids-mapping-template

Subsequent instructions assume that your current working directory is oc4ids-mapping-template, unless otherwise stated.

Create and activate a Python virtual environment

The following instructions assume you have Python 3.8 or newer installed on your machine.

You can use either pyenv or python3-venv for this step.

pyenv

1. Install pyenv. The pyenv installer is recommended.

2. Create a virtual environment.

   pyenv virtualenv oc4ids-mapping-template

3. Activate the virtual environment.

   pyenv activate oc4ids-mapping-template

4. Set the local application-specific virtual environment. Once set, navigating to the oc4ids-mapping-template directory will automatically activate the environment.

   pyenv local oc4ids-mapping-template

virtualenv

1. Create a virtual environment named .ve.

2. Linux/MacOS users:

   python3 -m venv .ve

3. Windows users:

   py -m venv .ve

4. Activate the virtual environment. You must run this command for each new terminal session.

5. Linux/MacOS users:

   source .ve/bin/activate

6. Windows users:

   .\.ve\Scripts\activate

Install requirements:

pip install -r requirements.txt

Update the template for a new version of the OC4IDS schema

Excel

For each language:

1. Update the Excel template using the latest OC4IDS schema. For example, to update the English template:

python manage.py create-template -v latest -l en -c excel

1. Open the template in Excel or Excel Online. In each sheet, select all cells (Ctrl+A) and auto fit row height.
2. Open the field-level mapping template folder and:
3. Right-click on the current version of the template
4. Select File information > Manage versions
5. Upload the new version.

Google Sheets

For each language:

1. Update the Google Sheets template using the latest OC4IDS schema. For example, to update the English template:

python manage.py create-template -v latest -l en -c gsheets

2. Open the current version of the template (English, Spanish), go to File > Import and upload the updated version, choosing to replace the existing spreadsheet.

Edit the template's structure, format or static content

Edit config/sheets.json or config/mapping_columns.json to update:

• Sheet titles
• Sheet subtitles
• Column widths
• Column headers
• Column notes
• Example values
• Translations

Mapping sheets (prefixed with (OC4IDS) have consistent columns, which are specified in config/mapping_columns.json.

If you edit string values in the config files, you must update the associated translations.

Edit manage.py to update:

• Cell formats
• Formulae
• Data validation
• Logic for determining which mapping sheet to add each field to