get_all_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import re\n",
    "\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.max_rows', 1862)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get year from file name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_numbers_from_filename(filename):\n",
    "    return re.search(r'\\d+', filename).group(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add year as column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df[\"year\"] = get_numbers_from_filename(filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get whole trager name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_whole_traeger_name(df):\n",
    "    df['T_Name1'] = df['T_Name1'].astype(str) + ', ' + \\\n",
    "                       df['T_Name2'].astype(str) + ', ' + \\\n",
    "                       df['T_Name3'].astype(str) + ', ' + \\\n",
    "                       df['T_Name4'].astype(str)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1992"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1992 = pd.read_excel('KHV_1992.xls', dtype='object')"
   ]
  },
  {
   "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>LAND</th>\n",
       "      <th>REGBEZ</th>\n",
       "      <th>KREIS</th>\n",
       "      <th>GEMEINDE</th>\n",
       "      <th>NAME1</th>\n",
       "      <th>NAME2</th>\n",
       "      <th>NAME3</th>\n",
       "      <th>NAME4</th>\n",
       "      <th>STRASSE</th>\n",
       "      <th>PLZ</th>\n",
       "      <th>ORT</th>\n",
       "      <th>TELEFON</th>\n",
       "      <th>TRAEGER</th>\n",
       "      <th>NAMET1</th>\n",
       "      <th>NAMET2</th>\n",
       "      <th>NAMET3</th>\n",
       "      <th>NAMET4</th>\n",
       "      <th>ART</th>\n",
       "      <th>BETTEN_P</th>\n",
       "      <th>BETTEN_I</th>\n",
       "      <th>AUGEN_P</th>\n",
       "      <th>AUGEN_I</th>\n",
       "      <th>CHIR_P</th>\n",
       "      <th>CHIR_I</th>\n",
       "      <th>C_GEF_P</th>\n",
       "      <th>C_GEF_I</th>\n",
       "      <th>C_KIN_P</th>\n",
       "      <th>C_KIN_I</th>\n",
       "      <th>C_PLA_P</th>\n",
       "      <th>C_PLA_I</th>\n",
       "      <th>C_THOR_P</th>\n",
       "      <th>C_THOR_I</th>\n",
       "      <th>C_UNF_P</th>\n",
       "      <th>C_UNF_I</th>\n",
       "      <th>FRAUEN_P</th>\n",
       "      <th>FRAUEN_I</th>\n",
       "      <th>F_FRAU_P</th>\n",
       "      <th>F_FRAU_I</th>\n",
       "      <th>F_GEBURT</th>\n",
       "      <th>F_GEBUR0</th>\n",
       "      <th>HNO_P</th>\n",
       "      <th>HNO_I</th>\n",
       "      <th>HUG_P</th>\n",
       "      <th>HUG_I</th>\n",
       "      <th>INNERE_P</th>\n",
       "      <th>INNERE_I</th>\n",
       "      <th>I_ENDO_P</th>\n",
       "      <th>I_ENDO_I</th>\n",
       "      <th>I_GAST_P</th>\n",
       "      <th>I_GAST_I</th>\n",
       "      <th>I_HAEM_P</th>\n",
       "      <th>I_HAEM_I</th>\n",
       "      <th>I_KARD_P</th>\n",
       "      <th>I_KARD_I</th>\n",
       "      <th>I_LUB_P</th>\n",
       "      <th>I_LUB_I</th>\n",
       "      <th>I_NEPH_P</th>\n",
       "      <th>I_NEPH_I</th>\n",
       "      <th>I_RHEUM_</th>\n",
       "      <th>I_RHEUM1</th>\n",
       "      <th>I_GER_P</th>\n",
       "      <th>I_GER_I</th>\n",
       "      <th>KINDER_P</th>\n",
       "      <th>KINDER_I</th>\n",
       "      <th>KJPSYCH_</th>\n",
       "      <th>KJPSYCH2</th>\n",
       "      <th>MKG_P</th>\n",
       "      <th>MKG_I</th>\n",
       "      <th>NEURCH_P</th>\n",
       "      <th>NEURCH_I</th>\n",
       "      <th>NEUROL_P</th>\n",
       "      <th>NEUROL_I</th>\n",
       "      <th>NUKLEAR_</th>\n",
       "      <th>NUKLEAR3</th>\n",
       "      <th>ORTH_P</th>\n",
       "      <th>ORTH_I</th>\n",
       "      <th>O_RHEU_P</th>\n",
       "      <th>O_RHEU_I</th>\n",
       "      <th>PSYCH_P</th>\n",
       "      <th>PSYCH_I</th>\n",
       "      <th>P_SUCHT_</th>\n",
       "      <th>P_SUCHT4</th>\n",
       "      <th>PSYSOM_P</th>\n",
       "      <th>PSYSOM_I</th>\n",
       "      <th>STRAHL_P</th>\n",
       "      <th>STRAHL_I</th>\n",
       "      <th>UROL_P</th>\n",
       "      <th>UROL_I</th>\n",
       "      <th>SONST_P</th>\n",
       "      <th>SONST_I</th>\n",
       "      <th>TN_GER</th>\n",
       "      <th>TN_KJPSY</th>\n",
       "      <th>TN_PSYCH</th>\n",
       "      <th>TN_PSYSO</th>\n",
       "      <th>TN_SONST</th>\n",
       "      <th>TN_SUM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1848</th>\n",
       "      <td>09</td>\n",
       "      <td>7</td>\n",
       "      <td>62</td>\n",
       "      <td>000</td>\n",
       "      <td>Chirurgische und Gyn„kolo-</td>\n",
       "      <td>gische Privatklinik Jessenius</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Markgrafenstraáe 24</td>\n",
       "      <td>87600</td>\n",
       "      <td>Kaufbeuren</td>\n",
       "      <td>(08341)-41660</td>\n",
       "      <td>3</td>\n",
       "      <td>Dr. med. Kralicek</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LAND REGBEZ KREIS GEMEINDE                       NAME1  \\\n",
       "1848   09      7    62      000  Chirurgische und Gyn„kolo-   \n",
       "\n",
       "                              NAME2 NAME3 NAME4              STRASSE    PLZ  \\\n",
       "1848  gische Privatklinik Jessenius   NaN   NaN  Markgrafenstraáe 24  87600   \n",
       "\n",
       "             ORT        TELEFON TRAEGER             NAMET1 NAMET2 NAMET3  \\\n",
       "1848  Kaufbeuren  (08341)-41660       3  Dr. med. Kralicek    NaN    NaN   \n",
       "\n",
       "     NAMET4 ART BETTEN_P BETTEN_I AUGEN_P AUGEN_I CHIR_P CHIR_I C_GEF_P  \\\n",
       "1848    NaN   4        0        4       0       0      0      2       0   \n",
       "\n",
       "     C_GEF_I C_KIN_P C_KIN_I C_PLA_P C_PLA_I C_THOR_P C_THOR_I C_UNF_P  \\\n",
       "1848       2       0       0       0       0        0        0       0   \n",
       "\n",
       "     C_UNF_I FRAUEN_P FRAUEN_I F_FRAU_P F_FRAU_I F_GEBURT F_GEBUR0 HNO_P  \\\n",
       "1848       0        0        2        0        0        0        0     0   \n",
       "\n",
       "     HNO_I HUG_P HUG_I INNERE_P INNERE_I I_ENDO_P I_ENDO_I I_GAST_P I_GAST_I  \\\n",
       "1848     0     0     0        0        0        0        0        0        0   \n",
       "\n",
       "     I_HAEM_P I_HAEM_I I_KARD_P I_KARD_I I_LUB_P I_LUB_I I_NEPH_P I_NEPH_I  \\\n",
       "1848        0        0        0        0       0       0        0        0   \n",
       "\n",
       "     I_RHEUM_ I_RHEUM1 I_GER_P I_GER_I KINDER_P KINDER_I KJPSYCH_ KJPSYCH2  \\\n",
       "1848        0        0       0       0        0        0        0        0   \n",
       "\n",
       "     MKG_P MKG_I NEURCH_P NEURCH_I NEUROL_P NEUROL_I NUKLEAR_ NUKLEAR3 ORTH_P  \\\n",
       "1848     0     0        0        0        0        0        0        0      0   \n",
       "\n",
       "     ORTH_I O_RHEU_P O_RHEU_I PSYCH_P PSYCH_I P_SUCHT_ P_SUCHT4 PSYSOM_P  \\\n",
       "1848      0        0        0       0       0        0        0        0   \n",
       "\n",
       "     PSYSOM_I STRAHL_P STRAHL_I UROL_P UROL_I SONST_P SONST_I TN_GER TN_KJPSY  \\\n",
       "1848        0        0        0      0      0       0       0      0        0   \n",
       "\n",
       "     TN_PSYCH TN_PSYSO TN_SONST TN_SUM  \n",
       "1848        0        0        0      0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1992.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['LAND', 'REGBEZ', 'KREIS', 'GEMEINDE', 'NAME1', 'NAME2', 'NAME3',\n",
       "       'NAME4', 'STRASSE', 'PLZ', 'ORT', 'TELEFON', 'TRAEGER', 'NAMET1',\n",
       "       'NAMET2', 'NAMET3', 'NAMET4', 'ART', 'BETTEN_P', 'BETTEN_I', 'AUGEN_P',\n",
       "       'AUGEN_I', 'CHIR_P', 'CHIR_I', 'C_GEF_P', 'C_GEF_I', 'C_KIN_P',\n",
       "       'C_KIN_I', 'C_PLA_P', 'C_PLA_I', 'C_THOR_P', 'C_THOR_I', 'C_UNF_P',\n",
       "       'C_UNF_I', 'FRAUEN_P', 'FRAUEN_I', 'F_FRAU_P', 'F_FRAU_I', 'F_GEBURT',\n",
       "       'F_GEBUR0', 'HNO_P', 'HNO_I', 'HUG_P', 'HUG_I', 'INNERE_P', 'INNERE_I',\n",
       "       'I_ENDO_P', 'I_ENDO_I', 'I_GAST_P', 'I_GAST_I', 'I_HAEM_P', 'I_HAEM_I',\n",
       "       'I_KARD_P', 'I_KARD_I', 'I_LUB_P', 'I_LUB_I', 'I_NEPH_P', 'I_NEPH_I',\n",
       "       'I_RHEUM_', 'I_RHEUM1', 'I_GER_P', 'I_GER_I', 'KINDER_P', 'KINDER_I',\n",
       "       'KJPSYCH_', 'KJPSYCH2', 'MKG_P', 'MKG_I', 'NEURCH_P', 'NEURCH_I',\n",
       "       'NEUROL_P', 'NEUROL_I', 'NUKLEAR_', 'NUKLEAR3', 'ORTH_P', 'ORTH_I',\n",
       "       'O_RHEU_P', 'O_RHEU_I', 'PSYCH_P', 'PSYCH_I', 'P_SUCHT_', 'P_SUCHT4',\n",
       "       'PSYSOM_P', 'PSYSOM_I', 'STRAHL_P', 'STRAHL_I', 'UROL_P', 'UROL_I',\n",
       "       'SONST_P', 'SONST_I', 'TN_GER', 'TN_KJPSY', 'TN_PSYCH', 'TN_PSYSO',\n",
       "       'TN_SONST', 'TN_SUM'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1992.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Prüfen: Steht BETTEN_I für Betten insgesamt?\n",
    "df_1992 = df_1992.rename(columns={'LAND': 'Land', 'KREIS': 'Kreis', 'GEMEINDE': 'Gemeinde', 'NAME1': 'Name1', 'ORT': 'Ort', 'TRAEGER': 'Traeger', 'NAMET1': 'T_Name1', 'NAMET2': 'T_Name2', 'NAMET3': 'T_Name3', 'NAMET4': 'T_Name4', 'ART':'Art', 'BETTEN_I': 'Betten_Ins'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Land', 'REGBEZ', 'Kreis', 'Gemeinde', 'Name1', 'NAME2', 'NAME3',\n",
       "       'NAME4', 'STRASSE', 'PLZ', 'Ort', 'TELEFON', 'Traeger', 'T_Name1',\n",
       "       'T_Name2', 'T_Name3', 'T_Name4', 'Art', 'BETTEN_P', 'Betten_Ins',\n",
       "       'AUGEN_P', 'AUGEN_I', 'CHIR_P', 'CHIR_I', 'C_GEF_P', 'C_GEF_I',\n",
       "       'C_KIN_P', 'C_KIN_I', 'C_PLA_P', 'C_PLA_I', 'C_THOR_P', 'C_THOR_I',\n",
       "       'C_UNF_P', 'C_UNF_I', 'FRAUEN_P', 'FRAUEN_I', 'F_FRAU_P', 'F_FRAU_I',\n",
       "       'F_GEBURT', 'F_GEBUR0', 'HNO_P', 'HNO_I', 'HUG_P', 'HUG_I', 'INNERE_P',\n",
       "       'INNERE_I', 'I_ENDO_P', 'I_ENDO_I', 'I_GAST_P', 'I_GAST_I', 'I_HAEM_P',\n",
       "       'I_HAEM_I', 'I_KARD_P', 'I_KARD_I', 'I_LUB_P', 'I_LUB_I', 'I_NEPH_P',\n",
       "       'I_NEPH_I', 'I_RHEUM_', 'I_RHEUM1', 'I_GER_P', 'I_GER_I', 'KINDER_P',\n",
       "       'KINDER_I', 'KJPSYCH_', 'KJPSYCH2', 'MKG_P', 'MKG_I', 'NEURCH_P',\n",
       "       'NEURCH_I', 'NEUROL_P', 'NEUROL_I', 'NUKLEAR_', 'NUKLEAR3', 'ORTH_P',\n",
       "       'ORTH_I', 'O_RHEU_P', 'O_RHEU_I', 'PSYCH_P', 'PSYCH_I', 'P_SUCHT_',\n",
       "       'P_SUCHT4', 'PSYSOM_P', 'PSYSOM_I', 'STRAHL_P', 'STRAHL_I', 'UROL_P',\n",
       "       'UROL_I', 'SONST_P', 'SONST_I', 'TN_GER', 'TN_KJPSY', 'TN_PSYCH',\n",
       "       'TN_PSYSO', 'TN_SONST', 'TN_SUM'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1992.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1992 = get_whole_traeger_name(df_1992)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1992 = df_1992[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1992[\"year\"] = get_numbers_from_filename(\"KHV_1992.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2350</th>\n",
       "      <td>16</td>\n",
       "      <td>33</td>\n",
       "      <td>210</td>\n",
       "      <td>Interdisziplin„res Therapie-</td>\n",
       "      <td>Weiáen</td>\n",
       "      <td>1</td>\n",
       "      <td>Land Thringen, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde                         Name1     Ort Traeger  \\\n",
       "2350   16    33      210  Interdisziplin„res Therapie-  Weiáen       1   \n",
       "\n",
       "                            T_Name1 Art Betten_Ins  year  \n",
       "2350  Land Thringen, nan, nan, nan   2         50  1992  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1992.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1995"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1995 = pd.read_excel('KHV_1995.XLS', 'KH', dtype='object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Prüfen: Steht BETTEN_I für Betten insgesamt?\n",
    "df_1995 = df_1995.rename(columns={'LAND': 'Land', 'KREIS': 'Kreis', 'GEMEINDE': 'Gemeinde', 'NAME1': 'Name1', 'ORT': 'Ort', 'TRAEGER': 'Traeger', 'NAME1': 'Name1', 'ORT': 'Ort', 'TRAEGER': 'Traeger', 'NAMET1': 'T_Name1', 'NAMET2': 'T_Name2', 'NAMET3': 'T_Name3', 'NAMET4': 'T_Name4', 'ART':'Art', 'BETTEN_I': 'Betten_Ins'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Land', 'REGBEZ', 'Kreis', 'Gemeinde', 'Name1', 'NAME2', 'NAME3',\n",
       "       'NAME4', 'STRASSE', 'PLZ', 'Ort', 'TELEFON', 'Traeger', 'T_Name1',\n",
       "       'T_Name2', 'T_Name3', 'T_Name4', 'Art', 'BETTEN_P', 'Betten_Ins',\n",
       "       'AUGEN_P', 'AUGEN_I', 'CHIR_P', 'CHIR_I', 'C_GEF_P', 'C_GEF_I',\n",
       "       'C_KIN_P', 'C_KIN_I', 'C_PLA_P', 'C_PLA_I', 'C_THOR_P', 'C_THOR_I',\n",
       "       'C_UNF_P', 'C_UNF_I', 'FRAUEN_P', 'FRAUEN_I', 'F_FRAU_P', 'F_FRAU_I',\n",
       "       'F_GEBURT_P', 'F_GEBURT_I', 'HNO_P', 'HNO_I', 'HUG_P', 'HUG_I',\n",
       "       'INNERE_P', 'INNERE_I', 'I_ENDO_P', 'I_ENDO_I', 'I_GAST_P', 'I_GAST_I',\n",
       "       'I_HÄMA_P', 'I_HÄMA_I', 'I_KARD_P', 'I_KARD_I', 'I_LUB_P', 'I_LUB_I',\n",
       "       'I_NEPH_P', 'I_NEPH_I', 'I_RHEUM_P', 'I_RHEUM_I', 'I_GER_P', 'I_GER_I',\n",
       "       'KINDER_P', 'KINDER_I', 'KJPSYCH_P', 'KJPSYCH_I', 'MKG_P', 'MKG_I',\n",
       "       'NEURCH_P', 'NEURCH_I', 'NEUROL_P', 'NEUROL_I', 'NUKLEAR_P',\n",
       "       'NUKLEAR_I', 'ORTH_P', 'ORTH_I', 'O_RHEU_P', 'O_RHEU_I', 'PSYCH_P',\n",
       "       'PSYCH_I', 'P_SUCHT_P', 'P_SUCHT_I', 'PSYSOM_P', 'PSYSOM_I', 'STRAHL_P',\n",
       "       'STRAHL_I', 'UROL_P', 'UROL_I', 'SONST_P', 'SONST_I', 'TN_GER',\n",
       "       'TN_KJPSYCH', 'TN_PSYCH', 'TN_PSYSOM', 'TN_SONST', 'TN_SUM'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1995.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1995 = get_whole_traeger_name(df_1995)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1995 = df_1995[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Land          object\n",
       "Kreis         object\n",
       "Gemeinde      object\n",
       "Name1         object\n",
       "Ort           object\n",
       "Traeger       object\n",
       "T_Name1       object\n",
       "Art           object\n",
       "Betten_Ins    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1995.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1995 = df_1995[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1995[\"year\"] = get_numbers_from_filename(\"KHV_1995.XLS\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>03</td>\n",
       "      <td>01</td>\n",
       "      <td>000</td>\n",
       "      <td>Kliniken</td>\n",
       "      <td>Braunschweig</td>\n",
       "      <td>2</td>\n",
       "      <td>Gemeinnützige Milde Stiftung, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>372</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Land Kreis Gemeinde     Name1           Ort Traeger  \\\n",
       "152   03    01      000  Kliniken  Braunschweig       2   \n",
       "\n",
       "                                         T_Name1 Art Betten_Ins  year  \n",
       "152  Gemeinnützige Milde Stiftung, nan, nan, nan   2        372  1995  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1995.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2003 - 2005"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "filenames = [\n",
    " 'KHV_2003.xls',\n",
    " 'KHV_2004.xls',\n",
    " 'KHV_2005.xls',]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_from_each_file = (pd.read_excel(file, 'KHV', dtype='object') for file in filenames)\n",
    "df_from_each_file = []\n",
    "\n",
    "for file in filenames:\n",
    "    data = pd.read_excel(file, sheet_name=4, dtype='object')\n",
    "    data[\"year\"] = get_numbers_from_filename(file)\n",
    "    data = get_whole_traeger_name(data)\n",
    "    df_from_each_file.append(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2003_2005 = pd.concat(df_from_each_file, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2003_2005 = df_2003_2005[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins', 'year']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1880</th>\n",
       "      <td>11</td>\n",
       "      <td>004</td>\n",
       "      <td>004</td>\n",
       "      <td>Park-Sanatorium Dahlem</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>5</td>\n",
       "      <td>Park-Sanatorium Dahlem GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>52</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428</th>\n",
       "      <td>05</td>\n",
       "      <td>120</td>\n",
       "      <td>000</td>\n",
       "      <td>Stiftung Tannenhof</td>\n",
       "      <td>Remscheid</td>\n",
       "      <td>4</td>\n",
       "      <td>Stiftung Tannenhof, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>603</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3844</th>\n",
       "      <td>09</td>\n",
       "      <td>675</td>\n",
       "      <td>141</td>\n",
       "      <td>Kreiskrankenhaus</td>\n",
       "      <td>Kitzingen</td>\n",
       "      <td>1</td>\n",
       "      <td>Landkreis Kitzingen, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>248</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1255</th>\n",
       "      <td>08</td>\n",
       "      <td>315</td>\n",
       "      <td>015</td>\n",
       "      <td>HELIOS Rosmann Klinik</td>\n",
       "      <td>Breisach</td>\n",
       "      <td>3</td>\n",
       "      <td>HELIOS Kliniken Breisgau-, Hochschwarzwald Gmb...</td>\n",
       "      <td>2</td>\n",
       "      <td>130</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5115</th>\n",
       "      <td>06</td>\n",
       "      <td>431</td>\n",
       "      <td>002</td>\n",
       "      <td>Nachsorgeklinik</td>\n",
       "      <td>Bensheim</td>\n",
       "      <td>4</td>\n",
       "      <td>HDV gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2329</th>\n",
       "      <td>03</td>\n",
       "      <td>158</td>\n",
       "      <td>037</td>\n",
       "      <td>Städtisches Klinikum</td>\n",
       "      <td>Wolfenbüttel</td>\n",
       "      <td>3</td>\n",
       "      <td>Stadt Wolfenbüttel, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>326</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5278</th>\n",
       "      <td>07</td>\n",
       "      <td>137</td>\n",
       "      <td>226</td>\n",
       "      <td>Neurologische Klinik</td>\n",
       "      <td>Vallendar</td>\n",
       "      <td>4</td>\n",
       "      <td>Bundesverband für, Rehabilitation und Interess...</td>\n",
       "      <td>2</td>\n",
       "      <td>80</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1521</th>\n",
       "      <td>09</td>\n",
       "      <td>189</td>\n",
       "      <td>124</td>\n",
       "      <td>Klinik Inzell-Eck</td>\n",
       "      <td>Inzell</td>\n",
       "      <td>5</td>\n",
       "      <td>Dr. med. O. H. Hertrich GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2887</th>\n",
       "      <td>05</td>\n",
       "      <td>916</td>\n",
       "      <td>000</td>\n",
       "      <td>St. Anna-Hospital</td>\n",
       "      <td>Herne</td>\n",
       "      <td>4</td>\n",
       "      <td>St. Vincenz Hospital gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>350</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>01</td>\n",
       "      <td>055</td>\n",
       "      <td>028</td>\n",
       "      <td>Fachklinik Hängebargshorst</td>\n",
       "      <td>Bad Malente</td>\n",
       "      <td>1</td>\n",
       "      <td>Landesversicherungsanstalt, Schleswig-Holstein...</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde                       Name1            Ort Traeger  \\\n",
       "1880   11   004      004      Park-Sanatorium Dahlem         Berlin       5   \n",
       "428    05   120      000          Stiftung Tannenhof      Remscheid       4   \n",
       "3844   09   675      141            Kreiskrankenhaus      Kitzingen       1   \n",
       "1255   08   315      015       HELIOS Rosmann Klinik       Breisach       3   \n",
       "5115   06   431      002             Nachsorgeklinik       Bensheim       4   \n",
       "2329   03   158      037        Städtisches Klinikum   Wolfenbüttel       3   \n",
       "5278   07   137      226        Neurologische Klinik      Vallendar       4   \n",
       "1521   09   189      124           Klinik Inzell-Eck         Inzell       5   \n",
       "2887   05   916      000           St. Anna-Hospital          Herne       4   \n",
       "48     01   055      028  Fachklinik Hängebargshorst    Bad Malente       1   \n",
       "\n",
       "                                                T_Name1 Art Betten_Ins  year  \n",
       "1880         Park-Sanatorium Dahlem GmbH, nan, nan, nan   2         52  2003  \n",
       "428                   Stiftung Tannenhof, nan, nan, nan   2        603  2003  \n",
       "3844                 Landkreis Kitzingen, nan, nan, nan   2        248  2004  \n",
       "1255  HELIOS Kliniken Breisgau-, Hochschwarzwald Gmb...   2        130  2003  \n",
       "5115                           HDV gGmbH, nan, nan, nan   2         53  2005  \n",
       "2329                  Stadt Wolfenbüttel, nan, nan, nan   2        326  2004  \n",
       "5278  Bundesverband für, Rehabilitation und Interess...   2         80  2005  \n",
       "1521        Dr. med. O. H. Hertrich GmbH, nan, nan, nan   2         26  2003  \n",
       "2887          St. Vincenz Hospital gGmbH, nan, nan, nan   2        350  2004  \n",
       "48    Landesversicherungsanstalt, Schleswig-Holstein...   3         15  2003  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2003_2005.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2006"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>06</td>\n",
       "      <td>412</td>\n",
       "      <td>000</td>\n",
       "      <td>Main Klinik</td>\n",
       "      <td>Frankfurt</td>\n",
       "      <td>5</td>\n",
       "      <td>Dres. med., G. Kovacs &amp;, U. Vogel GbR, nan</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1816</th>\n",
       "      <td>12</td>\n",
       "      <td>054</td>\n",
       "      <td>000</td>\n",
       "      <td>St. Josefs-Krankenhaus Potsdam</td>\n",
       "      <td>Potsdam</td>\n",
       "      <td>4</td>\n",
       "      <td>St. Josefhaus Potsdam gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>242</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>02</td>\n",
       "      <td>000</td>\n",
       "      <td>000</td>\n",
       "      <td>Cardio Clinic Hamburg</td>\n",
       "      <td>Hamburg</td>\n",
       "      <td>5</td>\n",
       "      <td>Cardio Clinic Hamburg, Krankenhausgesellschaft...</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1422</th>\n",
       "      <td>09</td>\n",
       "      <td>182</td>\n",
       "      <td>119</td>\n",
       "      <td>Krankenhaus</td>\n",
       "      <td>Hausham</td>\n",
       "      <td>3</td>\n",
       "      <td>Landkreis Miesbach, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>368</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>05</td>\n",
       "      <td>515</td>\n",
       "      <td>000</td>\n",
       "      <td>Fachklinik Hornheide</td>\n",
       "      <td>Münster</td>\n",
       "      <td>4</td>\n",
       "      <td>Fachklinik Hornheide e.V., nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>152</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>667</th>\n",
       "      <td>05</td>\n",
       "      <td>911</td>\n",
       "      <td>000</td>\n",
       "      <td>Augusta-Kranken-Anstalt</td>\n",
       "      <td>Bochum</td>\n",
       "      <td>4</td>\n",
       "      <td>Augusta-Kranken-Anstalt gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>541</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>01</td>\n",
       "      <td>062</td>\n",
       "      <td>005</td>\n",
       "      <td>Heinrich-Sengelmann-Krankenhaus</td>\n",
       "      <td>Bargfeld-Stegen</td>\n",
       "      <td>4</td>\n",
       "      <td>Evangelische Stiftung Alsterdorf, Heinrich-Sen...</td>\n",
       "      <td>2</td>\n",
       "      <td>215</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1122</th>\n",
       "      <td>08</td>\n",
       "      <td>128</td>\n",
       "      <td>007</td>\n",
       "      <td>Reha - Klinik Ob der Tauber</td>\n",
       "      <td>Bad Mergentheim</td>\n",
       "      <td>1</td>\n",
       "      <td>Reha Zentren der Deutschen, Rentenversicherung...</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>975</th>\n",
       "      <td>07</td>\n",
       "      <td>135</td>\n",
       "      <td>092</td>\n",
       "      <td>St. Josef-Krankenhaus Zell</td>\n",
       "      <td>Zell (Mosel)</td>\n",
       "      <td>4</td>\n",
       "      <td>Maria Hilf-, Kranken- und Pflegeanstalt GmbH, ...</td>\n",
       "      <td>2</td>\n",
       "      <td>206</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044</th>\n",
       "      <td>16</td>\n",
       "      <td>070</td>\n",
       "      <td>004</td>\n",
       "      <td>Orthopädische Klinik des</td>\n",
       "      <td>Arnstadt</td>\n",
       "      <td>4</td>\n",
       "      <td>Marienstift Arnstadt, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>115</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde                            Name1               Ort  \\\n",
       "797    06   412      000                      Main Klinik         Frankfurt   \n",
       "1816   12   054      000   St. Josefs-Krankenhaus Potsdam           Potsdam   \n",
       "101    02   000      000            Cardio Clinic Hamburg           Hamburg   \n",
       "1422   09   182      119                      Krankenhaus           Hausham   \n",
       "567    05   515      000             Fachklinik Hornheide           Münster   \n",
       "667    05   911      000          Augusta-Kranken-Anstalt            Bochum   \n",
       "84     01   062      005  Heinrich-Sengelmann-Krankenhaus   Bargfeld-Stegen   \n",
       "1122   08   128      007      Reha - Klinik Ob der Tauber   Bad Mergentheim   \n",
       "975    07   135      092       St. Josef-Krankenhaus Zell      Zell (Mosel)   \n",
       "2044   16   070      004         Orthopädische Klinik des          Arnstadt   \n",
       "\n",
       "     Traeger                                            T_Name1 Art  \\\n",
       "797        5         Dres. med., G. Kovacs &, U. Vogel GbR, nan   4   \n",
       "1816       4         St. Josefhaus Potsdam gGmbH, nan, nan, nan   2   \n",
       "101        5  Cardio Clinic Hamburg, Krankenhausgesellschaft...   2   \n",
       "1422       3                  Landkreis Miesbach, nan, nan, nan   2   \n",
       "567        4           Fachklinik Hornheide e.V., nan, nan, nan   2   \n",
       "667        4       Augusta-Kranken-Anstalt gGmbH, nan, nan, nan   2   \n",
       "84         4  Evangelische Stiftung Alsterdorf, Heinrich-Sen...   2   \n",
       "1122       1  Reha Zentren der Deutschen, Rentenversicherung...   2   \n",
       "975        4  Maria Hilf-, Kranken- und Pflegeanstalt GmbH, ...   2   \n",
       "2044       4                Marienstift Arnstadt, nan, nan, nan   2   \n",
       "\n",
       "     Betten_Ins  year  \n",
       "797           4  2006  \n",
       "1816        242  2006  \n",
       "101          25  2006  \n",
       "1422        368  2006  \n",
       "567         152  2006  \n",
       "667         541  2006  \n",
       "84          215  2006  \n",
       "1122          7  2006  \n",
       "975         206  2006  \n",
       "2044        115  2006  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2006 = pd.read_excel('KHV_2006.xls', sheet_name=4, dtype='object')\n",
    "df_2006[\"year\"] = \"2006\"\n",
    "df_2006 = get_whole_traeger_name(df_2006)\n",
    "df_2006 = df_2006[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins', 'year']]\n",
    "df_2006.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2012"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2012 = pd.read_excel('KHV_2012.xlsx', sheet_name=4, skiprows=4, dtype='object')\n",
    "df_2012[\"year\"] = \"2012\"\n",
    "df_2012 = get_whole_traeger_name(df_2012)\n",
    "df_2012 = df_2012.rename(columns={'Träger': 'Traeger'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1710</th>\n",
       "      <td>12</td>\n",
       "      <td>067</td>\n",
       "      <td>544</td>\n",
       "      <td>Evangelisches Krankenhaus</td>\n",
       "      <td>Woltersdorf</td>\n",
       "      <td>4</td>\n",
       "      <td>Evangelisches Krankenhaus, \"Gottesfriede\" GmbH...</td>\n",
       "      <td>2</td>\n",
       "      <td>159</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1712</th>\n",
       "      <td>12</td>\n",
       "      <td>069</td>\n",
       "      <td>017</td>\n",
       "      <td>Kliniken Beelitz GmbH</td>\n",
       "      <td>Beelitz-Heilstätten</td>\n",
       "      <td>5</td>\n",
       "      <td>Kliniken Beelitz GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>88</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>832</th>\n",
       "      <td>06</td>\n",
       "      <td>633</td>\n",
       "      <td>014</td>\n",
       "      <td>Fachklinik für</td>\n",
       "      <td>Immenhausen</td>\n",
       "      <td>4</td>\n",
       "      <td>Philippstiftung e.V., nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>107</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>02</td>\n",
       "      <td>000</td>\n",
       "      <td>000</td>\n",
       "      <td>Krankenhaus Mariahilf</td>\n",
       "      <td>Hamburg</td>\n",
       "      <td>5</td>\n",
       "      <td>HELIOS Mariahilf Klinik Hamburg, GmbH, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>193</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1060</th>\n",
       "      <td>08</td>\n",
       "      <td>226</td>\n",
       "      <td>013</td>\n",
       "      <td>GRN-Klinik Eberbach</td>\n",
       "      <td>Eberbach</td>\n",
       "      <td>3</td>\n",
       "      <td>Rhein-Neckar-Kreis, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>130</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1248</th>\n",
       "      <td>09</td>\n",
       "      <td>162</td>\n",
       "      <td>000</td>\n",
       "      <td>Artemed-Fachklinik</td>\n",
       "      <td>München</td>\n",
       "      <td>5</td>\n",
       "      <td>Artemed Kliniken GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1108</th>\n",
       "      <td>08</td>\n",
       "      <td>315</td>\n",
       "      <td>113</td>\n",
       "      <td>Klinik Haus Vogt GmbH</td>\n",
       "      <td>Titisee-Neustadt</td>\n",
       "      <td>3</td>\n",
       "      <td>Heidehof Stiftung GmbH, nan, nan, nan</td>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1619</th>\n",
       "      <td>11</td>\n",
       "      <td>003</td>\n",
       "      <td>003</td>\n",
       "      <td>St. Joseph-Krankenhaus</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>4</td>\n",
       "      <td>St. Joseph-Krankenhaus, Berlin-Weißensee GmbH,...</td>\n",
       "      <td>2</td>\n",
       "      <td>237</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>763</th>\n",
       "      <td>06</td>\n",
       "      <td>436</td>\n",
       "      <td>001</td>\n",
       "      <td>Kliniken des Main-</td>\n",
       "      <td>Bad Soden</td>\n",
       "      <td>3</td>\n",
       "      <td>Kliniken des, Main-Taunus-Kreises, GmbH, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>473</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>05</td>\n",
       "      <td>962</td>\n",
       "      <td>032</td>\n",
       "      <td>Krankenhaus für</td>\n",
       "      <td>Lüdenscheid</td>\n",
       "      <td>4</td>\n",
       "      <td>Sporthilfe e.V. /, Sozialwerk des Landessport-...</td>\n",
       "      <td>2</td>\n",
       "      <td>260</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde                      Name1                  Ort  \\\n",
       "1710   12   067      544  Evangelisches Krankenhaus          Woltersdorf   \n",
       "1712   12   069      017      Kliniken Beelitz GmbH  Beelitz-Heilstätten   \n",
       "832    06   633      014             Fachklinik für          Immenhausen   \n",
       "112    02   000      000      Krankenhaus Mariahilf              Hamburg   \n",
       "1060   08   226      013        GRN-Klinik Eberbach             Eberbach   \n",
       "1248   09   162      000         Artemed-Fachklinik              München   \n",
       "1108   08   315      113      Klinik Haus Vogt GmbH     Titisee-Neustadt   \n",
       "1619   11   003      003     St. Joseph-Krankenhaus               Berlin   \n",
       "763    06   436      001         Kliniken des Main-            Bad Soden   \n",
       "682    05   962      032            Krankenhaus für          Lüdenscheid   \n",
       "\n",
       "     Traeger                                            T_Name1 Art  \\\n",
       "1710       4  Evangelisches Krankenhaus, \"Gottesfriede\" GmbH...   2   \n",
       "1712       5               Kliniken Beelitz GmbH, nan, nan, nan   2   \n",
       "832        4                Philippstiftung e.V., nan, nan, nan   2   \n",
       "112        5    HELIOS Mariahilf Klinik Hamburg, GmbH, nan, nan   2   \n",
       "1060       3                  Rhein-Neckar-Kreis, nan, nan, nan   2   \n",
       "1248       5               Artemed Kliniken GmbH, nan, nan, nan   2   \n",
       "1108       3              Heidehof Stiftung GmbH, nan, nan, nan   3   \n",
       "1619       4  St. Joseph-Krankenhaus, Berlin-Weißensee GmbH,...   2   \n",
       "763        3       Kliniken des, Main-Taunus-Kreises, GmbH, nan   2   \n",
       "682        4  Sporthilfe e.V. /, Sozialwerk des Landessport-...   2   \n",
       "\n",
       "     Betten_Ins  year  \n",
       "1710        159  2012  \n",
       "1712         88  2012  \n",
       "832         107  2012  \n",
       "112         193  2012  \n",
       "1060        130  2012  \n",
       "1248         70  2012  \n",
       "1108         40  2012  \n",
       "1619        237  2012  \n",
       "763         473  2012  \n",
       "682         260  2012  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2012 = df_2012[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins', 'year']]\n",
    "df_2012.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2007 - 2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "filenames =  [\n",
    " 'KHV_2007.xls',\n",
    " 'KHV_2008.xls',\n",
    " 'KHV_2009.xls',\n",
    " 'KHV_2010.xls',\n",
    " 'KHV_2011.xls',\n",
    " 'KHV_2013.xlsx',\n",
    " 'KHV_2014.xlsx',\n",
    " 'KHV_2015.xlsx']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "## df_from_each_file = (pd.read_excel(file, sheet_name=4, dtype='object') for file in filenames)\n",
    "\n",
    "df_from_every_file = []\n",
    "\n",
    "for file in filenames:\n",
    "    data = pd.read_excel(file, sheet_name=4, skiprows=4, dtype='object')\n",
    "    data[\"year\"] = get_numbers_from_filename(file)\n",
    "    data = get_whole_traeger_name(data)\n",
    "    df_from_every_file.append(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2007_2015 = pd.concat(df_from_every_file, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2007_2015 = df_2007_2015[['Land', 'Kreis', 'Gemeinde', 'Name1', 'Ort', 'Traeger', 'T_Name1', 'Art', 'Betten_Ins', 'year']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13517</th>\n",
       "      <td>11</td>\n",
       "      <td>004</td>\n",
       "      <td>004</td>\n",
       "      <td>POLIKLIN Charlottenburg</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>5</td>\n",
       "      <td>Polikum Holding GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>-</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5320</th>\n",
       "      <td>08</td>\n",
       "      <td>436</td>\n",
       "      <td>064</td>\n",
       "      <td>Oberschwaben Klinik gGmbH</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Oberschwaben Klinik gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>532</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12020</th>\n",
       "      <td>02</td>\n",
       "      <td>000</td>\n",
       "      <td>000</td>\n",
       "      <td>Agaplesion Diakonie Hamburg</td>\n",
       "      <td>Hamburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Agaplesion Diakonie Hamburg GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>378</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7931</th>\n",
       "      <td>14</td>\n",
       "      <td>627</td>\n",
       "      <td>010</td>\n",
       "      <td>Fachkrankenhaus Coswig</td>\n",
       "      <td>Coswig</td>\n",
       "      <td>5</td>\n",
       "      <td>Fachkrankenhaus Coswig GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>190</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15627</th>\n",
       "      <td>15</td>\n",
       "      <td>090</td>\n",
       "      <td>225</td>\n",
       "      <td>KMG Klinikum Havelberg GmbH</td>\n",
       "      <td>Havelberg</td>\n",
       "      <td>5</td>\n",
       "      <td>KMG Kliniken plc, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>80</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3186</th>\n",
       "      <td>08</td>\n",
       "      <td>236</td>\n",
       "      <td>043</td>\n",
       "      <td>Kreiskrankenhaus  Neuenbürg</td>\n",
       "      <td>Neuenbürg</td>\n",
       "      <td>3</td>\n",
       "      <td>Enzkreis-Kliniken gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>105</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4347</th>\n",
       "      <td>03</td>\n",
       "      <td>457</td>\n",
       "      <td>002</td>\n",
       "      <td>Krankenhaus Borkum GmbH</td>\n",
       "      <td>Borkum</td>\n",
       "      <td>5</td>\n",
       "      <td>Abdulkader Ali, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1479</th>\n",
       "      <td>09</td>\n",
       "      <td>276</td>\n",
       "      <td>144</td>\n",
       "      <td>Kreiskrankenhaus</td>\n",
       "      <td>Viechtach</td>\n",
       "      <td>2</td>\n",
       "      <td>Kreiskrankenhäuser, Zwiesel-Viechtach, Anstalt...</td>\n",
       "      <td>2</td>\n",
       "      <td>140</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13546</th>\n",
       "      <td>11</td>\n",
       "      <td>008</td>\n",
       "      <td>008</td>\n",
       "      <td>Ida-Wolff-Krankenhaus</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>3</td>\n",
       "      <td>Vivantes, Netzwerk für Gesundheit GmbH, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>120</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5286</th>\n",
       "      <td>08</td>\n",
       "      <td>417</td>\n",
       "      <td>002</td>\n",
       "      <td>Krankenhaus</td>\n",
       "      <td>Balingen</td>\n",
       "      <td>3</td>\n",
       "      <td>Zollernalb Klinikum gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>268</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7089</th>\n",
       "      <td>08</td>\n",
       "      <td>118</td>\n",
       "      <td>079</td>\n",
       "      <td>Krankenhaus Bietigheim</td>\n",
       "      <td>Bietigheim-Bissingen</td>\n",
       "      <td>3</td>\n",
       "      <td>Kliniken Ludwigsburg-Bietigheim, gGmbH, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>346</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12668</th>\n",
       "      <td>06</td>\n",
       "      <td>438</td>\n",
       "      <td>013</td>\n",
       "      <td>Emma Klinik</td>\n",
       "      <td>Seligenstadt</td>\n",
       "      <td>5</td>\n",
       "      <td>Emma Klinik, Klinik für operative, Medizin Gmb...</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>16</td>\n",
       "      <td>064</td>\n",
       "      <td>003</td>\n",
       "      <td>Hufeland Klinikum GmbH</td>\n",
       "      <td>Bad Langensalza</td>\n",
       "      <td>3</td>\n",
       "      <td>Hufeland Klinikum GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>597</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5115</th>\n",
       "      <td>08</td>\n",
       "      <td>125</td>\n",
       "      <td>102</td>\n",
       "      <td>Klinikum am Weissenhof</td>\n",
       "      <td>Weinsberg</td>\n",
       "      <td>2</td>\n",
       "      <td>Zentrum für Psychiatrie, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>486</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9654</th>\n",
       "      <td>09</td>\n",
       "      <td>780</td>\n",
       "      <td>124</td>\n",
       "      <td>Kliniken Oberallgäu gGmbH</td>\n",
       "      <td>Immenstadt</td>\n",
       "      <td>2</td>\n",
       "      <td>Landkreis Oberallgäu, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>270</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3338</th>\n",
       "      <td>09</td>\n",
       "      <td>162</td>\n",
       "      <td>000</td>\n",
       "      <td>Deutsches Herzzentrum</td>\n",
       "      <td>München</td>\n",
       "      <td>1</td>\n",
       "      <td>Freistaat Bayern, Bayer. Staatsministerium, fü...</td>\n",
       "      <td>2</td>\n",
       "      <td>190</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7154</th>\n",
       "      <td>08</td>\n",
       "      <td>221</td>\n",
       "      <td>000</td>\n",
       "      <td>ATOS Praxisklinik</td>\n",
       "      <td>Heidelberg</td>\n",
       "      <td>5</td>\n",
       "      <td>ATOS Praxisklinik GmbH &amp; Co. KG, nan, nan, nan</td>\n",
       "      <td>4</td>\n",
       "      <td>69</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4990</th>\n",
       "      <td>07</td>\n",
       "      <td>140</td>\n",
       "      <td>112</td>\n",
       "      <td>Loreley-Kliniken</td>\n",
       "      <td>Oberwesel</td>\n",
       "      <td>4</td>\n",
       "      <td>MARIENHAUS GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>215</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4907</th>\n",
       "      <td>06</td>\n",
       "      <td>535</td>\n",
       "      <td>007</td>\n",
       "      <td>Helios Klinik</td>\n",
       "      <td>Grebenhain</td>\n",
       "      <td>5</td>\n",
       "      <td>Helios Klinik, Oberwald GmbH, nan, nan</td>\n",
       "      <td>3</td>\n",
       "      <td>168</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1234</th>\n",
       "      <td>08</td>\n",
       "      <td>335</td>\n",
       "      <td>066</td>\n",
       "      <td>Zentrum für Psychiatrie Reichenau</td>\n",
       "      <td>Reichenau</td>\n",
       "      <td>2</td>\n",
       "      <td>Zentrum für Psychiatrie Reichenau, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>293</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Land Kreis Gemeinde                              Name1  \\\n",
       "13517   11   004      004            POLIKLIN Charlottenburg   \n",
       "5320    08   436      064          Oberschwaben Klinik gGmbH   \n",
       "12020   02   000      000        Agaplesion Diakonie Hamburg   \n",
       "7931    14   627      010             Fachkrankenhaus Coswig   \n",
       "15627   15   090      225        KMG Klinikum Havelberg GmbH   \n",
       "3186    08   236      043        Kreiskrankenhaus  Neuenbürg   \n",
       "4347    03   457      002            Krankenhaus Borkum GmbH   \n",
       "1479    09   276      144                   Kreiskrankenhaus   \n",
       "13546   11   008      008              Ida-Wolff-Krankenhaus   \n",
       "5286    08   417      002                        Krankenhaus   \n",
       "7089    08   118      079             Krankenhaus Bietigheim   \n",
       "12668   06   438      013                        Emma Klinik   \n",
       "1998    16   064      003             Hufeland Klinikum GmbH   \n",
       "5115    08   125      102             Klinikum am Weissenhof   \n",
       "9654    09   780      124          Kliniken Oberallgäu gGmbH   \n",
       "3338    09   162      000              Deutsches Herzzentrum   \n",
       "7154    08   221      000                  ATOS Praxisklinik   \n",
       "4990    07   140      112                   Loreley-Kliniken   \n",
       "4907    06   535      007                      Helios Klinik   \n",
       "1234    08   335      066  Zentrum für Psychiatrie Reichenau   \n",
       "\n",
       "                         Ort Traeger  \\\n",
       "13517                 Berlin       5   \n",
       "5320              Ravensburg       4   \n",
       "12020                Hamburg       4   \n",
       "7931                  Coswig       5   \n",
       "15627              Havelberg       5   \n",
       "3186               Neuenbürg       3   \n",
       "4347                  Borkum       5   \n",
       "1479               Viechtach       2   \n",
       "13546                 Berlin       3   \n",
       "5286                Balingen       3   \n",
       "7089    Bietigheim-Bissingen       3   \n",
       "12668           Seligenstadt       5   \n",
       "1998         Bad Langensalza       3   \n",
       "5115               Weinsberg       2   \n",
       "9654              Immenstadt       2   \n",
       "3338                 München       1   \n",
       "7154              Heidelberg       5   \n",
       "4990               Oberwesel       4   \n",
       "4907              Grebenhain       5   \n",
       "1234               Reichenau       2   \n",
       "\n",
       "                                                 T_Name1 Art Betten_Ins  year  \n",
       "13517                Polikum Holding GmbH, nan, nan, nan   2         -   2014  \n",
       "5320            Oberschwaben Klinik gGmbH, nan, nan, nan   2        532  2009  \n",
       "12020    Agaplesion Diakonie Hamburg GmbH, nan, nan, nan   2        378  2014  \n",
       "7931          Fachkrankenhaus Coswig GmbH, nan, nan, nan   2        190  2010  \n",
       "15627                    KMG Kliniken plc, nan, nan, nan   2         80  2015  \n",
       "3186              Enzkreis-Kliniken gGmbH, nan, nan, nan   2        105  2008  \n",
       "4347                       Abdulkader Ali, nan, nan, nan   2          8  2009  \n",
       "1479   Kreiskrankenhäuser, Zwiesel-Viechtach, Anstalt...   2        140  2007  \n",
       "13546   Vivantes, Netzwerk für Gesundheit GmbH, nan, nan   2        120  2014  \n",
       "5286            Zollernalb Klinikum gGmbH, nan, nan, nan   2        268  2009  \n",
       "7089    Kliniken Ludwigsburg-Bietigheim, gGmbH, nan, nan   2        346  2010  \n",
       "12668  Emma Klinik, Klinik für operative, Medizin Gmb...   4         13  2014  \n",
       "1998               Hufeland Klinikum GmbH, nan, nan, nan   2        597  2007  \n",
       "5115              Zentrum für Psychiatrie, nan, nan, nan   2        486  2009  \n",
       "9654                 Landkreis Oberallgäu, nan, nan, nan   2        270  2011  \n",
       "3338   Freistaat Bayern, Bayer. Staatsministerium, fü...   2        190  2008  \n",
       "7154      ATOS Praxisklinik GmbH & Co. KG, nan, nan, nan   4         69  2010  \n",
       "4990                      MARIENHAUS GmbH, nan, nan, nan   2        215  2009  \n",
       "4907              Helios Klinik, Oberwald GmbH, nan, nan   3        168  2009  \n",
       "1234    Zentrum für Psychiatrie Reichenau, nan, nan, nan   2        293  2007  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2007_2015.sample(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add more columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def label_art(row):\n",
    "    if row == 1 :\n",
    "        return \"Uniklinik\"\n",
    "    if row == 2 :\n",
    "        return \"Plankrankenhaus\"\n",
    "    if row == 3 :\n",
    "        return \"mit Versorgungsvertrag\"\n",
    "    if row == 4 :\n",
    "        return \"ohne Versorgungsvertrag\"\n",
    "    if row == 5 :\n",
    "        return \"Bundeswehrkrankenhaus\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_betreiber(x):\n",
    "    if 'HELIOS' in x:\n",
    "        return 'HELIOS'\n",
    "    if 'ASKLEPIOS' in x:\n",
    "        return 'ASKLEPIOS'\n",
    "    if 'DRK' in x:\n",
    "        return 'DRK'\n",
    "    if 'PARACELSUS' in x:\n",
    "        return 'PARACELSUS'\n",
    "    if 'MEDECO' in x:\n",
    "        return 'MEDECO'\n",
    "    if 'RHÖN' in x:\n",
    "        return 'RHÖN'\n",
    "    if 'RHOEN' in x:\n",
    "        return 'RHÖN'\n",
    "    if 'SANA' in x:\n",
    "        return 'SANA'\n",
    "    if 'AHG' in x:\n",
    "        return 'AHG'\n",
    "    if 'AMEOS' in x:\n",
    "        return 'AMEOS'\n",
    "    if 'CAPIO' in x:\n",
    "        return 'CAPIO'\n",
    "    if 'CELENUS' in x:\n",
    "        return 'CELENUS'\n",
    "    if 'BECKER' in x:\n",
    "        return 'DR. BECKER'\n",
    "    if 'KMH' in x:\n",
    "        return 'KMH'\n",
    "    if 'MEDICLIN' in x:\n",
    "        return 'MEDICLIN'\n",
    "    if 'SCHÖN' in x:\n",
    "        return 'SCHÖN'\n",
    "    if 'SCHOEN' in x:\n",
    "        return 'SCHÖN'\n",
    "    if 'SRH' in x:\n",
    "        return 'SRH'\n",
    "    \n",
    "    #TODO Check for other betreiber\n",
    "    else:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_traeger(x):\n",
    "    if 1 <= x <= 4:\n",
    "       return \"oeffentlich\"\n",
    "    elif x == 5:\n",
    "       return \"privat\"\n",
    "    else:\n",
    "       return \"keine Angabe\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_more_columns(df):\n",
    "    df.Traeger = df.Traeger.fillna(0)\n",
    "    df.Traeger = df.Traeger.astype('int')\n",
    "    df[\"private_traeger\"]= df.Traeger.apply(check_traeger)\n",
    "    # np.where(cond1, exp1, np.where(cond2, exp2, ...))\n",
    "    # df.Traeger.apply(lambda x: \"oeffentlich\" if 1 <= x <= 4 elif x == 5 \"privat\" else \"keine Angabe\")\n",
    "\n",
    "    df[\"art_text\"]= df.Art.apply(lambda x: label_art(x))\n",
    "    df[\"betreiber\"] = df.T_Name1.str.strip().str.upper()\n",
    "    df.betreiber = df.betreiber.astype('str', errors='ignore')\n",
    "    df.betreiber = df.betreiber.apply(clean_betreiber)\n",
    "    df.Betten_Ins = df.Betten_Ins.astype('int', errors='ignore')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1992 = add_more_columns(df_1992)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DRK                                                                              38\n",
       "LAND BERLIN, NAN, NAN, NAN                                                       23\n",
       "LANDESWOHLFAHRTSVERBAND, HESSEN, NAN, NAN                                        21\n",
       "LANDSCHAFTSVERBAND, WESTFALEN-LIPPE, NAN, NAN                                    21\n",
       "KONGREGATION DER BARMHERZIGEN, SCHWESTERN VOM HEILIGEN, VINCENZ VON PAUL, NAN    19\n",
       "PARACELSUS                                                                       17\n",
       "LAND BADEN-WRTTEMBERG, NAN, NAN, NAN                                            14\n",
       "SANA                                                                             13\n",
       "LAND SACHSEN, NAN, NAN, NAN                                                      13\n",
       "ST. MARIENHAUS KRANKEN-, UND PFLEGEANSTALT GMBH, NAN, NAN                        12\n",
       "FREIE UND HANSESTADT HAMBURG, NAN, NAN, NAN                                      12\n",
       "LAND SACHSEN-ANHALT, NAN, NAN, NAN                                               10\n",
       "LANDSCHAFTSVERBAND RHEINLAND, NAN, NAN, NAN                                      10\n",
       "LAND THRINGEN, NAN, NAN, NAN                                                    10\n",
       "KATHOLISCHE WOHLT„TIGKEITS-, ANSTALT ZUR HEILIGEN ELISABETH, NAN, NAN             9\n",
       "FREISTAAT BAYERN, NAN, NAN, NAN                                                   9\n",
       "ORTENAUKREIS, NAN, NAN, NAN                                                       8\n",
       "REGIERUNGSBEZIRK OBERBAYERN, NAN, NAN, NAN                                        8\n",
       "LANDESSOZIALAMT, NIEDERSACHSEN, NAN, NAN                                          8\n",
       "LAND SCHLESWIG-HOLSTEIN, NAN, NAN, NAN                                            7\n",
       "Name: betreiber, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1992.betreiber.value_counts().head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DRK                                                                                                 34\n",
       "LANDESWOHLFAHRTSVERBAND, HESSEN, NAN, NAN                                                           19\n",
       "LAND BERLIN, NAN, NAN, NAN                                                                          19\n",
       "LANDSCHAFTSVERBAND, WESTFALEN-LIPPE, NAN, NAN                                                       18\n",
       "ST. MARIENHAUS KRANKEN-, UND PFLEGEANSTALT GMBH, NAN, NAN                                           12\n",
       "FREIE UND HANSESTADT HAMBURG, NAN, NAN, NAN                                                         12\n",
       "LANDSCHAFTSVERBAND RHEINLAND, NAN, NAN, NAN                                                         10\n",
       "SANA                                                                                                10\n",
       "KONGREGATION DER BARMHERZIGEN, SCHWESTERN VOM HEILIGEN, VINCENZ VON PAUL, NAN                       10\n",
       "BEZIRK OBERBAYERN, NAN, NAN, NAN                                                                     9\n",
       "KATHOLISCHE WOHLTÄTIGKEITS-, ANSTALT ZUR HEILIGEN ELISABETH, NAN, NAN                                9\n",
       "ORTENAUKREIS, NAN, NAN, NAN                                                                          8\n",
       "ZENTRUM FÜR PSYCHIATRIE, NAN, NAN, NAN                                                               8\n",
       "ASKLEPIOS                                                                                            8\n",
       "NIEDERSÄCHSISCHES, SOZIALMINISTERIUM, NAN, NAN                                                       8\n",
       "LANDESHAUPTSTADT HANNOVER, NAN, NAN, NAN                                                             7\n",
       "LAND NORDRHEIN-WESTFALEN, -MINISTER FÜR WISSENSCHAFT, UND FORSCHUNG-, NAN                            7\n",
       "SÄCHSISCHES STAATSMINISTERIUM, FÜR SOZIALES, GESUNDHEIT UND, FAMILIE, NAN                            7\n",
       "LAND SCHLESWIG-HOLSTEIN, NAN, NAN, NAN                                                               6\n",
       "LANDKREIS HANNOVER, NAN, NAN, NAN                                                                    6\n",
       "HESSISCHER DIAKONIEVEREIN E.V., NAN, NAN, NAN                                                        6\n",
       "FREISTAAT BAYERN, BAYERISCHES STAATSMINISTERIUM, FÜR UNTERRICHT, KULTUS,, WISSENSCHAFT UND KUNST     6\n",
       "CARITAS-TRÄGERGESELLSCHAFT, TRIER E.V., NAN, NAN                                                     6\n",
       "LAND SACHSEN-ANHALT, MINISTERIUM FÜR ARBEIT,, SOZIALES UND GESUNDHEIT, NAN                           6\n",
       "PARACELSUS                                                                                           6\n",
       "LANDESAMT FÜR SOZIALES UND, VERSORGUNG, NAN, NAN                                                     6\n",
       "BUNDESKNAPPSCHAFT BOCHUM, NAN, NAN, NAN                                                              6\n",
       "BEZIRK SCHWABEN, NAN, NAN, NAN                                                                       5\n",
       "EVANGELISCHES JOHANNESWERK, E.V., NAN, NAN                                                           5\n",
       "LANDKREIS BIBERACH, NAN, NAN, NAN                                                                    5\n",
       "LAND BADEN-WÜRTTEMBERG, NAN, NAN, NAN                                                                5\n",
       "BEZIRK UNTERFRANKEN, NAN, NAN, NAN                                                                   5\n",
       "FREISTAAT THÜRINGEN, NAN, NAN, NAN                                                                   5\n",
       "MARIENHAUS KRANKEN- UND, PFLEGEANSTALT GMBH, WALDBREITBACH, NAN                                      5\n",
       "MALTESER WERKE E.V., NAN, NAN, NAN                                                                   5\n",
       "STADT MÜNCHEN, NAN, NAN, NAN                                                                         5\n",
       "GEMEINNÜTZIGE GESELLSCHAFT DER, FRANZISKANERINNEN ZU OLPE MBH, NAN, NAN                              5\n",
       "LANDKREIS PASSAU, NAN, NAN, NAN                                                                      5\n",
       "LANDESHAUPTSTADT STUTTGART, NAN, NAN, NAN                                                            5\n",
       "BARMHERZIGE BRÜDER TRIER E.V., NAN, NAN, NAN                                                         5\n",
       "LANDKREIS RASTATT, NAN, NAN, NAN                                                                     5\n",
       "LANDKREIS MIESBACH, NAN, NAN, NAN                                                                    4\n",
       "DEUTSCHER GEMEINSCHAFTS-, DIAKONIEVERBAND GMBH, NAN, NAN                                             4\n",
       "STADT KÖLN, NAN, NAN, NAN                                                                            4\n",
       "OSTALBKREIS, NAN, NAN, NAN                                                                           4\n",
       "KLINIKEN LUDWIGSBURG-, BIETIGHEIM GGMBH, NAN, NAN                                                    4\n",
       "BUNDESMINISTERIUM DER, VERTEIDIGUNG, NAN, NAN                                                        4\n",
       "GEMEINNÜTZIGE KRANKENHAUS-, GESELLSCHAFT DES LANDKREISES, NÜRNBERGER LAND MBH, NAN                   4\n",
       "RHEIN-NECKAR-KREIS, NAN, NAN, NAN                                                                    4\n",
       "GEMEINNÜTZIGE KRANKENHAUS-, GESELLSCHAFT DES LANDKREISES, SCHWANDORF MBH, NAN                        4\n",
       "Name: betreiber, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1995 = add_more_columns(df_1995)\n",
    "df_1995.betreiber.value_counts().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DRK                                                                                   99\n",
       "ASKLEPIOS                                                                             73\n",
       "HELIOS                                                                                64\n",
       "SANA                                                                                  51\n",
       "PARACELSUS                                                                            48\n",
       "LANDRATSAMT, NAN, NAN, NAN                                                            45\n",
       "LANDSCHAFTSVERBAND, WESTFALEN-LIPPE, NAN, NAN                                         42\n",
       "LANDESWOHLFAHRTSVERBAND, HESSEN, NAN, NAN                                             37\n",
       "RHÖN                                                                                  33\n",
       "LAND NIEDERSACHSEN, NAN, NAN, NAN                                                     30\n",
       "ZENTRUM FÜR PSYCHIATRIE, NAN, NAN, NAN                                                28\n",
       "BEZIRK OBERBAYERN, NAN, NAN, NAN                                                      27\n",
       "REGION HANNOVER, NAN, NAN, NAN                                                        26\n",
       "STADTVERWALTUNG, NAN, NAN, NAN                                                        23\n",
       "SCHÖN                                                                                 21\n",
       "LANDRATSAMT ORTENAUKREIS, NAN, NAN, NAN                                               21\n",
       "FREIE UND HANSESTADT HAMBURG, NAN, NAN, NAN                                           20\n",
       "LANDSCHAFTSVERBAND RHEINLAND, NAN, NAN, NAN                                           20\n",
       "BEZIRK SCHWABEN, NAN, NAN, NAN                                                        19\n",
       "MEDECO                                                                                18\n",
       "SRH                                                                                   18\n",
       "LAND NORDRHEIN-WESTFALEN, -MINISTER FÜR WISSENSCHAFT,  UND FORSCHUNG-, NAN            18\n",
       "BEZIRK UNTERFRANKEN, NAN, NAN, NAN                                                    18\n",
       "FREISTAAT BAYERN, BAYER. STAATSMINISTERIUM, FÜR WISSENSCHAFT,, FORSCHUNG UND KUNST    17\n",
       "STADT MÜNCHEN, NAN, NAN, NAN                                                          15\n",
       "OBERSCHWABEN KLINIK GGMBH, NAN, NAN, NAN                                              15\n",
       "SÄCHSISCHES STAATSMINISTERIUM, FÜR SOZIALES, NAN, NAN                                 15\n",
       "ST. VINZENZ-, KRANKENHAUS, GGMBH, NAN                                                 14\n",
       "KLINIKUM REGION HANNOVER GMBH, NAN, NAN, NAN                                          13\n",
       "REGIONALVERBUND, KIRCHLICHER KRANKENHÄUSER GGMBH, NAN, NAN                            12\n",
       "BARMHERZIGE BRÜDER TRIER E.V., NAN, NAN, NAN                                          12\n",
       "LANDRATSAMT RHEIN-NECKAR-KREIS, GESUNDHEITS-/PFLEGEEINRICHTUNG, NAN, NAN              12\n",
       "LANDESHAUPTSTADT STUTTGART, BÜRGERMEISTERAMT, NAN, NAN                                12\n",
       "KRANKENHAUS GMBH, LANDKREIS, WEILHEIM-SCHONGAU, NAN                                   12\n",
       "LANDESAMT FÜR SOZIALES UND, VERSORGUNG, NAN, NAN                                      12\n",
       "KATH. KLINIKEN, EMSCHER-LIPPE GMBH, NAN, NAN                                          12\n",
       "OBERRHEINISCHE KLINIKEN, GMBH & CO. BETRIEBS-KG, NAN, NAN                             12\n",
       "KLINIKEN LUDWIGSBURG-, BIETIGHEIM GGMBH, NAN, NAN                                     12\n",
       "AMEOS                                                                                 11\n",
       "LANDRATSAMT OSTALBKREIS, NAN, NAN, NAN                                                11\n",
       "ST. GEORGSSTIFT E.V., NAN, NAN, NAN                                                   11\n",
       "BEZIRK OBERFRANKEN, NAN, NAN, NAN                                                     11\n",
       "KREISKLINIKEN OSTALLGÄU, SELBST. KOMMUNALUNTERNEHMEN, NAN, NAN                        11\n",
       "KLINIKEN BIBERACH GMBH, NAN, NAN, NAN                                                 10\n",
       "LANDSCHAFTSVERBAND, RHEINLAND, NAN, NAN                                               10\n",
       "DEUTSCHE RENTENVERSICHERUNG, KNAPPSCHAFT-BAHN-SEE, NAN, NAN                           10\n",
       "KONGREGATION DER, BARMHERZIGEN SCHWESTERN, V. HL. VINZENZ V. PAUL, NAN                10\n",
       "GEMEINNÜTZIGE, KRANKENHAUSGESELLSCHAFT DES, LANDKREISES SCHWANDORF MBH, NAN           10\n",
       "EV. JOHANNESWERK E.V., NAN, NAN, NAN                                                  10\n",
       "MÄRKISCHE KLINIKEN GMBH, NAN, NAN, NAN                                                 9\n",
       "Name: betreiber, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2003_2005 = add_more_columns(df_2003_2005)\n",
    "#Todo Convert floats to int? \n",
    "df_2003_2005.betreiber.value_counts().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "      <th>private_traeger</th>\n",
       "      <th>art_text</th>\n",
       "      <th>betreiber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1561</th>\n",
       "      <td>09</td>\n",
       "      <td>276</td>\n",
       "      <td>148</td>\n",
       "      <td>Kreiskrankenhaus</td>\n",
       "      <td>Zwiesel</td>\n",
       "      <td>2</td>\n",
       "      <td>Landkreis Regen, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>186</td>\n",
       "      <td>2003</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>LANDKREIS REGEN, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4980</th>\n",
       "      <td>05</td>\n",
       "      <td>913</td>\n",
       "      <td>000</td>\n",
       "      <td>Klinikum Dortmund</td>\n",
       "      <td>Dortmund</td>\n",
       "      <td>3</td>\n",
       "      <td>Klinikum Dortmund gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>1615</td>\n",
       "      <td>2005</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KLINIKUM DORTMUND GGMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4496</th>\n",
       "      <td>03</td>\n",
       "      <td>254</td>\n",
       "      <td>002</td>\n",
       "      <td>Leinebergland Kliniken</td>\n",
       "      <td>Alfeld</td>\n",
       "      <td>4</td>\n",
       "      <td>Krankenhaus Alfeld-Betriebs-gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>204</td>\n",
       "      <td>2005</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KRANKENHAUS ALFELD-BETRIEBS-GGMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>01</td>\n",
       "      <td>002</td>\n",
       "      <td>000</td>\n",
       "      <td>Klinikum der Christian-</td>\n",
       "      <td>Kiel</td>\n",
       "      <td>2</td>\n",
       "      <td>Rektor der Christian-Albrechts, Universität zu...</td>\n",
       "      <td>1</td>\n",
       "      <td>1198</td>\n",
       "      <td>2003</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>REKTOR DER CHRISTIAN-ALBRECHTS, UNIVERSITÄT ZU...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1572</th>\n",
       "      <td>09</td>\n",
       "      <td>362</td>\n",
       "      <td>000</td>\n",
       "      <td>Krankenhaus</td>\n",
       "      <td>Regensburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Orden d. Barmherzigen, Brüder in Bayern, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>859</td>\n",
       "      <td>2003</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>ORDEN D. BARMHERZIGEN, BRÜDER IN BAYERN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5680</th>\n",
       "      <td>09</td>\n",
       "      <td>162</td>\n",
       "      <td>000</td>\n",
       "      <td>Städt. Klinikum München GmbH</td>\n",
       "      <td>München</td>\n",
       "      <td>3</td>\n",
       "      <td>Stadt München, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>987</td>\n",
       "      <td>2005</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>STADT MÜNCHEN, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3650</th>\n",
       "      <td>09</td>\n",
       "      <td>188</td>\n",
       "      <td>113</td>\n",
       "      <td>Marianne-Strauss-Klinik</td>\n",
       "      <td>Berg</td>\n",
       "      <td>4</td>\n",
       "      <td>Behandlungszentrum, Kempfenhausen für, Multipl...</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "      <td>2004</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>BEHANDLUNGSZENTRUM, KEMPFENHAUSEN FÜR, MULTIPL...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2341</th>\n",
       "      <td>03</td>\n",
       "      <td>241</td>\n",
       "      <td>001</td>\n",
       "      <td>Krankenhaus Nordstadt</td>\n",
       "      <td>Hannover</td>\n",
       "      <td>1</td>\n",
       "      <td>Region Hannover, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>553</td>\n",
       "      <td>2004</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>REGION HANNOVER, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3856</th>\n",
       "      <td>09</td>\n",
       "      <td>678</td>\n",
       "      <td>193</td>\n",
       "      <td>Krankenhaus für</td>\n",
       "      <td>Werneck</td>\n",
       "      <td>1</td>\n",
       "      <td>Bezirk Unterfranken, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>290</td>\n",
       "      <td>2004</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>BEZIRK UNTERFRANKEN, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2535</th>\n",
       "      <td>05</td>\n",
       "      <td>112</td>\n",
       "      <td>000</td>\n",
       "      <td>Kath. Klinikum Duisburg</td>\n",
       "      <td>Duisburg-Laar</td>\n",
       "      <td>4</td>\n",
       "      <td>Zweckverband, Kath. Klinikum Duisburg, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>77</td>\n",
       "      <td>2004</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>ZWECKVERBAND, KATH. KLINIKUM DUISBURG, NAN, NAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde                         Name1             Ort  \\\n",
       "1561   09   276      148              Kreiskrankenhaus         Zwiesel   \n",
       "4980   05   913      000             Klinikum Dortmund        Dortmund   \n",
       "4496   03   254      002        Leinebergland Kliniken          Alfeld   \n",
       "12     01   002      000       Klinikum der Christian-            Kiel   \n",
       "1572   09   362      000                   Krankenhaus      Regensburg   \n",
       "5680   09   162      000  Städt. Klinikum München GmbH         München   \n",
       "3650   09   188      113       Marianne-Strauss-Klinik            Berg   \n",
       "2341   03   241      001         Krankenhaus Nordstadt        Hannover   \n",
       "3856   09   678      193               Krankenhaus für         Werneck   \n",
       "2535   05   112      000       Kath. Klinikum Duisburg   Duisburg-Laar   \n",
       "\n",
       "      Traeger                                            T_Name1 Art  \\\n",
       "1561        2                     Landkreis Regen, nan, nan, nan   2   \n",
       "4980        3             Klinikum Dortmund gGmbH, nan, nan, nan   2   \n",
       "4496        4   Krankenhaus Alfeld-Betriebs-gGmbH, nan, nan, nan   2   \n",
       "12          2  Rektor der Christian-Albrechts, Universität zu...   1   \n",
       "1572        4  Orden d. Barmherzigen, Brüder in Bayern, nan, nan   2   \n",
       "5680        3                       Stadt München, nan, nan, nan   2   \n",
       "3650        4  Behandlungszentrum, Kempfenhausen für, Multipl...   2   \n",
       "2341        1                     Region Hannover, nan, nan, nan   2   \n",
       "3856        1                 Bezirk Unterfranken, nan, nan, nan   2   \n",
       "2535        4    Zweckverband, Kath. Klinikum Duisburg, nan, nan   2   \n",
       "\n",
       "     Betten_Ins  year private_traeger art_text  \\\n",
       "1561        186  2003     oeffentlich     None   \n",
       "4980       1615  2005     oeffentlich     None   \n",
       "4496        204  2005     oeffentlich     None   \n",
       "12         1198  2003     oeffentlich     None   \n",
       "1572        859  2003     oeffentlich     None   \n",
       "5680        987  2005     oeffentlich     None   \n",
       "3650        100  2004     oeffentlich     None   \n",
       "2341        553  2004     oeffentlich     None   \n",
       "3856        290  2004     oeffentlich     None   \n",
       "2535         77  2004     oeffentlich     None   \n",
       "\n",
       "                                              betreiber  \n",
       "1561                     LANDKREIS REGEN, NAN, NAN, NAN  \n",
       "4980             KLINIKUM DORTMUND GGMBH, NAN, NAN, NAN  \n",
       "4496   KRANKENHAUS ALFELD-BETRIEBS-GGMBH, NAN, NAN, NAN  \n",
       "12    REKTOR DER CHRISTIAN-ALBRECHTS, UNIVERSITÄT ZU...  \n",
       "1572  ORDEN D. BARMHERZIGEN, BRÜDER IN BAYERN, NAN, NAN  \n",
       "5680                       STADT MÜNCHEN, NAN, NAN, NAN  \n",
       "3650  BEHANDLUNGSZENTRUM, KEMPFENHAUSEN FÜR, MULTIPL...  \n",
       "2341                     REGION HANNOVER, NAN, NAN, NAN  \n",
       "3856                 BEZIRK UNTERFRANKEN, NAN, NAN, NAN  \n",
       "2535    ZWECKVERBAND, KATH. KLINIKUM DUISBURG, NAN, NAN  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2003_2005.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ASKLEPIOS                                                                             34\n",
       "DRK                                                                                   33\n",
       "HELIOS                                                                                29\n",
       "SANA                                                                                  25\n",
       "RHÖN                                                                                  17\n",
       "PARACELSUS                                                                            17\n",
       "LANDSCHAFTSVERBAND, WESTFALEN-LIPPE, NAN, NAN                                         14\n",
       "LANDESWOHLFAHRTSVERBAND, HESSEN, NAN, NAN                                             12\n",
       "KLINIKUM REGION HANNOVER GMBH, NAN, NAN, NAN                                          12\n",
       "LANDSCHAFTSVERBAND RHEINLAND, NAN, NAN, NAN                                           10\n",
       "LAND NIEDERSACHSEN, NAN, NAN, NAN                                                      9\n",
       "BEZIRK OBERBAYERN, NAN, NAN, NAN                                                       9\n",
       "MARIENHAUS GMBH, NAN, NAN, NAN                                                         9\n",
       "SCHÖN                                                                                  8\n",
       "LANDRATSAMT ORTENAUKREIS - AMT 13, NAN, NAN, NAN                                       7\n",
       "BEZIRK SCHWABEN, NAN, NAN, NAN                                                         7\n",
       "KLINIKEN NORDOBERPFALZ AG, NAN, NAN, NAN                                               7\n",
       "MEDICLIN                                                                               6\n",
       "LAND NORDRHEIN-WESTFALEN, -MINISTER FÜR WISSENSCHAFT, UND FORSCHUNG-, NAN              6\n",
       "BEZIRK UNTERFRANKEN, NAN, NAN, NAN                                                     6\n",
       "SRH                                                                                    6\n",
       "DEUTSCHE RENTENVERSICHERUNG, KNAPPSCHAFT-BAHN-SEE, NAN, NAN                            6\n",
       "GEMEINNÜTZIGE GESELLSCHAFT DER, FRANZISKANERINNEN ZU OLPE MBH, NAN, NAN                6\n",
       "MEDECO                                                                                 6\n",
       "KLINIKVERBUND SÜDWEST, NAN, NAN, NAN                                                   6\n",
       "STADT MÜNCHEN, NAN, NAN, NAN                                                           5\n",
       "STADTVERWALTUNG, NAN, NAN, NAN                                                         5\n",
       "OBERSCHWABEN KLINIK GGMBH, NAN, NAN, NAN                                               5\n",
       "MARIENHAUS GMBH WALDBREITBACH, NAN, NAN, NAN                                           5\n",
       "LANDRATSAMT, NAN, NAN, NAN                                                             5\n",
       "KLINIKUM ST. GEORG GGMBH LEIPZIG, NAN, NAN, NAN                                        5\n",
       "ST. VINZENZ-, KRANKENHAUS, GGMBH, NAN                                                  5\n",
       "ST. GEORGSSTIFT E.V., NAN, NAN, NAN                                                    4\n",
       "FREISTAAT BAYERN, BAYER. STAATSMINISTERIUM, FÜR WISSENSCHAFT,, FORSCHUNG UND KUNST     4\n",
       "EV. JOHANNESWERK E.V., NAN, NAN, NAN                                                   4\n",
       "MÜHLENKREISKLINIKEN AÖR, NAN, NAN, NAN                                                 4\n",
       "KATH. HOSPITALVEREINIGUNG, WESER-EGGE GGMBH, NAN, NAN                                  4\n",
       "KRANKENHAUS GMBH, LANDKREIS, WEILHEIM-SCHONGAU, NAN                                    4\n",
       "KLINIKGRUPPE DR. GUTH GMBH & CO. KG, NAN, NAN, NAN                                     4\n",
       "REGIONALVERBUND, KIRCHLICHER KRANKENHÄUSER GGMBH, NAN, NAN                             4\n",
       "KLINIKUM NORDFRIESLAND GGMBH, NAN, NAN, NAN                                            4\n",
       "OBERRHEINISCHE KLINIKEN, GMBH & CO. BETRIEBS - KG, NAN, NAN                            4\n",
       "SLK - KLINIKEN HEILBRONN GMBH, NAN, NAN, NAN                                           4\n",
       "BARMHERZIGE BRÜDER TRIER E.V., NAN, NAN, NAN                                           4\n",
       "LANDESKRANKENHAUS (AÖR), NAN, NAN, NAN                                                 4\n",
       "DEUTSCHER GEMEINSCHAFTS-, DIAKONIEVERBAND GMBH, NAN, NAN                               4\n",
       "KLINIKUM MITTELBADEN GGMBH, NAN, NAN, NAN                                              4\n",
       "RHEIN - NECKAR - KREIS, NAN, NAN, NAN                                                  4\n",
       "KLINIKEN LUDWIGSBURG - BIETIGHEIM, GGMBH, NAN, NAN                                     4\n",
       "KREISSPITALSTIFTUNG WEISSENHORN, NAN, NAN, NAN                                         3\n",
       "Name: betreiber, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2006 = add_more_columns(df_2006)\n",
    "df_2006.betreiber.value_counts().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "      <th>private_traeger</th>\n",
       "      <th>art_text</th>\n",
       "      <th>betreiber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1482</th>\n",
       "      <td>09</td>\n",
       "      <td>272</td>\n",
       "      <td>120</td>\n",
       "      <td>Krankenhaus</td>\n",
       "      <td>Grafenau</td>\n",
       "      <td>3</td>\n",
       "      <td>Kliniken des Landkreises, Freyung-Grafenau gGm...</td>\n",
       "      <td>2</td>\n",
       "      <td>140</td>\n",
       "      <td>2006</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KLINIKEN DES LANDKREISES, FREYUNG-GRAFENAU GGM...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde        Name1        Ort  Traeger  \\\n",
       "1482   09   272      120  Krankenhaus   Grafenau        3   \n",
       "\n",
       "                                                T_Name1 Art Betten_Ins  year  \\\n",
       "1482  Kliniken des Landkreises, Freyung-Grafenau gGm...   2        140  2006   \n",
       "\n",
       "     private_traeger art_text  \\\n",
       "1482     oeffentlich     None   \n",
       "\n",
       "                                              betreiber  \n",
       "1482  KLINIKEN DES LANDKREISES, FREYUNG-GRAFENAU GGM...  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2006.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "HELIOS                                                                                                  52\n",
       "ASKLEPIOS                                                                                               43\n",
       "DRK                                                                                                     34\n",
       "SANA                                                                                                    28\n",
       "LANDSCHAFTSVERBAND, WESTFALEN-LIPPE, NAN, NAN                                                           15\n",
       "RHÖN                                                                                                    15\n",
       "PARACELSUS                                                                                              14\n",
       "SCHÖN                                                                                                   13\n",
       "MEDICLIN                                                                                                12\n",
       "KLINIKUM REGION HANNOVER GMBH, NAN, NAN, NAN                                                            12\n",
       "AMEOS                                                                                                   12\n",
       "LANDSCHAFTSVERBAND RHEINLAND, NAN, NAN, NAN                                                             10\n",
       "MARIENHAUS GMBH, NAN, NAN, NAN                                                                           9\n",
       "CAPIO                                                                                                    9\n",
       "BEZIRKSKLINIKEN SCHWABEN KU, ANSTALT DES ÖFFENTLICHEN RECHTS, DES BEZIRKS SCHWABEN, NAN                  7\n",
       "MEDECO                                                                                                   6\n",
       "LAND NORDRHEIN-WESTFALEN, - MINISTERIUM FÜR INNOVATION,, WISSENSCHAFT, FORSCHUNG UND, TECHNOLOGIE -      6\n",
       "LANDRATSAMT ORTENAUKREIS - AMT 13, NAN, NAN, NAN                                                         6\n",
       "KLINIKEN, SÜDOSTBAYERN AG, NAN, NAN                                                                      6\n",
       "BEZIRK UNTERFRANKEN, NAN, NAN, NAN                                                                       6\n",
       "SRH                                                                                                      6\n",
       "ZENTRUM FÜR PSYCHIATRIE, SÜDWÜRTTEMBERG, NAN, NAN                                                        5\n",
       "MARIENHAUS GMBH WALDBREITBACH, NAN, NAN, NAN                                                             5\n",
       "NIELS-STENSEN-KLINIKEN GMBH, NAN, NAN, NAN                                                               5\n",
       "STÄDTISCHES KLINIKUM, MÜNCHEN GMBH, NAN, NAN                                                             5\n",
       "KLINIKEN LUDWIGSBURG-BIETIGHEIM, GGMBH, NAN, NAN                                                         5\n",
       "KLINIKEN OSTALLGÄU-KAUFBEUREN, SELBST. KOMMUNALUNTERNEHMEN, NAN, NAN                                     5\n",
       "KLINIKUM MITTELBADEN GGMBH, NAN, NAN, NAN                                                                5\n",
       "GEMEINNÜTZIGE GESELLSCHAFT DER, FRANZISKANERINNEN ZU OLPE MBH, NAN, NAN                                  5\n",
       "BARMHERZIGE BRÜDER TRIER E.V., NAN, NAN, NAN                                                             5\n",
       "FREISTAAT BAYERN,, BAYERISCHES STAATSMINISTERIUM, FÜR WISSENSCHAFT,, FORSCHUNG UND KUNST                 4\n",
       "EVANG.-LUTH. DIAKONIEWERK, NEUENDETTELSAU, NAN, NAN                                                      4\n",
       "ST. VINZENZ-, KRANKENHAUS, GGMBH, NAN                                                                    4\n",
       "KATHOLISCHER KLINIKVERBUND, SÜDHESSEN GGMBH, NAN, NAN                                                    4\n",
       "ST. ANSGAR KLINIKVERBUND GMBH, NAN, NAN, NAN                                                             4\n",
       "OBERRHEINISCHE KLINIKEN, GMBH & CO. BETRIEBS-KG, NAN, NAN                                                4\n",
       "REGIONALVERBUND, KIRCHLICHER KRANKENHÄUSER GGMBH, NAN, NAN                                               4\n",
       "KOMMUNALUNTERNEHMEN KLINIKEN, U. HEIME DES BEZIRKS OBERFRANKEN, ANSTALT DES ÖFFENTLICHEN RECHTS, NAN     4\n",
       "ST. VINCENZ GRUPPE RUHR GMBH, NAN, NAN, NAN                                                              4\n",
       "KLINIKEN DER STADT UND, DES LANDKREISES ROSENHEIM GMBH, NAN, NAN                                         4\n",
       "SÄCHSISCHES STAATSMINISTERIUM, FÜR SOZIALES U. VERBRAUCHERSCHUTZ, NAN, NAN                               4\n",
       "KLINIKVERBUND SÜDWEST, NAN, NAN, NAN                                                                     4\n",
       "RHEIN-NECKAR-KREIS, NAN, NAN, NAN                                                                        4\n",
       "KATHOLISCHE KLINIKEN, OLDENBURGER MÜNSTERLAND GGMBH, NAN, NAN                                            4\n",
       "WITTGENSTEINER KLINIKEN GMBH, NAN, NAN, NAN                                                              3\n",
       "KATH. ST.-JOHANNES-GESELLSCHAFT, DORTMUND GGMBH, NAN, NAN                                                3\n",
       "KONGREGATION DER BARMHERZIGEN, SCHWESTERN V. HL. VINZENZ, VON PAUL, NAN                                  3\n",
       "BEZIRKSKLINIKEN MITTELFRANKEN, NAN, NAN, NAN                                                             3\n",
       "KLINIKEN NORDOBERPFALZ AG, NAN, NAN, NAN                                                                 3\n",
       "LANDKREIS DEGGENDORF, LANDKREIS, DINGOLFING-LANDAU, NAN                                                  3\n",
       "Name: betreiber, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2012 = add_more_columns(df_2012)\n",
    "df_2012.betreiber.value_counts().head(50)"
   ]
  },
  {
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "      <th>private_traeger</th>\n",
       "      <th>art_text</th>\n",
       "      <th>betreiber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>14</td>\n",
       "      <td>522</td>\n",
       "      <td>260</td>\n",
       "      <td>Diakoniekrankenhaus Chemnitzer</td>\n",
       "      <td>Hartmannsdorf</td>\n",
       "      <td>4</td>\n",
       "      <td>DIAKOMED Diakoniekrankenhaus, Chemnitzer Land ...</td>\n",
       "      <td>2</td>\n",
       "      <td>250</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>DIAKOMED DIAKONIEKRANKENHAUS, CHEMNITZER LAND ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Land Kreis Gemeinde                           Name1            Ort  \\\n",
       "1775   14   522      260  Diakoniekrankenhaus Chemnitzer  Hartmannsdorf   \n",
       "\n",
       "      Traeger                                            T_Name1 Art  \\\n",
       "1775        4  DIAKOMED Diakoniekrankenhaus, Chemnitzer Land ...   2   \n",
       "\n",
       "     Betten_Ins  year private_traeger art_text  \\\n",
       "1775        250  2012     oeffentlich     None   \n",
       "\n",
       "                                              betreiber  \n",
       "1775  DIAKOMED DIAKONIEKRANKENHAUS, CHEMNITZER LAND ...  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2012.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "HELIOS                                                                                                  390\n",
       "ASKLEPIOS                                                                                               333\n",
       "DRK                                                                                                     260\n",
       "SANA                                                                                                    222\n",
       "PARACELSUS                                                                                              119\n",
       "LANDSCHAFTSVERBAND, WESTFALEN-LIPPE, NAN, NAN                                                           117\n",
       "SCHÖN                                                                                                   100\n",
       "KLINIKUM REGION HANNOVER GMBH, NAN, NAN, NAN                                                             98\n",
       "RHÖN                                                                                                     96\n",
       "MEDICLIN                                                                                                 85\n",
       "LANDSCHAFTSVERBAND RHEINLAND, NAN, NAN, NAN                                                              80\n",
       "AMEOS                                                                                                    77\n",
       "MARIENHAUS GMBH, NAN, NAN, NAN                                                                           76\n",
       "CAPIO                                                                                                    66\n",
       "BEZIRK UNTERFRANKEN, NAN, NAN, NAN                                                                       51\n",
       "SRH                                                                                                      49\n",
       "MEDECO                                                                                                   48\n",
       "MARIENHAUS GMBH WALDBREITBACH, NAN, NAN, NAN                                                             45\n",
       "LANDRATSAMT ORTENAUKREIS - AMT 13, NAN, NAN, NAN                                                         43\n",
       "BARMHERZIGE BRÜDER TRIER E.V., NAN, NAN, NAN                                                             38\n",
       "GEMEINNÜTZIGE GESELLSCHAFT DER, FRANZISKANERINNEN ZU OLPE MBH, NAN, NAN                                  37\n",
       "LAND NORDRHEIN-WESTFALEN, - MINISTERIUM FÜR INNOVATION,, WISSENSCHAFT, FORSCHUNG UND, TECHNOLOGIE -      36\n",
       "BEZIRKSKLINIKEN SCHWABEN KU, ANSTALT DES ÖFFENTLICHEN RECHTS, DES BEZIRKS SCHWABEN, NAN                  35\n",
       "KLINIKEN LUDWIGSBURG-BIETIGHEIM, GGMBH, NAN, NAN                                                         35\n",
       "STÄDTISCHES KLINIKUM, MÜNCHEN GMBH, NAN, NAN                                                             35\n",
       "ST. VINZENZ-, KRANKENHAUS, GGMBH, NAN                                                                    34\n",
       "KLINIKEN OSTALLGÄU-KAUFBEUREN, SELBST. KOMMUNALUNTERNEHMEN, NAN, NAN                                     34\n",
       "KLINIKEN, SÜDOSTBAYERN AG, NAN, NAN                                                                      33\n",
       "NIELS-STENSEN-KLINIKEN GMBH, NAN, NAN, NAN                                                               33\n",
       "KLINIKUM MITTELBADEN GGMBH, NAN, NAN, NAN                                                                33\n",
       "EVANG.-LUTH. DIAKONIEWERK, NEUENDETTELSAU, NAN, NAN                                                      32\n",
       "MÜHLENKREISKLINIKEN AÖR, NAN, NAN, NAN                                                                   32\n",
       "KLINIKEN NORDOBERPFALZ AG, NAN, NAN, NAN                                                                 31\n",
       "LANDRATSAMT, NAN, NAN, NAN                                                                               31\n",
       "KRANKENHAUS GMBH, LANDKREIS, WEILHEIM-SCHONGAU, NAN                                                      29\n",
       "REGIONALVERBUND, KIRCHLICHER KRANKENHÄUSER GGMBH, NAN, NAN                                               29\n",
       "KOMMUNALUNTERNEHMEN KLINIKEN, U. HEIME DES BEZIRKS OBERFRANKEN, ANSTALT DES ÖFFENTLICHEN RECHTS, NAN     29\n",
       "KLINIKGRUPPE DR. GUTH GMBH & CO. KG, NAN, NAN, NAN                                                       28\n",
       "RHEIN-NECKAR-KREIS, NAN, NAN, NAN                                                                        28\n",
       "DEUTSCHER GEMEINSCHAFTS-, DIAKONIEVERBAND GMBH, NAN, NAN                                                 27\n",
       "GESUNDHEIT NORDHESSEN, HOLDING AG, NAN, NAN                                                              27\n",
       "KLINIKVERBUND SÜDWEST, NAN, NAN, NAN                                                                     25\n",
       "DEUTSCHE RENTENVERSICHERUNG, KNAPPSCHAFT-BAHN-SEE, NAN, NAN                                              25\n",
       "SAARLAND-HEILSTÄTTEN GMBH, NAN, NAN, NAN                                                                 24\n",
       "VESTISCHE CARITAS-KLINIKEN GMBH, NAN, NAN, NAN                                                           24\n",
       "MÄRKISCHE KLINIKEN GMBH, NAN, NAN, NAN                                                                   24\n",
       "WERNER WICKER KG, NAN, NAN, NAN                                                                          24\n",
       "KLINIKEN DER STADT KÖLN GGMBH, NAN, NAN, NAN                                                             24\n",
       "LECH-MANGFALL-KLINIKEN GGMBH, NAN, NAN, NAN                                                              24\n",
       "LANDESKRANKENHAUS, ANSTALT DES ÖFFENTLICHEN RECHTS, (AÖR), NAN                                           24\n",
       "Name: betreiber, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2007_2015 = add_more_columns(df_2007_2015)\n",
    "df_2007_2015.betreiber.value_counts().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "      <th>private_traeger</th>\n",
       "      <th>art_text</th>\n",
       "      <th>betreiber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10377</th>\n",
       "      <td>05</td>\n",
       "      <td>113</td>\n",
       "      <td>000</td>\n",
       "      <td>Alfried Krupp Krankenhaus Steele</td>\n",
       "      <td>Essen</td>\n",
       "      <td>4</td>\n",
       "      <td>Alfried Krupp von Bohlen, und Halbach Krankenh...</td>\n",
       "      <td>2</td>\n",
       "      <td>320</td>\n",
       "      <td>2013</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>ALFRIED KRUPP VON BOHLEN, UND HALBACH KRANKENH...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1845</th>\n",
       "      <td>13</td>\n",
       "      <td>056</td>\n",
       "      <td>072</td>\n",
       "      <td>Klinik Amsee</td>\n",
       "      <td>Waren</td>\n",
       "      <td>5</td>\n",
       "      <td>Ansgar Besitzgesellschaft, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>82</td>\n",
       "      <td>2007</td>\n",
       "      <td>privat</td>\n",
       "      <td>None</td>\n",
       "      <td>ANSGAR BESITZGESELLSCHAFT, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461</th>\n",
       "      <td>05</td>\n",
       "      <td>314</td>\n",
       "      <td>000</td>\n",
       "      <td>Gemeinschaftskrankenhaus</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>4</td>\n",
       "      <td>Gemeinschaftskrankenhaus, St. Elisabeth - St. ...</td>\n",
       "      <td>2</td>\n",
       "      <td>435</td>\n",
       "      <td>2007</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>GEMEINSCHAFTSKRANKENHAUS, ST. ELISABETH - ST. ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11364</th>\n",
       "      <td>09</td>\n",
       "      <td>275</td>\n",
       "      <td>116</td>\n",
       "      <td>Rheumaklinik Ostbayern</td>\n",
       "      <td>Bad Füssing</td>\n",
       "      <td>3</td>\n",
       "      <td>Landkreis Passau, Krankenhaus gGmbH, nan, nan</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>2013</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>LANDKREIS PASSAU, KRANKENHAUS GGMBH, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6136</th>\n",
       "      <td>01</td>\n",
       "      <td>062</td>\n",
       "      <td>001</td>\n",
       "      <td>Psych. Tagesklinik</td>\n",
       "      <td>Ahrensburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Heinrich Sengelmann Krankenhaus, gGmbH, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>HEINRICH SENGELMANN KRANKENHAUS, GGMBH, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8058</th>\n",
       "      <td>01</td>\n",
       "      <td>002</td>\n",
       "      <td>000</td>\n",
       "      <td>Park-Klinik</td>\n",
       "      <td>Kiel</td>\n",
       "      <td>5</td>\n",
       "      <td>Park-Klinik GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2011</td>\n",
       "      <td>privat</td>\n",
       "      <td>None</td>\n",
       "      <td>PARK-KLINIK GMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2028</th>\n",
       "      <td>01</td>\n",
       "      <td>001</td>\n",
       "      <td>000</td>\n",
       "      <td>Diakonissenkrankenhaus</td>\n",
       "      <td>Flensburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Ev.-luth. Diakonissenanstalt, zu Flensburg, na...</td>\n",
       "      <td>2</td>\n",
       "      <td>554</td>\n",
       "      <td>2008</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>EV.-LUTH. DIAKONISSENANSTALT, ZU FLENSBURG, NA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15438</th>\n",
       "      <td>12</td>\n",
       "      <td>054</td>\n",
       "      <td>000</td>\n",
       "      <td>St. Josefs-Krankenhaus Potsdam</td>\n",
       "      <td>Potsdam</td>\n",
       "      <td>4</td>\n",
       "      <td>St. Josefhaus Potsdam gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>241</td>\n",
       "      <td>2015</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>ST. JOSEFHAUS POTSDAM GGMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2134</th>\n",
       "      <td>02</td>\n",
       "      <td>000</td>\n",
       "      <td>000</td>\n",
       "      <td>Kath. Kinderkrankenhaus</td>\n",
       "      <td>Hamburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Katholisches Kinderkrankenhaus, Wilhelmstift g...</td>\n",
       "      <td>2</td>\n",
       "      <td>192</td>\n",
       "      <td>2008</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KATHOLISCHES KINDERKRANKENHAUS, WILHELMSTIFT G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3275</th>\n",
       "      <td>08</td>\n",
       "      <td>416</td>\n",
       "      <td>041</td>\n",
       "      <td>Berufsgenossenschaftliche</td>\n",
       "      <td>Tübingen</td>\n",
       "      <td>4</td>\n",
       "      <td>Verein für Berufsgenossen-, schaftliche Heilbe...</td>\n",
       "      <td>2</td>\n",
       "      <td>327</td>\n",
       "      <td>2008</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>VEREIN FÜR BERUFSGENOSSEN-, SCHAFTLICHE HEILBE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8021</th>\n",
       "      <td>16</td>\n",
       "      <td>063</td>\n",
       "      <td>003</td>\n",
       "      <td>Klinikum Bad Salzungen GmbH</td>\n",
       "      <td>Bad Salzungen</td>\n",
       "      <td>3</td>\n",
       "      <td>Klinikum Bad Salzungen GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>2010</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KLINIKUM BAD SALZUNGEN GMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13473</th>\n",
       "      <td>10</td>\n",
       "      <td>044</td>\n",
       "      <td>112</td>\n",
       "      <td>Caritas Krankenhaus</td>\n",
       "      <td>Lebach</td>\n",
       "      <td>4</td>\n",
       "      <td>Cusanus Trägergesellschaft, Trier mbH, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>155</td>\n",
       "      <td>2014</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>CUSANUS TRÄGERGESELLSCHAFT, TRIER MBH, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3960</th>\n",
       "      <td>14</td>\n",
       "      <td>730</td>\n",
       "      <td>310</td>\n",
       "      <td>Kreiskrankenhaus Torgau</td>\n",
       "      <td>Torgau</td>\n",
       "      <td>3</td>\n",
       "      <td>Kreiskrankenhaus Torgau, Johann Kentmann gGmbH...</td>\n",
       "      <td>2</td>\n",
       "      <td>250</td>\n",
       "      <td>2008</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KREISKRANKENHAUS TORGAU, JOHANN KENTMANN GGMBH...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10066</th>\n",
       "      <td>01</td>\n",
       "      <td>056</td>\n",
       "      <td>008</td>\n",
       "      <td>Fachklinik Bokholt</td>\n",
       "      <td>Bokholt-Hanredder</td>\n",
       "      <td>4</td>\n",
       "      <td>Therapiehilfe e. V., nan, nan, nan</td>\n",
       "      <td>3</td>\n",
       "      <td>35</td>\n",
       "      <td>2013</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>THERAPIEHILFE E. V., NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15308</th>\n",
       "      <td>09</td>\n",
       "      <td>776</td>\n",
       "      <td>116</td>\n",
       "      <td>Dr. Alram</td>\n",
       "      <td>Lindau</td>\n",
       "      <td>5</td>\n",
       "      <td>Dr. M. Alram, Privatklinik GmbH, nan, nan</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2015</td>\n",
       "      <td>privat</td>\n",
       "      <td>None</td>\n",
       "      <td>DR. M. ALRAM, PRIVATKLINIK GMBH, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10504</th>\n",
       "      <td>05</td>\n",
       "      <td>366</td>\n",
       "      <td>028</td>\n",
       "      <td>Kreiskrankenhaus Mechernich</td>\n",
       "      <td>Mechernich</td>\n",
       "      <td>3</td>\n",
       "      <td>Kreiskrankenhaus Mechernich GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>2013</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KREISKRANKENHAUS MECHERNICH GMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5600</th>\n",
       "      <td>09</td>\n",
       "      <td>661</td>\n",
       "      <td>000</td>\n",
       "      <td>Klinikum</td>\n",
       "      <td>Aschaffenburg</td>\n",
       "      <td>2</td>\n",
       "      <td>Krankenhauszweckverband, Aschaffenburg, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>685</td>\n",
       "      <td>2009</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KRANKENHAUSZWECKVERBAND, ASCHAFFENBURG, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5104</th>\n",
       "      <td>08</td>\n",
       "      <td>119</td>\n",
       "      <td>085</td>\n",
       "      <td>Klinikum Schloss Winnenden</td>\n",
       "      <td>Winnenden</td>\n",
       "      <td>2</td>\n",
       "      <td>Klinikum Schloss Winnenden, Zentrum Für Psychi...</td>\n",
       "      <td>2</td>\n",
       "      <td>462</td>\n",
       "      <td>2009</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KLINIKUM SCHLOSS WINNENDEN, ZENTRUM FÜR PSYCHI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12860</th>\n",
       "      <td>08</td>\n",
       "      <td>116</td>\n",
       "      <td>019</td>\n",
       "      <td>Klinikum Esslingen GmbH</td>\n",
       "      <td>Esslingen</td>\n",
       "      <td>3</td>\n",
       "      <td>Stadtverwaltung, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>585</td>\n",
       "      <td>2014</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>STADTVERWALTUNG, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7217</th>\n",
       "      <td>08</td>\n",
       "      <td>316</td>\n",
       "      <td>011</td>\n",
       "      <td>Kreiskrankenhaus</td>\n",
       "      <td>Emmendingen</td>\n",
       "      <td>1</td>\n",
       "      <td>Landratsamt, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>278</td>\n",
       "      <td>2010</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>LANDRATSAMT, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Land Kreis Gemeinde                             Name1  \\\n",
       "10377   05   113      000  Alfried Krupp Krankenhaus Steele   \n",
       "1845    13   056      072                      Klinik Amsee   \n",
       "461     05   314      000          Gemeinschaftskrankenhaus   \n",
       "11364   09   275      116            Rheumaklinik Ostbayern   \n",
       "6136    01   062      001                Psych. Tagesklinik   \n",
       "8058    01   002      000                       Park-Klinik   \n",
       "2028    01   001      000            Diakonissenkrankenhaus   \n",
       "15438   12   054      000    St. Josefs-Krankenhaus Potsdam   \n",
       "2134    02   000      000           Kath. Kinderkrankenhaus   \n",
       "3275    08   416      041         Berufsgenossenschaftliche   \n",
       "8021    16   063      003       Klinikum Bad Salzungen GmbH   \n",
       "13473   10   044      112               Caritas Krankenhaus   \n",
       "3960    14   730      310           Kreiskrankenhaus Torgau   \n",
       "10066   01   056      008                Fachklinik Bokholt   \n",
       "15308   09   776      116                         Dr. Alram   \n",
       "10504   05   366      028       Kreiskrankenhaus Mechernich   \n",
       "5600    09   661      000                          Klinikum   \n",
       "5104    08   119      085        Klinikum Schloss Winnenden   \n",
       "12860   08   116      019           Klinikum Esslingen GmbH   \n",
       "7217    08   316      011                  Kreiskrankenhaus   \n",
       "\n",
       "                     Ort  Traeger  \\\n",
       "10377              Essen        4   \n",
       "1845               Waren        5   \n",
       "461                 Bonn        4   \n",
       "11364        Bad Füssing        3   \n",
       "6136          Ahrensburg        4   \n",
       "8058                Kiel        5   \n",
       "2028           Flensburg        4   \n",
       "15438            Potsdam        4   \n",
       "2134             Hamburg        4   \n",
       "3275            Tübingen        4   \n",
       "8021       Bad Salzungen        3   \n",
       "13473             Lebach        4   \n",
       "3960              Torgau        3   \n",
       "10066  Bokholt-Hanredder        4   \n",
       "15308             Lindau        5   \n",
       "10504         Mechernich        3   \n",
       "5600       Aschaffenburg        2   \n",
       "5104           Winnenden        2   \n",
       "12860          Esslingen        3   \n",
       "7217         Emmendingen        1   \n",
       "\n",
       "                                                 T_Name1 Art Betten_Ins  year  \\\n",
       "10377  Alfried Krupp von Bohlen, und Halbach Krankenh...   2        320  2013   \n",
       "1845            Ansgar Besitzgesellschaft, nan, nan, nan   2         82  2007   \n",
       "461    Gemeinschaftskrankenhaus, St. Elisabeth - St. ...   2        435  2007   \n",
       "11364      Landkreis Passau, Krankenhaus gGmbH, nan, nan   3         20  2013   \n",
       "6136    Heinrich Sengelmann Krankenhaus, gGmbH, nan, nan   2        NaN  2010   \n",
       "8058                     Park-Klinik GmbH, nan, nan, nan   2         50  2011   \n",
       "2028   Ev.-luth. Diakonissenanstalt, zu Flensburg, na...   2        554  2008   \n",
       "15438         St. Josefhaus Potsdam gGmbH, nan, nan, nan   2        241  2015   \n",
       "2134   Katholisches Kinderkrankenhaus, Wilhelmstift g...   2        192  2008   \n",
       "3275   Verein für Berufsgenossen-, schaftliche Heilbe...   2        327  2008   \n",
       "8021          Klinikum Bad Salzungen GmbH, nan, nan, nan   2        403  2010   \n",
       "13473    Cusanus Trägergesellschaft, Trier mbH, nan, nan   2        155  2014   \n",
       "3960   Kreiskrankenhaus Torgau, Johann Kentmann gGmbH...   2        250  2008   \n",
       "10066                 Therapiehilfe e. V., nan, nan, nan   3         35  2013   \n",
       "15308          Dr. M. Alram, Privatklinik GmbH, nan, nan   4          3  2015   \n",
       "10504    Kreiskrankenhaus Mechernich GmbH, nan, nan, nan   2        403  2013   \n",
       "5600    Krankenhauszweckverband, Aschaffenburg, nan, nan   2        685  2009   \n",
       "5104   Klinikum Schloss Winnenden, Zentrum Für Psychi...   2        462  2009   \n",
       "12860                     Stadtverwaltung, nan, nan, nan   2        585  2014   \n",
       "7217                          Landratsamt, nan, nan, nan   2        278  2010   \n",
       "\n",
       "      private_traeger art_text  \\\n",
       "10377     oeffentlich     None   \n",
       "1845           privat     None   \n",
       "461       oeffentlich     None   \n",
       "11364     oeffentlich     None   \n",
       "6136      oeffentlich     None   \n",
       "8058           privat     None   \n",
       "2028      oeffentlich     None   \n",
       "15438     oeffentlich     None   \n",
       "2134      oeffentlich     None   \n",
       "3275      oeffentlich     None   \n",
       "8021      oeffentlich     None   \n",
       "13473     oeffentlich     None   \n",
       "3960      oeffentlich     None   \n",
       "10066     oeffentlich     None   \n",
       "15308          privat     None   \n",
       "10504     oeffentlich     None   \n",
       "5600      oeffentlich     None   \n",
       "5104      oeffentlich     None   \n",
       "12860     oeffentlich     None   \n",
       "7217      oeffentlich     None   \n",
       "\n",
       "                                               betreiber  \n",
       "10377  ALFRIED KRUPP VON BOHLEN, UND HALBACH KRANKENH...  \n",
       "1845            ANSGAR BESITZGESELLSCHAFT, NAN, NAN, NAN  \n",
       "461    GEMEINSCHAFTSKRANKENHAUS, ST. ELISABETH - ST. ...  \n",
       "11364      LANDKREIS PASSAU, KRANKENHAUS GGMBH, NAN, NAN  \n",
       "6136    HEINRICH SENGELMANN KRANKENHAUS, GGMBH, NAN, NAN  \n",
       "8058                     PARK-KLINIK GMBH, NAN, NAN, NAN  \n",
       "2028   EV.-LUTH. DIAKONISSENANSTALT, ZU FLENSBURG, NA...  \n",
       "15438         ST. JOSEFHAUS POTSDAM GGMBH, NAN, NAN, NAN  \n",
       "2134   KATHOLISCHES KINDERKRANKENHAUS, WILHELMSTIFT G...  \n",
       "3275   VEREIN FÜR BERUFSGENOSSEN-, SCHAFTLICHE HEILBE...  \n",
       "8021          KLINIKUM BAD SALZUNGEN GMBH, NAN, NAN, NAN  \n",
       "13473    CUSANUS TRÄGERGESELLSCHAFT, TRIER MBH, NAN, NAN  \n",
       "3960   KREISKRANKENHAUS TORGAU, JOHANN KENTMANN GGMBH...  \n",
       "10066                 THERAPIEHILFE E. V., NAN, NAN, NAN  \n",
       "15308          DR. M. ALRAM, PRIVATKLINIK GMBH, NAN, NAN  \n",
       "10504    KREISKRANKENHAUS MECHERNICH GMBH, NAN, NAN, NAN  \n",
       "5600    KRANKENHAUSZWECKVERBAND, ASCHAFFENBURG, NAN, NAN  \n",
       "5104   KLINIKUM SCHLOSS WINNENDEN, ZENTRUM FÜR PSYCHI...  \n",
       "12860                     STADTVERWALTUNG, NAN, NAN, NAN  \n",
       "7217                          LANDRATSAMT, NAN, NAN, NAN  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2007_2015.sample(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bring them all together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [df_1992, df_1995, df_2003_2005, df_2006, df_2012, df_2007_2015]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat(dfs, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_pickle(\"Krankenhäuser.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Name1</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Traeger</th>\n",
       "      <th>T_Name1</th>\n",
       "      <th>Art</th>\n",
       "      <th>Betten_Ins</th>\n",
       "      <th>year</th>\n",
       "      <th>private_traeger</th>\n",
       "      <th>art_text</th>\n",
       "      <th>betreiber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13077</th>\n",
       "      <td>01</td>\n",
       "      <td>001</td>\n",
       "      <td>000</td>\n",
       "      <td>Diakonissenkrankenhaus</td>\n",
       "      <td>Flensburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Ev.-luth.Diakonissenanstalt, zu Flensburg, nan...</td>\n",
       "      <td>2</td>\n",
       "      <td>531</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>EV.-LUTH.DIAKONISSENANSTALT, ZU FLENSBURG, NAN...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13078</th>\n",
       "      <td>01</td>\n",
       "      <td>001</td>\n",
       "      <td>000</td>\n",
       "      <td>Malteser Krankenhaus</td>\n",
       "      <td>Flensburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Malteser St. Franziskus gGmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>317</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>MALTESER ST. FRANZISKUS GGMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13079</th>\n",
       "      <td>01</td>\n",
       "      <td>002</td>\n",
       "      <td>000</td>\n",
       "      <td>Augenklinik Bellevue</td>\n",
       "      <td>Kiel</td>\n",
       "      <td>5</td>\n",
       "      <td>Prof. Dr. med. D. Uthoff, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>35</td>\n",
       "      <td>2012</td>\n",
       "      <td>privat</td>\n",
       "      <td>None</td>\n",
       "      <td>PROF. DR. MED. D. UTHOFF, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13080</th>\n",
       "      <td>01</td>\n",
       "      <td>002</td>\n",
       "      <td>000</td>\n",
       "      <td>Universitätsklinikum</td>\n",
       "      <td>Kiel</td>\n",
       "      <td>2</td>\n",
       "      <td>Universitätsklinikum, Schleswig-Holstein, Anst...</td>\n",
       "      <td>1</td>\n",
       "      <td>1227</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>UNIVERSITÄTSKLINIKUM, SCHLESWIG-HOLSTEIN, ANST...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13081</th>\n",
       "      <td>01</td>\n",
       "      <td>002</td>\n",
       "      <td>000</td>\n",
       "      <td>Städtisches Krankenhaus</td>\n",
       "      <td>Kiel</td>\n",
       "      <td>3</td>\n",
       "      <td>Stadt Kiel, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>574</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>STADT KIEL, NAN, NAN, NAN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15002</th>\n",
       "      <td>16</td>\n",
       "      <td>076</td>\n",
       "      <td>022</td>\n",
       "      <td>Klinik im LEBEN</td>\n",
       "      <td>Greiz</td>\n",
       "      <td>5</td>\n",
       "      <td>Klinik im LEBEN GmbH, nan, nan, nan</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>2012</td>\n",
       "      <td>privat</td>\n",
       "      <td>None</td>\n",
       "      <td>KLINIK IM LEBEN GMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15003</th>\n",
       "      <td>16</td>\n",
       "      <td>076</td>\n",
       "      <td>022</td>\n",
       "      <td>Kreiskrankenhaus Greiz GmbH</td>\n",
       "      <td>Greiz</td>\n",
       "      <td>3</td>\n",
       "      <td>Kreiskrankenhaus Greiz GmbH, nan, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>321</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KREISKRANKENHAUS GREIZ GMBH, NAN, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15004</th>\n",
       "      <td>16</td>\n",
       "      <td>076</td>\n",
       "      <td>061</td>\n",
       "      <td>Kreiskrankenhaus Ronneburg</td>\n",
       "      <td>Ronneburg</td>\n",
       "      <td>3</td>\n",
       "      <td>Kreiskrankenhaus Ronneburg, Fachklinik für Ger...</td>\n",
       "      <td>2</td>\n",
       "      <td>110</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KREISKRANKENHAUS RONNEBURG, FACHKLINIK FÜR GER...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15005</th>\n",
       "      <td>16</td>\n",
       "      <td>077</td>\n",
       "      <td>001</td>\n",
       "      <td>Klinikum Altenburger Land GmbH</td>\n",
       "      <td>Altenburg</td>\n",
       "      <td>3</td>\n",
       "      <td>Klinikum, Altenburger Land  GmbH, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>567</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>KLINIKUM, ALTENBURGER LAND  GMBH, NAN, NAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15006</th>\n",
       "      <td>16</td>\n",
       "      <td>077</td>\n",
       "      <td>001</td>\n",
       "      <td>Evangelische Lukas-Stiftung</td>\n",
       "      <td>Altenburg</td>\n",
       "      <td>4</td>\n",
       "      <td>Evangelische Lukas-Stiftung, Altenburg, nan, nan</td>\n",
       "      <td>2</td>\n",
       "      <td>71</td>\n",
       "      <td>2012</td>\n",
       "      <td>oeffentlich</td>\n",
       "      <td>None</td>\n",
       "      <td>EVANGELISCHE LUKAS-STIFTUNG, ALTENBURG, NAN, NAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1930 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Land Kreis Gemeinde                           Name1        Ort  Traeger  \\\n",
       "13077   01   001      000          Diakonissenkrankenhaus  Flensburg        4   \n",
       "13078   01   001      000            Malteser Krankenhaus  Flensburg        4   \n",
       "13079   01   002      000            Augenklinik Bellevue       Kiel        5   \n",
       "13080   01   002      000            Universitätsklinikum       Kiel        2   \n",
       "13081   01   002      000         Städtisches Krankenhaus       Kiel        3   \n",
       "...    ...   ...      ...                             ...        ...      ...   \n",
       "15002   16   076      022                 Klinik im LEBEN      Greiz        5   \n",
       "15003   16   076      022     Kreiskrankenhaus Greiz GmbH      Greiz        3   \n",
       "15004   16   076      061      Kreiskrankenhaus Ronneburg  Ronneburg        3   \n",
       "15005   16   077      001  Klinikum Altenburger Land GmbH  Altenburg        3   \n",
       "15006   16   077      001     Evangelische Lukas-Stiftung  Altenburg        4   \n",
       "\n",
       "                                                 T_Name1 Art Betten_Ins  year  \\\n",
       "13077  Ev.-luth.Diakonissenanstalt, zu Flensburg, nan...   2        531  2012   \n",
       "13078       Malteser St. Franziskus gGmbH, nan, nan, nan   2        317  2012   \n",
       "13079            Prof. Dr. med. D. Uthoff, nan, nan, nan   2         35  2012   \n",
       "13080  Universitätsklinikum, Schleswig-Holstein, Anst...   1       1227  2012   \n",
       "13081                          Stadt Kiel, nan, nan, nan   2        574  2012   \n",
       "...                                                  ...  ..        ...   ...   \n",
       "15002                Klinik im LEBEN GmbH, nan, nan, nan   4         11  2012   \n",
       "15003         Kreiskrankenhaus Greiz GmbH, nan, nan, nan   2        321  2012   \n",
       "15004  Kreiskrankenhaus Ronneburg, Fachklinik für Ger...   2        110  2012   \n",
       "15005         Klinikum, Altenburger Land  GmbH, nan, nan   2        567  2012   \n",
       "15006   Evangelische Lukas-Stiftung, Altenburg, nan, nan   2         71  2012   \n",
       "\n",
       "      private_traeger art_text  \\\n",
       "13077     oeffentlich     None   \n",
       "13078     oeffentlich     None   \n",
       "13079          privat     None   \n",
       "13080     oeffentlich     None   \n",
       "13081     oeffentlich     None   \n",
       "...               ...      ...   \n",
       "15002          privat     None   \n",
       "15003     oeffentlich     None   \n",
       "15004     oeffentlich     None   \n",
       "15005     oeffentlich     None   \n",
       "15006     oeffentlich     None   \n",
       "\n",
       "                                               betreiber  \n",
       "13077  EV.-LUTH.DIAKONISSENANSTALT, ZU FLENSBURG, NAN...  \n",
       "13078       MALTESER ST. FRANZISKUS GGMBH, NAN, NAN, NAN  \n",
       "13079            PROF. DR. MED. D. UTHOFF, NAN, NAN, NAN  \n",
       "13080  UNIVERSITÄTSKLINIKUM, SCHLESWIG-HOLSTEIN, ANST...  \n",
       "13081                          STADT KIEL, NAN, NAN, NAN  \n",
       "...                                                  ...  \n",
       "15002                KLINIK IM LEBEN GMBH, NAN, NAN, NAN  \n",
       "15003         KREISKRANKENHAUS GREIZ GMBH, NAN, NAN, NAN  \n",
       "15004  KREISKRANKENHAUS RONNEBURG, FACHKLINIK FÜR GER...  \n",
       "15005         KLINIKUM, ALTENBURGER LAND  GMBH, NAN, NAN  \n",
       "15006   EVANGELISCHE LUKAS-STIFTUNG, ALTENBURG, NAN, NAN  \n",
       "\n",
       "[1930 rows x 13 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.year == \"2012\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}