This site has been built with the data from councils and regions of Cameroon, but there is still work to do to complete all local councils across the country. And you can help! Whether you are a civil servant or a citizen, you can publish data from your locality. We explain below how to.
Publish and explore council budgets data on Cameroon Budget Inquirer
This guide explains step by step how to prepare and publish budget data to be able to explore them on Cameroon Budget Inquirer.
For an introduction to OpenSpending, please refer to the OpenSpending guide
Councils budgets in Cameroon are mainly available from municipalities in paper format. ASSOAL association and its partners have undertaken to collect and digitize these budgets, making it possible to share and reuse data online.
We explain here how to publish these data on the OpenSpending website in a format that will also allow everyone to explore the data on the Cameroon Budget Inquirer website.
These are the main steps to publish budget data on OpenSpending:
- Data validation
- Data cleaning
- Data sharing on DataHub.io
- Data publication on OpenSpending.org
- Data visualisation and exploration on Cameroon.OpenSpending.org
To start, let's have a look to the model of the dataset we use for budget council data
OpenSpending model for Budget Council Dataset
The Cameroon Budget Inquirer uses a predefined dataset model. You must comply with this model in order to be able to publish and visualize your data on Budget Cameroon Inquirer website. This means matching fields and format of columns from the original dataset to the dataset of destination (used by OpenSpending).
For budget council data, the dataset must have the following fields:
|Head-account||Numerical value for the head account||Three digits number without separator||Ex: 700||Head-account description||Description of head account||Text|
|Sub-account||Numerical value for the sub account||Six digits number including head-account and point as separator||700.101|
|Sub-account description||Description of sub account||Text|
|Amount||Investiment amount in FCFA||Number with decimal separator (point)||Ex : 1000000.0|
|Year||Year of budget||Date: YYYY|
|Division type||Type of administrative division (Region or Council)||Text||COUNCILREGION||Mandatory for visualization|
|Region||Name of the Region||Text||Extrême-Nord Nord Adamaoua Est Sud Centre Ouest Nord-Ouest Sud-Ouest Littoral||Mandatory for visualization|
|Council||Council name||Text||Ex: Yaounde1|
|Expense_type||Expense type description||Text|
|Revenue/Expenditure||Type or budget row (revenue or expenditure)||Text||REVENUEEXPENDITURE|
|Recurrent/Investment||Type of spending (recurrent or investment)||Text||RECURRENTINVESTMENT|
|Reporting type||Budget status (planned or executed)||Text||BUDGETACTUAL||ACTUAL = Executed|
1. Validation of data collected from paper version
You need to make sure that the data entered in digital format (Excel or CSV) includes all information from the paper version.
Let's see with a paper extract from a budget council in Cameroon (see picture below) and check information by information what they correspond to in the OpenSpending model (see following table):
|Paper version||Description||OpenSpending version|
|[Council Name]||Not available on the picture but appearing on the cover of the budget book. For this one, it is Douala 5 council.||Council||Recettes / Revenue||A budget plans expenditures and revenue. Here we have data on revenue.||Revenue/Expenditure|
|Exercice 2010||Adopted fical year. Here we have budget data for fiscal year 2010.||Year (except for columns refering to previous budget's year, see below)|
|Title||Empty||Do not report|
|Head, subhead, paragraph||Number that identifies the budget rows (account, subaccount) in the budget classification. For instance 7.12 identifies the account "Direct council taxe". 712.100 identifies the subaccount "Proceeds of water tax".||Head-account or Sub-account|
|Nature of revenue||Text description of the accoun or subaccount||Head-account description or Sub-account description|
|Executed revenue reported in the previous administrative account||Executed budget for the year N-2 of the written Fiscal year. Therefore, here we have executed budget for yeat 2008.||Amount (for Year = N-2 and Reporting type = ACTUAL)|
|Revenue estimated for the previous fiscal year.||Estimated revenue of the year N-1 of the the fiscal year. Here, we have estimated revenue for year 2009.||Amount (for Year = N-1 and Reporting type = BUDGET)|
|Revenue estimated voted by the municipal council.||Revenue estimated the current fiscal year (2010).||Amount (for Year N and Reporting type = BUDGET)|
|Revenue estimated approved by the supervisory authority.||Revenue approved by a superior authority (Department or Region).||If exist, replaceAmount (for Year = N and Reporting type = BUDGET)|
|Observations||Observation on the budget row||If exist Expense_type|
2. Data cleaning
When you manually enter or extract data from a PDF file, your resulting Excel or CSV file often contains conversion or seizure errors such as misplaced or splitted cells, etc. It is important to correct these errors before proceeding any further.
Your data set also contains recurring terms such as the names of projects. You must ensure that these references are all written in the same way. This will facilitate subsequent data analysis.
Finally, to publish a dataset on OpenSpending, you must ensure the quality of the data, like avoiding empty cells, and convert the dates and amounts in the correct format.
All these steps are part of the data cleaning process. Here, we use two main tools, a spreadsheet application such as LibreOffice Calc or Microsoft Excel and Open Refine, a tool to clean data.
Open Refine is a powerful tool to quickly clean your dataset. We will use Refine mainly for its "Cluster" feature that helps you to identify the different cells that could represent the same reference, and then allows you to assign a unique term to all these cells.
We use Excel or LibreOffice Calc for all other operations: aggregate divided cells; convert amounts in the correct currency; fill empty cells with a default value
Here are the tasks you may have to execute:
- Assign a default value to empty cells where value is required (for example, add "0" in the column amount if the cell is empty, make sure that all cells head-account and sub-account do have a number)
- Concatenate values splited into multiple cells
- Assign a unique value to the same references in the whole dataset (use standards if appropriate. For instance with names of communes use Yaounde6 but not Yaoundé 6th)
- Convert amounts in the right currency if necessary (here FCFA)
- Convert dates into Open Spending format (YYYY, ex: 2010)
- Save your file as a CSV with comma separated values
Tips and tricks for Excel
Tips and tricks for LibreOffice or OpenOffice are quite similars.
Remove line breaks in your data set:
Option Search and Replace (CTRL H) -> search "\n" replace all by " " (white space);
Avoid Excel replacing automatically comma separator with a point separator or add a separator:
Right click on the column (top), select option Format and then select Text as format (not number).
Transform 710100 into 710.100
Create a new column on the right of your columnSub-account which should be column C. Add title Sub-account to the new column.
In the first following cell of the new colum add the following function:
For Excel in english:
For Excel in french:
Then right click in the bottom-left corner of the cell to apply the function to all cells in the column.
Copy the column. Then right click on the header cell, option special paste and uncheck formula. Click on OK. Remove the unusefull column Sub-account on the left.
Finally format your column to Text format to avoid Excel to consider it as a column with numbers and add separators.
3. Data sharing on DataHub.io
In order to be able to publish you data on OpenSpending you need to share you data online (your dataset must have its own URL). We will use http://datahub.io a dataset management and publication tool.
We choose to organize our datasets first by councils and then by years on datahub.
Here is the publication process:
- Register on datahub.io
- Join the group OpenSpending Cameroun. You need to send a request to [email protected] and indicate your datahub user name.
- Once added to the group, go to the group page and click on “Add Dataset”
- Describe your dataset by setting a title (Budget_[CouncilName]), a description, the type of license (open) and keywords.
- Modify visibility to make your data “public” et then click on “Next: Add Data”.
- Once on the page ”Add data”, import CSV file from your computer by clicking on “Upload a file”. Indicate the name of the ressource (Budget_[CouncilName]_[Year]), add a description and specify the format (CSV).
- Click on “Save and add another”.
- Your dataset is now downloadable and viewable online. Repeat the procedure for all datasets available.
4. Data publication on OpenSpending.org
Once your dataset is available online, you just have to add it to the budget council dataset on openspending.org website.
To do so, send a request to [email protected] with the link of your dataset. The admin will check and add you data on OpenSpending.
If you want to learn more on how to use OpenSpending go to the OpenSpending guide.
5. Data visualization and exploration
Once you have received confirmation of the publication of your data on OpenSpending.org website then you can explore and visualize the data directly on this website.
Well done. It's now time to communicate your work! Use social media, publish an article on your Blog and contact the press.