Calculations

First we want to select all members the European Union and the European Free Trade Association. A great way to start of is by using the Wikipedia Query Service and using the Cats example and continue editing from there. Let's start by selecting all countries in Europe.

Next, we want to apply a UNION to get countries from both the European Union and European Free Trade Association. Additionally we want to remove doubles that occur through the union, by using the DISTINCT modifier before SELECT.

Let's define a utils function in utils.py that returns a Pandas DataFrame with all the columns from the Wikidata query.

Final Query for each Country

Let's collect all mayors and their birth cities with Wikidata. We create the script load_european_mayor.py which collects and cleans the data based on the results from the following query. In this case we collect the information for all mayors in Germany.

It appears that Munich has three mayors currently running, which is after double checking indeed the case. Take note that Wikidata is continuously updated and information can be incomplete for certain items.

All Mayors in Europe

After collecting the data we can now explore it.

How many Mayors (with birth place) are per Country in Wikidata?

How does that look like on the Map?

What is the Average Age of a Mayor?

We can see that 55 years is the prime age to be a mayor