Import Gemeinden.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: xlrd==2.0.1 in /opt/conda/lib/python3.6/site-packages (2.0.1)\n",
      "Requirement already satisfied: pandas==1.1.5 in /opt/conda/lib/python3.6/site-packages (1.1.5)\n",
      "Requirement already satisfied: pytz>=2017.2 in /opt/conda/lib/python3.6/site-packages (from pandas==1.1.5) (2017.2)\n",
      "Requirement already satisfied: numpy>=1.15.4 in /opt/conda/lib/python3.6/site-packages (from pandas==1.1.5) (1.19.5)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /opt/conda/lib/python3.6/site-packages (from pandas==1.1.5) (2.8.2)\n",
      "Requirement already satisfied: six>=1.5 in /opt/conda/lib/python3.6/site-packages (from python-dateutil>=2.7.3->pandas==1.1.5) (1.10.0)\n",
      "Requirement already satisfied: numpy==1.19.5 in /opt/conda/lib/python3.6/site-packages (1.19.5)\n",
      "Requirement already satisfied: scipy==1.1.0 in /opt/conda/lib/python3.6/site-packages (1.1.0)\n",
      "Requirement already satisfied: numpy>=1.8.2 in /opt/conda/lib/python3.6/site-packages (from scipy==1.1.0) (1.19.5)\n",
      "Requirement already satisfied: openpyxl==3.0.9 in /opt/conda/lib/python3.6/site-packages (3.0.9)\n",
      "Requirement already satisfied: et-xmlfile in /opt/conda/lib/python3.6/site-packages (from openpyxl==3.0.9) (1.1.0)\n",
      "Requirement already satisfied: pyproj==3.0.1 in /opt/conda/lib/python3.6/site-packages (3.0.1)\n",
      "Requirement already satisfied: certifi in /opt/conda/lib/python3.6/site-packages (from pyproj==3.0.1) (2017.7.27.1)\n",
      "Requirement already satisfied: geopandas==0.8.0 in /opt/conda/lib/python3.6/site-packages (0.8.0)\n",
      "Requirement already satisfied: pandas>=0.23.0 in /opt/conda/lib/python3.6/site-packages (from geopandas==0.8.0) (1.1.5)\n",
      "Requirement already satisfied: shapely in /opt/conda/lib/python3.6/site-packages (from geopandas==0.8.0) (1.8.2)\n",
      "Requirement already satisfied: pyproj>=2.2.0 in /opt/conda/lib/python3.6/site-packages (from geopandas==0.8.0) (3.0.1)\n",
      "Requirement already satisfied: fiona in /opt/conda/lib/python3.6/site-packages (from geopandas==0.8.0) (1.8.21)\n",
      "Requirement already satisfied: numpy>=1.15.4 in /opt/conda/lib/python3.6/site-packages (from pandas>=0.23.0->geopandas==0.8.0) (1.19.5)\n",
      "Requirement already satisfied: pytz>=2017.2 in /opt/conda/lib/python3.6/site-packages (from pandas>=0.23.0->geopandas==0.8.0) (2017.2)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /opt/conda/lib/python3.6/site-packages (from pandas>=0.23.0->geopandas==0.8.0) (2.8.2)\n",
      "Requirement already satisfied: certifi in /opt/conda/lib/python3.6/site-packages (from pyproj>=2.2.0->geopandas==0.8.0) (2017.7.27.1)\n",
      "Requirement already satisfied: click-plugins>=1.0 in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (1.1.1)\n",
      "Requirement already satisfied: cligj>=0.5 in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (0.7.2)\n",
      "Requirement already satisfied: click>=4.0 in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (6.7)\n",
      "Requirement already satisfied: attrs>=17 in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (21.4.0)\n",
      "Requirement already satisfied: six>=1.7 in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (1.10.0)\n",
      "Requirement already satisfied: munch in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (2.5.0)\n",
      "Requirement already satisfied: setuptools in /opt/conda/lib/python3.6/site-packages (from fiona->geopandas==0.8.0) (36.2.2.post20170724)\n",
      "Requirement already satisfied: matplotlib==3.3.4 in /opt/conda/lib/python3.6/site-packages (3.3.4)\n",
      "Requirement already satisfied: python-dateutil>=2.1 in /opt/conda/lib/python3.6/site-packages (from matplotlib==3.3.4) (2.8.2)\n",
      "Requirement already satisfied: cycler>=0.10 in /opt/conda/lib/python3.6/site-packages/cycler-0.10.0-py3.6.egg (from matplotlib==3.3.4) (0.10.0)\n",
      "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in /opt/conda/lib/python3.6/site-packages (from matplotlib==3.3.4) (2.2.0)\n",
      "Requirement already satisfied: pillow>=6.2.0 in /opt/conda/lib/python3.6/site-packages (from matplotlib==3.3.4) (8.4.0)\n",
      "Requirement already satisfied: numpy>=1.15 in /opt/conda/lib/python3.6/site-packages (from matplotlib==3.3.4) (1.19.5)\n",
      "Requirement already satisfied: kiwisolver>=1.0.1 in /opt/conda/lib/python3.6/site-packages (from matplotlib==3.3.4) (1.3.1)\n",
      "Requirement already satisfied: six in /opt/conda/lib/python3.6/site-packages (from cycler>=0.10->matplotlib==3.3.4) (1.10.0)\n",
      "Requirement already satisfied: folium==0.12.1.post1 in /opt/conda/lib/python3.6/site-packages (0.12.1.post1)\n",
      "Requirement already satisfied: numpy in /opt/conda/lib/python3.6/site-packages (from folium==0.12.1.post1) (1.19.5)\n",
      "Requirement already satisfied: jinja2>=2.9 in /opt/conda/lib/python3.6/site-packages (from folium==0.12.1.post1) (2.9.6)\n",
      "Requirement already satisfied: requests in /opt/conda/lib/python3.6/site-packages (from folium==0.12.1.post1) (2.18.4)\n",
      "Requirement already satisfied: branca>=0.3.0 in /opt/conda/lib/python3.6/site-packages (from folium==0.12.1.post1) (0.5.0)\n",
      "Requirement already satisfied: MarkupSafe>=0.23 in /opt/conda/lib/python3.6/site-packages (from jinja2>=2.9->folium==0.12.1.post1) (1.0)\n",
      "Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /opt/conda/lib/python3.6/site-packages (from requests->folium==0.12.1.post1) (3.0.4)\n",
      "Requirement already satisfied: idna<2.7,>=2.5 in /opt/conda/lib/python3.6/site-packages (from requests->folium==0.12.1.post1) (2.5)\n",
      "Requirement already satisfied: urllib3<1.23,>=1.21.1 in /opt/conda/lib/python3.6/site-packages (from requests->folium==0.12.1.post1) (1.22)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /opt/conda/lib/python3.6/site-packages (from requests->folium==0.12.1.post1) (2017.7.27.1)\n"
     ]
    }
   ],
   "source": [
    "!pip install xlrd==2.0.1\n",
    "!pip install pandas==1.1.5\n",
    "!pip install numpy==1.19.5\n",
    "!pip install scipy==1.1.0\n",
    "!pip install openpyxl==3.0.9\n",
    "!pip install pyproj==3.0.1\n",
    "!pip install geopandas==0.8.0\n",
    "!pip install matplotlib==3.3.4\n",
    "!pip install folium==0.12.1.post1\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Package             Version\n",
      "------------------- -------------------\n",
      "alembic             0.9.5\n",
      "asn1crypto          0.22.0\n",
      "attrs               21.4.0\n",
      "beautifulsoup4      4.5.3\n",
      "bleach              2.0.0\n",
      "bokeh               0.12.9\n",
      "branca              0.5.0\n",
      "certifi             2017.7.27.1\n",
      "cffi                1.10.0\n",
      "chardet             3.0.4\n",
      "click               6.7\n",
      "click-plugins       1.1.1\n",
      "cligj               0.7.2\n",
      "cloudpickle         0.2.2\n",
      "conda               4.3.23\n",
      "cryptography        1.9\n",
      "cycler              0.10.0\n",
      "Cython              0.25.2\n",
      "dask                0.15.3\n",
      "decorator           4.1.2\n",
      "dill                0.2.6\n",
      "distributed         1.19.1\n",
      "docopt              0.6.2\n",
      "entrypoints         0.2.3\n",
      "et-xmlfile          1.1.0\n",
      "fastcache           1.0.2\n",
      "Fiona               1.8.21\n",
      "folium              0.12.1.post1\n",
      "geopandas           0.8.0\n",
      "h5py                2.6.0\n",
      "heapdict            1.0.0\n",
      "html5lib            0.999999999\n",
      "idna                2.5\n",
      "ipykernel           4.6.1\n",
      "ipython             6.2.1\n",
      "ipython_genutils    0.2.0\n",
      "ipywidgets          7.0.1\n",
      "jedi                0.10.2\n",
      "Jinja2              2.9.6\n",
      "jsonschema          2.6.0\n",
      "jupyter-client      5.1.0\n",
      "jupyter-core        4.3.0\n",
      "jupyterhub          0.8.0\n",
      "jupyterlab          0.27.0\n",
      "jupyterlab-launcher 0.5.1\n",
      "kiwisolver          1.3.1\n",
      "llvmlite            0.16.0\n",
      "locket              0.2.0\n",
      "Mako                1.0.7\n",
      "MarkupSafe          1.0\n",
      "matplotlib          3.3.4\n",
      "mistune             0.7.4\n",
      "msgpack-python      0.4.8\n",
      "munch               2.5.0\n",
      "nbconvert           5.3.1\n",
      "nbformat            4.4.0\n",
      "networkx            2.0\n",
      "notebook            5.1.0\n",
      "numba               0.31.0\n",
      "numexpr             2.6.4\n",
      "numpy               1.19.5\n",
      "olefile             0.44\n",
      "openpyxl            3.0.9\n",
      "packaging           16.8\n",
      "pamela              0.3.0\n",
      "pandas              1.1.5\n",
      "pandocfilters       1.4.1\n",
      "partd               0.3.8\n",
      "patsy               0.4.1\n",
      "pexpect             4.2.1\n",
      "pickleshare         0.7.4\n",
      "Pillow              8.4.0\n",
      "pip                 21.3.1\n",
      "prompt-toolkit      1.0.15\n",
      "protobuf            3.4.0\n",
      "psutil              5.3.1\n",
      "ptpython            0.41\n",
      "ptyprocess          0.5.2\n",
      "pycosat             0.6.2\n",
      "pycparser           2.18\n",
      "Pygments            2.2.0\n",
      "pyOpenSSL           17.2.0\n",
      "pyparsing           2.2.0\n",
      "pyproj              3.0.1\n",
      "PySocks             1.6.7\n",
      "pyspark             2.1.1+hadoop2.7\n",
      "python-dateutil     2.8.2\n",
      "python-editor       1.0.3\n",
      "python-oauth2       1.0.1\n",
      "pytz                2017.2\n",
      "PyYAML              3.12\n",
      "pyzmq               16.0.2\n",
      "requests            2.18.4\n",
      "ruamel_yaml         0.11.14\n",
      "scikit-image        0.12.3\n",
      "scikit-learn        0.18.2\n",
      "scipy               1.1.0\n",
      "seaborn             0.7.1\n",
      "setuptools          36.2.2.post20170724\n",
      "Shapely             1.8.2\n",
      "simplegeneric       0.8.1\n",
      "six                 1.10.0\n",
      "sortedcontainers    1.5.7\n",
      "SQLAlchemy          1.1.13\n",
      "statsmodels         0.8.0\n",
      "sympy               1.0\n",
      "tblib               1.3.2\n",
      "terminado           0.6\n",
      "testpath            0.3.1\n",
      "toolz               0.8.2\n",
      "tornado             4.5.2\n",
      "traitlets           4.3.2\n",
      "urllib3             1.22\n",
      "vincent             0.4.4\n",
      "wcwidth             0.1.7\n",
      "webencodings        0.5\n",
      "wheel               0.29.0\n",
      "widgetsnbextension  3.0.3\n",
      "xlrd                2.0.1\n",
      "zict                0.1.3\n"
     ]
    }
   ],
   "source": [
    "!pip list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyproj in /opt/conda/lib/python3.6/site-packages (3.0.1)\r\n",
      "Requirement already satisfied: certifi in /opt/conda/lib/python3.6/site-packages (from pyproj) (2017.7.27.1)\r\n"
     ]
    }
   ],
   "source": [
    "import xlrd\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import scipy\n",
    "import openpyxl\n",
    "\n",
    "!pip install pyproj\n",
    "from pyproj import Proj, transform\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import geopandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1) Gemeindedaten einlesen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#URL für Datenbezug -> https://www.bfs.admin.ch/bfs/de/home/statistiken/kataloge-datenbanken/tabellen.assetdetail.15864450.html\n",
    "#Dokument als xlsx gespeichert\n",
    "\n",
    "excel_file = pd.ExcelFile('./Rohdaten/gemeinden 2021.xlsx', engine='openpyxl')\n",
    "gemeinden_2021 = pd.read_excel(excel_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Regionalporträts 2021: Gemeinden - Kennzahlen</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 33</th>\n",
       "      <th>Unnamed: 34</th>\n",
       "      <th>Unnamed: 35</th>\n",
       "      <th>Unnamed: 36</th>\n",
       "      <th>Unnamed: 37</th>\n",
       "      <th>Unnamed: 38</th>\n",
       "      <th>Unnamed: 39</th>\n",
       "      <th>Unnamed: 40</th>\n",
       "      <th>Unnamed: 41</th>\n",
       "      <th>T21.3.1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bevölkerung</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Altersverteilung in %</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bevölkerungsbewegung (in ‰)</td>\n",
       "      <td>...</td>\n",
       "      <td>Wähleranteile ausgewählter Parteien in % (Nati...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gemeindecode</td>\n",
       "      <td>Gemeindename</td>\n",
       "      <td>Einwohner</td>\n",
       "      <td>Veränderung in %</td>\n",
       "      <td>Bevölkerungs-dichte pro km²</td>\n",
       "      <td>Ausländer in %</td>\n",
       "      <td>0-19 Jahre</td>\n",
       "      <td>20-64 Jahre</td>\n",
       "      <td>65 Jahre und mehr</td>\n",
       "      <td>Rohe Heiratssziffer</td>\n",
       "      <td>...</td>\n",
       "      <td>FDP 2)</td>\n",
       "      <td>CVP</td>\n",
       "      <td>SP</td>\n",
       "      <td>SVP</td>\n",
       "      <td>EVP/CSP</td>\n",
       "      <td>GLP</td>\n",
       "      <td>BDP</td>\n",
       "      <td>PdA/Sol.</td>\n",
       "      <td>GPS</td>\n",
       "      <td>Kleine Rechtsparteien</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019</td>\n",
       "      <td>2010-2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>...</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Schweiz</td>\n",
       "      <td>8606033</td>\n",
       "      <td>9.4</td>\n",
       "      <td>215.2</td>\n",
       "      <td>25.3</td>\n",
       "      <td>20</td>\n",
       "      <td>61.4</td>\n",
       "      <td>18.7</td>\n",
       "      <td>4.5</td>\n",
       "      <td>...</td>\n",
       "      <td>15.1</td>\n",
       "      <td>11.4</td>\n",
       "      <td>16.8</td>\n",
       "      <td>25.6</td>\n",
       "      <td>2.3</td>\n",
       "      <td>7.8</td>\n",
       "      <td>2.4</td>\n",
       "      <td>1</td>\n",
       "      <td>13.2</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1981</td>\n",
       "      <td>8.60746</td>\n",
       "      <td>250.442</td>\n",
       "      <td>14.1848</td>\n",
       "      <td>19.788</td>\n",
       "      <td>62.1908</td>\n",
       "      <td>18.0212</td>\n",
       "      <td>7.06535</td>\n",
       "      <td>...</td>\n",
       "      <td>16.421</td>\n",
       "      <td>2.49797</td>\n",
       "      <td>11.5651</td>\n",
       "      <td>30.381</td>\n",
       "      <td>3.22747</td>\n",
       "      <td>14.3173</td>\n",
       "      <td>1.26372</td>\n",
       "      <td>0.16211</td>\n",
       "      <td>15.0505</td>\n",
       "      <td>3.82065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>12303</td>\n",
       "      <td>10.9278</td>\n",
       "      <td>1161.76</td>\n",
       "      <td>28.7003</td>\n",
       "      <td>20.1658</td>\n",
       "      <td>61.6679</td>\n",
       "      <td>18.1663</td>\n",
       "      <td>5.54378</td>\n",
       "      <td>...</td>\n",
       "      <td>13.3854</td>\n",
       "      <td>4.53534</td>\n",
       "      <td>14.6453</td>\n",
       "      <td>30.3069</td>\n",
       "      <td>6.61856</td>\n",
       "      <td>12.6666</td>\n",
       "      <td>2.00145</td>\n",
       "      <td>0.198718</td>\n",
       "      <td>12.5906</td>\n",
       "      <td>1.55552</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Regionalporträts 2021: Gemeinden - Kennzahlen          Unnamed: 1  \\\n",
       "0                                           NaN                 NaN   \n",
       "1                                           NaN                 NaN   \n",
       "2                                           NaN                 NaN   \n",
       "3                                           NaN                 NaN   \n",
       "4                                  Gemeindecode        Gemeindename   \n",
       "5                                           NaN                 NaN   \n",
       "6                                           NaN                 NaN   \n",
       "7                                           NaN             Schweiz   \n",
       "8                                             1     Aeugst am Albis   \n",
       "9                                             2  Affoltern am Albis   \n",
       "\n",
       "    Unnamed: 2        Unnamed: 3                   Unnamed: 4      Unnamed: 5  \\\n",
       "0          NaN               NaN                          NaN             NaN   \n",
       "1          NaN               NaN                          NaN             NaN   \n",
       "2  Bevölkerung               NaN                          NaN             NaN   \n",
       "3          NaN               NaN                          NaN             NaN   \n",
       "4    Einwohner  Veränderung in %  Bevölkerungs-dichte pro km²  Ausländer in %   \n",
       "5         2019         2010-2019                         2019            2019   \n",
       "6          NaN               NaN                          NaN             NaN   \n",
       "7      8606033               9.4                        215.2            25.3   \n",
       "8         1981           8.60746                      250.442         14.1848   \n",
       "9        12303           10.9278                      1161.76         28.7003   \n",
       "\n",
       "              Unnamed: 6   Unnamed: 7         Unnamed: 8  \\\n",
       "0                    NaN          NaN                NaN   \n",
       "1                    NaN          NaN                NaN   \n",
       "2  Altersverteilung in %          NaN                NaN   \n",
       "3                    NaN          NaN                NaN   \n",
       "4             0-19 Jahre  20-64 Jahre  65 Jahre und mehr   \n",
       "5                   2019         2019               2019   \n",
       "6                    NaN          NaN                NaN   \n",
       "7                     20         61.4               18.7   \n",
       "8                 19.788      62.1908            18.0212   \n",
       "9                20.1658      61.6679            18.1663   \n",
       "\n",
       "                    Unnamed: 9  ...  \\\n",
       "0                          NaN  ...   \n",
       "1                          NaN  ...   \n",
       "2  Bevölkerungsbewegung (in ‰)  ...   \n",
       "3                          NaN  ...   \n",
       "4          Rohe Heiratssziffer  ...   \n",
       "5                         2019  ...   \n",
       "6                          NaN  ...   \n",
       "7                          4.5  ...   \n",
       "8                      7.06535  ...   \n",
       "9                      5.54378  ...   \n",
       "\n",
       "                                         Unnamed: 33 Unnamed: 34 Unnamed: 35  \\\n",
       "0                                                NaN         NaN         NaN   \n",
       "1                                                NaN         NaN         NaN   \n",
       "2  Wähleranteile ausgewählter Parteien in % (Nati...         NaN         NaN   \n",
       "3                                                NaN         NaN         NaN   \n",
       "4                                             FDP 2)         CVP          SP   \n",
       "5                                               2019        2019        2019   \n",
       "6                                                NaN         NaN         NaN   \n",
       "7                                               15.1        11.4        16.8   \n",
       "8                                             16.421     2.49797     11.5651   \n",
       "9                                            13.3854     4.53534     14.6453   \n",
       "\n",
       "  Unnamed: 36 Unnamed: 37 Unnamed: 38 Unnamed: 39 Unnamed: 40 Unnamed: 41  \\\n",
       "0         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "1         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "2         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "3         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "4         SVP     EVP/CSP         GLP         BDP    PdA/Sol.         GPS   \n",
       "5        2019        2019        2019        2019        2019        2019   \n",
       "6         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "7        25.6         2.3         7.8         2.4           1        13.2   \n",
       "8      30.381     3.22747     14.3173     1.26372     0.16211     15.0505   \n",
       "9     30.3069     6.61856     12.6666     2.00145    0.198718     12.5906   \n",
       "\n",
       "                 T21.3.1  \n",
       "0                    NaN  \n",
       "1                    NaN  \n",
       "2                    NaN  \n",
       "3                    NaN  \n",
       "4  Kleine Rechtsparteien  \n",
       "5                   2019  \n",
       "6                    NaN  \n",
       "7                    2.1  \n",
       "8                3.82065  \n",
       "9                1.55552  \n",
       "\n",
       "[10 rows x 43 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gemeinden_2021.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1) Löschen der NaN Zeilen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Werte aus Zeile 4 als Spaltenüberschrift einsetzen\n",
    "new_header = gemeinden_2021.iloc[4]\n",
    "gemeinden_2021.columns = new_header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Entfernen der leeren Zeilen vor der Tabelle und am Tabellenende\n",
    "gemeinden_2021 = gemeinden_2021.drop(labels=range(0,7), axis=0)\n",
    "gemeinden_2021 = gemeinden_2021.drop(labels=range(2180,2196), axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>4</th>\n",
       "      <th>Gemeindecode</th>\n",
       "      <th>Gemeindename</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>FDP 2)</th>\n",
       "      <th>CVP</th>\n",
       "      <th>SP</th>\n",
       "      <th>SVP</th>\n",
       "      <th>EVP/CSP</th>\n",
       "      <th>GLP</th>\n",
       "      <th>BDP</th>\n",
       "      <th>PdA/Sol.</th>\n",
       "      <th>GPS</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Schweiz</td>\n",
       "      <td>8606033</td>\n",
       "      <td>9.4</td>\n",
       "      <td>215.2</td>\n",
       "      <td>25.3</td>\n",
       "      <td>20</td>\n",
       "      <td>61.4</td>\n",
       "      <td>18.7</td>\n",
       "      <td>4.5</td>\n",
       "      <td>...</td>\n",
       "      <td>15.1</td>\n",
       "      <td>11.4</td>\n",
       "      <td>16.8</td>\n",
       "      <td>25.6</td>\n",
       "      <td>2.3</td>\n",
       "      <td>7.8</td>\n",
       "      <td>2.4</td>\n",
       "      <td>1</td>\n",
       "      <td>13.2</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1981</td>\n",
       "      <td>8.60746</td>\n",
       "      <td>250.442</td>\n",
       "      <td>14.1848</td>\n",
       "      <td>19.788</td>\n",
       "      <td>62.1908</td>\n",
       "      <td>18.0212</td>\n",
       "      <td>7.06535</td>\n",
       "      <td>...</td>\n",
       "      <td>16.421</td>\n",
       "      <td>2.49797</td>\n",
       "      <td>11.5651</td>\n",
       "      <td>30.381</td>\n",
       "      <td>3.22747</td>\n",
       "      <td>14.3173</td>\n",
       "      <td>1.26372</td>\n",
       "      <td>0.16211</td>\n",
       "      <td>15.0505</td>\n",
       "      <td>3.82065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>12303</td>\n",
       "      <td>10.9278</td>\n",
       "      <td>1161.76</td>\n",
       "      <td>28.7003</td>\n",
       "      <td>20.1658</td>\n",
       "      <td>61.6679</td>\n",
       "      <td>18.1663</td>\n",
       "      <td>5.54378</td>\n",
       "      <td>...</td>\n",
       "      <td>13.3854</td>\n",
       "      <td>4.53534</td>\n",
       "      <td>14.6453</td>\n",
       "      <td>30.3069</td>\n",
       "      <td>6.61856</td>\n",
       "      <td>12.6666</td>\n",
       "      <td>2.00145</td>\n",
       "      <td>0.198718</td>\n",
       "      <td>12.5906</td>\n",
       "      <td>1.55552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>Bonstetten</td>\n",
       "      <td>5572</td>\n",
       "      <td>8.00543</td>\n",
       "      <td>749.933</td>\n",
       "      <td>16.565</td>\n",
       "      <td>23.636</td>\n",
       "      <td>59.8349</td>\n",
       "      <td>16.5291</td>\n",
       "      <td>4.31655</td>\n",
       "      <td>...</td>\n",
       "      <td>15.355</td>\n",
       "      <td>3.86796</td>\n",
       "      <td>16.7429</td>\n",
       "      <td>26.1163</td>\n",
       "      <td>4.19402</td>\n",
       "      <td>16.2379</td>\n",
       "      <td>1.28403</td>\n",
       "      <td>0.176013</td>\n",
       "      <td>12.3685</td>\n",
       "      <td>1.38502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4</td>\n",
       "      <td>Hausen am Albis</td>\n",
       "      <td>3751</td>\n",
       "      <td>11.6369</td>\n",
       "      <td>275.809</td>\n",
       "      <td>16.0224</td>\n",
       "      <td>22.1808</td>\n",
       "      <td>59.824</td>\n",
       "      <td>17.9952</td>\n",
       "      <td>3.22061</td>\n",
       "      <td>...</td>\n",
       "      <td>11.1352</td>\n",
       "      <td>3.93455</td>\n",
       "      <td>13.7115</td>\n",
       "      <td>33.1848</td>\n",
       "      <td>4.05976</td>\n",
       "      <td>12.417</td>\n",
       "      <td>1.61075</td>\n",
       "      <td>0.19312</td>\n",
       "      <td>16.7441</td>\n",
       "      <td>1.61075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "4  Gemeindecode        Gemeindename Einwohner Veränderung in %  \\\n",
       "7           NaN             Schweiz   8606033              9.4   \n",
       "8             1     Aeugst am Albis      1981          8.60746   \n",
       "9             2  Affoltern am Albis     12303          10.9278   \n",
       "10            3          Bonstetten      5572          8.00543   \n",
       "11            4     Hausen am Albis      3751          11.6369   \n",
       "\n",
       "4  Bevölkerungs-dichte pro km² Ausländer in % 0-19 Jahre 20-64 Jahre  \\\n",
       "7                        215.2           25.3         20        61.4   \n",
       "8                      250.442        14.1848     19.788     62.1908   \n",
       "9                      1161.76        28.7003    20.1658     61.6679   \n",
       "10                     749.933         16.565     23.636     59.8349   \n",
       "11                     275.809        16.0224    22.1808      59.824   \n",
       "\n",
       "4  65 Jahre und mehr Rohe Heiratssziffer  ...   FDP 2)      CVP       SP  \\\n",
       "7               18.7                 4.5  ...     15.1     11.4     16.8   \n",
       "8            18.0212             7.06535  ...   16.421  2.49797  11.5651   \n",
       "9            18.1663             5.54378  ...  13.3854  4.53534  14.6453   \n",
       "10           16.5291             4.31655  ...   15.355  3.86796  16.7429   \n",
       "11           17.9952             3.22061  ...  11.1352  3.93455  13.7115   \n",
       "\n",
       "4       SVP  EVP/CSP      GLP      BDP  PdA/Sol.      GPS  \\\n",
       "7      25.6      2.3      7.8      2.4         1     13.2   \n",
       "8    30.381  3.22747  14.3173  1.26372   0.16211  15.0505   \n",
       "9   30.3069  6.61856  12.6666  2.00145  0.198718  12.5906   \n",
       "10  26.1163  4.19402  16.2379  1.28403  0.176013  12.3685   \n",
       "11  33.1848  4.05976   12.417  1.61075   0.19312  16.7441   \n",
       "\n",
       "4  Kleine Rechtsparteien  \n",
       "7                    2.1  \n",
       "8                3.82065  \n",
       "9                1.55552  \n",
       "10               1.38502  \n",
       "11               1.61075  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gemeinden_2021.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2) Nulls identifizieren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check dass keine Nulls im Dataframe\n",
    "gemeinden_2021.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1 NaN identifiziert. Da nur 1 NaN in der ganzen Tabelle wir der Wert CH als Füllwert eingesetzt da die Schweiz \n",
    "#keinen Gemeindecode besitzt.\n",
    "gemeinden_2021 = gemeinden_2021.fillna('CH') #CH löschen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check dass keine Nulls im Dataframe\n",
    "gemeinden_2021.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3) Import der Geodaten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Ortschaftsname</th>\n",
       "      <th>PLZ</th>\n",
       "      <th>Zusatzziffer</th>\n",
       "      <th>Gemeindename</th>\n",
       "      <th>BFS-Nr</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>E</th>\n",
       "      <th>N</th>\n",
       "      <th>Sprache</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>8914</td>\n",
       "      <td>0</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2679435.817</td>\n",
       "      <td>1235653.185</td>\n",
       "      <td>de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Aeugstertal</td>\n",
       "      <td>8914</td>\n",
       "      <td>2</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2679815.475</td>\n",
       "      <td>1237410.215</td>\n",
       "      <td>de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Zwillikon</td>\n",
       "      <td>8909</td>\n",
       "      <td>0</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>2</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2675104.519</td>\n",
       "      <td>1237890.488</td>\n",
       "      <td>de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>8910</td>\n",
       "      <td>0</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>2</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2676439.358</td>\n",
       "      <td>1236966.371</td>\n",
       "      <td>de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bonstetten</td>\n",
       "      <td>8906</td>\n",
       "      <td>0</td>\n",
       "      <td>Bonstetten</td>\n",
       "      <td>3</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2677798.571</td>\n",
       "      <td>1241024.843</td>\n",
       "      <td>de</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Ortschaftsname   PLZ  Zusatzziffer        Gemeindename  BFS-Nr  \\\n",
       "0     Aeugst am Albis  8914             0     Aeugst am Albis       1   \n",
       "1         Aeugstertal  8914             2     Aeugst am Albis       1   \n",
       "2           Zwillikon  8909             0  Affoltern am Albis       2   \n",
       "3  Affoltern am Albis  8910             0  Affoltern am Albis       2   \n",
       "4          Bonstetten  8906             0          Bonstetten       3   \n",
       "\n",
       "  Kantonskürzel            E            N Sprache  \n",
       "0            ZH  2679435.817  1235653.185      de  \n",
       "1            ZH  2679815.475  1237410.215      de  \n",
       "2            ZH  2675104.519  1237890.488      de  \n",
       "3            ZH  2676439.358  1236966.371      de  \n",
       "4            ZH  2677798.571  1241024.843      de  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#https://data.geo.admin.ch/ch.swisstopo-vd.ortschaftenverzeichnis_plz/PLZO_CSV_LV95.zip\n",
    "#Start Import der Geodaten\n",
    "geo = pd.read_csv('./Rohdaten/geodaten.csv', sep=';')\n",
    "geo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4126, 4)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#löschen der nicht benötigten columns\n",
    "geo = geo.drop(['Ortschaftsname','PLZ','Gemeindename','Zusatzziffer','Sprache'], axis=1)\n",
    "geo.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.4) Geodaten transformieren von LV95 -> WGS84"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Convert LV95 to WGS84\n",
    "inProj = Proj('epsg:2056')\n",
    "outProj = Proj('epsg:4326')\n",
    "x1,y1 = geo['E'], geo['N']\n",
    "x2,y2 = transform(inProj, outProj, x1,y1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo['WGS84_E'] = x2\n",
    "geo['WGS84_N'] = y2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Gemeindecode</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2679435.817</td>\n",
       "      <td>1235653.185</td>\n",
       "      <td>47.267004</td>\n",
       "      <td>8.488313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2679815.475</td>\n",
       "      <td>1237410.215</td>\n",
       "      <td>47.282761</td>\n",
       "      <td>8.493642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2675104.519</td>\n",
       "      <td>1237890.488</td>\n",
       "      <td>47.287633</td>\n",
       "      <td>8.431459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2676439.358</td>\n",
       "      <td>1236966.371</td>\n",
       "      <td>47.279169</td>\n",
       "      <td>8.448945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2677798.571</td>\n",
       "      <td>1241024.843</td>\n",
       "      <td>47.315510</td>\n",
       "      <td>8.467611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Gemeindecode Kantonskürzel       LV95_E       LV95_N    WGS84_E   WGS84_N\n",
       "0             1            ZH  2679435.817  1235653.185  47.267004  8.488313\n",
       "1             1            ZH  2679815.475  1237410.215  47.282761  8.493642\n",
       "2             2            ZH  2675104.519  1237890.488  47.287633  8.431459\n",
       "3             2            ZH  2676439.358  1236966.371  47.279169  8.448945\n",
       "4             3            ZH  2677798.571  1241024.843  47.315510  8.467611"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo = geo.rename(columns = {'E':'LV95_E', 'N':'LV95_N', 'BFS-Nr':'Gemeindecode'})\n",
    "geo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.5) Merge der Geodaten und Gemeindedaten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Gemeindecode</th>\n",
       "      <th>Gemeindename</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>GLP</th>\n",
       "      <th>BDP</th>\n",
       "      <th>PdA/Sol.</th>\n",
       "      <th>GPS</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CH</td>\n",
       "      <td>Schweiz</td>\n",
       "      <td>8606033</td>\n",
       "      <td>9.400000</td>\n",
       "      <td>215.200000</td>\n",
       "      <td>25.300000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>61.400000</td>\n",
       "      <td>18.700000</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.8</td>\n",
       "      <td>2.4</td>\n",
       "      <td>1</td>\n",
       "      <td>13.2</td>\n",
       "      <td>2.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1981</td>\n",
       "      <td>8.607456</td>\n",
       "      <td>250.442478</td>\n",
       "      <td>14.184755</td>\n",
       "      <td>19.787986</td>\n",
       "      <td>62.190813</td>\n",
       "      <td>18.021201</td>\n",
       "      <td>7.065355</td>\n",
       "      <td>...</td>\n",
       "      <td>14.3173</td>\n",
       "      <td>1.26372</td>\n",
       "      <td>0.16211</td>\n",
       "      <td>15.0505</td>\n",
       "      <td>3.82065</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2679435.817</td>\n",
       "      <td>1235653.185</td>\n",
       "      <td>47.267004</td>\n",
       "      <td>8.488313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1981</td>\n",
       "      <td>8.607456</td>\n",
       "      <td>250.442478</td>\n",
       "      <td>14.184755</td>\n",
       "      <td>19.787986</td>\n",
       "      <td>62.190813</td>\n",
       "      <td>18.021201</td>\n",
       "      <td>7.065355</td>\n",
       "      <td>...</td>\n",
       "      <td>14.3173</td>\n",
       "      <td>1.26372</td>\n",
       "      <td>0.16211</td>\n",
       "      <td>15.0505</td>\n",
       "      <td>3.82065</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2679815.475</td>\n",
       "      <td>1237410.215</td>\n",
       "      <td>47.282761</td>\n",
       "      <td>8.493642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>12303</td>\n",
       "      <td>10.927779</td>\n",
       "      <td>1161.756374</td>\n",
       "      <td>28.700317</td>\n",
       "      <td>20.165813</td>\n",
       "      <td>61.667886</td>\n",
       "      <td>18.166301</td>\n",
       "      <td>5.543780</td>\n",
       "      <td>...</td>\n",
       "      <td>12.6666</td>\n",
       "      <td>2.00145</td>\n",
       "      <td>0.198718</td>\n",
       "      <td>12.5906</td>\n",
       "      <td>1.55552</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2675104.519</td>\n",
       "      <td>1237890.488</td>\n",
       "      <td>47.287633</td>\n",
       "      <td>8.431459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>12303</td>\n",
       "      <td>10.927779</td>\n",
       "      <td>1161.756374</td>\n",
       "      <td>28.700317</td>\n",
       "      <td>20.165813</td>\n",
       "      <td>61.667886</td>\n",
       "      <td>18.166301</td>\n",
       "      <td>5.543780</td>\n",
       "      <td>...</td>\n",
       "      <td>12.6666</td>\n",
       "      <td>2.00145</td>\n",
       "      <td>0.198718</td>\n",
       "      <td>12.5906</td>\n",
       "      <td>1.55552</td>\n",
       "      <td>ZH</td>\n",
       "      <td>2676439.358</td>\n",
       "      <td>1236966.371</td>\n",
       "      <td>47.279169</td>\n",
       "      <td>8.448945</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Gemeindecode        Gemeindename  Einwohner  Veränderung in %  \\\n",
       "0           CH             Schweiz    8606033          9.400000   \n",
       "1            1     Aeugst am Albis       1981          8.607456   \n",
       "2            1     Aeugst am Albis       1981          8.607456   \n",
       "3            2  Affoltern am Albis      12303         10.927779   \n",
       "4            2  Affoltern am Albis      12303         10.927779   \n",
       "\n",
       "   Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "0                   215.200000       25.300000   20.000000    61.400000   \n",
       "1                   250.442478       14.184755   19.787986    62.190813   \n",
       "2                   250.442478       14.184755   19.787986    62.190813   \n",
       "3                  1161.756374       28.700317   20.165813    61.667886   \n",
       "4                  1161.756374       28.700317   20.165813    61.667886   \n",
       "\n",
       "   65 Jahre und mehr  Rohe Heiratssziffer  ...      GLP      BDP  PdA/Sol.  \\\n",
       "0          18.700000             4.500000  ...      7.8      2.4         1   \n",
       "1          18.021201             7.065355  ...  14.3173  1.26372   0.16211   \n",
       "2          18.021201             7.065355  ...  14.3173  1.26372   0.16211   \n",
       "3          18.166301             5.543780  ...  12.6666  2.00145  0.198718   \n",
       "4          18.166301             5.543780  ...  12.6666  2.00145  0.198718   \n",
       "\n",
       "       GPS  Kleine Rechtsparteien  Kantonskürzel       LV95_E       LV95_N  \\\n",
       "0     13.2                    2.1            NaN          NaN          NaN   \n",
       "1  15.0505                3.82065             ZH  2679435.817  1235653.185   \n",
       "2  15.0505                3.82065             ZH  2679815.475  1237410.215   \n",
       "3  12.5906                1.55552             ZH  2675104.519  1237890.488   \n",
       "4  12.5906                1.55552             ZH  2676439.358  1236966.371   \n",
       "\n",
       "     WGS84_E   WGS84_N  \n",
       "0        NaN       NaN  \n",
       "1  47.267004  8.488313  \n",
       "2  47.282761  8.493642  \n",
       "3  47.287633  8.431459  \n",
       "4  47.279169  8.448945  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Kombinierte Geodaten und Gemeindedaten basierend auf dem Gemeindecode. Da die Geodaten alle Ortschaften pro PLZ enthält,\n",
    "#werden in der Gemeindetabelle Duplikate generiert.\n",
    "data_merged = gemeinden_2021.merge(geo, how='left', on=['Gemeindecode'])\n",
    "data_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4114, 48)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Die durch den merge generierten Duplikate werden wieder gelöscht. Es soll pro politische Gemeinde nur eine Ortschaft\n",
    "#verwendet werden.\n",
    "data_merged = data_merged.drop_duplicates(subset=['Gemeindename'], keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2173, 48)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check ob Anzahl politische Gemeinden korrekt. Gemäss https://www.bfs.admin.ch/bfs/de/home/grundlagen/agvch.assetdetail.15144517.html\n",
    "#Gibt es 2172 politische Gemeinden in der Schweiz inkl. Daten auf Stufe CH sollte das Dataframe 2173 Zeilen beinhalten.\n",
    "data_merged.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.5) Bereinigung fehlender und nicht vollständiger Daten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gemeindecode                                      0\n",
       "Gemeindename                                      0\n",
       "Einwohner                                         0\n",
       "Veränderung in %                                  0\n",
       "Bevölkerungs-dichte pro km²                       0\n",
       "Ausländer in %                                    0\n",
       "0-19 Jahre                                        0\n",
       "20-64 Jahre                                       0\n",
       "65 Jahre und mehr                                 0\n",
       "Rohe Heiratssziffer                               0\n",
       "Rohe Scheidungsziffer                             0\n",
       "Rohe Geburtenziffer                               0\n",
       "Rohe Sterbeziffer                                 0\n",
       "Anzahl Privathaushalte                            0\n",
       "Durchschnittliche Haushaltsgrösse in Personen     0\n",
       "Gesamtfläche in km² 1)                            0\n",
       "Siedlungsfläche in %                              0\n",
       "Veränderung in ha                                 0\n",
       "Landwirtschafts-fläche in %                       0\n",
       "Veränderung in ha                                 0\n",
       "Wald und Gehölze in %                             0\n",
       "Unproduktive Fläche in %                          0\n",
       "Beschäftigte total                                0\n",
       "im 1. Sektor                                      0\n",
       "im 2. Sektor                                      0\n",
       "im 3. Sektor                                      0\n",
       "Arbeitsstätten total                              0\n",
       "im 1. Sektor                                      0\n",
       "im 2. Sektor                                      0\n",
       "im 3. Sektor                                      0\n",
       "Leerwohnungs-ziffer                               0\n",
       "Neu gebaute Wohnungen pro 1000 Einwohner          0\n",
       "Sozialhilfequote                                  0\n",
       "FDP 2)                                            0\n",
       "CVP                                               0\n",
       "SP                                                0\n",
       "SVP                                               0\n",
       "EVP/CSP                                           0\n",
       "GLP                                               0\n",
       "BDP                                               0\n",
       "PdA/Sol.                                          0\n",
       "GPS                                               0\n",
       "Kleine Rechtsparteien                             0\n",
       "Kantonskürzel                                    31\n",
       "LV95_E                                           31\n",
       "LV95_N                                           31\n",
       "WGS84_E                                          31\n",
       "WGS84_N                                          31\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check of in einzelnen Columns Nulls vorhanden sind.\n",
    "data_merged.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2142, 48)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Nach dem Merge gibt es einzelne Gemeinden für welche keine Keine Geodaten zur Verfügung stehen. Es handelt sich dabei\n",
    "#um sehr kleine Gemeinden die auch sonst kaum Daten zur Verfügung stellen. Dies werden entfernt.\n",
    "data_merged = data_merged.dropna()\n",
    "data_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Gemeindecode</th>\n",
       "      <th>Gemeindename</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>GLP</th>\n",
       "      <th>BDP</th>\n",
       "      <th>PdA/Sol.</th>\n",
       "      <th>GPS</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1587</th>\n",
       "      <td>2549</td>\n",
       "      <td>Kammersrohr</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>10.1852</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>*</td>\n",
       "      <td>7.40741</td>\n",
       "      <td>11.1111</td>\n",
       "      <td>SO</td>\n",
       "      <td>2611701.146</td>\n",
       "      <td>1233572.044</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3754</th>\n",
       "      <td>6172</td>\n",
       "      <td>Bister</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.28947</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>10.5263</td>\n",
       "      <td>*</td>\n",
       "      <td>VS</td>\n",
       "      <td>2648289.945</td>\n",
       "      <td>1134692.620</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>708</td>\n",
       "      <td>Schelten</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>...</td>\n",
       "      <td>5.88235</td>\n",
       "      <td>0</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>30.8824</td>\n",
       "      <td>0</td>\n",
       "      <td>BE</td>\n",
       "      <td>2608514.796</td>\n",
       "      <td>1242354.134</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>849</th>\n",
       "      <td>972</td>\n",
       "      <td>Berken</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.31548</td>\n",
       "      <td>7.29167</td>\n",
       "      <td>0</td>\n",
       "      <td>0.595238</td>\n",
       "      <td>0</td>\n",
       "      <td>BE</td>\n",
       "      <td>2620245.695</td>\n",
       "      <td>1230572.848</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>715</td>\n",
       "      <td>Rebévelier</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.320513</td>\n",
       "      <td>3.52564</td>\n",
       "      <td>8.49359</td>\n",
       "      <td>3.52564</td>\n",
       "      <td>BE</td>\n",
       "      <td>2581390.049</td>\n",
       "      <td>1237837.181</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Gemeindecode Gemeindename  Einwohner  Veränderung in %  \\\n",
       "1587         2549  Kammersrohr         32        -17.948718   \n",
       "3754         6172       Bister         33          0.000000   \n",
       "633           708     Schelten         35        -14.634146   \n",
       "849           972       Berken         41        -16.326531   \n",
       "638           715   Rebévelier         42        -10.638298   \n",
       "\n",
       "      Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "1587                    33.684211        3.125000   12.500000    62.500000   \n",
       "3754                     5.679862        6.060606    9.090909    60.606061   \n",
       "633                      6.294964        5.714286   17.142857    65.714286   \n",
       "849                     29.496403        2.439024    2.439024    65.853659   \n",
       "638                     11.830986        2.380952   26.190476    47.619048   \n",
       "\n",
       "      65 Jahre und mehr  Rohe Heiratssziffer  ...      GLP       BDP  \\\n",
       "1587          25.000000             0.000000  ...  10.1852  0.925926   \n",
       "3754          30.303030             0.000000  ...  3.28947         *   \n",
       "633           17.142857            28.169014  ...  5.88235         0   \n",
       "849           31.707317             0.000000  ...  4.31548   7.29167   \n",
       "638           26.190476            24.096386  ...        0  0.320513   \n",
       "\n",
       "      PdA/Sol.       GPS  Kleine Rechtsparteien  Kantonskürzel       LV95_E  \\\n",
       "1587         *   7.40741                11.1111             SO  2611701.146   \n",
       "3754         *   10.5263                      *             VS  2648289.945   \n",
       "633   0.490196   30.8824                      0             BE  2608514.796   \n",
       "849          0  0.595238                      0             BE  2620245.695   \n",
       "638    3.52564   8.49359                3.52564             BE  2581390.049   \n",
       "\n",
       "           LV95_N    WGS84_E   WGS84_N  \n",
       "1587  1233572.044  47.252957  7.593204  \n",
       "3754  1134692.620  46.361879  8.066106  \n",
       "633   1242354.134  47.331996  7.551276  \n",
       "849   1230572.848  47.225775  7.705946  \n",
       "638   1237837.181  47.291158  7.192605  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Dataframe anhand der Einwohnerzahl sortieren \"Aufsteigend\"\n",
    "data_merged = data_merged.sort_values(by='Einwohner', ascending=True)\n",
    "data_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gemeindecode                                     2142\n",
       "Gemeindename                                        0\n",
       "Einwohner                                        2142\n",
       "Veränderung in %                                 2142\n",
       "Bevölkerungs-dichte pro km²                      2142\n",
       "Ausländer in %                                   2142\n",
       "0-19 Jahre                                       2142\n",
       "20-64 Jahre                                      2142\n",
       "65 Jahre und mehr                                2142\n",
       "Rohe Heiratssziffer                              2142\n",
       "Rohe Scheidungsziffer                            2142\n",
       "Rohe Geburtenziffer                              2142\n",
       "Rohe Sterbeziffer                                2142\n",
       "Anzahl Privathaushalte                           2142\n",
       "Durchschnittliche Haushaltsgrösse in Personen    2142\n",
       "Gesamtfläche in km² 1)                           2142\n",
       "Siedlungsfläche in %                             2142\n",
       "Veränderung in ha                                2142\n",
       "Landwirtschafts-fläche in %                      2142\n",
       "Veränderung in ha                                2142\n",
       "Wald und Gehölze in %                            2142\n",
       "Unproduktive Fläche in %                         2142\n",
       "Beschäftigte total                               2048\n",
       "im 1. Sektor                                     2108\n",
       "im 2. Sektor                                     2085\n",
       "im 3. Sektor                                     2136\n",
       "Arbeitsstätten total                             1854\n",
       "im 1. Sektor                                     1996\n",
       "im 2. Sektor                                     1983\n",
       "im 3. Sektor                                     2122\n",
       "Leerwohnungs-ziffer                              2142\n",
       "Neu gebaute Wohnungen pro 1000 Einwohner         2142\n",
       "Sozialhilfequote                                 1671\n",
       "FDP 2)                                           2098\n",
       "CVP                                              2114\n",
       "SP                                               2106\n",
       "SVP                                              2134\n",
       "EVP/CSP                                          1836\n",
       "GLP                                              2018\n",
       "BDP                                              1618\n",
       "PdA/Sol.                                          971\n",
       "GPS                                              2074\n",
       "Kleine Rechtsparteien                            1641\n",
       "Kantonskürzel                                       0\n",
       "LV95_E                                           2142\n",
       "LV95_N                                           2142\n",
       "WGS84_E                                          2142\n",
       "WGS84_N                                          2142\n",
       "dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Damit statistische Auswertungen möglich sind, müssen sämtliche Columns bis auf Gemeindename und Kürzel nur Numerische Werte\n",
    "#Werte enthalten. Dies wird wie folgt überprüft\n",
    "data_merged.applymap(np.isreal).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Die übrigen nicht numerischen Werte der Statistischen Daten wurden im Datensatz mit einem * oder X gefüllt.\n",
    "#Diese Werte werden mit der ffill Methode überschrieben. Da der Datensatz zuvor anhand der Einwohnerzahl geordnet wurde,\n",
    "#werden deshalb bei Fehldaten die Werte der nächst kleineren Gemeinde kopiert. \n",
    "data_merged = data_merged.replace('*', np.nan).ffill()\n",
    "data_merged = data_merged.replace('X', np.nan).ffill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "48"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check Nulls\n",
    "data_merged.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Gemeindecode</th>\n",
       "      <th>Gemeindename</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>GLP</th>\n",
       "      <th>BDP</th>\n",
       "      <th>PdA/Sol.</th>\n",
       "      <th>GPS</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1587</th>\n",
       "      <td>2549</td>\n",
       "      <td>Kammersrohr</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>10.185185</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>7.407407</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>SO</td>\n",
       "      <td>2611701.146</td>\n",
       "      <td>1233572.044</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3754</th>\n",
       "      <td>6172</td>\n",
       "      <td>Bister</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.289474</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>10.526316</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>VS</td>\n",
       "      <td>2648289.945</td>\n",
       "      <td>1134692.620</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>708</td>\n",
       "      <td>Schelten</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>...</td>\n",
       "      <td>5.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>30.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2608514.796</td>\n",
       "      <td>1242354.134</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>849</th>\n",
       "      <td>972</td>\n",
       "      <td>Berken</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.315476</td>\n",
       "      <td>7.291667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.595238</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2620245.695</td>\n",
       "      <td>1230572.848</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>715</td>\n",
       "      <td>Rebévelier</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.320513</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>8.493590</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>BE</td>\n",
       "      <td>2581390.049</td>\n",
       "      <td>1237837.181</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Gemeindecode Gemeindename  Einwohner  Veränderung in %  \\\n",
       "1587          2549  Kammersrohr         32        -17.948718   \n",
       "3754          6172       Bister         33          0.000000   \n",
       "633            708     Schelten         35        -14.634146   \n",
       "849            972       Berken         41        -16.326531   \n",
       "638            715   Rebévelier         42        -10.638298   \n",
       "\n",
       "      Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "1587                    33.684211        3.125000   12.500000    62.500000   \n",
       "3754                     5.679862        6.060606    9.090909    60.606061   \n",
       "633                      6.294964        5.714286   17.142857    65.714286   \n",
       "849                     29.496403        2.439024    2.439024    65.853659   \n",
       "638                     11.830986        2.380952   26.190476    47.619048   \n",
       "\n",
       "      65 Jahre und mehr  Rohe Heiratssziffer  ...        GLP       BDP  \\\n",
       "1587          25.000000             0.000000  ...  10.185185  0.925926   \n",
       "3754          30.303030             0.000000  ...   3.289474  0.925926   \n",
       "633           17.142857            28.169014  ...   5.882353  0.000000   \n",
       "849           31.707317             0.000000  ...   4.315476  7.291667   \n",
       "638           26.190476            24.096386  ...   0.000000  0.320513   \n",
       "\n",
       "      PdA/Sol.        GPS  Kleine Rechtsparteien  Kantonskürzel       LV95_E  \\\n",
       "1587  0.490196   7.407407              11.111111             SO  2611701.146   \n",
       "3754  0.490196  10.526316              11.111111             VS  2648289.945   \n",
       "633   0.490196  30.882353               0.000000             BE  2608514.796   \n",
       "849   0.000000   0.595238               0.000000             BE  2620245.695   \n",
       "638   3.525641   8.493590               3.525641             BE  2581390.049   \n",
       "\n",
       "           LV95_N    WGS84_E   WGS84_N  \n",
       "1587  1233572.044  47.252957  7.593204  \n",
       "3754  1134692.620  46.361879  8.066106  \n",
       "633   1242354.134  47.331996  7.551276  \n",
       "849   1230572.848  47.225775  7.705946  \n",
       "638   1237837.181  47.291158  7.192605  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Für einzelne Columns fehlte auf der 1. Zeile ein Wert, weshalb durch ffill nicht alle Daten gefüllt werden konnten.\n",
    "#Dies wird durch bfill korrigiert.\n",
    "data_merged = data_merged.fillna(method = 'bfill')\n",
    "data_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check Nulls\n",
    "data_merged.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gemeindecode                                     2142\n",
       "Gemeindename                                        0\n",
       "Einwohner                                        2142\n",
       "Veränderung in %                                 2142\n",
       "Bevölkerungs-dichte pro km²                      2142\n",
       "Ausländer in %                                   2142\n",
       "0-19 Jahre                                       2142\n",
       "20-64 Jahre                                      2142\n",
       "65 Jahre und mehr                                2142\n",
       "Rohe Heiratssziffer                              2142\n",
       "Rohe Scheidungsziffer                            2142\n",
       "Rohe Geburtenziffer                              2142\n",
       "Rohe Sterbeziffer                                2142\n",
       "Anzahl Privathaushalte                           2142\n",
       "Durchschnittliche Haushaltsgrösse in Personen    2142\n",
       "Gesamtfläche in km² 1)                           2142\n",
       "Siedlungsfläche in %                             2142\n",
       "Veränderung in ha                                2142\n",
       "Landwirtschafts-fläche in %                      2142\n",
       "Veränderung in ha                                2142\n",
       "Wald und Gehölze in %                            2142\n",
       "Unproduktive Fläche in %                         2142\n",
       "Beschäftigte total                               2142\n",
       "im 1. Sektor                                     2142\n",
       "im 2. Sektor                                     2142\n",
       "im 3. Sektor                                     2142\n",
       "Arbeitsstätten total                             2142\n",
       "im 1. Sektor                                     2142\n",
       "im 2. Sektor                                     2142\n",
       "im 3. Sektor                                     2142\n",
       "Leerwohnungs-ziffer                              2142\n",
       "Neu gebaute Wohnungen pro 1000 Einwohner         2142\n",
       "Sozialhilfequote                                 2142\n",
       "FDP 2)                                           2142\n",
       "CVP                                              2142\n",
       "SP                                               2142\n",
       "SVP                                              2142\n",
       "EVP/CSP                                          2142\n",
       "GLP                                              2142\n",
       "BDP                                              2142\n",
       "PdA/Sol.                                         2142\n",
       "GPS                                              2142\n",
       "Kleine Rechtsparteien                            2142\n",
       "Kantonskürzel                                       0\n",
       "LV95_E                                           2142\n",
       "LV95_N                                           2142\n",
       "WGS84_E                                          2142\n",
       "WGS84_N                                          2142\n",
       "dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check, dass bis auf Gemeindename und Kantonskürzel alle columns numerische Werte ausweisen.\n",
    "data_merged.applymap(np.isreal).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Gemeindecode</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>Rohe Scheidungsziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>GLP</th>\n",
       "      <th>BDP</th>\n",
       "      <th>PdA/Sol.</th>\n",
       "      <th>GPS</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gemeindename</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Kammersrohr</th>\n",
       "      <td>2549</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.185185</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>7.407407</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>SO</td>\n",
       "      <td>2611701.146</td>\n",
       "      <td>1233572.044</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bister</th>\n",
       "      <td>6172</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.289474</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>10.526316</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>VS</td>\n",
       "      <td>2648289.945</td>\n",
       "      <td>1134692.620</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schelten</th>\n",
       "      <td>708</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>30.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2608514.796</td>\n",
       "      <td>1242354.134</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Berken</th>\n",
       "      <td>972</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.315476</td>\n",
       "      <td>7.291667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.595238</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2620245.695</td>\n",
       "      <td>1230572.848</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rebévelier</th>\n",
       "      <td>715</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.320513</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>8.493590</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>BE</td>\n",
       "      <td>2581390.049</td>\n",
       "      <td>1237837.181</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Gemeindecode  Einwohner  Veränderung in %  \\\n",
       "Gemeindename                                              \n",
       "Kammersrohr           2549         32        -17.948718   \n",
       "Bister                6172         33          0.000000   \n",
       "Schelten               708         35        -14.634146   \n",
       "Berken                 972         41        -16.326531   \n",
       "Rebévelier             715         42        -10.638298   \n",
       "\n",
       "              Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  \\\n",
       "Gemeindename                                                            \n",
       "Kammersrohr                     33.684211        3.125000   12.500000   \n",
       "Bister                           5.679862        6.060606    9.090909   \n",
       "Schelten                         6.294964        5.714286   17.142857   \n",
       "Berken                          29.496403        2.439024    2.439024   \n",
       "Rebévelier                      11.830986        2.380952   26.190476   \n",
       "\n",
       "              20-64 Jahre  65 Jahre und mehr  Rohe Heiratssziffer  \\\n",
       "Gemeindename                                                        \n",
       "Kammersrohr     62.500000          25.000000             0.000000   \n",
       "Bister          60.606061          30.303030             0.000000   \n",
       "Schelten        65.714286          17.142857            28.169014   \n",
       "Berken          65.853659          31.707317             0.000000   \n",
       "Rebévelier      47.619048          26.190476            24.096386   \n",
       "\n",
       "              Rohe Scheidungsziffer  ...        GLP       BDP  PdA/Sol.  \\\n",
       "Gemeindename                         ...                                  \n",
       "Kammersrohr                     0.0  ...  10.185185  0.925926  0.490196   \n",
       "Bister                          0.0  ...   3.289474  0.925926  0.490196   \n",
       "Schelten                        0.0  ...   5.882353  0.000000  0.490196   \n",
       "Berken                          0.0  ...   4.315476  7.291667  0.000000   \n",
       "Rebévelier                      0.0  ...   0.000000  0.320513  3.525641   \n",
       "\n",
       "                    GPS  Kleine Rechtsparteien  Kantonskürzel       LV95_E  \\\n",
       "Gemeindename                                                                 \n",
       "Kammersrohr    7.407407              11.111111             SO  2611701.146   \n",
       "Bister        10.526316              11.111111             VS  2648289.945   \n",
       "Schelten      30.882353               0.000000             BE  2608514.796   \n",
       "Berken         0.595238               0.000000             BE  2620245.695   \n",
       "Rebévelier     8.493590               3.525641             BE  2581390.049   \n",
       "\n",
       "                   LV95_N    WGS84_E   WGS84_N  \n",
       "Gemeindename                                    \n",
       "Kammersrohr   1233572.044  47.252957  7.593204  \n",
       "Bister        1134692.620  46.361879  8.066106  \n",
       "Schelten      1242354.134  47.331996  7.551276  \n",
       "Berken        1230572.848  47.225775  7.705946  \n",
       "Rebévelier    1237837.181  47.291158  7.192605  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Gemeindename als Index einsetzen\n",
    "data_merged = data_merged.set_index('Gemeindename')\n",
    "data_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Zwischenspeichern der Daten\n",
    "data_merged.to_csv('./Daten/Gemeinde inkl. geo.csv')\n",
    "data_merged.to_excel('./Daten/Gemeinde inkl. geo.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.6) Verarbeitung des Shapefiles für Choropleths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Verarbeiten der Kantonsgrenzen der Schweiz\n",
    "\n",
    "#URL der Daten: https://www.swisstopo.admin.ch/de/geodata/landscape/boundaries3d.html\n",
    "\n",
    "#https://stackoverflow.com/questions/43119040/shapefile-into-geojson-conversion-python-3\n",
    "shp_file = geopandas.read_file('./Rohdaten/kanton.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Umwandlung LV95 -> WGS84\n",
    "shp_file= shp_file.set_crs(2056, allow_override=True)\n",
    "shp_file= shp_file.to_crs(4326)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Speichern des JSON files\n",
    "shp_file.to_file('./Daten/Kantonsgrenzen.geojson', driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2) Anreichern der Daten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Einlesen des Zwischengespeicherten Datensatzes\n",
    "df_gemeinden = pd.read_csv('./Daten/Gemeinde inkl. geo.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Gemeindename</th>\n",
       "      <th>Gemeindecode</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>GLP</th>\n",
       "      <th>BDP</th>\n",
       "      <th>PdA/Sol.</th>\n",
       "      <th>GPS</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kammersrohr</td>\n",
       "      <td>2549</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>10.185185</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>7.407407</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>SO</td>\n",
       "      <td>2611701.146</td>\n",
       "      <td>1233572.044</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bister</td>\n",
       "      <td>6172</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.289474</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>10.526316</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>VS</td>\n",
       "      <td>2648289.945</td>\n",
       "      <td>1134692.620</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schelten</td>\n",
       "      <td>708</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>...</td>\n",
       "      <td>5.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.490196</td>\n",
       "      <td>30.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2608514.796</td>\n",
       "      <td>1242354.134</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Berken</td>\n",
       "      <td>972</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.315476</td>\n",
       "      <td>7.291667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.595238</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2620245.695</td>\n",
       "      <td>1230572.848</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rebévelier</td>\n",
       "      <td>715</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.320513</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>8.493590</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>BE</td>\n",
       "      <td>2581390.049</td>\n",
       "      <td>1237837.181</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Gemeindename  Gemeindecode  Einwohner  Veränderung in %  \\\n",
       "0  Kammersrohr          2549         32        -17.948718   \n",
       "1       Bister          6172         33          0.000000   \n",
       "2     Schelten           708         35        -14.634146   \n",
       "3       Berken           972         41        -16.326531   \n",
       "4   Rebévelier           715         42        -10.638298   \n",
       "\n",
       "   Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "0                    33.684211        3.125000   12.500000    62.500000   \n",
       "1                     5.679862        6.060606    9.090909    60.606061   \n",
       "2                     6.294964        5.714286   17.142857    65.714286   \n",
       "3                    29.496403        2.439024    2.439024    65.853659   \n",
       "4                    11.830986        2.380952   26.190476    47.619048   \n",
       "\n",
       "   65 Jahre und mehr  Rohe Heiratssziffer  ...        GLP       BDP  PdA/Sol.  \\\n",
       "0          25.000000             0.000000  ...  10.185185  0.925926  0.490196   \n",
       "1          30.303030             0.000000  ...   3.289474  0.925926  0.490196   \n",
       "2          17.142857            28.169014  ...   5.882353  0.000000  0.490196   \n",
       "3          31.707317             0.000000  ...   4.315476  7.291667  0.000000   \n",
       "4          26.190476            24.096386  ...   0.000000  0.320513  3.525641   \n",
       "\n",
       "         GPS  Kleine Rechtsparteien  Kantonskürzel       LV95_E       LV95_N  \\\n",
       "0   7.407407              11.111111             SO  2611701.146  1233572.044   \n",
       "1  10.526316              11.111111             VS  2648289.945  1134692.620   \n",
       "2  30.882353               0.000000             BE  2608514.796  1242354.134   \n",
       "3   0.595238               0.000000             BE  2620245.695  1230572.848   \n",
       "4   8.493590               3.525641             BE  2581390.049  1237837.181   \n",
       "\n",
       "     WGS84_E   WGS84_N  \n",
       "0  47.252957  7.593204  \n",
       "1  46.361879  8.066106  \n",
       "2  47.331996  7.551276  \n",
       "3  47.225775  7.705946  \n",
       "4  47.291158  7.192605  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gemeinden.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1) Politische Kategorisierung einfügen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Einfügen der Politischen Kategorisierung nach dem Schema Links - Mitte - Rechts.\n",
    "#Grundlage gemäss Visualisierung: https://ch-info.swiss/edition-2020/direkte-demokratie/parteien-von-links-nach-rechts\n",
    "df_gemeinden['Linksparteien'] = df_gemeinden['SP']+df_gemeinden['PdA/Sol.']+df_gemeinden['GPS']\n",
    "df_gemeinden['Mitteparteien'] = df_gemeinden['CVP']+df_gemeinden['EVP/CSP']+df_gemeinden['GLP']+df_gemeinden['BDP']\n",
    "df_gemeinden['Rechtsparteien'] = df_gemeinden['FDP 2)']+df_gemeinden['SVP']+df_gemeinden['Kleine Rechtsparteien']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2) Ersetzen Kantonskürzel durch Kantonsname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "kanton_lookup = {\n",
    "    'AG':'Aargau',\n",
    "    'AI':'Appenzell Innerrhoden',\n",
    "    'AR':'Appenzell Ausserrhoden',\n",
    "    'BE':'Bern',\n",
    "    'BL':'Basel-Landschaft',\n",
    "    'BS':'Basel-Stadt',\n",
    "    'FR':'Fribourg',\n",
    "    'GE':'Genève',\n",
    "    'GL':'Glarus',\n",
    "    'GR':'Graubünden',\n",
    "    'JU':'Jura',\n",
    "    'LU':'Luzern',\n",
    "    'NE':'Neuchâtel',\n",
    "    'NW':'Nidwalden',\n",
    "    'OW':'Obwalden',\n",
    "    'SG':'St. Gallen',\n",
    "    'SH':'Schaffhausen',\n",
    "    'SO':'Solothurn',\n",
    "    'SZ':'Schwyz',\n",
    "    'TG':'Thurgau',\n",
    "    'TI':'Ticino',\n",
    "    'UR':'Uri',\n",
    "    'VD':'Vaud',\n",
    "    'VS':'Valais',\n",
    "    'ZG':'Zug',\n",
    "    'ZH':'Zürich',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(kanton_lookup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Gemeindename</th>\n",
       "      <th>Gemeindecode</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>Kleine Rechtsparteien</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "      <th>Linksparteien</th>\n",
       "      <th>Mitteparteien</th>\n",
       "      <th>Rechtsparteien</th>\n",
       "      <th>Kantonsname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kammersrohr</td>\n",
       "      <td>2549</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>SO</td>\n",
       "      <td>2611701.146</td>\n",
       "      <td>1233572.044</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "      <td>18.082789</td>\n",
       "      <td>35.185185</td>\n",
       "      <td>45.370370</td>\n",
       "      <td>Solothurn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bister</td>\n",
       "      <td>6172</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>11.111111</td>\n",
       "      <td>VS</td>\n",
       "      <td>2648289.945</td>\n",
       "      <td>1134692.620</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "      <td>25.490196</td>\n",
       "      <td>48.952242</td>\n",
       "      <td>38.084795</td>\n",
       "      <td>Valais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schelten</td>\n",
       "      <td>708</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2608514.796</td>\n",
       "      <td>1242354.134</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "      <td>37.990196</td>\n",
       "      <td>17.647059</td>\n",
       "      <td>35.784314</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Berken</td>\n",
       "      <td>972</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>BE</td>\n",
       "      <td>2620245.695</td>\n",
       "      <td>1230572.848</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "      <td>4.464286</td>\n",
       "      <td>11.607143</td>\n",
       "      <td>80.803571</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rebévelier</td>\n",
       "      <td>715</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>...</td>\n",
       "      <td>3.525641</td>\n",
       "      <td>BE</td>\n",
       "      <td>2581390.049</td>\n",
       "      <td>1237837.181</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "      <td>17.467949</td>\n",
       "      <td>15.384615</td>\n",
       "      <td>50.801282</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Gemeindename  Gemeindecode  Einwohner  Veränderung in %  \\\n",
       "0  Kammersrohr          2549         32        -17.948718   \n",
       "1       Bister          6172         33          0.000000   \n",
       "2     Schelten           708         35        -14.634146   \n",
       "3       Berken           972         41        -16.326531   \n",
       "4   Rebévelier           715         42        -10.638298   \n",
       "\n",
       "   Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "0                    33.684211        3.125000   12.500000    62.500000   \n",
       "1                     5.679862        6.060606    9.090909    60.606061   \n",
       "2                     6.294964        5.714286   17.142857    65.714286   \n",
       "3                    29.496403        2.439024    2.439024    65.853659   \n",
       "4                    11.830986        2.380952   26.190476    47.619048   \n",
       "\n",
       "   65 Jahre und mehr  Rohe Heiratssziffer  ...  Kleine Rechtsparteien  \\\n",
       "0          25.000000             0.000000  ...              11.111111   \n",
       "1          30.303030             0.000000  ...              11.111111   \n",
       "2          17.142857            28.169014  ...               0.000000   \n",
       "3          31.707317             0.000000  ...               0.000000   \n",
       "4          26.190476            24.096386  ...               3.525641   \n",
       "\n",
       "   Kantonskürzel       LV95_E       LV95_N    WGS84_E   WGS84_N  \\\n",
       "0             SO  2611701.146  1233572.044  47.252957  7.593204   \n",
       "1             VS  2648289.945  1134692.620  46.361879  8.066106   \n",
       "2             BE  2608514.796  1242354.134  47.331996  7.551276   \n",
       "3             BE  2620245.695  1230572.848  47.225775  7.705946   \n",
       "4             BE  2581390.049  1237837.181  47.291158  7.192605   \n",
       "\n",
       "   Linksparteien  Mitteparteien  Rechtsparteien  Kantonsname  \n",
       "0      18.082789      35.185185       45.370370    Solothurn  \n",
       "1      25.490196      48.952242       38.084795       Valais  \n",
       "2      37.990196      17.647059       35.784314         Bern  \n",
       "3       4.464286      11.607143       80.803571         Bern  \n",
       "4      17.467949      15.384615       50.801282         Bern  \n",
       "\n",
       "[5 rows x 52 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Duplikat des Kantontskürzels erstellen\n",
    "df_gemeinden['Kantonskürzel2'] = df_gemeinden['Kantonskürzel']\n",
    "#Katnonskürzel2 wird durch Kantonsname ersetzt. Wird später bei Choropleths benötigt\n",
    "df_gemeinden = df_gemeinden.replace({'Kantonskürzel2':kanton_lookup})\n",
    "df_gemeinden = df_gemeinden.rename(columns = {'Kantonskürzel2':'Kantonsname'})\n",
    "df_gemeinden.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Erstellen eines Dictionary der Regionen pro Kanton\n",
    "region_dict =\t{\n",
    "  \"Ostschweiz\": [\"SG\", \"TG\", \"SH\", \"AR\", \"AI\", \"GL\", \"GR\"],\n",
    "  \"Genferseeregion\": [\"GE\", \"VD\", \"VS\"],\n",
    "  \"EspaceMittelland\": [\"BE\", \"FR\", \"SO\", \"NE\", \"JU\"],\n",
    "  \"Nordwestschweiz\": [\"BL\", \"BS\", \"AG\"],\n",
    "  \"Zürich\" : [\"ZH\"],\n",
    "  \"Zentralschweiz\" : [\"LU\", \"UR\", \"SZ\", \"NW\", \"OW\", \"ZG\"],\n",
    "  \"Tessin\": [\"TI\"]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AG': 'Nordwestschweiz',\n",
       " 'AI': 'Ostschweiz',\n",
       " 'AR': 'Ostschweiz',\n",
       " 'BE': 'EspaceMittelland',\n",
       " 'BL': 'Nordwestschweiz',\n",
       " 'BS': 'Nordwestschweiz',\n",
       " 'FR': 'EspaceMittelland',\n",
       " 'GE': 'Genferseeregion',\n",
       " 'GL': 'Ostschweiz',\n",
       " 'GR': 'Ostschweiz',\n",
       " 'JU': 'EspaceMittelland',\n",
       " 'LU': 'Zentralschweiz',\n",
       " 'NE': 'EspaceMittelland',\n",
       " 'NW': 'Zentralschweiz',\n",
       " 'OW': 'Zentralschweiz',\n",
       " 'SG': 'Ostschweiz',\n",
       " 'SH': 'Ostschweiz',\n",
       " 'SO': 'EspaceMittelland',\n",
       " 'SZ': 'Zentralschweiz',\n",
       " 'TG': 'Ostschweiz',\n",
       " 'TI': 'Tessin',\n",
       " 'UR': 'Zentralschweiz',\n",
       " 'VD': 'Genferseeregion',\n",
       " 'VS': 'Genferseeregion',\n",
       " 'ZG': 'Zentralschweiz',\n",
       " 'ZH': 'Zürich'}"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Key Value change des Dict region_dict\n",
    "kanton_region= dict()\n",
    "for region in region_dict.items():\n",
    "    for kantone in region[1:]:\n",
    "        for kanton in kantone:\n",
    "            kanton_region[kanton] = region[0]\n",
    "\n",
    "\n",
    "kanton_region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Gemeindename</th>\n",
       "      <th>Gemeindecode</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>Kantonskürzel</th>\n",
       "      <th>LV95_E</th>\n",
       "      <th>LV95_N</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "      <th>Linksparteien</th>\n",
       "      <th>Mitteparteien</th>\n",
       "      <th>Rechtsparteien</th>\n",
       "      <th>Kantonsname</th>\n",
       "      <th>Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kammersrohr</td>\n",
       "      <td>2549</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>SO</td>\n",
       "      <td>2611701.146</td>\n",
       "      <td>1233572.044</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "      <td>18.082789</td>\n",
       "      <td>35.185185</td>\n",
       "      <td>45.370370</td>\n",
       "      <td>Solothurn</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bister</td>\n",
       "      <td>6172</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>VS</td>\n",
       "      <td>2648289.945</td>\n",
       "      <td>1134692.620</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "      <td>25.490196</td>\n",
       "      <td>48.952242</td>\n",
       "      <td>38.084795</td>\n",
       "      <td>Valais</td>\n",
       "      <td>Genferseeregion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schelten</td>\n",
       "      <td>708</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>...</td>\n",
       "      <td>BE</td>\n",
       "      <td>2608514.796</td>\n",
       "      <td>1242354.134</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "      <td>37.990196</td>\n",
       "      <td>17.647059</td>\n",
       "      <td>35.784314</td>\n",
       "      <td>Bern</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Berken</td>\n",
       "      <td>972</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>BE</td>\n",
       "      <td>2620245.695</td>\n",
       "      <td>1230572.848</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "      <td>4.464286</td>\n",
       "      <td>11.607143</td>\n",
       "      <td>80.803571</td>\n",
       "      <td>Bern</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rebévelier</td>\n",
       "      <td>715</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>...</td>\n",
       "      <td>BE</td>\n",
       "      <td>2581390.049</td>\n",
       "      <td>1237837.181</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "      <td>17.467949</td>\n",
       "      <td>15.384615</td>\n",
       "      <td>50.801282</td>\n",
       "      <td>Bern</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Gemeindename  Gemeindecode  Einwohner  Veränderung in %  \\\n",
       "0  Kammersrohr          2549         32        -17.948718   \n",
       "1       Bister          6172         33          0.000000   \n",
       "2     Schelten           708         35        -14.634146   \n",
       "3       Berken           972         41        -16.326531   \n",
       "4   Rebévelier           715         42        -10.638298   \n",
       "\n",
       "   Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "0                    33.684211        3.125000   12.500000    62.500000   \n",
       "1                     5.679862        6.060606    9.090909    60.606061   \n",
       "2                     6.294964        5.714286   17.142857    65.714286   \n",
       "3                    29.496403        2.439024    2.439024    65.853659   \n",
       "4                    11.830986        2.380952   26.190476    47.619048   \n",
       "\n",
       "   65 Jahre und mehr  Rohe Heiratssziffer  ...  Kantonskürzel       LV95_E  \\\n",
       "0          25.000000             0.000000  ...             SO  2611701.146   \n",
       "1          30.303030             0.000000  ...             VS  2648289.945   \n",
       "2          17.142857            28.169014  ...             BE  2608514.796   \n",
       "3          31.707317             0.000000  ...             BE  2620245.695   \n",
       "4          26.190476            24.096386  ...             BE  2581390.049   \n",
       "\n",
       "        LV95_N    WGS84_E   WGS84_N  Linksparteien  Mitteparteien  \\\n",
       "0  1233572.044  47.252957  7.593204      18.082789      35.185185   \n",
       "1  1134692.620  46.361879  8.066106      25.490196      48.952242   \n",
       "2  1242354.134  47.331996  7.551276      37.990196      17.647059   \n",
       "3  1230572.848  47.225775  7.705946       4.464286      11.607143   \n",
       "4  1237837.181  47.291158  7.192605      17.467949      15.384615   \n",
       "\n",
       "   Rechtsparteien  Kantonsname            Region  \n",
       "0       45.370370    Solothurn  EspaceMittelland  \n",
       "1       38.084795       Valais   Genferseeregion  \n",
       "2       35.784314         Bern  EspaceMittelland  \n",
       "3       80.803571         Bern  EspaceMittelland  \n",
       "4       50.801282         Bern  EspaceMittelland  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Erstellen der Column Region durch die definierte Region\n",
    "df_gemeinden['Region'] = df_gemeinden['Kantonskürzel']\n",
    "df_gemeinden = df_gemeinden.replace({'Region':kanton_region})\n",
    "df_gemeinden = df_gemeinden.rename(columns = {'Region':'Region'})\n",
    "df_gemeinden.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Der Altersquotient ist eine wichtige demographische Kennziffer, bei der die Anzahl der älteren Menschen zur Anzahl der erwerbsfähigen Menschen gesetzt wird. Zur Berechnung können verschiedene Altersgrenzen gezogen werden. Beispielsweise können alle Einwohner über 65 Jahre als Ältere und Personen zwischen 15 und 64 Jahren als erwerbsfähig eingestuft werden. Mit dem Altersquotient können Rückschlüsse auf das Rentner-Erwerbsverhältnis gezogen werden. Dem Altersquotient steht der Jugendquotient gegenüber, bei dem die Anzahl der Kinder und Jugendlichen zur Anzahl der Erwerbsfähigen ins Verhältnis gesetzt wird. Werden Altersquotient und Jugendquotient addiert, ergibt sich Gesamtlastquotient. Aus dieser Zahl können Belastungen für die Sozialsysteme abgeleitet werden. Für den Altersquotient gibt es auch die Begriffe Alterslastquote oder Englisch: aging quotient."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Demografische Kennzahlen: https://de.wikipedia.org/wiki/Abh%C3%A4ngigenquotient\n",
    "Aufgrund der vorliegende Zahlen wurden die Definition von Wikipedia angepasst - \n",
    "\n",
    "\n",
    "#Abhängigenquotient\n",
    "\n",
    "Der Abhängigenquotient ist ein Begriff aus der Demografie. Er gibt das Verhältnis der Anzahl von Personen, die nicht im Erwerbstätigenalter sind, zur Anzahl von Personen im Erwerbstätigenalter in einer Gesellschaft an.\n",
    "\n",
    "Der Abhängigenquotient ergibt sich aus zwei Komponenten. Der Altenquotient gibt das Verhältnis der Anzahl älterer Menschen zur Anzahl jüngerer Menschen in einer Gesellschaft an und wird üblicherweise genauer als das Verhältnis der Anzahl von Personen, die nicht mehr im Erwerbstätigenalter sind, zur Anzahl von Personen im Erwerbstätigenalter definiert. Weitgehend synonym zum Begriff „Altenquotient“ werden die Begriffe „Altersquotient“, „Belastungsquotient“ oder auch „Altenlastquote“ verwendet. Der Jugendquotient stellt die zweite Komponente dar und gibt entsprechend das Verhältnis der Anzahl von Personen, die noch nicht im Erwerbstätigenalter sind, zur Anzahl von Personen im Erwerbstätigenalter an.\n",
    "\n",
    "Diese Quotienten beziehen sich ausschließlich auf die Altersgrenzen und lassen Fragen einer tatsächlichen Erwerbstätigkeit und Erwerbsfähigkeit der Personen außer Betracht.\n",
    "Inhaltsverzeichnis\n",
    "\n",
    "    1 Berechnung\n",
    "    2 Aktuelle Daten\n",
    "    3 Kritik\n",
    "    4 Einzelnachweise\n",
    "\n",
    "Berechnung\n",
    "\n",
    "Der Altenquotient gibt das statistische Verhältnis der Menschen, die (meist ab 65 bzw. 60 Jahre) nicht mehr im erwerbsfähigen Alter sind, zu jenen im erwerbsfähigen Alter (meist ab 15 bzw. 20 Jahre bis 64 bzw. 59 Jahre) an. Er bezieht sich dabei immer auf eine bestimmte Bevölkerung zu einem bestimmten Zeitpunkt. Üblich ist die Berechnung eines Altenquotienten aus der Zahl von Personen im Alter ab 65 Jahren bezogen auf die Zahl der 15- bis 64-Jährigen (also bezogen auf die Zahl der Personen im typischen Erwerbsalter). Eine abweichende Wahl von Altersgrenzen ist jedoch gleichfalls möglich. Die verwendeten Grenzen sollten entsprechend immer kenntlich gemacht werden.\n",
    "\n",
    "Der Jugendquotient gibt entsprechend üblicherweise die Zahl der unter 15-Jährigen bezogen auf die Zahl der 15- bis 64-Jährigen an.\n",
    "\n",
    "Der Abhängigenquotient gibt die Zahl derjeniger, die unter 15 oder über 64 Jahre alt sind, wieder auf die Zahl der 15- bis 64-Jährigen bezogen. Er ergibt sich daher als Summe aus dem Alten- und dem Jugendquotient.\n",
    "\n",
    "    Altenquotient = Bevölkerung ab 65 Jahre Bevölkerung 15–64 Jahre {\\displaystyle {\\text{Altenquotient}}={\\frac {\\text{Bevölkerung ab 65 Jahre}}{\\text{Bevölkerung 15–64 Jahre}}}} {\\displaystyle {\\text{Altenquotient}}={\\frac {\\text{Bevölkerung ab 65 Jahre}}{\\text{Bevölkerung 15–64 Jahre}}}}\n",
    "\n",
    "    Jugendquotient = Bevölkerung 0–14 Jahre Bevölkerung 15–64 Jahre {\\displaystyle {\\text{Jugendquotient}}={\\frac {\\text{Bevölkerung 0–14 Jahre}}{\\text{Bevölkerung 15–64 Jahre}}}} {\\displaystyle {\\text{Jugendquotient}}={\\frac {\\text{Bevölkerung 0–14 Jahre}}{\\text{Bevölkerung 15–64 Jahre}}}}\n",
    "\n",
    "    Abhängigenquotient = Bevölkerung 0–14 Jahre oder ab 65 Jahre Bevölkerung 15–64 Jahre = Altenquotient + Jugendquotient {\\displaystyle {\\text{Abhängigenquotient}}={\\frac {\\text{Bevölkerung 0–14 Jahre oder ab 65 Jahre}}{\\text{Bevölkerung 15–64 Jahre}}}={\\text{Altenquotient}}+{\\text{Jugendquotient}}} {\\displaystyle {\\text{Abhängigenquotient}}={\\frac {\\text{Bevölkerung 0–14 Jahre oder ab 65 Jahre}}{\\text{Bevölkerung 15–64 Jahre}}}={\\text{Altenquotient}}+{\\text{Jugendquotient}}}\n",
    "\n",
    "Der errechnete Quotient kann jeweils als Dezimalzahl mit Nachkommastellen angegeben werden (z. B. durch die Angabe eines Altenquotienten 0,20 bei einer Relation von 1 zu 5). Üblich ist eine Multiplikation des Wertes mit dem Faktor 100 (im genannten Beispiel würde entsprechend der Altenquotient 20 im Sinne von 20 Älteren je 100 Jüngere resultieren).\n",
    "\n",
    "Bisweilen findet sich auch eine Angabe als Prozentwert. Eine Angabe als Prozentwert ist allerdings streng genommen verkehrt und sollte vermieden werden, da der Quotient immer eine Ratio bzw. ein Größenverhältnis von zwei unterschiedlichen Populationen beschreibt. Prozentwerte erfassen demgegenüber meistens einen Teil eines Ganzen. Da in der mathematischen Definition der Nenner nicht die Gesamtpopulation, sondern die Population im Erwerbstätigenalter eingeht, wäre eine Prozentangabe somit irreführend. Deutlich wird dies an einem hypothetischen Zahlenbeispiel: bei einem Abhängigenquotient von 1,0 (bzw. 100) gäbe es altersbezogen ebenso viele Personen innerhalb wie außerhalb des Erwerbstätigenalters; wäre er als „100 %“ angegeben, würde fälschlicherweise der Eindruck erweckt, das Alter sämtlicher Personen läge außerhalb des Erwerbstätigenalters. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>Gemeindename</th>\n",
       "      <th>Gemeindecode</th>\n",
       "      <th>Einwohner</th>\n",
       "      <th>Veränderung in %</th>\n",
       "      <th>Bevölkerungs-dichte pro km²</th>\n",
       "      <th>Ausländer in %</th>\n",
       "      <th>0-19 Jahre</th>\n",
       "      <th>20-64 Jahre</th>\n",
       "      <th>65 Jahre und mehr</th>\n",
       "      <th>Rohe Heiratssziffer</th>\n",
       "      <th>...</th>\n",
       "      <th>WGS84_E</th>\n",
       "      <th>WGS84_N</th>\n",
       "      <th>Linksparteien</th>\n",
       "      <th>Mitteparteien</th>\n",
       "      <th>Rechtsparteien</th>\n",
       "      <th>Kantonsname</th>\n",
       "      <th>Region</th>\n",
       "      <th>Altenquotient</th>\n",
       "      <th>Jugendquotient</th>\n",
       "      <th>Abhaengigenquotient</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kammersrohr</td>\n",
       "      <td>2549</td>\n",
       "      <td>32</td>\n",
       "      <td>-17.948718</td>\n",
       "      <td>33.684211</td>\n",
       "      <td>3.125000</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>62.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>47.252957</td>\n",
       "      <td>7.593204</td>\n",
       "      <td>18.082789</td>\n",
       "      <td>35.185185</td>\n",
       "      <td>45.370370</td>\n",
       "      <td>Solothurn</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bister</td>\n",
       "      <td>6172</td>\n",
       "      <td>33</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.679862</td>\n",
       "      <td>6.060606</td>\n",
       "      <td>9.090909</td>\n",
       "      <td>60.606061</td>\n",
       "      <td>30.303030</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>46.361879</td>\n",
       "      <td>8.066106</td>\n",
       "      <td>25.490196</td>\n",
       "      <td>48.952242</td>\n",
       "      <td>38.084795</td>\n",
       "      <td>Valais</td>\n",
       "      <td>Genferseeregion</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.150000</td>\n",
       "      <td>0.650000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schelten</td>\n",
       "      <td>708</td>\n",
       "      <td>35</td>\n",
       "      <td>-14.634146</td>\n",
       "      <td>6.294964</td>\n",
       "      <td>5.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>65.714286</td>\n",
       "      <td>17.142857</td>\n",
       "      <td>28.169014</td>\n",
       "      <td>...</td>\n",
       "      <td>47.331996</td>\n",
       "      <td>7.551276</td>\n",
       "      <td>37.990196</td>\n",
       "      <td>17.647059</td>\n",
       "      <td>35.784314</td>\n",
       "      <td>Bern</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "      <td>0.260870</td>\n",
       "      <td>0.260870</td>\n",
       "      <td>0.521739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Berken</td>\n",
       "      <td>972</td>\n",
       "      <td>41</td>\n",
       "      <td>-16.326531</td>\n",
       "      <td>29.496403</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>2.439024</td>\n",
       "      <td>65.853659</td>\n",
       "      <td>31.707317</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>47.225775</td>\n",
       "      <td>7.705946</td>\n",
       "      <td>4.464286</td>\n",
       "      <td>11.607143</td>\n",
       "      <td>80.803571</td>\n",
       "      <td>Bern</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "      <td>0.481481</td>\n",
       "      <td>0.037037</td>\n",
       "      <td>0.518519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rebévelier</td>\n",
       "      <td>715</td>\n",
       "      <td>42</td>\n",
       "      <td>-10.638298</td>\n",
       "      <td>11.830986</td>\n",
       "      <td>2.380952</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>47.619048</td>\n",
       "      <td>26.190476</td>\n",
       "      <td>24.096386</td>\n",
       "      <td>...</td>\n",
       "      <td>47.291158</td>\n",
       "      <td>7.192605</td>\n",
       "      <td>17.467949</td>\n",
       "      <td>15.384615</td>\n",
       "      <td>50.801282</td>\n",
       "      <td>Bern</td>\n",
       "      <td>EspaceMittelland</td>\n",
       "      <td>0.550000</td>\n",
       "      <td>0.550000</td>\n",
       "      <td>1.100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Gemeindename  Gemeindecode  Einwohner  Veränderung in %  \\\n",
       "0  Kammersrohr          2549         32        -17.948718   \n",
       "1       Bister          6172         33          0.000000   \n",
       "2     Schelten           708         35        -14.634146   \n",
       "3       Berken           972         41        -16.326531   \n",
       "4   Rebévelier           715         42        -10.638298   \n",
       "\n",
       "   Bevölkerungs-dichte pro km²  Ausländer in %  0-19 Jahre  20-64 Jahre  \\\n",
       "0                    33.684211        3.125000   12.500000    62.500000   \n",
       "1                     5.679862        6.060606    9.090909    60.606061   \n",
       "2                     6.294964        5.714286   17.142857    65.714286   \n",
       "3                    29.496403        2.439024    2.439024    65.853659   \n",
       "4                    11.830986        2.380952   26.190476    47.619048   \n",
       "\n",
       "   65 Jahre und mehr  Rohe Heiratssziffer  ...    WGS84_E   WGS84_N  \\\n",
       "0          25.000000             0.000000  ...  47.252957  7.593204   \n",
       "1          30.303030             0.000000  ...  46.361879  8.066106   \n",
       "2          17.142857            28.169014  ...  47.331996  7.551276   \n",
       "3          31.707317             0.000000  ...  47.225775  7.705946   \n",
       "4          26.190476            24.096386  ...  47.291158  7.192605   \n",
       "\n",
       "   Linksparteien  Mitteparteien  Rechtsparteien  Kantonsname  \\\n",
       "0      18.082789      35.185185       45.370370    Solothurn   \n",
       "1      25.490196      48.952242       38.084795       Valais   \n",
       "2      37.990196      17.647059       35.784314         Bern   \n",
       "3       4.464286      11.607143       80.803571         Bern   \n",
       "4      17.467949      15.384615       50.801282         Bern   \n",
       "\n",
       "             Region  Altenquotient  Jugendquotient  Abhaengigenquotient  \n",
       "0  EspaceMittelland       0.400000        0.200000             0.600000  \n",
       "1   Genferseeregion       0.500000        0.150000             0.650000  \n",
       "2  EspaceMittelland       0.260870        0.260870             0.521739  \n",
       "3  EspaceMittelland       0.481481        0.037037             0.518519  \n",
       "4  EspaceMittelland       0.550000        0.550000             1.100000  \n",
       "\n",
       "[5 rows x 56 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Berechnung von Altenquotien, Jugendquotient und Abhängigenquotient\n",
    "df_gemeinden['Altenquotient'] = df_gemeinden['65 Jahre und mehr']/df_gemeinden['20-64 Jahre']\n",
    "df_gemeinden['Jugendquotient'] = df_gemeinden['0-19 Jahre']/df_gemeinden['20-64 Jahre']\n",
    "df_gemeinden['Abhaengigenquotient'] = df_gemeinden['Altenquotient'] + df_gemeinden['Jugendquotient']\n",
    "df_gemeinden.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Einlesen des Files Raumgliederungen zum Mappen nach Stadt, Agglomeration, Land.\n",
    "#URL: https://www.agvchapp.bfs.admin.ch/de/typologies/query\n",
    "excel_file = pd.ExcelFile(\"./Rohdaten/raumgliederungen.xlsx\", engine='openpyxl')\n",
    "raumgliederungen = pd.read_excel(excel_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Agglomerationen 2012</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BFS Gde-nummer</td>\n",
       "      <td>Gemeindename</td>\n",
       "      <td>Kantons-nummer</td>\n",
       "      <td>Kanton</td>\n",
       "      <td>Bezirks-nummer</td>\n",
       "      <td>Bezirksname</td>\n",
       "      <td>Agglomerationen 2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CH1+CL_AGGL+2012.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>Bonstetten</td>\n",
       "      <td>1</td>\n",
       "      <td>ZH</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0          Unnamed: 1      Unnamed: 2 Unnamed: 3  \\\n",
       "0  BFS Gde-nummer        Gemeindename  Kantons-nummer     Kanton   \n",
       "1             NaN                 NaN             NaN        NaN   \n",
       "2               1     Aeugst am Albis               1         ZH   \n",
       "3               2  Affoltern am Albis               1         ZH   \n",
       "4               3          Bonstetten               1         ZH   \n",
       "\n",
       "       Unnamed: 4   Unnamed: 5  Agglomerationen 2012  \n",
       "0  Bezirks-nummer  Bezirksname  Agglomerationen 2012  \n",
       "1             NaN          NaN    CH1+CL_AGGL+2012.0  \n",
       "2             101    Affoltern                   261  \n",
       "3             101    Affoltern                   261  \n",
       "4             101    Affoltern                   261  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raumgliederungen.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Column Header einsetzen\n",
    "new_header = raumgliederungen.iloc[0]\n",
    "raumgliederungen.columns = new_header\n",
    "raumgliederungen = raumgliederungen.drop([0, 1],)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Definieren der Funktion zur Zuweisung des Agglo-Status auf die einzelnen Gemeinden.\n",
    "def f(row):\n",
    "    if row['BFS Gde-nummer'] == row['Agglomerationen 2012']:\n",
    "        val = 'Stadt'\n",
    "    elif 0 < row['Agglomerationen 2012']:\n",
    "        val = 'Agglomeration'\n",
    "    else:\n",
    "        val = 'Land'\n",
    "    return val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "BFS Gde-nummer          object\n",
      "Gemeindename            object\n",
      "Kantons-nummer          object\n",
      "Kanton                  object\n",
      "Bezirks-nummer          object\n",
      "Bezirksname             object\n",
      "Agglomerationen 2012    object\n",
      "dtype: object\n",
      "0\n",
      "BFS Gde-nummer           int64\n",
      "Gemeindename            object\n",
      "Kantons-nummer          object\n",
      "Kanton                  object\n",
      "Bezirks-nummer          object\n",
      "Bezirksname             object\n",
      "Agglomerationen 2012     int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(raumgliederungen.dtypes)\n",
    "\n",
    "# using dictionary to convert specific columns\n",
    "convert_dict = {'BFS Gde-nummer': int,\n",
    "                'Agglomerationen 2012': int\n",
    "               }\n",
    "  \n",
    "raumgliederungen  = raumgliederungen.astype(convert_dict)\n",
    "print(raumgliederungen.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Einfügen des Agglo-Status in einer neuen column\n",
    "raumgliederungen['Charakter'] =  raumgliederungen.apply(f, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Entfernen nicht notwendiger columns\n",
    "raumgliederungen = raumgliederungen.drop(['Gemeindename', 'Kantons-nummer', 'Kanton'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>BFS Gde-nummer</th>\n",
       "      <th>Bezirks-nummer</th>\n",
       "      <th>Bezirksname</th>\n",
       "      <th>Agglomerationen 2012</th>\n",
       "      <th>Charakter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "      <td>Agglomeration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "      <td>Agglomeration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "      <td>Agglomeration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "      <td>Agglomeration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>101</td>\n",
       "      <td>Affoltern</td>\n",
       "      <td>261</td>\n",
       "      <td>Agglomeration</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0  BFS Gde-nummer Bezirks-nummer Bezirksname  Agglomerationen 2012  \\\n",
       "2               1            101   Affoltern                   261   \n",
       "3               2            101   Affoltern                   261   \n",
       "4               3            101   Affoltern                   261   \n",
       "5               4            101   Affoltern                   261   \n",
       "6               5            101   Affoltern                   261   \n",
       "\n",
       "0      Charakter  \n",
       "2  Agglomeration  \n",
       "3  Agglomeration  \n",
       "4  Agglomeration  \n",
       "5  Agglomeration  \n",
       "6  Agglomeration  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raumgliederungen.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Zusammenführen des Raumgliederungs Datensatz mit dem Gemeinde Dataframe\n",
    "df_gemeinden = pd.merge(df_gemeinden, raumgliederungen, left_on='Gemeindecode', right_on='BFS Gde-nummer', how='left').drop(['BFS Gde-nummer','Bezirks-nummer','Bezirksname'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2142, 58)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gemeinden.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gemeindename                                     0\n",
       "Gemeindecode                                     0\n",
       "Einwohner                                        0\n",
       "Veränderung in %                                 0\n",
       "Bevölkerungs-dichte pro km²                      0\n",
       "Ausländer in %                                   0\n",
       "0-19 Jahre                                       0\n",
       "20-64 Jahre                                      0\n",
       "65 Jahre und mehr                                0\n",
       "Rohe Heiratssziffer                              0\n",
       "Rohe Scheidungsziffer                            0\n",
       "Rohe Geburtenziffer                              0\n",
       "Rohe Sterbeziffer                                0\n",
       "Anzahl Privathaushalte                           0\n",
       "Durchschnittliche Haushaltsgrösse in Personen    0\n",
       "Gesamtfläche in km² 1)                           0\n",
       "Siedlungsfläche in %                             0\n",
       "Veränderung in ha                                0\n",
       "Landwirtschafts-fläche in %                      0\n",
       "Veränderung in ha.1                              0\n",
       "Wald und Gehölze in %                            0\n",
       "Unproduktive Fläche in %                         0\n",
       "Beschäftigte total                               0\n",
       "im 1. Sektor                                     0\n",
       "im 2. Sektor                                     0\n",
       "im 3. Sektor                                     0\n",
       "Arbeitsstätten total                             0\n",
       "im 1. Sektor.1                                   0\n",
       "im 2. Sektor.1                                   0\n",
       "im 3. Sektor.1                                   0\n",
       "Leerwohnungs-ziffer                              0\n",
       "Neu gebaute Wohnungen pro 1000 Einwohner         0\n",
       "Sozialhilfequote                                 0\n",
       "FDP 2)                                           0\n",
       "CVP                                              0\n",
       "SP                                               0\n",
       "SVP                                              0\n",
       "EVP/CSP                                          0\n",
       "GLP                                              0\n",
       "BDP                                              0\n",
       "PdA/Sol.                                         0\n",
       "GPS                                              0\n",
       "Kleine Rechtsparteien                            0\n",
       "Kantonskürzel                                    0\n",
       "LV95_E                                           0\n",
       "LV95_N                                           0\n",
       "WGS84_E                                          0\n",
       "WGS84_N                                          0\n",
       "Linksparteien                                    0\n",
       "Mitteparteien                                    0\n",
       "Rechtsparteien                                   0\n",
       "Kantonsname                                      0\n",
       "Region                                           0\n",
       "Altenquotient                                    0\n",
       "Jugendquotient                                   0\n",
       "Abhaengigenquotient                              0\n",
       "Agglomerationen 2012                             5\n",
       "Charakter                                        5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Überprüfen ob neue Null's entstanden sind\n",
    "df_gemeinden.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Da das Excelfile zur Raumgliederung von 2012 ist gibt es kleine Differenzen bei der Anzahl Gemeinden und 5 weisen keinen Aglo-Status aus.\n",
    "#Die nicht definierbaren Gemeinden erhalten den Status Land.\n",
    "df_gemeinden[['Agglomerationen 2012']] = df_gemeinden[['Agglomerationen 2012']].fillna(value=0.0)\n",
    "df_gemeinden[['Charakter']] = df_gemeinden[['Charakter']].fillna(value='Land')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gemeinden.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Speichern finale Gemeindedaten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gemeinden = df_gemeinden.set_index('Gemeindename')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gemeinden.to_csv('./Daten/Gemeinden_Final.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}