analysis_mm.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set-up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Libraries and Dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "code_folding": [],
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "# Import Libraries\n",
    "%load_ext autoreload\n",
    "%autoreload\n",
    "\n",
    "import os\n",
    "#if not os.path.basename(os.getcwd()) == \"datenguide-python\": os.chdir(\"..\")\n",
    "    \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "#import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from numpy import loadtxt\n",
    "#import glob\n",
    "import geopandas as gpd\n",
    "#import geojsonio\n",
    "#import ipywidgets as widgets\n",
    "#from ipywidgets import interact, interactive, fixed, interact_manual\n",
    "import math\n",
    "from IPython.display import Image\n",
    "\n",
    "# Plotly\n",
    "import plotly\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "import plotly.io as pio\n",
    "#import cufflinks as cf\n",
    "\n",
    "# Datenguidepy\n",
    "from datenguidepy.query_helper import get_regions, get_statistics, get_availability_summary\n",
    "from datenguidepy import Query\n",
    "\n",
    "# Processing/App\n",
    "from sklearn.preprocessing import StandardScaler, MinMaxScaler\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.pipeline import Pipeline\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Dashboard\n",
    "\n",
    "#Global variables\n",
    "regions = get_regions()\n",
    "statistics = get_statistics()\n",
    "availability_summary = get_availability_summary()  # Save availability summary as dataframe\n",
    "availability_summary.reset_index(inplace=True)\n",
    "nuts3_codes = pd.DataFrame(get_regions().query('level == \"nuts3\"').name)   # Get all NUTS-3 codes\n",
    "years = [2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]       # Define years for timeframe\n",
    "\n",
    "#Table display settings\n",
    "pd.set_option('display.max_colwidth', 150)\n",
    "pd.set_option('display.max_columns', 150)\n",
    "pd.set_option('display.max_rows', 100)\n",
    "pd.set_option('display.min_rows', 50)\n",
    "\n",
    "#Plotting Settings\n",
    "#sns.set(style=\"white\")  #, palette=[\"#FF5A5F\", \"#00A699\"])\n",
    "#sns.set_palette(\n",
    "#    sns.color_palette(\n",
    "#        [\"#00A699\", \"#767676\", \"#FF5A5F\", \"#F9C70C\", \"#FC642D\", \"#484848\"]))\n",
    "\n",
    "pio.templates.default = \"simple_white\"\n",
    "px.defaults.template = \"simple_white\"  # 'ggplot2', 'seaborn', 'simple_white', 'plotly', 'plotly_white', 'plotly_dark', 'presentation', 'xgridoff', 'ygridoff', 'gridon', 'none'\n",
    "px.defaults.color_continuous_scale = [\n",
    "    \"#00A699\", \"#50D6B9\", \"#FF9A7F\", \"#FF5A5F\"\n",
    "]  # [\"#50D6B9\", \"#00A699\", \"#FF9A7F\", \"#FF5A5F\"] #\"burgyl\"\n",
    "px.defaults.color_discrete_sequence = [\n",
    "    \"#00A699\", \"#767676\", \"#FF5A5F\", \"#F9C70C\", \"#FC642D\", \"#484848\"\n",
    "]\n",
    "px.defaults.width = 900\n",
    "px.defaults.height = 600"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Global functions**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Function for standardized query (I get duplicate rows, so I delete them in sequence. Can surely be improved)\n",
    "def nuts3_query(code):\n",
    "    query = Query.region(list(nuts3_codes.index))    # Query.all_regions(nuts=3) did not work for me somehow\n",
    "    query.add_field(code)\n",
    "    query_res = query.results(verbose_statistics=True)    # verbose_statistics changes column name of code to title\n",
    "    value_col = [col for col in query_res.columns if code in col][0]   # necessary as \"code\" is no longer column title\n",
    "    query_res = query_res[[\"id\", \"name\", \"year\", value_col]]     # retain only required columns\n",
    "    query_res.drop_duplicates(subset=None, keep='first', inplace=True)\n",
    "    query_res.rename(columns=lambda x: x+\"_nuts3\" if x in [\"id\", \"name\"] else x, inplace=True)\n",
    "    return query_res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset contents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "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>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Saarland</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Sachsen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Thüringen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Saarland</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Chemnitz, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Dresden, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Leipzig, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Chemnitz, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Dresden, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Leipzig, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Dessau, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>Halle, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Magdeburg, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Thüringen</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10041</th>\n",
       "      <td>Saarbrücken, Regionalverband</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10042</th>\n",
       "      <td>Merzig-Wadern, Landkreis</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10043</th>\n",
       "      <td>Neunkirchen, Landkreis</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>033</th>\n",
       "      <td>Lüneburg, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>034</th>\n",
       "      <td>Weser-Ems, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>040</th>\n",
       "      <td>Bremen</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>051</th>\n",
       "      <td>Düsseldorf, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>053</th>\n",
       "      <td>Köln, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>055</th>\n",
       "      <td>Münster, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>057</th>\n",
       "      <td>Detmold, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>059</th>\n",
       "      <td>Arnsberg, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>064</th>\n",
       "      <td>Darmstadt, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>065</th>\n",
       "      <td>Gießen, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>066</th>\n",
       "      <td>Kassel, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>071</th>\n",
       "      <td>Koblenz, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>072</th>\n",
       "      <td>Trier, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>073</th>\n",
       "      <td>Rheinhessen-Pfalz, Stat. Region</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>081</th>\n",
       "      <td>Stuttgart, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>082</th>\n",
       "      <td>Karlsruhe, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>083</th>\n",
       "      <td>Freiburg, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>084</th>\n",
       "      <td>Tübingen, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>091</th>\n",
       "      <td>Oberbayern, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>092</th>\n",
       "      <td>Niederbayern, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>093</th>\n",
       "      <td>Oberpfalz, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>094</th>\n",
       "      <td>Oberfranken, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>095</th>\n",
       "      <td>Mittelfranken, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>096</th>\n",
       "      <td>Unterfranken, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>097</th>\n",
       "      <td>Schwaben, Regierungsbezirk</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14085 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      name  level parent\n",
       "region_id                                               \n",
       "10                                Saarland  nuts1     DG\n",
       "11                                  Berlin  nuts1     DG\n",
       "12                             Brandenburg  nuts1     DG\n",
       "13                  Mecklenburg-Vorpommern  nuts1     DG\n",
       "14                                 Sachsen  nuts1     DG\n",
       "15                          Sachsen-Anhalt  nuts1     DG\n",
       "16                               Thüringen  nuts1     DG\n",
       "100                               Saarland  nuts2     10\n",
       "110                                 Berlin  nuts2     11\n",
       "120                            Brandenburg  nuts2     12\n",
       "130                 Mecklenburg-Vorpommern  nuts2     13\n",
       "141             Chemnitz, Regierungsbezirk  nuts2     14\n",
       "142              Dresden, Regierungsbezirk  nuts2     14\n",
       "143              Leipzig, Regierungsbezirk  nuts2     14\n",
       "145                 Chemnitz, Stat. Region  nuts2     14\n",
       "146                  Dresden, Stat. Region  nuts2     14\n",
       "147                  Leipzig, Stat. Region  nuts2     14\n",
       "150                         Sachsen-Anhalt  nuts2     15\n",
       "151                   Dessau, Stat. Region  nuts2     15\n",
       "152                    Halle, Stat. Region  nuts2     15\n",
       "153                Magdeburg, Stat. Region  nuts2     15\n",
       "160                              Thüringen  nuts2     16\n",
       "10041         Saarbrücken, Regionalverband  nuts3    100\n",
       "10042             Merzig-Wadern, Landkreis  nuts3    100\n",
       "10043               Neunkirchen, Landkreis  nuts3    100\n",
       "...                                    ...    ...    ...\n",
       "033                 Lüneburg, Stat. Region  nuts2     03\n",
       "034                Weser-Ems, Stat. Region  nuts2     03\n",
       "040                                 Bremen  nuts2     04\n",
       "051           Düsseldorf, Regierungsbezirk  nuts2     05\n",
       "053                 Köln, Regierungsbezirk  nuts2     05\n",
       "055              Münster, Regierungsbezirk  nuts2     05\n",
       "057              Detmold, Regierungsbezirk  nuts2     05\n",
       "059             Arnsberg, Regierungsbezirk  nuts2     05\n",
       "064            Darmstadt, Regierungsbezirk  nuts2     06\n",
       "065               Gießen, Regierungsbezirk  nuts2     06\n",
       "066               Kassel, Regierungsbezirk  nuts2     06\n",
       "071                  Koblenz, Stat. Region  nuts2     07\n",
       "072                    Trier, Stat. Region  nuts2     07\n",
       "073        Rheinhessen-Pfalz, Stat. Region  nuts2     07\n",
       "081            Stuttgart, Regierungsbezirk  nuts2     08\n",
       "082            Karlsruhe, Regierungsbezirk  nuts2     08\n",
       "083             Freiburg, Regierungsbezirk  nuts2     08\n",
       "084             Tübingen, Regierungsbezirk  nuts2     08\n",
       "091           Oberbayern, Regierungsbezirk  nuts2     09\n",
       "092         Niederbayern, Regierungsbezirk  nuts2     09\n",
       "093            Oberpfalz, Regierungsbezirk  nuts2     09\n",
       "094          Oberfranken, Regierungsbezirk  nuts2     09\n",
       "095        Mittelfranken, Regierungsbezirk  nuts2     09\n",
       "096         Unterfranken, Regierungsbezirk  nuts2     09\n",
       "097             Schwaben, Regierungsbezirk  nuts2     09\n",
       "\n",
       "[14085 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display regions\n",
    "regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "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>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Saarland</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Sachsen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Thüringen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02</th>\n",
       "      <td>Hamburg</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03</th>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04</th>\n",
       "      <td>Bremen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05</th>\n",
       "      <td>Nordrhein-Westfalen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06</th>\n",
       "      <td>Hessen</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>07</th>\n",
       "      <td>Rheinland-Pfalz</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>08</th>\n",
       "      <td>Baden-Württemberg, Land</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09</th>\n",
       "      <td>Bayern</td>\n",
       "      <td>nuts1</td>\n",
       "      <td>DG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              name  level parent\n",
       "region_id                                       \n",
       "10                        Saarland  nuts1     DG\n",
       "11                          Berlin  nuts1     DG\n",
       "12                     Brandenburg  nuts1     DG\n",
       "13          Mecklenburg-Vorpommern  nuts1     DG\n",
       "14                         Sachsen  nuts1     DG\n",
       "15                  Sachsen-Anhalt  nuts1     DG\n",
       "16                       Thüringen  nuts1     DG\n",
       "01              Schleswig-Holstein  nuts1     DG\n",
       "02                         Hamburg  nuts1     DG\n",
       "03                   Niedersachsen  nuts1     DG\n",
       "04                          Bremen  nuts1     DG\n",
       "05             Nordrhein-Westfalen  nuts1     DG\n",
       "06                          Hessen  nuts1     DG\n",
       "07                 Rheinland-Pfalz  nuts1     DG\n",
       "08         Baden-Württemberg, Land  nuts1     DG\n",
       "09                          Bayern  nuts1     DG"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query regions, e.g. get all \"Bundesländer\n",
    "get_regions().query(\"level == 'nuts1'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "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>short_description</th>\n",
       "      <th>long_description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>statistic</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>RLE001</th>\n",
       "      <td>Regelleistungsempfänger</td>\n",
       "      <td>Regelleistungsempfänger\\n \\n \\nErläuterung für folgende Statistik(en):\\n22221 Statistik der Empfänger von Asylbewerberregelleis-\\n      tungen\\n \\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEV002</th>\n",
       "      <td>Gestorbene</td>\n",
       "      <td>Gestorbene\\n \\n \\nErläuterung für folgende Statistik(en):\\n12613 Statistik der Sterbefälle\\n \\nBegriffsinhalt:\\nSterbefälle\\n \\nAls Sterbefälle we...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLCX05</th>\n",
       "      <td>Bodenfläche</td>\n",
       "      <td>Bodenfläche\\n \\n \\nErläuterung für folgende Statistik(en):\\n33111 Flächenerhebung nach Art der tatsächlichen Nutzung\\n \\nBegriffsinhalt:\\nBodenflä...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLC005</th>\n",
       "      <td>Bodenfläche</td>\n",
       "      <td>Bodenfläche\\n\\n\\nErläuterung für folgende Statistik(en):\\n33111 Flächenerhebung nach Art der tatsächlichen Nutzung\\n \\nBegriffsinhalt:\\nBodenfläch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND35</th>\n",
       "      <td>Kinder</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND36</th>\n",
       "      <td>Betreuungsquote</td>\n",
       "      <td>Betreuungsquote\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n            geseinri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND37</th>\n",
       "      <td>Anteil der Kinder mit ausl. Herkunft mind. eines Elternteils</td>\n",
       "      <td>Anteil der Kinder mit ausländischer Herkunft mindestens\\neines Elternteils\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND38</th>\n",
       "      <td>Ant.d.Kinder m.e.durchgeh. Betreuungszeit v.m.a. 7 Std.p.Betreuungstag</td>\n",
       "      <td>Anteil der Kinder mit einer durchgehenden Betreuungszeit von\\nmehr als 7 Stunden pro Betreuungstag\\n \\n \\nErläuterung für folgende Statistik(en):\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PERS01</th>\n",
       "      <td>Pädagog.,Leitungs-u.Verwaltg.personal i.Kita.einr.</td>\n",
       "      <td>Pädagogisches, Leitungs- und Verwaltungspersonal in Kinder-\\ntageseinrichtungen\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PERS02</th>\n",
       "      <td>Kindertagespflegepersonen</td>\n",
       "      <td>Kindertagespflegepersonen\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n          ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND01</th>\n",
       "      <td>Betreute Kinder</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND33</th>\n",
       "      <td>Betreute Kinder von unter 3 Jahren</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND34</th>\n",
       "      <td>Betreute Kinder von unter 14 Jahren</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND02</th>\n",
       "      <td>Kinder, Herkunft mind. 1 Elternteil ausländisch</td>\n",
       "      <td>Kinder, Herkunft mindestens 1 Elternteil ausländisch\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND03</th>\n",
       "      <td>Kinder mit Mittagsverpflegung</td>\n",
       "      <td>Kinder mit Mittagsverpflegung\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n      ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EGS002</th>\n",
       "      <td>Empfänger von Grundsicherung</td>\n",
       "      <td>Empfänger von Grundsicherung im Alter und bei\\nErwerbsminderung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EGS001</th>\n",
       "      <td>Empfänger von Grundsicherung</td>\n",
       "      <td>Empfänger von Grundsicherung im Alter und bei\\nErwerbsminderung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLC048</th>\n",
       "      <td>LF in Betrieben mit ökologischem Landbau</td>\n",
       "      <td>Landwirtschaftlich genutzte Fläche in Betrieben mit ökolo-\\ngischem Landbau\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftsz...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERW046</th>\n",
       "      <td>Arbeitskräfte</td>\n",
       "      <td>Arbeitskräfte\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nArbeitskräfte in landwirtschaftlic...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LST009</th>\n",
       "      <td>Arbeitsleistung</td>\n",
       "      <td>Arbeitsleistung\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nArbeitsleistung\\n \\nDie AK-Einhe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BTR034</th>\n",
       "      <td>Landwirtschaftliche Betriebe mit Viehhaltung</td>\n",
       "      <td>Landwirtschaftliche Betriebe mit Viehhaltung\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n\\nBegriffsinhalt:\\nBeri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BTR038</th>\n",
       "      <td>Landwirtschaftliche Betriebe mit 100 u.m. GV</td>\n",
       "      <td>Landwirtschaftliche Betriebe mit 100 und mehr GV\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VIE003</th>\n",
       "      <td>Viehbestand</td>\n",
       "      <td>Viehbestand\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nGroßvieheinheiten (GV)\\n \\nDie Großv...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BTR010</th>\n",
       "      <td>Landwirtschaftliche Betriebe</td>\n",
       "      <td>Landwirtschaftliche Betriebe\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n\\nBegriffsinhalt:\\nBerichtskreis\\n\\nSei...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLC017</th>\n",
       "      <td>Landwirtschaftlich genutzte Fläche</td>\n",
       "      <td>Landwirtschaftlich genutzte Fläche\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nLandwirtschaf...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STEU09</th>\n",
       "      <td>Gewerbesteuerumlage</td>\n",
       "      <td>Gewerbesteuerumlage\\n \\n \\nErläuterung für folgende Statistik(en):\\n71231 Realsteuervergleich\\n \\nBegriffsinhalt:\\nGewerbesteuerumlage\\n \\nDie Gem...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EKM006</th>\n",
       "      <td>verfügbares Einkommen der privaten Haushalte</td>\n",
       "      <td>verfügbares Einkommen der privaten Haushalte\\n \\n \\nErläuterung für folgende Statistik(en):\\n82411 VGR der Länder - Umverteilungsrechnung\\n \\nBegr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EKM014</th>\n",
       "      <td>verfüg. Einkommen der priv. Haushalte je Einwohner</td>\n",
       "      <td>verfügbares Einkommen der privaten Haushalte je Einwohner\\n \\n \\nErläuterung für folgende Statistik(en):\\n82411 VGR der Länder - Umverteilungsrech...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UMS051</th>\n",
       "      <td>Ausbaugewerblicher Umsatz des Vorjahres</td>\n",
       "      <td>Ausbaugewerblicher Umsatz des Vorjahres\\n \\n \\nErläuterung für folgende Statistik(en):\\n44241 Jährliche Erhebung im Ausbaugewerbe und bei Bauträge...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRM001</th>\n",
       "      <td>Trockenmasse a.Abwasserbeh.-anl.dir.ents.Klärschl.</td>\n",
       "      <td>Trockenmasse des aus Abwasserbehandlungsanlagen direkt\\nentsorgten Klärschlamms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER001</th>\n",
       "      <td>Unfälle (insgesamt)</td>\n",
       "      <td>Unfälle (ingesamt)\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nStraßenverkehrs...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER002</th>\n",
       "      <td>Unfälle mit Personenschaden</td>\n",
       "      <td>Unfälle mit Personenschaden\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nUnfäll...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER005</th>\n",
       "      <td>Schwerwiegende Unfälle mit Sachschäden i.e.S.</td>\n",
       "      <td>Schwerwiegende Unfälle mit Sachschäden im engeren Sinne\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER006</th>\n",
       "      <td>Sons.Sachschadensunf.unter d.Einfl.berausch.Mittel</td>\n",
       "      <td>Alkoholunfälle ohne Personenschaden und schwerwiegende\\nSachschäden\\n \\nUnfälle, bei denen alle beteiligten Kfz noch fahrbereit\\nwaren und gleichz...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER009</th>\n",
       "      <td>Getötete Personen</td>\n",
       "      <td>Getötete Personen\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nGetötete Persone...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER019</th>\n",
       "      <td>Verletzte Personen</td>\n",
       "      <td>Verletzte Personen\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nVerletzte Perso...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER056</th>\n",
       "      <td>Schwerwiegende Unfälle mit Sachschaden</td>\n",
       "      <td>Schwerwiegende Unfälle mit Sachschaden\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinha...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GAST01</th>\n",
       "      <td>Gästeankünfte</td>\n",
       "      <td>Gästeankünfte\\n \\n \\nErläuterung für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nGästeankünfte\\n \\nEs handelt ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GAST02</th>\n",
       "      <td>Gästeübernachtungen</td>\n",
       "      <td>Gästeübernachtungen\\n \\n \\nErläuterung für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nGästeübernachtungen\\n \\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GAST03</th>\n",
       "      <td>Angebotene Gästebetten</td>\n",
       "      <td>Angebotene Gästebetten\\n \\n \\nErläuterungen für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nGästebetten\\n \\nAl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GAST04</th>\n",
       "      <td>Geöffnete Beherbergungsbetriebe</td>\n",
       "      <td>Geöffnete Beherbergungsbetriebe\\n \\n \\nErläuterung für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nBeherbergun...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GAST05</th>\n",
       "      <td>Angebotene Schlafgelegenheiten</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VER012</th>\n",
       "      <td>Kraftfahrzeugbestand</td>\n",
       "      <td>Kraftfahrzeugbestand\\n \\n \\nErläuterung für folgende Statistik(en):\\n46251 Statistik des Kraftfahrzeugbestandes\\n \\nBegriffsinhalt:\\nKraftfahrzeug...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERW061</th>\n",
       "      <td>Arbeitnehmer im Jahresdurchschn. (Inlandskonzept)</td>\n",
       "      <td>Arbeitnehmer im Jahresdurchschnitt (Inlandskonzept)\\n \\n \\nErläuterung für folgende Statistik(en):\\n13312 Erwerbstätigenrechnung des Bundes und de...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERW063</th>\n",
       "      <td>Erwerbstätige im Jahresdurchschn. (Inlandskonzept)</td>\n",
       "      <td>Erwerbstätige im Jahresdurchschnitt (Inlandskonzept)\\n \\n \\nErläuterung für folgende Statistik(en):\\n13312 Erwerbstätigenrechnung des Bundes und d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UMS011</th>\n",
       "      <td>Gesamtumsatz des Vorjahres</td>\n",
       "      <td>Gesamtumsatz des Vorjahres\\n \\n \\nErläuterung für folgende Statistik(en):\\n44231 Ergänzungserhebung im Bauhauptgewerbe\\n \\nBegriffsinhalt:\\nGesamt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UMS041</th>\n",
       "      <td>Baugewerblicher Umsatz des Vorjahres</td>\n",
       "      <td>Baugewerblicher Umsatz des Vorjahres\\n \\n \\nErläuterung für folgende Statistik(en):\\n44231 Ergänzungserhebung im Bauhauptgewerbe\\n \\nBegriffsinhal...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLC006</th>\n",
       "      <td>Gebietsfläche</td>\n",
       "      <td>Erläuterung für folgende Statistik(en):\\nFlächenerhebung nach Art der tatsächlichen Nutzung\\n \\nBegriffsinhalt:\\nDie Angaben über die Gebietsfläch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEM001</th>\n",
       "      <td>Zahl der Gemeinden</td>\n",
       "      <td>Zahl der Gemeinden\\n \\n \\nErläuterung für folgende Statistik(en):\\n11111 Feststellung des Gebietsstandes\\n \\nBegriffsinhalt:\\nZahl der Gemeinden\\n...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERT001</th>\n",
       "      <td>Hektarerträge</td>\n",
       "      <td>Hektarerträge\\n \\n \\nErläuterung für folgende Statistik(en):\\n41241/42146 Erntestatistik\\n \\nBegriffsinhalt:\\nErnteertrag\\n \\nDie Ertragsschätzung...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>492 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                short_description  \\\n",
       "statistic                                                                           \n",
       "RLE001                                                    Regelleistungsempfänger   \n",
       "BEV002                                                                 Gestorbene   \n",
       "FLCX05                                                                Bodenfläche   \n",
       "FLC005                                                                Bodenfläche   \n",
       "KIND35                                                                     Kinder   \n",
       "KIND36                                                            Betreuungsquote   \n",
       "KIND37               Anteil der Kinder mit ausl. Herkunft mind. eines Elternteils   \n",
       "KIND38     Ant.d.Kinder m.e.durchgeh. Betreuungszeit v.m.a. 7 Std.p.Betreuungstag   \n",
       "PERS01                         Pädagog.,Leitungs-u.Verwaltg.personal i.Kita.einr.   \n",
       "PERS02                                                  Kindertagespflegepersonen   \n",
       "KIND01                                                            Betreute Kinder   \n",
       "KIND33                                         Betreute Kinder von unter 3 Jahren   \n",
       "KIND34                                        Betreute Kinder von unter 14 Jahren   \n",
       "KIND02                            Kinder, Herkunft mind. 1 Elternteil ausländisch   \n",
       "KIND03                                              Kinder mit Mittagsverpflegung   \n",
       "EGS002                                               Empfänger von Grundsicherung   \n",
       "EGS001                                               Empfänger von Grundsicherung   \n",
       "FLC048                                   LF in Betrieben mit ökologischem Landbau   \n",
       "ERW046                                                              Arbeitskräfte   \n",
       "LST009                                                            Arbeitsleistung   \n",
       "BTR034                               Landwirtschaftliche Betriebe mit Viehhaltung   \n",
       "BTR038                               Landwirtschaftliche Betriebe mit 100 u.m. GV   \n",
       "VIE003                                                                Viehbestand   \n",
       "BTR010                                               Landwirtschaftliche Betriebe   \n",
       "FLC017                                         Landwirtschaftlich genutzte Fläche   \n",
       "...                                                                           ...   \n",
       "STEU09                                                        Gewerbesteuerumlage   \n",
       "EKM006                               verfügbares Einkommen der privaten Haushalte   \n",
       "EKM014                         verfüg. Einkommen der priv. Haushalte je Einwohner   \n",
       "UMS051                                    Ausbaugewerblicher Umsatz des Vorjahres   \n",
       "TRM001                         Trockenmasse a.Abwasserbeh.-anl.dir.ents.Klärschl.   \n",
       "VER001                                                        Unfälle (insgesamt)   \n",
       "VER002                                                Unfälle mit Personenschaden   \n",
       "VER005                              Schwerwiegende Unfälle mit Sachschäden i.e.S.   \n",
       "VER006                         Sons.Sachschadensunf.unter d.Einfl.berausch.Mittel   \n",
       "VER009                                                          Getötete Personen   \n",
       "VER019                                                         Verletzte Personen   \n",
       "VER056                                     Schwerwiegende Unfälle mit Sachschaden   \n",
       "GAST01                                                              Gästeankünfte   \n",
       "GAST02                                                        Gästeübernachtungen   \n",
       "GAST03                                                     Angebotene Gästebetten   \n",
       "GAST04                                            Geöffnete Beherbergungsbetriebe   \n",
       "GAST05                                             Angebotene Schlafgelegenheiten   \n",
       "VER012                                                       Kraftfahrzeugbestand   \n",
       "ERW061                          Arbeitnehmer im Jahresdurchschn. (Inlandskonzept)   \n",
       "ERW063                         Erwerbstätige im Jahresdurchschn. (Inlandskonzept)   \n",
       "UMS011                                                 Gesamtumsatz des Vorjahres   \n",
       "UMS041                                       Baugewerblicher Umsatz des Vorjahres   \n",
       "FLC006                                                              Gebietsfläche   \n",
       "GEM001                                                         Zahl der Gemeinden   \n",
       "ERT001                                                              Hektarerträge   \n",
       "\n",
       "                                                                                                                                                long_description  \n",
       "statistic                                                                                                                                                         \n",
       "RLE001     Regelleistungsempfänger\\n \\n \\nErläuterung für folgende Statistik(en):\\n22221 Statistik der Empfänger von Asylbewerberregelleis-\\n      tungen\\n \\...  \n",
       "BEV002     Gestorbene\\n \\n \\nErläuterung für folgende Statistik(en):\\n12613 Statistik der Sterbefälle\\n \\nBegriffsinhalt:\\nSterbefälle\\n \\nAls Sterbefälle we...  \n",
       "FLCX05     Bodenfläche\\n \\n \\nErläuterung für folgende Statistik(en):\\n33111 Flächenerhebung nach Art der tatsächlichen Nutzung\\n \\nBegriffsinhalt:\\nBodenflä...  \n",
       "FLC005     Bodenfläche\\n\\n\\nErläuterung für folgende Statistik(en):\\n33111 Flächenerhebung nach Art der tatsächlichen Nutzung\\n \\nBegriffsinhalt:\\nBodenfläch...  \n",
       "KIND35                                                                                                                                                   Missing  \n",
       "KIND36     Betreuungsquote\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n            geseinri...  \n",
       "KIND37     Anteil der Kinder mit ausländischer Herkunft mindestens\\neines Elternteils\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik d...  \n",
       "KIND38     Anteil der Kinder mit einer durchgehenden Betreuungszeit von\\nmehr als 7 Stunden pro Betreuungstag\\n \\n \\nErläuterung für folgende Statistik(en):\\...  \n",
       "PERS01     Pädagogisches, Leitungs- und Verwaltungspersonal in Kinder-\\ntageseinrichtungen\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statis...  \n",
       "PERS02     Kindertagespflegepersonen\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n          ...  \n",
       "KIND01                                                                                                                                                   Missing  \n",
       "KIND33                                                                                                                                                   Missing  \n",
       "KIND34                                                                                                                                                   Missing  \n",
       "KIND02     Kinder, Herkunft mindestens 1 Elternteil ausländisch\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen ...  \n",
       "KIND03     Kinder mit Mittagsverpflegung\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n      ...  \n",
       "EGS002                                                                                           Empfänger von Grundsicherung im Alter und bei\\nErwerbsminderung  \n",
       "EGS001                                                                                           Empfänger von Grundsicherung im Alter und bei\\nErwerbsminderung  \n",
       "FLC048     Landwirtschaftlich genutzte Fläche in Betrieben mit ökolo-\\ngischem Landbau\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftsz...  \n",
       "ERW046     Arbeitskräfte\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nArbeitskräfte in landwirtschaftlic...  \n",
       "LST009     Arbeitsleistung\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nArbeitsleistung\\n \\nDie AK-Einhe...  \n",
       "BTR034     Landwirtschaftliche Betriebe mit Viehhaltung\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n\\nBegriffsinhalt:\\nBeri...  \n",
       "BTR038     Landwirtschaftliche Betriebe mit 100 und mehr GV\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\...  \n",
       "VIE003     Viehbestand\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nGroßvieheinheiten (GV)\\n \\nDie Großv...  \n",
       "BTR010     Landwirtschaftliche Betriebe\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n\\nBegriffsinhalt:\\nBerichtskreis\\n\\nSei...  \n",
       "FLC017     Landwirtschaftlich genutzte Fläche\\n \\n \\nErläuterung für folgende Statistik(en):\\n41141 Landwirtschaftszählung\\n \\nBegriffsinhalt:\\nLandwirtschaf...  \n",
       "...                                                                                                                                                          ...  \n",
       "STEU09     Gewerbesteuerumlage\\n \\n \\nErläuterung für folgende Statistik(en):\\n71231 Realsteuervergleich\\n \\nBegriffsinhalt:\\nGewerbesteuerumlage\\n \\nDie Gem...  \n",
       "EKM006     verfügbares Einkommen der privaten Haushalte\\n \\n \\nErläuterung für folgende Statistik(en):\\n82411 VGR der Länder - Umverteilungsrechnung\\n \\nBegr...  \n",
       "EKM014     verfügbares Einkommen der privaten Haushalte je Einwohner\\n \\n \\nErläuterung für folgende Statistik(en):\\n82411 VGR der Länder - Umverteilungsrech...  \n",
       "UMS051     Ausbaugewerblicher Umsatz des Vorjahres\\n \\n \\nErläuterung für folgende Statistik(en):\\n44241 Jährliche Erhebung im Ausbaugewerbe und bei Bauträge...  \n",
       "TRM001                                                                           Trockenmasse des aus Abwasserbehandlungsanlagen direkt\\nentsorgten Klärschlamms  \n",
       "VER001     Unfälle (ingesamt)\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nStraßenverkehrs...  \n",
       "VER002     Unfälle mit Personenschaden\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nUnfäll...  \n",
       "VER005     Schwerwiegende Unfälle mit Sachschäden im engeren Sinne\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle...  \n",
       "VER006     Alkoholunfälle ohne Personenschaden und schwerwiegende\\nSachschäden\\n \\nUnfälle, bei denen alle beteiligten Kfz noch fahrbereit\\nwaren und gleichz...  \n",
       "VER009     Getötete Personen\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nGetötete Persone...  \n",
       "VER019     Verletzte Personen\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinhalt:\\nVerletzte Perso...  \n",
       "VER056     Schwerwiegende Unfälle mit Sachschaden\\n \\n \\nErläuterung für folgende Statistik(en):\\n46241 Statistik der Straßenverkehrsunfälle\\n \\nBegriffsinha...  \n",
       "GAST01     Gästeankünfte\\n \\n \\nErläuterung für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nGästeankünfte\\n \\nEs handelt ...  \n",
       "GAST02     Gästeübernachtungen\\n \\n \\nErläuterung für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nGästeübernachtungen\\n \\...  \n",
       "GAST03     Angebotene Gästebetten\\n \\n \\nErläuterungen für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nGästebetten\\n \\nAl...  \n",
       "GAST04     Geöffnete Beherbergungsbetriebe\\n \\n \\nErläuterung für folgende Statistik(en):\\n45412 Monatserhebung im Tourismus\\n \\nBegriffsinhalt:\\nBeherbergun...  \n",
       "GAST05                                                                                                                                                   Missing  \n",
       "VER012     Kraftfahrzeugbestand\\n \\n \\nErläuterung für folgende Statistik(en):\\n46251 Statistik des Kraftfahrzeugbestandes\\n \\nBegriffsinhalt:\\nKraftfahrzeug...  \n",
       "ERW061     Arbeitnehmer im Jahresdurchschnitt (Inlandskonzept)\\n \\n \\nErläuterung für folgende Statistik(en):\\n13312 Erwerbstätigenrechnung des Bundes und de...  \n",
       "ERW063     Erwerbstätige im Jahresdurchschnitt (Inlandskonzept)\\n \\n \\nErläuterung für folgende Statistik(en):\\n13312 Erwerbstätigenrechnung des Bundes und d...  \n",
       "UMS011     Gesamtumsatz des Vorjahres\\n \\n \\nErläuterung für folgende Statistik(en):\\n44231 Ergänzungserhebung im Bauhauptgewerbe\\n \\nBegriffsinhalt:\\nGesamt...  \n",
       "UMS041     Baugewerblicher Umsatz des Vorjahres\\n \\n \\nErläuterung für folgende Statistik(en):\\n44231 Ergänzungserhebung im Bauhauptgewerbe\\n \\nBegriffsinhal...  \n",
       "FLC006     Erläuterung für folgende Statistik(en):\\nFlächenerhebung nach Art der tatsächlichen Nutzung\\n \\nBegriffsinhalt:\\nDie Angaben über die Gebietsfläch...  \n",
       "GEM001     Zahl der Gemeinden\\n \\n \\nErläuterung für folgende Statistik(en):\\n11111 Feststellung des Gebietsstandes\\n \\nBegriffsinhalt:\\nZahl der Gemeinden\\n...  \n",
       "ERT001     Hektarerträge\\n \\n \\nErläuterung für folgende Statistik(en):\\n41241/42146 Erntestatistik\\n \\nBegriffsinhalt:\\nErnteertrag\\n \\nDie Ertragsschätzung...  \n",
       "\n",
       "[492 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display all statistics\n",
    "statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "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>short_description</th>\n",
       "      <th>long_description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>statistic</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AI0209</th>\n",
       "      <td>Lebendgeborene je 10.000 Einwohner</td>\n",
       "      <td>wiki\\n==Lebendgeborene je 10.000 Einwohner==\\n===Aussage===\\nDer Indikator gibt Auskunft darüber, wie viele Neugeborene\\npro Jahr auf 10.000 Einwo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI0211</th>\n",
       "      <td>Geburten-/Gestorbenenüberschuss je 10.000 Einw.</td>\n",
       "      <td>wiki\\n==Geburtenüberschuss (+) bzw. Gestorbenenüberschuss (-) je\\n10.000 Einwohner==\\n===Aussage===\\nDer Geburtensaldo bildet die natürliche Bevöl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI0219</th>\n",
       "      <td>Durchschnittsalter der Mutter bei der Geburt des 1. Kindes</td>\n",
       "      <td>wiki\\n==Durchschnittsalter der Mutter bei der Geburt des 1.\\nlebendgeborenen Kindes==\\n\\n===Aussage=== \\nDer Indikator gibt Auskunft über das durc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEV001</th>\n",
       "      <td>Lebend Geborene</td>\n",
       "      <td>Lebend Geborene\\n \\n \\nErläuterung für folgende Statistik(en):\\n12612 Statistik der Geburten\\n \\nBegriffsinhalt:\\nLebendgeborene\\n \\nLebendgeboren...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEVDG1</th>\n",
       "      <td>Durchschnittsalter der Mutter bei der Geburt</td>\n",
       "      <td>Durchschnittsalter der Mutter bei der Geburt\\n\\n\\nErläuterung für folgende Statistik(en):\\n12612 Statistik der Geburten\\n \\nBegriffsinhalt:\\nDurch...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    short_description  \\\n",
       "statistic                                                               \n",
       "AI0209                             Lebendgeborene je 10.000 Einwohner   \n",
       "AI0211                Geburten-/Gestorbenenüberschuss je 10.000 Einw.   \n",
       "AI0219     Durchschnittsalter der Mutter bei der Geburt des 1. Kindes   \n",
       "BEV001                                                Lebend Geborene   \n",
       "BEVDG1                   Durchschnittsalter der Mutter bei der Geburt   \n",
       "\n",
       "                                                                                                                                                long_description  \n",
       "statistic                                                                                                                                                         \n",
       "AI0209     wiki\\n==Lebendgeborene je 10.000 Einwohner==\\n===Aussage===\\nDer Indikator gibt Auskunft darüber, wie viele Neugeborene\\npro Jahr auf 10.000 Einwo...  \n",
       "AI0211     wiki\\n==Geburtenüberschuss (+) bzw. Gestorbenenüberschuss (-) je\\n10.000 Einwohner==\\n===Aussage===\\nDer Geburtensaldo bildet die natürliche Bevöl...  \n",
       "AI0219     wiki\\n==Durchschnittsalter der Mutter bei der Geburt des 1.\\nlebendgeborenen Kindes==\\n\\n===Aussage=== \\nDer Indikator gibt Auskunft über das durc...  \n",
       "BEV001     Lebend Geborene\\n \\n \\nErläuterung für folgende Statistik(en):\\n12612 Statistik der Geburten\\n \\nBegriffsinhalt:\\nLebendgeborene\\n \\nLebendgeboren...  \n",
       "BEVDG1     Durchschnittsalter der Mutter bei der Geburt\\n\\n\\nErläuterung für folgende Statistik(en):\\n12612 Statistik der Geburten\\n \\nBegriffsinhalt:\\nDurch...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find out the name of the desired statistic about birth\n",
    "get_statistics().query('long_description.str.contains(\"Statistik der Geburten\")', engine='python')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## General demographic data for Germany (regional)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Population per region\n",
    "- Children per age per region\n",
    "- ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data inspection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Via Ipywidgets Dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Extract statistics_tuple\n",
    "statistics_tuple = get_statistics().reset_index().loc[:,[\"short_description\", \"statistic\"]]\n",
    "statistics_tuple = sorted([tuple(x) for x in statistics_tuple.to_numpy()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8d386149c978455b9f5053e3537044d4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "interactive(children=(Dropdown(description='nuts_level', options=(('NUTS 1', \"'nuts1'\"), ('NUTS 2', \"'nuts2'\")…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Define nuts_selector\n",
    "def nuts_selector(nuts_level):\n",
    "    \n",
    "    regions = get_regions().query(f\"level == {nuts_level}\").reset_index()[[\"name\", \"region_id\"]]\n",
    "    regions = sorted([tuple(x) for x in regions.to_numpy()])\n",
    "\n",
    "    @interact\n",
    "    def get_statistics_dashboard(region=regions, statistic=statistics_tuple):\n",
    "\n",
    "        q = Query.region(region)\n",
    "\n",
    "        try:\n",
    "            field = q.add_field(statistic)\n",
    "        except KeyError as e:\n",
    "            return(f\"Statistic not available: {e}\")\n",
    "        \n",
    "        return q.results()\n",
    "\n",
    "interact(nuts_selector, nuts_level=[('NUTS 1', \"'nuts1'\"), ('NUTS 2', \"'nuts2'\"), ('NUTS 3', \"'nuts3'\")]);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Via Keyword Search Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>short_description</th>\n",
       "      <th>long_description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>statistic</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>KIND35</th>\n",
       "      <td>Kinder</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND37</th>\n",
       "      <td>Anteil der Kinder mit ausl. Herkunft mind. eines Elternteils</td>\n",
       "      <td>Anteil der Kinder mit ausländischer Herkunft mindestens\\neines Elternteils\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND38</th>\n",
       "      <td>Ant.d.Kinder m.e.durchgeh. Betreuungszeit v.m.a. 7 Std.p.Betreuungstag</td>\n",
       "      <td>Anteil der Kinder mit einer durchgehenden Betreuungszeit von\\nmehr als 7 Stunden pro Betreuungstag\\n \\n \\nErläuterung für folgende Statistik(en):\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PERS02</th>\n",
       "      <td>Kindertagespflegepersonen</td>\n",
       "      <td>Kindertagespflegepersonen\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n          ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND01</th>\n",
       "      <td>Betreute Kinder</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND33</th>\n",
       "      <td>Betreute Kinder von unter 3 Jahren</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND34</th>\n",
       "      <td>Betreute Kinder von unter 14 Jahren</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND02</th>\n",
       "      <td>Kinder, Herkunft mind. 1 Elternteil ausländisch</td>\n",
       "      <td>Kinder, Herkunft mindestens 1 Elternteil ausländisch\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIND03</th>\n",
       "      <td>Kinder mit Mittagsverpflegung</td>\n",
       "      <td>Kinder mit Mittagsverpflegung\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n      ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI_Z14</th>\n",
       "      <td>Haushalte mit Kindern</td>\n",
       "      <td>wiki\\n==Anteil der Privathaushalte mit Kindern an den\\nPrivathaushalten insgesamt==\\n===Aussage===\\nDer Indikator gibt Auskunft über den Anteil vo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI0301</th>\n",
       "      <td>Anteil betreute Kinder 0-2 Jahre in Tageseinricht.</td>\n",
       "      <td>wiki\\n==Anteil der in Tageseinrichtungen für Kinder betreuten\\nKinder im Alter von 0 bis 2 Jahren an den Kindern gleichen\\nAlters==\\n===Aussage===...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI0302</th>\n",
       "      <td>Anteil betreute Kinder 0-2 Jahre in Tagespflege</td>\n",
       "      <td>wiki\\n==Anteil der in öffentlich geförderter Kindertagespflege\\nbetreuter Kinder im Alter von 0 bis 2 Jahren an den Kindern\\ngleichen Alters==\\n==...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI0303</th>\n",
       "      <td>Anteil betreute Kinder 3-5 Jahre in Tageseinricht.</td>\n",
       "      <td>wiki\\n==Anteil der in Tageseinrichtungen für Kinder betreuten\\nKinder im Alter von 3 bis 5 Jahren an den Kindern\\ngleichen Alters==\\n===Aussage===...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AI0219</th>\n",
       "      <td>Durchschnittsalter der Mutter bei der Geburt des 1. Kindes</td>\n",
       "      <td>wiki\\n==Durchschnittsalter der Mutter bei der Geburt des 1.\\nlebendgeborenen Kindes==\\n\\n===Aussage=== \\nDer Indikator gibt Auskunft über das durc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIN002</th>\n",
       "      <td>Tageseinrichtungen für Kinder</td>\n",
       "      <td>Tageseinrichtungen für Kinder\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541 Statistik der Kinder und tätigen Personen in Tagesein-\\n      ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIN004</th>\n",
       "      <td>Einrichtungen mit integrativer Kinderbetreuung</td>\n",
       "      <td>Einrichtungen mit integrativer Kinderbetreuung\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541 Statistik der Kinder und tätigen Personen in ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEVMK3</th>\n",
       "      <td>Von der Scheidung betroffene Kinder</td>\n",
       "      <td>Missing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                short_description  \\\n",
       "statistic                                                                           \n",
       "KIND35                                                                     Kinder   \n",
       "KIND37               Anteil der Kinder mit ausl. Herkunft mind. eines Elternteils   \n",
       "KIND38     Ant.d.Kinder m.e.durchgeh. Betreuungszeit v.m.a. 7 Std.p.Betreuungstag   \n",
       "PERS02                                                  Kindertagespflegepersonen   \n",
       "KIND01                                                            Betreute Kinder   \n",
       "KIND33                                         Betreute Kinder von unter 3 Jahren   \n",
       "KIND34                                        Betreute Kinder von unter 14 Jahren   \n",
       "KIND02                            Kinder, Herkunft mind. 1 Elternteil ausländisch   \n",
       "KIND03                                              Kinder mit Mittagsverpflegung   \n",
       "AI_Z14                                                      Haushalte mit Kindern   \n",
       "AI0301                         Anteil betreute Kinder 0-2 Jahre in Tageseinricht.   \n",
       "AI0302                            Anteil betreute Kinder 0-2 Jahre in Tagespflege   \n",
       "AI0303                         Anteil betreute Kinder 3-5 Jahre in Tageseinricht.   \n",
       "AI0219                 Durchschnittsalter der Mutter bei der Geburt des 1. Kindes   \n",
       "KIN002                                              Tageseinrichtungen für Kinder   \n",
       "KIN004                             Einrichtungen mit integrativer Kinderbetreuung   \n",
       "BEVMK3                                        Von der Scheidung betroffene Kinder   \n",
       "\n",
       "                                                                                                                                                long_description  \n",
       "statistic                                                                                                                                                         \n",
       "KIND35                                                                                                                                                   Missing  \n",
       "KIND37     Anteil der Kinder mit ausländischer Herkunft mindestens\\neines Elternteils\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik d...  \n",
       "KIND38     Anteil der Kinder mit einer durchgehenden Betreuungszeit von\\nmehr als 7 Stunden pro Betreuungstag\\n \\n \\nErläuterung für folgende Statistik(en):\\...  \n",
       "PERS02     Kindertagespflegepersonen\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n          ...  \n",
       "KIND01                                                                                                                                                   Missing  \n",
       "KIND33                                                                                                                                                   Missing  \n",
       "KIND34                                                                                                                                                   Missing  \n",
       "KIND02     Kinder, Herkunft mindestens 1 Elternteil ausländisch\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen ...  \n",
       "KIND03     Kinder mit Mittagsverpflegung\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541/22543 Statistik der Kinder und tätigen Personen in Ta-\\n      ...  \n",
       "AI_Z14     wiki\\n==Anteil der Privathaushalte mit Kindern an den\\nPrivathaushalten insgesamt==\\n===Aussage===\\nDer Indikator gibt Auskunft über den Anteil vo...  \n",
       "AI0301     wiki\\n==Anteil der in Tageseinrichtungen für Kinder betreuten\\nKinder im Alter von 0 bis 2 Jahren an den Kindern gleichen\\nAlters==\\n===Aussage===...  \n",
       "AI0302     wiki\\n==Anteil der in öffentlich geförderter Kindertagespflege\\nbetreuter Kinder im Alter von 0 bis 2 Jahren an den Kindern\\ngleichen Alters==\\n==...  \n",
       "AI0303     wiki\\n==Anteil der in Tageseinrichtungen für Kinder betreuten\\nKinder im Alter von 3 bis 5 Jahren an den Kindern\\ngleichen Alters==\\n===Aussage===...  \n",
       "AI0219     wiki\\n==Durchschnittsalter der Mutter bei der Geburt des 1.\\nlebendgeborenen Kindes==\\n\\n===Aussage=== \\nDer Indikator gibt Auskunft über das durc...  \n",
       "KIN002     Tageseinrichtungen für Kinder\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541 Statistik der Kinder und tätigen Personen in Tagesein-\\n      ...  \n",
       "KIN004     Einrichtungen mit integrativer Kinderbetreuung\\n \\n \\nErläuterung für folgende Statistik(en):\\n22541 Statistik der Kinder und tätigen Personen in ...  \n",
       "BEVMK3                                                                                                                                                   Missing  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Search get_statistics('KEYWORD')\n",
    "get_statistics('kind')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region_id</th>\n",
       "      <th>statistic</th>\n",
       "      <th>region_name</th>\n",
       "      <th>entries</th>\n",
       "      <th>start_year</th>\n",
       "      <th>end_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>10</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Saarland</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>11</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>12</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>13</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>14</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Sachsen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>15</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>16</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Thüringen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275</th>\n",
       "      <td>01</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>02</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Hamburg</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>03</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>04</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Bremen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>05</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Nordrhein-Westfalen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>06</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Hessen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>07</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Rheinland-Pfalz</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>08</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Baden-Württemberg, Land</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>09</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Bayern</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6787</th>\n",
       "      <td>141</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Chemnitz, Regierungsbezirk</td>\n",
       "      <td>4</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7076</th>\n",
       "      <td>142</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Dresden, Regierungsbezirk</td>\n",
       "      <td>4</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7365</th>\n",
       "      <td>143</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Leipzig, Regierungsbezirk</td>\n",
       "      <td>4</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7656</th>\n",
       "      <td>145</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Chemnitz, Stat. Region</td>\n",
       "      <td>23</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8034</th>\n",
       "      <td>146</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Dresden, Stat. Region</td>\n",
       "      <td>23</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8412</th>\n",
       "      <td>147</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Leipzig, Stat. Region</td>\n",
       "      <td>23</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9624</th>\n",
       "      <td>031</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Braunschweig, Stat. Region</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10039</th>\n",
       "      <td>032</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Hannover, Stat. Region</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10454</th>\n",
       "      <td>033</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Lüneburg, Stat. Region</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237332</th>\n",
       "      <td>09663</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Würzburg</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237745</th>\n",
       "      <td>09671</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Aschaffenburg, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238156</th>\n",
       "      <td>09672</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Bad Kissingen, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238569</th>\n",
       "      <td>09673</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Rhön-Grabfeld, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238982</th>\n",
       "      <td>09674</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Haßberge, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239395</th>\n",
       "      <td>09675</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Kitzingen, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239808</th>\n",
       "      <td>09676</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Miltenberg, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240221</th>\n",
       "      <td>09677</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Main-Spessart, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240634</th>\n",
       "      <td>09678</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Schweinfurt, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241047</th>\n",
       "      <td>09679</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Würzburg, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241460</th>\n",
       "      <td>09761</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Augsburg</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241873</th>\n",
       "      <td>09762</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Kaufbeuren</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242286</th>\n",
       "      <td>09763</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Kempten (Allgäu)</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242699</th>\n",
       "      <td>09764</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Memmingen</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243112</th>\n",
       "      <td>09771</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Aichach-Friedberg, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243525</th>\n",
       "      <td>09772</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Augsburg, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243938</th>\n",
       "      <td>09773</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Dillingen a.d.Donau, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244351</th>\n",
       "      <td>09774</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Günzburg, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244764</th>\n",
       "      <td>09775</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Neu-Ulm, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245177</th>\n",
       "      <td>09776</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Lindau (Bodensee), Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245590</th>\n",
       "      <td>09777</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Ostallgäu, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246003</th>\n",
       "      <td>09778</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Unterallgäu, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246416</th>\n",
       "      <td>09779</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Donau-Ries, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246829</th>\n",
       "      <td>09780</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Oberallgäu, Landkreis</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262733</th>\n",
       "      <td>DG</td>\n",
       "      <td>PERS02</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>25</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>535 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       region_id statistic                     region_name  entries  \\\n",
       "65            10    PERS02                        Saarland       25   \n",
       "95            11    PERS02                          Berlin       25   \n",
       "125           12    PERS02                     Brandenburg       25   \n",
       "155           13    PERS02          Mecklenburg-Vorpommern       25   \n",
       "185           14    PERS02                         Sachsen       25   \n",
       "215           15    PERS02                  Sachsen-Anhalt       25   \n",
       "245           16    PERS02                       Thüringen       25   \n",
       "275           01    PERS02              Schleswig-Holstein       25   \n",
       "305           02    PERS02                         Hamburg       25   \n",
       "335           03    PERS02                   Niedersachsen       25   \n",
       "365           04    PERS02                          Bremen       25   \n",
       "395           05    PERS02             Nordrhein-Westfalen       25   \n",
       "425           06    PERS02                          Hessen       25   \n",
       "455           07    PERS02                 Rheinland-Pfalz       25   \n",
       "485           08    PERS02         Baden-Württemberg, Land       25   \n",
       "515           09    PERS02                          Bayern       25   \n",
       "6787         141    PERS02      Chemnitz, Regierungsbezirk        4   \n",
       "7076         142    PERS02       Dresden, Regierungsbezirk        4   \n",
       "7365         143    PERS02       Leipzig, Regierungsbezirk        4   \n",
       "7656         145    PERS02          Chemnitz, Stat. Region       23   \n",
       "8034         146    PERS02           Dresden, Stat. Region       23   \n",
       "8412         147    PERS02           Leipzig, Stat. Region       23   \n",
       "9624         031    PERS02      Braunschweig, Stat. Region       25   \n",
       "10039        032    PERS02          Hannover, Stat. Region       25   \n",
       "10454        033    PERS02          Lüneburg, Stat. Region       25   \n",
       "...          ...       ...                             ...      ...   \n",
       "237332     09663    PERS02                        Würzburg       25   \n",
       "237745     09671    PERS02        Aschaffenburg, Landkreis       25   \n",
       "238156     09672    PERS02        Bad Kissingen, Landkreis       25   \n",
       "238569     09673    PERS02        Rhön-Grabfeld, Landkreis       25   \n",
       "238982     09674    PERS02             Haßberge, Landkreis       25   \n",
       "239395     09675    PERS02            Kitzingen, Landkreis       25   \n",
       "239808     09676    PERS02           Miltenberg, Landkreis       25   \n",
       "240221     09677    PERS02        Main-Spessart, Landkreis       25   \n",
       "240634     09678    PERS02          Schweinfurt, Landkreis       25   \n",
       "241047     09679    PERS02             Würzburg, Landkreis       25   \n",
       "241460     09761    PERS02                        Augsburg       25   \n",
       "241873     09762    PERS02                      Kaufbeuren       25   \n",
       "242286     09763    PERS02                Kempten (Allgäu)       25   \n",
       "242699     09764    PERS02                       Memmingen       25   \n",
       "243112     09771    PERS02    Aichach-Friedberg, Landkreis       25   \n",
       "243525     09772    PERS02             Augsburg, Landkreis       25   \n",
       "243938     09773    PERS02  Dillingen a.d.Donau, Landkreis       25   \n",
       "244351     09774    PERS02             Günzburg, Landkreis       25   \n",
       "244764     09775    PERS02              Neu-Ulm, Landkreis       25   \n",
       "245177     09776    PERS02    Lindau (Bodensee), Landkreis       25   \n",
       "245590     09777    PERS02            Ostallgäu, Landkreis       25   \n",
       "246003     09778    PERS02          Unterallgäu, Landkreis       25   \n",
       "246416     09779    PERS02           Donau-Ries, Landkreis       25   \n",
       "246829     09780    PERS02           Oberallgäu, Landkreis       25   \n",
       "262733        DG    PERS02                     Deutschland       25   \n",
       "\n",
       "        start_year  end_year  \n",
       "65          2007.0    2019.0  \n",
       "95          2007.0    2019.0  \n",
       "125         2007.0    2019.0  \n",
       "155         2007.0    2019.0  \n",
       "185         2007.0    2019.0  \n",
       "215         2007.0    2019.0  \n",
       "245         2007.0    2019.0  \n",
       "275         2007.0    2019.0  \n",
       "305         2007.0    2019.0  \n",
       "335         2007.0    2019.0  \n",
       "365         2007.0    2019.0  \n",
       "395         2007.0    2019.0  \n",
       "425         2007.0    2019.0  \n",
       "455         2007.0    2019.0  \n",
       "485         2007.0    2019.0  \n",
       "515         2007.0    2019.0  \n",
       "6787        2007.0    2008.0  \n",
       "7076        2007.0    2008.0  \n",
       "7365        2007.0    2008.0  \n",
       "7656        2007.0    2019.0  \n",
       "8034        2007.0    2019.0  \n",
       "8412        2007.0    2019.0  \n",
       "9624        2007.0    2019.0  \n",
       "10039       2007.0    2019.0  \n",
       "10454       2007.0    2019.0  \n",
       "...            ...       ...  \n",
       "237332      2007.0    2019.0  \n",
       "237745      2007.0    2019.0  \n",
       "238156      2007.0    2019.0  \n",
       "238569      2007.0    2019.0  \n",
       "238982      2007.0    2019.0  \n",
       "239395      2007.0    2019.0  \n",
       "239808      2007.0    2019.0  \n",
       "240221      2007.0    2019.0  \n",
       "240634      2007.0    2019.0  \n",
       "241047      2007.0    2019.0  \n",
       "241460      2007.0    2019.0  \n",
       "241873      2007.0    2019.0  \n",
       "242286      2007.0    2019.0  \n",
       "242699      2007.0    2019.0  \n",
       "243112      2007.0    2019.0  \n",
       "243525      2007.0    2019.0  \n",
       "243938      2007.0    2019.0  \n",
       "244351      2007.0    2019.0  \n",
       "244764      2007.0    2019.0  \n",
       "245177      2007.0    2019.0  \n",
       "245590      2007.0    2019.0  \n",
       "246003      2007.0    2019.0  \n",
       "246416      2007.0    2019.0  \n",
       "246829      2007.0    2019.0  \n",
       "262733      2007.0    2019.0  \n",
       "\n",
       "[535 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Search availability_summary('CODE')\n",
    "availability_summary[availability_summary.statistic == \"PERS02\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Via Code Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Define parameters for query\n",
    "#regions = get_regions()\n",
    "#ids = list(regions.index) # Alt.: \"01\", list(regions[regions.parent ==\"130\"].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Query region and code\n",
    "#query = Query.all_regions(nuts=1)\n",
    "#field = query.add_field('AI1801')\n",
    "#result_df = query.results(verbose_statistics=True)\n",
    "#result_df.sort_values('year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# get information on the field\n",
    "#field_info = field\n",
    "#field_info = query.add_field('AI1801')\n",
    "#field_info.get_info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Get all NUTS-3 codes and define timeframe\n",
    "nuts3_codes = pd.DataFrame(get_regions().query('level == \"nuts3\"').name)\n",
    "#years = [2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Create default dataframe on NUTS-3 level (2010-2019)\n",
    "#df_template = pd.concat([nuts3_codes, pd.DataFrame(columns=years)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Material well-being"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NEW: 2014 indicator of progressive behaviour towards role of women/men as parents .....prior: <s>percentage of children in families without an employed adult</s>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Get relevant data\n",
    "#query1 = Query.all_regions(nuts=1)\n",
    "#query1.add_field('AI1801')\n",
    "#result_df1 = query.results(verbose_statistics=True)\n",
    "#result_df1 = result_df1[result_df1.year == 2014].sort_values(\"Elterngeldbezug Vater (AI1801)\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Plot comparison\n",
    "#fig = px.bar(result_df1, x='name', y='Elterngeldbezug Vater (AI1801)', title=\"Anteil Väter an Elterngeldbezug (in %)\")\n",
    "#fig.update_layout(xaxis_showgrid=False, yaxis_showgrid=True)\n",
    "#fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Get data on NUTS3 level\n",
    "mwb_prog = nuts3_query('AI1801')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1mkind:\u001b[0m\n",
      "OBJECT\n",
      "\n",
      "\u001b[1mdescription:\u001b[0m\n",
      "Anteil Schulabgänger mit allgem. Hochschulreife\n",
      "\n",
      "\u001b[1marguments:\u001b[0m\n",
      "\u001b[4myear\u001b[0m: LIST of type SCALAR(Int)\n",
      "\n",
      "\u001b[4mstatistics\u001b[0m: LIST of type ENUM(AI0304Statistics)\n",
      "enum values:\n",
      "R99910: Regionalatlas Deutschland\n",
      "\n",
      "\u001b[1mfields:\u001b[0m\n",
      "id: Interne eindeutige ID\n",
      "year: Jahr des Stichtages\n",
      "value: Wert\n",
      "source: Quellenverweis zur GENESIS Regionaldatenbank\n",
      "\n",
      "\u001b[1menum values:\u001b[0m\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# Get additional information on the field\n",
    "query = Query.all_regions(nuts=1)\n",
    "mwb_prog_info = query.add_field('AI0304')\n",
    "mwb_prog_info.get_info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Save DataFrame\n",
    "mwb_prog.to_pickle(\"data_pickles/share_fathers_with_parental_allowance.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Health and well-being"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NEW: Percentage of children leaving school with \"allgemeine Hochschulreife\" (not included in map)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**VERSION 1: id and year as single columns, one value column**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# Get content through query\n",
    "health_1 = nuts3_query('AI0304')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "health_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "health_1 = health_1.rename(columns={\"Unnamed: 2\": \"id_nuts3\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# Save DataFrame\n",
    "health_1.to_pickle(\"data_pickles/schulabg_mit_hochschulreife.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NEW: Percentage of children leaving school without certificate .....prior: <s>percentage aged 15-19 remaining in education</s> (not included in map)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Anteil der Schulabgänger/-innen allgemeinbildender Schulen ohne Hauptschulabschluss an den Schulabgänger/-innen allgemeinbildender Schulen insgesamt**\n",
    "\n",
    "**Aussage**\n",
    "Der Indikator gibt an, wie viel Prozent aller Schulabgänger die Schule vorzeitig bzw. ohne einen Abschluss beenden und damit die ungünstigsten Voraussetzungen für den Einstieg in die Berufsausbildung haben.\n",
    "\n",
    "**Indikatorberechnung**\n",
    "Für die Berechnung des Indikators Anteil der Schulabgänger/-innen allgemeinbildender Schulen ohne Hauptschulabschluss an den Schulabgänger/-innen allgemeinbildender Schulen insgesamt wird die Zahl der Schulabgänger/-innen allgemeinbildender Schulen ohne Hauptschulabschluss im Jahr durch die Zahl aller Schulabgänger/-innen allgemeinbildender Schulen insgesamt im Jahr dividiert und mit 100 multipliziert.\n",
    "\n",
    "**Herkunftsstatistiken**\n",
    "Der Indikator beruht auf Daten der Statistik der allgemeinbildenden Schulen.\n",
    "\n",
    "**Merkmalsbeschreibungen**\n",
    "Schulen\n",
    "- Im weitesten Sinne gilt als Schule eine Bildungsstätte, -einrichtung oder -anstalt, in der Unterricht nach einem von der zuständigen Aufsichtsbehörde festgesetzten oder genehmigten Lehrplan erteilt wird.\n",
    "- Absolventen/Abgänger ohne Hauptschulabschluss\n",
    "- Hierzu zählen Absolventen/Abgänger aus Haupt-/Volksschulen, Förderschulen, Schulen mit mehreren Bildungsgängen, Gesamtschulen ohne Hauptschulabschluss, sowie Abgänger aus Klassen-/Jahrgangsstufe 7 und 8 (bei Ländern mit 10jähriger Vollzeitschulpflicht auch aus Klassen-/Jahrgangsstufe 9) der Realschulen, Gymnasien, Freien Waldorfschulen und drei- und vierstufigen Wirtschaftsschulen, nach Ableistung der allgemeinen Vollzeitschulpflicht."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# Get content through query\n",
    "health_2 = nuts3_query('AI0305')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# Save DataFrame\n",
    "health_2.to_pickle(\"data_pickles/schulabg_ohne_hauptschulabschl.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "code_folding": []
   },
   "source": [
    "## Behaviours and risks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NEW: 2019 Percentage of <14 and <18yr olds accused of serious assault involving heavy injuries.....prior: <s>percentage of 11, 13 and 15 yearolds involved in fighting in last 12 months</s> (not included in map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Get relevant data\n",
    "#query2 = Query.all_regions(nuts=1)\n",
    "#query2.add_field('AI1801')\n",
    "#result_df2 = query.results(verbose_statistics=True)\n",
    "#result_df2 = result_df2[result_df2.year == 2014].sort_values(\"id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data of alleged crimes from: [BKA 2019](https://www.bka.de/DE/AktuelleInformationen/StatistikenLagebilder/PolizeilicheKriminalstatistik/PKS2019/InteraktiveKarten/06GefaehrlicheSchwereKoerperverletzung/06_GefaehrlicheSchwereKoerperverletzung_node.html) (inputted manually, as only available as PDF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Manually add data of alleged crimes to dataframe\n",
    "#result_df2[\"children_u14_m\"] = [165, 325, 704, 50, 1567, 316, 288, 439, 638, 58, 507, 250, 181, 266, 212, 122]\n",
    "#result_df2[\"children_u14_f\"] = [27, 76, 136, 26, 395, 46, 92, 111, 153, 7, 104, 31, 49, 38, 45, 37]\n",
    "#result_df2[\"young_u18_m\"] = [447, 407, 1621, 211, 4011, 1077, 764, 1538, 1723, 214, 932, 460, 378, 657, 493, 325]\n",
    "#result_df2[\"young_u18_f\"] = [90, 127, 361, 54, 993, 199, 199, 267, 313, 52, 174, 81, 75, 120, 85, 43]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Display result\n",
    "#result_df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional to UNICEF index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Institutions for child welfare/ caretaking, such as the availability of Kitas, sport clubs or educational support (not included in map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Get relevant data\n",
    "result_df3 = pd.read_csv(\"data/einrichtungen_regional.csv\", encoding='unicode_escape', delimiter=\";\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Crime suspects in NUTS-3 regions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Description \"Crime suspects <14 per 1000\"**\n",
    "\n",
    "Number of crime suspects from 0-13 (incl.) years in given NUTS-3 region. Includes all possible types of crimes (please refer to this [list](https://www.bka.de/SharedDocs/Downloads/DE/Publikationen/PolizeilicheKriminalstatistik/2019/Interpretation/01_div_Dok/Straftatenkatalog-Historie.pdf?__blob=publicationFile&v=3) for a full explanation of included crimes). The value shows the negative of crimes per 1000 population (e.g. -1.4 indicates 1.4 suspects per 1000 population in that NUTS-3 region in given year). Data is taken from [BKA](https://www.bka.de/DE/AktuelleInformationen/StatistikenLagebilder/PolizeilicheKriminalstatistik/pks_node.html).\n",
    "\n",
    "**Description \"Crime suspects 14-<18 per 1000\"**\n",
    "\n",
    "Number of crime suspects from 14-17 (incl.) years in given NUTS-3 region. Includes all possible types of crimes (please refer to this [list](https://www.bka.de/SharedDocs/Downloads/DE/Publikationen/PolizeilicheKriminalstatistik/2019/Interpretation/01_div_Dok/Straftatenkatalog-Historie.pdf?__blob=publicationFile&v=3) for a full explanation of included crimes). The value shows the negative of crimes per 1000 population (e.g. -1.4 indicates 1.4 suspects per 1000 population in that NUTS-3 region in given year). Data is taken from [BKA](https://www.bka.de/DE/AktuelleInformationen/StatistikenLagebilder/PolizeilicheKriminalstatistik/pks_node.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get data for suspected criminals and merge to one DataFrame**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Dataset for 2014\n",
    "suspects_2014 = pd.read_csv(\"data/tatverdaechtige_2014.csv\", encoding='unicode_escape', delimiter=\";\", decimal=',')\n",
    "suspects_2014 = suspects_2014[(suspects_2014[\"2\"] == \"Straftaten insgesamt\") & (suspects_2014[\"3\"] == \"X\")][[\"Unnamed: 2\", \"10\", \"13\"]]\n",
    "suspects_2014 = suspects_2014.rename(columns={\"Unnamed: 2\": \"id_nuts3\",\"10\": \"Crime suspects <14 per 1000\", \"13\": \"Crime suspects 14-<18 per 1000\"})\n",
    "suspects_2014[\"year\"] = \"2014\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Mauricio\\anaconda3\\envs\\airbnb42\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning:\n",
      "\n",
      "Columns (0,2,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Loop for remaining years\n",
    "suspects_all = suspects_2014.copy()\n",
    "for year in [\"2015\", \"2016\", \"2017\", \"2018\"]:\n",
    "    suspects_year = pd.read_csv(f\"data/tatverdaechtige_{year}.csv\", encoding='unicode_escape', delimiter=\";\", decimal=',')\n",
    "    suspects_year = suspects_year[(suspects_year[\"2\"] == \"Straftaten insgesamt\") & (suspects_year[\"6\"] == \"X\")][[\"3\", \"13\", \"16\"]]\n",
    "    suspects_year = suspects_year.rename(columns={\"3\": \"id_nuts3\",\"13\": \"Crime suspects <14 per 1000\", \"16\": \"Crime suspects 14-<18 per 1000\"})\n",
    "    suspects_year[\"year\"] = year\n",
    "    suspects_all = suspects_all.append(suspects_year, ignore_index=False, verify_integrity=False, sort=None)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert year to int\n",
    "suspects_all.year = [int(year) for year in suspects_all.year]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Add \"0\" to 4-digit NUTS-3 id\n",
    "suspects_all.id_nuts3 = [\"0\"+el if len(el)==4 else el for el in suspects_all.id_nuts3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get base dataframe and population from query**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Get data on NUTS3 level\n",
    "straftaten = nuts3_query('BEVSTD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Assign dataset\n",
    "straftaten = straftaten_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert year and BEVSTD to int\n",
    "straftaten.year = straftaten.year.astype(int)\n",
    "straftaten[\"Bevölkerungsstand (BEVSTD)\"] = straftaten[\"Bevölkerungsstand (BEVSTD)\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Keep only 2014-2018\n",
    "straftaten = straftaten[straftaten.year > 2013]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Remove duplicates\n",
    "straftaten = straftaten.drop_duplicates([\"id_nuts3\", \"year\"], keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Sort values\n",
    "straftaten = straftaten.sort_values(\"id_nuts3\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Merge datasets and bring in format**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Merge datasets\n",
    "result_df4 = pd.merge(straftaten, suspects_all, how='left', left_on=[\"id_nuts3\", \"year\"], right_on=[\"id_nuts3\", \"year\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert values to string\n",
    "result_df4[\"Crime suspects <14 per 1000\"] = result_df4[\"Crime suspects <14 per 1000\"].astype(str)\n",
    "result_df4[\"Crime suspects 14-<18 per 1000\"] = result_df4[\"Crime suspects 14-<18 per 1000\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Replace \",\"\n",
    "result_df4[\"Crime suspects <14 per 1000\"] = result_df4[\"Crime suspects <14 per 1000\"].apply(lambda x: x.replace(',',''))\n",
    "result_df4[\"Crime suspects 14-<18 per 1000\"] = result_df4[\"Crime suspects 14-<18 per 1000\"].apply(lambda x: x.replace(',',''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Remove nan\n",
    "result_df4 = result_df4[result_df4[\"Crime suspects <14 per 1000\"]!=\"nan\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert back to int\n",
    "result_df4[\"Crime suspects <14 per 1000\"] = result_df4[\"Crime suspects <14 per 1000\"].astype(int)\n",
    "result_df4[\"Crime suspects 14-<18 per 1000\"] = result_df4[\"Crime suspects 14-<18 per 1000\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Calculate values as per 1000 population\n",
    "result_df4[\"Crime suspects <14 per 1000\"] = result_df4[\"Crime suspects <14 per 1000\"] / result_df4[\"Bevölkerungsstand (BEVSTD)\"] * -1000\n",
    "result_df4[\"Crime suspects 14-<18 per 1000\"] = result_df4[\"Crime suspects 14-<18 per 1000\"] / result_df4[\"Bevölkerungsstand (BEVSTD)\"] * -1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Drop BEVSTD\n",
    "result_df4.drop(\n",
    "        [\"Bevölkerungsstand (BEVSTD)\"],\n",
    "        inplace=True,\n",
    "        axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Save DataFrame\n",
    "result_df4.to_pickle(\"data_pickles/crime_suspects_per_1000.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Crime victims in NUTS-3 regions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Description \"Crime victims <14 per 1000\"**\n",
    "\n",
    "Number of crime victims from 0-13 (incl.) years in given NUTS-3 region. Includes all possible types of crimes (please refer to this [list](https://www.bka.de/SharedDocs/Downloads/DE/Publikationen/PolizeilicheKriminalstatistik/2019/Interpretation/01_div_Dok/Straftatenkatalog-Historie.pdf?__blob=publicationFile&v=3) for a full explanation of included crimes). The value shows the negative of victims per 1000 population (e.g. -1.74 indicates 1.74 victims per 1000 population in that NUTS-3 region in given year). Data is taken from [BKA](https://www.bka.de/DE/AktuelleInformationen/StatistikenLagebilder/PolizeilicheKriminalstatistik/pks_node.html).\n",
    "\n",
    "**Description \"Crime victims 14-<18 per 1000\"**\n",
    "\n",
    "Number of crime victims from 14-17 (incl.) years in given NUTS-3 region. Includes all possible types of crimes (please refer to this [list](https://www.bka.de/SharedDocs/Downloads/DE/Publikationen/PolizeilicheKriminalstatistik/2019/Interpretation/01_div_Dok/Straftatenkatalog-Historie.pdf?__blob=publicationFile&v=3) for a full explanation of included crimes). The value shows the negative of victims per 1000 population (e.g. -1.74 indicates 1.74 victims per 1000 population in that NUTS-3 region in given year). Data is taken from [BKA](https://www.bka.de/DE/AktuelleInformationen/StatistikenLagebilder/PolizeilicheKriminalstatistik/pks_node.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get data for suspected criminals and merge to one DataFrame**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Mauricio\\anaconda3\\envs\\airbnb42\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3072: DtypeWarning:\n",
      "\n",
      "Columns (0,2,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Dataset for 2014\n",
    "victims_2014 = pd.read_csv(\"data/opfer_2014.csv\", encoding='unicode_escape', delimiter=\";\", decimal=',')\n",
    "victims_2014 = victims_2014[(victims_2014[\"2\"] == \"Straftaten insgesamt\") & (victims_2014[\"3\"] == \"insg.\")][[\"Unnamed: 2\", \"10\", \"13\"]]\n",
    "victims_2014 = victims_2014.rename(columns={\"Unnamed: 2\": \"id_nuts3\", \"10\": \"Crime victims <14 per 1000\", \"13\": \"Crime victims 14-<18 per 1000\"})\n",
    "victims_2014[\"year\"] = \"2014\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Loop for remaining years\n",
    "victims_all = victims_2014.copy()\n",
    "for year in [\"2015\", \"2016\", \"2017\", \"2018\"]:\n",
    "    victims_year = pd.read_csv(f\"data/opfer_{year}.csv\", encoding='unicode_escape', delimiter=\";\", decimal=',')\n",
    "    victims_year = victims_year[(victims_year[\"2\"] == \"Straftaten insgesamt\") & (victims_year[\"6\"] == \"insg.\")][[\"3\", \"25\", \"34\"]]\n",
    "    victims_year = victims_year.rename(columns={\"3\": \"id_nuts3\",\"25\": \"Crime victims <14 per 1000\", \"34\": \"Crime victims 14-<18 per 1000\"})\n",
    "    victims_year[\"year\"] = year\n",
    "    victims_all = victims_all.append(victims_year, ignore_index=False, verify_integrity=False, sort=None)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert year to int\n",
    "victims_all.year = [int(year) for year in victims_all.year]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Add \"0\" to 4-digit NUTS-3 id\n",
    "victims_all.id_nuts3 = [\"0\"+el if len(el)==4 else el for el in victims_all.id_nuts3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get base dataframe and population from query**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Copy straftaten dataset\n",
    "opfer = straftaten.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Merge datasets and bring in format**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {
    "code_folding": [
     0
    ],
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Merge datasets\n",
    "result_df5 = pd.merge(opfer, victims_all, how='left', left_on=[\"id_nuts3\", \"year\"], right_on=[\"id_nuts3\", \"year\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert values to string\n",
    "result_df5[\"Crime victims <14 per 1000\"] = result_df5[\"Crime victims <14 per 1000\"].astype(str)\n",
    "result_df5[\"Crime victims 14-<18 per 1000\"] = result_df5[\"Crime victims 14-<18 per 1000\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Replace \",\"\n",
    "result_df5[\"Crime victims <14 per 1000\"] = result_df5[\"Crime victims <14 per 1000\"].apply(lambda x: x.replace(',',''))\n",
    "result_df5[\"Crime victims 14-<18 per 1000\"] = result_df5[\"Crime victims 14-<18 per 1000\"].apply(lambda x: x.replace(',',''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Remove nan\n",
    "result_df5 = result_df5[result_df5[\"Crime victims <14 per 1000\"]!=\"nan\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Convert back to int\n",
    "result_df5[\"Crime victims <14 per 1000\"] = result_df5[\"Crime victims <14 per 1000\"].astype(int)\n",
    "result_df5[\"Crime victims 14-<18 per 1000\"] = result_df5[\"Crime victims 14-<18 per 1000\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Calculate values as per 1000 population\n",
    "result_df5[\"Crime victims <14 per 1000\"] = result_df5[\"Crime victims <14 per 1000\"] / result_df5[\"Bevölkerungsstand (BEVSTD)\"] * -1000\n",
    "result_df5[\"Crime victims 14-<18 per 1000\"] = result_df5[\"Crime victims 14-<18 per 1000\"] / result_df5[\"Bevölkerungsstand (BEVSTD)\"] * -1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Drop BEVSTD\n",
    "result_df5.drop(\n",
    "        [\"Bevölkerungsstand (BEVSTD)\"],\n",
    "        inplace=True,\n",
    "        axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "# Save DataFrame\n",
    "result_df5.to_pickle(\"data_pickles/crime_victims_per_1000.pkl\")"
   ]
  }
 ],
 "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.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}