Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "pycharm": {
     "is_executing": false
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing for time stamped Corona Cases on german county level"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Dataset from Robert-Koch-Institut with included time stamps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "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>BL</th>\n",
       "      <th>BL_ID</th>\n",
       "      <th>EWZ</th>\n",
       "      <th>NUTS</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>cases</th>\n",
       "      <th>cases_per_100k</th>\n",
       "      <th>cases_per_population</th>\n",
       "      <th>county</th>\n",
       "      <th>deaths</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>89504</td>\n",
       "      <td>DEF01</td>\n",
       "      <td>4.918293e+07</td>\n",
       "      <td>42752.592015</td>\n",
       "      <td>13</td>\n",
       "      <td>14.524491</td>\n",
       "      <td>0.014524</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>247548</td>\n",
       "      <td>DEF02</td>\n",
       "      <td>1.122314e+08</td>\n",
       "      <td>104373.457289</td>\n",
       "      <td>52</td>\n",
       "      <td>21.006027</td>\n",
       "      <td>0.021006</td>\n",
       "      <td>SK Kiel</td>\n",
       "      <td>1</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>217198</td>\n",
       "      <td>DEF03</td>\n",
       "      <td>2.116771e+08</td>\n",
       "      <td>146459.457897</td>\n",
       "      <td>36</td>\n",
       "      <td>16.574738</td>\n",
       "      <td>0.016575</td>\n",
       "      <td>SK LÌ_beck</td>\n",
       "      <td>1</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>79487</td>\n",
       "      <td>DEF04</td>\n",
       "      <td>7.140224e+07</td>\n",
       "      <td>54863.995416</td>\n",
       "      <td>10</td>\n",
       "      <td>12.580674</td>\n",
       "      <td>0.012581</td>\n",
       "      <td>SK NeumÌ_nster</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>133210</td>\n",
       "      <td>DEF05</td>\n",
       "      <td>1.425511e+09</td>\n",
       "      <td>250768.570200</td>\n",
       "      <td>15</td>\n",
       "      <td>11.260416</td>\n",
       "      <td>0.011260</td>\n",
       "      <td>LK Dithmarschen</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>197264</td>\n",
       "      <td>DEF06</td>\n",
       "      <td>1.262107e+09</td>\n",
       "      <td>255833.719818</td>\n",
       "      <td>42</td>\n",
       "      <td>21.291264</td>\n",
       "      <td>0.021291</td>\n",
       "      <td>LK Herzogtum Lauenburg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>165507</td>\n",
       "      <td>DEF07</td>\n",
       "      <td>2.088326e+09</td>\n",
       "      <td>638776.023240</td>\n",
       "      <td>20</td>\n",
       "      <td>12.084081</td>\n",
       "      <td>0.012084</td>\n",
       "      <td>LK Nordfriesland</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>200581</td>\n",
       "      <td>DEF08</td>\n",
       "      <td>1.385825e+09</td>\n",
       "      <td>361935.231065</td>\n",
       "      <td>15</td>\n",
       "      <td>7.478276</td>\n",
       "      <td>0.007478</td>\n",
       "      <td>LK Ostholstein</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>314391</td>\n",
       "      <td>DEF09</td>\n",
       "      <td>6.367902e+08</td>\n",
       "      <td>185849.136734</td>\n",
       "      <td>80</td>\n",
       "      <td>25.446021</td>\n",
       "      <td>0.025446</td>\n",
       "      <td>LK Pinneberg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>128647</td>\n",
       "      <td>DEF0A</td>\n",
       "      <td>1.082369e+09</td>\n",
       "      <td>228984.759756</td>\n",
       "      <td>23</td>\n",
       "      <td>17.878380</td>\n",
       "      <td>0.017878</td>\n",
       "      <td>LK PlÌ¦n</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>272775</td>\n",
       "      <td>DEF0B</td>\n",
       "      <td>2.167736e+09</td>\n",
       "      <td>371442.746317</td>\n",
       "      <td>46</td>\n",
       "      <td>16.863716</td>\n",
       "      <td>0.016864</td>\n",
       "      <td>LK Rendsburg-EckernfÌ¦rde</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>200025</td>\n",
       "      <td>DEF0C</td>\n",
       "      <td>1.989341e+09</td>\n",
       "      <td>437586.513613</td>\n",
       "      <td>29</td>\n",
       "      <td>14.498188</td>\n",
       "      <td>0.014498</td>\n",
       "      <td>LK Schleswig-Flensburg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>276032</td>\n",
       "      <td>DEF0D</td>\n",
       "      <td>1.345048e+09</td>\n",
       "      <td>287922.801843</td>\n",
       "      <td>30</td>\n",
       "      <td>10.868305</td>\n",
       "      <td>0.010868</td>\n",
       "      <td>LK Segeberg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>131347</td>\n",
       "      <td>DEF0E</td>\n",
       "      <td>1.017354e+09</td>\n",
       "      <td>206757.303494</td>\n",
       "      <td>19</td>\n",
       "      <td>14.465500</td>\n",
       "      <td>0.014465</td>\n",
       "      <td>LK Steinburg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>1</td>\n",
       "      <td>243196</td>\n",
       "      <td>DEF0F</td>\n",
       "      <td>7.656416e+08</td>\n",
       "      <td>254210.277474</td>\n",
       "      <td>48</td>\n",
       "      <td>19.737167</td>\n",
       "      <td>0.019737</td>\n",
       "      <td>LK Stormarn</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Hamburg</td>\n",
       "      <td>2</td>\n",
       "      <td>1841179</td>\n",
       "      <td>DE600</td>\n",
       "      <td>7.420817e+08</td>\n",
       "      <td>234529.147982</td>\n",
       "      <td>1043</td>\n",
       "      <td>56.648484</td>\n",
       "      <td>0.056648</td>\n",
       "      <td>SK Hamburg</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>3</td>\n",
       "      <td>248292</td>\n",
       "      <td>DE911</td>\n",
       "      <td>1.922479e+08</td>\n",
       "      <td>96243.240009</td>\n",
       "      <td>54</td>\n",
       "      <td>21.748586</td>\n",
       "      <td>0.021749</td>\n",
       "      <td>SK Braunschweig</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>3</td>\n",
       "      <td>104948</td>\n",
       "      <td>DE912</td>\n",
       "      <td>2.249334e+08</td>\n",
       "      <td>109246.816466</td>\n",
       "      <td>24</td>\n",
       "      <td>22.868468</td>\n",
       "      <td>0.022868</td>\n",
       "      <td>SK Salzgitter</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>3</td>\n",
       "      <td>124151</td>\n",
       "      <td>DE913</td>\n",
       "      <td>2.049177e+08</td>\n",
       "      <td>91260.769875</td>\n",
       "      <td>28</td>\n",
       "      <td>22.553181</td>\n",
       "      <td>0.022553</td>\n",
       "      <td>SK Wolfsburg</td>\n",
       "      <td>1</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>3</td>\n",
       "      <td>175920</td>\n",
       "      <td>DE914</td>\n",
       "      <td>1.568403e+09</td>\n",
       "      <td>281623.329721</td>\n",
       "      <td>34</td>\n",
       "      <td>19.326967</td>\n",
       "      <td>0.019327</td>\n",
       "      <td>LK Gifhorn</td>\n",
       "      <td>0</td>\n",
       "      <td>24.03.2020 00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    BL  BL_ID      EWZ   NUTS   Shape__Area  Shape__Length  cases  cases_per_100k  cases_per_population                     county  deaths       last_update\n",
       "0   Schleswig-Holstein      1    89504  DEF01  4.918293e+07   42752.592015     13       14.524491              0.014524               SK Flensburg       0  24.03.2020 00:00\n",
       "1   Schleswig-Holstein      1   247548  DEF02  1.122314e+08  104373.457289     52       21.006027              0.021006                    SK Kiel       1  24.03.2020 00:00\n",
       "2   Schleswig-Holstein      1   217198  DEF03  2.116771e+08  146459.457897     36       16.574738              0.016575                 SK LÌ_beck       1  24.03.2020 00:00\n",
       "3   Schleswig-Holstein      1    79487  DEF04  7.140224e+07   54863.995416     10       12.580674              0.012581             SK NeumÌ_nster       0  24.03.2020 00:00\n",
       "4   Schleswig-Holstein      1   133210  DEF05  1.425511e+09  250768.570200     15       11.260416              0.011260            LK Dithmarschen       0  24.03.2020 00:00\n",
       "5   Schleswig-Holstein      1   197264  DEF06  1.262107e+09  255833.719818     42       21.291264              0.021291     LK Herzogtum Lauenburg       0  24.03.2020 00:00\n",
       "6   Schleswig-Holstein      1   165507  DEF07  2.088326e+09  638776.023240     20       12.084081              0.012084           LK Nordfriesland       0  24.03.2020 00:00\n",
       "7   Schleswig-Holstein      1   200581  DEF08  1.385825e+09  361935.231065     15        7.478276              0.007478             LK Ostholstein       0  24.03.2020 00:00\n",
       "8   Schleswig-Holstein      1   314391  DEF09  6.367902e+08  185849.136734     80       25.446021              0.025446               LK Pinneberg       0  24.03.2020 00:00\n",
       "9   Schleswig-Holstein      1   128647  DEF0A  1.082369e+09  228984.759756     23       17.878380              0.017878                   LK PlÌ¦n       0  24.03.2020 00:00\n",
       "10  Schleswig-Holstein      1   272775  DEF0B  2.167736e+09  371442.746317     46       16.863716              0.016864  LK Rendsburg-EckernfÌ¦rde       0  24.03.2020 00:00\n",
       "11  Schleswig-Holstein      1   200025  DEF0C  1.989341e+09  437586.513613     29       14.498188              0.014498     LK Schleswig-Flensburg       0  24.03.2020 00:00\n",
       "12  Schleswig-Holstein      1   276032  DEF0D  1.345048e+09  287922.801843     30       10.868305              0.010868                LK Segeberg       0  24.03.2020 00:00\n",
       "13  Schleswig-Holstein      1   131347  DEF0E  1.017354e+09  206757.303494     19       14.465500              0.014465               LK Steinburg       0  24.03.2020 00:00\n",
       "14  Schleswig-Holstein      1   243196  DEF0F  7.656416e+08  254210.277474     48       19.737167              0.019737                LK Stormarn       0  24.03.2020 00:00\n",
       "15             Hamburg      2  1841179  DE600  7.420817e+08  234529.147982   1043       56.648484              0.056648                 SK Hamburg       0  24.03.2020 00:00\n",
       "16       Niedersachsen      3   248292  DE911  1.922479e+08   96243.240009     54       21.748586              0.021749            SK Braunschweig       0  24.03.2020 00:00\n",
       "17       Niedersachsen      3   104948  DE912  2.249334e+08  109246.816466     24       22.868468              0.022868              SK Salzgitter       0  24.03.2020 00:00\n",
       "18       Niedersachsen      3   124151  DE913  2.049177e+08   91260.769875     28       22.553181              0.022553               SK Wolfsburg       1  24.03.2020 00:00\n",
       "19       Niedersachsen      3   175920  DE914  1.568403e+09  281623.329721     34       19.326967              0.019327                 LK Gifhorn       0  24.03.2020 00:00"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)\n",
    "df_agg = pd.read_csv('data/RKI_Corona_Landkreise_updated.csv')\n",
    "df_agg = df_agg.filter(['BL', 'BL_ID', 'EWZ', 'NUTS', 'Shape__Area', 'Shape__Length', 'cases', 'cases_per_100k', 'cases_per_population', 'county', 'death rate', 'deaths', 'last_update'])\n",
    "df_agg.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>IdBundesland</th>\n",
       "      <th>Bundesland</th>\n",
       "      <th>Landkreis</th>\n",
       "      <th>Altersgruppe</th>\n",
       "      <th>Geschlecht</th>\n",
       "      <th>AnzahlFall</th>\n",
       "      <th>AnzahlTodesfall</th>\n",
       "      <th>Meldedatum</th>\n",
       "      <th>IdLandkreis</th>\n",
       "      <th>Datenstand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-19</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-21</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-28</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-20</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-19</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-20</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-28</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-24</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-26</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-26</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A80+</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A80+</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-30</td>\n",
       "      <td>1001</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Kiel</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-10</td>\n",
       "      <td>1002</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    IdBundesland          Bundesland     Landkreis Altersgruppe Geschlecht  AnzahlFall  AnzahlTodesfall  Meldedatum  IdLandkreis        Datenstand\n",
       "0              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           1                0  2020-03-14         1001  01.04.2020 00:00\n",
       "1              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           2                0  2020-03-19         1001  01.04.2020 00:00\n",
       "2              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           1                0  2020-03-21         1001  01.04.2020 00:00\n",
       "3              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           1                0  2020-03-27         1001  01.04.2020 00:00\n",
       "4              1  Schleswig-Holstein  SK Flensburg      A15-A34          W           1                0  2020-03-14         1001  01.04.2020 00:00\n",
       "5              1  Schleswig-Holstein  SK Flensburg      A15-A34          W           2                0  2020-03-18         1001  01.04.2020 00:00\n",
       "6              1  Schleswig-Holstein  SK Flensburg      A15-A34          W           1                0  2020-03-28         1001  01.04.2020 00:00\n",
       "7              1  Schleswig-Holstein  SK Flensburg      A35-A59          M           1                0  2020-03-14         1001  01.04.2020 00:00\n",
       "8              1  Schleswig-Holstein  SK Flensburg      A35-A59          M           1                0  2020-03-20         1001  01.04.2020 00:00\n",
       "9              1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-14         1001  01.04.2020 00:00\n",
       "10             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           2                0  2020-03-19         1001  01.04.2020 00:00\n",
       "11             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-20         1001  01.04.2020 00:00\n",
       "12             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-28         1001  01.04.2020 00:00\n",
       "13             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-29         1001  01.04.2020 00:00\n",
       "14             1  Schleswig-Holstein  SK Flensburg      A60-A79          M           1                0  2020-03-24         1001  01.04.2020 00:00\n",
       "15             1  Schleswig-Holstein  SK Flensburg      A60-A79          M           2                0  2020-03-26         1001  01.04.2020 00:00\n",
       "16             1  Schleswig-Holstein  SK Flensburg      A60-A79          W           2                0  2020-03-26         1001  01.04.2020 00:00\n",
       "17             1  Schleswig-Holstein  SK Flensburg         A80+          M           2                0  2020-03-31         1001  01.04.2020 00:00\n",
       "18             1  Schleswig-Holstein  SK Flensburg         A80+          W           1                0  2020-03-30         1001  01.04.2020 00:00\n",
       "19             1  Schleswig-Holstein       SK Kiel      A15-A34          M           1                0  2020-03-10         1002  01.04.2020 00:00"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time = pd.read_csv('data/RKI_COVID19.csv', index_col=[0]).drop(columns=['NeuerFall', 'NeuerTodesfall', 'ObjectId'])\n",
    "df_time.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['A15-A34', 'A35-A59', 'A60-A79', 'A80+', 'A00-A04', 'A05-A14',\n",
       "       'unbekannt'], dtype=object)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time.Altersgruppe.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def granular_dict(agegroup, sex, infected, death):\n",
    "    \n",
    "    dict = {\n",
    "        \n",
    "    }\n",
    "\n",
    "\n",
    "def init_dict(df):\n",
    "    dict = {\n",
    "        'Infected': 0,\n",
    "        'Death': 0,\n",
    "        'Recovered': None,\n",
    "        \n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>IdBundesland</th>\n",
       "      <th>Bundesland</th>\n",
       "      <th>Landkreis</th>\n",
       "      <th>Altersgruppe</th>\n",
       "      <th>Geschlecht</th>\n",
       "      <th>AnzahlFall</th>\n",
       "      <th>AnzahlTodesfall</th>\n",
       "      <th>Meldedatum</th>\n",
       "      <th>IdLandkreis</th>\n",
       "      <th>Datenstand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-19</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-21</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A15-A34</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-28</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-20</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-14</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-19</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-20</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-28</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-24</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-26</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>W</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-26</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A80+</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>Schleswig-Holstein</td>\n",
       "      <td>SK Flensburg</td>\n",
       "      <td>A80+</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-03-30</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>01.04.2020 00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    IdBundesland          Bundesland     Landkreis Altersgruppe Geschlecht  AnzahlFall  AnzahlTodesfall  Meldedatum  IdLandkreis        Datenstand\n",
       "0              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           1                0  2020-03-14       1001.0  01.04.2020 00:00\n",
       "1              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           2                0  2020-03-19       1001.0  01.04.2020 00:00\n",
       "2              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           1                0  2020-03-21       1001.0  01.04.2020 00:00\n",
       "3              1  Schleswig-Holstein  SK Flensburg      A15-A34          M           1                0  2020-03-27       1001.0  01.04.2020 00:00\n",
       "4              1  Schleswig-Holstein  SK Flensburg      A15-A34          W           1                0  2020-03-14       1001.0  01.04.2020 00:00\n",
       "5              1  Schleswig-Holstein  SK Flensburg      A15-A34          W           2                0  2020-03-18       1001.0  01.04.2020 00:00\n",
       "6              1  Schleswig-Holstein  SK Flensburg      A15-A34          W           1                0  2020-03-28       1001.0  01.04.2020 00:00\n",
       "7              1  Schleswig-Holstein  SK Flensburg      A35-A59          M           1                0  2020-03-14       1001.0  01.04.2020 00:00\n",
       "8              1  Schleswig-Holstein  SK Flensburg      A35-A59          M           1                0  2020-03-20       1001.0  01.04.2020 00:00\n",
       "9              1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-14       1001.0  01.04.2020 00:00\n",
       "10             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           2                0  2020-03-19       1001.0  01.04.2020 00:00\n",
       "11             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-20       1001.0  01.04.2020 00:00\n",
       "12             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-28       1001.0  01.04.2020 00:00\n",
       "13             1  Schleswig-Holstein  SK Flensburg      A35-A59          W           1                0  2020-03-29       1001.0  01.04.2020 00:00\n",
       "14             1  Schleswig-Holstein  SK Flensburg      A60-A79          M           1                0  2020-03-24       1001.0  01.04.2020 00:00\n",
       "15             1  Schleswig-Holstein  SK Flensburg      A60-A79          M           2                0  2020-03-26       1001.0  01.04.2020 00:00\n",
       "16             1  Schleswig-Holstein  SK Flensburg      A60-A79          W           2                0  2020-03-26       1001.0  01.04.2020 00:00\n",
       "17             1  Schleswig-Holstein  SK Flensburg         A80+          M           2                0  2020-03-31       1001.0  01.04.2020 00:00\n",
       "18             1  Schleswig-Holstein  SK Flensburg         A80+          W           1                0  2020-03-30       1001.0  01.04.2020 00:00"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time  = df_time.astype({'IdLandkreis': 'float64'})\n",
    "#df_time.Meldedatum.iloc[5]\n",
    "Altersgruppen = df_time.Altersgruppe.unique()\n",
    "Geschlechter = df_time.Geschlecht.unique()\n",
    "LK_list = df_time.Landkreis.unique()\n",
    "grouped = df_time.groupby('Landkreis')\n",
    "lk = 'SK Flensburg'\n",
    "df_tmp1 = grouped.get_group(lk)\n",
    "Meldedaten = df_tmp1.Meldedatum.unique()\n",
    "df_tmp2 = pd.DataFrame(data=[lk, df_temp.Bundesland.iloc[0], df_temp.IdBundesland.iloc[0], df_temp.IdLandkreis.iloc[0]])\n",
    "df\n",
    "#for lk in LK_list:\n",
    "#    df_temp = grouped.get_group(lk)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df_time = df_time[df_time['IdLandkreis']!='0-1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8230, 10)"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load and merge data from other RKI_Corona_Landkreise.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "nuts = pd.read_csv('data/RKI_Corona_Landkreise.csv')\n",
    "nuts = nuts.filter(['county', 'NUTS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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>IdBundesland</th>\n",
       "      <th>Bundesland</th>\n",
       "      <th>Landkreis</th>\n",
       "      <th>Altersgruppe</th>\n",
       "      <th>Geschlecht</th>\n",
       "      <th>AnzahlFall</th>\n",
       "      <th>AnzahlTodesfall</th>\n",
       "      <th>ObjectId</th>\n",
       "      <th>Meldedatum</th>\n",
       "      <th>IdLandkreis</th>\n",
       "      <th>NUTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>154936</td>\n",
       "      <td>2020-03-18T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>DEE03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154937</td>\n",
       "      <td>2020-03-12T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>DEE03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154938</td>\n",
       "      <td>2020-03-17T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>DEE03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154939</td>\n",
       "      <td>2020-03-10T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>DEE03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154940</td>\n",
       "      <td>2020-03-19T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>DEE03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IdBundesland      Bundesland     Landkreis Altersgruppe Geschlecht  \\\n",
       "0            15  Sachsen-Anhalt  SK Magdeburg      A35-A59          M   \n",
       "1            15  Sachsen-Anhalt  SK Magdeburg      A35-A59          W   \n",
       "2            15  Sachsen-Anhalt  SK Magdeburg      A35-A59          W   \n",
       "3            15  Sachsen-Anhalt  SK Magdeburg      A60-A79          M   \n",
       "4            15  Sachsen-Anhalt  SK Magdeburg      A60-A79          M   \n",
       "\n",
       "   AnzahlFall  AnzahlTodesfall  ObjectId                Meldedatum  \\\n",
       "0           2                0    154936  2020-03-18T00:00:00.000Z   \n",
       "1           1                0    154937  2020-03-12T00:00:00.000Z   \n",
       "2           1                0    154938  2020-03-17T00:00:00.000Z   \n",
       "3           1                0    154939  2020-03-10T00:00:00.000Z   \n",
       "4           1                0    154940  2020-03-19T00:00:00.000Z   \n",
       "\n",
       "  IdLandkreis   NUTS  \n",
       "0       15003  DEE03  \n",
       "1       15003  DEE03  \n",
       "2       15003  DEE03  \n",
       "3       15003  DEE03  \n",
       "4       15003  DEE03  "
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time = pd.merge(left=df_time, right=nuts, how='left', left_on='Landkreis', right_on='county').drop(columns=['county'])\n",
    "df_time.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load and merge data about population numbers, sex and density"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>NUTS3</th>\n",
       "      <th>Flaeche</th>\n",
       "      <th>Bevoelkerung</th>\n",
       "      <th>maennlich</th>\n",
       "      <th>weiblich</th>\n",
       "      <th>Dichte</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DEF01</td>\n",
       "      <td>56,73</td>\n",
       "      <td>89 504</td>\n",
       "      <td>44 599</td>\n",
       "      <td>44 905</td>\n",
       "      <td>1 578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DEF02</td>\n",
       "      <td>118,65</td>\n",
       "      <td>247 548</td>\n",
       "      <td>120 566</td>\n",
       "      <td>126 982</td>\n",
       "      <td>2 086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DEF03</td>\n",
       "      <td>214,19</td>\n",
       "      <td>217 198</td>\n",
       "      <td>104 371</td>\n",
       "      <td>112 827</td>\n",
       "      <td>1 014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DEF04</td>\n",
       "      <td>71,66</td>\n",
       "      <td>79 487</td>\n",
       "      <td>39 241</td>\n",
       "      <td>40 246</td>\n",
       "      <td>1 109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEF05</td>\n",
       "      <td>1 428,17</td>\n",
       "      <td>133 210</td>\n",
       "      <td>65 720</td>\n",
       "      <td>67 490</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NUTS3      Flaeche Bevoelkerung   maennlich    weiblich    Dichte\n",
       "0  DEF01        56,73       89 504      44 599      44 905     1 578\n",
       "1  DEF02       118,65      247 548     120 566     126 982     2 086\n",
       "2  DEF03       214,19      217 198     104 371     112 827     1 014\n",
       "3  DEF04        71,66       79 487      39 241      40 246     1 109\n",
       "4  DEF05     1 428,17      133 210      65 720      67 490        93"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "BV = pd.read_csv('data/Landkreise1.csv', encoding = \"utf-8\", sep=';').drop(columns=['LandkreisId'])\n",
    "BV.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(245,)"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "BV['NUTS3'].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(394,)"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time['NUTS'].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>IdBundesland</th>\n",
       "      <th>Bundesland</th>\n",
       "      <th>Landkreis</th>\n",
       "      <th>Altersgruppe</th>\n",
       "      <th>Geschlecht</th>\n",
       "      <th>AnzahlFall</th>\n",
       "      <th>AnzahlTodesfall</th>\n",
       "      <th>ObjectId</th>\n",
       "      <th>Meldedatum</th>\n",
       "      <th>IdLandkreis</th>\n",
       "      <th>NUTS3</th>\n",
       "      <th>Flaeche</th>\n",
       "      <th>Bevoelkerung</th>\n",
       "      <th>maennlich</th>\n",
       "      <th>weiblich</th>\n",
       "      <th>Dichte</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>154936</td>\n",
       "      <td>2020-03-18T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154937</td>\n",
       "      <td>2020-03-12T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A35-A59</td>\n",
       "      <td>W</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154938</td>\n",
       "      <td>2020-03-17T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154939</td>\n",
       "      <td>2020-03-10T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>Sachsen-Anhalt</td>\n",
       "      <td>SK Magdeburg</td>\n",
       "      <td>A60-A79</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154940</td>\n",
       "      <td>2020-03-19T00:00:00.000Z</td>\n",
       "      <td>15003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IdBundesland      Bundesland     Landkreis Altersgruppe Geschlecht  \\\n",
       "0            15  Sachsen-Anhalt  SK Magdeburg      A35-A59          M   \n",
       "1            15  Sachsen-Anhalt  SK Magdeburg      A35-A59          W   \n",
       "2            15  Sachsen-Anhalt  SK Magdeburg      A35-A59          W   \n",
       "3            15  Sachsen-Anhalt  SK Magdeburg      A60-A79          M   \n",
       "4            15  Sachsen-Anhalt  SK Magdeburg      A60-A79          M   \n",
       "\n",
       "   AnzahlFall  AnzahlTodesfall  ObjectId                Meldedatum  \\\n",
       "0           2                0    154936  2020-03-18T00:00:00.000Z   \n",
       "1           1                0    154937  2020-03-12T00:00:00.000Z   \n",
       "2           1                0    154938  2020-03-17T00:00:00.000Z   \n",
       "3           1                0    154939  2020-03-10T00:00:00.000Z   \n",
       "4           1                0    154940  2020-03-19T00:00:00.000Z   \n",
       "\n",
       "  IdLandkreis NUTS3 Flaeche Bevoelkerung maennlich weiblich Dichte  \n",
       "0       15003   NaN     NaN          NaN       NaN      NaN    NaN  \n",
       "1       15003   NaN     NaN          NaN       NaN      NaN    NaN  \n",
       "2       15003   NaN     NaN          NaN       NaN      NaN    NaN  \n",
       "3       15003   NaN     NaN          NaN       NaN      NaN    NaN  \n",
       "4       15003   NaN     NaN          NaN       NaN      NaN    NaN  "
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_time = pd.merge(left=df_time, right=BV, how='left', left_on='NUTS', right_on='NUTS3').drop(columns=['NUTS'])\n",
    "df_time.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_time.to_csv('data/df_time.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}