btw_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": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:4: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw05_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW05_Erststimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:5: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw09_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW09_Erststimmen_Wbz.csv', sep=';', encoding = 'UTF-8-SIG',skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:6: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw13_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW13_Erststimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:7: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw17_es = pd.read_csv(f'{path}/data/elections/btw/es/btw17_wbz_erststimmen.csv', sep=';', encoding = 'Windows-1252', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str, 'Kennziffer Briefwahlzugehörigkeit': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:12: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw05_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW05_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:13: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw09_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW09_Zweitstimmen_Wbz.csv', sep=';', encoding = 'UTF-8-SIG',skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:14: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw13_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW13_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
      "/var/folders/wm/mtrphj0s0msgrmyshf3hgk740000gn/T/ipykernel_34868/115399916.py:15: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  btw17_zs = pd.read_csv(f'{path}/data/elections/btw/zs/btw17_wbz_zweitstimmen.csv', sep=';', encoding = 'Windows-1252', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str, 'Kennziffer Briefwahlzugehörigkeit': str})\n"
     ]
    }
   ],
   "source": [
    "btw94_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW94_Erststimmen_Gemeinden.csv', encoding = 'UTF-8-SIG', sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw98_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW98_Erststimmen_Gemeinden.csv', encoding = 'UTF-8-SIG', sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw02_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW02_Erststimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG', sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw05_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW05_Erststimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw09_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW09_Erststimmen_Wbz.csv', sep=';', encoding = 'UTF-8-SIG',skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw13_es = pd.read_csv(f'{path}/data/elections/btw/es/BTW13_Erststimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw17_es = pd.read_csv(f'{path}/data/elections/btw/es/btw17_wbz_erststimmen.csv', sep=';', encoding = 'Windows-1252', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str, 'Kennziffer Briefwahlzugehörigkeit': str})\n",
    "\n",
    "btw94_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW94_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG', sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw98_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW98_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG', sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw02_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW02_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG', sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw05_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW05_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw09_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW09_Zweitstimmen_Wbz.csv', sep=';', encoding = 'UTF-8-SIG',skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw13_zs = pd.read_csv(f'{path}/data/elections/btw/zs/BTW13_Zweitstimmen_Wahlbezirke.csv', encoding = 'UTF-8-SIG',sep=';', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str})\n",
    "btw17_zs = pd.read_csv(f'{path}/data/elections/btw/zs/btw17_wbz_zweitstimmen.csv', sep=';', encoding = 'Windows-1252', skiprows=4, converters = {'Land': str, 'Regierungsbezirk': str, 'Kreis': str, 'Gemeinde': str, 'Kennziffer Briefwahlzugehörigkeit': str})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create and convert all AGS to the AGS in 2013, add year column "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "ags_trans = pd.read_csv(f'{path}/data/ags_transition.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "both          28815\n",
      "left_only       542\n",
      "right_only        0\n",
      "Name: _merge, dtype: int64\n",
      "both          32944\n",
      "left_only       405\n",
      "right_only        0\n",
      "Name: _merge, dtype: int64\n",
      "both          282569\n",
      "left_only       4224\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          310506\n",
      "left_only       4160\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          322634\n",
      "left_only       3488\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          358616\n",
      "left_only       3185\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          365560\n",
      "left_only       3415\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          185948\n",
      "left_only       3853\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          233340\n",
      "left_only       3939\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          282569\n",
      "left_only       4224\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          310506\n",
      "left_only       4160\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          322634\n",
      "left_only       3488\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          358616\n",
      "left_only       3185\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n",
      "both          365560\n",
      "left_only       3415\n",
      "right_only         0\n",
      "Name: _merge, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "dfs = [btw94_es, btw98_es, btw02_es, btw05_es, btw09_es, btw13_es, btw17_es, btw94_zs, btw98_zs, btw02_zs, btw05_zs, btw09_zs, btw13_zs, btw17_zs]\n",
    "years = [1994, 1998, 2002, 2005, 2009, 2013, 2017, 1994, 1998, 2002, 2005, 2009, 2013, 2017]\n",
    "for i, (year, df) in enumerate(zip(years, dfs)):\n",
    "    # insert year column\n",
    "    dfs[i].insert(loc=0, column='year', value=year)\n",
    "    # create AGS: it follows the scheme ll r kk ggg (Land, Regierunsbezirk, Kreis, Gemeinde) -> combine these columns\n",
    "    dfs[i].insert(loc=0, column='AGS', value=0)\n",
    "    dfs[i]['AGS'] = dfs[i]['Land'] + dfs[i]['Regierungsbezirk'] + dfs[i]['Kreis'] + dfs[i]['Gemeinde']\n",
    "    # Sometimes the AGS of previous years are used (likely because electroral districts are decided on quite early)\n",
    "    # read in AGS transition datasets for same 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",
    "    # concat to one df\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='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', 'AGS_new', '_merge'], axis=1)\n",
    "    # move AGS to front\n",
    "    AGS = dfs[i].pop('AGS')\n",
    "    dfs[i].insert(1, 'AGS', AGS)\n",
    "    # the merge produces duplicates --> drop\n",
    "    dfs[i] = dfs[i].drop_duplicates(keep='first')\n",
    "# unpack list back to variables\n",
    "btw94_es, btw98_es, btw02_es, btw05_es, btw09_es, btw13_es, btw17_es, btw94_zs, btw98_zs, btw02_zs, btw05_zs, btw09_zs, btw13_zs, btw17_zs = dfs\n",
    "#btw_ags_trans = pd.concat(dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since some municipalities share a ballot vote election board, in this case, postal ballots cannot be attributed to a single municipality. \n",
    "In the 1994 to 2017 data, these cases were signified with '999' as Gemeindekennziffer. From 2017 on the Gemeindekennziffer is given by 9 + Kennziffer Briefwahlzugehörigkeit."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To drop the 999 Gemeinde in for 1994-2013, first append these dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(535761, 88)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs = [btw94_es, btw98_es, btw02_es, btw05_es, btw09_es, btw13_es]\n",
    "btw9413_es = pd.concat(dfs, sort=False)\n",
    "dfs = [btw94_zs, btw98_zs, btw02_zs, btw05_zs, btw09_zs, btw13_zs]\n",
    "btw9413_zs = pd.concat(dfs, sort=False)\n",
    "btw9413_zs.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then drop all observations with Gemeinde == 999"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(529717, 88)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw9413_es = btw9413_es[btw9413_es['Gemeinde'] != '999']\n",
    "btw9413_zs = btw9413_zs[btw9413_zs['Gemeinde'] != '999']\n",
    "btw9413_zs.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For 2017, drop all observations where Gemeinde == 9 + Kennziffer Briefwahlzugehörigkeit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw17_es = btw17_es[btw17_es['Gemeinde'] != '9' + btw17_es['Kennziffer Briefwahlzugehörigkeit']]\n",
    "btw17_zs = btw17_zs[btw17_zs['Gemeinde'] != '9' + btw17_zs['Kennziffer Briefwahlzugehörigkeit']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now contatenate the dfs for 1994-2013 with 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [btw9413_es, btw17_es]\n",
    "btw_es_raw = pd.concat(dfs, sort=False)\n",
    "dfs = [btw9413_zs, btw17_zs]\n",
    "btw_zs_raw = pd.concat(dfs, sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since there is only one polygon with the AGS 11000000 for Berlin, replace AGS values here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_es_raw['AGS'] = np.where(btw_es_raw['Land'] == '11', '11000000', btw_es_raw['AGS']) #if condition, = 110..., else AGS\n",
    "btw_zs_raw['AGS'] = np.where(btw_zs_raw['Land'] == '11', '11000000', btw_zs_raw['AGS'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since Kreis Numbers repeat within states, concatenate State Number and Kreis Number to make them unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "92"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(btw_es_raw['Kreis'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "301"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_es_raw['Kreis'] = btw_es_raw['Land'] + btw_es_raw['Kreis']\n",
    "btw_zs_raw['Kreis'] = btw_zs_raw['Land'] + btw_zs_raw['Kreis']\n",
    "len(btw_es_raw['Kreis'].unique()) # In 2021, there are 294 Kreise in DE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "328"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(btw_es_raw['Wahlkreis'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Collapse data along AGS column, sums votes over all constituencies in a municipality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "es_cols = list(btw_es_raw.columns)\n",
    "es_cols_to_keep = list(btw_es_raw.select_dtypes(['object']).columns) + ['Wahlkreis', 'Bezirksart'] # keep these columns\n",
    "es_cols_to_sum = [i for i in es_cols if i not in es_cols_to_keep] # sum these columns\n",
    "es_cols_to_sum.remove('year')\n",
    "zs_cols = list(btw_zs_raw.columns)\n",
    "zs_cols_to_keep = list(btw_zs_raw.select_dtypes(['object']).columns) + ['Wahlkreis', 'Bezirksart']\n",
    "zs_cols_to_sum = [i for i in zs_cols if i not in zs_cols_to_keep]\n",
    "zs_cols_to_sum.remove('year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>year</th>\n",
       "      <th>AGS</th>\n",
       "      <th>Land</th>\n",
       "      <th>Regierungsbezirk</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlbezirk</th>\n",
       "      <th>Kennziffer Briefwahlzugehörigkeit</th>\n",
       "      <th>Ungekürzte Wahlbezirksbezeichnung</th>\n",
       "      <th>Wahlkreis</th>\n",
       "      <th>...</th>\n",
       "      <th>Die Grauen</th>\n",
       "      <th>du</th>\n",
       "      <th>MG</th>\n",
       "      <th>MENSCHLICHE WELT</th>\n",
       "      <th>Gesundheitsforschung</th>\n",
       "      <th>V-Partei³</th>\n",
       "      <th>Bündnis C</th>\n",
       "      <th>DIE EINHEIT</th>\n",
       "      <th>MIETERPARTEI</th>\n",
       "      <th>Neue Liberale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>01001000</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998</td>\n",
       "      <td>01001000</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>01001000</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>01001000</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>000001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>01001000</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>000025</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 123 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year       AGS Land Regierungsbezirk Kreis Gemeinde Wahlbezirk  \\\n",
       "0  1994  01001000   01                0  0101      000       None   \n",
       "1  1998  01001000   01                0  0101      000       None   \n",
       "2  2002  01001000   01                0  0101      000          1   \n",
       "3  2005  01001000   01                0  0101      000     000001   \n",
       "4  2009  01001000   01                0  0101      000     000025   \n",
       "\n",
       "  Kennziffer Briefwahlzugehörigkeit Ungekürzte Wahlbezirksbezeichnung  \\\n",
       "0                              None                              None   \n",
       "1                              None                              None   \n",
       "2                              None                              None   \n",
       "3                               0.0                              None   \n",
       "4                               0.0                              None   \n",
       "\n",
       "   Wahlkreis  ...  Die Grauen   du   MG  MENSCHLICHE WELT  \\\n",
       "0          1  ...         0.0  0.0  0.0               0.0   \n",
       "1          1  ...         0.0  0.0  0.0               0.0   \n",
       "2          1  ...         0.0  0.0  0.0               0.0   \n",
       "3          1  ...         0.0  0.0  0.0               0.0   \n",
       "4          1  ...         0.0  0.0  0.0               0.0   \n",
       "\n",
       "   Gesundheitsforschung  V-Partei³  Bündnis C  DIE EINHEIT  MIETERPARTEI  \\\n",
       "0                   0.0        0.0        0.0          0.0           0.0   \n",
       "1                   0.0        0.0        0.0          0.0           0.0   \n",
       "2                   0.0        0.0        0.0          0.0           0.0   \n",
       "3                   0.0        0.0        0.0          0.0           0.0   \n",
       "4                   0.0        0.0        0.0          0.0           0.0   \n",
       "\n",
       "   Neue Liberale  \n",
       "0            0.0  \n",
       "1            0.0  \n",
       "2            0.0  \n",
       "3            0.0  \n",
       "4            0.0  \n",
       "\n",
       "[5 rows x 123 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_es_coll = btw_es_raw.groupby(['AGS', 'year'], as_index=False).agg({**{e:'first' for e in es_cols_to_keep}, **{e:'sum' for e in es_cols_to_sum}})\n",
    "btw_zs_coll = btw_zs_raw.groupby(['AGS', 'year'], as_index=False).agg({**{e:'first' for e in zs_cols_to_keep}, **{e:'sum' for e in zs_cols_to_sum}})\n",
    "btw_es_coll.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inspect the distribution of obs. over years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1994    11167\n",
       "2009    11163\n",
       "1998    11161\n",
       "2005    11160\n",
       "2002    11159\n",
       "2013    11159\n",
       "2017    11157\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_es_coll['year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1994    11167\n",
       "2009    11163\n",
       "1998    11161\n",
       "2005    11160\n",
       "2002    11159\n",
       "2013    11159\n",
       "2017    11157\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_zs_coll['year'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add indicator for first and second vote"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>AGS</th>\n",
       "      <th>second_vote</th>\n",
       "      <th>Land</th>\n",
       "      <th>Regierungsbezirk</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Gemeinde</th>\n",
       "      <th>Wahlbezirk</th>\n",
       "      <th>Kennziffer Briefwahlzugehörigkeit</th>\n",
       "      <th>Ungekürzte Wahlbezirksbezeichnung</th>\n",
       "      <th>...</th>\n",
       "      <th>BGE</th>\n",
       "      <th>DiB</th>\n",
       "      <th>DM</th>\n",
       "      <th>Die Grauen</th>\n",
       "      <th>du.</th>\n",
       "      <th>MG</th>\n",
       "      <th>MENSCHLICHE WELT</th>\n",
       "      <th>Die Humanisten</th>\n",
       "      <th>Gesundheitsforschung</th>\n",
       "      <th>V-Partei³</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>29</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>000001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>0101</td>\n",
       "      <td>000</td>\n",
       "      <td>000001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 107 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year       AGS  second_vote Land Regierungsbezirk Kreis Gemeinde  \\\n",
       "0  1994  01001000            1   01                0  0101      000   \n",
       "1  1998  01001000            1   01                0  0101      000   \n",
       "2  2002  01001000            1   01                0  0101      000   \n",
       "3  2005  01001000            1   01                0  0101      000   \n",
       "4  2009  01001000            1   01                0  0101      000   \n",
       "\n",
       "  Wahlbezirk Kennziffer Briefwahlzugehörigkeit  \\\n",
       "0          1                              None   \n",
       "1         29                              None   \n",
       "2          1                              None   \n",
       "3     000001                               0.0   \n",
       "4     000001                               0.0   \n",
       "\n",
       "  Ungekürzte Wahlbezirksbezeichnung  ...  BGE  DiB   DM  Die Grauen  du.   MG  \\\n",
       "0                              None  ...  0.0  0.0  0.0         0.0  0.0  0.0   \n",
       "1                              None  ...  0.0  0.0  0.0         0.0  0.0  0.0   \n",
       "2                              None  ...  0.0  0.0  0.0         0.0  0.0  0.0   \n",
       "3                              None  ...  0.0  0.0  0.0         0.0  0.0  0.0   \n",
       "4                              None  ...  0.0  0.0  0.0         0.0  0.0  0.0   \n",
       "\n",
       "   MENSCHLICHE WELT  Die Humanisten  Gesundheitsforschung  V-Partei³  \n",
       "0               0.0             0.0                   0.0        0.0  \n",
       "1               0.0             0.0                   0.0        0.0  \n",
       "2               0.0             0.0                   0.0        0.0  \n",
       "3               0.0             0.0                   0.0        0.0  \n",
       "4               0.0             0.0                   0.0        0.0  \n",
       "\n",
       "[5 rows x 107 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs = [btw_es_coll, btw_zs_coll]\n",
    "eszs = ['first_vote', 'second_vote']\n",
    "for df, stimme in zip(dfs, eszs):\n",
    "    df.insert(loc=2, column=stimme, value=1)\n",
    "btw_zs_coll.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ES: Sum votes for other, Union and Die Linke parties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First declare the columns to sum\n",
    "es_col = list(btw_es_coll.columns)\n",
    "union = ['CDU', 'CSU']\n",
    "linke = ['Die Linke', 'DIE LINKE', 'PDS']\n",
    "invalid = ['Ungültige', 'Ungültig']\n",
    "valid = ['Gültige', 'Gültig']\n",
    "other_keep = ['year',\n",
    " 'AGS',\n",
    " 'first_vote',\n",
    " 'Land',\n",
    " 'Regierungsbezirk',\n",
    " 'Kreis',\n",
    " 'Gemeinde',\n",
    " 'Wahlbezirk',\n",
    " 'Kennziffer Briefwahlzugehörigkeit',\n",
    " 'Ungekürzte Wahlbezirksbezeichnung',\n",
    " 'Wahlkreis',\n",
    " 'Bezirksart',\n",
    " 'Verbandsgemeinde',\n",
    " 'Wahlberechtigte (A)',\n",
    " 'Wahlberechtigte ohne Sperrvermerk (A1)',\n",
    " 'Wahlberechtigte mit Sperrvermerk (A2)',\n",
    " 'Wahlberechtigte nach § 25 Abs. 2 BWO (A3)',\n",
    " 'Wähler (B)',\n",
    " 'Wähler mit Wahlschein (B1)', 'SPD', 'GRÜNE', 'FDP']\n",
    "es_cols_to_keep = union + linke + invalid + valid + other_keep\n",
    "andere = list(set(es_col) ^ set(es_cols_to_keep))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['DEMOKRATEN', 'STATT Partei', 'Die PARTEI', 'DIE EINHEIT', 'Bündnis 21/RRP', 'APD', 'Volksabstimmung', 'DIE RECHTE', 'MG', 'Tierschutzpartei', 'AfD', 'WGr/EB', 'ZENTRUM', 'PIRATEN', 'Bergpartei', 'AB 2000', 'Freie Union', 'DIE FRAUEN', 'ÜBRIGE', 'NATURGESETZ', 'DIE PARTEI', 'DVU', 'BÜSO', 'BüSo', 'MENSCHLICHE WELT', 'LIGA', 'Die Tierschutzpartei', 'V-Partei³', 'BGD', 'AGFG', 'PRG', 'PARTEI DER VERNUNFT', 'FSU', 'Übrige', 'FP D', 'GRAUE', 'ADM', 'pro Deutschland', 'FBU', 'BfB', 'VIOLETTEN', 'CHANCE 2000', 'Tierschutzallianz', 'BIG', 'KPD', 'DIE VIOLETTEN', 'PDV', 'DMP', 'Pro DM', 'Solidarität', 'UAP', 'DEUTSCHLAND', 'PASS', 'DKP', 'FP Deutschland', 'SCHILL', 'CM', 'LD', 'DPD', 'PSG', 'DVP', 'ÖDP', 'Gesundheitsforschung', 'REP', 'FAMILIE', 'MLPD', 'PBC', 'Deutschland', 'MIETERPARTEI', 'Nichtwähler', 'B*', 'FREIE WÄHLER', 'DSU', 'UNABHÄNGIGE', 'Offensive D', 'APPD', 'NEIN', 'Die Grauen', 'RENTNER', 'SGP', 'Neue Liberale', 'du', 'HUMANWIRTSCHAFTSPARTEI', 'NPD', 'AUFBRUCH', 'TIERSCHUTZ', 'Bündnis C', 'BP', 'HP', 'ödp', 'FRAUEN', 'FORUM', 'RRP']\n"
     ]
    }
   ],
   "source": [
    "print(andere)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_es_sum = btw_es_coll.copy()\n",
    "btw_es_sum['Andere'] = btw_es_sum[andere].sum(axis=1) # sum votes for 'Other' Parties\n",
    "btw_es_sum['Union'] = btw_es_sum[union].sum(axis=1) # sum votes for 'Union' Parties\n",
    "btw_es_sum['Linke'] = btw_es_sum[linke].sum(axis=1) # sum votes for 'Linke' Parties\n",
    "btw_es_sum['Ungültig'] = btw_es_sum[invalid].sum(axis=1) # sum invalid votes\n",
    "btw_es_sum['Gültig'] = btw_es_sum[valid].sum(axis=1) # sum valid votes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop not needed columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['year', 'AGS', 'first_vote', 'Land', 'Kreis', 'Wahlkreis', 'Bezirksart',\n",
       "       'Wahlberechtigte (A)', 'Wahlberechtigte ohne Sperrvermerk (A1)',\n",
       "       'Wahlberechtigte mit Sperrvermerk (A2)',\n",
       "       'Wahlberechtigte nach § 25 Abs. 2 BWO (A3)', 'Wähler (B)',\n",
       "       'Wähler mit Wahlschein (B1)', 'Ungültig', 'Gültig', 'SPD', 'FDP',\n",
       "       'GRÜNE', 'Andere', 'Union', 'Linke'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_drop = andere + union + linke + ['Ungültige', 'Gültige', 'Verbandsgemeinde', 'Regierungsbezirk', 'Gemeinde', 'Wahlbezirk', 'Kennziffer Briefwahlzugehörigkeit', 'Ungekürzte Wahlbezirksbezeichnung']\n",
    "btw_es_sum.drop(columns_to_drop, axis=1, inplace=True)\n",
    "btw_es_sum.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ZS: Sum votes for other, Union and DIE LINKE parties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First declare the columns to sum\n",
    "zs_col = list(btw_zs_coll.columns)\n",
    "union = ['CDU', 'CSU']\n",
    "linke = ['Die Linke', 'DIE LINKE', 'PDS']\n",
    "invalid = ['Ungültige', 'Ungültig']\n",
    "valid = ['Gültige', 'Gültig']\n",
    "other_keep = ['year',\n",
    " 'AGS',\n",
    " 'second_vote',\n",
    " 'Land',\n",
    " 'Regierungsbezirk',\n",
    " 'Kreis',\n",
    " 'Gemeinde',\n",
    " 'Wahlbezirk',\n",
    " 'Kennziffer Briefwahlzugehörigkeit',\n",
    " 'Ungekürzte Wahlbezirksbezeichnung',\n",
    " 'Wahlkreis',\n",
    " 'Bezirksart',\n",
    " 'Verbandsgemeinde',\n",
    " 'Wahlberechtigte (A)',\n",
    " 'Wahlberechtigte ohne Sperrvermerk (A1)',\n",
    " 'Wahlberechtigte mit Sperrvermerk (A2)',\n",
    " 'Wahlberechtigte nach § 25 Abs. 2 BWO (A3)',\n",
    " 'Wähler (B)',\n",
    " 'Wähler mit Wahlschein (B1)', 'SPD', 'GRÜNE', 'FDP']\n",
    "zs_cols_to_keep = union + linke + invalid + valid + other_keep\n",
    "andere = list(set(zs_col) ^ set(zs_cols_to_keep))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['STATT Partei', 'Schill', 'Die PARTEI', 'DiB', 'Bündnis 21/RRP', 'APD', 'Volksabstimmung', 'DIE RECHTE', 'MG', 'AfD', 'Tierschutzpartei', 'ZENTRUM', 'PIRATEN', 'AB 2000', 'du.', 'DIE FRAUEN', 'NATURGESETZ', 'DVU', 'BüSo', 'MENSCHLICHE WELT', 'LIGA', 'Die Tierschutzpartei', 'BGE', 'V-Partei³', 'AGFG', 'PRG', 'AD-DEMOKRATEN', 'PARTEI DER VERNUNFT', 'GRAUE', 'ADM', 'FWD', 'pro Deutschland', 'Violetten', 'BfB', 'Partei der Nichtwähler', 'CHANCE 2000', 'Tierschutzallianz', 'BIG', 'KPD', 'DIE VIOLETTEN', 'PDV', 'Pro DM', '50plus', 'BSA', 'PASS', 'DKP', 'CM', 'DPD', 'PSG', 'ÖDP', 'Gesundheitsforschung', 'REP', 'FAMILIE', 'MLPD', 'DM', 'PBC', 'Deutschland', 'Nichtwähler', 'Tierschutz', 'B*', 'Die Partei', 'FREIE WÄHLER', 'Offensive D', 'APPD', 'Die Grauen', 'RENTNER', 'SGP', 'NPD', 'Die Humanisten', 'AUFBRUCH', 'BP', 'HP', 'ödp', 'FRAUEN', 'FORUM', 'RRP']\n"
     ]
    }
   ],
   "source": [
    "print(andere)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_zs_sum = btw_zs_coll.copy()\n",
    "btw_zs_sum['Andere'] = btw_zs_sum[andere].sum(axis=1) # sum votes for 'Other' Parties\n",
    "btw_zs_sum['Union'] = btw_zs_sum[union].sum(axis=1) # sum votes for 'Union' Parties\n",
    "btw_zs_sum['Linke'] = btw_zs_sum[linke].sum(axis=1) # sum votes for 'Linke' Parties\n",
    "btw_zs_sum['Ungültig'] = btw_zs_sum[invalid].sum(axis=1) # sum invalid votes\n",
    "btw_zs_sum['Gültig'] = btw_zs_sum[valid].sum(axis=1) # sum valid votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>AGS</th>\n",
       "      <th>second_vote</th>\n",
       "      <th>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Wahlkreis</th>\n",
       "      <th>Bezirksart</th>\n",
       "      <th>Wahlberechtigte (A)</th>\n",
       "      <th>Wahlberechtigte ohne Sperrvermerk (A1)</th>\n",
       "      <th>Wahlberechtigte mit Sperrvermerk (A2)</th>\n",
       "      <th>...</th>\n",
       "      <th>Wähler (B)</th>\n",
       "      <th>Wähler mit Wahlschein (B1)</th>\n",
       "      <th>Ungültig</th>\n",
       "      <th>Gültig</th>\n",
       "      <th>SPD</th>\n",
       "      <th>FDP</th>\n",
       "      <th>GRÜNE</th>\n",
       "      <th>Andere</th>\n",
       "      <th>Union</th>\n",
       "      <th>Linke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>68987</td>\n",
       "      <td>61031</td>\n",
       "      <td>7956</td>\n",
       "      <td>...</td>\n",
       "      <td>52379</td>\n",
       "      <td>7535</td>\n",
       "      <td>556.0</td>\n",
       "      <td>51823.0</td>\n",
       "      <td>24172</td>\n",
       "      <td>3404</td>\n",
       "      <td>4651</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>17837</td>\n",
       "      <td>700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>65755</td>\n",
       "      <td>58545</td>\n",
       "      <td>7210</td>\n",
       "      <td>...</td>\n",
       "      <td>50560</td>\n",
       "      <td>6678</td>\n",
       "      <td>698.0</td>\n",
       "      <td>49862.0</td>\n",
       "      <td>25490</td>\n",
       "      <td>3182</td>\n",
       "      <td>3815</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>14538</td>\n",
       "      <td>912.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>65740</td>\n",
       "      <td>57788</td>\n",
       "      <td>7949</td>\n",
       "      <td>...</td>\n",
       "      <td>49054</td>\n",
       "      <td>7573</td>\n",
       "      <td>501.0</td>\n",
       "      <td>48553.0</td>\n",
       "      <td>23396</td>\n",
       "      <td>3424</td>\n",
       "      <td>5556</td>\n",
       "      <td>956.0</td>\n",
       "      <td>14362</td>\n",
       "      <td>859.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>66970</td>\n",
       "      <td>58379</td>\n",
       "      <td>8591</td>\n",
       "      <td>...</td>\n",
       "      <td>49002</td>\n",
       "      <td>8218</td>\n",
       "      <td>767.0</td>\n",
       "      <td>48235.0</td>\n",
       "      <td>21000</td>\n",
       "      <td>4196</td>\n",
       "      <td>5028</td>\n",
       "      <td>1035.0</td>\n",
       "      <td>13910</td>\n",
       "      <td>3066.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>69075</td>\n",
       "      <td>60983</td>\n",
       "      <td>8092</td>\n",
       "      <td>...</td>\n",
       "      <td>46583</td>\n",
       "      <td>7238</td>\n",
       "      <td>1152.0</td>\n",
       "      <td>45431.0</td>\n",
       "      <td>12739</td>\n",
       "      <td>6530</td>\n",
       "      <td>7323</td>\n",
       "      <td>2334.0</td>\n",
       "      <td>11238</td>\n",
       "      <td>5267.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   year       AGS  second_vote Land Kreis  Wahlkreis  Bezirksart  \\\n",
       "0  1994  01001000            1   01  0101          1           0   \n",
       "1  1998  01001000            1   01  0101          1           0   \n",
       "2  2002  01001000            1   01  0101          1           0   \n",
       "3  2005  01001000            1   01  0101          1           0   \n",
       "4  2009  01001000            1   01  0101          1           0   \n",
       "\n",
       "   Wahlberechtigte (A)  Wahlberechtigte ohne Sperrvermerk (A1)  \\\n",
       "0                68987                                   61031   \n",
       "1                65755                                   58545   \n",
       "2                65740                                   57788   \n",
       "3                66970                                   58379   \n",
       "4                69075                                   60983   \n",
       "\n",
       "   Wahlberechtigte mit Sperrvermerk (A2)  ...  Wähler (B)  \\\n",
       "0                                   7956  ...       52379   \n",
       "1                                   7210  ...       50560   \n",
       "2                                   7949  ...       49054   \n",
       "3                                   8591  ...       49002   \n",
       "4                                   8092  ...       46583   \n",
       "\n",
       "   Wähler mit Wahlschein (B1)  Ungültig   Gültig    SPD   FDP  GRÜNE  Andere  \\\n",
       "0                        7535     556.0  51823.0  24172  3404   4651  1059.0   \n",
       "1                        6678     698.0  49862.0  25490  3182   3815  1925.0   \n",
       "2                        7573     501.0  48553.0  23396  3424   5556   956.0   \n",
       "3                        8218     767.0  48235.0  21000  4196   5028  1035.0   \n",
       "4                        7238    1152.0  45431.0  12739  6530   7323  2334.0   \n",
       "\n",
       "   Union   Linke  \n",
       "0  17837   700.0  \n",
       "1  14538   912.0  \n",
       "2  14362   859.0  \n",
       "3  13910  3066.0  \n",
       "4  11238  5267.0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_drop = andere + union + linke + ['Ungültige', 'Gültige', 'Verbandsgemeinde', 'Regierungsbezirk', 'Gemeinde', 'Wahlbezirk', 'Kennziffer Briefwahlzugehörigkeit', 'Ungekürzte Wahlbezirksbezeichnung']\n",
    "btw_zs_sum.drop(columns_to_drop, axis=1, inplace=True)\n",
    "btw_zs_sum.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concatenate first and second vote datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(156252, 22)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs = [btw_es_sum, btw_zs_sum]\n",
    "btw_eszs = pd.concat(dfs, sort=False)\n",
    "btw_eszs = btw_eszs.sort_values(['AGS', 'year'])\n",
    "btw_eszs.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop postal votes for Robustness check (uncomment to run)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(156252, 22)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#btw_eszs = btw_eszs[btw_eszs['Bezirksart'] == 0]\n",
    "btw_eszs.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "View balacing of panel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1994    22334\n",
       "2009    22326\n",
       "1998    22322\n",
       "2005    22320\n",
       "2002    22318\n",
       "2013    22318\n",
       "2017    22314\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_eszs.year.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace Land values with corresponding state abbreviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11',\n",
       "       '12', '13', '14', '15', '16'], dtype=object)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_eszs['Land'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SH', 'HH', 'NI', 'HB', 'NW', 'HE', 'RP', 'BW', 'BY', 'SL', 'BE',\n",
       "       'BB', 'MV', 'SN', 'ST', 'TH'], dtype=object)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states = {'01': 'SH', '02': 'HH', '03': 'NI', '04': 'HB', '05': 'NW', '06': 'HE', '07': 'RP', '08': 'BW', \\\n",
    "         '09': 'BY', '10': 'SL', '11': 'BE', '12': 'BB', '13': 'MV', '14': 'SN', '15': 'ST', '16': 'TH'}\n",
    "btw_eszs['state_id'] = btw_eszs['Land']\n",
    "for key, value in states.items():\n",
    "    btw_eszs['Land'] = np.where(btw_eszs['Land'] == key, value, btw_eszs['Land'])\n",
    "btw_eszs['Land'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop not needed columns and rename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(156252, 18)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_eszs.rename(columns = {'Wahlberechtigte (A)': 'Wahlberechtigte', 'GRÜNE': 'Grüne', 'Wähler (B)': 'Wähler'}, inplace = True)\n",
    "columns_to_drop = ['Bezirksart',\n",
    "       'Wahlberechtigte ohne Sperrvermerk (A1)',\n",
    "       'Wahlberechtigte mit Sperrvermerk (A2)',\n",
    "       'Wahlberechtigte nach § 25 Abs. 2 BWO (A3)',\n",
    "       'Wähler mit Wahlschein (B1)']\n",
    "btw_eszs.drop(columns_to_drop, axis=1, inplace=True)\n",
    "btw_eszs.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since vote percentages are calculated based on the 'valid' votes and there are some rows with valid == 0, calculate the vote shares for these (Voters - Invalid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_eszs.loc[:, 'Gültig'] = np.where(btw_eszs['Gültig'] == 0, btw_eszs['Wähler'] - btw_eszs['Ungültig'], btw_eszs['Gültig'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check summation of party and invalid votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "parties = ['Union', 'SPD', 'FDP', 'Linke', 'Grüne', 'Andere', 'Ungültig']\n",
    "btw_eszs.loc[:, 'votes'] = btw_eszs[parties].sum(axis=1)\n",
    "btw_eszs.loc[:, 'votes'] = btw_eszs['votes'].astype(int) #convert to integer\n",
    "print(btw_eszs['votes'].equals(btw_eszs['Wähler'])) # check if votes == Wähler\n",
    "btw_eszs.drop(['votes'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check summation of valid and invalid votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "votes = ['Gültig', 'Ungültig']\n",
    "btw_eszs['votes'] = btw_eszs[votes].sum(axis=1)\n",
    "btw_eszs['votes'] = btw_eszs['votes'].astype(int) #convert to integer\n",
    "print(btw_eszs['votes'].equals(btw_eszs['Wähler'])) # check if votes == Wähler\n",
    "btw_eszs.drop(['votes', 'Ungültig'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate vote shares in percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>year</th>\n",
       "      <th>AGS</th>\n",
       "      <th>first_vote</th>\n",
       "      <th>Land</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Wahlkreis</th>\n",
       "      <th>Wahlberechtigte</th>\n",
       "      <th>Wähler</th>\n",
       "      <th>Gültig</th>\n",
       "      <th>SPD</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Grüne</th>\n",
       "      <th>Andere</th>\n",
       "      <th>Union</th>\n",
       "      <th>Linke</th>\n",
       "      <th>second_vote</th>\n",
       "      <th>state_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>68987</td>\n",
       "      <td>75.925899</td>\n",
       "      <td>51827.0</td>\n",
       "      <td>50.026048</td>\n",
       "      <td>1.954580</td>\n",
       "      <td>6.770602</td>\n",
       "      <td>1.921778</td>\n",
       "      <td>39.326992</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>01001000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>68987</td>\n",
       "      <td>75.925899</td>\n",
       "      <td>51823.0</td>\n",
       "      <td>46.643382</td>\n",
       "      <td>6.568512</td>\n",
       "      <td>8.974780</td>\n",
       "      <td>2.043494</td>\n",
       "      <td>34.419080</td>\n",
       "      <td>1.350752</td>\n",
       "      <td>1.0</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>65755</td>\n",
       "      <td>76.891491</td>\n",
       "      <td>49887.0</td>\n",
       "      <td>55.403211</td>\n",
       "      <td>1.725901</td>\n",
       "      <td>4.009060</td>\n",
       "      <td>1.798064</td>\n",
       "      <td>35.788883</td>\n",
       "      <td>1.274881</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1998</td>\n",
       "      <td>01001000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>65755</td>\n",
       "      <td>76.891491</td>\n",
       "      <td>49862.0</td>\n",
       "      <td>51.121094</td>\n",
       "      <td>6.381613</td>\n",
       "      <td>7.651117</td>\n",
       "      <td>3.860655</td>\n",
       "      <td>29.156472</td>\n",
       "      <td>1.829048</td>\n",
       "      <td>1.0</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>65740</td>\n",
       "      <td>74.618193</td>\n",
       "      <td>48551.0</td>\n",
       "      <td>54.188379</td>\n",
       "      <td>4.197648</td>\n",
       "      <td>5.835101</td>\n",
       "      <td>0.399580</td>\n",
       "      <td>33.815987</td>\n",
       "      <td>1.563305</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>01001000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>65740</td>\n",
       "      <td>74.618193</td>\n",
       "      <td>48553.0</td>\n",
       "      <td>48.186518</td>\n",
       "      <td>7.052087</td>\n",
       "      <td>11.443165</td>\n",
       "      <td>1.968982</td>\n",
       "      <td>29.580047</td>\n",
       "      <td>1.769201</td>\n",
       "      <td>1.0</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>66970</td>\n",
       "      <td>73.170076</td>\n",
       "      <td>48244.0</td>\n",
       "      <td>50.288119</td>\n",
       "      <td>3.318547</td>\n",
       "      <td>5.656662</td>\n",
       "      <td>0.802172</td>\n",
       "      <td>35.142194</td>\n",
       "      <td>4.792306</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>01001000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>66970</td>\n",
       "      <td>73.170076</td>\n",
       "      <td>48235.0</td>\n",
       "      <td>43.536851</td>\n",
       "      <td>8.699077</td>\n",
       "      <td>10.423966</td>\n",
       "      <td>2.145745</td>\n",
       "      <td>28.837981</td>\n",
       "      <td>6.356380</td>\n",
       "      <td>1.0</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>01001000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>69075</td>\n",
       "      <td>67.438292</td>\n",
       "      <td>45379.0</td>\n",
       "      <td>36.475021</td>\n",
       "      <td>9.372177</td>\n",
       "      <td>12.164217</td>\n",
       "      <td>1.097424</td>\n",
       "      <td>30.540558</td>\n",
       "      <td>10.350603</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>01001000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SH</td>\n",
       "      <td>0101</td>\n",
       "      <td>1</td>\n",
       "      <td>69075</td>\n",
       "      <td>67.438292</td>\n",
       "      <td>45431.0</td>\n",
       "      <td>28.040325</td>\n",
       "      <td>14.373445</td>\n",
       "      <td>16.118950</td>\n",
       "      <td>5.137461</td>\n",
       "      <td>24.736413</td>\n",
       "      <td>11.593405</td>\n",
       "      <td>1.0</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year       AGS  first_vote Land Kreis  Wahlkreis  Wahlberechtigte  \\\n",
       "0  1994  01001000         1.0   SH  0101          1            68987   \n",
       "0  1994  01001000         NaN   SH  0101          1            68987   \n",
       "1  1998  01001000         1.0   SH  0101          1            65755   \n",
       "1  1998  01001000         NaN   SH  0101          1            65755   \n",
       "2  2002  01001000         1.0   SH  0101          1            65740   \n",
       "2  2002  01001000         NaN   SH  0101          1            65740   \n",
       "3  2005  01001000         1.0   SH  0101          1            66970   \n",
       "3  2005  01001000         NaN   SH  0101          1            66970   \n",
       "4  2009  01001000         1.0   SH  0101          1            69075   \n",
       "4  2009  01001000         NaN   SH  0101          1            69075   \n",
       "\n",
       "      Wähler   Gültig        SPD        FDP      Grüne    Andere      Union  \\\n",
       "0  75.925899  51827.0  50.026048   1.954580   6.770602  1.921778  39.326992   \n",
       "0  75.925899  51823.0  46.643382   6.568512   8.974780  2.043494  34.419080   \n",
       "1  76.891491  49887.0  55.403211   1.725901   4.009060  1.798064  35.788883   \n",
       "1  76.891491  49862.0  51.121094   6.381613   7.651117  3.860655  29.156472   \n",
       "2  74.618193  48551.0  54.188379   4.197648   5.835101  0.399580  33.815987   \n",
       "2  74.618193  48553.0  48.186518   7.052087  11.443165  1.968982  29.580047   \n",
       "3  73.170076  48244.0  50.288119   3.318547   5.656662  0.802172  35.142194   \n",
       "3  73.170076  48235.0  43.536851   8.699077  10.423966  2.145745  28.837981   \n",
       "4  67.438292  45379.0  36.475021   9.372177  12.164217  1.097424  30.540558   \n",
       "4  67.438292  45431.0  28.040325  14.373445  16.118950  5.137461  24.736413   \n",
       "\n",
       "       Linke  second_vote state_id  \n",
       "0   0.000000          NaN       01  \n",
       "0   1.350752          1.0       01  \n",
       "1   1.274881          NaN       01  \n",
       "1   1.829048          1.0       01  \n",
       "2   1.563305          NaN       01  \n",
       "2   1.769201          1.0       01  \n",
       "3   4.792306          NaN       01  \n",
       "3   6.356380          1.0       01  \n",
       "4  10.350603          NaN       01  \n",
       "4  11.593405          1.0       01  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parties = ['Union',\t'SPD', 'FDP', 'Linke', 'Grüne', 'Andere']\n",
    "for party in parties:\n",
    "    btw_eszs.loc[:, party] = (btw_eszs[party] / btw_eszs['Gültig'])*100\n",
    "btw_eszs.loc[:, 'Wähler'] = (btw_eszs['Wähler'] / btw_eszs['Wahlberechtigte'])*100\n",
    "btw_eszs.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean dataset and export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_eszs = btw_eszs[['AGS', 'year', 'Land', 'state_id', 'Kreis', 'Wahlkreis', 'first_vote', 'second_vote', 'Wähler', 'Union', 'SPD', 'FDP', 'Linke', 'Grüne', 'Andere']]\n",
    "btw_eszs = btw_eszs.set_index(['AGS', 'year'])\n",
    "btw_eszs.to_csv(f'{path}/data/btw_eszs.csv', encoding = 'utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Group Erst- and Zweitstimme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw = btw_eszs.reset_index()\n",
    "cols_to_keep = ['Land', 'state_id', 'Kreis', 'Wahlkreis',]\n",
    "cols_to_sum = ['Wähler', 'Union', 'SPD', 'FDP', 'Linke', 'Grüne', 'Andere']\n",
    "btw_grouped = btw.groupby(['AGS', 'year'], as_index=False).agg({**{e:'first' for e in cols_to_keep}, **{e:'mean' for e in cols_to_sum}})\n",
    "btw = btw_grouped.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop obs. with missing values for election results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(78123, 13)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw = btw.dropna(axis='rows', how='any', subset=['Union', 'SPD', 'FDP', 'Linke', 'Grüne', 'Andere'])\n",
    "btw.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge with treatment dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          78110\n",
       "right_only      219\n",
       "left_only        13\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "treatment = pd.read_csv(f'{path}/data/treatment.csv', converters = {'AGS': str})\n",
    "btw_treat = pd.merge(btw, treatment, on='AGS', how='outer', indicator=True)\n",
    "btw_treat['_merge'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean data and export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_treat = btw_treat[btw_treat['_merge'] == 'both']\n",
    "btw_treat = btw_treat.drop(columns=['_merge'])\n",
    "btw_treat = btw_treat.set_index(['AGS', 'GEN', 'year'])\n",
    "btw_treat.to_csv(f'{path}/data/btw_treat.csv', encoding = 'utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge in control variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          78110\n",
       "left_only         0\n",
       "right_only        0\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_treat = btw_treat.reset_index()\n",
    "controls = pd.read_csv(f'{path}/data/controls.csv', converters = {'AGS': str})\n",
    "btw_control = pd.merge(btw_treat, controls, on=['AGS', 'year'], how='left', indicator=True)\n",
    "btw_control['_merge'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "btw_control = btw_control.set_index(['AGS', 'year'])\n",
    "btw_control = btw_control.drop(columns=['_merge'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add dummy for East and South Germany"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SH', 'HH', 'NI', 'HB', 'NW', 'HE', 'RP', 'BW', 'BY', 'SL', 'BE',\n",
       "       'BB', 'MV', 'SN', 'ST', 'TH'], dtype=object)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_control['Land'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "east = ['BE', 'BB', 'MV', 'SN', 'ST', 'TH']\n",
    "south = ['BW', 'BY']\n",
    "btw_control['east'] = np.where(btw_control['Land'].isin(east), 1, 0)\n",
    "btw_control['south'] = np.where(btw_control['Land'].isin(south), 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    59147\n",
       "1    18963\n",
       "Name: east, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_control['east'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    56006\n",
       "1    22104\n",
       "Name: south, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_control['south'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Export dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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></th>\n",
       "      <th>GEN</th>\n",
       "      <th>Land</th>\n",
       "      <th>state_id</th>\n",
       "      <th>Kreis</th>\n",
       "      <th>Wahlkreis</th>\n",
       "      <th>Wähler</th>\n",
       "      <th>Union</th>\n",
       "      <th>SPD</th>\n",
       "      <th>FDP</th>\n",
       "      <th>Linke</th>\n",
       "      <th>...</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",
       "      <th>east</th>\n",
       "      <th>south</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGS</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">01001000</th>\n",
       "      <th>1994.0</th>\n",
       "      <td>Flensburg</td>\n",
       "      <td>SH</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>75.925899</td>\n",
       "      <td>36.873036</td>\n",
       "      <td>48.334715</td>\n",
       "      <td>4.261546</td>\n",
       "      <td>0.675376</td>\n",
       "      <td>...</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1558.114812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998.0</th>\n",
       "      <td>Flensburg</td>\n",
       "      <td>SH</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>76.891491</td>\n",
       "      <td>32.472677</td>\n",
       "      <td>53.262153</td>\n",
       "      <td>4.053757</td>\n",
       "      <td>1.551965</td>\n",
       "      <td>...</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1501.417434</td>\n",
       "      <td>6.825423</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.73</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002.0</th>\n",
       "      <td>Flensburg</td>\n",
       "      <td>SH</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>74.618193</td>\n",
       "      <td>31.698017</td>\n",
       "      <td>51.187449</td>\n",
       "      <td>5.624868</td>\n",
       "      <td>1.666253</td>\n",
       "      <td>...</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1502.305782</td>\n",
       "      <td>5.994994</td>\n",
       "      <td>41.13</td>\n",
       "      <td>51.60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005.0</th>\n",
       "      <td>Flensburg</td>\n",
       "      <td>SH</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>73.170076</td>\n",
       "      <td>31.990087</td>\n",
       "      <td>46.912485</td>\n",
       "      <td>6.008812</td>\n",
       "      <td>5.574343</td>\n",
       "      <td>...</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1526.782547</td>\n",
       "      <td>8.737221</td>\n",
       "      <td>41.25</td>\n",
       "      <td>51.25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009.0</th>\n",
       "      <td>Flensburg</td>\n",
       "      <td>SH</td>\n",
       "      <td>01</td>\n",
       "      <td>0101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>67.438292</td>\n",
       "      <td>27.638486</td>\n",
       "      <td>32.257673</td>\n",
       "      <td>11.872811</td>\n",
       "      <td>10.972004</td>\n",
       "      <td>...</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>Within 0-15 km</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1559.746211</td>\n",
       "      <td>6.236017</td>\n",
       "      <td>41.52</td>\n",
       "      <td>50.68</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       GEN Land state_id Kreis  Wahlkreis     Wähler  \\\n",
       "AGS      year                                                          \n",
       "01001000 1994.0  Flensburg   SH       01  0101        1.0  75.925899   \n",
       "         1998.0  Flensburg   SH       01  0101        1.0  76.891491   \n",
       "         2002.0  Flensburg   SH       01  0101        1.0  74.618193   \n",
       "         2005.0  Flensburg   SH       01  0101        1.0  73.170076   \n",
       "         2009.0  Flensburg   SH       01  0101        1.0  67.438292   \n",
       "\n",
       "                     Union        SPD        FDP      Linke  ...  \\\n",
       "AGS      year                                                ...   \n",
       "01001000 1994.0  36.873036  48.334715   4.261546   0.675376  ...   \n",
       "         1998.0  32.472677  53.262153   4.053757   1.551965  ...   \n",
       "         2002.0  31.698017  51.187449   5.624868   1.666253  ...   \n",
       "         2005.0  31.990087  46.912485   6.008812   5.574343  ...   \n",
       "         2009.0  27.638486  32.257673  11.872811  10.972004  ...   \n",
       "\n",
       "                 treatment_50    ever_treated ever_treated_2010 never_treated  \\\n",
       "AGS      year                                                                   \n",
       "01001000 1994.0        2010.0  Within 0-15 km    Within 0-15 km           0.0   \n",
       "         1998.0        2010.0  Within 0-15 km    Within 0-15 km           0.0   \n",
       "         2002.0        2010.0  Within 0-15 km    Within 0-15 km           0.0   \n",
       "         2005.0        2010.0  Within 0-15 km    Within 0-15 km           0.0   \n",
       "         2009.0        2010.0  Within 0-15 km    Within 0-15 km           0.0   \n",
       "\n",
       "                 pop_density  unemployed  avg_age  female  east south  \n",
       "AGS      year                                                          \n",
       "01001000 1994.0  1558.114812         NaN      NaN     NaN     0     0  \n",
       "         1998.0  1501.417434    6.825423      NaN   51.73     0     0  \n",
       "         2002.0  1502.305782    5.994994    41.13   51.60     0     0  \n",
       "         2005.0  1526.782547    8.737221    41.25   51.25     0     0  \n",
       "         2009.0  1559.746211    6.236017    41.52   50.68     0     0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btw_control.to_csv(f'{path}/data/btw_control.csv', encoding = 'utf-8-sig')\n",
    "btw_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
}