Where does the money go? Brno Participatory Budgeting edition
Getting started
The first day it felt like in school: a childish first-grade-like thought “what if others ain’t gonna like me…” turned into a real concern when I learned that we were to partner up with someone for a final project. To my greatest relief, I met Adela. We hit it off right away and decided to work together.
We both agreed on what we want from a project:
- Use Python;
- Get acquainted with as many other tools as possible;
- Make sure it’s useful;
- Something that deals with money 🤑
These were quite broad preferences so we were open to pretty much anything.
Later on, after a successful networking event, where Adela had a very productive talk with Robert Spál, local government representative, the project was on.
Brno Participatory Budgeting Analysis
The initial goals were the following:
- Get an overview of where and what projects are created and how much money goes into them (by location, category).
- Study what’s behind the decision-making process when choosing whether a submitted project is appropriate or not.
- If we manage to integrate the election results by districts into the project and see whether the districts with higher voter turnout also have more proposed projects within the participatory budgeting. Later on, all of us agreed that that step was unnecessary and we didn’t carry on with it.
Main questions:
- How much money goes to what (by category/location)?
- What type of projects are proposed most often?
- What project categories become the most popular among voters?
- What projects get rejected as unrealistic?
- What projects do not receive support in the first round (by category/location/concept)?
Meet you mentor
The next social challenge was to choose (and to be chosen by) a mentor, and luckily Michal Kolacek became the one. Getting ahead of myself, I must mention what a great job Michal has done for us. Not only is he a great professional in the field who seems to know all the answers but also a careful mentor, always paying attention to our needs and struggles.
The fieldtrip
After studying the data, we gathered some important conceptual questions and set up a meeting with Marco Banti, a Head of Participatory budgeting Office. On the meeting, Mr. Banti covered all the technical details we were missing, such as:
- admin interface;
- first round of the tender;
- main issues of the Office;
- background and history;
- experience of the international participatory budgeting community.
Before hackathon
Now, when we had a better picture, a fun but painful part of web scraping started. Since Adela and I decided to work together on each step, we created Google Colaboratory document. Michal carefully supervised the development of the script and led us towards a more efficient way to rewrite it.
- We worked with data provided in a dynamic form through API. Voting data are located on the Participatory Budgeting webpage.
- We transfered all datasets into CSV file, which wasn’t efficient enough. The example below is the script for one year of voting:
3. We learned about the existence of Beautifulsoup and improved working with Pandas, which made it a little more sophisticated:
4. Next - we joined two Data Frames, and that’s it. I could finally get some sleep before the Hackathon.
Hackathon
The agenda was set up as follows:
- Morning: clarify the technical solution (API, GitHub, Heroku, Google Sheets, import to Tableau);
- Morning/afternoon: finish Python script;
- Afternoon: play with visualizations in Tableau.
We stuck to the plan and by the end of the day I felt like my head was about to explode (that’s how much smarter I became).
Now let’s zero in on each step.
Morning
After getting credential from Google API we now were able to push our dataset into Google Sheet. Then the question appeared — How to make sure our dynamic data keeps updating if it was going to run through a server on one of our computers?
That’s when (again) Michal came to rescue. Now we could use Heroku to host, schedule the script and update our data.
Afternoon
Among other things, Michal gave us a lecture on how to use regular expressions. It helped to get the id number from the HTML code in a more sophisticated way. Also we made sure the code looked nice and short.
Before:
After:
Hackathon Summary
The script was completed and published on GitHub, it runs through Heroku, the dataset is exported to Google Sheets and final visualization is performed via Tableau Public.
I wish it were as simple as it sounds.
After Hackathon
More studying
My life was now divided into “before-” and “after Hackathon”. And in that phase we were so excited to learn more that we turned to Udemy course to gather some practical knowledge on working with Tableau (as if we didn’t have enough studying to do).
Cleaning the data
We cleaned the data. It took a while. Although it wasn’t as horrible as we expected, besides, we already knew exactly what to do after having done it in Excel.
Vizualization details
We gave ourselves some time to play around with the dataset, come up with various visualizations/dashboards after which I wound up focusing on budget allocation. That’s where Adela and I devided the work. Adela was now mainly working with the data on votes.
While locating projects based on the GPS details, we came to realize that there are some of them, that couldn’t be assigned to a specific place. Therefore, those projects are visualized in an additional map of Brno within the main map.
In order to make visualization of districts more clear, we downloaded additional spatial data for Tableau and adjusted the script so that the districts matched.
Along with the main dashboar, we also created several visualizations for analysing particular data. The final visualization of the project is published under our team’s account on Tableau Public.
The results
The links in this section will lead you to Tableau Public visualization of our project and analysis of particular data. I recommend anyone who reads this, opening them up because they look pretty.
The map shows the districts where the budget tends to allocate. It only shows the projects that have already been realized or are still under construction.
Many districts in Brno are rather active in submitting new ideas for the city but, unfortunately, so far, not all have been so lucky to get their projects to be accepted.
Failed projects tend to have random values of budgets from 0 CZK up to over 20 mil CZK, with the limit set up to 3 mil CZK. Which is one of the reasons the projects were rejected in the first place.
More expensive projects tend to take longer to get finished.
The first year of the Participatory Budgeting program was rather experimental when it comes to proposing budget for a project, which we can see on the pictures below. Over the years proposers have started to ask for more realistic sums.
The analysis of voting preferences over budget shows that voters don’t tend to focus on budget but on the idea of the project itself.
The summary of budget allocation along with the analysis of votes were visualized in both Czech and English.
We’are submitting the dashboard and results of our project, hoping it will come in handy for Participatory budgeting Office in Brno and many other cities where the program will expand. There’s nothing better for a resident than to be able to use their voice for good and that’s where they have an opportunity to do so. Don’t you miss your chance either.
Acknowledgement
There are not enough cupcakes in the world that I would love to give them as a thank you to Adela and Michal for all their support. Especially in the darkest of times when my computer would refuse to work.
The Team Czechitas has changed my life. I’m grateful to everyone who participated in my learning process and made the impossible, possible.
