hmt_psp_example.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "---\n",
    "title: \"Untitled\"\n",
    "format: html\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: SPARQLwrapper in /Users/waf/.pyenv/versions/3.10.3/lib/python3.10/site-packages (2.0.0)\n",
      "Requirement already satisfied: rdflib>=6.1.1 in /Users/waf/.pyenv/versions/3.10.3/lib/python3.10/site-packages (from SPARQLwrapper) (7.0.0)\n",
      "Requirement already satisfied: pyparsing<4,>=2.1.0 in /Users/waf/.pyenv/versions/3.10.3/lib/python3.10/site-packages (from rdflib>=6.1.1->SPARQLwrapper) (2.4.7)\n",
      "Requirement already satisfied: isodate<0.7.0,>=0.6.0 in /Users/waf/.pyenv/versions/3.10.3/lib/python3.10/site-packages (from rdflib>=6.1.1->SPARQLwrapper) (0.6.1)\n",
      "Requirement already satisfied: six in /Users/waf/.pyenv/versions/3.10.3/lib/python3.10/site-packages (from isodate<0.7.0,>=0.6.0->rdflib>=6.1.1->SPARQLwrapper) (1.16.0)\n",
      "\u001b[33mWARNING: You are using pip version 22.0.4; however, version 23.2.1 is available.\n",
      "You should consider upgrading via the '/Users/waf/.pyenv/versions/3.10.3/bin/python -m pip install --upgrade pip' command.\u001b[0m\u001b[33m\n",
      "\u001b[0mNote: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install SPARQLwrapper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, JSON "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "sparql = SPARQLWrapper(\n",
    "    \"http://staging.gss-data.org.uk/sparql\"\n",
    ")\n",
    "sparql.setCredentials(\"guest\", \"gss-staging\")\n",
    "sparql.setHTTPAuth(\"BASIC\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "sparql.setReturnFormat(\"csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "sparql_query = \"\"\"\n",
    "PREFIX dcat: <http://www.w3.org/ns/dcat#>\n",
    "PREFIX dcterms: <http://purl.org/dc/terms/>\n",
    "PREFIX owl: <http://www.w3.org/2002/07/owl#>\n",
    "PREFIX qb: <http://purl.org/linked-data/cube#>\n",
    "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX skos: <http://www.w3.org/2004/02/skos/core#>\n",
    "PREFIX void: <http://rdfs.org/ns/void#>\n",
    "PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>\n",
    "PREFIX measure: <https://raw.githubusercontent.com/canwaf/provider-ons/main/out/public-service-productivity/draft-public-service-productivity-estimates-total-public-service.csv#measure/>\n",
    "PREFIX dimension: <https://raw.githubusercontent.com/canwaf/provider-ons/main/out/public-service-productivity/draft-public-service-productivity-estimates-total-public-service.csv#dimension/>\n",
    "\n",
    "SELECT ?year ?serviceArea ?QAPI ?QAOI ?III \n",
    "WHERE {\n",
    "?obs1 qb:dataSet <https://raw.githubusercontent.com/canwaf/provider-ons/main/out/public-service-productivity/draft-public-service-productivity-estimates-total-public-service.csv#dataset> ;\n",
    "\tdimension:year [rdfs:label ?year] ;\n",
    "\tdimension:service-area [rdfs:label ?serviceArea] ;\n",
    "\tmeasure:quality-adjusted-productivity-index-1997-base ?QAPI .\n",
    "\n",
    "?obs2 qb:dataSet <https://raw.githubusercontent.com/canwaf/provider-ons/main/out/public-service-productivity/draft-public-service-productivity-estimates-total-public-service.csv#dataset> ;\n",
    "\tdimension:year [rdfs:label ?year]  ;\n",
    "\tdimension:service-area [rdfs:label ?serviceArea] ;\n",
    "\tmeasure:input-indices-index-1997-base ?III .\n",
    "\n",
    "?obs3 qb:dataSet <https://raw.githubusercontent.com/canwaf/provider-ons/main/out/public-service-productivity/draft-public-service-productivity-estimates-total-public-service.csv#dataset> ;\n",
    "\tdimension:year [rdfs:label ?year]  ;\n",
    "\tdimension:service-area [rdfs:label ?serviceArea] ;\n",
    "\tmeasure:quality-adjusted-output-index-1997-base ?QAOI .\n",
    "\n",
    "FILTER(?year = \"2005\")\n",
    "}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = sparql.setQuery(sparql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = sparql.queryAndConvert()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b\"year,serviceArea,QAPI,QAOI,III\\r\\n2005,Public Order & Safety,78.7,114.6,145.6\\r\\n2005,Healthcare,105.6,155.0,146.7\\r\\n2005,Defence,100.0,124.7,124.7\\r\\n2005,Adult Social Care,97.9,134.7,137.5\\r\\n2005,Children's Social Care,81.0,121.6,150.0\\r\\n2005,Police,100.0,116.5,116.5\\r\\n2005,Education,98.1,116.7,118.9\\r\\n2005,Total,99.0,135.7,137.1\\r\\n2005,Other,100.0,152.9,152.9\\r\\n2005,Social Security Administration,71.9,92.6,128.7\\r\\n\""
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "year,serviceArea,QAPI,QAOI,III\n",
      "2005,Public Order & Safety,78.7,114.6,145.6\n",
      "2005,Healthcare,105.6,155.0,146.7\n",
      "2005,Defence,100.0,124.7,124.7\n",
      "2005,Adult Social Care,97.9,134.7,137.5\n",
      "2005,Children's Social Care,81.0,121.6,150.0\n",
      "2005,Police,100.0,116.5,116.5\n",
      "2005,Education,98.1,116.7,118.9\n",
      "2005,Total,99.0,135.7,137.1\n",
      "2005,Other,100.0,152.9,152.9\n",
      "2005,Social Security Administration,71.9,92.6,128.7\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(result.decode(\"utf-8\"))"
   ]
  }
 ],
 "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.10.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}