matplotlib https://matplotlib.org/3.1.1/tutorials/introductory/pyplot.html

https://matplotlib.org/3.1.1/tutorials/introductory/pyplot.html

Why f, ax = plt.subplots()

f, ax = plt.subplots()
ax.plot(x, y)
ax.set_title('Simple plot')

plt.subplots() is a function that returns a tuple containing a figure and axes object(s). Thus when using fig, ax = plt.subplots() you unpack this tuple into the variables fig and ax.
 Having fig is useful if you want to change figure-level attributes or save the figure as an image file later (e.g. with fig.savefig('yourfilename.png')). You certainly don't have to use the returned figure object but many people do use it later so it's common to see.
Also, all axes objects (the objects that have plotting methods), have a parent figure object anyway, thus:

fig, ax = plt.subplots()

is more concise than this:

fig = plt.figure()
ax = fig.add_subplot(111)

plt.plot([1, 2, 3, 4], [1, 4, 9, 16]) 

Plot Geodataframe kleur

https://matplotlib.org/tutorials/colors/colormaps.html
kleure 'Greys', 'Purples', 'Blues', 'Greens', 'Oranges', 'Reds', 'YlOrBr', 'YlOrRd', 'OrRd', 'PuRd', 'RdPu', 'BuPu', 'GnBu', 'PuBu', 'YlGnBu', 'PuBuGn', 'BuGn', 'YlGn']

 ​

cmap='Blues'

gdf2.plot(column='myval',cmap=cmap)

Plot Legend mooi

Zet legend op true print niet heel mooi

fig, ax = plt.subplots(1, 1)
gdf2.plot(column='myval',ax=ax,legend=True) 

Aanpassen op de volgende manier

from mpl_toolkits.axes_grid1 import make_axes_locatable
fig, ax = plt.subplots(1, 1,figsize = (10, 10))
divider = make_axes_locatable(ax)
cax = divider.append_axes("right", size="5%", pad=0.4)
gdf2.plot(column='myval',ax=ax,legend=True,cax=cax,cmap='Blues') 

Plot een marker in het midden van een shape

from shapely.geometry import Point
from shapely.geometry import shape
gdfP=gdf2.copy()
gdfP['geometry']=gdfP['geometry'].centroid

fig, ax = plt.subplots(figsize=(15, 15))
base = gdf2.plot(column='myval',ax=ax,legend=True)
gdfP.plot(ax=base,marker='o',color='red', markersize=4)

Print een cloropleth en een markers over elkaar

ax1 = gdf2.plot(figsize = (15, 15),column='myval',cmap='Blues')

texts = []
for x, y, label in zip(gdfP.geometry.x, gdfP.geometry.y, gdfP["Gebied"]):
    texts.append(plt.text(x, y, label, fontsize = 8))

dit werkt ook : zonder teksts.append

ax1 = gdf2.plot(figsize = (15, 15),column='myval',cmap='Blues')

texts = []
for x, y, label in zip(gdfP.geometry.x, gdfP.geometry.y, gdfP["Gebied"]):
   plt.text(x, y, label, fontsize = 8)

Geomapping Folium

# -*- coding: utf-8 -*-
"""
Created on Thu Aug 29 09:42:55 2019

@author: wagenerj
"""
import pandas as pd
import folium
import geojson
from shapely.geometry import shape


shapefile2 = 'C://Users//wagenerj//Documents//data//amsterdamgeo//GEBIEDEN22.json'#Read shapefile using Geopandas

# =============================================================================
# https://github.com/python-visualization/folium/blob/master/examples/GeoJSON_and_choropleth.ipynb
# =============================================================================

#Lees GEOJSON file in
with open(shapefile2) as f:
    geo_json_data = geojson.load(f)



# =============================================================================
# Probeer de structuur van de GEOJSON file te begrijpen
#
# GEOJSON ___type (string)
#         ___features (list)
#       
# features is een lijst van  Feature objecten
#
# een Feature is een collectie met de volgende keys
# geometry   (geometry.Polygon)
# id         (int)
# properties (dict)
# type       (str)
#
# EVT TE bekijken via
# for key, value in geo_json_data.features[2].items() :
#     print (key)
#
# Belangrijk is om te weten wat de properties zijn van het feature die kunnen nl elke keer weer een beetje verschillen
# dit kan je opvragen door bijv
# geo_json_data.features[2]['properties']
#
#
# for feature in geo_json_data['features']:
#     print(feature['properties'])
#
# geo_json_data.features[2].properties
# geo_json_data.features[2].properties['Gebied']
# geo_json_data.features[2].properties['Gebied_code']
# =============================================================================


for feature in geo_json_data['features']:
     print(feature['id'])
#    print(feature['properties']['Gebied_code'])
     feature['properties']['centroid_x']=shape(feature['geometry']).centroid.x
     feature['properties']['centroid_y']=shape(feature['geometry']).centroid.y
     print(shape(feature['geometry']).centroid)
# Essentieel we maken hier het feature ID gelijk aan Gebied_code omdat er op gematsch kan worden 
#    feature['id']=feature['properties']['Gebied_code']




m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
#simpel
folium.GeoJson(geo_json_data).add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )


m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
folium.GeoJson(
    geo_json_data,
    style_function=lambda feature: {
       'fillColor': 'green' if 'e' in feature['properties']['Gebied'].lower() else '#ffff00',
        'color': 'black',
        'weight': 2,
        'dashArray': '5, 5'
    }
).add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )



# we moeten een een kleur voor elk gebied berekenen
gbdata = pd.read_csv('C://Users//wagenerj//Documents//data//amsterdamgeo//datapergebied.csv',sep=';')
gbdata.columns =gbdata.columns.str.strip()


#create een functiie die op basis van de waarden een colormap aanmaakt
from branca.colormap import linear
colormap = linear.YlGn_09.scale(gbdata['aantal'].min(),gbdata['aantal'].max())
print(colormap(5.0))
colormap
print(colormap)

#convert de tabel in a dictionary, zodat we een featere aan de waarde van het gebeid kunnen koppelen
gbdata_dict= gbdata.set_index('Gebied_code')['aantal']


#Essentie is hier dat je bij fillcoolor de juiste key doorgeeft aan de gbdata_dict. Hierin moet de gebiedscode staan van het Feature :feature['properties']['Gebied_code']
#WERKEND add Popup to polygon shapes in chloropleth
#https://github.com/python-visualization/folium/issues/1020
m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
gj=folium.GeoJson(
    geo_json_data,
    name='unemployment',
    tooltip=folium.GeoJsonTooltip(fields=['centroid_x']),
    style_function=lambda feature: {
        'fillColor': colormap(gbdata_dict[feature['properties']['Gebied_code']]),
        'color': 'black',
        'weight': 1,
        'dashArray': '5, 5',
        'fillOpacity': 0.9,})
#gj.add_child(folium.Popup('outline Popup on GeoJSON'))
gj.add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )







   
   








#https://github.com/python-visualization/folium/issues/1020
m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
gj=folium.GeoJson(
    geo_json_data,
    name='unemployment',
    tooltip=folium.GeoJsonTooltip(fields=['Gebied']),
    style_function=lambda feature: {
        'fillColor': colormap(gbdata_dict[feature['properties']['Gebied_code']]),
        'color': 'black',
        'weight': 1,
        'dashArray': '5, 5',
        'fillOpacity': 0.9,})
#gj.add_child(folium.Popup('outline Popup on GeoJSON'))
gj.add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )


# Let op dat de Xen Y  in de juiste volgorde staan.
for feature in geo_json_data['features']:
    folium.Marker(
        location=[feature['properties']['centroid_y'], feature['properties']['centroid_x']],
        popup=folium.Popup("Let's try quotes", parse_html=True, max_width=100)
    ).add_to(m)

m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )











































>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
There is a work-around for this. You need to iterate over the each geoJson feature and create a new geojson for each one.
Then, add a popup for each geoJson feature. Then combine all features in a layer. In my code, the full geoJson is data_geojson_dict

m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
list_tooltip_vars=['Gebied_code']

layer_geom = folium.FeatureGroup(name='layer',control=False)

for i in range(len(geo_json_data["features"])):
    temp_geojson = {"features":[geo_json_data["features"][i]],"type":"FeatureCollection"}
    temp_geojson_layer = folium.GeoJson(temp_geojson,
                   highlight_function=lambda x: {'weight':3, 'color':'black'},
                    control=False,
                    style_function=lambda feature: {
                   'color': 'black',
                   'weight': 1},
                    tooltip=folium.features.GeoJsonTooltip(fields=list_tooltip_vars,
                                        aliases=[x.capitalize()+":" for x in list_tooltip_vars],
                                          labels=True,
                                          sticky=False))
#    folium.Popup(temp_geojson["features"][0]["properties"]["Gebied"]).add_to(temp_geojson_layer)
    temp_geojson_layer.add_to(layer_geom)

layer_geom.add_to(m)
folium.LayerControl(autoZIndex=False, collapsed=True).add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )





# =============================================================================
# # Using Choropleth class
# # Now if you want to get faster, you can use the Choropleth class. Have a look at it's docstring, it has several styling options.
# # You can use it in providing a file name (geo_path) :
# # =============================================================================
m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
folium.Choropleth(geo_data=geo_json_data).add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )


# werkend folium.chloropleth
m = folium.Map(location=[52.37, 4.90], tiles='Stamen Terrain', zoom_start=12)
folium.Choropleth(
    geo_data=geo_json_data,
    data=gbdata,
    columns=['Gebied_code', 'aantal'],
    key_on='feature.properties.Gebied_code',
    fill_color='YlGn',
    fill_opacity=0.7,
    line_opacity=0.2,
    legend_name='Gebieden  (%)',
#    highlight=True,
    tooltip=folium.GeoJsonTooltip(fields=['Gebied'])
).add_to(m)
m.save("C://Users//wagenerj//Documents//data//amsterdamgeo//m.html" )

Converteren datatypes

converteer bedrag naar numeriek

Vaak krijg je bij inlezen van data uit csv een bedrag in Nederlandse notatie. dus met een Komma. Dit bedrag wordt niet gezien als een getal en ingelezen als dtype string. je kan niet zomaar PD.TO_NUMERIC doen

Hoe maak je van deze string een bedrag

vervang eerst punt door komma met str.replace en dan converteren


dfcombi['bedrag_ZENG']=round(pd.to_numeric(dfcombi['BEDRAG_2018'].str.replace(',', '.')),0)

 

 

Converteer Floating getallen naar integers in een dataframe

selecteer eerst alle dtypes kolomen in een dataframe en filter evt nog een column. Verander daarna collectief het datatype

df_float_col = dfData.select_dtypes(include = ['float64'])
df_float_col=df_float_col.drop(columns=['bedrag'])
print(dffloat_col.columns)
for col in df_float_col.columns.values:
    dfData[col]=dfData[col].fillna(0.0).astype('int64')
dfData

SQLALCHEMY : dynamische dataframe in een tabel stoppen zonder class te definieren (ORACLE 12)

#  Hoe krijg ik een grote dataset snel in een tabel
#  Oracle heeft zijn eigen specifieke dingetjes mbt datatypes etc

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import *

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
from sqlalchemy.dialects.oracle import VARCHAR2

sqllite_DB='sqlite:///C:\\Users\\wagene002\\Documents\\Python\\howto\\DB_ZenG.db'
engine = create_engine(sqllite_DB,echo=False)
Base = declarative_base()

import cx_Oracle

#  method 2: met service naam
oracle_connection_string = ('oracle+cx_oracle://DM:*****@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=********)(SERVER=DEDICATED)))')engine = create_engine(oracle_connection_string)
Base = declarative_base()



#==>  Maak een Pandas dataset
import pandas as pd
df=pd.read_csv('C:\\Users\wagene002\Documents\Python\howto\levering1.csv')
df1=df[[ 'valid_bsn','bsn', 'code_voorziening', 'jaar', 'bedrag']]


# speciaal voor Oracle: to_sql maakt clobs van characters, via dtype dit oplossen

# manier 1: maak handmatig types aan
dict_types={'bsn': VARCHAR2(128), 'code_voorziening': VARCHAR2(100)}
# manier 2: maak automatisch dict aan met alle velden  varchar
dict_types={}
for i in df1.columns:
    dict_types[i]= VARCHAR2(150)

#maak een tabel van een gestripte dataset (3 records)
dfDef=df1.iloc[0:2,]
dfDef.to_sql(name='LeveringenSociaal',con=engine, index=False,if_exists="replace" ,dtype=dict_types)

# ORACLE TRUUK:  maak alle velden van de de dataset die weggeschreven worden string values
#zonder deze stap krijg je bij wegschrijven naar ORAClEfoutmelding TypeError: expecting string or bytes object
df8=df1.astype(str)


#===> Map een Database Tabel aan een Class Object cLev
class cLev(object):
    pass

metadata=MetaData(engine)
tblLeveringen=Table('LeveringenSociaal', metadata,Column("id", Integer, primary_key=True) ,autoload=True)
engine.execute(tblLeveringen.delete())
mapper(cLev,tblLeveringen)


# Nu de volledige dataset in de tabel stoppen
Session = sessionmaker(bind=engine)
session = Session()
session.bulk_insert_mappings(cLev, df8.to_dict(orient="records"))
session.commit()
session.close()

SQLALCHEMY: links

https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/

http://www.blog.pythonlibrary.org/2010/09/10/sqlalchemy-connecting-to-pre-existing-databases/

https://www.freecodecamp.org/news/sqlalchemy-makes-etl-magically-easy-ab2bd0df928/

https://www.codementor.io/bruce3557/graceful-data-ingestion-with-sqlalchemy-and-pandas-pft7ddcy6


https://sdsawtelle.github.io/blog/output/large-data-files-pandas-sqlite.html

SQLALCHEMY : Oracle tabel aanmaken

from sqlalchemy import *
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String

from sqlalchemy.dialects.oracle import VARCHAR2
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
import cx_Oracle

#  method 2: met service naam
oracle_connection_string = ('oracle+cx_oracle://DM:######@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=******.basis.lan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=********)(SERVER=DEDICATED)))')

engine = create_engine(oracle_connection_string)
# engine = create_engine('sqlite:///student.db', echo=True)
Base = declarative_base()

########################################################################
class Student(Base):
    """"""
    __tablename__ = "student"

    id = Column(Integer, primary_key=True)
    username = Column(VARCHAR2(255))
  

    #----------------------------------------------------------------------
    def __init__(self, username, firstname, lastname, university):
        """"""
        self.username = username
       

# create tables
Base.metadata.create_all(engine)

SQLALCHEMY: Hoe vul je tabel uit Statische dataframe via ORM class.

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import *


sqllite_DB='sqlite:///C:\\Users\\wagene002\\Documents\\Python\\howto\\DB_ZenG.db'
engine = create_engine(sqllite_DB)
Base = declarative_base()

class LeveringenSociaal(Base):
    __tablename__ = "LeveringenSociaal"
    Index = Column(Integer, primary_key=True)
    valid_bsn = Column(String)
    bsn = Column(String)
    code_voorziening = Column(String)
    jaar = Column(String)
    bedrag = Column(String)

LeveringenSociaal.__table__.create(bind=engine, checkfirst=True)



==>
import pandas as pd
df=pd.read_csv('C:\\Users\wagene002\Documents\Python\howto\levering1.csv')

df1=df[['valid_bsn','bsn', 'code_voorziening', 'jaar', 'bedrag']]



===> manier 1. Niet zo snel. Per record Inserten
leveringensociaal=[]

for index,row in df1.iterrows():
    leveringensociaal.append(row)
  
Session = sessionmaker(bind=engine)
session = Session()

for lever in leveringensociaal:
    row = LeveringenSociaal(**lever)
    session.add(row)
  

session.commit()


===>  manier 2: Zeer Snel. Via Bulk Loader
Session = sessionmaker(bind=engine)
session = Session()
session.bulk_insert_mappings(LeveringenSociaal, df1.to_dict(orient="records"))
session.commit()
session.close()