import pandas as pd
import matplotlib.pyplot as plt
import datetime
import numpy as np
import pymysql
from sqlalchemy import create_engine
%matplotlib inline
import requests
data_zhwn = pd.read_json(path_or_buf="https://data.stadt-zuerich.ch/dataset/zueriwieneu_meldungen/resource/2fee5562-1842-4ccc-a390-c52c9dade90d/download/zueriwieneu_meldungen.json", lines=False,encoding='utf-8', orient="records")
data_zhwn.head()
features name type
0 {'type': 'Feature', 'geometry': {'type': 'Mult... ZueriWieNeu_Meldungen FeatureCollection
1 {'type': 'Feature', 'geometry': {'type': 'Mult... ZueriWieNeu_Meldungen FeatureCollection
2 {'type': 'Feature', 'geometry': {'type': 'Mult... ZueriWieNeu_Meldungen FeatureCollection
3 {'type': 'Feature', 'geometry': {'type': 'Mult... ZueriWieNeu_Meldungen FeatureCollection
4 {'type': 'Feature', 'geometry': {'type': 'Mult... ZueriWieNeu_Meldungen FeatureCollection
data_zhwn.type.unique()
array(['FeatureCollection'], dtype=object)
data_zhwn['features'][0]
{'geometry': {'coordinates': [[8.48422630132149, 47.3740377357398]],
  'type': 'MultiPoint'},
 'properties': {'agency_sent_datetime': '20130404072505',
  'description': 'Unebener Bürgersteig: Auf dem Asphalt des Bürgersteigs hat es eine Erhebung, die man wirklich nicht sieht und immer wieder drüber stolpert.',
  'detail': 'Auf dem Asphalt des Bürgersteigs hat es eine Erhebung, die man wirklich nicht sieht und immer wieder drüber stolpert.',
  'e': 2678968,
  'interface_used': 'Web interface',
  'media_url': '',
  'n': 1247548,
  'requested_datetime': '20130314151615',
  'service_code': 'Strasse/Trottoir/Platz',
  'service_name': 'Strasse/Trottoir/Platz',
  'service_notice': 'Diese Reparatur wird von uns in den kommenden Wochen / Monaten ausgeführt.  <br/>  <br/> Freundliche Grüsse <br/>  <br/> Ihre Stadt Zürich',
  'service_request_id': '1',
  'status': 'fixed - council',
  'title': 'Unebener Bürgersteig',
  'updated_datetime': None},
 'type': 'Feature'}
def processData(row):

    point = row['features']['geometry']
    coord = np.asarray(point['coordinates'])[0]
    coordx=  coord[0]
    coordy=  coord[1]
    
    
    desc = row['features']['properties']['description']
    detail = row['features']['properties']['detail']
    e = row['features']['properties']['e']
    interface = row['features']['properties']['interface_used']
    purl = row['features']['properties']['media_url']
    reqDatetime = row['features']['properties']['requested_datetime']
    code = row['features']['properties']['service_code']
    name = row['features']['properties']['service_name']
    notice = row['features']['properties']['service_notice']
    reqId = row['features']['properties']['service_request_id']
    status = row['features']['properties']['status']
    title = row['features']['properties']['title']
    updated_datetime = row['features']['properties']['updated_datetime']
    
    
    data_zhwn['reqId'] = reqId
    data_zhwn['desc'] = desc
    data_zhwn['e'] = e
    data_zhwn['interafce'] = interface
    data_zhwn['purl'] = purl
    data_zhwn['reqDatetime'] = reqDatetime
    data_zhwn['code'] = code
    data_zhwn['name'] = name
    data_zhwn['notice'] = notice
    data_zhwn['status'] = status
    data_zhwn['title'] = title
    data_zhwn['updated_datetime'] = updated_datetime
    
    
    

    
   
data = data_zhwn.apply(processData,axis=1)
---------------------------------------------------------------------------
KeyboardInterrupt                         Traceback (most recent call last)
<ipython-input-6-50dacdf22c5a> in <module>()
----> 1 data = data_zhwn.apply(processData,axis=1)

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in apply(self, func, axis, broadcast, raw, reduce, args, **kwds)
   4260                         f, axis,
   4261                         reduce=reduce,
-> 4262                         ignore_failures=ignore_failures)
   4263             else:
   4264                 return self._apply_broadcast(f, axis)

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in _apply_standard(self, func, axis, ignore_failures, reduce)
   4316                     labels = self._get_agg_axis(axis)
   4317                     result = lib.reduce(values, func, axis=axis, dummy=dummy,
-> 4318                                         labels=labels)
   4319                     return Series(result, index=labels)
   4320                 except Exception:

pandas/_libs/src/reduce.pyx in pandas._libs.lib.reduce()

pandas/_libs/src/reduce.pyx in pandas._libs.lib.Reducer.get_result()

<ipython-input-5-075bc1009366> in processData(row)
     27     data_zhwn['interafce'] = interface
     28     data_zhwn['purl'] = purl
---> 29     data_zhwn['reqDatetime'] = reqDatetime
     30     data_zhwn['code'] = code
     31     data_zhwn['name'] = name

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in __setitem__(self, key, value)
   2329         else:
   2330             # set column
-> 2331             self._set_item(key, value)
   2332 
   2333     def _setitem_slice(self, key, value):

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in _set_item(self, key, value)
   2395 
   2396         self._ensure_valid_index(value)
-> 2397         value = self._sanitize_column(key, value)
   2398         NDFrame._set_item(self, key, value)
   2399 

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in _sanitize_column(self, key, value, broadcast)
   2586             # upcast the scalar
   2587             dtype, value = infer_dtype_from_scalar(value)
-> 2588             value = np.repeat(value, len(self.index)).astype(dtype)
   2589             value = maybe_cast_to_datetime(value, dtype)
   2590 

KeyboardInterrupt: 
data_zhwn.head()
data = data_zhwn.drop(['features'], axis=1)
data.head(1)
data.to_csv('zueriwieneu.csv',index=False)
#!pip install geopandas
import geopandas
quartiere = pd.read_json(path_or_buf="https://data.stadt-zuerich.ch/dataset/statistisches_quartier/resource/c837926e-035d-48b9-8656-03f1b13c323b/download/statistische_quartiere.json", lines=False,encoding='utf-8', orient="records")
quartiere.head()
features name type
0 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
1 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
2 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
3 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
4 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
#!pip install --upgrade pip
Requirement already up-to-date: pip in /Users/sarasua/anaconda3/lib/python3.6/site-packages (18.0)
#!pip install rtree
Collecting rtree
  Downloading https://files.pythonhosted.org/packages/33/0d/0bcb0fdd759570e85697189f675fa462131e5636482f2832288bbe4bb6f7/Rtree-0.8.3-py3-none-any.whl
Requirement already satisfied: setuptools in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from rtree)
Installing collected packages: rtree
Successfully installed rtree-0.8.3
You are using pip version 9.0.1, however version 18.0 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
#!pip install libspatialindex_c
Collecting libspatialindex_c
  Could not find a version that satisfies the requirement libspatialindex_c (from versions: )
No matching distribution found for libspatialindex_c
 
 
import geopandas
q = pd.read_json("https://data.stadt-zuerich.ch/dataset/statistisches_quartier/resource/c837926e-035d-48b9-8656-03f1b13c323b/download/statistische_quartiere.json")
q.head()
features name type
0 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
1 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
2 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
3 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
4 {'type': 'Feature', 'geometry': {'type': 'Mult... StatistischesQuartier FeatureCollection
zuriwieneu = geopandas.read_file('https://data.stadt-zuerich.ch/dataset/zueriwieneu_meldungen/resource/2fee5562-1842-4ccc-a390-c52c9dade90d/download/zueriwieneu_meldungen.json')
quartiere = geopandas.read_file('https://data.stadt-zuerich.ch/dataset/statistisches_quartier/resource/c837926e-035d-48b9-8656-03f1b13c323b/download/statistische_quartiere.json');

zuriwieneu_joint = geopandas.sjoin(quartiere, zuriwieneu, how="left", op="intersects");
zuriwieneu_joint.head()
qnr qname knr kname geometry index_right service_request_id e n service_code ... detail media_url interface_used requested_datetime agency_sent_datetime updated_datetime service_notice title description service_name
0 92 Altstetten 9 Kreis 9 (POLYGON ((8.470236386719071 47.3739506414208,... 4156 4652 2678720 1250712 Grünflächen/Spielplätze ... Vor einiger Zeit wurden die Limmatauen mit seh... Web interface 20140730111906 20140805115005 None Ab einem Abfluss von 200m3/s fliesst das Limma... Vor einiger Zeit wurden d Vor einiger Zeit wurden d: Vor einiger Zeit wu... Grünflächen/Spielplätze
0 92 Altstetten 9 Kreis 9 (POLYGON ((8.470236386719071 47.3739506414208,... 4305 4809 2677753 1250704 Strasse/Trottoir/Platz ... Dieses Teil auf dem Foto ragt ziemlich weit ra... https://www.zueriwieneu.ch/photo/4809.0.jpeg?2... Android 20140820225111 20140821162507 None Diese Reparatur wird von uns in den kommenden ... Stolperfalle Stolperfalle: Dieses Teil auf dem Foto ragt zi... Strasse/Trottoir/Platz
0 92 Altstetten 9 Kreis 9 (POLYGON ((8.470236386719071 47.3739506414208,... 10707 11716 2678713 1250723 Grünflächen/Spielplätze ... Loch im Holzweg https://www.zueriwieneu.ch/photo/11716.0.jpeg?... iOS 20170709143414 20170710133304 None Besten Dank für Ihre Meldung. Wir werden den S... Loch im Holzweg Loch im Holzweg: Loch im Holzweg Grünflächen/Spielplätze
0 92 Altstetten 9 Kreis 9 (POLYGON ((8.470236386719071 47.3739506414208,... 310 367 2677881 1250716 Strasse/Trottoir/Platz ... Auf der anderen Seite der Limmat ist die Umlei... https://www.zueriwieneu.ch/photo/367.0.jpeg?93... Web interface 20130423125215 20130423142007 None Dieser Schaden wird im Rahmen des anstehenden ... Fussgängerweg Fussgängerweg: Auf der anderen Seite der Limma... Strasse/Trottoir/Platz
0 92 Altstetten 9 Kreis 9 (POLYGON ((8.470236386719071 47.3739506414208,... 14133 15481 2678303 1250723 Grünflächen/Spielplätze ... Vorkommen von Schmalblättrigem Greiskraut am F... Web interface 20180726123247 20180731143202 None Besten Dank für Ihre Meldung. Wir gehen der Sa... Vorkommen Vorkommen: Vorkommen von Schmalblättrigem Grei... Grünflächen/Spielplätze

5 rows × 21 columns

%matplotlib inline 
import seaborn as sns
ax = zuriwieneu_joint['qname'].value_counts().plot(kind='bar')
#ax.set_xticklabels(c['Quartier'],rotation=90)
#zuriwieneu_joint['qname'].value_counts()
#sns.distplot(zuriwieneu_joint['qname'].value_counts(), kde=False, rug=True);
zuriwieneu_joint.service_code.unique()
array(['Grünflächen/Spielplätze', 'Strasse/Trottoir/Platz',
       'Abfall/Sammelstelle', 'Signalisation/Lichtsignal',
       'Brunnen/Hydranten', 'Graffiti', 'Beleuchtung/Uhren', 'VBZ/ÖV'], dtype=object)
c =zuriwieneu_joint.groupby(by=['qname'])['service_code'].value_counts()
#ax = c.plot(kind='bar')
#!pip install jupyter-gmaps
Collecting jupyter-gmaps
  Could not find a version that satisfies the requirement jupyter-gmaps (from versions: )
No matching distribution found for jupyter-gmaps
#!pip install gmaps
Collecting gmaps
  Downloading https://files.pythonhosted.org/packages/38/6d/351fb2605ad757d296308be7714000b14599425ebeb6482d3cfcd2690a3f/gmaps-0.8.0.tar.gz (1.1MB)
    100% |████████████████████████████████| 1.1MB 7.0MB/s eta 0:00:01
Requirement already satisfied: ipython>=5.3.0 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from gmaps) (6.1.0)
Requirement already satisfied: ipywidgets>=7.0.0 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from gmaps) (7.0.0)
Requirement already satisfied: traitlets>=4.3.0 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from gmaps) (4.3.2)
Collecting geojson>=2.0.0 (from gmaps)
  Downloading https://files.pythonhosted.org/packages/8d/39/231105abbfd2332f108cdbfe736e56324949fa9e80e536ae60a082cf96a9/geojson-2.4.0-py2.py3-none-any.whl
Requirement already satisfied: six in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from gmaps) (1.11.0)
Requirement already satisfied: setuptools>=18.5 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (36.5.0.post20170921)
Requirement already satisfied: jedi>=0.10 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (0.10.2)
Requirement already satisfied: decorator in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (4.1.2)
Requirement already satisfied: pickleshare in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (0.7.4)
Requirement already satisfied: simplegeneric>0.8 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (0.8.1)
Requirement already satisfied: prompt_toolkit<2.0.0,>=1.0.4 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (1.0.15)
Requirement already satisfied: pygments in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (2.2.0)
Requirement already satisfied: appnope in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (0.1.0)
Requirement already satisfied: pexpect in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipython>=5.3.0->gmaps) (4.2.1)
Requirement already satisfied: ipykernel>=4.5.1 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipywidgets>=7.0.0->gmaps) (4.6.1)
Requirement already satisfied: nbformat>=4.2.0 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipywidgets>=7.0.0->gmaps) (4.4.0)
Requirement already satisfied: widgetsnbextension~=3.0.0 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipywidgets>=7.0.0->gmaps) (3.0.2)
Requirement already satisfied: ipython_genutils in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from traitlets>=4.3.0->gmaps) (0.2.0)
Requirement already satisfied: wcwidth in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from prompt_toolkit<2.0.0,>=1.0.4->ipython>=5.3.0->gmaps) (0.1.7)
Requirement already satisfied: jupyter_client in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->gmaps) (5.1.0)
Requirement already satisfied: tornado>=4.0 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from ipykernel>=4.5.1->ipywidgets>=7.0.0->gmaps) (4.5.2)
Requirement already satisfied: jsonschema!=2.5.0,>=2.4 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from nbformat>=4.2.0->ipywidgets>=7.0.0->gmaps) (2.6.0)
Requirement already satisfied: jupyter_core in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from nbformat>=4.2.0->ipywidgets>=7.0.0->gmaps) (4.3.0)
Requirement already satisfied: notebook>=4.4.1 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from widgetsnbextension~=3.0.0->ipywidgets>=7.0.0->gmaps) (5.0.0)
Requirement already satisfied: pyzmq>=13 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from jupyter_client->ipykernel>=4.5.1->ipywidgets>=7.0.0->gmaps) (16.0.2)
Requirement already satisfied: python-dateutil>=2.1 in /Users/sarasua/anaconda3/lib/python3.6/site-packages (from jupyter_client->ipykernel>=4.5.1->ipywidgets>=7.0.0->gmaps) (2.6.1)
Building wheels for collected packages: gmaps
  Running setup.py bdist_wheel for gmaps ... done
  Stored in directory: /Users/sarasua/Library/Caches/pip/wheels/03/25/cd/097e0f11523a7a214390fcc056e68d45611bfe465be6c84d33
Successfully built gmaps
Installing collected packages: geojson, gmaps
Successfully installed geojson-2.4.0 gmaps-0.8.0
zuriwieneu_joint['geometry'][0]
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
                           ...                        
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
0    (POLYGON ((8.470236386719071 47.3739506414208,...
Name: geometry, Length: 759, dtype: object
 
 
 
 
 
import gmaps
gmaps.configure(api_key='AIzaSyDKqiAJcooSJp3J0mpakABt3EEL_ZGZzOE')
import gmaps.datasets


earthquake_df = gmaps.datasets.load_dataset_as_df('earthquakes')
earthquake_df.head()
latitude longitude magnitude
0 65.193300 -149.072500 1.70
1 38.791832 -122.780830 2.10
2 38.818001 -122.792168 0.48
3 33.601667 -116.727667 0.78
4 37.378334 -118.520836 3.64
geo = zuriwieneu_joint['geometry']
locations = earthquake_df[['latitude', 'longitude']]
weights = earthquake_df['magnitude']
fig = gmaps.figure()
fig.add_layer(gmaps.heatmap_layer(locations, weights=weights))
#fig.add_layer(gmaps.heatmap_layer(zuriwieneu_joint, weights=weights))
fig
import gmaps.geojson_geometries


countries_geojson = gmaps.geojson_geometries.load_geometry('countries')

fig = gmaps.figure()

gini_layer = gmaps.geojson_layer(countries_geojson)
fig.add_layer(gini_layer)
fig
 
 
## My data
g = zuriwieneu_joint[['qname','service_code']]
c = g.groupby(by=['qname','service_code']).size()
c
qname         service_code             
Affoltern     Abfall/Sammelstelle          149
              Beleuchtung/Uhren             71
              Brunnen/Hydranten              8
              Graffiti                      18
              Grünflächen/Spielplätze       56
              Signalisation/Lichtsignal     37
              Strasse/Trottoir/Platz       136
              VBZ/ÖV                        10
Albisrieden   Abfall/Sammelstelle          117
              Beleuchtung/Uhren             31
              Brunnen/Hydranten             11
              Graffiti                       7
              Grünflächen/Spielplätze       28
              Signalisation/Lichtsignal     49
              Strasse/Trottoir/Platz       136
              VBZ/ÖV                         5
Alt-Wiedikon  Abfall/Sammelstelle          244
              Beleuchtung/Uhren             65
              Brunnen/Hydranten              8
              Graffiti                       3
              Grünflächen/Spielplätze       35
              Signalisation/Lichtsignal    104
              Strasse/Trottoir/Platz       122
              VBZ/ÖV                         3
Altstetten    Abfall/Sammelstelle          194
              Beleuchtung/Uhren             64
              Brunnen/Hydranten             13
              Graffiti                      52
              Grünflächen/Spielplätze       63
              Signalisation/Lichtsignal    149
                                          ... 
Werd          Brunnen/Hydranten              5
              Graffiti                      16
              Grünflächen/Spielplätze        8
              Signalisation/Lichtsignal     35
              Strasse/Trottoir/Platz        65
              VBZ/ÖV                         9
Wipkingen     Abfall/Sammelstelle          266
              Beleuchtung/Uhren             60
              Brunnen/Hydranten              9
              Graffiti                      54
              Grünflächen/Spielplätze       91
              Signalisation/Lichtsignal    142
              Strasse/Trottoir/Platz       232
              VBZ/ÖV                         5
Witikon       Abfall/Sammelstelle           41
              Beleuchtung/Uhren             34
              Brunnen/Hydranten              3
              Graffiti                       7
              Grünflächen/Spielplätze       59
              Signalisation/Lichtsignal     46
              Strasse/Trottoir/Platz        59
              VBZ/ÖV                         8
Wollishofen   Abfall/Sammelstelle          142
              Beleuchtung/Uhren             51
              Brunnen/Hydranten              3
              Graffiti                      23
              Grünflächen/Spielplätze       75
              Signalisation/Lichtsignal     90
              Strasse/Trottoir/Platz       120
              VBZ/ÖV                         2
Length: 269, dtype: int64
zuriwieneu_joint['qname'].value_counts().to_csv('zuriwienew_requests.csv')
data = zuriwieneu_joint['qname'].value_counts().index
print(type(data))
<class 'method'>
print(type(c))
c.columns =['Quartier','requests']
c.to_csv("zuriewineu_indicator.csv")
    
    
 
### Column
 
 
 
 
 
#TODO create a general boxplot / histogram that focusing on one concrete feature (column in the CSV file) gives the plot
 
 
 
# For the team
 
 
 
 

------------------------------------------------------

-- PLOTS
 
 
data_culture = pd.read_csv('/Users/sarasua/Downloads/Amenities_Per_Living_Area_final.v01.csv')
data_culture.head()
Unnamed: 0 qnr kindergarten_nr kunstimstadtraum_nr parke_nr brunnen_nr kinderhort_nr spielplatzte_nr denkmalschutzobjekt_nr volksschule_nr kinderkrippe_nr sporthalle_nr qname knr kname areaKm2
0 0 11 3.644 54.665 7.289 131.195 0.000 7.289 54.665 0.000 0.000 0.0 Rathaus 1 Kreis 1 0.2744
1 1 12 2.176 30.461 10.879 100.087 4.352 2.176 30.461 2.176 6.527 0.0 Hochschulen 1 Kreis 1 0.4596
2 2 13 0.000 52.533 11.257 120.075 0.000 7.505 52.533 0.000 0.000 0.0 Lindenhof 1 Kreis 1 0.2665
3 3 14 3.644 40.080 7.287 65.586 1.822 1.822 40.080 1.822 14.575 0.0 City 1 Kreis 1 0.5489
4 4 21 4.573 5.717 1.715 19.436 6.860 2.001 5.717 2.001 4.002 0.0 Wollishofen 2 Kreis 2 3.4986
c1 = data_culture[['qname','denkmalschutzobjekt_nr']]
cc1 = c1.sort_values(by=['denkmalschutzobjekt_nr'],ascending=False)
ax = cc1.plot(x='qname',y='denkmalschutzobjekt_nr',kind='bar')
ax.set_title("Listed Objects per Neighbourhood")
ax.set_xlabel("Quartier")
#ax.set_ylabel("Number of Fountains")
fig = ax.get_figure()
#fig.savefig('/Users/sarasua/Desktop/culture/brunnen.jpg')
c2 = data_culture[['qname','brunnen_nr']]
cc2 = c2.sort_values(by=['brunnen_nr'],ascending=False)
ax = cc2.plot(x='qname',y='brunnen_nr',kind='bar')
ax.set_title("Number of Fountains per Neighbourhood")
ax.set_xlabel("Quartier")
#ax.set_ylabel("Number of Fountains")
fig = ax.get_figure()
#fig.savefig('/Users/sarasua/Desktop/culture/brunnen.jpg')
c3 = data_culture[['qname','kunstimstadtraum_nr']]
cc3 = c3.sort_values(by=['kunstimstadtraum_nr'],ascending=False)
ax = cc3.plot(x='qname',y='kunstimstadtraum_nr',kind='bar')
ax.set_title("Public Art Pieces per Neighbourhood")
ax.set_xlabel("Quartier")
#ax.set_ylabel("Number of Fountains")
fig = ax.get_figure()
#fig.savefig('/Users/sarasua/Desktop/culture/brunnen.jpg')
import csv
population = pd.read_csv('/Users/sarasua/Downloads/density.csv',sep=',', quoting=csv.QUOTE_ALL)
population.head()
spaceLabel year peoplePerKm2 Unnamed: 3
0 Mühlebach 2017-12-31 9664.471621 NaN
1 Weinegg 2017-12-31 3331.640427 NaN
2 Seefeld 2017-12-31 7649.515287 NaN
3 Altstetten 2017-12-31 5765.952179 NaN
4 Albisrieden 2017-12-31 8147.152015 NaN
population.columns
Index([' spaceLabel', 'year', 'peoplePerKm2 ', 'Unnamed: 3'], dtype='object')
e1 = population[[' spaceLabel','peoplePerKm2 ']]
ee1 = e1.sort_values(by=['peoplePerKm2 '])
ax = ee1.plot(x=' spaceLabel',y='peoplePerKm2 ',kind='bar')
ax.set_title("Population Density per Neighbourhood")
ax.set_xlabel("Quartier")
#ax.set_ylabel("Number of Fountains")
fig = ax.get_figure()
#fig.savefig('/Users/sarasua/Desktop/culture/brunnen.jpg')
landuse = pd.read_csv('/Users/sarasua/Downloads/landuse.csv',sep=',', quoting=csv.QUOTE_ALL)
landuse.head()
spaceLabel year greenAreaRatio Unnamed: 3
0 Affoltern 2017-12-31 45.199292 NaN
1 Albisrieden 2017-12-31 50.000000 NaN
2 Alt-Wiedikon 2017-12-31 6.824628 NaN
3 Altstetten 2017-12-31 33.595332 NaN
4 City 2017-12-31 6.419089 NaN
landuse.columns
Index([' spaceLabel', 'year', 'greenAreaRatio ', 'Unnamed: 3'], dtype='object')
e2 = landuse[[' spaceLabel','greenAreaRatio ']]

ee2 = e2.sort_values(by=['greenAreaRatio '])
ax = ee2.plot(x=' spaceLabel',y='greenAreaRatio ',kind='bar')
ax.set_title("Land Use per Neighbourhood")
ax.set_xlabel("Quartier")
#ax.set_ylabel("Number of Fountains")
fig = ax.get_figure()
#fig.savefig('/Users/sarasua/Desktop/culture/brunnen.jpg')
import csv
pollution = pd.read_csv('/Users/sarasua/Downloads/pollutionraw_my.csv',sep=',', quoting=csv.QUOTE_ALL)
pollution.head()
Quartier Code AVG
0 Albisrieden http://qweb.cs.aau.dk/airbase/schema/PM10 18.200000
1 Albisrieden http://qweb.cs.aau.dk/airbase/schema/NOX 76.211000
2 Albisrieden http://qweb.cs.aau.dk/airbase/schema/CO 0.453667
3 Alt-Wiedikon http://qweb.cs.aau.dk/airbase/schema/PM10 18.200000
4 Alt-Wiedikon http://qweb.cs.aau.dk/airbase/schema/NOX 76.211000
pollution.columns
Index(['Quartier', 'Code', 'AVG'], dtype='object')
pollution.Code.unique()
array(['http://qweb.cs.aau.dk/airbase/schema/PM10',
       'http://qweb.cs.aau.dk/airbase/schema/NOX',
       'http://qweb.cs.aau.dk/airbase/schema/CO',
       ' http://qweb.cs.aau.dk/airbase/schema/NOX',
       ' http://qweb.cs.aau.dk/airbase/schema/CO',
       ' http://qweb.cs.aau.dk/airbase/schema/PM10'], dtype=object)
g = pollution.loc[pollution['Code']=='http://qweb.cs.aau.dk/airbase/schema/CO']
g.head()
Quartier Code AVG
2 Albisrieden http://qweb.cs.aau.dk/airbase/schema/CO 0.453667
5 Alt-Wiedikon http://qweb.cs.aau.dk/airbase/schema/CO 0.453667
8 Altstetten http://qweb.cs.aau.dk/airbase/schema/CO 0.453667
9 City http://qweb.cs.aau.dk/airbase/schema/CO 0.311000
12 Escher Wyss http://qweb.cs.aau.dk/airbase/schema/CO 0.311000
ee3a.head()
Quartier AVG
66 Saatlen 28.5855
60 Rathaus 28.5855
35 Hirzenbach 28.5855
1 Werd 33.9185
65 Rathaus 33.9185
 
e3a = g[['Quartier','AVG']]

ee3 = e3a.sort_values(by=['AVG'])

ax = ee3.plot(x='Quartier',y='AVG',kind='bar')
ax.set_title("Pollution per Neighbourhood (CO2)")
ax.set_xlabel("Quartier")
#ax.set_ylabel("Number of Fountains")
fig = ax.get_figure()
#fig.savefig('/Users/sarasua/Desktop/culture/brunnen.jpg')
 
 
 
 
 
 ax = c1['brunnen_nr'].hist(l)