seat_calculus.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.ensemble import RandomForestRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Prediction</th>\n",
       "      <th>Votes</th>\n",
       "      <th>city</th>\n",
       "      <th>party</th>\n",
       "      <th>last_name</th>\n",
       "      <th>city_id</th>\n",
       "      <th>county_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>place_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25682.343534</td>\n",
       "      <td>NaN</td>\n",
       "      <td>München</td>\n",
       "      <td>AfD</td>\n",
       "      <td>Wassill</td>\n",
       "      <td>9162</td>\n",
       "      <td>9162</td>\n",
       "      <td>Iris</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>25287.080224</td>\n",
       "      <td>NaN</td>\n",
       "      <td>München</td>\n",
       "      <td>AfD</td>\n",
       "      <td>Walbrunn</td>\n",
       "      <td>9162</td>\n",
       "      <td>9162</td>\n",
       "      <td>Markus</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>25418.300274</td>\n",
       "      <td>NaN</td>\n",
       "      <td>München</td>\n",
       "      <td>AfD</td>\n",
       "      <td>Stanke</td>\n",
       "      <td>9162</td>\n",
       "      <td>9162</td>\n",
       "      <td>Daniel</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>24319.455760</td>\n",
       "      <td>NaN</td>\n",
       "      <td>München</td>\n",
       "      <td>AfD</td>\n",
       "      <td>Reuter</td>\n",
       "      <td>9162</td>\n",
       "      <td>9162</td>\n",
       "      <td>Andreas</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>25817.278687</td>\n",
       "      <td>NaN</td>\n",
       "      <td>München</td>\n",
       "      <td>AfD</td>\n",
       "      <td>Klemp</td>\n",
       "      <td>9162</td>\n",
       "      <td>9162</td>\n",
       "      <td>Roland</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13225</td>\n",
       "      <td>13225</td>\n",
       "      <td>3440.048857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gunzenhausen</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Röhl</td>\n",
       "      <td>9577136</td>\n",
       "      <td>9577</td>\n",
       "      <td>Cornelia</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13226</td>\n",
       "      <td>13226</td>\n",
       "      <td>3453.342702</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gunzenhausen</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Müller</td>\n",
       "      <td>9577136</td>\n",
       "      <td>9577</td>\n",
       "      <td>Alfred</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13227</td>\n",
       "      <td>13227</td>\n",
       "      <td>2879.466851</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gunzenhausen</td>\n",
       "      <td>Grüne</td>\n",
       "      <td>Schnell</td>\n",
       "      <td>9577136</td>\n",
       "      <td>9577</td>\n",
       "      <td>Peter</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13228</td>\n",
       "      <td>13228</td>\n",
       "      <td>3491.557692</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gunzenhausen</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Hinderks</td>\n",
       "      <td>9577136</td>\n",
       "      <td>9577</td>\n",
       "      <td>Daniel</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13229</td>\n",
       "      <td>13229</td>\n",
       "      <td>3196.515307</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gunzenhausen</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Wopperer</td>\n",
       "      <td>9577136</td>\n",
       "      <td>9577</td>\n",
       "      <td>Monika</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13230 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0    Prediction  Votes          city  party last_name  \\\n",
       "0               0  25682.343534    NaN       München    AfD   Wassill   \n",
       "1               1  25287.080224    NaN       München    AfD  Walbrunn   \n",
       "2               2  25418.300274    NaN       München    AfD    Stanke   \n",
       "3               3  24319.455760    NaN       München    AfD    Reuter   \n",
       "4               4  25817.278687    NaN       München    AfD     Klemp   \n",
       "...           ...           ...    ...           ...    ...       ...   \n",
       "13225       13225   3440.048857    NaN  Gunzenhausen    SPD      Röhl   \n",
       "13226       13226   3453.342702    NaN  Gunzenhausen    SPD    Müller   \n",
       "13227       13227   2879.466851    NaN  Gunzenhausen  Grüne   Schnell   \n",
       "13228       13228   3491.557692    NaN  Gunzenhausen    SPD  Hinderks   \n",
       "13229       13229   3196.515307    NaN  Gunzenhausen    SPD  Wopperer   \n",
       "\n",
       "       city_id  county_id first_name  place_list  \n",
       "0         9162       9162       Iris           1  \n",
       "1         9162       9162     Markus           2  \n",
       "2         9162       9162     Daniel           3  \n",
       "3         9162       9162    Andreas           4  \n",
       "4         9162       9162     Roland           5  \n",
       "...        ...        ...        ...         ...  \n",
       "13225  9577136       9577   Cornelia           5  \n",
       "13226  9577136       9577     Alfred           8  \n",
       "13227  9577136       9577      Peter           4  \n",
       "13228  9577136       9577     Daniel           4  \n",
       "13229  9577136       9577     Monika           7  \n",
       "\n",
       "[13230 rows x 10 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe = pd.read_excel(r'C:\\Users\\mariu\\Documents\\Project Local Elections\\forecast_candidates.xlsx')\n",
    "dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[1166080.003534426, 'AfD', 'München'],\n",
       " [629220.4597858847, 'BIA', 'München'],\n",
       " [702410.5502588446, 'BP', 'München'],\n",
       " [10183654.1763767, 'CSU', 'München'],\n",
       " [783426.009830417, 'FAIR', 'München'],\n",
       " [1798765.760657481, 'FDP', 'München'],\n",
       " [1635113.1800019, 'FW', 'München'],\n",
       " [5848403.943984292, 'Grüne', 'München'],\n",
       " [1666343.605055456, 'Linke', 'München'],\n",
       " [813742.6644227188, 'München Liste', 'München'],\n",
       " [1145059.289494705, 'mut', 'München'],\n",
       " [1690168.7271471955, 'ödp', 'München'],\n",
       " [752202.1482241091, 'Partei', 'München'],\n",
       " [1661976.591556192, 'RoLi', 'München'],\n",
       " [9153605.189513817, 'SPD', 'München'],\n",
       " [774338.9945886574, 'volt', 'München'],\n",
       " [733076.2696563189, 'Zusammen Bayern', 'München'],\n",
       " [40940.485318721185, 'AfD', 'Rosenheim'],\n",
       " [338192.2479389429, 'CSU', 'Rosenheim'],\n",
       " [64849.14722968236, 'FDP', 'Rosenheim'],\n",
       " [100252.94679197931, 'FW', 'Rosenheim'],\n",
       " [168786.62258393122, 'Grüne', 'Rosenheim'],\n",
       " [74143.20472231327, 'ödp', 'Rosenheim'],\n",
       " [48678.1759243711, 'REP', 'Rosenheim'],\n",
       " [235614.7043568589, 'SPD', 'Rosenheim'],\n",
       " [50004.328834500106, 'V3-Partei', 'Rosenheim'],\n",
       " [16988.498535227256, 'AfD', 'Burghausen'],\n",
       " [176180.7633081191, 'CSU', 'Burghausen'],\n",
       " [40159.29480176329, 'FDP', 'Burghausen'],\n",
       " [82441.58928153763, 'Grüne', 'Burghausen'],\n",
       " [118674.30503766335, 'SPD', 'Burghausen'],\n",
       " [55511.216551011996, 'UW Burghausen', 'Burghausen'],\n",
       " [26580.64433926699, 'AfD', 'Dachau'],\n",
       " [90453.08612693804, 'Bündnis Dachau', 'Dachau'],\n",
       " [45441.57639259621, 'Bürger für Dachau', 'Dachau'],\n",
       " [343469.52943160007, 'CSU', 'Dachau'],\n",
       " [33630.034148989005, 'FDP', 'Dachau'],\n",
       " [76004.59672997869, 'FW', 'Dachau'],\n",
       " [140061.07434078847, 'Grüne', 'Dachau'],\n",
       " [201926.29059068585, 'SPD', 'Dachau'],\n",
       " [91488.67354654419, 'Überparteiliches Dachau', 'Dachau'],\n",
       " [89238.9491023455, 'Bündnis für Karlsfeld', 'Karlsfeld'],\n",
       " [173823.4737169468, 'CSU', 'Karlsfeld'],\n",
       " [9958.89838675877, 'FDP', 'Karlsfeld'],\n",
       " [28654.25343073239, 'FW', 'Karlsfeld'],\n",
       " [63189.21600586689, 'Grüne', 'Karlsfeld'],\n",
       " [137016.55419452908, 'SPD', 'Karlsfeld'],\n",
       " [75893.04884811504, 'CSU', 'Markt Schwaben'],\n",
       " [5961.927299669374, 'FDP', 'Markt Schwaben'],\n",
       " [28287.677833250335, 'FW', 'Markt Schwaben'],\n",
       " [29363.202554022057, 'Grüne', 'Markt Schwaben'],\n",
       " [42499.29572898549, 'SPD', 'Markt Schwaben'],\n",
       " [11905.9566739538, 'AfD', 'Dorfen'],\n",
       " [76760.86007309039, 'CSU', 'Dorfen'],\n",
       " [26822.200807136916, 'Dorfen West', 'Dorfen'],\n",
       " [24669.49549686005, 'EWG', 'Dorfen'],\n",
       " [25728.022506204958, 'Gemeinwohl Schwindkirchen', 'Dorfen'],\n",
       " [28959.152619299213, 'Grüne', 'Dorfen'],\n",
       " [37316.49102893548, 'SPD', 'Dorfen'],\n",
       " [27881.552221285776, 'TEG', 'Dorfen'],\n",
       " [25859.73209994356, 'ÜWG Dorfen', 'Dorfen'],\n",
       " [8630.525152811359, 'Grüne AL', 'Dorfen'],\n",
       " [28794.78303339648, 'AfD', 'Freising'],\n",
       " [309610.68487001024, 'CSU', 'Freising'],\n",
       " [46427.55866196671, 'FDP', 'Freising'],\n",
       " [98629.2624311656, 'Freisinger Mitte', 'Freising'],\n",
       " [82577.83615422438, 'FW', 'Freising'],\n",
       " [149410.8999356515, 'Grüne', 'Freising'],\n",
       " [73561.4924417663, 'Linke', 'Freising'],\n",
       " [67653.72844309558, 'ödp', 'Freising'],\n",
       " [194835.68601459666, 'SPD', 'Freising'],\n",
       " [174338.48140101111, 'BBV', 'Fürstenfeldbruck'],\n",
       " [599672.300057671, 'CSU', 'Fürstenfeldbruck'],\n",
       " [147942.4527055318, 'FDP', 'Fürstenfeldbruck'],\n",
       " [158532.58896165615, 'FW', 'Fürstenfeldbruck'],\n",
       " [285635.18438081443, 'Grüne', 'Fürstenfeldbruck'],\n",
       " [52901.226248790845, 'Linke', 'Fürstenfeldbruck'],\n",
       " [113334.43009439489, 'ödp', 'Fürstenfeldbruck'],\n",
       " [160332.47756745495, 'Partei', 'Fürstenfeldbruck'],\n",
       " [495890.4343482363, 'SPD', 'Fürstenfeldbruck'],\n",
       " [686446.6229109466, 'CSU', 'Germering'],\n",
       " [61596.855581760086, 'FDP', 'Germering'],\n",
       " [94320.44368941769, 'FW', 'Germering'],\n",
       " [365329.7044414558, 'Grüne', 'Germering'],\n",
       " [39218.739738931414, 'Linke', 'Germering'],\n",
       " [149292.63954423065, 'ödp', 'Germering'],\n",
       " [547168.8428160859, 'SPD', 'Germering'],\n",
       " [215116.121229354, 'CSU', 'Maisach'],\n",
       " [38590.99283418511, 'FDP', 'Maisach'],\n",
       " [99624.19139400151, 'FW', 'Maisach'],\n",
       " [113950.02348176244, 'Grüne', 'Maisach'],\n",
       " [267630.3644584921, 'SPD', 'Maisach'],\n",
       " [55684.45472500555, 'AfD', 'Unterschleißheim'],\n",
       " [408357.960992346, 'CSU', 'Unterschleißheim'],\n",
       " [85869.13473118415, 'FDP', 'Unterschleißheim'],\n",
       " [137367.69670164297, 'FW', 'Unterschleißheim'],\n",
       " [192891.41565159982, 'Grüne', 'Unterschleißheim'],\n",
       " [102113.08837583513, 'ödp', 'Unterschleißheim'],\n",
       " [458921.13038275216, 'SPD', 'Unterschleißheim'],\n",
       " [108809.2210315127, 'CSU', 'Pfaffenhofen'],\n",
       " [9903.42908956004, 'FDP', 'Pfaffenhofen'],\n",
       " [32509.81931167558, 'FW', 'Pfaffenhofen'],\n",
       " [22604.972647008755, 'Gemeinsam für Gemeinwohl', 'Pfaffenhofen'],\n",
       " [38820.46637638604, 'Grüne', 'Pfaffenhofen'],\n",
       " [31613.06685393262, 'ödp', 'Pfaffenhofen'],\n",
       " [80412.47600714455, 'SPD', 'Pfaffenhofen'],\n",
       " [9508.943884473187, 'AfD', 'Bad Aibling'],\n",
       " [659.9643119487118, 'BP', 'Bad Aibling'],\n",
       " [104553.59836097686, 'CSU', 'Bad Aibling'],\n",
       " [6304.969318344771, 'FDP', 'Bad Aibling'],\n",
       " [36699.38960093628, 'Grüne', 'Bad Aibling'],\n",
       " [8392.571833902903, 'Linke', 'Bad Aibling'],\n",
       " [10410.428927862082, 'ödp', 'Bad Aibling'],\n",
       " [55624.50675324307, 'SPD', 'Bad Aibling'],\n",
       " [32943.15436205234, 'ÜW Bad Aibling', 'Bad Aibling'],\n",
       " [77202.99455280599, 'Bürger für Penzberg', 'Penzberg'],\n",
       " [141948.91982863675, 'CSU', 'Penzberg'],\n",
       " [24184.330443047373, 'FDP', 'Penzberg'],\n",
       " [78163.27130802974, 'Freie Lokalpolitik Penzberg', 'Penzberg'],\n",
       " [39915.85039807551, 'Grüne', 'Penzberg'],\n",
       " [78657.4816012283, 'Penzberg Miteinander', 'Penzberg'],\n",
       " [95715.4436562897, 'SPD', 'Penzberg'],\n",
       " [41492.34441479876, 'AfD', 'Landshut'],\n",
       " [60219.255621037926, 'BP', 'Landshut'],\n",
       " [85714.75221235861, 'Bürger für Landshut', 'Landshut'],\n",
       " [390444.90114208875, 'CSU', 'Landshut'],\n",
       " [71925.37597355609, 'FDP', 'Landshut'],\n",
       " [77841.67601291371, 'FW', 'Landshut'],\n",
       " [197733.69433878863, 'Grüne', 'Landshut'],\n",
       " [53337.316854483164, 'Junge Wähler', 'Landshut'],\n",
       " [84383.41718899722, 'Junges Landshut', 'Landshut'],\n",
       " [103646.91027041734, 'Landshuter Mitte', 'Landshut'],\n",
       " [41352.635922151036, 'Linke', 'Landshut'],\n",
       " [76815.79317041572, 'ödp', 'Landshut'],\n",
       " [259507.16996123004, 'SPD', 'Landshut'],\n",
       " [24423.13077736044, 'AfD', 'Passau'],\n",
       " [312467.82152685063, 'CSU', 'Passau'],\n",
       " [24738.19186921191, 'FDP', 'Passau'],\n",
       " [55997.697863490524, 'FW', 'Passau'],\n",
       " [96190.6561381555, 'Grüne', 'Passau'],\n",
       " [33767.55627542279, 'Linke', 'Passau'],\n",
       " [54692.64783418275, 'ödp', 'Passau'],\n",
       " [46642.63959847584, 'PaL', 'Passau'],\n",
       " [190195.08647500936, 'SPD', 'Passau'],\n",
       " [39037.05812747373, 'Zukunft Passau', 'Passau'],\n",
       " [25406.515194434076, 'AfD', 'Straubing'],\n",
       " [253696.94164880711, 'CSU', 'Straubing'],\n",
       " [14518.589420125729, 'FDP', 'Straubing'],\n",
       " [61892.28759134511, 'FW', 'Straubing'],\n",
       " [82783.74578183156, 'Grüne', 'Straubing'],\n",
       " [28436.51535550759, 'Linke', 'Straubing'],\n",
       " [53001.54875832956, 'ödp', 'Straubing'],\n",
       " [133710.3824862527, 'SPD', 'Straubing'],\n",
       " [8227.37702135411, 'AfD', 'Deggendorf'],\n",
       " [152522.18680815815, 'CSU', 'Deggendorf'],\n",
       " [-7847.321171191788, 'FDP', 'Deggendorf'],\n",
       " [4499.116703826425, 'FW', 'Deggendorf'],\n",
       " [9007.521535822461, 'Grüne', 'Deggendorf'],\n",
       " [1930.8616897532634, 'Junge Liste', 'Deggendorf'],\n",
       " [114283.18586533592, 'SPD', 'Deggendorf'],\n",
       " [9656.125149416563, 'WAN', 'Deggendorf'],\n",
       " [72470.72272663706, 'CSU', 'Kelheim'],\n",
       " [22105.624939706093, 'FW', 'Kelheim'],\n",
       " [21238.966608937597, 'Grüne', 'Kelheim'],\n",
       " [23120.595109020578, 'Kelheimer Mitte', 'Kelheim'],\n",
       " [9723.593565115241, 'SLU', 'Kelheim'],\n",
       " [50358.950193853656, 'SPD', 'Kelheim'],\n",
       " [73645.54667589704, 'CSU', 'Mainburg'],\n",
       " [5438.57081524655, 'FDP', 'Mainburg'],\n",
       " [20686.387988349, 'FW', 'Mainburg'],\n",
       " [16788.23926837915, 'Grüne', 'Mainburg'],\n",
       " [12817.136989591809, 'Junge Liste', 'Mainburg'],\n",
       " [15846.867939476018, 'ödp', 'Mainburg'],\n",
       " [19653.239768077703, 'SLU', 'Mainburg'],\n",
       " [31474.786258221353, 'SPD', 'Mainburg'],\n",
       " [9684.998051619637, 'Zukunft Mainburg', 'Mainburg'],\n",
       " [20955.029771740355, 'Amberger Bunte', 'Amberg'],\n",
       " [292878.5118174283, 'CSU', 'Amberg'],\n",
       " [3987.8812676968437, 'FDP', 'Amberg'],\n",
       " [22241.46625464272, 'FW', 'Amberg'],\n",
       " [48630.57601727274, 'Grüne', 'Amberg'],\n",
       " [28219.857234540632, 'Liste', 'Amberg'],\n",
       " [15804.025778680672, 'ödp', 'Amberg'],\n",
       " [112080.17345770747, 'SPD', 'Amberg'],\n",
       " [945037.2795911813, 'CSU', 'Regensburg'],\n",
       " [404387.76207277726, 'Grüne', 'Regensburg'],\n",
       " [158216.37704576337, 'FW', 'Regensburg'],\n",
       " [81855.8564228086, 'AfD', 'Regensburg'],\n",
       " [739803.2079915358, 'SPD', 'Regensburg'],\n",
       " [118207.21959169742, 'FDP', 'Regensburg'],\n",
       " [130820.29536605607, 'ödp', 'Regensburg'],\n",
       " [95362.2284022717, 'Linke', 'Regensburg'],\n",
       " [172552.06232611748, 'CSB', 'Regensburg'],\n",
       " [158771.52197835105, 'Brücke', 'Regensburg'],\n",
       " [71882.14347068753, 'Partei', 'Regensburg'],\n",
       " [82387.20359674699, 'Ribisl', 'Regensburg'],\n",
       " [255432.00571448755, 'CSU', 'Neumarkt'],\n",
       " [3466.8480999625026, 'FDP', 'Neumarkt'],\n",
       " [23882.69877066355, 'Flitz', 'Neumarkt'],\n",
       " [35500.24531171691, 'FW', 'Neumarkt'],\n",
       " [88189.37419831422, 'Grüne', 'Neumarkt'],\n",
       " [15914.890397066176, 'Linke', 'Neumarkt'],\n",
       " [79092.77135466036, 'SPD', 'Neumarkt'],\n",
       " [77557.70559129375, 'CSU', 'Regenstauf'],\n",
       " [8512.066946706853, 'FDP', 'Regenstauf'],\n",
       " [21004.282599723636, 'FW', 'Regenstauf'],\n",
       " [16490.053886477217, 'Grüne', 'Regenstauf'],\n",
       " [13986.661950727663, 'ödp', 'Regenstauf'],\n",
       " [49893.809122999606, 'SPD', 'Regenstauf'],\n",
       " [77088.23483325436, 'AfD', 'Bamberg'],\n",
       " [176939.04549828396, 'Bamberger Allianz', 'Bamberg'],\n",
       " [175800.73689535502, 'Bamberger Bürger Block', 'Bamberg'],\n",
       " [161385.30087608154, 'Bambergs Mitte', 'Bamberg'],\n",
       " [168490.27122150882, 'Bambergs Unabhängige Bürger', 'Bamberg'],\n",
       " [777710.3040963818, 'CSU', 'Bamberg'],\n",
       " [173359.68779073097, 'FDP', 'Bamberg'],\n",
       " [168193.26270115987, 'FW', 'Bamberg'],\n",
       " [424494.89922629646, 'Grüne', 'Bamberg'],\n",
       " [167539.5406671284, 'Linke', 'Bamberg'],\n",
       " [118298.28948021636, 'ödp', 'Bamberg'],\n",
       " [67379.31863020358, 'Partei', 'Bamberg'],\n",
       " [786501.84059083, 'SPD', 'Bamberg'],\n",
       " [75053.81287587674, 'volt', 'Bamberg'],\n",
       " [64637.32166388035, 'AfD', 'Coburg'],\n",
       " [166575.9442090061, 'Christlich Soziale Bürger', 'Coburg'],\n",
       " [128356.43395976501, 'Coburger Lister', 'Coburg'],\n",
       " [708632.7894704752, 'CSU', 'Coburg'],\n",
       " [159559.81494100686, 'FDP', 'Coburg'],\n",
       " [381956.02032245795, 'Grüne', 'Coburg'],\n",
       " [157149.95414193312, 'Junges Coburg', 'Coburg'],\n",
       " [123452.6368154316, 'Linke', 'Coburg'],\n",
       " [141611.313919822, 'ödp', 'Coburg'],\n",
       " [162490.3108718343, 'Pro Coburg', 'Coburg'],\n",
       " [733591.16934817, 'SPD', 'Coburg'],\n",
       " [72382.05571338595, 'CSU', 'Pegnitz'],\n",
       " [39185.734300389864, 'FW', 'Pegnitz'],\n",
       " [22581.62996670258, 'FWG', 'Pegnitz'],\n",
       " [18816.02324572418, 'Grüne', 'Pegnitz'],\n",
       " [20475.80568424673, 'PEG', 'Pegnitz'],\n",
       " [45601.8062757303, 'SPD', 'Pegnitz'],\n",
       " [15734.430583483985, 'Zukunft Pegnitz', 'Pegnitz'],\n",
       " [18102.593179688007, 'AfD', 'Kronach'],\n",
       " [99635.04184147311, 'CSU', 'Kronach'],\n",
       " [37433.217098984496, 'Frauenliste', 'Kronach'],\n",
       " [41440.53620453439, 'FW', 'Kronach'],\n",
       " [41344.9951248843, 'Grüne', 'Kronach'],\n",
       " [13581.188111064977, 'Linke', 'Kronach'],\n",
       " [67704.1264640758, 'SPD', 'Kronach'],\n",
       " [14737.996057996623, 'AfD', 'Kulmbach'],\n",
       " [165614.18235217108, 'CSU', 'Kulmbach'],\n",
       " [7608.677824634608, 'FDP', 'Kulmbach'],\n",
       " [36747.771168592844, 'FW', 'Kulmbach'],\n",
       " [41359.0770362078, 'Grüne', 'Kulmbach'],\n",
       " [76506.6357378354, 'SPD', 'Kulmbach'],\n",
       " [4051.203464892505, 'AfD', 'Lichtenfels'],\n",
       " [97493.06857666186, 'CSU', 'Lichtenfels'],\n",
       " [6724.487899206254, 'FW', 'Lichtenfels'],\n",
       " [29970.066369829085, 'Grüne', 'Lichtenfels'],\n",
       " [15189.672098988587, 'Junge Bürger', 'Lichtenfels'],\n",
       " [51655.7775459175, 'SPD', 'Lichtenfels'],\n",
       " [10217.938360064407, 'Wählervereinigung LJ', 'Lichtenfels'],\n",
       " [39421.08662179582, 'Aktive Bürger', 'Selb'],\n",
       " [73783.19128565588, 'CSU', 'Selb'],\n",
       " [5593.686999239836, 'FDP', 'Selb'],\n",
       " [24433.492712369352, 'FW', 'Selb'],\n",
       " [22088.758739011773, 'Grüne', 'Selb'],\n",
       " [55479.602065737265, 'SPD', 'Selb'],\n",
       " [77876.7522818001, 'AfD', 'Fürth'],\n",
       " [701209.1479994266, 'CSU', 'Fürth'],\n",
       " [64313.439463652096, 'FDP', 'Fürth'],\n",
       " [126815.33530577736, 'FW', 'Fürth'],\n",
       " [304187.38612305355, 'Grüne', 'Fürth'],\n",
       " [94029.81609219116, 'Linke', 'Fürth'],\n",
       " [655090.069309895, 'SPD', 'Fürth'],\n",
       " [458830.6113859601, 'AfD', 'Nürnberg'],\n",
       " [443750.957628978, 'BIA', 'Nürnberg'],\n",
       " [6051380.081764084, 'CSU', 'Nürnberg'],\n",
       " [542750.6922442904, 'Die Guten', 'Nürnberg'],\n",
       " [1051689.9423434713, 'FDP', 'Nürnberg'],\n",
       " [549135.9884658678, 'FW', 'Nürnberg'],\n",
       " [3438306.316723647, 'Grüne', 'Nürnberg'],\n",
       " [1647101.3315807753, 'Linke', 'Nürnberg'],\n",
       " [803952.1304100943, 'Linke Liste', 'Nürnberg'],\n",
       " [987482.6215865229, 'ödp', 'Nürnberg'],\n",
       " [430932.0170392815, 'Partei', 'Nürnberg'],\n",
       " [995746.8886558898, 'politbande', 'Nürnberg'],\n",
       " [5601375.138339436, 'SPD', 'Nürnberg'],\n",
       " [221500.60185986868, 'CSU', 'Schwabach'],\n",
       " [-50642.195612200616, 'FDP', 'Schwabach'],\n",
       " [-9619.41979359637, 'FW', 'Schwabach'],\n",
       " [75190.12171178598, 'Grüne', 'Schwabach'],\n",
       " [-2993.2138711572, 'Linke', 'Schwabach'],\n",
       " [50904.37141079399, 'SPD', 'Schwabach'],\n",
       " [83854.99334325372, 'CSU', 'Feucht'],\n",
       " [32341.845501794516, 'Die Franken', 'Feucht'],\n",
       " [11413.669703630661, 'FDP', 'Feucht'],\n",
       " [29255.578613317808, 'FW', 'Feucht'],\n",
       " [25726.985695938718, 'Grüne', 'Feucht'],\n",
       " [54373.95789242658, 'SPD', 'Feucht'],\n",
       " [32129.64722359403, 'UCS', 'Feucht'],\n",
       " [95431.40829994858, 'CSU', 'Wendelstein'],\n",
       " [9340.119175815405, 'FDP', 'Wendelstein'],\n",
       " [14691.152547513004, 'FW', 'Wendelstein'],\n",
       " [27971.019513443265, 'Grüne', 'Wendelstein'],\n",
       " [59931.37455932058, 'SPD', 'Wendelstein'],\n",
       " [31556.702099893097, 'AfD', 'Aschaffenburg'],\n",
       " [361771.73959412274, 'CSU', 'Aschaffenburg'],\n",
       " [41676.70318361982, 'FDP', 'Aschaffenburg'],\n",
       " [171241.6417066736, 'Grüne', 'Aschaffenburg'],\n",
       " [55909.48497340144, 'Kommunale Initiative', 'Aschaffenburg'],\n",
       " [44893.87498054362, 'ödp', 'Aschaffenburg'],\n",
       " [266593.55488485383, 'SPD', 'Aschaffenburg'],\n",
       " [59436.44756107319, 'UBV', 'Aschaffenburg'],\n",
       " [33552.331003076695, 'AfD', 'Schweinfurt'],\n",
       " [275380.93850550405, 'CSU', 'Schweinfurt'],\n",
       " [32739.585355478117, 'FDP', 'Schweinfurt'],\n",
       " [66561.86742796566, 'FW', 'Schweinfurt'],\n",
       " [129570.24753461081, 'Grüne', 'Schweinfurt'],\n",
       " [67383.19055293992, 'Linke', 'Schweinfurt'],\n",
       " [50494.95949561847, 'ödp', 'Schweinfurt'],\n",
       " [39345.73117536923, 'Pro Schweinfurt', 'Schweinfurt'],\n",
       " [176521.28407525923, 'SPD', 'Schweinfurt'],\n",
       " [83216.89322307808, 'AfD', 'Würzburg'],\n",
       " [168658.57969566376, 'Bürgerforum', 'Würzburg'],\n",
       " [1214458.018148574, 'CSU', 'Würzburg'],\n",
       " [161982.45696111492, 'FDP', 'Würzburg'],\n",
       " [176767.44374402505, 'FW', 'Würzburg'],\n",
       " [691714.9889909654, 'Grüne', 'Würzburg'],\n",
       " [166217.60896108992, 'Linke', 'Würzburg'],\n",
       " [154704.3842009667, 'ödp', 'Würzburg'],\n",
       " [1174407.8581576808, 'SPD', 'Würzburg'],\n",
       " [178208.26196396357, 'WL', 'Würzburg'],\n",
       " [143819.70187123568, 'ZfW', 'Würzburg'],\n",
       " [80410.53314898605, 'CSU', 'Haßfurt'],\n",
       " [19123.46195245228, 'Grüne', 'Haßfurt'],\n",
       " [10997.804855629376, 'JL', 'Haßfurt'],\n",
       " [33286.82385420971, 'SPD', 'Haßfurt'],\n",
       " [27010.76442456863, 'WG', 'Haßfurt'],\n",
       " [265915.02896616474, 'AfD', 'Augsburg'],\n",
       " [226496.9472596843, 'Augsburg in Bürgerhand', 'Augsburg'],\n",
       " [236499.25938078683, 'AUX', 'Augsburg'],\n",
       " [1900739.4000498257, 'CSU', 'Augsburg'],\n",
       " [232294.26741240412, 'FDP', 'Augsburg'],\n",
       " [235943.9167258656, 'FW', 'Augsburg'],\n",
       " [1027532.745483683, 'Grüne', 'Augsburg'],\n",
       " [234651.6632498472, 'Linke', 'Augsburg'],\n",
       " [193009.7309796599, 'ödp', 'Augsburg'],\n",
       " [72342.40907604725, 'Partei', 'Augsburg'],\n",
       " [156395.79317403157, 'Polit-WG', 'Augsburg'],\n",
       " [167253.424713584, 'Pro Augsburg', 'Augsburg'],\n",
       " [1949961.1818561729, 'SPD', 'Augsburg'],\n",
       " [232252.7204550293, 'V-Partei', 'Augsburg'],\n",
       " [242073.40659679554, 'WSA', 'Augsburg'],\n",
       " [20705.03918343282, 'AfD', 'Kaufbeuren'],\n",
       " [238097.94543445238, 'CSU', 'Kaufbeuren'],\n",
       " [31288.246936378007, 'FDP', 'Kaufbeuren'],\n",
       " [67892.2313143758, 'FW', 'Kaufbeuren'],\n",
       " [43893.82316841489, 'Generation KF', 'Kaufbeuren'],\n",
       " [95871.10561104224, 'Grüne', 'Kaufbeuren'],\n",
       " [65063.827955907895, 'Kaufbeurer Initiative', 'Kaufbeuren'],\n",
       " [25749.763405341306, 'Linke', 'Kaufbeuren'],\n",
       " [144960.94643041008, 'SPD', 'Kaufbeuren'],\n",
       " [32312.430492831667, 'AfD', 'Kempten'],\n",
       " [355069.16912978835, 'CSU', 'Kempten'],\n",
       " [50904.758445858075, 'FDP', 'Kempten'],\n",
       " [30870.629969190464, 'Future for Kempten', 'Kempten'],\n",
       " [76061.75333305939, 'FW', 'Kempten'],\n",
       " [165116.8500174535, 'Grüne', 'Kempten'],\n",
       " [29515.69116889217, 'Junge Union', 'Kempten'],\n",
       " [15216.901650979133, 'Linke', 'Kempten'],\n",
       " [51762.22835186088, 'ödp', 'Kempten'],\n",
       " [237200.61415533337, 'SPD', 'Kempten'],\n",
       " [17582.625692287544, 'AfD', 'Memmingen'],\n",
       " [51332.77519323898, 'Christlicher Rathausblock', 'Memmingen'],\n",
       " [181441.18061013435, 'CSU', 'Memmingen'],\n",
       " [19057.599943100766, 'FDP', 'Memmingen'],\n",
       " [47904.170227650284, 'FW', 'Memmingen'],\n",
       " [94527.93562085874, 'Grüne', 'Memmingen'],\n",
       " [18819.50711121732, 'Linke', 'Memmingen'],\n",
       " [40678.32797307098, 'ödp', 'Memmingen'],\n",
       " [104612.06236411647, 'SPD', 'Memmingen'],\n",
       " [141716.5560893527, 'CSU', 'Friedberg'],\n",
       " [6742.273984393167, 'FDP', 'Friedberg'],\n",
       " [21676.615394040717, 'FW', 'Friedberg'],\n",
       " [23034.032146478872, 'Grüne', 'Friedberg'],\n",
       " [15042.128204795375, 'ödp', 'Friedberg'],\n",
       " [27988.031278864986, 'Parteifreie', 'Friedberg'],\n",
       " [95425.31392663931, 'SPD', 'Friedberg'],\n",
       " [94116.4340240866, 'CSU', 'Illertissen'],\n",
       " [27590.255670482708, 'FW', 'Illertissen'],\n",
       " [20968.57942361232, 'Grüne', 'Illertissen'],\n",
       " [6920.568085499322, 'Linke', 'Illertissen'],\n",
       " [27117.202777158825, 'SPD', 'Illertissen'],\n",
       " [310972.6420041422, 'CSU', 'Neu-Ulm'],\n",
       " [44742.571344164346, 'FDP', 'Neu-Ulm'],\n",
       " [82603.65107635398, 'FW', 'Neu-Ulm'],\n",
       " [146934.71989756427, 'Grüne', 'Neu-Ulm'],\n",
       " [26020.80135170631, 'Junge Union', 'Neu-Ulm'],\n",
       " [40126.28056920925, 'Linke', 'Neu-Ulm'],\n",
       " [71098.09898243152, 'Pro Ulm', 'Neu-Ulm'],\n",
       " [218350.07411764745, 'SPD', 'Neu-Ulm'],\n",
       " [77222.65520659674, 'CSU', 'Bad Wörishofen'],\n",
       " [4130.946985027901, 'FDP', 'Bad Wörishofen'],\n",
       " [21946.456110721578, 'FW', 'Bad Wörishofen'],\n",
       " [13118.569578536784, 'Generation Fortschritt', 'Bad Wörishofen'],\n",
       " [23427.957361088298, 'Grüne', 'Bad Wörishofen'],\n",
       " [13088.680565719455, 'ödp', 'Bad Wörishofen'],\n",
       " [26743.08305564732, 'SPD', 'Bad Wörishofen'],\n",
       " [74727.64319594078, 'CSU', 'Gunzenhausen'],\n",
       " [4661.2165587917025, 'FDP', 'Gunzenhausen'],\n",
       " [16333.671039358916, 'FW', 'Gunzenhausen'],\n",
       " [21725.820215816348, 'Grüne', 'Gunzenhausen'],\n",
       " [10309.488916119497, 'Linke', 'Gunzenhausen'],\n",
       " [51846.37617405229, 'SPD', 'Gunzenhausen']]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_parties = dataframe.party.unique()\n",
    "list_cities = dataframe.city.unique()\n",
    "data_sums = []\n",
    "\n",
    "for j in range(len(list_cities)):\n",
    "        \n",
    "    city_now = list_cities[j]\n",
    "    data_city = dataframe[dataframe['city'].str.match(city_now)]\n",
    "        \n",
    "    list_parties = data_city.party.unique()\n",
    "        \n",
    "    for k in range(len(list_parties)):\n",
    "            \n",
    "        party_now = list_parties[k]\n",
    "        data_party = data_city[data_city['party'].str.match(party_now)]\n",
    "\n",
    "        sum_party_city = data_party['Prediction'].sum()\n",
    "        data_sums.append([sum_party_city, list_parties[k], list_cities[j]])\n",
    "        \n",
    "data_sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = pd.DataFrame(data= data_sums, columns = ['sum', 'party', 'city'])\n",
    "\n",
    "seat_calc_data = pd.DataFrame(columns = [])\n",
    "\n",
    "    \n",
    "for j in range(len(list_cities)):\n",
    "        \n",
    "    city_now = list_cities[j]\n",
    "    data_city = result_df[result_df['city'].str.match(city_now)]\n",
    "        \n",
    "    list_parties = data_city.party.unique()\n",
    "        \n",
    "    for k in range(len(list_parties)):\n",
    "            \n",
    "        party_now = list_parties[k]\n",
    "        data_party = data_city[data_city['party'].str.match(party_now)]\n",
    "            \n",
    "        for n in range(1,41):   \n",
    "                \n",
    "            number = data_party['sum']/n\n",
    "                \n",
    "            num_str = str(number)\n",
    "                \n",
    "            number_now = pd.DataFrame([[num_str, n, party_now, city_now]], columns = ['votes_ranked', 'rank',\n",
    "                                                                                                  'party', 'city'])\n",
    "                \n",
    "            seat_calc_data = seat_calc_data.append(number_now, ignore_index = True)\n",
    "\n",
    "# Something turned awry here. Some in the 'votes_ranked' are 'sum,' and not the number it should be \n",
    "# -> if neg estimated vote count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "calc_2 = seat_calc_data['votes_ranked'].str.split(' ', expand = True)\n",
    "calc_2 = calc_2[4].str.split('\\n', expand = True)\n",
    "data = pd.concat([calc_2, seat_calc_data], axis = 1)\n",
    "data.to_excel(r'C:\\Users\\mariu\\Documents\\Project Local Elections\\seat_evaluation_bavaria.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>votes</th>\n",
       "      <th>rank</th>\n",
       "      <th>party</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>72382.055713</td>\n",
       "      <td>1</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>45601.806276</td>\n",
       "      <td>1</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>39185.734300</td>\n",
       "      <td>1</td>\n",
       "      <td>FW</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>36191.027857</td>\n",
       "      <td>2</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>24127.351904</td>\n",
       "      <td>3</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>22800.903138</td>\n",
       "      <td>2</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>22581.629967</td>\n",
       "      <td>1</td>\n",
       "      <td>FWG</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>20475.805684</td>\n",
       "      <td>1</td>\n",
       "      <td>PEG</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>19592.867150</td>\n",
       "      <td>2</td>\n",
       "      <td>FW</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>18816.023246</td>\n",
       "      <td>1</td>\n",
       "      <td>Grüne</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>18095.513928</td>\n",
       "      <td>4</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>15734.430583</td>\n",
       "      <td>1</td>\n",
       "      <td>Zukunft Pegnitz</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>15200.602092</td>\n",
       "      <td>3</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>14476.411143</td>\n",
       "      <td>5</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>13061.911433</td>\n",
       "      <td>3</td>\n",
       "      <td>FW</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>12063.675952</td>\n",
       "      <td>6</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>11400.451569</td>\n",
       "      <td>4</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>11290.814983</td>\n",
       "      <td>2</td>\n",
       "      <td>FWG</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>10340.293673</td>\n",
       "      <td>7</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>10237.902842</td>\n",
       "      <td>2</td>\n",
       "      <td>PEG</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>9796.433575</td>\n",
       "      <td>4</td>\n",
       "      <td>FW</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>9408.011623</td>\n",
       "      <td>2</td>\n",
       "      <td>Grüne</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>9120.361255</td>\n",
       "      <td>5</td>\n",
       "      <td>SPD</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>9047.756964</td>\n",
       "      <td>8</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>8042.450635</td>\n",
       "      <td>9</td>\n",
       "      <td>CSU</td>\n",
       "      <td>Pegnitz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           votes  rank            party     city\n",
       "0   72382.055713     1              CSU  Pegnitz\n",
       "1   45601.806276     1              SPD  Pegnitz\n",
       "2   39185.734300     1               FW  Pegnitz\n",
       "3   36191.027857     2              CSU  Pegnitz\n",
       "4   24127.351904     3              CSU  Pegnitz\n",
       "5   22800.903138     2              SPD  Pegnitz\n",
       "6   22581.629967     1              FWG  Pegnitz\n",
       "7   20475.805684     1              PEG  Pegnitz\n",
       "8   19592.867150     2               FW  Pegnitz\n",
       "9   18816.023246     1            Grüne  Pegnitz\n",
       "10  18095.513928     4              CSU  Pegnitz\n",
       "11  15734.430583     1  Zukunft Pegnitz  Pegnitz\n",
       "12  15200.602092     3              SPD  Pegnitz\n",
       "13  14476.411143     5              CSU  Pegnitz\n",
       "14  13061.911433     3               FW  Pegnitz\n",
       "15  12063.675952     6              CSU  Pegnitz\n",
       "16  11400.451569     4              SPD  Pegnitz\n",
       "17  11290.814983     2              FWG  Pegnitz\n",
       "18  10340.293673     7              CSU  Pegnitz\n",
       "19  10237.902842     2              PEG  Pegnitz\n",
       "20   9796.433575     4               FW  Pegnitz\n",
       "21   9408.011623     2            Grüne  Pegnitz\n",
       "22   9120.361255     5              SPD  Pegnitz\n",
       "23   9047.756964     8              CSU  Pegnitz\n",
       "24   8042.450635     9              CSU  Pegnitz"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seat = data_sorted[data_sorted['city'] == list_cities[49]].reset_index(drop = True).loc[:24]\n",
    "seat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_sorted = pd.read_excel(r'C:\\Users\\mariu\\Documents\\Project Local Elections\\seat_evaluation_bavaria.xlsx')\n",
    "\n",
    "council_seats_no = [80, 70, 60, 50, 50, 44, 44, 50, 40, 40, 30, 44, 44, 44, 40, 44, 44, 44, 40, 40, 44, 24, 40, \n",
    "                    40, 40, 40, 40, 24, 24, 30, 40, 24, 30, 30, 24, 30, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24]\n",
    "\n",
    "list_cities = data_sorted.city.unique()\n",
    "\n",
    "seat_alloc_array = pd.DataFrame()\n",
    "\n",
    "for i in range(len(data_sorted.city.unique())):\n",
    "    \n",
    "    seat = data_sorted[data_sorted['city'] == list_cities[i-1]].reset_index(drop = True).loc[:council_seats_no[i-1]-1]\n",
    "    \n",
    "    frames = [seat_alloc_array, seat]\n",
    "    \n",
    "    seat_alloc_array = pd.concat(frames)\n",
    "\n",
    "seat_alloc_array.to_excel(r'C:\\Users\\mariu\\Documents\\Project Local Elections\\seat_allocation.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}