zurich_fountains_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Automatic import of Zurich fountain data to Wikidata\n",
    "The following script downloads fountain data from Open Data Zurich, compares it to existing fountains in Wikidata for the same region, and creates Wikidata Quickstatement commands to complete the entries in Wikidata. New entities are created if no matching fountains are found."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initialize environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "191129_220708\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime as dt\n",
    "dtFmt = \"%y%m%d_%H%M%S\"\n",
    "print (dt.now().strftime(dtFmt))\n",
    "import pandas as pd\n",
    "import io\n",
    "import numpy as np\n",
    "from urllib.request import urlopen\n",
    "import json\n",
    "import codecs\n",
    "from math import *\n",
    "from gastrodon import RemoteEndpoint,QName,ttl,URIRef,inline\n",
    "from matplotlib import pyplot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#@prefix wikibase: <wikibase: <http://wikiba.se/ontology#> .\n",
    "prefixes=inline(\"\"\"\n",
    "   @prefix wd: <http://www.wikidata.org/entity/> .\n",
    "   @prefix wdt: <http://www.wikidata.org/prop/direct/> .\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",
    "\"\"\").graph\n",
    "endpoint=RemoteEndpoint(\n",
    "   \"http://query.wikidata.org/sparql\"\n",
    "   ,prefixes=prefixes\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data_url = 'https://data.stadt-zuerich.ch/dataset/brunnen/resource/d741cf9c-63be-495f-8c3e-9418168fcdbf/download/brunnen.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "ename": "HTTPError",
     "evalue": "HTTP Error 404: Not Found",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mHTTPError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-25-b53cec1e1317>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mctxMgr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0murlopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata_url\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mc:\\users\\hauser\\appdata\\local\\programs\\python\\python36\\lib\\urllib\\request.py\u001b[0m in \u001b[0;36murlopen\u001b[1;34m(url, data, timeout, cafile, capath, cadefault, context)\u001b[0m\n\u001b[0;32m    221\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    222\u001b[0m         \u001b[0mopener\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_opener\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 223\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mopener\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0murl\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    224\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    225\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0minstall_opener\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mopener\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\hauser\\appdata\\local\\programs\\python\\python36\\lib\\urllib\\request.py\u001b[0m in \u001b[0;36mopen\u001b[1;34m(self, fullurl, data, timeout)\u001b[0m\n\u001b[0;32m    530\u001b[0m         \u001b[1;32mfor\u001b[0m \u001b[0mprocessor\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mprocess_response\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mprotocol\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    531\u001b[0m             \u001b[0mmeth\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mprocessor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmeth_name\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 532\u001b[1;33m             \u001b[0mresponse\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmeth\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mreq\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mresponse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    533\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    534\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\hauser\\appdata\\local\\programs\\python\\python36\\lib\\urllib\\request.py\u001b[0m in \u001b[0;36mhttp_response\u001b[1;34m(self, request, response)\u001b[0m\n\u001b[0;32m    640\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;33m(\u001b[0m\u001b[1;36m200\u001b[0m \u001b[1;33m<=\u001b[0m \u001b[0mcode\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m300\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    641\u001b[0m             response = self.parent.error(\n\u001b[1;32m--> 642\u001b[1;33m                 'http', request, response, code, msg, hdrs)\n\u001b[0m\u001b[0;32m    643\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    644\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\hauser\\appdata\\local\\programs\\python\\python36\\lib\\urllib\\request.py\u001b[0m in \u001b[0;36merror\u001b[1;34m(self, proto, *args)\u001b[0m\n\u001b[0;32m    568\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mhttp_err\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    569\u001b[0m             \u001b[0margs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mdict\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'default'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'http_error_default'\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0morig_args\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 570\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_call_chain\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    571\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    572\u001b[0m \u001b[1;31m# XXX probably also want an abstract factory that knows when it makes\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\hauser\\appdata\\local\\programs\\python\\python36\\lib\\urllib\\request.py\u001b[0m in \u001b[0;36m_call_chain\u001b[1;34m(self, chain, kind, meth_name, *args)\u001b[0m\n\u001b[0;32m    502\u001b[0m         \u001b[1;32mfor\u001b[0m \u001b[0mhandler\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mhandlers\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    503\u001b[0m             \u001b[0mfunc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhandler\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmeth_name\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 504\u001b[1;33m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    505\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    506\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\users\\hauser\\appdata\\local\\programs\\python\\python36\\lib\\urllib\\request.py\u001b[0m in \u001b[0;36mhttp_error_default\u001b[1;34m(self, req, fp, code, msg, hdrs)\u001b[0m\n\u001b[0;32m    648\u001b[0m \u001b[1;32mclass\u001b[0m \u001b[0mHTTPDefaultErrorHandler\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mBaseHandler\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    649\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mhttp_error_default\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mreq\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfp\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcode\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmsg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mhdrs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 650\u001b[1;33m         \u001b[1;32mraise\u001b[0m \u001b[0mHTTPError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mreq\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfull_url\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcode\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmsg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mhdrs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfp\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    651\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    652\u001b[0m \u001b[1;32mclass\u001b[0m \u001b[0mHTTPRedirectHandler\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mBaseHandler\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mHTTPError\u001b[0m: HTTP Error 404: Not Found"
     ]
    }
   ],
   "source": [
    "ctxMgr = urlopen(data_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'ctxMgr' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-27-157f70e88583>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mrd\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mctxMgr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'ctxMgr' is not defined"
     ]
    }
   ],
   "source": [
    "rd = ctxMgr.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'rd' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-26-291f7c0de546>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdata_js\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mjson\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloads\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrd\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'rd' is not defined"
     ]
    }
   ],
   "source": [
    "data_js = json.loads(rd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "with codecs.open('20191128wvz_brunnen.json', 'r', encoding='utf8') as f:\n",
    "    fs = f.read()\n",
    "    data_js = json.loads(fs)\n",
    "    \n",
    "#data_js"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename columns to make them easier to work with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "features = data_js['features'];"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to dataframe\n",
    "df = pd.io.json.json_normalize(features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>geometry.type</th>\n",
       "      <th>geometry.coordinates</th>\n",
       "      <th>properties.objectid</th>\n",
       "      <th>properties.nummer</th>\n",
       "      <th>properties.art_txt</th>\n",
       "      <th>properties.brunnenart_txt</th>\n",
       "      <th>properties.historisches_baujahr</th>\n",
       "      <th>properties.wasserart_txt</th>\n",
       "      <th>properties.bezeichnung</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Point</td>\n",
       "      <td>[8.5992552918, 47.3697523961]</td>\n",
       "      <td>1</td>\n",
       "      <td>510</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>Aussichtsturm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Point</td>\n",
       "      <td>[8.5908112951, 47.3692926222]</td>\n",
       "      <td>2</td>\n",
       "      <td>349</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Point</td>\n",
       "      <td>[8.5757539215, 47.3661299392]</td>\n",
       "      <td>3</td>\n",
       "      <td>365</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>Biberlinterrasse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Point</td>\n",
       "      <td>[8.5648453902, 47.3709930413]</td>\n",
       "      <td>4</td>\n",
       "      <td>338</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Feature</td>\n",
       "      <td>Point</td>\n",
       "      <td>[8.5643903248, 47.3693267015]</td>\n",
       "      <td>5</td>\n",
       "      <td>6069</td>\n",
       "      <td>Normbrunnen_Notwasserbrunnen</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      type geometry.type           geometry.coordinates  properties.objectid  \\\n",
       "0  Feature         Point  [8.5992552918, 47.3697523961]                    1   \n",
       "1  Feature         Point  [8.5908112951, 47.3692926222]                    2   \n",
       "2  Feature         Point  [8.5757539215, 47.3661299392]                    3   \n",
       "3  Feature         Point  [8.5648453902, 47.3709930413]                    4   \n",
       "4  Feature         Point  [8.5643903248, 47.3693267015]                    5   \n",
       "\n",
       "  properties.nummer            properties.art_txt properties.brunnenart_txt  \\\n",
       "0               510    Brunnen_Trinkwasserbrunnen      öffentlicher Brunnen   \n",
       "1               349    Brunnen_Trinkwasserbrunnen      öffentlicher Brunnen   \n",
       "2               365    Brunnen_Trinkwasserbrunnen      öffentlicher Brunnen   \n",
       "3               338    Brunnen_Trinkwasserbrunnen      öffentlicher Brunnen   \n",
       "4              6069  Normbrunnen_Notwasserbrunnen          Notwasserbrunnen   \n",
       "\n",
       "   properties.historisches_baujahr properties.wasserart_txt  \\\n",
       "0                           1970.0              Verteilnetz   \n",
       "1                           1933.0              Verteilnetz   \n",
       "2                           1965.0              Quellwasser   \n",
       "3                           1910.0              Quellwasser   \n",
       "4                           1988.0              Quellwasser   \n",
       "\n",
       "  properties.bezeichnung  \n",
       "0          Aussichtsturm  \n",
       "1                   None  \n",
       "2       Biberlinterrasse  \n",
       "3                   None  \n",
       "4                   None  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract coords\n",
    "df['X'] = df['geometry.coordinates'].apply(lambda crds:crds[0])\n",
    "df['Y'] = df['geometry.coordinates'].apply(lambda crds:crds[1])\n",
    "# remove not needed columns\n",
    "df = df.drop(columns=['geometry.coordinates', 'geometry.type', 'properties.objectid', 'type'])\n",
    "# rename columns\n",
    "df = df.rename(index=str, columns=\n",
    "               {\"properties.bezeichnung\": \"label_de\", \n",
    "                \"properties.brunnenart_txt\": \"fountain_type\", \n",
    "                \"properties.wasserart_txt\": \"water_type\", \n",
    "                \"properties.nummer\":\"operator_id\",\n",
    "                \"properties.historisches_baujahr\": \"date\"\n",
    "               })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Verteilnetz', 'Quellwasser', 'eigene Versorgung', 'Grundwasser'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.water_type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>operator_id</th>\n",
       "      <th>properties.art_txt</th>\n",
       "      <th>fountain_type</th>\n",
       "      <th>date</th>\n",
       "      <th>water_type</th>\n",
       "      <th>label_de</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>510</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>Aussichtsturm</td>\n",
       "      <td>8.599255</td>\n",
       "      <td>47.369752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>349</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>None</td>\n",
       "      <td>8.590811</td>\n",
       "      <td>47.369293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>365</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>Biberlinterrasse</td>\n",
       "      <td>8.575754</td>\n",
       "      <td>47.366130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>338</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.564845</td>\n",
       "      <td>47.370993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6069</td>\n",
       "      <td>Normbrunnen_Notwasserbrunnen</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.564390</td>\n",
       "      <td>47.369327</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  operator_id            properties.art_txt         fountain_type    date  \\\n",
       "0         510    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1970.0   \n",
       "1         349    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1933.0   \n",
       "2         365    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1965.0   \n",
       "3         338    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1910.0   \n",
       "4        6069  Normbrunnen_Notwasserbrunnen      Notwasserbrunnen  1988.0   \n",
       "\n",
       "    water_type          label_de         X          Y  \n",
       "0  Verteilnetz     Aussichtsturm  8.599255  47.369752  \n",
       "1  Verteilnetz              None  8.590811  47.369293  \n",
       "2  Quellwasser  Biberlinterrasse  8.575754  47.366130  \n",
       "3  Quellwasser              None  8.564845  47.370993  \n",
       "4  Quellwasser              None  8.564390  47.369327  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify already existing fountains\n",
    "### Query fountains from Wikidata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the geographic extent of the data\n",
    "\n",
    "buffer = 0.0003  # in degrees, corresponds to about 20-30 meters)\n",
    "bounds = {\n",
    "    'minX': df['X'].min() - buffer,\n",
    "    'minY': df['Y'].min() - buffer,\n",
    "    'maxX': df['X'].max() + buffer,\n",
    "    'maxY': df['Y'].max() + buffer\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query fountains (both water wells and fountains) from Wikidata within bounding box found above\n",
    "\n",
    "query_string = \"\"\" SELECT ?place ?placeLabel ?location ?date ?catalog_code ?catalogLabel ?operator ?water_supply_type\n",
    "WHERE\n",
    "{{\n",
    "  # Enter coordinates\n",
    "  SERVICE wikibase:box {{\n",
    "    ?place wdt:P625 ?location .\n",
    "    bd:serviceParam wikibase:cornerWest \"Point({minX} {minY})\"^^geo:wktLiteral.\n",
    "    bd:serviceParam wikibase:cornerEast \"Point({maxX} {maxY})\"^^geo:wktLiteral.\n",
    "  }} .\n",
    "  # Is a water well or fountain or subclass of fountain\n",
    "  FILTER (EXISTS {{ ?place wdt:P31/wdt:P279* wd:Q43483 }} || EXISTS {{ ?place wdt:P31/wdt:P279* wd:Q483453 }}).\n",
    "  SERVICE wikibase:label {{\n",
    "    bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],de\" .\n",
    "  }} \n",
    "  OPTIONAL {{ ?place p:P528 ?catalog_code.\n",
    "            ?catalog_code pq:P972 ?catalog.}}\n",
    "  OPTIONAL {{ ?place wdt:P571 ?date.}}\n",
    "  OPTIONAL {{ ?place wdt:P5623 ?water_supply_type}}\n",
    "  OPTIONAL {{ ?place wdt:P137 ?operator.}}\n",
    "}}\n",
    "  \"\"\".format(**bounds)\n",
    "\n",
    "# Perform query\n",
    "query_result = endpoint.select(query_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " SELECT ?place ?placeLabel ?location ?date ?catalog_code ?catalogLabel ?operator ?water_supply_type\n",
      "WHERE\n",
      "{\n",
      "  # Enter coordinates\n",
      "  SERVICE wikibase:box {\n",
      "    ?place wdt:P625 ?location .\n",
      "    bd:serviceParam wikibase:cornerWest \"Point(8.4596025998 47.3229261256)\"^^geo:wktLiteral.\n",
      "    bd:serviceParam wikibase:cornerEast \"Point(8.619402727499999 47.4311197123)\"^^geo:wktLiteral.\n",
      "  } .\n",
      "  # Is a water well or fountain or subclass of fountain\n",
      "  FILTER (EXISTS { ?place wdt:P31/wdt:P279* wd:Q43483 } || EXISTS { ?place wdt:P31/wdt:P279* wd:Q483453 }).\n",
      "  SERVICE wikibase:label {\n",
      "    bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],de\" .\n",
      "  } \n",
      "  OPTIONAL { ?place p:P528 ?catalog_code.\n",
      "            ?catalog_code pq:P972 ?catalog.}\n",
      "  OPTIONAL { ?place wdt:P571 ?date.}\n",
      "  OPTIONAL { ?place wdt:P5623 ?water_supply_type}\n",
      "  OPTIONAL { ?place wdt:P137 ?operator.}\n",
      "}\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "print(query_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tidy up data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract coordinates from Wikidata results\n",
    "\n",
    "query_result['X'] = query_result['location'].apply(lambda l:float(l.split('(')[1].split(' ')[0]))\n",
    "query_result['Y'] = query_result['location'].apply(lambda l:float(l.split(' ')[1].split(')')[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# remove duplicate entries\n",
    "\n",
    "# duplicate entries are caused when e.g. a fountain has catalog codes from two catalogs\n",
    "query_result = query_result.drop_duplicates('place')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compute distances between fountains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# helper function to compute distances on the globe, returns distances in meters\n",
    "def spherical_dist(pos1, pos2, r=6371000):\n",
    "    pos1 = pos1 * np.pi / 180\n",
    "    pos2 = pos2 * np.pi / 180\n",
    "    cos_lat1 = np.cos(pos1[..., 0])\n",
    "    cos_lat2 = np.cos(pos2[..., 0])\n",
    "    cos_lat_d = np.cos(pos1[..., 0] - pos2[..., 0])\n",
    "    cos_lon_d = np.cos(pos1[..., 1] - pos2[..., 1])\n",
    "    return r * np.arccos(cos_lat_d - cos_lat1 * cos_lat2 * (1 - cos_lon_d))\n",
    "\n",
    "\n",
    "# compute distances from each ODZ fountain to each Wikidata fountain\n",
    "distances = spherical_dist(df[['X','Y']].values[:, None], query_result[['X','Y']].values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify nearest and second nearest matches for each ODZ fountain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>operator_id</th>\n",
       "      <th>properties.art_txt</th>\n",
       "      <th>fountain_type</th>\n",
       "      <th>date</th>\n",
       "      <th>water_type</th>\n",
       "      <th>label_de</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>nearest_qid</th>\n",
       "      <th>nearest_distance</th>\n",
       "      <th>2nd_nearest_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>510</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>Aussichtsturm</td>\n",
       "      <td>8.599255</td>\n",
       "      <td>47.369752</td>\n",
       "      <td>Q55165903</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.913012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>349</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>None</td>\n",
       "      <td>8.590811</td>\n",
       "      <td>47.369293</td>\n",
       "      <td>Q55165905</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>541.765145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>365</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>Biberlinterrasse</td>\n",
       "      <td>8.575754</td>\n",
       "      <td>47.366130</td>\n",
       "      <td>Q55165919</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>360.590844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>338</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.564845</td>\n",
       "      <td>47.370993</td>\n",
       "      <td>Q27230192</td>\n",
       "      <td>1.980033</td>\n",
       "      <td>190.081520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6069</td>\n",
       "      <td>Normbrunnen_Notwasserbrunnen</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.564390</td>\n",
       "      <td>47.369327</td>\n",
       "      <td>Q55165936</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>188.767471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>198</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>beim Kiosk (Anlagage Sihlhölzli)</td>\n",
       "      <td>8.523652</td>\n",
       "      <td>47.367071</td>\n",
       "      <td>Q55166180</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>130.772779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>199.2</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>None</td>\n",
       "      <td>8.526157</td>\n",
       "      <td>47.367916</td>\n",
       "      <td>Q55166182</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>130.015303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>199.3</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>None</td>\n",
       "      <td>8.525117</td>\n",
       "      <td>47.367237</td>\n",
       "      <td>Q55166184</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>125.329084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>125</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1909.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>Mosaikbrunnen</td>\n",
       "      <td>8.537833</td>\n",
       "      <td>47.364619</td>\n",
       "      <td>Q27229889</td>\n",
       "      <td>0.545362</td>\n",
       "      <td>120.155050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>6020</td>\n",
       "      <td>Normbrunnen_Notwasserbrunnen</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.537253</td>\n",
       "      <td>47.367253</td>\n",
       "      <td>Q55166188</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>130.579525</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   operator_id            properties.art_txt         fountain_type    date  \\\n",
       "0          510    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1970.0   \n",
       "1          349    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1933.0   \n",
       "2          365    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1965.0   \n",
       "3          338    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1910.0   \n",
       "4         6069  Normbrunnen_Notwasserbrunnen      Notwasserbrunnen  1988.0   \n",
       "..         ...                           ...                   ...     ...   \n",
       "95         198    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1931.0   \n",
       "96       199.2    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1931.0   \n",
       "97       199.3    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1931.0   \n",
       "98         125    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1909.0   \n",
       "99        6020  Normbrunnen_Notwasserbrunnen      Notwasserbrunnen  1978.0   \n",
       "\n",
       "     water_type                          label_de         X          Y  \\\n",
       "0   Verteilnetz                     Aussichtsturm  8.599255  47.369752   \n",
       "1   Verteilnetz                              None  8.590811  47.369293   \n",
       "2   Quellwasser                  Biberlinterrasse  8.575754  47.366130   \n",
       "3   Quellwasser                              None  8.564845  47.370993   \n",
       "4   Quellwasser                              None  8.564390  47.369327   \n",
       "..          ...                               ...       ...        ...   \n",
       "95  Verteilnetz  beim Kiosk (Anlagage Sihlhölzli)  8.523652  47.367071   \n",
       "96  Verteilnetz                              None  8.526157  47.367916   \n",
       "97  Verteilnetz                              None  8.525117  47.367237   \n",
       "98  Verteilnetz                     Mosaikbrunnen  8.537833  47.364619   \n",
       "99  Quellwasser                              None  8.537253  47.367253   \n",
       "\n",
       "   nearest_qid  nearest_distance  2nd_nearest_distance  \n",
       "0    Q55165903          0.000000            342.913012  \n",
       "1    Q55165905          0.000000            541.765145  \n",
       "2    Q55165919          0.000000            360.590844  \n",
       "3    Q27230192          1.980033            190.081520  \n",
       "4    Q55165936          0.000000            188.767471  \n",
       "..         ...               ...                   ...  \n",
       "95   Q55166180          0.000000            130.772779  \n",
       "96   Q55166182          0.000000            130.015303  \n",
       "97   Q55166184          0.000000            125.329084  \n",
       "98   Q27229889          0.545362            120.155050  \n",
       "99   Q55166188          0.000000            130.579525  \n",
       "\n",
       "[100 rows x 11 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# indexes of nearest fountains\n",
    "nearest_idx = np.argmin(distances, axis=1).tolist()\n",
    "\n",
    "# QID of nearest fountains\n",
    "df['nearest_qid'] = query_result.iloc[nearest_idx]['place'].apply(lambda id:id[3:]).tolist()\n",
    "\n",
    "# distance to nearest fountain\n",
    "df['nearest_distance'] = np.min(distances, axis=1).tolist()\n",
    "\n",
    "\n",
    "# then remove nearest\n",
    "i_line=0\n",
    "for i_col in nearest_idx:\n",
    "    distances[i_line, i_col] = 100000\n",
    "    i_line += 1\n",
    "# find distance to second nearest\n",
    "df['2nd_nearest_distance'] = np.min(distances, axis=1).tolist()\n",
    "\n",
    "df.head(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find out what information already exists for the nearest fountains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "# does nearest have label in german?\n",
    "df['nearest_has_label_de'] = (query_result.iloc[nearest_idx]['place'].apply(lambda p:p[3:]) != query_result.iloc[nearest_idx]['placeLabel']).tolist()\n",
    "\n",
    "# does nearest have date?\n",
    "df['nearest_has_date'] = query_result.iloc[nearest_idx]['date'].apply(lambda d:d is not None).tolist()\n",
    "\n",
    "# does nearest have operator?\n",
    "df['nearest_has_operator'] = query_result.iloc[nearest_idx]['operator'].apply(lambda id:id is not None).tolist()\n",
    "\n",
    "# does nearest have catalog code?\n",
    "df['nearest_has_code'] = query_result.iloc[nearest_idx]['catalog_code'].apply(lambda id:id is not None).tolist()\n",
    "\n",
    "# does nearest have water type?\n",
    "df['nearest_has_water_type'] = query_result.iloc[nearest_idx]['water_supply_type'].apply(lambda id:id is not None).tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Decide on whether nearest fountain should be considered a match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The nearest fountain is a match if: \n",
    "# - no further than x m away\n",
    "# - 2nd nearest fountain at nearest least ratio_min further away than the nearest fountain\n",
    "def validate_proposal(qid, d1, d2, dmax=10, ratio_min=0.5):\n",
    "    \n",
    "    if d1 == 0 or (d1<=dmax and d2/d1-1 >= ratio_min):\n",
    "        return 'match'\n",
    "    elif d1<=dmax and d2/d1-1 < ratio_min:\n",
    "        return 'unclear'\n",
    "    else:\n",
    "        return 'no match'\n",
    "    \n",
    "for index, row in df.iterrows():\n",
    "    df.loc[index, 'match_found'] = validate_proposal(\n",
    "        row['nearest_qid'], \n",
    "        row['nearest_distance'], \n",
    "        row['2nd_nearest_distance'],\n",
    "        dmax=15\n",
    "    )\n",
    "dffinal = df.drop(columns=['nearest_distance', '2nd_nearest_distance'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>operator_id</th>\n",
       "      <th>properties.art_txt</th>\n",
       "      <th>fountain_type</th>\n",
       "      <th>date</th>\n",
       "      <th>water_type</th>\n",
       "      <th>label_de</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>nearest_qid</th>\n",
       "      <th>nearest_has_label_de</th>\n",
       "      <th>nearest_has_date</th>\n",
       "      <th>nearest_has_operator</th>\n",
       "      <th>nearest_has_code</th>\n",
       "      <th>nearest_has_water_type</th>\n",
       "      <th>match_found</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>510</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>Aussichtsturm</td>\n",
       "      <td>8.599255</td>\n",
       "      <td>47.369752</td>\n",
       "      <td>Q55165903</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>349</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>None</td>\n",
       "      <td>8.590811</td>\n",
       "      <td>47.369293</td>\n",
       "      <td>Q55165905</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>365</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>Biberlinterrasse</td>\n",
       "      <td>8.575754</td>\n",
       "      <td>47.366130</td>\n",
       "      <td>Q55165919</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>338</td>\n",
       "      <td>Brunnen_Trinkwasserbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.564845</td>\n",
       "      <td>47.370993</td>\n",
       "      <td>Q27230192</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6069</td>\n",
       "      <td>Normbrunnen_Notwasserbrunnen</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>None</td>\n",
       "      <td>8.564390</td>\n",
       "      <td>47.369327</td>\n",
       "      <td>Q55165936</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  operator_id            properties.art_txt         fountain_type    date  \\\n",
       "0         510    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1970.0   \n",
       "1         349    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1933.0   \n",
       "2         365    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1965.0   \n",
       "3         338    Brunnen_Trinkwasserbrunnen  öffentlicher Brunnen  1910.0   \n",
       "4        6069  Normbrunnen_Notwasserbrunnen      Notwasserbrunnen  1988.0   \n",
       "\n",
       "    water_type          label_de         X          Y nearest_qid  \\\n",
       "0  Verteilnetz     Aussichtsturm  8.599255  47.369752   Q55165903   \n",
       "1  Verteilnetz              None  8.590811  47.369293   Q55165905   \n",
       "2  Quellwasser  Biberlinterrasse  8.575754  47.366130   Q55165919   \n",
       "3  Quellwasser              None  8.564845  47.370993   Q27230192   \n",
       "4  Quellwasser              None  8.564390  47.369327   Q55165936   \n",
       "\n",
       "   nearest_has_label_de  nearest_has_date  nearest_has_operator  \\\n",
       "0                  True              True                  True   \n",
       "1                  True              True                  True   \n",
       "2                  True              True                  True   \n",
       "3                  True              True                  True   \n",
       "4                 False              True                  True   \n",
       "\n",
       "   nearest_has_code  nearest_has_water_type match_found  \n",
       "0              True                    True       match  \n",
       "1              True                    True       match  \n",
       "2              True                    True       match  \n",
       "3              True                    True       match  \n",
       "4              True                    True       match  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dffinal.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Quickstatement commands from data\n",
    "### Helper functions to format content according to Quickstatements v1 syntax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_coordinates(x, y):\n",
    "    # format geographic coordinates\n",
    "    return '@{1:1.8f}/{0:1.8f}'.format(x,y)\n",
    "\n",
    "\n",
    "def process_year(date):\n",
    "    # format date\n",
    "    if np.isnan(date):\n",
    "        return ''\n",
    "    else:\n",
    "        return '+{0:4d}-00-00T00:00:00Z/9'.format(int(date))\n",
    "\n",
    "    \n",
    "fountain_type_map = {\n",
    "    'öffentlicher Brunnen': 'Q53628296',\n",
    "    'Notwasserbrunnen': 'Q53628522',\n",
    "    'privater Brunnen': 'Q53629707',\n",
    "    'Brunnen in städtischer Liegenschaft': 'Q53628618',\n",
    "    'Brunnen des Verschönerungsvereins': 'Q53628761',\n",
    "    'Brunnen mit eigener Versorgung': 'Q53630002'\n",
    "}\n",
    "\n",
    "water_type_map = {\n",
    "    'Verteilnetz': 'Q53633635',\n",
    "    'Quellwasser': 'Q1881858',\n",
    "    'eigene Versorgung': 'Q53634173',\n",
    "    'Grundwasser': 'Q161598'\n",
    "}\n",
    "\n",
    "def process_fountain_type(type):\n",
    "    # translate fountain types to wikidata values\n",
    "    return fountain_type_map[type]\n",
    "\n",
    "\n",
    "def process_water_type(type):\n",
    "    # translate water types to wikidata values\n",
    "    return water_type_map[type]\n",
    "\n",
    "\n",
    "def process_label_de(text):\n",
    "    # process German language labels\n",
    "    if text is None:\n",
    "        return ''\n",
    "    elif 'brunnen' in text.lower():\n",
    "        return '\"{}\"'.format(text)\n",
    "    else:\n",
    "        return '\"Brunnen ({})\"'.format(text)\n",
    "    \n",
    "\n",
    "def createline(lines, item, prop, value, extra, qualifiers=[]):\n",
    "    # general function to create Quickstatement v1 commands\n",
    "    if value != '' and value != '\"\"':\n",
    "        statement = '{}\\t{}\\t{}'.format(item, prop, value)\n",
    "        if len(qualifiers):\n",
    "            # append qualifiers if applicable\n",
    "            for q in qualifiers:\n",
    "                statement += '\\t{}\\t{}'.format(q['prop'], q['value'])\n",
    "        statement += extra\n",
    "        statement += '\\n'\n",
    "        lines.append(statement)\n",
    "    return lines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create statements, taking care not to overwrite existing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initialize command storage list\n",
    "lines = []\n",
    "statedId = \"\\tS248\\tQ53629101\"\n",
    "for index, row in dffinal.iterrows():\n",
    "    \n",
    "    # either create new or edit existing entity\n",
    "    if row['match_found'] == 'no match':\n",
    "        # create a new fountain\n",
    "        lines.append('CREATE\\n')\n",
    "        item = 'LAST'\n",
    "    elif row['match_found'] == 'unclear':\n",
    "        print('unclear match')\n",
    "        print(row)\n",
    "        continue\n",
    "    elif row['match_found'] == 'match':\n",
    "        # update existing fountain\n",
    "        item = row['nearest_qid']\n",
    "        \n",
    "        \n",
    "    # Add this basic information only if creating a new entity\n",
    "    if item == 'LAST':\n",
    "        # instance of drinking fountain\n",
    "        lines = createline(lines, item, 'P31', 'Q1630622', statedId)\n",
    "\n",
    "        # instance of specific water fountain type\n",
    "        lines = createline(lines, item, 'P31', process_fountain_type(row['fountain_type']), statedId)\n",
    "\n",
    "        # coordinates\n",
    "        lines = createline(lines, item, 'P625', process_coordinates(row['X'], row['Y']), statedId)\n",
    "    else:\n",
    "        lines = createline(lines, item, 'P31', process_fountain_type(row['fountain_type']), statedId)\n",
    "        #short URL as per https://github.com/water-fountains/proximap/issues/244\n",
    "        lines = createline(lines, item, 'P973', '\"https://h2o.do/'+item+'\"','')\n",
    "        \n",
    "        \n",
    "    # For other properties, add information if the entity is new or if property does not yet exist\n",
    "    \n",
    "    # label in german\n",
    "    if item == 'LAST' or not row['nearest_has_label_de']:\n",
    "        lines = createline(lines, item, 'Lde', process_label_de(row['label_de']),statedId)\n",
    "    \n",
    "    # creation date\n",
    "    if item == 'LAST' or not row['nearest_has_date']:\n",
    "        lines = createline(lines, item, 'P571', process_year(row['date']),statedId)\n",
    "\n",
    "    # operated by WVZ    \n",
    "    if item == 'LAST' or not row['nearest_has_operator']:\n",
    "        lines = createline(lines, item, 'P137', 'Q27229237',statedId)\n",
    "        \n",
    "    \n",
    "    # operated by WVZ    \n",
    "    if item == 'LAST' or not row['nearest_has_water_type']:\n",
    "        lines = createline(lines, item, 'P5623', process_water_type(row['water_type']),statedId)\n",
    "    \n",
    "    \n",
    "    # catalog number can always be added (it is hard to check for)\n",
    "    #lines = createline(lines, item, 'P528', '\"{}\"'.format(row['operator_id']),statedId, [{\n",
    "    #    'prop': 'P972',\n",
    "    #    'value': 'Q53629101'\n",
    "    #}])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write commands to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "wrote 'quickstatement_commands_Zuerich_fountain_191129_222107.txt' with 2572 lines\n"
     ]
    }
   ],
   "source": [
    "quickStatFileName = \"quickstatement_commands_Zuerich_fountain_\"+dt.now().strftime(dtFmt)+\".txt\"\n",
    "with io.open(quickStatFileName, \"w\", encoding='utf8') as f:\n",
    "    f.writelines(lines)\n",
    "print(\"wrote '\"+quickStatFileName+\"' with \"+str(len(lines))+\" lines\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import into Wikidata\n",
    "- Go to https://tools.wmflabs.org/wikidata-todo/quick_statements.php.\n",
    "- Authenticate yourself with your Wikidata account.\n",
    "- Copy and paste the contents of quickstatement_commands*.txt into the blank field, and run the commands"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}