{ "cells": [ { "cell_type": "markdown", "id": "0ea02b0c", "metadata": {}, "source": [ "
\n", " \n", "
\n", " \n", "
Run in Google Colab
\n", "
\n", " \n", " \n", "
\n", " \n", "
Download Notebook
\n", "
\n", " \n", " \n", "
\n", " \n", "
View on GitHub
\n", "
\n", " \n", "
\n", "\n", "# Swiss Parliament: Commissions\n", "\n", "The Federal Chancellery maintains data on the Swiss political system. The curia dataset is publicly available it provides data on political parties, parliamentary comissions, members of parliament and their affiliations. \n", "\n", "Parliament data is also available as [Linked Data](https://en.wikipedia.org/wiki/Linked_data). " ] }, { "cell_type": "markdown", "id": "06f10cf8", "metadata": {}, "source": [ "## Setup" ] }, { "cell_type": "markdown", "id": "9b91c431", "metadata": {}, "source": [ "### SPARQL endpoint\n", "\n", "Swiss political data can be accessed with [SPARQL queries](https://www.w3.org/TR/rdf-sparql-query/). \n", "You can send queries using HTTP requests. The API endpoint is **[https://lindas.admin.ch/query/](https://lindas.admin.ch/query).** " ] }, { "cell_type": "markdown", "id": "b7a85d46", "metadata": {}, "source": [ "### SPARQL client\n", "\n", "We will use the `SparqlClient` from [graphly](https://github.com/zazuko/graphly) to communicate with the database. \n", "Graphly will allow us to:\n", "* send SPARQL queries\n", "* automatically add prefixes to all queries\n", "* format response to `pandas` or `geopandas`" ] }, { "cell_type": "code", "execution_count": null, "id": "c0bfc3f4", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import plotly.express as px\n", "import plotly.graph_objects as go\n", "import matplotlib.pyplot as plt\n", "import matplotlib.colors as mcolors\n", "import warnings\n", "\n", "from plotly.subplots import make_subplots\n", "from graphly.api_client import SparqlClient\n", "\n", "%matplotlib inline\n", "pd.set_option('display.max_rows', 100)\n", "warnings.filterwarnings('ignore')" ] }, { "cell_type": "code", "execution_count": null, "id": "5abffb11", "metadata": {}, "outputs": [], "source": [ "# Uncomment to install dependencies in Colab environment\n", "#!pip install git+https://github.com/zazuko/graphly.git" ] }, { "cell_type": "markdown", "id": "d06f39ee", "metadata": {}, "source": [] }, { "cell_type": "code", "execution_count": null, "id": "35033b56", "metadata": {}, "outputs": [], "source": [ "sparql = SparqlClient(\"https://int.lindas.admin.ch/query\")\n", "\n", "sparql.add_prefixes({\n", " \"schema\": \"\"\n", "})" ] }, { "cell_type": "markdown", "id": "1ffbace4", "metadata": {}, "source": [ "SPARQL queries can become very long. To improve the readibility, we will work wih [prefixes](https://en.wikibooks.org/wiki/SPARQL/Prefixes).\n", " \n", "Using the `add_prefixes` method, we can define persistent prefixes. \n", "Every time you send a query, `graphly` will now automatically add the prefixes for you." ] }, { "cell_type": "markdown", "id": "dbdad6bc", "metadata": {}, "source": [ "# Political Parties" ] }, { "cell_type": "code", "execution_count": null, "id": "a7856a09", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?council ?faction (COUNT(?mep) AS ?members)\n", "FROM \n", "FROM \n", "WHERE {\n", "\n", " \tVALUES ?_council { }\n", "\t?mep a schema:Person;\n", " schema:memberOf/schema:memberOf ?_council;\n", " schema:memberOf ?role.\n", " \n", " FILTER NOT EXISTS { ?role schema:endDate ?end }\n", " \n", " ?role schema:memberOf ?_faction.\n", " ?_faction a ;\n", " schema:name ?faction.\n", " \n", " ?_council schema:name ?council.\n", " FILTER (lang(?council) = 'de')\n", " FILTER (lang(?faction) = 'de')\n", "}\n", "GROUP BY ?council ?faction\n", "ORDER BY DESC(?council) DESC (?members)\n", "\"\"\"\n", "\n", "df = sparql.send_query(query)" ] }, { "cell_type": "code", "execution_count": null, "id": "f7f8e444", "metadata": {}, "outputs": [], "source": [ "N_FACTIONS=df.faction.nunique()\n", "cmap = plt.get_cmap('YlOrRd')\n", "colors = [mcolors.rgb2hex(cmap((i+1)/N_FACTIONS)) for i in range(N_FACTIONS)]\n", "factions = [\n", " \"Grünliberale Fraktion\",\n", " \"Grüne Fraktion\",\n", " \"Die Mitte-Fraktion. Die Mitte. EVP.\",\n", " \"Sozialdemokratische Fraktion\",\n", " \"FDP-Liberale Fraktion\",\n", " \"Fraktion der Schweizerischen Volkspartei\"\n", "]\n", "colormap = dict(zip(factions,colors))\n", "\n", "fig = make_subplots(rows=1, cols=3, subplot_titles=df[\"council\"].unique(), specs=[[{\"type\": \"pie\"}, {\"type\": \"pie\"}, {\"type\": \"pie\"}]])\n", "for i, council in enumerate(df[\"council\"].unique()):\n", "\n", " members = dict(zip(df[df.council == council][\"faction\"],df[df.council == council][\"members\"]))\n", "\n", " fig.add_trace(go.Pie(\n", " values=[members[f] if f in members else 0 for f in factions],\n", " labels=factions, sort=False\n", " ), row=1, col=i+1)\n", " fig.update_traces(textinfo='none', marker={\"colors\": [colormap[f] for f in factions]})\n", "\n", "fig.update_annotations(yshift=-280)\n", "fig.update_layout(height=400, title={\"text\": \"Members of Parliament (as of today)\", \"x\": 0.5})\n", "fig.show()" ] }, { "cell_type": "markdown", "id": "5b67cb53", "metadata": {}, "source": [ "# Parliamentary Committees" ] }, { "cell_type": "code", "execution_count": null, "id": "5fcba7d8", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?council ?committee (COUNT(?mep) AS ?members)\n", "FROM \n", "FROM \n", "WHERE {\n", "\n", " \tVALUES ?_council { }\n", "\t?mep a schema:Person;\n", " schema:memberOf/schema:memberOf ?_council;\n", " schema:memberOf ?role.\n", " \n", " FILTER NOT EXISTS { ?role schema:endDate ?end }\n", " \n", " ?role schema:memberOf ?_committee.\n", " ?_committee a ;\n", " schema:additionalType schema:MainCommittee;\n", " schema:name ?committee.\n", " \n", " ?_council schema:name ?council.\n", " FILTER (lang(?council) = 'de')\n", " FILTER (lang(?committee) = 'de')\n", "}\n", "GROUP BY ?council ?committee\n", "ORDER BY DESC (?members)\n", "\"\"\"\n", "\n", "df = sparql.send_query(query)" ] }, { "cell_type": "code", "execution_count": null, "id": "3f1caa47", "metadata": {}, "outputs": [], "source": [ "px.treemap(df, path=[\"council\", \"committee\"], values=\"members\")" ] }, { "cell_type": "markdown", "id": "ce1459e2", "metadata": {}, "source": [ "The above chart shows the number of members per committee." ] }, { "cell_type": "markdown", "id": "16f3c7f3", "metadata": {}, "source": [ "# Commissions and Factions" ] }, { "cell_type": "code", "execution_count": null, "id": "e7b08858", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?faction (COUNT(?mep) AS ?members)\n", "FROM \n", "WHERE {\n", "\n", "\t?mep a schema:Person;\n", " schema:memberOf/schema:memberOf ;\n", " schema:memberOf ?role.\n", " \n", " FILTER NOT EXISTS { ?role schema:endDate ?end }\n", " \n", " ?role schema:memberOf ?_faction.\n", " ?_faction a ;\n", " schema:name ?faction.\n", " \n", " FILTER (lang(?faction) = 'de')\n", "}\n", "GROUP BY ?faction\n", "ORDER BY DESC(?faction) DESC (?members)\n", "\"\"\"\n", "\n", "df_members = sparql.send_query(query)" ] }, { "cell_type": "code", "execution_count": null, "id": "5924d318", "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ?faction (count(?_committee) as ?committees)\n", "FROM \n", "WHERE {\n", "\n", "\t?mep a schema:Person;\n", " schema:memberOf ?ufa_role;\n", " schema:memberOf ?committee_role;\n", " schema:memberOf ?faction_role.\n", " \n", " ?ufa_role schema:memberOf .\n", " \n", " FILTER NOT EXISTS { ?ufa_role schema:endDate ?end }\n", " \n", " ?faction_role schema:memberOf ?_faction.\n", " ?committee_role schema:memberOf ?_committee.\n", " ?_faction a ;\n", " schema:name ?faction.\n", " ?_committee a schema:ParliamentaryCommittee;\n", " schema:additionalType schema:MainCommittee.\n", " \n", " FILTER (lang(?faction) = 'de')\n", "}\n", "GROUP BY ?faction\n", "ORDER BY DESC (?count)\n", "\"\"\"\n", "\n", "df_committees = sparql.send_query(query)" ] }, { "cell_type": "code", "execution_count": null, "id": "5adae137", "metadata": {}, "outputs": [], "source": [ "df = pd.merge(df_committees,df_members, on='faction', how='left')\n", "df[\"metric\"] = df[\"committees\"]/df[\"members\"]\n", "df.sort_values(by=\"metric\", ascending=False, inplace=True)\n", "df" ] }, { "cell_type": "code", "execution_count": null, "id": "c4ba33fd", "metadata": {}, "outputs": [], "source": [ "fig = px.bar(df, x=\"faction\", y=\"metric\", hover_data=[\"committees\", \"members\"], labels={'metric':'Affiliations per member', \"faction\": \"Faction\", \"committees\": \"Committee affiliations\",\"members\": \"Members\"})\n", "\n", "fig.update_layout(\n", " title='Committee Affiliations', \n", " title_x=0.5,\n", " yaxis_title=\"Affiliations per member\",\n", " xaxis_title=\"\"\n", ")\n", "fig.show()" ] }, { "cell_type": "markdown", "id": "425d5bfd", "metadata": {}, "source": [ "The above chart shows in how many committees members of the different parties are engaged in on average." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.0" }, "title": "Parliamentary Commissions", "vscode": { "interpreter": { "hash": "52612798f5eced5fbe3f58e9481faf8401bf1ada0bff399e6095e3d5ee493763" } } }, "nbformat": 4, "nbformat_minor": 5 }