In [8]:
#!pip install SPARQLWrapper

Requirement already satisfied: SPARQLWrapper in /srv/paws/lib/python3.6/site-packages
Requirement already satisfied: rdflib>=4.0 in /srv/paws/lib/python3.6/site-packages (from SPARQLWrapper)
Requirement already satisfied: pyparsing in /srv/paws/lib/python3.6/site-packages (from rdflib>=4.0->SPARQLWrapper)
Requirement already satisfied: isodate in /srv/paws/lib/python3.6/site-packages (from rdflib>=4.0->SPARQLWrapper)
Requirement already satisfied: six in /srv/paws/lib/python3.6/site-packages (from isodate->rdflib>=4.0->SPARQLWrapper)
In [20]:
import requests
import pandas as pd
import zipfile
In [21]:
URL = "https://www.web.statistik.zh.ch:8443/gp/GP?type=EXPORT&indikatoren=133&raumtyp=1&export=csv"

r = requests.get(url = URL)

os.mkdir('data')

open('data/dataKt.zip', 'wb').write(r.content)

zfile = zipfile.ZipFile('data/dataKt.zip')


##for finfo in zfile.infolist():
##    ifile = zfile.open(finfo)
##    line_list = ifile.readlines()
##    print(line_list)

with zipfile.ZipFile('data/dataKt.zip', 'r') as f:
    names = f.namelist()

os.rmdir(dir_name)    
    
df = pd.read_csv(zfile.open(names[0]), sep=';')
print(df)

---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
<ipython-input-21-6650de6e0fd2> in <module>
      3 r = requests.get(url = URL)
      4 
----> 5 open('data/dataKt.zip', 'wb').write(r.content)
      6 
      7 zfile = zipfile.ZipFile('data/dataKt.zip')

FileNotFoundError: [Errno 2] No such file or directory: 'data/dataKt.zip'
In [52]:
from SPARQLWrapper import SPARQLWrapper, JSON

endpoint_url = "https://ld.geo.admin.ch/query"

query = """
PREFIX schema: <http://schema.org/>
PREFIX gn: <http://www.geonames.org/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX dct: <http://purl.org/dc/terms/>
PREFIX st: <https://ld.geo.admin.ch/def/>

select ?Municipality ?Name ?Population ?bfs ?date ?wikidata_id
where{
?Municipality gn:featureCode gn:A.ADM3 .
?Municipality schema:name ?Name .
?Municipality gn:population?Population .
?Municipality st:bfsNumber ?bfs .
?Municipality dct:issued ?date .
?Municipality gn:parentADM1 ?InCanton .
?InCanton schema:name ?CantonName .
#?Municipality geo:hasGeometry ?Geometry .
#?Geometry geo:asWKT ?WKT .

#FILTER (?date = '2019-01-01'^^xsd:date)
FILTER (?CantonName = 'Zürich')

{SELECT DISTINCT (xsd:integer(?bfsWD) AS ?bfs)?wikidata_id ?Image WHERE {
SERVICE <https://query.wikidata.org/bigdata/namespace/wdq/sparql>

{
?wikidata_id wdt:P771 ?bfsWD .
?wikidata_id wdt:P31 wd:Q70208 .
#OPTIONAL { ?wikidata_id wdt:P18 ?Image. } .
}
}
}
}
"""

def get_results(endpoint_url, query):
    sparql = SPARQLWrapper(endpoint_url, agent = "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.75 Safari/537.36")
    sparql.setQuery(query)
    sparql.setReturnFormat(JSON)
    return sparql.query().convert()

result = results["results"]["bindings"]

#for result in result = results["results"]["bindings"]:


# select ?Municipality ?Name ?Population ?bfs ?date ?wikidata_id

population = []
for p in result:
    mon = {
    'Municipality':p['Municipality']['value'],
    'Name':p['Name']['value'],
    'Population':p['Population']['value'],
    'bfs':p['bfs']['value'], 
    'date':p['date']['value'], 
    'wikidata_id':p['wikidata_id']['value'],   
    'wikidata_id': p['wikidata_id']['value'].replace('http://www.wikidata.org/entity/', '')#,
     #'date': p['date']['value'],
    }
    population.append(mon)
    
    
pop = pd.DataFrame(population)


data["wikidata_id"]= data["wikidata_id"].replace(25.0, "Twenty five")
Out[52]:
<bound method NDFrame.head of                                           Municipality                 Name  \
0    https://ld.geo.admin.ch/boundaries/municipalit...            Bäretswil   
1    https://ld.geo.admin.ch/boundaries/municipalit...            Bäretswil   
2    https://ld.geo.admin.ch/boundaries/municipalit...            Ottenbach   
3    https://ld.geo.admin.ch/boundaries/municipalit...            Ottenbach   
4    https://ld.geo.admin.ch/boundaries/municipalit...        Wetzikon (ZH)   
5    https://ld.geo.admin.ch/boundaries/municipalit...        Wetzikon (ZH)   
6    https://ld.geo.admin.ch/boundaries/municipalit...             Adliswil   
7    https://ld.geo.admin.ch/boundaries/municipalit...             Adliswil   
8    https://ld.geo.admin.ch/boundaries/municipalit...              Thalwil   
9    https://ld.geo.admin.ch/boundaries/municipalit...              Thalwil   
10   https://ld.geo.admin.ch/boundaries/municipalit...            Wädenswil   
11   https://ld.geo.admin.ch/boundaries/municipalit...            Wädenswil   
12   https://ld.geo.admin.ch/boundaries/municipalit...       Uetikon am See   
13   https://ld.geo.admin.ch/boundaries/municipalit...       Uetikon am See   
14   https://ld.geo.admin.ch/boundaries/municipalit...             Zollikon   
15   https://ld.geo.admin.ch/boundaries/municipalit...             Zollikon   
16   https://ld.geo.admin.ch/boundaries/municipalit...           Fehraltorf   
17   https://ld.geo.admin.ch/boundaries/municipalit...           Fehraltorf   
18   https://ld.geo.admin.ch/boundaries/municipalit...              Hittnau   
19   https://ld.geo.admin.ch/boundaries/municipalit...              Hittnau   
20   https://ld.geo.admin.ch/boundaries/municipalit...            Dübendorf   
21   https://ld.geo.admin.ch/boundaries/municipalit...            Dübendorf   
22   https://ld.geo.admin.ch/boundaries/municipalit...           Greifensee   
23   https://ld.geo.admin.ch/boundaries/municipalit...           Greifensee   
24   https://ld.geo.admin.ch/boundaries/municipalit...  Wangen-Brüttisellen   
25   https://ld.geo.admin.ch/boundaries/municipalit...  Wangen-Brüttisellen   
26   https://ld.geo.admin.ch/boundaries/municipalit...              Altikon   
27   https://ld.geo.admin.ch/boundaries/municipalit...              Altikon   
28   https://ld.geo.admin.ch/boundaries/municipalit...              Dinhard   
29   https://ld.geo.admin.ch/boundaries/municipalit...              Dinhard   
..                                                 ...                  ...   
622  https://ld.geo.admin.ch/boundaries/municipalit...         Bachenbülach   
623  https://ld.geo.admin.ch/boundaries/municipalit...         Bachenbülach   
624  https://ld.geo.admin.ch/boundaries/municipalit...              Embrach   
625  https://ld.geo.admin.ch/boundaries/municipalit...              Embrach   
626  https://ld.geo.admin.ch/boundaries/municipalit...            Grüningen   
627  https://ld.geo.admin.ch/boundaries/municipalit...               Hinwil   
628  https://ld.geo.admin.ch/boundaries/municipalit...          Richterswil   
629  https://ld.geo.admin.ch/boundaries/municipalit...            Stallikon   
630  https://ld.geo.admin.ch/boundaries/municipalit...       Erlenbach (ZH)   
631  https://ld.geo.admin.ch/boundaries/municipalit...             Wildberg   
632  https://ld.geo.admin.ch/boundaries/municipalit...              Adlikon   
633  https://ld.geo.admin.ch/boundaries/municipalit...     Unterengstringen   
634  https://ld.geo.admin.ch/boundaries/municipalit...                 Dorf   
635  https://ld.geo.admin.ch/boundaries/municipalit...   Affoltern am Albis   
636  https://ld.geo.admin.ch/boundaries/municipalit...               Volken   
637  https://ld.geo.admin.ch/boundaries/municipalit...         Bachenbülach   
638  https://ld.geo.admin.ch/boundaries/municipalit...              Embrach   
639  https://ld.geo.admin.ch/boundaries/municipalit...            Grüningen   
640  https://ld.geo.admin.ch/boundaries/municipalit...               Hinwil   
641  https://ld.geo.admin.ch/boundaries/municipalit...          Richterswil   
642  https://ld.geo.admin.ch/boundaries/municipalit...            Stallikon   
643  https://ld.geo.admin.ch/boundaries/municipalit...       Erlenbach (ZH)   
644  https://ld.geo.admin.ch/boundaries/municipalit...             Wildberg   
645  https://ld.geo.admin.ch/boundaries/municipalit...              Adlikon   
646  https://ld.geo.admin.ch/boundaries/municipalit...     Unterengstringen   
647  https://ld.geo.admin.ch/boundaries/municipalit...                 Dorf   
648  https://ld.geo.admin.ch/boundaries/municipalit...   Affoltern am Albis   
649  https://ld.geo.admin.ch/boundaries/municipalit...               Volken   
650  https://ld.geo.admin.ch/boundaries/municipalit...         Bachenbülach   
651  https://ld.geo.admin.ch/boundaries/municipalit...              Embrach   

    Population  bfs        date wikidata_id  
0         4993  111  2016-01-01      Q65868  
1         4974  111  2017-01-01      Q65868  
2         2548   11  2016-01-01      Q67513  
3         2569   11  2017-01-01      Q67513  
4        23938  121  2016-01-01      Q68305  
5        24407  121  2017-01-01      Q68305  
6        18448  131  2016-01-01      Q68210  
7        18605  131  2017-01-01      Q68210  
8        17631  141  2016-01-01      Q68959  
9        17747  141  2017-01-01      Q68959  
10       21245  142  2016-01-01      Q69718  
11       21621  142  2017-01-01      Q69718  
12        5962  159  2016-01-01      Q67058  
13        5988  159  2017-01-01      Q67058  
14       12533  161  2016-01-01     Q218691  
15       12753  161  2017-01-01     Q218691  
16        6317  172  2016-01-01      Q70369  
17        6307  172  2017-01-01      Q70369  
18        3644  173  2016-01-01      Q65841  
19        3614  173  2017-01-01      Q65841  
20       26317  191  2016-01-01      Q64082  
21       26844  191  2017-01-01      Q64082  
22        5309  194  2016-01-01      Q64882  
23        5374  194  2017-01-01      Q64882  
24        7626  200  2016-01-01      Q66829  
25        7727  200  2017-01-01      Q66829  
26         667  211  2016-01-01      Q65929  
27         655  211  2017-01-01      Q65929  
28        1490  216  2016-01-01      Q65795  
29        1494  216  2017-01-01      Q65795  
..         ...  ...         ...         ...  
622       4079   51  2016-01-01      Q69887  
623       4034   51  2017-01-01      Q69887  
624       9114   56  2016-01-01      Q65877  
625       9139   56  2017-01-01      Q65877  
626       3382  116  2019-01-01      Q67120  
627      11199  117  2019-01-01      Q69383  
628      13454  138  2019-01-01      Q68227  
629       3717   13  2019-01-01      Q66062  
630       5576  151  2019-01-01      Q69203  
631       1000  182  2019-01-01      Q67346  
632        665   21  2019-01-01      Q68356  
633       3804  249  2019-01-01      Q66079  
634        687   26  2019-01-01      Q70283  
635      12146    2  2019-01-01      Q68290  
636        342   43  2019-01-01      Q67922  
637       4114   51  2019-01-01      Q69887  
638       9432   56  2019-01-01      Q65877  
639       3401  116  2018-01-01      Q67120  
640      11095  117  2018-01-01      Q69383  
641      13352  138  2018-01-01      Q68227  
642       3646   13  2018-01-01      Q66062  
643       5517  151  2018-01-01      Q69203  
644        985  182  2018-01-01      Q67346  
645        666   21  2018-01-01      Q68356  
646       3758  249  2018-01-01      Q66079  
647        629   26  2018-01-01      Q70283  
648      11900    2  2018-01-01      Q68290  
649        341   43  2018-01-01      Q67922  
650       4087   51  2018-01-01      Q69887  
651       9189   56  2018-01-01      Q65877  

[652 rows x 6 columns]>
In [ ]:
In [ ]: