Cleaning and Extracting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Umwandlung von .xml Daten in pd.DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import xml.etree.ElementTree as ET"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import von .xml Daten aus \"MDB_STAMMDATEN.XML\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Funktionen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parsen/Lesen der Daten in XML-Format\n",
    "def read_xml(path):\n",
    "   xml = ET.parse(path).getroot()\n",
    "   return xml\n",
    "\n",
    "# Extraktion von Daten aus dem XML-Code\n",
    "def search_for_name(tree, attr):\n",
    "   names = []\n",
    "   for child in tree:\n",
    "       if child.tag == attr:\n",
    "           names += [child.text]\n",
    "       else:\n",
    "           names += search_for_name(child, attr)\n",
    "   return names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Erster Test mit dem Attribut \"Geschlecht\" (--> neue Spalte)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['männlich', 'männlich', 'weiblich', 'weiblich']\n",
      "4073\n"
     ]
    }
   ],
   "source": [
    "if __name__==\"__main__\":\n",
    "    MdBxml = read_xml(\"MDB_STAMMDATEN.XML\")\n",
    "    names = search_for_name(MdBxml,\"GESCHLECHT\")\n",
    "\n",
    "# Überprüfung\n",
    "print(names[:4])\n",
    "print(len(names))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Erste Ansätze für ein vorläufiges DataFrame mit den folgenden Spalten:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merkmale (--> \"WP\" steht für Wahlperioden)\n",
    "liste_merk = [\"ID\", \"NACHNAME\", \"VORNAME\",\"GESCHLECHT\", \"PARTEI_KURZ\",\"GEBURTSDATUM\",\"BERUF\", \"GEBURTSLAND\", \"WP\"]\n",
    "\n",
    "# Einmaliges zusammensetzten einer Liste von Listen mit den Ausprägungen der obigen Merkmale\n",
    "column_list = []\n",
    "for attr in liste_merk:\n",
    "    if __name__==\"__main__\":\n",
    "        MdBxml = read_xml(\"MDB_STAMMDATEN.XML\")\n",
    "        names = search_for_name(MdBxml, attr)\n",
    "        column_list.append(names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Nun wird die Kompatibilität der einzelnen Listen überprüft.\n",
    "\n",
    "#### Es soll ein pd.DataFrame erstellt werden und dafür müssen die Spaltenlängen übereinstimmen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\n",
      "4073\n",
      "4336\n",
      "4336\n",
      "4073\n",
      "4073\n",
      "4073\n",
      "4073\n",
      "4073\n",
      "11600\n"
     ]
    }
   ],
   "source": [
    "print(len(column_list))\n",
    "\n",
    "for i in column_list:\n",
    "    print(len(i))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mit dieser verkürzten Liste lässt sich schon ein DataFrame erstellen ( Länge = 4073 Einträge, da die IDs definitiv eimalig sind). \n",
    "\n",
    "#### Etwas umständlich, aber wirksam wird ein erstes DataFrame zusammengestellt und ein erster Mangel behoben "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11000001</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1930</td>\n",
       "      <td>Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...</td>\n",
       "      <td>Deutschland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11000002</td>\n",
       "      <td>männlich</td>\n",
       "      <td>FDP</td>\n",
       "      <td>1909</td>\n",
       "      <td>Rechtsanwalt und Notar</td>\n",
       "      <td>Deutschland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11000003</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1913</td>\n",
       "      <td>Hilfsreferentin</td>\n",
       "      <td>Jugoslawien</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11000004</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1933</td>\n",
       "      <td>Ärztin</td>\n",
       "      <td>Deutschland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11000005</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1950</td>\n",
       "      <td>Mathematiker, Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "0  11000001   männlich         CDU         1930   \n",
       "1  11000002   männlich         FDP         1909   \n",
       "2  11000003   weiblich         CDU         1913   \n",
       "3  11000004   weiblich         CDU         1933   \n",
       "4  11000005   männlich         CDU         1950   \n",
       "\n",
       "                                               BERUF  GEBURTSLAND  \n",
       "0  Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...  Deutschland  \n",
       "1                             Rechtsanwalt und Notar  Deutschland  \n",
       "2                                    Hilfsreferentin  Jugoslawien  \n",
       "3                                             Ärztin  Deutschland  \n",
       "4                      Mathematiker, Geschäftsführer  Deutschland  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "liste_merk_2 = [\"ID\",\"GESCHLECHT\", \"PARTEI_KURZ\",\"GEBURTSDATUM\",\"BERUF\", \"GEBURTSLAND\"]\n",
    "\n",
    "df_first = pd.DataFrame(np.column_stack([column_list[0],column_list[3],column_list[4],column_list[5],column_list[6],column_list[7]]), columns = liste_merk_2)\n",
    "\n",
    "# None values in \"Geburtsland werden durch \"Deutschland\" ersetzt\n",
    "df_first.loc[df_first[\"GEBURTSLAND\"].isnull(), \"GEBURTSLAND\"] = \"Deutschland\"\n",
    "\n",
    "df_first.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Bei den Datentypen besteht noch Verbesserungsbedarf, dazu aber später mehr."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID              object\n",
       "GESCHLECHT      object\n",
       "PARTEI_KURZ     object\n",
       "GEBURTSDATUM    object\n",
       "BERUF           object\n",
       "GEBURTSLAND     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_first.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mit Hilfe einer höheren Macht lässt sich der Parsing-Prozess weiterentwickeln.\n",
    "\n",
    "#### Hier die Anwendung für die Vor- und Nachnamen:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11000001</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1930</td>\n",
       "      <td>Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Abelein</td>\n",
       "      <td>Manfred</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11000002</td>\n",
       "      <td>männlich</td>\n",
       "      <td>FDP</td>\n",
       "      <td>1909</td>\n",
       "      <td>Rechtsanwalt und Notar</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Achenbach</td>\n",
       "      <td>Ernst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11000003</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1913</td>\n",
       "      <td>Hilfsreferentin</td>\n",
       "      <td>Jugoslawien</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Annemarie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11000004</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1933</td>\n",
       "      <td>Ärztin</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Else</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11000005</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1950</td>\n",
       "      <td>Mathematiker, Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Ulrich</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "0  11000001   männlich         CDU         1930   \n",
       "1  11000002   männlich         FDP         1909   \n",
       "2  11000003   weiblich         CDU         1913   \n",
       "3  11000004   weiblich         CDU         1933   \n",
       "4  11000005   männlich         CDU         1950   \n",
       "\n",
       "                                               BERUF  GEBURTSLAND   NACHNAME  \\\n",
       "0  Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...  Deutschland    Abelein   \n",
       "1                             Rechtsanwalt und Notar  Deutschland  Achenbach   \n",
       "2                                    Hilfsreferentin  Jugoslawien  Ackermann   \n",
       "3                                             Ärztin  Deutschland  Ackermann   \n",
       "4                      Mathematiker, Geschäftsführer  Deutschland       Adam   \n",
       "\n",
       "     VORNAME  \n",
       "0    Manfred  \n",
       "1      Ernst  \n",
       "2  Annemarie  \n",
       "3       Else  \n",
       "4     Ulrich  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Funktion\n",
    "def search_for_nameRoots(root):\n",
    "    names = []\n",
    "    for child in root:\n",
    "        if child.tag == \"NAMEN\":\n",
    "            names += [child]\n",
    "        else:\n",
    "            names += search_for_nameRoots(child)\n",
    "    return names\n",
    "\n",
    "\n",
    "nameRoots = search_for_nameRoots(MdBxml)\n",
    "\n",
    "# Listen für Nach- und Vornamen\n",
    "firstNameElements = []\n",
    "for element in nameRoots:\n",
    "    firstNameElements += [element[0].find('VORNAME').text]\n",
    "\n",
    "lastNameElements = []\n",
    "for element in nameRoots:\n",
    "    lastNameElements += [element[0].find('NACHNAME').text]\n",
    "\n",
    "# Spalten erstellen    \n",
    "df_first[\"NACHNAME\"] = lastNameElements\n",
    "df_first[\"VORNAME\"] = firstNameElements\n",
    "\n",
    "#Die Integration ist geglückt!\n",
    "df_first.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Nun muss der Parsing-Prozess nochmal umgestaltet werden, um zu einer spezifischen ID/einem spezifischen MdB die Nummern aller Wahlperioden zu finden. \n",
    "#### Langfrisitg soll auf diese Perioden umindiziert werden."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['5', '6', '7', '8', '9', '10', '11'], ['3', '4', '5', '6', '7'], ['2', '3', '4'], ['11', '12']]\n",
      "4073\n"
     ]
    }
   ],
   "source": [
    "# Die folgende Funktion soll eine Liste mit Listen dieser nummerierten Wahlperioden zu jeden MdB produzieren\n",
    "\n",
    "\n",
    "def search_for_wpRoots1(root1):\n",
    "\n",
    "    # Nested-function: Entscheidend ist hier der Break beim nächsten \"ID\"-Tag, da diese für jedes MdB einzigartig sind.\n",
    "    def search_for_wpRoots2(root2):\n",
    "        wplist2 = []\n",
    "        for child2 in root2:\n",
    "            if child2.tag == \"WP\":\n",
    "                wplist2 += [child2]\n",
    "            elif child2.tag == \"ID\":\n",
    "                break\n",
    "            else:\n",
    "                wplist2 += search_for_wpRoots2(child2)\n",
    "        return wplist2\n",
    "    # Ende nested-function\n",
    "    \n",
    "    wplist = []\n",
    "    for child in root1:\n",
    "        if child.tag == \"WAHLPERIODEN\":\n",
    "            wplist += [search_for_wpRoots2(child)]\n",
    "        else:\n",
    "            wplist += search_for_wpRoots1(child)\n",
    "    return wplist\n",
    "\n",
    "\n",
    "\n",
    "# Anwenden\n",
    "WProots = search_for_wpRoots1(MdBxml)\n",
    "    \n",
    "# Nun müssen noch die gewünschten Nummern aus dem Code extrahiert werden    \n",
    "\n",
    "#Funktion (wie schon weiter oben bei anderen Merkmalen)\n",
    "def extracting_WP(list1):\n",
    "    WPelements = []\n",
    "    for i in list1:\n",
    "        sub_list = []\n",
    "        for a in i:\n",
    "            sub_list += [a.text]\n",
    "        WPelements += [sub_list]\n",
    "    return WPelements\n",
    "\n",
    "# Anwenden\n",
    "WPs = extracting_WP(WProots)\n",
    "\n",
    "# Wie sieht's aus?\n",
    "print(WPs[:4])\n",
    "# Testen der neuen Liste auf längenmäßige Kompatibilität mit dem DF  \n",
    "print(len(WPs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11000001</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1930</td>\n",
       "      <td>Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Abelein</td>\n",
       "      <td>Manfred</td>\n",
       "      <td>[5, 6, 7, 8, 9, 10, 11]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11000002</td>\n",
       "      <td>männlich</td>\n",
       "      <td>FDP</td>\n",
       "      <td>1909</td>\n",
       "      <td>Rechtsanwalt und Notar</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Achenbach</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>[3, 4, 5, 6, 7]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11000003</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1913</td>\n",
       "      <td>Hilfsreferentin</td>\n",
       "      <td>Jugoslawien</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Annemarie</td>\n",
       "      <td>[2, 3, 4]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11000004</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1933</td>\n",
       "      <td>Ärztin</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Else</td>\n",
       "      <td>[11, 12]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11000005</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1950</td>\n",
       "      <td>Mathematiker, Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Ulrich</td>\n",
       "      <td>[15, 16, 12, 13, 14]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "0  11000001   männlich         CDU         1930   \n",
       "1  11000002   männlich         FDP         1909   \n",
       "2  11000003   weiblich         CDU         1913   \n",
       "3  11000004   weiblich         CDU         1933   \n",
       "4  11000005   männlich         CDU         1950   \n",
       "\n",
       "                                               BERUF  GEBURTSLAND   NACHNAME  \\\n",
       "0  Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...  Deutschland    Abelein   \n",
       "1                             Rechtsanwalt und Notar  Deutschland  Achenbach   \n",
       "2                                    Hilfsreferentin  Jugoslawien  Ackermann   \n",
       "3                                             Ärztin  Deutschland  Ackermann   \n",
       "4                      Mathematiker, Geschäftsführer  Deutschland       Adam   \n",
       "\n",
       "     VORNAME             WAHLPERIODEN  \n",
       "0    Manfred  [5, 6, 7, 8, 9, 10, 11]  \n",
       "1      Ernst          [3, 4, 5, 6, 7]  \n",
       "2  Annemarie                [2, 3, 4]  \n",
       "3       Else                 [11, 12]  \n",
       "4     Ulrich     [15, 16, 12, 13, 14]  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Einfügen in das DataFrame\n",
    "df_first[\"WAHLPERIODEN\"] = WPs\n",
    "df_first.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Der gleiche Prozess wird nun auch für die Manadatsart gewählt.\n",
    "#### In der Liste, die nun zu jedem MdB erstellt wird, kann man die Mandatsart zur jeweiligen Wahlperiode (Index wie in Wahlperioden) finden."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Direktwahl', 'Direktwahl', 'Direktwahl', 'Direktwahl', 'Direktwahl', 'Direktwahl', 'Direktwahl'], ['Landesliste', 'Landesliste', 'Landesliste', 'Landesliste', 'Landesliste'], ['Landesliste', 'Landesliste', 'Landesliste']]\n",
      "4073\n"
     ]
    }
   ],
   "source": [
    "# Funktion (wie zuvor)\n",
    "\n",
    "\n",
    "def search_for_MandatsArtRoots1(root):\n",
    "    \n",
    "    # Nested-function\n",
    "    def search_for_MandatRoots2(root2):\n",
    "        mandatlist2 = []\n",
    "        for child2 in root2:\n",
    "            if child2.tag == \"MANDATSART\":\n",
    "                mandatlist2 += [child2]\n",
    "            elif child2.tag == \"ID\":\n",
    "                break\n",
    "            else:\n",
    "                mandatlist2 += search_for_MandatRoots2(child2)\n",
    "        return mandatlist2\n",
    "    # Ende nested-function\n",
    "    \n",
    "    mandatlist = []\n",
    "    for child in root:\n",
    "        if child.tag == \"WAHLPERIODEN\":\n",
    "            mandatlist += [search_for_MandatRoots2(child)]\n",
    "        else:\n",
    "            mandatlist += search_for_MandatsArtRoots1(child)\n",
    "    return mandatlist\n",
    "\n",
    "\n",
    "\n",
    "MandatRoots = search_for_MandatsArtRoots1(MdBxml)\n",
    "\n",
    "\n",
    "# Nun müssen noch die gewünschten Mandatsarten aus dem Code extrahiert werden    \n",
    "def extracting_Mandate(list1):\n",
    "    MandatElements = []\n",
    "    for i in list1:\n",
    "        sub_list = []\n",
    "        for a in i:\n",
    "            sub_list += [a.text]\n",
    "        MandatElements += [sub_list]\n",
    "    return MandatElements\n",
    "\n",
    "Mandate = extracting_Mandate(MandatRoots)\n",
    "\n",
    "# Wie sieht's hier aus?\n",
    "print(Mandate[:3])\n",
    "# Testen der neuen Liste auf längenmäßige Kompatibilität mit dem DF  \n",
    "print(len(Mandate))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11000001</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1930</td>\n",
       "      <td>Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Abelein</td>\n",
       "      <td>Manfred</td>\n",
       "      <td>[5, 6, 7, 8, 9, 10, 11]</td>\n",
       "      <td>[Direktwahl, Direktwahl, Direktwahl, Direktwah...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11000002</td>\n",
       "      <td>männlich</td>\n",
       "      <td>FDP</td>\n",
       "      <td>1909</td>\n",
       "      <td>Rechtsanwalt und Notar</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Achenbach</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>[3, 4, 5, 6, 7]</td>\n",
       "      <td>[Landesliste, Landesliste, Landesliste, Landes...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11000003</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1913</td>\n",
       "      <td>Hilfsreferentin</td>\n",
       "      <td>Jugoslawien</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Annemarie</td>\n",
       "      <td>[2, 3, 4]</td>\n",
       "      <td>[Landesliste, Landesliste, Landesliste]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11000004</td>\n",
       "      <td>weiblich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1933</td>\n",
       "      <td>Ärztin</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Else</td>\n",
       "      <td>[11, 12]</td>\n",
       "      <td>[Volkskammer, Landesliste]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11000005</td>\n",
       "      <td>männlich</td>\n",
       "      <td>CDU</td>\n",
       "      <td>1950</td>\n",
       "      <td>Mathematiker, Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Ulrich</td>\n",
       "      <td>[15, 16, 12, 13, 14]</td>\n",
       "      <td>[Direktwahl, Direktwahl, Direktwahl, Direktwah...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "0  11000001   männlich         CDU         1930   \n",
       "1  11000002   männlich         FDP         1909   \n",
       "2  11000003   weiblich         CDU         1913   \n",
       "3  11000004   weiblich         CDU         1933   \n",
       "4  11000005   männlich         CDU         1950   \n",
       "\n",
       "                                               BERUF  GEBURTSLAND   NACHNAME  \\\n",
       "0  Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...  Deutschland    Abelein   \n",
       "1                             Rechtsanwalt und Notar  Deutschland  Achenbach   \n",
       "2                                    Hilfsreferentin  Jugoslawien  Ackermann   \n",
       "3                                             Ärztin  Deutschland  Ackermann   \n",
       "4                      Mathematiker, Geschäftsführer  Deutschland       Adam   \n",
       "\n",
       "     VORNAME             WAHLPERIODEN  \\\n",
       "0    Manfred  [5, 6, 7, 8, 9, 10, 11]   \n",
       "1      Ernst          [3, 4, 5, 6, 7]   \n",
       "2  Annemarie                [2, 3, 4]   \n",
       "3       Else                 [11, 12]   \n",
       "4     Ulrich     [15, 16, 12, 13, 14]   \n",
       "\n",
       "                                        MANDATSARTEN  \n",
       "0  [Direktwahl, Direktwahl, Direktwahl, Direktwah...  \n",
       "1  [Landesliste, Landesliste, Landesliste, Landes...  \n",
       "2            [Landesliste, Landesliste, Landesliste]  \n",
       "3                         [Volkskammer, Landesliste]  \n",
       "4  [Direktwahl, Direktwahl, Direktwahl, Direktwah...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Einfügen in das DataFrame\n",
    "df_first[\"MANDATSARTEN\"] = Mandate\n",
    "df_first.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Das Dataframe muss nun gecleant werden, um gut für die Visualisierung genutzt werden zu können. \n",
    "\n",
    "Hierzu muss:\n",
    "- Parteien sinnvoll zusammengefasst werden\n",
    "- geguckt werden, ob ein drittes Geschlecht vorliegt und mit Boolean Werten ersetzt werden\n",
    "- ID auf Dopplungen überprüft werden\n",
    "- Geburtsdatum mit Alter ersetzt werden\n",
    "- Geburtsland mit Boolean Werten ersetzt werden\n",
    "- Dopplungen von Abgeordneten überprüft werden\n",
    "- Mandatsarten mit Boolean Werten ersetzt werden (0=Direktwahl, 1=Landesliste, Volkskammer wird zu Landesliste gezählt)\n",
    "- Einzelne Columns für die WP erstellt werden, um pivotisieren zu können\n",
    "- Die Anzahl der Abgeordneten in jeder Wahlperiode mit historischen Daten abgeglichen werden"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['CDU', 'FDP', 'SPD', 'KPD', 'DP', 'CSU', 'DZP', 'FU', 'GRÜNE',\n",
       "       'BP', 'BÜNDNIS 90/DIE GRÜNEN', 'GB/ BHE', 'DIE GRÜNEN/BÜNDNIS 90',\n",
       "       'PDS', 'PDS/LL', 'DIE LINKE.', 'SRP', 'Plos', None, 'WAV', 'DRP',\n",
       "       'CSUS', 'DSU', 'DPB', 'CVP', 'DPS', 'AfD', 'Blaue'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Gucken, welche Parteien vorhanden sind\n",
    "df_first['PARTEI_KURZ'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Glossar:\n",
    "\n",
    "* KPD = Kommunistische Partei Deutschlands\n",
    "* DP = Deutsche Partei\n",
    "* DZP = Deutsche Zentrumspartei\n",
    "* FU = Föderalistische Union (Fraktion aus BP und DZP)\n",
    "* BP = Bayernpartei\n",
    "* GB/ BHE = Gesamtdeutscher Block/Bund der Heimatvertriebenen und Entrechteten\n",
    "* PDS = Partei des Demokratischen Sozialismus \n",
    "* PDS/LL = Partei des Demokratischen Sozialismus - Landesliste\n",
    "* SRP = Sozialistische Reichspartei\n",
    "* Plos = Parteilos\n",
    "* WAV = Wirtschaftliche Aufbau-Vereinigung\n",
    "* DRP = Deutsche Reichspartei\n",
    "* CSUS = CSU-Saar\n",
    "* DSU = Deutsche Soziale Union\n",
    "* DPB = Deutsche Partei Bayern\n",
    "* CVP = Christdemokratische Volkspartei des Saarlandes\n",
    "* DPS = Demokratische Partei Saar (Saarpartei der FDP)\n",
    "* Blaue = Blaue Partei\n",
    "\n",
    "None Einträge müssen gefüllt oder gedropt werden\n",
    "\n",
    "CDU, CSU, CSUS, CVP -> Union\n",
    "\n",
    "Grüne, Bündnis 90/Die Grünen, Die Grünen/Bündnis 90 -> Bündnis 90/Die Grünen\n",
    "\n",
    "PDS, PDS/LL, Die Linke -> Die Linke\n",
    "\n",
    "FDP, DPS -> FDP\n",
    "\n",
    "AfD, Blaue -> AfD\n",
    "\n",
    "KPD, DP, DZP, FU, BP, GB/ BHE, SRP, WAV, DRP, DSU, DPB, Plos -> Andere"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Zusammenfassen von Parteien\n",
    "df_first['PARTEI_KURZ'].replace([\"CDU\", \"CSU\", \"CSUS\", \"CVP\"],[\"Union\", \"Union\", \"Union\", \"Union\"], inplace = True)\n",
    "df_first['PARTEI_KURZ'].replace([\"GRÜNE\", \"BÜNDNIS 90/DIE GRÜNEN\", \"DIE GRÜNEN/BÜNDNIS 90\"],[\"Bündnis 90/Die Grünen\", \"Bündnis 90/Die Grünen\", \"Bündnis 90/Die Grünen\"], inplace = True)\n",
    "df_first['PARTEI_KURZ'].replace([\"PDS\", \"PDS/LL\", \"DIE LINKE.\"],[\"Die Linke\", \"Die Linke\", \"Die Linke\"], inplace = True)\n",
    "df_first['PARTEI_KURZ'].replace([\"FDP\", \"DPS\"],[\"FDP\", \"FDP\"], inplace = True)\n",
    "df_first['PARTEI_KURZ'].replace([\"AfD\", \"Blaue\"],[\"AfD\", \"AfD\"], inplace = True)\n",
    "df_first['PARTEI_KURZ'].replace([\"KPD\", \"DP\", \"DZP\", \"FU\", \"BP\", \"GB/ BHE\", \"SRP\", \"WAV\", \"DRP\", \"DSU\", \"DPB\", \"Plos\"],[\"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\", \"Andere\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Füllen von None Werten mit Andere\n",
    "df_first.PARTEI_KURZ.fillna(value=\"Andere\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Union', 'FDP', 'SPD', 'Andere', 'Bündnis 90/Die Grünen',\n",
       "       'Die Linke', 'AfD'], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Das Zusammenfassen ist geglückt\n",
    "df_first['PARTEI_KURZ'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['männlich', 'weiblich'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Überpüfung der Anzahl an Geschlechtern\n",
    "df_first['GESCHLECHT'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ID, GESCHLECHT, PARTEI_KURZ, GEBURTSDATUM, BERUF, GEBURTSLAND, NACHNAME, VORNAME, WAHLPERIODEN, MANDATSARTEN]\n",
       "Index: []"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Überprüfung auf Dopplungen der ID\n",
    "df_first[df_first.duplicated(subset=\"ID\", keep=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ID, GESCHLECHT, PARTEI_KURZ, GEBURTSDATUM, BERUF, GEBURTSLAND, NACHNAME, VORNAME, WAHLPERIODEN, MANDATSARTEN]\n",
       "Index: []"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Überprüfung auf Dopplungen der Namen\n",
    "df_first[df_first.duplicated(subset=[\"NACHNAME\", \"VORNAME\", \"GEBURTSDATUM\"], keep=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1930', '1909', '1913', '1933', '1950', '1919', '1912', '1876',\n",
       "       '1944', '1920', '1941', '1904', '1922', '1879', '1902', '1924',\n",
       "       '1936', '1890', '1901', '1943', '1914', '1896', '1947', '1949',\n",
       "       '1928', '1946', '1889', '1888', '1955', '1934', '1940', '1951',\n",
       "       '1923', '1942', '1880', '1939', '1932', '1925', '1918', '1894',\n",
       "       '1927', '1882', '1899', '1931', '1895', '1898', '1964', '1915',\n",
       "       '1921', '1893', '1938', '1917', '1926', '1911', '1891', '1903',\n",
       "       '1937', '1905', '1900', '1929', '1910', '1957', '1908', '1907',\n",
       "       '1897', '1883', '1945', '1954', '1906', '1948', '1952', '1935',\n",
       "       '1956', '1959', '1887', '1884', '1885', '1966', '1953', '1892',\n",
       "       '1886', '1916', '1958', '1960', '1965', '1961', '1963', '1881',\n",
       "       '1877', '1875', '1878', '1962', '1971', '1968', '1967', '1969',\n",
       "       '1972', '1973', '1970', '1976', '1975', '1974', '1977', '1983',\n",
       "       '1978', '1980', '1979', '1981', '1982', '1986', '1985', '1984',\n",
       "       '1987', '1989', '1992', '1988', '1990', '1991'], dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Überpüfung auf Korrektheit der Geburtsjahre\n",
    "df_first['GEBURTSDATUM'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Deutschland', 'Jugoslawien', 'Australien', 'Kenia', 'Spanien',\n",
       "       'CSSR', 'Ukraine', 'Finnland', 'Dänemark', 'Österreich',\n",
       "       'Lettland', 'Oberösterreich',\n",
       "       'Tansania (ehem. Deutsch-Ost-Afrika)', 'Niederlande',\n",
       "       'Niederösterreich', 'Estland', 'ehem. Deutsch-Ost-Afrika',\n",
       "       'Tschechoslowakei', 'Slowakei', 'Ceylon', 'Rumänien', 'Perú',\n",
       "       'Schweiz', 'Israel', 'Ungarn', 'Frankreich', 'Tonga-Insel, Südsee',\n",
       "       '(damalige) CSR', 'Tansania', 'USA', 'CSFR', 'Polen',\n",
       "       'Südwestafrika', 'Türkei', 'Italien', 'Kroatien', 'Iran',\n",
       "       'Russland'], dtype=object)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Überpüfung auf Korrektheit der Geburtsorte\n",
    "df_first['GEBURTSLAND'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Um unser DF analysierbarer zu machen legen wir nun boolean-Spalten an.\n",
    "# Alle Geburtsländer, die nicht Deutschland sind, werden der Einfachheit halber zusammengefasst und mit 0 kodiert\n",
    "df_first[\"AUSLAND\"] = df_first[\"GEBURTSLAND\"] != \"Deutschland\"\n",
    "df_first['AUSLAND'].replace([True, False], [1,0], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ähnliche Umgestaltung auch für die Spalte mt dem Geschlecht\n",
    "df_first['GESCHLECHT'].replace([\"männlich\", \"weiblich\"],[1,0], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Das Gleiche noch einmal für Mandatsart: Volkskammer wird hier zu Landesliste zusammengefasst\n",
    "mandate = list(df_first['MANDATSARTEN'])\n",
    "dictionarym = {\"Direktwahl\": 1, \"Landesliste\": 0, \"Volkskammer\": 0}\n",
    "\n",
    "def replace_mandate(mandate_list, dictionary):\n",
    "    mandate_list = [[dictionary.get(item, item) for item in lst] for lst in mandate_list]\n",
    "    return mandate_list\n",
    "\n",
    "mandate = replace_mandate(mandate, dictionarym)\n",
    "df_first[\"MANDATSARTEN\"] = mandate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivotisierung, Feature Engineering und Export"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Das Cleaning ist abgeschlossen, jetzt geht es an die Pivotisierung"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "      <th>AUSLAND</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11000001</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1930</td>\n",
       "      <td>Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Abelein</td>\n",
       "      <td>Manfred</td>\n",
       "      <td>[5, 6, 7, 8, 9, 10, 11]</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11000002</td>\n",
       "      <td>1</td>\n",
       "      <td>FDP</td>\n",
       "      <td>1909</td>\n",
       "      <td>Rechtsanwalt und Notar</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Achenbach</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>[3, 4, 5, 6, 7]</td>\n",
       "      <td>[0, 0, 0, 0, 0]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11000003</td>\n",
       "      <td>0</td>\n",
       "      <td>Union</td>\n",
       "      <td>1913</td>\n",
       "      <td>Hilfsreferentin</td>\n",
       "      <td>Jugoslawien</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Annemarie</td>\n",
       "      <td>[2, 3, 4]</td>\n",
       "      <td>[0, 0, 0]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11000004</td>\n",
       "      <td>0</td>\n",
       "      <td>Union</td>\n",
       "      <td>1933</td>\n",
       "      <td>Ärztin</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Ackermann</td>\n",
       "      <td>Else</td>\n",
       "      <td>[11, 12]</td>\n",
       "      <td>[0, 0]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11000005</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1950</td>\n",
       "      <td>Mathematiker, Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Ulrich</td>\n",
       "      <td>[15, 16, 12, 13, 14]</td>\n",
       "      <td>[1, 1, 1, 1, 1]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID  GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "0  11000001           1       Union         1930   \n",
       "1  11000002           1         FDP         1909   \n",
       "2  11000003           0       Union         1913   \n",
       "3  11000004           0       Union         1933   \n",
       "4  11000005           1       Union         1950   \n",
       "\n",
       "                                               BERUF  GEBURTSLAND   NACHNAME  \\\n",
       "0  Rechtsanwalt, Wirtschaftsprüfer, Universitätsp...  Deutschland    Abelein   \n",
       "1                             Rechtsanwalt und Notar  Deutschland  Achenbach   \n",
       "2                                    Hilfsreferentin  Jugoslawien  Ackermann   \n",
       "3                                             Ärztin  Deutschland  Ackermann   \n",
       "4                      Mathematiker, Geschäftsführer  Deutschland       Adam   \n",
       "\n",
       "     VORNAME             WAHLPERIODEN           MANDATSARTEN  AUSLAND  \n",
       "0    Manfred  [5, 6, 7, 8, 9, 10, 11]  [1, 1, 1, 1, 1, 1, 1]        0  \n",
       "1      Ernst          [3, 4, 5, 6, 7]        [0, 0, 0, 0, 0]        0  \n",
       "2  Annemarie                [2, 3, 4]              [0, 0, 0]        1  \n",
       "3       Else                 [11, 12]                 [0, 0]        0  \n",
       "4     Ulrich     [15, 16, 12, 13, 14]        [1, 1, 1, 1, 1]        0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfnew = df_first.copy()\n",
    "dfnew.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Für die Pivotisierung wollen wir jetzt 19 Spalten für die verschiedenen Wahlperioden erstellen und diese mit Elementen aus [1,0] besetzen.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]\n"
     ]
    }
   ],
   "source": [
    "# Wie viele Wahlperioden gibt es?\n",
    "\n",
    "unique_list = []\n",
    "for e in dfnew[\"WAHLPERIODEN\"]:\n",
    "    for i in e:\n",
    "        if int(i) in unique_list:\n",
    "            None\n",
    "        else:\n",
    "            unique_list.append(int(i))\n",
    "            \n",
    "unique_list.sort()            \n",
    "print(unique_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Erstellen der 19 Spalten:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Funktion\n",
    "\n",
    "def WPcolumnchecker(column, range1):\n",
    "    \n",
    "    # Diese Funktion soll eine iteration über die Listen in der Spalte \"Wahlperioden\" durchführen\n",
    "    def listchecker(WPNum, WPList):\n",
    "        if WPNum in WPList:\n",
    "            return 1\n",
    "        else:\n",
    "            return 0\n",
    "    # Ende\n",
    "    for n in range1:\n",
    "        WPstr = \"WP\" + str(n)\n",
    "        binary_list = []\n",
    "        nstr= str(n)\n",
    "        for index in range(0,4073):\n",
    "            WPListe = column.loc[index]\n",
    "            binary_list.append(listchecker(nstr, WPListe))\n",
    "        dfnew[WPstr] = binary_list\n",
    "        \n",
    "\n",
    "        \n",
    "# Anwendung\n",
    "dfrange1 = unique_list\n",
    "dfcolumn = dfnew[\"WAHLPERIODEN\"]\n",
    "\n",
    "WPcolumnchecker(dfcolumn, dfrange1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Überprüfung für ein Beispiel\n",
    "- Sind die 1 und 0 an den richtigen Stellen? --> z.B WP3 = 1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID                            11000002\n",
      "GESCHLECHT                           1\n",
      "PARTEI_KURZ                        FDP\n",
      "GEBURTSDATUM                      1909\n",
      "BERUF           Rechtsanwalt und Notar\n",
      "GEBURTSLAND                Deutschland\n",
      "NACHNAME                     Achenbach\n",
      "VORNAME                          Ernst\n",
      "WAHLPERIODEN           [3, 4, 5, 6, 7]\n",
      "MANDATSARTEN           [0, 0, 0, 0, 0]\n",
      "AUSLAND                              0\n",
      "WP1                                  0\n",
      "WP2                                  0\n",
      "WP3                                  1\n",
      "WP4                                  1\n",
      "WP5                                  1\n",
      "WP6                                  1\n",
      "WP7                                  1\n",
      "WP8                                  0\n",
      "WP9                                  0\n",
      "WP10                                 0\n",
      "WP11                                 0\n",
      "WP12                                 0\n",
      "WP13                                 0\n",
      "WP14                                 0\n",
      "WP15                                 0\n",
      "WP16                                 0\n",
      "WP17                                 0\n",
      "WP18                                 0\n",
      "WP19                                 0\n",
      "Name: 1, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Überprüfung an einem Beispiel --> sind die 1 und 0 an den richtigen Stellen? --> z.B WP3 = 1 \n",
    "print(dfnew.loc[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Um ein DF nach den Wahlperioden zu indizieren, müssen zunächst die \"Bundestage 1 bis 19\" aus dem Mitgliederpool zusammen gestellt werden"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Es gab 711 Abgeordnete im 19.Bundestag\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "      <th>...</th>\n",
       "      <th>WP10</th>\n",
       "      <th>WP11</th>\n",
       "      <th>WP12</th>\n",
       "      <th>WP13</th>\n",
       "      <th>WP14</th>\n",
       "      <th>WP15</th>\n",
       "      <th>WP16</th>\n",
       "      <th>WP17</th>\n",
       "      <th>WP18</th>\n",
       "      <th>WP19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>11000198</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1952</td>\n",
       "      <td>Landwirtschaftsmeister, Parl. Staatssekretär</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Bleser</td>\n",
       "      <td>Peter</td>\n",
       "      <td>[12, 13, 14, 15, 16, 17, 18, 19]</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1]</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>11000365</td>\n",
       "      <td>1</td>\n",
       "      <td>Die Linke</td>\n",
       "      <td>1950</td>\n",
       "      <td>Autor, Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Dehm-Desoi</td>\n",
       "      <td>Jörg-Diether</td>\n",
       "      <td>[12, 16, 17, 18, 19]</td>\n",
       "      <td>[0, 0, 0, 0, 0]</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>609</th>\n",
       "      <td>11000616</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1952</td>\n",
       "      <td>Parl. Staatssekretär</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Fuchtel</td>\n",
       "      <td>Hans-Joachim</td>\n",
       "      <td>[11, 12, 13, 14, 15, 16, 17, 18, 19]</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1]</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>749</th>\n",
       "      <td>11000756</td>\n",
       "      <td>1</td>\n",
       "      <td>Die Linke</td>\n",
       "      <td>1948</td>\n",
       "      <td>Rechtsanwalt</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Gysi</td>\n",
       "      <td>Gregor</td>\n",
       "      <td>[11, 12, 13, 14, 16, 17, 18, 19]</td>\n",
       "      <td>[0, 1, 1, 1, 1, 1, 1, 1]</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1014</th>\n",
       "      <td>11001023</td>\n",
       "      <td>0</td>\n",
       "      <td>Die Linke</td>\n",
       "      <td>1951</td>\n",
       "      <td>Dipl.-Soziologin, Journalistin</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Jelpke</td>\n",
       "      <td>Ulla</td>\n",
       "      <td>[12, 13, 14, 16, 17, 18, 19]</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "195   11000198           1       Union         1952   \n",
       "360   11000365           1   Die Linke         1950   \n",
       "609   11000616           1       Union         1952   \n",
       "749   11000756           1   Die Linke         1948   \n",
       "1014  11001023           0   Die Linke         1951   \n",
       "\n",
       "                                             BERUF  GEBURTSLAND    NACHNAME  \\\n",
       "195   Landwirtschaftsmeister, Parl. Staatssekretär  Deutschland      Bleser   \n",
       "360                         Autor, Geschäftsführer  Deutschland  Dehm-Desoi   \n",
       "609                           Parl. Staatssekretär  Deutschland     Fuchtel   \n",
       "749                                   Rechtsanwalt  Deutschland        Gysi   \n",
       "1014                Dipl.-Soziologin, Journalistin  Deutschland      Jelpke   \n",
       "\n",
       "           VORNAME                          WAHLPERIODEN  \\\n",
       "195          Peter      [12, 13, 14, 15, 16, 17, 18, 19]   \n",
       "360   Jörg-Diether                  [12, 16, 17, 18, 19]   \n",
       "609   Hans-Joachim  [11, 12, 13, 14, 15, 16, 17, 18, 19]   \n",
       "749         Gregor      [11, 12, 13, 14, 16, 17, 18, 19]   \n",
       "1014          Ulla          [12, 13, 14, 16, 17, 18, 19]   \n",
       "\n",
       "                     MANDATSARTEN  ...   WP10  WP11  WP12  WP13  WP14  WP15  \\\n",
       "195      [1, 1, 1, 1, 1, 1, 1, 1]  ...      0     0     1     1     1     1   \n",
       "360               [0, 0, 0, 0, 0]  ...      0     0     1     0     0     0   \n",
       "609   [1, 1, 1, 1, 1, 1, 1, 1, 1]  ...      0     1     1     1     1     1   \n",
       "749      [0, 1, 1, 1, 1, 1, 1, 1]  ...      0     1     1     1     1     0   \n",
       "1014        [0, 0, 0, 0, 0, 0, 0]  ...      0     0     1     1     1     0   \n",
       "\n",
       "      WP16  WP17  WP18  WP19  \n",
       "195      1     1     1     1  \n",
       "360      1     1     1     1  \n",
       "609      1     1     1     1  \n",
       "749      1     1     1     1  \n",
       "1014     1     1     1     1  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Wenn irgendwer Ahnung hat, wie man Namen von Variablen generieren kann, BITTE ÄNDERN!\n",
    "df_WP1 = dfnew.loc[dfnew[\"WP1\"] == 1]\n",
    "df_WP2 = dfnew.loc[dfnew[\"WP2\"] == 1]\n",
    "df_WP3 = dfnew.loc[dfnew[\"WP3\"] == 1]\n",
    "df_WP4 = dfnew.loc[dfnew[\"WP4\"] == 1]\n",
    "df_WP5 = dfnew.loc[dfnew[\"WP5\"] == 1]\n",
    "df_WP6 = dfnew.loc[dfnew[\"WP6\"] == 1]\n",
    "df_WP7 = dfnew.loc[dfnew[\"WP7\"] == 1]\n",
    "df_WP8 = dfnew.loc[dfnew[\"WP8\"] == 1]\n",
    "df_WP9 = dfnew.loc[dfnew[\"WP9\"] == 1]\n",
    "df_WP10 = dfnew.loc[dfnew[\"WP10\"] == 1]\n",
    "df_WP11 = dfnew.loc[dfnew[\"WP11\"] == 1]\n",
    "df_WP12 = dfnew.loc[dfnew[\"WP12\"] == 1]\n",
    "df_WP13 = dfnew.loc[dfnew[\"WP13\"] == 1]\n",
    "df_WP14 = dfnew.loc[dfnew[\"WP14\"] == 1]\n",
    "df_WP15 = dfnew.loc[dfnew[\"WP15\"] == 1]\n",
    "df_WP16 = dfnew.loc[dfnew[\"WP16\"] == 1]\n",
    "df_WP17 = dfnew.loc[dfnew[\"WP17\"] == 1]\n",
    "df_WP18 = dfnew.loc[dfnew[\"WP18\"] == 1]\n",
    "df_WP19 = dfnew.loc[dfnew[\"WP19\"] == 1]\n",
    "\n",
    "# Beispiel\n",
    "print(\"Es gab \" + str(len(df_WP19)) + \" Abgeordnete im 19.Bundestag\")\n",
    "df_WP19.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Als nächsten Schritt werden wir nun die 19 Bundestage(DFs) zusammenfügen und nach den Wahlperioden indizieren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Laszlo Kuehl\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:24: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['1949-1953;01', '1949-1953;01', '1949-1953;01', '1949-1953;01',\n",
      "       '1949-1953;01', '1949-1953;01', '1949-1953;01', '1949-1953;01',\n",
      "       '1949-1953;01', '1949-1953;01',\n",
      "       ...\n",
      "       '1949-1953;01', '1949-1953;01', '1949-1953;01', '1949-1953;01',\n",
      "       '1949-1953;01', '1949-1953;01', '1949-1953;01', '1949-1953;01',\n",
      "       '1949-1953;01', '1949-1953;01'],\n",
      "      dtype='object', name='WP-JAHRE', length=474)\n",
      "Index(['2017-;19', '2017-;19', '2017-;19', '2017-;19', '2017-;19', '2017-;19',\n",
      "       '2017-;19', '2017-;19', '2017-;19', '2017-;19',\n",
      "       ...\n",
      "       '2017-;19', '2017-;19', '2017-;19', '2017-;19', '2017-;19', '2017-;19',\n",
      "       '2017-;19', '2017-;19', '2017-;19', '2017-;19'],\n",
      "      dtype='object', name='WP-JAHRE', length=711)\n"
     ]
    }
   ],
   "source": [
    "# Vorbereitungen\n",
    "unique_list_wp = []\n",
    "for e in unique_list:\n",
    "    unique_list_wp.append(\"WP\" + str(e))\n",
    "\n",
    "# Index\n",
    "list_years = [\"1949-1953;01\", \"1953–1957;02\",\"1957–1961;03\", \"1961–1965;04\", \"1965–1969;05\", \"1969–1972;06\", \"1972–1976;07\",\"1976-1980;08\",\n",
    "\"1980–1983;09\", \"1983–1987;10\",\"1987–1990;11\",\"1990–1994;12\",\"1994–1998;13\",\"1998–2002;14\",\"2002–2005;15\",\"2005–2009;16\",\"2009–2013;17\",\n",
    "              \"2013–2017;18\", \"2017-;19\"]              \n",
    "\n",
    "dict_WPyears = dict(zip(unique_list_wp, list_years))\n",
    "\n",
    "# Liste zum einfacheren Abruf\n",
    "df_wp_list = [df_WP1, df_WP2, df_WP3, df_WP4, df_WP5, df_WP6, df_WP7,df_WP8,df_WP9, df_WP10,df_WP11,df_WP12,df_WP13,\n",
    "df_WP14,df_WP15,df_WP16,df_WP17,df_WP18, df_WP19]\n",
    "\n",
    "# Transformation / Pivotisierung der einzelnen DataFrame\n",
    "for d in unique_list_wp:\n",
    "    index = unique_list_wp.index(d)\n",
    "    df_wp_list[index].name = d\n",
    "\n",
    "for e in df_wp_list:\n",
    "    name = e.name\n",
    "    e[\"WP-JAHRE\"] = dict_WPyears[name]\n",
    "            \n",
    "for df in df_wp_list:\n",
    "    df.set_index(\"WP-JAHRE\", drop = False, inplace=True)\n",
    "\n",
    "# Test\n",
    "print(df_WP1.index)\n",
    "print(df_WP19[\"WP-JAHRE\"].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Ausführung: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "      <th>...</th>\n",
       "      <th>WP12</th>\n",
       "      <th>WP13</th>\n",
       "      <th>WP14</th>\n",
       "      <th>WP15</th>\n",
       "      <th>WP16</th>\n",
       "      <th>WP17</th>\n",
       "      <th>WP18</th>\n",
       "      <th>WP19</th>\n",
       "      <th>WP-JAHRE</th>\n",
       "      <th>NUMIND</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WP-JAHRE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>11000009</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1876</td>\n",
       "      <td>Bundeskanzler a. D.</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Adenauer</td>\n",
       "      <td>Konrad</td>\n",
       "      <td>[1, 2, 3, 4, 5]</td>\n",
       "      <td>[1, 1, 1, 1, 1]</td>\n",
       "      <td>...</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>1949-1953;01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>11000013</td>\n",
       "      <td>1</td>\n",
       "      <td>Andere</td>\n",
       "      <td>1904</td>\n",
       "      <td>Bergmann, Gewerkschaftsfunktionär</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Agatz</td>\n",
       "      <td>Willi</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0]</td>\n",
       "      <td>...</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>1949-1953;01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>11000015</td>\n",
       "      <td>1</td>\n",
       "      <td>Andere</td>\n",
       "      <td>1879</td>\n",
       "      <td>Kapitän a. D.</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Ahrens</td>\n",
       "      <td>Adolf</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0]</td>\n",
       "      <td>...</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>1949-1953;01</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>11000020</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1890</td>\n",
       "      <td>Geschäftsführer</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Albers</td>\n",
       "      <td>Johannes</td>\n",
       "      <td>[1, 2]</td>\n",
       "      <td>[1, 1]</td>\n",
       "      <td>...</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>1949-1953;01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>11000021</td>\n",
       "      <td>0</td>\n",
       "      <td>SPD</td>\n",
       "      <td>1901</td>\n",
       "      <td>Buchhalterin</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Albertz</td>\n",
       "      <td>Luise</td>\n",
       "      <td>[1, 2, 3, 4, 5]</td>\n",
       "      <td>[0, 0, 0, 0, 1]</td>\n",
       "      <td>...</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>1949-1953;01</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    ID  GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "WP-JAHRE                                                      \n",
       "1949-1953;01  11000009           1       Union         1876   \n",
       "1949-1953;01  11000013           1      Andere         1904   \n",
       "1949-1953;01  11000015           1      Andere         1879   \n",
       "1949-1953;01  11000020           1       Union         1890   \n",
       "1949-1953;01  11000021           0         SPD         1901   \n",
       "\n",
       "                                          BERUF  GEBURTSLAND  NACHNAME  \\\n",
       "WP-JAHRE                                                                 \n",
       "1949-1953;01                Bundeskanzler a. D.  Deutschland  Adenauer   \n",
       "1949-1953;01  Bergmann, Gewerkschaftsfunktionär  Deutschland     Agatz   \n",
       "1949-1953;01                      Kapitän a. D.  Deutschland    Ahrens   \n",
       "1949-1953;01                    Geschäftsführer  Deutschland    Albers   \n",
       "1949-1953;01                       Buchhalterin  Deutschland   Albertz   \n",
       "\n",
       "               VORNAME     WAHLPERIODEN     MANDATSARTEN   ...    WP12  WP13  \\\n",
       "WP-JAHRE                                                   ...                 \n",
       "1949-1953;01    Konrad  [1, 2, 3, 4, 5]  [1, 1, 1, 1, 1]   ...       0     0   \n",
       "1949-1953;01     Willi              [1]              [0]   ...       0     0   \n",
       "1949-1953;01     Adolf              [1]              [0]   ...       0     0   \n",
       "1949-1953;01  Johannes           [1, 2]           [1, 1]   ...       0     0   \n",
       "1949-1953;01     Luise  [1, 2, 3, 4, 5]  [0, 0, 0, 0, 1]   ...       0     0   \n",
       "\n",
       "              WP14  WP15  WP16  WP17  WP18  WP19      WP-JAHRE  NUMIND  \n",
       "WP-JAHRE                                                                \n",
       "1949-1953;01     0     0     0     0     0     0  1949-1953;01       0  \n",
       "1949-1953;01     0     0     0     0     0     0  1949-1953;01       1  \n",
       "1949-1953;01     0     0     0     0     0     0  1949-1953;01       2  \n",
       "1949-1953;01     0     0     0     0     0     0  1949-1953;01       3  \n",
       "1949-1953;01     0     0     0     0     0     0  1949-1953;01       4  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Zusammenfügen\n",
    "df_indexed = pd.concat(df_wp_list)\n",
    "\n",
    "\n",
    "#Indexing (zusätzlicher numerischer Index als Spalte hinzugefügt --> einfachere Selektion)\n",
    "num_index = list(range(0,11600))\n",
    "df_indexed[\"NUMIND\"] = num_index\n",
    "df_indexed.set_index([\"WP-JAHRE\"])\n",
    "\n",
    "\n",
    "#Check\n",
    "df_indexed.head()                       "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature Engneering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Erstellen einer Spalte für das Alter der MdBs am Anfang der jeweiligen Wahlperiode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([73, 45, 70, 59, 48, 53, 60, 61, 69, 31, 55, 67, 54, 34, 56, 29, 49,\n",
       "       58, 44, 36, 42, 66, 39, 43, 51, 38, 52, 37, 65, 64, 47, 50, 57, 46,\n",
       "       41, 62, 40, 63, 32, 35, 68, 72, 74, 27, 33, 30, 28, 77, 75, 81, 26,\n",
       "       71, 79, 25, 76, 85, 80, 89, 24, 21, 23, 22, 19, 78], dtype=int64)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ANFANG_list = []\n",
    "for e in df_indexed.index:\n",
    "    ANFANG_list.append(int(e[:4]))\n",
    "\n",
    "df_indexed[\"ANFANG\"] = ANFANG_list\n",
    "df_indexed[\"ALTER\"] = df_indexed[\"ANFANG\"] - df_indexed[\"GEBURTSDATUM\"].apply(int)\n",
    "\n",
    "\n",
    "# Suche nach vermeidbaren Fehlern \n",
    "df_indexed.ALTER.unique() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Erstellen einer Spalte für die Mandatsart der MdBs der jeweiligen Wahlperiode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11600\n"
     ]
    }
   ],
   "source": [
    "# Unschöne Lösung, aber funktioniert\n",
    "anumindex = df_indexed[\"NUMIND\"]\n",
    "awpindlst = df_indexed.index.tolist()\n",
    "awplst = list(df_indexed[\"WAHLPERIODEN\"])\n",
    "amandlst = list(df_indexed[\"MANDATSARTEN\"])\n",
    "anewcol = []\n",
    "\n",
    "for ind in anumindex:\n",
    "    num = str(int(awpindlst[ind][-2:]))\n",
    "    if num in awplst[ind]:\n",
    "        subind = int(awplst[ind].index(num))\n",
    "        anewcol.append(amandlst[ind][subind])\n",
    "    \n",
    "\n",
    "# Test (sollte 11600 Einträge haben)\n",
    "print(len(anewcol))\n",
    "# Neue Spalte setzen\n",
    "df_indexed[\"MANDATSART\"] = anewcol"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>GEBURTSDATUM</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>GEBURTSLAND</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>WAHLPERIODEN</th>\n",
       "      <th>MANDATSARTEN</th>\n",
       "      <th>...</th>\n",
       "      <th>WP15</th>\n",
       "      <th>WP16</th>\n",
       "      <th>WP17</th>\n",
       "      <th>WP18</th>\n",
       "      <th>WP19</th>\n",
       "      <th>WP-JAHRE</th>\n",
       "      <th>NUMIND</th>\n",
       "      <th>ANFANG</th>\n",
       "      <th>ALTER</th>\n",
       "      <th>MANDATSART</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WP-JAHRE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1998–2002;14</th>\n",
       "      <td>11002710</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>1955</td>\n",
       "      <td>Dipl.-Ingenieur für Schiffstechnik</td>\n",
       "      <td>Deutschland</td>\n",
       "      <td>Kuhn</td>\n",
       "      <td>Werner</td>\n",
       "      <td>[13, 14, 15]</td>\n",
       "      <td>[1, None, 0]</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1998–2002;14</td>\n",
       "      <td>8023</td>\n",
       "      <td>1998</td>\n",
       "      <td>43</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    ID  GESCHLECHT PARTEI_KURZ GEBURTSDATUM  \\\n",
       "WP-JAHRE                                                      \n",
       "1998–2002;14  11002710           1       Union         1955   \n",
       "\n",
       "                                           BERUF  GEBURTSLAND NACHNAME  \\\n",
       "WP-JAHRE                                                                 \n",
       "1998–2002;14  Dipl.-Ingenieur für Schiffstechnik  Deutschland     Kuhn   \n",
       "\n",
       "             VORNAME  WAHLPERIODEN  MANDATSARTEN     ...      WP15  WP16  \\\n",
       "WP-JAHRE                                             ...                   \n",
       "1998–2002;14  Werner  [13, 14, 15]  [1, None, 0]     ...         1     0   \n",
       "\n",
       "              WP17  WP18  WP19      WP-JAHRE  NUMIND  ANFANG  ALTER  \\\n",
       "WP-JAHRE                                                              \n",
       "1998–2002;14     0     0     0  1998–2002;14    8023    1998     43   \n",
       "\n",
       "              MANDATSART  \n",
       "WP-JAHRE                  \n",
       "1998–2002;14         NaN  \n",
       "\n",
       "[1 rows x 35 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fehler?\n",
    "df_indexed[df_indexed['MANDATSART'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "source": [
    "# Fixing the problem\n",
    "df_indexed[\"MANDATSART\"].fillna(0, inplace=True) #0, weil kein Direktmandat ersetzt wurde (Siehe Bundestagsarchiv)\n",
    "df_indexed[\"MANDATSART\"].value_counts()\n",
    "df_indexed[\"MANDATSART\"].round(0).astype(int)\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Vorbereitung für den Export"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Letzte Ausgestalungen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ID' 'GESCHLECHT' 'PARTEI_KURZ' 'GEBURTSDATUM' 'BERUF' 'GEBURTSLAND'\n",
      " 'NACHNAME' 'VORNAME' 'WAHLPERIODEN' 'MANDATSARTEN' 'AUSLAND' 'WP1' 'WP2'\n",
      " 'WP3' 'WP4' 'WP5' 'WP6' 'WP7' 'WP8' 'WP9' 'WP10' 'WP11' 'WP12' 'WP13'\n",
      " 'WP14' 'WP15' 'WP16' 'WP17' 'WP18' 'WP19' 'WP-JAHRE' 'NUMIND' 'ANFANG'\n",
      " 'ALTER' 'MANDATSART']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NUMIND</th>\n",
       "      <th>ID</th>\n",
       "      <th>ANFANG</th>\n",
       "      <th>VORNAME</th>\n",
       "      <th>NACHNAME</th>\n",
       "      <th>ALTER</th>\n",
       "      <th>GESCHLECHT</th>\n",
       "      <th>PARTEI_KURZ</th>\n",
       "      <th>BERUF</th>\n",
       "      <th>AUSLAND</th>\n",
       "      <th>...</th>\n",
       "      <th>WP10</th>\n",
       "      <th>WP11</th>\n",
       "      <th>WP12</th>\n",
       "      <th>WP13</th>\n",
       "      <th>WP14</th>\n",
       "      <th>WP15</th>\n",
       "      <th>WP16</th>\n",
       "      <th>WP17</th>\n",
       "      <th>WP18</th>\n",
       "      <th>WP19</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WP-JAHRE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>0</td>\n",
       "      <td>11000009</td>\n",
       "      <td>1949</td>\n",
       "      <td>Konrad</td>\n",
       "      <td>Adenauer</td>\n",
       "      <td>73</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>Bundeskanzler a. D.</td>\n",
       "      <td>0</td>\n",
       "      <td>...</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",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>1</td>\n",
       "      <td>11000013</td>\n",
       "      <td>1949</td>\n",
       "      <td>Willi</td>\n",
       "      <td>Agatz</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>Andere</td>\n",
       "      <td>Bergmann, Gewerkschaftsfunktionär</td>\n",
       "      <td>0</td>\n",
       "      <td>...</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",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>2</td>\n",
       "      <td>11000015</td>\n",
       "      <td>1949</td>\n",
       "      <td>Adolf</td>\n",
       "      <td>Ahrens</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>Andere</td>\n",
       "      <td>Kapitän a. D.</td>\n",
       "      <td>0</td>\n",
       "      <td>...</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",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>3</td>\n",
       "      <td>11000020</td>\n",
       "      <td>1949</td>\n",
       "      <td>Johannes</td>\n",
       "      <td>Albers</td>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "      <td>Union</td>\n",
       "      <td>Geschäftsführer</td>\n",
       "      <td>0</td>\n",
       "      <td>...</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",
       "    <tr>\n",
       "      <th>1949-1953;01</th>\n",
       "      <td>4</td>\n",
       "      <td>11000021</td>\n",
       "      <td>1949</td>\n",
       "      <td>Luise</td>\n",
       "      <td>Albertz</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Buchhalterin</td>\n",
       "      <td>0</td>\n",
       "      <td>...</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",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              NUMIND        ID  ANFANG   VORNAME  NACHNAME  ALTER  GESCHLECHT  \\\n",
       "WP-JAHRE                                                                        \n",
       "1949-1953;01       0  11000009    1949    Konrad  Adenauer     73           1   \n",
       "1949-1953;01       1  11000013    1949     Willi     Agatz     45           1   \n",
       "1949-1953;01       2  11000015    1949     Adolf    Ahrens     70           1   \n",
       "1949-1953;01       3  11000020    1949  Johannes    Albers     59           1   \n",
       "1949-1953;01       4  11000021    1949     Luise   Albertz     48           0   \n",
       "\n",
       "             PARTEI_KURZ                              BERUF  AUSLAND  ...   \\\n",
       "WP-JAHRE                                                              ...    \n",
       "1949-1953;01       Union                Bundeskanzler a. D.        0  ...    \n",
       "1949-1953;01      Andere  Bergmann, Gewerkschaftsfunktionär        0  ...    \n",
       "1949-1953;01      Andere                      Kapitän a. D.        0  ...    \n",
       "1949-1953;01       Union                    Geschäftsführer        0  ...    \n",
       "1949-1953;01         SPD                       Buchhalterin        0  ...    \n",
       "\n",
       "              WP10  WP11  WP12  WP13  WP14  WP15  WP16  WP17  WP18  WP19  \n",
       "WP-JAHRE                                                                  \n",
       "1949-1953;01     0     0     0     0     0     0     0     0     0     0  \n",
       "1949-1953;01     0     0     0     0     0     0     0     0     0     0  \n",
       "1949-1953;01     0     0     0     0     0     0     0     0     0     0  \n",
       "1949-1953;01     0     0     0     0     0     0     0     0     0     0  \n",
       "1949-1953;01     0     0     0     0     0     0     0     0     0     0  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Welche Spalten können gelöscht werden? \n",
    "print(df_indexed.columns.values)\n",
    "\n",
    "# Für die Analyse (Visualisierung/Machine Learning) werden redundante Infos entfernt\n",
    "df_indexed_final = df_indexed.drop([\"GEBURTSDATUM\", \"GEBURTSLAND\", \"WAHLPERIODEN\",\"WP-JAHRE\", \"MANDATSARTEN\"], axis=1)\n",
    "\n",
    "\n",
    "# Sortieren\n",
    "df_indexed_final = df_indexed_final[\n",
    "['NUMIND',\"ID\",'ANFANG', 'VORNAME', 'NACHNAME', 'ALTER', 'GESCHLECHT', 'PARTEI_KURZ',\"BERUF\",\"AUSLAND\", \"MANDATSART\", \"WP1\", \"WP2\", \"WP3\", \"WP4\", \"WP5\",\n",
    "       \"WP6\", \"WP7\", \"WP8\", \"WP9\", \"WP10\", \"WP11\", \"WP12\", \"WP13\", \"WP14\",\n",
    "       'WP15', 'WP16', 'WP17', 'WP18', 'WP19']]\n",
    "\n",
    "df_indexed_final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export des Datensatzes in .csv-Format "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_indexed_final.to_csv(\"MdB_Indexed.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Abgeschlossen"
   ]
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}