ltw_data_prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, import needed modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Initalize variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/jan/Dropbox/UP_EPQM/2222/MA/powerlinemonsters\n"
     ]
    }
   ],
   "source": [
    "path = os.getcwd()\n",
    "print(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bavaria (2008, 13, 18)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "by08 = pd.read_csv(f'{path}/data/elections/ltw/by/2008.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "by13 = pd.read_csv(f'{path}/data/elections/ltw/by/2013.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "by18 = pd.read_csv(f'{path}/data/elections/ltw/by/2018.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>AGS</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>GŸltige Stimmen</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>GrŸne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14.10.18</td>\n",
       "      <td>9</td>\n",
       "      <td>Bayern</td>\n",
       "      <td>9479428.0</td>\n",
       "      <td>72,3</td>\n",
       "      <td>6782374.0</td>\n",
       "      <td>2523041.0</td>\n",
       "      <td>654539.0</td>\n",
       "      <td>1196178.0</td>\n",
       "      <td>345250.0</td>\n",
       "      <td>218944.0</td>\n",
       "      <td>694311.0</td>\n",
       "      <td>1150112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14.10.18</td>\n",
       "      <td>91</td>\n",
       "      <td>Oberbayern, Regierungsbezirk</td>\n",
       "      <td>3203709.0</td>\n",
       "      <td>74,6</td>\n",
       "      <td>2367136.0</td>\n",
       "      <td>798011.0</td>\n",
       "      <td>221710.0</td>\n",
       "      <td>527462.0</td>\n",
       "      <td>151403.0</td>\n",
       "      <td>74839.0</td>\n",
       "      <td>202708.0</td>\n",
       "      <td>391004.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14.10.18</td>\n",
       "      <td>9161</td>\n",
       "      <td>Ingolstadt</td>\n",
       "      <td>90643.0</td>\n",
       "      <td>64</td>\n",
       "      <td>57337.0</td>\n",
       "      <td>20784.0</td>\n",
       "      <td>5217.0</td>\n",
       "      <td>9517.0</td>\n",
       "      <td>3972.0</td>\n",
       "      <td>2894.0</td>\n",
       "      <td>7383.0</td>\n",
       "      <td>7572.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14.10.18</td>\n",
       "      <td>9162</td>\n",
       "      <td>München, Landeshauptstadt</td>\n",
       "      <td>910459.0</td>\n",
       "      <td>72,7</td>\n",
       "      <td>656649.0</td>\n",
       "      <td>162579.0</td>\n",
       "      <td>84340.0</td>\n",
       "      <td>204303.0</td>\n",
       "      <td>57488.0</td>\n",
       "      <td>30752.0</td>\n",
       "      <td>42441.0</td>\n",
       "      <td>74748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14.10.18</td>\n",
       "      <td>9163</td>\n",
       "      <td>Rosenheim</td>\n",
       "      <td>41029.0</td>\n",
       "      <td>68,2</td>\n",
       "      <td>27618.0</td>\n",
       "      <td>9396.0</td>\n",
       "      <td>2254.0</td>\n",
       "      <td>5767.0</td>\n",
       "      <td>1620.0</td>\n",
       "      <td>967.0</td>\n",
       "      <td>3203.0</td>\n",
       "      <td>4412.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Datum   AGS                        Gemeinde  Wahlberechtigte  \\\n",
       "0  14.10.18     9                          Bayern        9479428.0   \n",
       "1  14.10.18    91    Oberbayern, Regierungsbezirk        3203709.0   \n",
       "2  14.10.18  9161                      Ingolstadt          90643.0   \n",
       "3  14.10.18  9162       München, Landeshauptstadt         910459.0   \n",
       "4  14.10.18  9163                       Rosenheim          41029.0   \n",
       "\n",
       "  Wahlbeteiligung  GŸltige Stimmen      Union       SPD      GrŸne       FDP  \\\n",
       "0            72,3        6782374.0  2523041.0  654539.0  1196178.0  345250.0   \n",
       "1            74,6        2367136.0   798011.0  221710.0   527462.0  151403.0   \n",
       "2              64          57337.0    20784.0    5217.0     9517.0    3972.0   \n",
       "3            72,7         656649.0   162579.0   84340.0   204303.0   57488.0   \n",
       "4            68,2          27618.0     9396.0    2254.0     5767.0    1620.0   \n",
       "\n",
       "      Linke       AfD  Sonstige Parteien  \n",
       "0  218944.0  694311.0          1150112.0  \n",
       "1   74839.0  202708.0           391004.0  \n",
       "2    2894.0    7383.0             7572.0  \n",
       "3   30752.0   42441.0            74748.0  \n",
       "4     967.0    3203.0             4412.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by18.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hesse (2009, 13, 18)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "he09 = pd.read_csv(f'{path}/data/elections/ltw/he/2009.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "he13 = pd.read_csv(f'{path}/data/elections/ltw/he/2013.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "he18 = pd.read_csv(f'{path}/data/elections/ltw/he/2018.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Datum</th>\n",
       "      <th>AGS</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>GŸltige Stimmen</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>GrŸne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18.01.09</td>\n",
       "      <td>6</td>\n",
       "      <td>Hessen</td>\n",
       "      <td>4375286.0</td>\n",
       "      <td>61</td>\n",
       "      <td>2591872.0</td>\n",
       "      <td>963763.0</td>\n",
       "      <td>614648.0</td>\n",
       "      <td>356040.0</td>\n",
       "      <td>420426.0</td>\n",
       "      <td>139074.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97921.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18.01.09</td>\n",
       "      <td>64</td>\n",
       "      <td>Darmstadt, Regierungsbezirk</td>\n",
       "      <td>2620658.0</td>\n",
       "      <td>61,9</td>\n",
       "      <td>1576303.0</td>\n",
       "      <td>593959.0</td>\n",
       "      <td>327432.0</td>\n",
       "      <td>240227.0</td>\n",
       "      <td>270740.0</td>\n",
       "      <td>85101.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18.01.09</td>\n",
       "      <td>6411</td>\n",
       "      <td>Darmstadt, kreisfreie Stadt</td>\n",
       "      <td>98087.0</td>\n",
       "      <td>62,8</td>\n",
       "      <td>60073.0</td>\n",
       "      <td>17269.0</td>\n",
       "      <td>12730.0</td>\n",
       "      <td>14907.0</td>\n",
       "      <td>9051.0</td>\n",
       "      <td>4322.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18.01.09</td>\n",
       "      <td>6412</td>\n",
       "      <td>Frankfurt am Main, kreisfreie Stadt</td>\n",
       "      <td>395834.0</td>\n",
       "      <td>59,4</td>\n",
       "      <td>229893.0</td>\n",
       "      <td>75211.0</td>\n",
       "      <td>45421.0</td>\n",
       "      <td>45134.0</td>\n",
       "      <td>38640.0</td>\n",
       "      <td>18003.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18.01.09</td>\n",
       "      <td>6413</td>\n",
       "      <td>Offenbach am Main, kreisfreie Stadt</td>\n",
       "      <td>66517.0</td>\n",
       "      <td>53,6</td>\n",
       "      <td>34483.0</td>\n",
       "      <td>11694.0</td>\n",
       "      <td>7311.0</td>\n",
       "      <td>6350.0</td>\n",
       "      <td>4881.0</td>\n",
       "      <td>2598.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1649.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Datum   AGS                                 Gemeinde  Wahlberechtigte  \\\n",
       "0  18.01.09     6                                   Hessen        4375286.0   \n",
       "1  18.01.09    64              Darmstadt, Regierungsbezirk        2620658.0   \n",
       "2  18.01.09  6411              Darmstadt, kreisfreie Stadt          98087.0   \n",
       "3  18.01.09  6412      Frankfurt am Main, kreisfreie Stadt         395834.0   \n",
       "4  18.01.09  6413      Offenbach am Main, kreisfreie Stadt          66517.0   \n",
       "\n",
       "  Wahlbeteiligung  GŸltige Stimmen     Union       SPD     GrŸne       FDP  \\\n",
       "0              61        2591872.0  963763.0  614648.0  356040.0  420426.0   \n",
       "1            61,9        1576303.0  593959.0  327432.0  240227.0  270740.0   \n",
       "2            62,8          60073.0   17269.0   12730.0   14907.0    9051.0   \n",
       "3            59,4         229893.0   75211.0   45421.0   45134.0   38640.0   \n",
       "4            53,6          34483.0   11694.0    7311.0    6350.0    4881.0   \n",
       "\n",
       "      Linke  AfD  Sonstige Parteien  \n",
       "0  139074.0  NaN            97921.0  \n",
       "1   85101.0  NaN            58844.0  \n",
       "2    4322.0  NaN             1794.0  \n",
       "3   18003.0  NaN             7484.0  \n",
       "4    2598.0  NaN             1649.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "he09.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NRW (2010, 12, 17)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "nrw10 = pd.read_csv(f'{path}/data/elections/ltw/nrw/2010.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "nrw12 = pd.read_csv(f'{path}/data/elections/ltw/nrw/2012.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "nrw17 = pd.read_csv(f'{path}/data/elections/ltw/nrw/2017.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Datum</th>\n",
       "      <th>AGS</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>GŸltige Stimmen</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>GrŸne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09.05.10</td>\n",
       "      <td>5</td>\n",
       "      <td>Nordrhein-Westfalen</td>\n",
       "      <td>13267052.0</td>\n",
       "      <td>59,3</td>\n",
       "      <td>7760546.0</td>\n",
       "      <td>2681700.0</td>\n",
       "      <td>2675818.0</td>\n",
       "      <td>941162.0</td>\n",
       "      <td>522229.0</td>\n",
       "      <td>435627.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>504010.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09.05.10</td>\n",
       "      <td>51</td>\n",
       "      <td>Düsseldorf, Regierungsbezirk</td>\n",
       "      <td>3803015.0</td>\n",
       "      <td>58,9</td>\n",
       "      <td>2212669.0</td>\n",
       "      <td>742241.0</td>\n",
       "      <td>767674.0</td>\n",
       "      <td>262033.0</td>\n",
       "      <td>157156.0</td>\n",
       "      <td>130781.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>152784.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>09.05.10</td>\n",
       "      <td>5111</td>\n",
       "      <td>Düsseldorf, kreisfreie Stadt</td>\n",
       "      <td>412910.0</td>\n",
       "      <td>61,1</td>\n",
       "      <td>249688.0</td>\n",
       "      <td>87499.0</td>\n",
       "      <td>72533.0</td>\n",
       "      <td>37996.0</td>\n",
       "      <td>21710.0</td>\n",
       "      <td>14934.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>09.05.10</td>\n",
       "      <td>5112</td>\n",
       "      <td>Duisburg, kreisfreie Stadt</td>\n",
       "      <td>344154.0</td>\n",
       "      <td>54,2</td>\n",
       "      <td>184005.0</td>\n",
       "      <td>44331.0</td>\n",
       "      <td>82532.0</td>\n",
       "      <td>17521.0</td>\n",
       "      <td>7242.0</td>\n",
       "      <td>14657.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09.05.10</td>\n",
       "      <td>5113</td>\n",
       "      <td>Essen, kreisfreie Stadt</td>\n",
       "      <td>433631.0</td>\n",
       "      <td>58,8</td>\n",
       "      <td>251546.0</td>\n",
       "      <td>72507.0</td>\n",
       "      <td>101932.0</td>\n",
       "      <td>30175.0</td>\n",
       "      <td>13547.0</td>\n",
       "      <td>16265.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17120.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Datum   AGS                          Gemeinde  Wahlberechtigte  \\\n",
       "0  09.05.10     5               Nordrhein-Westfalen       13267052.0   \n",
       "1  09.05.10    51      Düsseldorf, Regierungsbezirk        3803015.0   \n",
       "2  09.05.10  5111      Düsseldorf, kreisfreie Stadt         412910.0   \n",
       "3  09.05.10  5112        Duisburg, kreisfreie Stadt         344154.0   \n",
       "4  09.05.10  5113           Essen, kreisfreie Stadt         433631.0   \n",
       "\n",
       "  Wahlbeteiligung  GŸltige Stimmen      Union        SPD     GrŸne       FDP  \\\n",
       "0            59,3        7760546.0  2681700.0  2675818.0  941162.0  522229.0   \n",
       "1            58,9        2212669.0   742241.0   767674.0  262033.0  157156.0   \n",
       "2            61,1         249688.0    87499.0    72533.0   37996.0   21710.0   \n",
       "3            54,2         184005.0    44331.0    82532.0   17521.0    7242.0   \n",
       "4            58,8         251546.0    72507.0   101932.0   30175.0   13547.0   \n",
       "\n",
       "      Linke  AfD  Sonstige Parteien  \n",
       "0  435627.0  NaN           504010.0  \n",
       "1  130781.0  NaN           152784.0  \n",
       "2   14934.0  NaN            15016.0  \n",
       "3   14657.0  NaN            17722.0  \n",
       "4   16265.0  NaN            17120.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nrw10.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lower Saxony (2008, 13, 17)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "ns08 = pd.read_csv(f'{path}/data/elections/ltw/ns/2008.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "ns13 = pd.read_csv(f'{path}/data/elections/ltw/ns/2013.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "ns17 = pd.read_csv(f'{path}/data/elections/ltw/ns/2017.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Datum</th>\n",
       "      <th>AGS</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>GŸltige Stimmen</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>GrŸne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27.01.08</td>\n",
       "      <td>3</td>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>6087297.0</td>\n",
       "      <td>57,1</td>\n",
       "      <td>3425426.0</td>\n",
       "      <td>1456742.0</td>\n",
       "      <td>1036727.0</td>\n",
       "      <td>274221.0</td>\n",
       "      <td>279826.0</td>\n",
       "      <td>243361.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>134549.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27.01.08</td>\n",
       "      <td>31</td>\n",
       "      <td>Braunschweig, Stat. Region</td>\n",
       "      <td>1263011.0</td>\n",
       "      <td>57,2</td>\n",
       "      <td>710602.0</td>\n",
       "      <td>276493.0</td>\n",
       "      <td>241568.0</td>\n",
       "      <td>54767.0</td>\n",
       "      <td>53255.0</td>\n",
       "      <td>56762.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27757.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27.01.08</td>\n",
       "      <td>3101</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>187773.0</td>\n",
       "      <td>57,2</td>\n",
       "      <td>106089.0</td>\n",
       "      <td>39106.0</td>\n",
       "      <td>33148.0</td>\n",
       "      <td>11695.0</td>\n",
       "      <td>8580.0</td>\n",
       "      <td>10131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3429.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27.01.08</td>\n",
       "      <td>3102</td>\n",
       "      <td>Salzgitter, kreisfreie Stadt</td>\n",
       "      <td>78313.0</td>\n",
       "      <td>54,5</td>\n",
       "      <td>41776.0</td>\n",
       "      <td>15656.0</td>\n",
       "      <td>16405.0</td>\n",
       "      <td>1793.0</td>\n",
       "      <td>2398.0</td>\n",
       "      <td>3688.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1836.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27.01.08</td>\n",
       "      <td>3103</td>\n",
       "      <td>Wolfsburg, kreisfreie Stadt</td>\n",
       "      <td>90631.0</td>\n",
       "      <td>51,6</td>\n",
       "      <td>45996.0</td>\n",
       "      <td>19834.0</td>\n",
       "      <td>14496.0</td>\n",
       "      <td>2994.0</td>\n",
       "      <td>3078.0</td>\n",
       "      <td>3617.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1977.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Datum   AGS                            Gemeinde  Wahlberechtigte  \\\n",
       "0  27.01.08     3                       Niedersachsen        6087297.0   \n",
       "1  27.01.08    31          Braunschweig, Stat. Region        1263011.0   \n",
       "2  27.01.08  3101      Braunschweig, kreisfreie Stadt         187773.0   \n",
       "3  27.01.08  3102        Salzgitter, kreisfreie Stadt          78313.0   \n",
       "4  27.01.08  3103         Wolfsburg, kreisfreie Stadt          90631.0   \n",
       "\n",
       "  Wahlbeteiligung  GŸltige Stimmen      Union        SPD     GrŸne       FDP  \\\n",
       "0            57,1        3425426.0  1456742.0  1036727.0  274221.0  279826.0   \n",
       "1            57,2         710602.0   276493.0   241568.0   54767.0   53255.0   \n",
       "2            57,2         106089.0    39106.0    33148.0   11695.0    8580.0   \n",
       "3            54,5          41776.0    15656.0    16405.0    1793.0    2398.0   \n",
       "4            51,6          45996.0    19834.0    14496.0    2994.0    3078.0   \n",
       "\n",
       "      Linke  AfD  Sonstige Parteien  \n",
       "0  243361.0  NaN           134549.0  \n",
       "1   56762.0  NaN            27757.0  \n",
       "2   10131.0  NaN             3429.0  \n",
       "3    3688.0  NaN             1836.0  \n",
       "4    3617.0  NaN             1977.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ns08.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thuringia (2009, 14, 19)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "th09 = pd.read_csv(f'{path}/data/elections/ltw/th/2009.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "th14 = pd.read_csv(f'{path}/data/elections/ltw/th/2014.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')\n",
    "th19 = pd.read_csv(f'{path}/data/elections/ltw/th/2019.csv', encoding = 'Windows-1252', sep=';', converters = {'AGS': str}, na_values='-')"
   ]
  },
  {
   "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>Datum</th>\n",
       "      <th>AGS</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>GŸltige Stimmen</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>GrŸne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30.08.09</td>\n",
       "      <td>16</td>\n",
       "      <td>Thüringen</td>\n",
       "      <td>1910074.0</td>\n",
       "      <td>56,2</td>\n",
       "      <td>1054297.0</td>\n",
       "      <td>329302.0</td>\n",
       "      <td>195363.0</td>\n",
       "      <td>64912.0</td>\n",
       "      <td>80600.0</td>\n",
       "      <td>288915.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95205.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>30.08.09</td>\n",
       "      <td>16051</td>\n",
       "      <td>Erfurt, kreisfreie Stadt</td>\n",
       "      <td>167444.0</td>\n",
       "      <td>58,1</td>\n",
       "      <td>95498.0</td>\n",
       "      <td>24134.0</td>\n",
       "      <td>18875.0</td>\n",
       "      <td>10041.0</td>\n",
       "      <td>7226.0</td>\n",
       "      <td>27096.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8126.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.08.09</td>\n",
       "      <td>16052</td>\n",
       "      <td>Gera, kreisfreie Stadt</td>\n",
       "      <td>85482.0</td>\n",
       "      <td>53</td>\n",
       "      <td>44488.0</td>\n",
       "      <td>12197.0</td>\n",
       "      <td>7002.0</td>\n",
       "      <td>2486.0</td>\n",
       "      <td>3727.0</td>\n",
       "      <td>16096.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2980.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30.08.09</td>\n",
       "      <td>16053</td>\n",
       "      <td>Jena, kreisfreie Stadt</td>\n",
       "      <td>84679.0</td>\n",
       "      <td>59,1</td>\n",
       "      <td>49407.0</td>\n",
       "      <td>11155.0</td>\n",
       "      <td>11001.0</td>\n",
       "      <td>6818.0</td>\n",
       "      <td>4571.0</td>\n",
       "      <td>12840.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30.08.09</td>\n",
       "      <td>16054</td>\n",
       "      <td>Suhl, kreisfreie Stadt</td>\n",
       "      <td>34101.0</td>\n",
       "      <td>55</td>\n",
       "      <td>18489.0</td>\n",
       "      <td>4590.0</td>\n",
       "      <td>2949.0</td>\n",
       "      <td>1091.0</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>7108.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1464.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Datum    AGS                      Gemeinde  Wahlberechtigte  \\\n",
       "0  30.08.09     16                     Thüringen        1910074.0   \n",
       "1  30.08.09  16051      Erfurt, kreisfreie Stadt         167444.0   \n",
       "2  30.08.09  16052        Gera, kreisfreie Stadt          85482.0   \n",
       "3  30.08.09  16053        Jena, kreisfreie Stadt          84679.0   \n",
       "4  30.08.09  16054        Suhl, kreisfreie Stadt          34101.0   \n",
       "\n",
       "  Wahlbeteiligung  GŸltige Stimmen     Union       SPD    GrŸne      FDP  \\\n",
       "0            56,2        1054297.0  329302.0  195363.0  64912.0  80600.0   \n",
       "1            58,1          95498.0   24134.0   18875.0  10041.0   7226.0   \n",
       "2              53          44488.0   12197.0    7002.0   2486.0   3727.0   \n",
       "3            59,1          49407.0   11155.0   11001.0   6818.0   4571.0   \n",
       "4              55          18489.0    4590.0    2949.0   1091.0   1287.0   \n",
       "\n",
       "      Linke  AfD  Sonstige Parteien  \n",
       "0  288915.0  NaN            95205.0  \n",
       "1   27096.0  NaN             8126.0  \n",
       "2   16096.0  NaN             2980.0  \n",
       "3   12840.0  NaN             3022.0  \n",
       "4    7108.0  NaN             1464.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "th09.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fix AGS, add year column and state number to dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define function to fix the AGS\n",
    "def ags_fixer(df):\n",
    "    df = df[df['AGS'] != '']\n",
    "    df['AGS'] = df['AGS'].astype(int)\n",
    "    df['AGS'] = df['AGS'].astype(str)\n",
    "    df['AGS'] = np.where(df['AGS'].str.len() <= 2, df['AGS'] + '0'*6, df['AGS']) # add 6*0 after AGS for states with 1 or 2 digit AGS\n",
    "    df['AGS'] = np.where(df['AGS'].str.len() < 6, df['AGS'] + '0'*3, df['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
    "    df['AGS'] = np.where(df['AGS'].str.len() < 8, '0' + df['AGS'], df['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "both          4510\n",
      "left_only      252\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n",
      "both          4510\n",
      "left_only      253\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n",
      "both          860\n",
      "left_only      23\n",
      "right_only      0\n",
      "Name: _merge, dtype: int64\n",
      "both          860\n",
      "left_only      22\n",
      "right_only      0\n",
      "Name: _merge, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "both          792\n",
      "left_only      41\n",
      "right_only      0\n",
      "Name: _merge, dtype: int64\n",
      "both          792\n",
      "left_only      41\n",
      "right_only      0\n",
      "Name: _merge, dtype: int64\n",
      "both          792\n",
      "left_only      41\n",
      "right_only      0\n",
      "Name: _merge, dtype: int64\n",
      "both          2122\n",
      "left_only      108\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:11: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:12: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34837/1859480891.py:13: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "both          2122\n",
      "left_only      185\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n",
      "both          3432\n",
      "left_only       59\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n",
      "both          3432\n",
      "left_only      171\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n",
      "both          3432\n",
      "left_only      178\n",
      "right_only       0\n",
      "Name: _merge, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(32989, 16)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs = [by08, by13, by18, he09, he13, he18, nrw10, nrw12, nrw17, ns08, ns13, ns17, th09, th14, th19]\n",
    "years = [2008, 2013, 2018, 2009, 2013, 2018, 2010, 2012, 2017, 2008, 2013, 2017, 2009, 2014, 2019]\n",
    "states = ['BY', 'BY', 'BY', 'HE', 'HE', 'HE', 'NW', 'NW', 'NW', 'NI', 'NI', 'NI',  'TH', 'TH', 'TH']\n",
    "for i, (year, df, state) in enumerate(zip(years, dfs, states)):\n",
    "    try:\n",
    "        # insert year column and state\n",
    "        dfs[i].insert(loc=0, column='year', value=year)\n",
    "        dfs[i].insert(loc=0, column='Land', value=state)\n",
    "        dfs[i] = dfs[i][dfs[i]['AGS'].str.len() > 3]\n",
    "        # fix the AGS\n",
    "        dfs[i]['AGS'] = dfs[i]['AGS'].astype(str)\n",
    "        dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 6, dfs[i]['AGS'] + '0'*3, dfs[i]['AGS']) # add trailing 000 for kreisfreie Städte (AGS has 4-5 digits before, 7-8 after)\n",
    "        dfs[i]['AGS'] = np.where(dfs[i]['AGS'].str.len() < 8, '0' + dfs[i]['AGS'], dfs[i]['AGS']) # add leading 0 for state ids < 10 (AGS has 7 digits)\n",
    "    except Exception:\n",
    "        pass\n",
    "    # don't have to change the AGS for 2013\n",
    "    if year == 2013:\n",
    "        continue\n",
    "    # read in AGS transition datasets for same year and previous year\n",
    "    ags_trans_same = pd.read_csv(f'{path}/data/ags_transition.csv', usecols=[f'AGS_{year}', 'AGS_2013'], converters={f'AGS_{year}': str, 'AGS_2013': str})\n",
    "    ags_trans_same.rename(columns = {f'AGS_{year}':'AGS_new'}, inplace = True)\n",
    "    ags_trans_prev = pd.read_csv(f'{path}/data/ags_transition.csv', usecols=[f'AGS_{year-1}', 'AGS_2013'], converters={f'AGS_{year-1}': str, 'AGS_2013': str})\n",
    "    ags_trans_prev.rename(columns = {f'AGS_{year-1}':'AGS_new'}, inplace = True)\n",
    "    ags_trans = pd.concat([ags_trans_same, ags_trans_prev])\n",
    "    # merge dfs[i] with ags trans\n",
    "    dfs[i] = dfs[i].merge(ags_trans, left_on='AGS', right_on=f'AGS_new', how='left', indicator=True)\n",
    "    print(dfs[i]._merge.value_counts())\n",
    "    # if merged successfully, replace AGS\n",
    "    dfs[i]['AGS'] = np.where(dfs[i]['AGS_2013'].notna(), dfs[i]['AGS_2013'], dfs[i]['AGS'])\n",
    "    dfs[i] = dfs[i].drop(['AGS_2013', f'AGS_new'], axis=1)\n",
    "    # move AGS to front\n",
    "    AGS = dfs[i].pop('AGS')\n",
    "    dfs[i].insert(1, 'AGS', AGS)\n",
    "# Concatenate dfs\n",
    "ltw = pd.concat(dfs, sort=False)\n",
    "ltw.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The merging creates some duplicates --> drop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16527, 16)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw = ltw.drop_duplicates(keep='first')\n",
    "ltw.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check if AGS was properly calculated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16527, 16)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw_ags = ltw[ltw['AGS'].str.len() == 8]\n",
    "ltw_ags.shape\n",
    "# --> all AGS are 8 digits long\n",
    "# Still there are some duplicates, e.g. Gießen Stadt and Landkreis --> these won't be matched when merging with treatment data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add County ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16061    293\n",
       "16074    284\n",
       "16071    260\n",
       "16075    247\n",
       "16067    227\n",
       "        ... \n",
       "05114      3\n",
       "05113      3\n",
       "09162      3\n",
       "05111      3\n",
       "05112      3\n",
       "Name: Kreis, Length: 246, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw['Kreis'] = ltw['AGS'].str.slice(0,5)\n",
    "ltw['Kreis'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add state ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "09    7056\n",
       "03    3522\n",
       "16    3276\n",
       "06    1362\n",
       "05    1311\n",
       "Name: state_ID, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw['state_ID'] = ltw['AGS'].str.slice(0,2)\n",
    "ltw['state_ID'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean data and sum votes for AfD and Andere"
   ]
  },
  {
   "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>Land</th>\n",
       "      <th>AGS</th>\n",
       "      <th>year</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>Gültig</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>Grüne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "      <th>_merge</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>state_ID</th>\n",
       "      <th>Andere</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BY</td>\n",
       "      <td>09161000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Ingolstadt</td>\n",
       "      <td>87814.0</td>\n",
       "      <td>49,1</td>\n",
       "      <td>42062.0</td>\n",
       "      <td>16784.0</td>\n",
       "      <td>8303.0</td>\n",
       "      <td>3537.0</td>\n",
       "      <td>3429.0</td>\n",
       "      <td>2265.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7745.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09161</td>\n",
       "      <td>09</td>\n",
       "      <td>7745.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BY</td>\n",
       "      <td>09162000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>München, Landeshauptstadt</td>\n",
       "      <td>882602.0</td>\n",
       "      <td>57</td>\n",
       "      <td>496289.0</td>\n",
       "      <td>157505.0</td>\n",
       "      <td>139864.0</td>\n",
       "      <td>71528.0</td>\n",
       "      <td>62369.0</td>\n",
       "      <td>25746.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39279.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09162</td>\n",
       "      <td>09</td>\n",
       "      <td>39279.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BY</td>\n",
       "      <td>09163000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Rosenheim</td>\n",
       "      <td>40902.0</td>\n",
       "      <td>52,1</td>\n",
       "      <td>20831.0</td>\n",
       "      <td>8481.0</td>\n",
       "      <td>3788.0</td>\n",
       "      <td>2686.0</td>\n",
       "      <td>1739.0</td>\n",
       "      <td>938.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09163</td>\n",
       "      <td>09</td>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Altötting, Landkreis</td>\n",
       "      <td>82425.0</td>\n",
       "      <td>56,4</td>\n",
       "      <td>45350.0</td>\n",
       "      <td>21106.0</td>\n",
       "      <td>8030.0</td>\n",
       "      <td>3542.0</td>\n",
       "      <td>3411.0</td>\n",
       "      <td>1531.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7731.0</td>\n",
       "      <td>left_only</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>7731.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171111</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Altötting, St</td>\n",
       "      <td>9731.0</td>\n",
       "      <td>52,1</td>\n",
       "      <td>4911.0</td>\n",
       "      <td>2273.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>324.0</td>\n",
       "      <td>191.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1223.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>1223.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Land       AGS  year     Datum                       Gemeinde  \\\n",
       "0   BY  09161000  2008  28.09.08                     Ingolstadt   \n",
       "2   BY  09162000  2008  28.09.08      München, Landeshauptstadt   \n",
       "4   BY  09163000  2008  28.09.08                      Rosenheim   \n",
       "6   BY  09171000  2008  28.09.08           Altötting, Landkreis   \n",
       "7   BY  09171111  2008  28.09.08                  Altötting, St   \n",
       "\n",
       "   Wahlberechtigte Wahlbeteiligung    Gültig     Union       SPD    Grüne  \\\n",
       "0          87814.0            49,1   42062.0   16784.0    8303.0   3537.0   \n",
       "2         882602.0              57  496289.0  157505.0  139864.0  71528.0   \n",
       "4          40902.0            52,1   20831.0    8481.0    3788.0   2686.0   \n",
       "6          82425.0            56,4   45350.0   21106.0    8030.0   3542.0   \n",
       "7           9731.0            52,1    4911.0    2273.0     567.0    335.0   \n",
       "\n",
       "       FDP    Linke  AfD  Sonstige Parteien     _merge  Kreis state_ID  \\\n",
       "0   3429.0   2265.0  NaN             7745.0       both  09161       09   \n",
       "2  62369.0  25746.0  NaN            39279.0       both  09162       09   \n",
       "4   1739.0    938.0  NaN             3200.0       both  09163       09   \n",
       "6   3411.0   1531.0  NaN             7731.0  left_only  09171       09   \n",
       "7    324.0    191.0  NaN             1223.0       both  09171       09   \n",
       "\n",
       "    Andere  \n",
       "0   7745.0  \n",
       "2  39279.0  \n",
       "4   3200.0  \n",
       "6   7731.0  \n",
       "7   1223.0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw.rename(columns = {'GŸltige Stimmen':'Gültig', 'GrŸne': 'Grüne'}, inplace = True)\n",
    "andere = ['AfD', 'Sonstige Parteien']\n",
    "ltw['Andere'] = ltw[andere].sum(axis=1)\n",
    "ltw.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate vote shares in percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Land</th>\n",
       "      <th>AGS</th>\n",
       "      <th>year</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>Gültig</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>Grüne</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "      <th>_merge</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>state_ID</th>\n",
       "      <th>Andere</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BY</td>\n",
       "      <td>09161000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Ingolstadt</td>\n",
       "      <td>87814.0</td>\n",
       "      <td>49,1</td>\n",
       "      <td>42062.0</td>\n",
       "      <td>39.903000</td>\n",
       "      <td>19.739908</td>\n",
       "      <td>8.409015</td>\n",
       "      <td>8.152251</td>\n",
       "      <td>5.384908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7745.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09161</td>\n",
       "      <td>09</td>\n",
       "      <td>18.413295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BY</td>\n",
       "      <td>09162000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>München, Landeshauptstadt</td>\n",
       "      <td>882602.0</td>\n",
       "      <td>57</td>\n",
       "      <td>496289.0</td>\n",
       "      <td>31.736549</td>\n",
       "      <td>28.181967</td>\n",
       "      <td>14.412570</td>\n",
       "      <td>12.567073</td>\n",
       "      <td>5.187703</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39279.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09162</td>\n",
       "      <td>09</td>\n",
       "      <td>7.914542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BY</td>\n",
       "      <td>09163000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Rosenheim</td>\n",
       "      <td>40902.0</td>\n",
       "      <td>52,1</td>\n",
       "      <td>20831.0</td>\n",
       "      <td>40.713360</td>\n",
       "      <td>18.184437</td>\n",
       "      <td>12.894244</td>\n",
       "      <td>8.348135</td>\n",
       "      <td>4.502904</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09163</td>\n",
       "      <td>09</td>\n",
       "      <td>15.361721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171000</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Altötting, Landkreis</td>\n",
       "      <td>82425.0</td>\n",
       "      <td>56,4</td>\n",
       "      <td>45350.0</td>\n",
       "      <td>46.540243</td>\n",
       "      <td>17.706725</td>\n",
       "      <td>7.810364</td>\n",
       "      <td>7.521499</td>\n",
       "      <td>3.375965</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7731.0</td>\n",
       "      <td>left_only</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>17.047409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171111</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Altötting, St</td>\n",
       "      <td>9731.0</td>\n",
       "      <td>52,1</td>\n",
       "      <td>4911.0</td>\n",
       "      <td>46.283853</td>\n",
       "      <td>11.545510</td>\n",
       "      <td>6.821421</td>\n",
       "      <td>6.597434</td>\n",
       "      <td>3.889228</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1223.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>24.903278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171112</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Burghausen, St</td>\n",
       "      <td>13273.0</td>\n",
       "      <td>56,6</td>\n",
       "      <td>7326.0</td>\n",
       "      <td>40.281190</td>\n",
       "      <td>26.003276</td>\n",
       "      <td>9.555010</td>\n",
       "      <td>10.455910</td>\n",
       "      <td>3.931204</td>\n",
       "      <td>NaN</td>\n",
       "      <td>718.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>9.800710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171113</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Burgkirchen a.d.Alz</td>\n",
       "      <td>7664.0</td>\n",
       "      <td>51,3</td>\n",
       "      <td>3846.0</td>\n",
       "      <td>48.465939</td>\n",
       "      <td>19.344774</td>\n",
       "      <td>6.838274</td>\n",
       "      <td>7.124285</td>\n",
       "      <td>2.704108</td>\n",
       "      <td>NaN</td>\n",
       "      <td>597.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>15.522621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171114</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Emmerting</td>\n",
       "      <td>3081.0</td>\n",
       "      <td>57,5</td>\n",
       "      <td>1722.0</td>\n",
       "      <td>42.508711</td>\n",
       "      <td>16.202091</td>\n",
       "      <td>7.897793</td>\n",
       "      <td>9.407666</td>\n",
       "      <td>2.613240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>369.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>21.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171115</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Erlbach</td>\n",
       "      <td>950.0</td>\n",
       "      <td>63,7</td>\n",
       "      <td>592.0</td>\n",
       "      <td>59.290541</td>\n",
       "      <td>6.418919</td>\n",
       "      <td>5.574324</td>\n",
       "      <td>7.263514</td>\n",
       "      <td>2.027027</td>\n",
       "      <td>NaN</td>\n",
       "      <td>116.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>19.594595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>BY</td>\n",
       "      <td>09171116</td>\n",
       "      <td>2008</td>\n",
       "      <td>28.09.08</td>\n",
       "      <td>Feichten a.d.Alz</td>\n",
       "      <td>890.0</td>\n",
       "      <td>61,1</td>\n",
       "      <td>536.0</td>\n",
       "      <td>52.611940</td>\n",
       "      <td>9.328358</td>\n",
       "      <td>13.432836</td>\n",
       "      <td>7.649254</td>\n",
       "      <td>4.104478</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.0</td>\n",
       "      <td>both</td>\n",
       "      <td>09171</td>\n",
       "      <td>09</td>\n",
       "      <td>13.246269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Land       AGS  year     Datum                       Gemeinde  \\\n",
       "0    BY  09161000  2008  28.09.08                     Ingolstadt   \n",
       "2    BY  09162000  2008  28.09.08      München, Landeshauptstadt   \n",
       "4    BY  09163000  2008  28.09.08                      Rosenheim   \n",
       "6    BY  09171000  2008  28.09.08           Altötting, Landkreis   \n",
       "7    BY  09171111  2008  28.09.08                  Altötting, St   \n",
       "9    BY  09171112  2008  28.09.08                 Burghausen, St   \n",
       "11   BY  09171113  2008  28.09.08            Burgkirchen a.d.Alz   \n",
       "13   BY  09171114  2008  28.09.08                      Emmerting   \n",
       "15   BY  09171115  2008  28.09.08                        Erlbach   \n",
       "17   BY  09171116  2008  28.09.08               Feichten a.d.Alz   \n",
       "\n",
       "    Wahlberechtigte Wahlbeteiligung    Gültig      Union        SPD  \\\n",
       "0           87814.0            49,1   42062.0  39.903000  19.739908   \n",
       "2          882602.0              57  496289.0  31.736549  28.181967   \n",
       "4           40902.0            52,1   20831.0  40.713360  18.184437   \n",
       "6           82425.0            56,4   45350.0  46.540243  17.706725   \n",
       "7            9731.0            52,1    4911.0  46.283853  11.545510   \n",
       "9           13273.0            56,6    7326.0  40.281190  26.003276   \n",
       "11           7664.0            51,3    3846.0  48.465939  19.344774   \n",
       "13           3081.0            57,5    1722.0  42.508711  16.202091   \n",
       "15            950.0            63,7     592.0  59.290541   6.418919   \n",
       "17            890.0            61,1     536.0  52.611940   9.328358   \n",
       "\n",
       "        Grüne        FDP     Linke  AfD  Sonstige Parteien     _merge  Kreis  \\\n",
       "0    8.409015   8.152251  5.384908  NaN             7745.0       both  09161   \n",
       "2   14.412570  12.567073  5.187703  NaN            39279.0       both  09162   \n",
       "4   12.894244   8.348135  4.502904  NaN             3200.0       both  09163   \n",
       "6    7.810364   7.521499  3.375965  NaN             7731.0  left_only  09171   \n",
       "7    6.821421   6.597434  3.889228  NaN             1223.0       both  09171   \n",
       "9    9.555010  10.455910  3.931204  NaN              718.0       both  09171   \n",
       "11   6.838274   7.124285  2.704108  NaN              597.0       both  09171   \n",
       "13   7.897793   9.407666  2.613240  NaN              369.0       both  09171   \n",
       "15   5.574324   7.263514  2.027027  NaN              116.0       both  09171   \n",
       "17  13.432836   7.649254  4.104478  NaN               71.0       both  09171   \n",
       "\n",
       "   state_ID     Andere  \n",
       "0        09  18.413295  \n",
       "2        09   7.914542  \n",
       "4        09  15.361721  \n",
       "6        09  17.047409  \n",
       "7        09  24.903278  \n",
       "9        09   9.800710  \n",
       "11       09  15.522621  \n",
       "13       09  21.428571  \n",
       "15       09  19.594595  \n",
       "17       09  13.246269  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parties = ['Union',\t'SPD', 'FDP', 'Linke', 'Grüne', 'Andere']\n",
    "for party in parties:\n",
    "    ltw.loc[:, party] = (ltw[party] / ltw['Gültig'])*100\n",
    "ltw.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add post-treatment indicator"
   ]
  },
  {
   "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>AGS</th>\n",
       "      <th>year</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wahlbeteiligung</th>\n",
       "      <th>Gültig</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>...</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>AfD</th>\n",
       "      <th>Sonstige Parteien</th>\n",
       "      <th>_merge</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>state_ID</th>\n",
       "      <th>Andere</th>\n",
       "      <th>post_2014</th>\n",
       "      <th>post_2010</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NI</td>\n",
       "      <td>03101000</td>\n",
       "      <td>2008</td>\n",
       "      <td>27.01.08</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>187773.0</td>\n",
       "      <td>57,2</td>\n",
       "      <td>106089.0</td>\n",
       "      <td>36.861503</td>\n",
       "      <td>31.245464</td>\n",
       "      <td>...</td>\n",
       "      <td>8.087549</td>\n",
       "      <td>9.549529</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3429.0</td>\n",
       "      <td>both</td>\n",
       "      <td>03101</td>\n",
       "      <td>03</td>\n",
       "      <td>3.232192</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NI</td>\n",
       "      <td>03101000</td>\n",
       "      <td>2013</td>\n",
       "      <td>20.01.13</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>192582.0</td>\n",
       "      <td>59,9</td>\n",
       "      <td>114222.0</td>\n",
       "      <td>29.666789</td>\n",
       "      <td>33.671272</td>\n",
       "      <td>...</td>\n",
       "      <td>8.250600</td>\n",
       "      <td>4.246117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03101</td>\n",
       "      <td>03</td>\n",
       "      <td>5.556723</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NI</td>\n",
       "      <td>03101000</td>\n",
       "      <td>2017</td>\n",
       "      <td>15.10.17</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>190754.0</td>\n",
       "      <td>63,7</td>\n",
       "      <td>120937.0</td>\n",
       "      <td>26.342641</td>\n",
       "      <td>37.604703</td>\n",
       "      <td>...</td>\n",
       "      <td>7.904115</td>\n",
       "      <td>6.741527</td>\n",
       "      <td>7303.0</td>\n",
       "      <td>4240.0</td>\n",
       "      <td>both</td>\n",
       "      <td>03101</td>\n",
       "      <td>03</td>\n",
       "      <td>9.544639</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NI</td>\n",
       "      <td>03102000</td>\n",
       "      <td>2008</td>\n",
       "      <td>27.01.08</td>\n",
       "      <td>Salzgitter, kreisfreie Stadt</td>\n",
       "      <td>78313.0</td>\n",
       "      <td>54,5</td>\n",
       "      <td>41776.0</td>\n",
       "      <td>37.476063</td>\n",
       "      <td>39.268958</td>\n",
       "      <td>...</td>\n",
       "      <td>5.740138</td>\n",
       "      <td>8.828035</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>both</td>\n",
       "      <td>03102</td>\n",
       "      <td>03</td>\n",
       "      <td>4.394868</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NI</td>\n",
       "      <td>03102000</td>\n",
       "      <td>2013</td>\n",
       "      <td>20.01.13</td>\n",
       "      <td>Salzgitter, kreisfreie Stadt</td>\n",
       "      <td>75483.0</td>\n",
       "      <td>52,5</td>\n",
       "      <td>38983.0</td>\n",
       "      <td>30.880127</td>\n",
       "      <td>44.075623</td>\n",
       "      <td>...</td>\n",
       "      <td>6.053921</td>\n",
       "      <td>4.135136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2184.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03102</td>\n",
       "      <td>03</td>\n",
       "      <td>5.602442</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NI</td>\n",
       "      <td>03102000</td>\n",
       "      <td>2017</td>\n",
       "      <td>15.10.17</td>\n",
       "      <td>Salzgitter, kreisfreie Stadt</td>\n",
       "      <td>73602.0</td>\n",
       "      <td>56,9</td>\n",
       "      <td>41561.0</td>\n",
       "      <td>24.445995</td>\n",
       "      <td>45.203436</td>\n",
       "      <td>...</td>\n",
       "      <td>5.319891</td>\n",
       "      <td>4.862732</td>\n",
       "      <td>5665.0</td>\n",
       "      <td>944.0</td>\n",
       "      <td>both</td>\n",
       "      <td>03102</td>\n",
       "      <td>03</td>\n",
       "      <td>15.901927</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NI</td>\n",
       "      <td>03103000</td>\n",
       "      <td>2008</td>\n",
       "      <td>27.01.08</td>\n",
       "      <td>Wolfsburg, kreisfreie Stadt</td>\n",
       "      <td>90631.0</td>\n",
       "      <td>51,6</td>\n",
       "      <td>45996.0</td>\n",
       "      <td>43.121141</td>\n",
       "      <td>31.515784</td>\n",
       "      <td>...</td>\n",
       "      <td>6.691886</td>\n",
       "      <td>7.863727</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>both</td>\n",
       "      <td>03103</td>\n",
       "      <td>03</td>\n",
       "      <td>4.298200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NI</td>\n",
       "      <td>03103000</td>\n",
       "      <td>2013</td>\n",
       "      <td>20.01.13</td>\n",
       "      <td>Wolfsburg, kreisfreie Stadt</td>\n",
       "      <td>92716.0</td>\n",
       "      <td>53,1</td>\n",
       "      <td>48597.0</td>\n",
       "      <td>37.629895</td>\n",
       "      <td>35.812910</td>\n",
       "      <td>...</td>\n",
       "      <td>7.342017</td>\n",
       "      <td>3.210075</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2480.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03103</td>\n",
       "      <td>03</td>\n",
       "      <td>5.103196</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NI</td>\n",
       "      <td>03103000</td>\n",
       "      <td>2017</td>\n",
       "      <td>15.10.17</td>\n",
       "      <td>Wolfsburg, kreisfreie Stadt</td>\n",
       "      <td>89468.0</td>\n",
       "      <td>58,2</td>\n",
       "      <td>51820.0</td>\n",
       "      <td>29.942107</td>\n",
       "      <td>43.211115</td>\n",
       "      <td>...</td>\n",
       "      <td>6.092242</td>\n",
       "      <td>4.525280</td>\n",
       "      <td>4177.0</td>\n",
       "      <td>1251.0</td>\n",
       "      <td>both</td>\n",
       "      <td>03103</td>\n",
       "      <td>03</td>\n",
       "      <td>10.474720</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NI</td>\n",
       "      <td>03151000</td>\n",
       "      <td>2008</td>\n",
       "      <td>27.01.08</td>\n",
       "      <td>Gifhorn, Landkreis</td>\n",
       "      <td>132690.0</td>\n",
       "      <td>57</td>\n",
       "      <td>74634.0</td>\n",
       "      <td>45.717769</td>\n",
       "      <td>30.467347</td>\n",
       "      <td>...</td>\n",
       "      <td>6.957955</td>\n",
       "      <td>6.738216</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2885.0</td>\n",
       "      <td>left_only</td>\n",
       "      <td>03151</td>\n",
       "      <td>03</td>\n",
       "      <td>3.865530</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Land       AGS  year     Datum                            Gemeinde  \\\n",
       "0   NI  03101000  2008  27.01.08      Braunschweig, kreisfreie Stadt   \n",
       "2   NI  03101000  2013  20.01.13      Braunschweig, kreisfreie Stadt   \n",
       "0   NI  03101000  2017  15.10.17      Braunschweig, kreisfreie Stadt   \n",
       "2   NI  03102000  2008  27.01.08        Salzgitter, kreisfreie Stadt   \n",
       "3   NI  03102000  2013  20.01.13        Salzgitter, kreisfreie Stadt   \n",
       "2   NI  03102000  2017  15.10.17        Salzgitter, kreisfreie Stadt   \n",
       "4   NI  03103000  2008  27.01.08         Wolfsburg, kreisfreie Stadt   \n",
       "4   NI  03103000  2013  20.01.13         Wolfsburg, kreisfreie Stadt   \n",
       "4   NI  03103000  2017  15.10.17         Wolfsburg, kreisfreie Stadt   \n",
       "6   NI  03151000  2008  27.01.08                  Gifhorn, Landkreis   \n",
       "\n",
       "   Wahlberechtigte Wahlbeteiligung    Gültig      Union        SPD  ...  \\\n",
       "0         187773.0            57,2  106089.0  36.861503  31.245464  ...   \n",
       "2         192582.0            59,9  114222.0  29.666789  33.671272  ...   \n",
       "0         190754.0            63,7  120937.0  26.342641  37.604703  ...   \n",
       "2          78313.0            54,5   41776.0  37.476063  39.268958  ...   \n",
       "3          75483.0            52,5   38983.0  30.880127  44.075623  ...   \n",
       "2          73602.0            56,9   41561.0  24.445995  45.203436  ...   \n",
       "4          90631.0            51,6   45996.0  43.121141  31.515784  ...   \n",
       "4          92716.0            53,1   48597.0  37.629895  35.812910  ...   \n",
       "4          89468.0            58,2   51820.0  29.942107  43.211115  ...   \n",
       "6         132690.0              57   74634.0  45.717769  30.467347  ...   \n",
       "\n",
       "        FDP     Linke     AfD  Sonstige Parteien     _merge  Kreis state_ID  \\\n",
       "0  8.087549  9.549529     NaN             3429.0       both  03101       03   \n",
       "2  8.250600  4.246117     NaN             6347.0        NaN  03101       03   \n",
       "0  7.904115  6.741527  7303.0             4240.0       both  03101       03   \n",
       "2  5.740138  8.828035     NaN             1836.0       both  03102       03   \n",
       "3  6.053921  4.135136     NaN             2184.0        NaN  03102       03   \n",
       "2  5.319891  4.862732  5665.0              944.0       both  03102       03   \n",
       "4  6.691886  7.863727     NaN             1977.0       both  03103       03   \n",
       "4  7.342017  3.210075     NaN             2480.0        NaN  03103       03   \n",
       "4  6.092242  4.525280  4177.0             1251.0       both  03103       03   \n",
       "6  6.957955  6.738216     NaN             2885.0  left_only  03151       03   \n",
       "\n",
       "      Andere  post_2014  post_2010  \n",
       "0   3.232192          0          0  \n",
       "2   5.556723          0          1  \n",
       "0   9.544639          1          1  \n",
       "2   4.394868          0          0  \n",
       "3   5.602442          0          1  \n",
       "2  15.901927          1          1  \n",
       "4   4.298200          0          0  \n",
       "4   5.103196          0          1  \n",
       "4  10.474720          1          1  \n",
       "6   3.865530          0          0  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw['post_2014'] = np.where(ltw['year'] >= 2014, 1, 0)\n",
    "ltw['post_2010'] = np.where(ltw['year'] > 2010, 1, 0)\n",
    "ltw = ltw.sort_values(['AGS', 'year'])\n",
    "ltw.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subset dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16527, 14)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw = ltw[['AGS', 'Gemeinde', 'Land', 'state_ID', 'Kreis', 'year', 'post_2010', 'post_2014', 'Union', 'SPD', 'Grüne', 'FDP', 'Linke', 'Andere']]\n",
    "ltw.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BY    7056\n",
       "NI    3522\n",
       "TH    3276\n",
       "HE    1362\n",
       "NW    1311\n",
       "Name: Land, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw.Land.value_counts(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inspect balacing of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BY\n",
      "2018    2354\n",
      "2008    2351\n",
      "2013    2351\n",
      "Name: year, dtype: int64\n",
      "NI\n",
      "2017    1212\n",
      "2008    1155\n",
      "2013    1155\n",
      "Name: year, dtype: int64\n",
      "TH\n",
      "2019    1235\n",
      "2009    1021\n",
      "2014    1020\n",
      "Name: year, dtype: int64\n",
      "HE\n",
      "2018    456\n",
      "2009    453\n",
      "2013    453\n",
      "Name: year, dtype: int64\n",
      "NW\n",
      "2010    437\n",
      "2012    437\n",
      "2017    437\n",
      "Name: year, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "dfs = ['BY', 'NI', 'TH', 'HE', 'NW']\n",
    "for i, df in enumerate(dfs):\n",
    "    print(df)\n",
    "    dfs[i] = ltw[ltw['Land'] == df]\n",
    "    print(dfs[i].year.value_counts())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge with treatment dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          15329\n",
       "left_only      1198\n",
       "right_only        0\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "treatment = pd.read_csv(f'{path}/data/treatment.csv', converters = {'AGS': str})\n",
    "ltw_treat = pd.merge(ltw, treatment, on='AGS', how='left', indicator=True)\n",
    "ltw_treat['_merge'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean and export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "ltw_treat = ltw_treat[ltw_treat['_merge'] == 'both']\n",
    "ltw_treat = ltw_treat.drop(columns=['_merge'])\n",
    "ltw_treat = ltw_treat.set_index(['AGS', 'GEN', 'year'])\n",
    "ltw_treat.to_csv(f'{path}/data/ltw_treat.csv', encoding = 'utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge in control variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          15148\n",
       "left_only       181\n",
       "right_only        0\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw_treat = ltw_treat.reset_index()\n",
    "controls = pd.read_csv(f'{path}/data/controls_ltw.csv', converters = {'AGS': str})\n",
    "ltw_control = pd.merge(ltw_treat, controls, on=['AGS', 'year'], how='left', indicator=True)\n",
    "ltw_control['_merge'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15148, 29)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw_control = ltw_control[ltw_control['_merge'] == 'both']\n",
    "#ltw_control = ltw_control.set_index(['AGS', 'GEN', 'year'])\n",
    "ltw_control = ltw_control.drop(columns=['_merge'])\n",
    "ltw_control.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Export dataset"
   ]
  },
  {
   "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>AGS</th>\n",
       "      <th>GEN</th>\n",
       "      <th>year</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Land</th>\n",
       "      <th>state_ID</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>post_2010</th>\n",
       "      <th>post_2014</th>\n",
       "      <th>Union</th>\n",
       "      <th>...</th>\n",
       "      <th>treatment_15</th>\n",
       "      <th>treatment_30</th>\n",
       "      <th>treatment_50</th>\n",
       "      <th>ever_treated</th>\n",
       "      <th>ever_treated_2010</th>\n",
       "      <th>never_treated</th>\n",
       "      <th>pop_density</th>\n",
       "      <th>unemployed</th>\n",
       "      <th>avg_age</th>\n",
       "      <th>female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>03101000</td>\n",
       "      <td>Braunschweig</td>\n",
       "      <td>2008</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>NI</td>\n",
       "      <td>03</td>\n",
       "      <td>03101</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36.861503</td>\n",
       "      <td>...</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1280.301848</td>\n",
       "      <td>4.627010</td>\n",
       "      <td>43.07</td>\n",
       "      <td>50.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03101000</td>\n",
       "      <td>Braunschweig</td>\n",
       "      <td>2013</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>NI</td>\n",
       "      <td>03</td>\n",
       "      <td>03101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>29.666789</td>\n",
       "      <td>...</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1286.517146</td>\n",
       "      <td>3.762130</td>\n",
       "      <td>43.16</td>\n",
       "      <td>50.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03101000</td>\n",
       "      <td>Braunschweig</td>\n",
       "      <td>2017</td>\n",
       "      <td>Braunschweig, kreisfreie Stadt</td>\n",
       "      <td>NI</td>\n",
       "      <td>03</td>\n",
       "      <td>03101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>26.342641</td>\n",
       "      <td>...</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1287.078360</td>\n",
       "      <td>3.240828</td>\n",
       "      <td>43.37</td>\n",
       "      <td>50.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>03102000</td>\n",
       "      <td>Salzgitter</td>\n",
       "      <td>2008</td>\n",
       "      <td>Salzgitter, kreisfreie Stadt</td>\n",
       "      <td>NI</td>\n",
       "      <td>03</td>\n",
       "      <td>03102</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>37.476063</td>\n",
       "      <td>...</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Direct Line</td>\n",
       "      <td>Direct Line</td>\n",
       "      <td>0.0</td>\n",
       "      <td>466.368915</td>\n",
       "      <td>4.766191</td>\n",
       "      <td>43.99</td>\n",
       "      <td>51.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>03102000</td>\n",
       "      <td>Salzgitter</td>\n",
       "      <td>2013</td>\n",
       "      <td>Salzgitter, kreisfreie Stadt</td>\n",
       "      <td>NI</td>\n",
       "      <td>03</td>\n",
       "      <td>03102</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>30.880127</td>\n",
       "      <td>...</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Direct Line</td>\n",
       "      <td>Direct Line</td>\n",
       "      <td>0.0</td>\n",
       "      <td>438.549482</td>\n",
       "      <td>4.765930</td>\n",
       "      <td>45.13</td>\n",
       "      <td>51.23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        AGS           GEN  year                            Gemeinde Land  \\\n",
       "0  03101000  Braunschweig  2008      Braunschweig, kreisfreie Stadt   NI   \n",
       "1  03101000  Braunschweig  2013      Braunschweig, kreisfreie Stadt   NI   \n",
       "2  03101000  Braunschweig  2017      Braunschweig, kreisfreie Stadt   NI   \n",
       "3  03102000    Salzgitter  2008        Salzgitter, kreisfreie Stadt   NI   \n",
       "4  03102000    Salzgitter  2013        Salzgitter, kreisfreie Stadt   NI   \n",
       "\n",
       "  state_ID  Kreis  post_2010  post_2014      Union  ...  treatment_15  \\\n",
       "0       03  03101          0          0  36.861503  ...        2005.0   \n",
       "1       03  03101          1          0  29.666789  ...        2005.0   \n",
       "2       03  03101          1          1  26.342641  ...        2005.0   \n",
       "3       03  03102          0          0  37.476063  ...        2005.0   \n",
       "4       03  03102          1          0  30.880127  ...        2005.0   \n",
       "\n",
       "   treatment_30  treatment_50    ever_treated  ever_treated_2010  \\\n",
       "0        2005.0        2005.0  Within 0-15 km     Within 0-15 km   \n",
       "1        2005.0        2005.0  Within 0-15 km     Within 0-15 km   \n",
       "2        2005.0        2005.0  Within 0-15 km     Within 0-15 km   \n",
       "3        2005.0        2005.0     Direct Line        Direct Line   \n",
       "4        2005.0        2005.0     Direct Line        Direct Line   \n",
       "\n",
       "  never_treated  pop_density unemployed  avg_age  female  \n",
       "0           0.0  1280.301848   4.627010    43.07   50.85  \n",
       "1           0.0  1286.517146   3.762130    43.16   50.91  \n",
       "2           0.0  1287.078360   3.240828    43.37   50.52  \n",
       "3           0.0   466.368915   4.766191    43.99   51.09  \n",
       "4           0.0   438.549482   4.765930    45.13   51.23  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ltw_control.to_csv(f'{path}/data/ltw_control.csv', encoding = 'utf-8-sig')\n",
    "ltw_control.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.4 ('vscode')",
   "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.10.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "656f4cd1472b14b513faaa63bd5e4a7577d125de6a277113d4e82c4085557d3e"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}