Open Data Editor in Action: Strategic Data Cleansing for Catalan Municipalities

This text shows a real case of how the Open Data Editor (ODE) impacted the workflow of an organisation working to serve the public good.

The StoryData team during one of the data visualisation courses offered. (source)

Organisation: StoryData
Location: Barcelona, Spain 🇪🇸
Knowledge Area: Data Journalism
Type of Data: Municipal data (population, income, strategic plans) and SDGs

StoryData is a data communication project based in Barcelona. It combines research, analysis, visualisation and storytelling to reveal the stories behind the data. Its mission is to address the current communication gap by rigorously practising data journalism and advocating for the openness of data to citizens, with the aim of democratising public information and combatting the post-truth era.

The Challenge

Problem:

StoryData collaborates with Catalan public administrations to make complex data accessible to citizens. One of its projects with the Barcelona Provincial Council involved analysing 50-year Partial Territorial Plans and cross-referencing population, family income and developable land data with the Sustainable Development Goals (SDGs).

The challenges were:

Fragmented data: Information was scattered across multiple sources, such as the Catalan Institute of Statistics and municipal records.
Serious inconsistencies: Variable formats (e.g. numbers with or without decimal points), cells combined with multiple municipalities, values calculated without raw data.
Lack of standardisation: Historical data is stored in unstructured spreadsheets that are designed for humans, but not for machines.

Impact

Errors in the datasets (e.g. 1000 vs. 1.000) distorted the key visualisations used to evaluate public policies.
The manual cleaning process took weeks, delaying projects.
The inability to reuse inaccurate data limited transparency and the potential of open data.

The population data for each Catalan municipality between 1987 and 2022 is presented differently in these two spreadsheets, with different values in the columns.

The same was true of historical household income data between 1991 and 2020: each data source had a different scope and collection method, and there were standardisation issues.

The Solution

StoryData used Open Data Editor to:

Automatically detect errors: Inconsistencies in numerical formats and dates, and missing values in datasets with thousands of rows.
Standardise metadata: Tag critical variables (e.g. annual population by municipality) to align them with the SDGs.
Prepare data for visualisation: Clean datasets can be exported to tools such as Looker Studio, enabling the comparison of municipal progress on goals such as reducing inequalities or achieving sustainable cities.

In this case, ODE was used to identify incorrect dates in a column, which had made it impossible to add the data to other spreadsheets.

Here, ODE pointed out the cells in which a dot was used to represent thousands. Curiously, this did not occur in all cells.

As can be seen in the above screenshot, ODE highlights the blank cells, which would be impossible for the human eye to detect.

The Results

After cleaning the data using Open Data Editor, the StoryData team developed complex dashboards to track urban and territorial strategic plans. They cross-referenced the analysed information with the Sustainable Development Goals (SDGs).

Clear visualisations of quality data helped technicians to:

Compare the performance of municipalities in relation to the SDGs.
Identify gaps (e.g. municipalities without post-2010 income data).
Justify investments in sustainability using standardised evidence.

Below, we highlight the tabs by subject area.

Quote

Eli Vivas, Co-founder of StoryData

“With ODE, we can now identify invisible errors in historical data, such as inconsistent decimal points that would otherwise ruin visualisations. Now, we can provide administrations with clean datasets, fulfilling their mandate for transparency and accelerating our journalistic work.”

About the Open Data Editor

The Open Data Editor (ODE) is Open Knowledge’s new open source desktop application for nonprofits, data journalists, activists, and public servants, aiming at helping them detect errors in their datasets. It’s a free, open-source tool designed for people working with tabular data (Excel, Google Sheets, CSV) who don’t know how to code or don’t have the programming skills to automatise the data exploration process.

Simple, lightweight, privacy-friendly, and built for real-world challenges like offline work and low-resource settings, ODE is part of Open Knowledge’s initiative The Tech We Want — our ambitious effort to reimagine how technology is built and used.

And there’s more! ODE comes with a free online course that can help you improve the quality of your datasets, therefore making your life/work easier.

Download Open Data Editor

↪ Take the course: Learn how to use ODE

All of Open Knowledge’s work with the Open Data Editor is made possible thanks to a charitable grant from the Patrick J. McGovern Foundation. Learn more about its funding programmes here.