allRegionsAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datenguidepy.query_builder import Query\n",
    "from datenguidepy.query_execution import QueryExecutioner\n",
    "\n",
    "import pandas as pd\n",
    "from functools import partial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from unittest.mock import Mock"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 59s, sys: 3.24 s, total: 3min 2s\n",
      "Wall time: 3min 9s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "def get_all_regions():\n",
    "    def nuts_query(nuts_level):\n",
    "        q = Query.all_regions(nuts=nuts_level)\n",
    "        return q\n",
    "\n",
    "    def lau_query(lau_level):\n",
    "        q = Query.all_regions(lau = lau_level)\n",
    "        return q\n",
    "\n",
    "    qb_all = Query.all_regions()\n",
    "\n",
    "    qe = QueryExecutioner()\n",
    "    all_regions = qe.run_query(qb_all)\n",
    "    r_nuts1 = qe.run_query(nuts_query(1))\n",
    "    r_nuts2 = qe.run_query(nuts_query(2))\n",
    "    r_nuts3 = qe.run_query(nuts_query(3))\n",
    "    r_lau1 = qe.run_query(lau_query(1))\n",
    "    r_lau2 = qe.run_query(lau_query(2))\n",
    "\n",
    "    levels = {\n",
    "    'nuts1':r_nuts1,\n",
    "    'nuts2':r_nuts2,\n",
    "    'nuts3':r_nuts3,\n",
    "    'lau':r_lau1,\n",
    "    # 'lau2':r_lau2\n",
    "    }\n",
    "\n",
    "    def isAnscestor(region_id,candidate):\n",
    "        return region_id.startswith(candidate) and candidate != region_id\n",
    "\n",
    "\n",
    "    def parent(region_id,region_details):\n",
    "        desc =  region_details.assign(ansc = lambda df:df.index.map(lambda i: isAnscestor(region_id,i))).query('ansc')\n",
    "        max_lev = desc.level.max()\n",
    "        parent_frame = desc.query('level == @max_lev')\n",
    "        if not parent_frame.empty:\n",
    "            return parent_frame.iloc[0,:].name\n",
    "        else:\n",
    "            None\n",
    "\n",
    "    all_regions_df = pd.concat([pd.DataFrame(page['data']['allRegions']['regions']) for page in all_regions[0].query_results]).set_index('id')\n",
    "\n",
    "    level_df = pd.concat(pd.concat([pd.DataFrame(page['data']['allRegions']['regions']) for page in levels[k][0].query_results]).assign(level = k) for k in levels)\n",
    "\n",
    "    all_rg_parents= (all_regions_df.join(\n",
    "        level_df.set_index('id').loc[:,'level']\n",
    "        ).assign(parent = lambda df: df.index.map(partial(parent,region_details = all_regions_df.assign(level = lambda df:df.index.map(len)))))\n",
    "    )\n",
    "    all_rg_parents.loc[all_rg_parents.level == 'nuts1','parent'] = 'DG'\n",
    "    \n",
    "    return all_rg_parents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'query ($page : Int, $itemsPerPage : Int) {allRegions (page: $page, itemsPerPage: $itemsPerPage){regions {id name }page itemsPerPage total }}'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qb_all.get_graphql_query()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Saarland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Brandenburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Sachsen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name\n",
       "id                        \n",
       "10                Saarland\n",
       "11                  Berlin\n",
       "12             Brandenburg\n",
       "13  Mecklenburg-Vorpommern\n",
       "14                 Sachsen"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_regions_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Sachsen</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name  level\n",
       "id                               \n",
       "10                Saarland      2\n",
       "11                  Berlin      2\n",
       "12             Brandenburg      2\n",
       "13  Mecklenburg-Vorpommern      2\n",
       "14                 Sachsen      2"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ar_detail.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name  level parent\n",
       "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"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_rg_parents.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>Saarland</td>\n",
       "      <td>nuts1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>nuts1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>nuts1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>nuts1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>Sachsen</td>\n",
       "      <td>nuts1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                    name  level\n",
       "0  10                Saarland  nuts1\n",
       "1  11                  Berlin  nuts1\n",
       "2  12             Brandenburg  nuts1\n",
       "3  13  Mecklenburg-Vorpommern  nuts1\n",
       "4  14                 Sachsen  nuts1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "level_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "    def isDescendent(region_id,candidate):\n",
    "        return candidate.startswith(region_id) and candidate != region_id\n",
    "\n",
    "    def children(region_id,region_details):\n",
    "        desc =  region_details.assign(desc = lambda df:df.index.map(lambda i: isDescendent(region_id,i))).query('desc')\n",
    "        min_lev = desc.level.min()\n",
    "        return desc.query('level == @min_lev')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_regions_df.join(level_df.set_index('id').loc[:,'level']).to_csv('regions.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nuts1       16\n",
       "nuts2       35\n",
       "nuts3      483\n",
       "lau1     13963\n",
       "lau2     13963\n",
       "Name: level, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "level_df.level.value_counts(ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This does not correspond to the current german nuts. These changed in the past however. One shoudl try to make a query with statistics from 2018 or later and do the same analysis again, to see whether one gets an up to date view of the nuts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2        16\n",
       "3        35\n",
       "5       483\n",
       "11     1734\n",
       "10     5476\n",
       "8     20716\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "level_df.id.str.len().value_counts(ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "level\n",
       "lau1     (8, 11)\n",
       "lau2     (8, 11)\n",
       "nuts1     (2, 2)\n",
       "nuts2     (3, 3)\n",
       "nuts3     (5, 5)\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "level_df.groupby('level').apply(lambda df: (df.id.str.len().min(),df.id.str.len().max()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(180, 4)\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>name_x</th>\n",
       "      <th>name_y</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9561</th>\n",
       "      <td>DG</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16316</th>\n",
       "      <td>0713101</td>\n",
       "      <td>Adenau</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16391</th>\n",
       "      <td>0713102</td>\n",
       "      <td>Altenahr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16416</th>\n",
       "      <td>0713103</td>\n",
       "      <td>Bad Breisig</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16425</th>\n",
       "      <td>0713104</td>\n",
       "      <td>Brohltal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            id       name_x name_y level\n",
       "9561        DG  Deutschland    NaN   NaN\n",
       "16316  0713101       Adenau    NaN   NaN\n",
       "16391  0713102     Altenahr    NaN   NaN\n",
       "16416  0713103  Bad Breisig    NaN   NaN\n",
       "16425  0713104     Brohltal    NaN   NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_level = all_regions_df.merge(level_df,on='id',how='left').query('level.isna()')\n",
    "print(no_level.shape)\n",
    "no_level.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 46s, sys: 2.44 s, total: 2min 49s\n",
      "Wall time: 2min 49s\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_rg_parents.to_csv('regions.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "def hirachy_up(lowestids,hirachy_frame = all_rg_parents):\n",
    "    anscestors = []\n",
    "    current_ids = lowestids\n",
    "    while len(current_ids)>0:\n",
    "        current_regions = hirachy_frame.query('index.isin(@current_ids)')\n",
    "        anscestors.append(current_regions)\n",
    "        current_ids = current_regions.dropna().parent.unique()\n",
    "    return pd.concat(anscestors).sort_index()\n",
    "\n",
    "def hirachy_down(highest_ids,lowest_level='lau',hirachy_frame = all_rg_parents):\n",
    "    descendents = [hirachy_frame.query('index.isin(@highest_ids)')]\n",
    "    current_ids = highest_ids\n",
    "    while len(current_ids)>0:\n",
    "        current_regions = hirachy_frame.query('parent.isin(@current_ids)')\n",
    "        descendents.append(current_regions)\n",
    "        current_ids = current_regions.dropna().index.unique()\n",
    "#         print(current_regions)\n",
    "#         print(current_regions.level.unique())\n",
    "        if lowest_level in current_regions.level.unique():\n",
    "            break\n",
    "    return pd.concat(descendents).sort_index()\n",
    "\n",
    "def siblings(region_id,hirachy_frame = all_rg_parents):\n",
    "    parent = hirachy_frame.query('index == @region_id').loc[:,'parent'].iloc[0]\n",
    "    return hirachy_frame.query('parent == @parent')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>051</th>\n",
       "      <td>Düsseldorf</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>053</th>\n",
       "      <td>Köln</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>055</th>\n",
       "      <td>Münster</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>057</th>\n",
       "      <td>Detmold</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>059</th>\n",
       "      <td>Arnsberg</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name  level parent\n",
       "id                           \n",
       "051  Düsseldorf  nuts2     05\n",
       "053        Köln  nuts2     05\n",
       "055     Münster  nuts2     05\n",
       "057     Detmold  nuts2     05\n",
       "059    Arnsberg  nuts2     05"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings('051')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>DG</th>\n",
       "      <td>Deutschland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</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>051</th>\n",
       "      <td>Düsseldorf</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name  level parent\n",
       "id                                    \n",
       "DG           Deutschland    NaN   None\n",
       "05   Nordrhein-Westfalen  nuts1     DG\n",
       "051           Düsseldorf  nuts2     05"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hirachy_up(['051']).sort_values(['parent','level'])\n",
    "\n",
    "dg_map = lambda x: x if x != 'DG' else ''\n",
    "\n",
    "hu = hirachy_up(['051'])\n",
    "hu.assign(sort_col = lambda df: df.index.map(dg_map)).sort_values('sort_col').drop('sort_col',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\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>051</th>\n",
       "      <td>Düsseldorf</td>\n",
       "      <td>nuts2</td>\n",
       "      <td>05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05111</th>\n",
       "      <td>Düsseldorf</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05111000</th>\n",
       "      <td>Düsseldorf</td>\n",
       "      <td>lau</td>\n",
       "      <td>05111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05112</th>\n",
       "      <td>Duisburg</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05112000</th>\n",
       "      <td>Duisburg</td>\n",
       "      <td>lau</td>\n",
       "      <td>05112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05113</th>\n",
       "      <td>Essen</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05113000</th>\n",
       "      <td>Essen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05114</th>\n",
       "      <td>Krefeld</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05114000</th>\n",
       "      <td>Krefeld</td>\n",
       "      <td>lau</td>\n",
       "      <td>05114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05116</th>\n",
       "      <td>Mönchengladbach</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05116000</th>\n",
       "      <td>Mönchengladbach</td>\n",
       "      <td>lau</td>\n",
       "      <td>05116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05117</th>\n",
       "      <td>Mülheim an der Ruhr</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05117000</th>\n",
       "      <td>Mülheim an der Ruhr</td>\n",
       "      <td>lau</td>\n",
       "      <td>05117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05119</th>\n",
       "      <td>Oberhausen</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05119000</th>\n",
       "      <td>Oberhausen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05120</th>\n",
       "      <td>Remscheid</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05120000</th>\n",
       "      <td>Remscheid</td>\n",
       "      <td>lau</td>\n",
       "      <td>05120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05122</th>\n",
       "      <td>Solingen</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05122000</th>\n",
       "      <td>Solingen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05124</th>\n",
       "      <td>Wuppertal</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05124000</th>\n",
       "      <td>Wuppertal</td>\n",
       "      <td>lau</td>\n",
       "      <td>05124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154</th>\n",
       "      <td>Kleve</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154004</th>\n",
       "      <td>Bedburg-Hau</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154008</th>\n",
       "      <td>Emmerich am Rhein</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154012</th>\n",
       "      <td>Geldern</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154016</th>\n",
       "      <td>Goch</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154020</th>\n",
       "      <td>Issum</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154024</th>\n",
       "      <td>Kalkar</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05154028</th>\n",
       "      <td>Kerken</td>\n",
       "      <td>lau</td>\n",
       "      <td>05154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05970032</th>\n",
       "      <td>Netphen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05970036</th>\n",
       "      <td>Neunkirchen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05970040</th>\n",
       "      <td>Siegen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05970044</th>\n",
       "      <td>Wilnsdorf</td>\n",
       "      <td>lau</td>\n",
       "      <td>05970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974</th>\n",
       "      <td>Soest</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974004</th>\n",
       "      <td>Anröchte</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974008</th>\n",
       "      <td>Bad Sassendorf</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974012</th>\n",
       "      <td>Ense</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974016</th>\n",
       "      <td>Erwitte</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974020</th>\n",
       "      <td>Geseke</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974024</th>\n",
       "      <td>Lippetal</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974028</th>\n",
       "      <td>Lippstadt</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974032</th>\n",
       "      <td>Möhnesee</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974036</th>\n",
       "      <td>Rüthen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974040</th>\n",
       "      <td>Soest</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974044</th>\n",
       "      <td>Warstein</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974048</th>\n",
       "      <td>Welver</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974052</th>\n",
       "      <td>Werl</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05974056</th>\n",
       "      <td>Wickede (Ruhr)</td>\n",
       "      <td>lau</td>\n",
       "      <td>05974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978</th>\n",
       "      <td>Unna</td>\n",
       "      <td>nuts3</td>\n",
       "      <td>059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978004</th>\n",
       "      <td>Bergkamen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978008</th>\n",
       "      <td>Bönen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978012</th>\n",
       "      <td>Fröndenberg/Ruhr</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978016</th>\n",
       "      <td>Holzwickede</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978020</th>\n",
       "      <td>Kamen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978024</th>\n",
       "      <td>Lünen</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978028</th>\n",
       "      <td>Schwerte</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978032</th>\n",
       "      <td>Selm</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978036</th>\n",
       "      <td>Unna</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05978040</th>\n",
       "      <td>Werne</td>\n",
       "      <td>lau</td>\n",
       "      <td>05978</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>465 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name  level parent\n",
       "id                                         \n",
       "05        Nordrhein-Westfalen  nuts1     DG\n",
       "051                Düsseldorf  nuts2     05\n",
       "05111              Düsseldorf  nuts3    051\n",
       "05111000           Düsseldorf    lau  05111\n",
       "05112                Duisburg  nuts3    051\n",
       "...                       ...    ...    ...\n",
       "05978024                Lünen    lau  05978\n",
       "05978028             Schwerte    lau  05978\n",
       "05978032                 Selm    lau  05978\n",
       "05978036                 Unna    lau  05978\n",
       "05978040                Werne    lau  05978\n",
       "\n",
       "[465 rows x 3 columns]"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hirachy_down(['05'],None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>05911</th>\n",
       "      <td>Bochum</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name  level\n",
       "id                  \n",
       "05911  Bochum      5"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ar_detail.query('id == \"05911\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "parent_rel = ar_detail.reset_index().assign(parent = lambda df: df.id.map(parent))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_rg_parents.loc[all_rg_parents.level == 'nuts1','parent'] = 'DG'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "def children(region_id):\n",
    "    return parent_rel.query('parent == @region_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>16051</td>\n",
       "      <td>Erfurt</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>16052</td>\n",
       "      <td>Gera</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>16053</td>\n",
       "      <td>Jena</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>16054</td>\n",
       "      <td>Suhl</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>16055</td>\n",
       "      <td>Weimar</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>16056</td>\n",
       "      <td>Eisenach</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>16061</td>\n",
       "      <td>Eichsfeld</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>16062</td>\n",
       "      <td>Nordhausen</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>16063</td>\n",
       "      <td>Wartburgkreis</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>16064</td>\n",
       "      <td>Unstrut-Hainich-Kreis</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>16065</td>\n",
       "      <td>Kyffhäuserkreis</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>16066</td>\n",
       "      <td>Schmalkalden-Meiningen</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>16067</td>\n",
       "      <td>Gotha</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>16068</td>\n",
       "      <td>Sömmerda</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>16069</td>\n",
       "      <td>Hildburghausen</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>16070</td>\n",
       "      <td>Ilm-Kreis</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>16071</td>\n",
       "      <td>Weimarer Land</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>16072</td>\n",
       "      <td>Sonneberg</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>16073</td>\n",
       "      <td>Saalfeld-Rudolstadt</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>16074</td>\n",
       "      <td>Saale-Holzland-Kreis</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>16075</td>\n",
       "      <td>Saale-Orla-Kreis</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>16076</td>\n",
       "      <td>Greiz</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>16077</td>\n",
       "      <td>Altenburger Land</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                    name  level parent\n",
       "156  16051                  Erfurt      5     16\n",
       "157  16052                    Gera      5     16\n",
       "158  16053                    Jena      5     16\n",
       "159  16054                    Suhl      5     16\n",
       "160  16055                  Weimar      5     16\n",
       "161  16056                Eisenach      5     16\n",
       "162  16061               Eichsfeld      5     16\n",
       "163  16062              Nordhausen      5     16\n",
       "164  16063           Wartburgkreis      5     16\n",
       "165  16064   Unstrut-Hainich-Kreis      5     16\n",
       "166  16065         Kyffhäuserkreis      5     16\n",
       "167  16066  Schmalkalden-Meiningen      5     16\n",
       "168  16067                   Gotha      5     16\n",
       "169  16068                Sömmerda      5     16\n",
       "170  16069          Hildburghausen      5     16\n",
       "171  16070               Ilm-Kreis      5     16\n",
       "172  16071           Weimarer Land      5     16\n",
       "173  16072               Sonneberg      5     16\n",
       "174  16073     Saalfeld-Rudolstadt      5     16\n",
       "175  16074    Saale-Holzland-Kreis      5     16\n",
       "176  16075        Saale-Orla-Kreis      5     16\n",
       "177  16076                   Greiz      5     16\n",
       "178  16077        Altenburger Land      5     16"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "children('16')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "datenguide",
   "language": "python",
   "name": "datenguide"
  },
  "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.7.4+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}