{ "cells": [ { "cell_type": "markdown", "id": "c63fc66e-2e1a-4972-a030-c67660757990", "metadata": {}, "source": [ "# Create a Gannt chart of Australian government departments\n", "\n", "This notebook creates a Gannt-style chart showing the creation and dissolution dates of Australian government departments.\n", "\n", "You can [view the Wikidata SPARQL query](https://w.wiki/5tXN) used for this visualisation using the Wikidata Query Service." ] }, { "cell_type": "code", "execution_count": 8, "id": "9b657cfd-13ec-4436-9011-b10fb7da5b0f", "metadata": {}, "outputs": [], "source": [ "import altair as alt\n", "import pandas as pd\n", "from SPARQLWrapper import JSON, SPARQLWrapper" ] }, { "cell_type": "code", "execution_count": 9, "id": "1b9ac56f-dc6e-4fef-9571-16f77b2a6971", "metadata": {}, "outputs": [], "source": [ "sparql = SPARQLWrapper(\"https://query.wikidata.org/sparql\")" ] }, { "cell_type": "code", "execution_count": 10, "id": "df1f92d9-a13d-4b90-95ed-0f19bcd83313", "metadata": {}, "outputs": [], "source": [ "sparql.setQuery(\n", " \"\"\"\n", "SELECT\n", " ?agency ?agencyLabel\n", " ?naa_id ?start_date ?end_date\n", "WHERE {\n", " ?agency wdt:P31 wd:Q57605562;\n", " wdt:P10856 ?naa_id;\n", " wdt:P571 ?start_date;\n", " OPTIONAL { ?agency wdt:P576 ?end_date. }\n", " SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\". }\n", "} ORDER BY ?start_date\n", "\"\"\"\n", ")\n", "sparql.setReturnFormat(JSON)\n", "results = sparql.query().convert()" ] }, { "cell_type": "code", "execution_count": 11, "id": "015d67c9-8f81-45a3-98e8-429db105036f", "metadata": {}, "outputs": [], "source": [ "df = pd.json_normalize(results[\"results\"][\"bindings\"], sep=\"_\")" ] }, { "cell_type": "code", "execution_count": 12, "id": "ff5f5424-90e8-46ab-88be-421a38a2db59", "metadata": { "tags": [ "hide_cell" ] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
agency_typeagency_valuenaa_id_typenaa_id_valuestart_date_datatypestart_date_typestart_date_valueend_date_datatypeend_date_typeend_date_valueagencyLabel_xml:langagencyLabel_typeagencyLabel_value
0urihttp://www.wikidata.org/entity/Q113757341literalCA 11http://www.w3.org/2001/XMLSchema#dateTimeliteral1901-01-01T00:00:00Zhttp://www.w3.org/2001/XMLSchema#dateTimeliteral1976-12-07T00:00:00ZenliteralDepartment of the Treasury
1urihttp://www.wikidata.org/entity/Q16959680literalCA 6http://www.w3.org/2001/XMLSchema#dateTimeliteral1901-01-01T00:00:00Zhttp://www.w3.org/2001/XMLSchema#dateTimeliteral1921-12-21T00:00:00ZenliteralDepartment of Defence
2urihttp://www.wikidata.org/entity/Q16959770literalCA 10http://www.w3.org/2001/XMLSchema#dateTimeliteral1901-01-01T00:00:00Zhttp://www.w3.org/2001/XMLSchema#dateTimeliteral1956-01-11T00:00:00ZenliteralDepartment of Trade and Customs
3urihttp://www.wikidata.org/entity/Q3044789literalCA 5http://www.w3.org/2001/XMLSchema#dateTimeliteral1901-01-01T00:00:00ZNaNNaNNaNenliteralAttorney-General's Department
4urihttp://www.wikidata.org/entity/Q7234360literalCA 9http://www.w3.org/2001/XMLSchema#dateTimeliteral1901-01-01T00:00:00Zhttp://www.w3.org/2001/XMLSchema#dateTimeliteral1975-12-22T00:00:00ZenliteralPostmaster-General's Department
\n", "
" ], "text/plain": [ " agency_type agency_value naa_id_type \\\n", "0 uri http://www.wikidata.org/entity/Q113757341 literal \n", "1 uri http://www.wikidata.org/entity/Q16959680 literal \n", "2 uri http://www.wikidata.org/entity/Q16959770 literal \n", "3 uri http://www.wikidata.org/entity/Q3044789 literal \n", "4 uri http://www.wikidata.org/entity/Q7234360 literal \n", "\n", " naa_id_value start_date_datatype start_date_type \\\n", "0 CA 11 http://www.w3.org/2001/XMLSchema#dateTime literal \n", "1 CA 6 http://www.w3.org/2001/XMLSchema#dateTime literal \n", "2 CA 10 http://www.w3.org/2001/XMLSchema#dateTime literal \n", "3 CA 5 http://www.w3.org/2001/XMLSchema#dateTime literal \n", "4 CA 9 http://www.w3.org/2001/XMLSchema#dateTime literal \n", "\n", " start_date_value end_date_datatype \\\n", "0 1901-01-01T00:00:00Z http://www.w3.org/2001/XMLSchema#dateTime \n", "1 1901-01-01T00:00:00Z http://www.w3.org/2001/XMLSchema#dateTime \n", "2 1901-01-01T00:00:00Z http://www.w3.org/2001/XMLSchema#dateTime \n", "3 1901-01-01T00:00:00Z NaN \n", "4 1901-01-01T00:00:00Z http://www.w3.org/2001/XMLSchema#dateTime \n", "\n", " end_date_type end_date_value agencyLabel_xml:lang agencyLabel_type \\\n", "0 literal 1976-12-07T00:00:00Z en literal \n", "1 literal 1921-12-21T00:00:00Z en literal \n", "2 literal 1956-01-11T00:00:00Z en literal \n", "3 NaN NaN en literal \n", "4 literal 1975-12-22T00:00:00Z en literal \n", "\n", " agencyLabel_value \n", "0 Department of the Treasury \n", "1 Department of Defence \n", "2 Department of Trade and Customs \n", "3 Attorney-General's Department \n", "4 Postmaster-General's Department " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "code", "execution_count": 13, "id": "aa2d16b2-f637-4d6e-ac2b-ccfc6089fa94", "metadata": {}, "outputs": [], "source": [ "# Convert date fields to date types\n", "df[\"start_date_value\"] = pd.to_datetime(df[\"start_date_value\"])\n", "df[\"end_date_value\"] = pd.to_datetime(df[\"end_date_value\"])\n", "# If there's no end date, set it to now\n", "df[[\"end_date_value\"]] = df[[\"end_date_value\"]].fillna(pd.to_datetime(\"now\", utc=True))" ] }, { "cell_type": "code", "execution_count": 14, "id": "57b07bcd-02f3-4bf0-992f-11f93f6ba95d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", "" ], "text/plain": [ "alt.Chart(...)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Create the chart\n", "# The two X values give the start and end of the bar\n", "alt.Chart(df).mark_bar().encode(\n", " x=\"start_date_value:T\",\n", " x2=\"end_date_value:T\",\n", " y=alt.Y(\"naa_id_value:N\", sort=\"x\"),\n", " color=alt.Color(\"naa_id_value:N\", sort=\"-x\"),\n", " tooltip=[\"naa_id_value:N\", \"agencyLabel_value:N\"],\n", ").properties(width=1000)" ] }, { "cell_type": "markdown", "id": "58ece8e2-5f50-4add-9204-a3bd43296d3e", "metadata": {}, "source": [ "----\n", "\n", "Created by [Tim Sherratt](https://timsherratt.org) for the [GLAM Workbench](https://glam-workbench.net/).\n", "\n", "The development of the Wikidata section of the GLAM Workbench was supported by [Wikimedia Australia](https://wikimedia.org.au/wiki/Wikimedia_Australia)." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.8.12 64-bit ('3.8.12')", "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.8.12" }, "vscode": { "interpreter": { "hash": "f54aba2de7a75230217f549a064c6555500d2132634fbcab9606dbfda34a2a1b" } }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 5 }