Prototyping OCDS data using spreadsheets
In this first post for our technical series, we look at the need and tools for creating a pilot OCDS dataset using spreadsheets. Check out part 2 that looks at how to create a template and how to use it to enter, check and convert data and part 3 on how to adapt the spreadsheet template for your needs.
You might already know that OCDS data can be published in both JSON and spreadsheet formats, but did you know that spreadsheet templates can also be used to produce OCDS JSON data?
In this blog we’ll be exploring how simple spreadsheet templates can be used to prototype an OCDS dataset and looking at the lessons learned from applying this approach to the open contracting pilot in Los Angeles.
OCDS is a JSON data standard because using a structured format like JSON makes it possible to accurately represent the complexities of different contracting processes. JSON is also the de-facto standard for communication on the web, making it easier for developers to build tools that work with OCDS data.
However, not all Open Contracting initiatives have the capacity to start generating JSON data from their systems right away, so undertaking a prototyping exercise using spreadsheets can help to demonstrate the value of bringing contracting data together using OCDS and can also help to surface some of the issues which would need to be addressed in a full implementation.
| Case study:
Open Contracting in Los AngelesSince 2018, the City of Los Angeles Mayor’s Office, the Sunlight Open Cities team, and the Open Contracting Partnership (OCP) have been collaborating to transform how businesses compete for opportunities in the city and to build a more diverse vendor pool for city contracts. One of the strengths of OCDS is the way in which it can be used to join up data which is spread across different systems. This is exactly the situation found in Los Angeles: the city’s contracting data is split across multiple systems with little integration and few common identifiers. Whilst OCDS provides a model in which data from different systems can be joined, significant effort can still be required to create the required integrations to enable data from the city’s systems to be joined up using OCDS. Since creating such integrations between LA’s contracting and finance systems was beyond the scope and technical capacity of the Open Contracting Pilot, the implementation team decided to focus on exporting what data they could from the city’s systems, making best efforts to join it on the identifiers that were available and then converting the joined data into a pilot OCDS dataset. |
Why create a pilot dataset and why use spreadsheets
There are three key benefits to creating a pilot OCDS dataset:
- Demonstrating the potential of a full OCDS implementation by showing the additional insights possible when contracting data is joined up and standardised.
- Prototyping the steps required to convert existing data into OCDS to inform scoping for a future implementation.
- Identifying where changes to existing systems are required, either to make joining data possible or ensure the right data is being collected to meet the city’s needs.
Spreadsheets may be an appropriate approach to creating a pilot dataset when:
- The technical skills of the team working on an Open Contracting pilot is better suited to working with spreadsheets than other technologies.
- Using spreadsheets would reduce the amount of manual data entry required, by making it possible to bulk copy data from exports from the existing systems.
- There are known issues with completeness of existing data and using spreadsheets would make it possible for some of the missing data to be added by hand.
- To allow for easy collaboration between the different teams involved in a pilot.
Of course, there are downsides to using spreadsheets to prototype OCDS data, including a lack of reproducibility and limitations on the size of the dataset, but a spreadsheet based approach can reduce barriers to generating and working with OCDS data.
Spreadsheet tools
There are a number of different tools for generating OCDS data from spreadsheets, including:
A tool for checking the structure and format of OCDS data and converting OCDS data between spreadsheet and JSON formats.
Try loading some sample data into the tool and clicking the ‘Convert to Spreadsheet’ button to see how this works.
The DRT can be used to check the structure and format of the data entered into a spreadsheet template and to generate an OCDS JSON file from a work in progress or completed spreadsheet template.
| Want to convert a larger OCDS dataset? The Data Review Tool is great for converting smaller datasets using a web interface, but if you need to convert a larger dataset, check out flatten-tool: a command-line tool for converting between flattened spreadsheet data and structured JSON data. |
A suite of command-line tools for working with OCDS data.
The mapping-sheet command from OCDS Kit can be used to generate a flattened version of the OCDS schema for use in a spreadsheet template.
A free online spreadsheet package.
Online tools such as Google Sheets can make collaboration between the different teams working on the prototype easier and can be configured to support a one-click method for sending data to the OCDS Data Review Tool for checking.
In part 2 of this blog we’ll cover how to build a spreadsheet template and how to enter, check and convert data using a template.
