symptoms_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Symptom Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "import zeep\n",
    "from zeep import Client\n",
    "from lxml import etree\n",
    "from zeep import Plugin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Read in data\n",
    "df = pd.read_csv('symptoms.csv', sep=',',encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>[PathogenMitKrankheit].[Meldeweg Web71].[Meldeweg ID Web71].[MEMBER_CAPTION]</th>\n",
       "      <th>[PathogenMitKrankheit].[Meldeweg Web71].[Krankheit ID Web71].[MEMBER_CAPTION]</th>\n",
       "      <th>[Symptome].[ID].[ID].[MEMBER_CAPTION]</th>\n",
       "      <th>[ReportingDate].[YearWeek].[YearWeek].[MEMBER_CAPTION]</th>\n",
       "      <th>[Measures].[FallCount]</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2015-KW13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2015-KW33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2015-KW44</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2015-KW53</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW05</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW06</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW07</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW10</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW26</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW28</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW31</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW36</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW37</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW40</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW45</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW46</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW47</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW48</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW49</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2016-KW50</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2017-KW01</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2017-KW04</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>Gemäß IfSG</td>\n",
       "      <td>Adenovirus-K(eratok)onjunktivitis</td>\n",
       "      <td>Karunkelschwellung</td>\n",
       "      <td>2017-KW06</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146188</th>\n",
       "      <td>146189</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2003-KW50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146189</th>\n",
       "      <td>146190</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2005-KW48</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146190</th>\n",
       "      <td>146191</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2006-KW51</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146191</th>\n",
       "      <td>146192</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2009-KW26</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146192</th>\n",
       "      <td>146193</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2011-KW13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146193</th>\n",
       "      <td>146194</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2011-KW37</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146194</th>\n",
       "      <td>146195</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2012-KW33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146195</th>\n",
       "      <td>146196</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2012-KW38</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146196</th>\n",
       "      <td>146197</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2013-KW07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146197</th>\n",
       "      <td>146198</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2014-KW07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146198</th>\n",
       "      <td>146199</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2016-KW07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146199</th>\n",
       "      <td>146200</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2016-KW20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146200</th>\n",
       "      <td>146201</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>zentralnervöse Störungen</td>\n",
       "      <td>2017-KW31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146201</th>\n",
       "      <td>146202</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2005-KW50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146202</th>\n",
       "      <td>146203</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2010-KW47</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146203</th>\n",
       "      <td>146204</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2011-KW02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146204</th>\n",
       "      <td>146205</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2013-KW35</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146205</th>\n",
       "      <td>146206</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2014-KW11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146206</th>\n",
       "      <td>146207</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2016-KW18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146207</th>\n",
       "      <td>146208</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Chorioretinitis (Entzündung der Netzhaut und A...</td>\n",
       "      <td>2017-KW23</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146208</th>\n",
       "      <td>146209</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2002-KW25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146209</th>\n",
       "      <td>146210</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2004-KW07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146210</th>\n",
       "      <td>146211</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2011-KW16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146211</th>\n",
       "      <td>146212</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2011-KW37</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146212</th>\n",
       "      <td>146213</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2012-KW27</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146213</th>\n",
       "      <td>146214</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2013-KW24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146214</th>\n",
       "      <td>146215</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2014-KW24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146215</th>\n",
       "      <td>146216</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2015-KW51</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146216</th>\n",
       "      <td>146217</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2016-KW20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146217</th>\n",
       "      <td>146218</td>\n",
       "      <td>Gemäß länderspezifischer Meldeverordnung</td>\n",
       "      <td>Toxoplasmose</td>\n",
       "      <td>Enzephalitis (Gehirnentzündung)</td>\n",
       "      <td>2017-KW35</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>146218 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0  \\\n",
       "0                1   \n",
       "1                2   \n",
       "2                3   \n",
       "3                4   \n",
       "4                5   \n",
       "5                6   \n",
       "6                7   \n",
       "7                8   \n",
       "8                9   \n",
       "9               10   \n",
       "10              11   \n",
       "11              12   \n",
       "12              13   \n",
       "13              14   \n",
       "14              15   \n",
       "15              16   \n",
       "16              17   \n",
       "17              18   \n",
       "18              19   \n",
       "19              20   \n",
       "20              21   \n",
       "21              22   \n",
       "22              23   \n",
       "23              24   \n",
       "24              25   \n",
       "25              26   \n",
       "26              27   \n",
       "27              28   \n",
       "28              29   \n",
       "29              30   \n",
       "...            ...   \n",
       "146188      146189   \n",
       "146189      146190   \n",
       "146190      146191   \n",
       "146191      146192   \n",
       "146192      146193   \n",
       "146193      146194   \n",
       "146194      146195   \n",
       "146195      146196   \n",
       "146196      146197   \n",
       "146197      146198   \n",
       "146198      146199   \n",
       "146199      146200   \n",
       "146200      146201   \n",
       "146201      146202   \n",
       "146202      146203   \n",
       "146203      146204   \n",
       "146204      146205   \n",
       "146205      146206   \n",
       "146206      146207   \n",
       "146207      146208   \n",
       "146208      146209   \n",
       "146209      146210   \n",
       "146210      146211   \n",
       "146211      146212   \n",
       "146212      146213   \n",
       "146213      146214   \n",
       "146214      146215   \n",
       "146215      146216   \n",
       "146216      146217   \n",
       "146217      146218   \n",
       "\n",
       "       [PathogenMitKrankheit].[Meldeweg Web71].[Meldeweg ID Web71].[MEMBER_CAPTION]  \\\n",
       "0                                              Gemäß IfSG                             \n",
       "1                                              Gemäß IfSG                             \n",
       "2                                              Gemäß IfSG                             \n",
       "3                                              Gemäß IfSG                             \n",
       "4                                              Gemäß IfSG                             \n",
       "5                                              Gemäß IfSG                             \n",
       "6                                              Gemäß IfSG                             \n",
       "7                                              Gemäß IfSG                             \n",
       "8                                              Gemäß IfSG                             \n",
       "9                                              Gemäß IfSG                             \n",
       "10                                             Gemäß IfSG                             \n",
       "11                                             Gemäß IfSG                             \n",
       "12                                             Gemäß IfSG                             \n",
       "13                                             Gemäß IfSG                             \n",
       "14                                             Gemäß IfSG                             \n",
       "15                                             Gemäß IfSG                             \n",
       "16                                             Gemäß IfSG                             \n",
       "17                                             Gemäß IfSG                             \n",
       "18                                             Gemäß IfSG                             \n",
       "19                                             Gemäß IfSG                             \n",
       "20                                             Gemäß IfSG                             \n",
       "21                                             Gemäß IfSG                             \n",
       "22                                             Gemäß IfSG                             \n",
       "23                                             Gemäß IfSG                             \n",
       "24                                             Gemäß IfSG                             \n",
       "25                                             Gemäß IfSG                             \n",
       "26                                             Gemäß IfSG                             \n",
       "27                                             Gemäß IfSG                             \n",
       "28                                             Gemäß IfSG                             \n",
       "29                                             Gemäß IfSG                             \n",
       "...                                                   ...                             \n",
       "146188           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146189           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146190           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146191           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146192           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146193           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146194           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146195           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146196           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146197           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146198           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146199           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146200           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146201           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146202           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146203           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146204           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146205           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146206           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146207           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146208           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146209           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146210           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146211           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146212           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146213           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146214           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146215           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146216           Gemäß länderspezifischer Meldeverordnung                             \n",
       "146217           Gemäß länderspezifischer Meldeverordnung                             \n",
       "\n",
       "       [PathogenMitKrankheit].[Meldeweg Web71].[Krankheit ID Web71].[MEMBER_CAPTION]  \\\n",
       "0                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "1                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "2                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "3                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "4                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "5                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "6                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "7                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "8                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "9                       Adenovirus-K(eratok)onjunktivitis                              \n",
       "10                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "11                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "12                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "13                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "14                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "15                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "16                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "17                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "18                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "19                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "20                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "21                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "22                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "23                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "24                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "25                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "26                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "27                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "28                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "29                      Adenovirus-K(eratok)onjunktivitis                              \n",
       "...                                                   ...                              \n",
       "146188                                       Toxoplasmose                              \n",
       "146189                                       Toxoplasmose                              \n",
       "146190                                       Toxoplasmose                              \n",
       "146191                                       Toxoplasmose                              \n",
       "146192                                       Toxoplasmose                              \n",
       "146193                                       Toxoplasmose                              \n",
       "146194                                       Toxoplasmose                              \n",
       "146195                                       Toxoplasmose                              \n",
       "146196                                       Toxoplasmose                              \n",
       "146197                                       Toxoplasmose                              \n",
       "146198                                       Toxoplasmose                              \n",
       "146199                                       Toxoplasmose                              \n",
       "146200                                       Toxoplasmose                              \n",
       "146201                                       Toxoplasmose                              \n",
       "146202                                       Toxoplasmose                              \n",
       "146203                                       Toxoplasmose                              \n",
       "146204                                       Toxoplasmose                              \n",
       "146205                                       Toxoplasmose                              \n",
       "146206                                       Toxoplasmose                              \n",
       "146207                                       Toxoplasmose                              \n",
       "146208                                       Toxoplasmose                              \n",
       "146209                                       Toxoplasmose                              \n",
       "146210                                       Toxoplasmose                              \n",
       "146211                                       Toxoplasmose                              \n",
       "146212                                       Toxoplasmose                              \n",
       "146213                                       Toxoplasmose                              \n",
       "146214                                       Toxoplasmose                              \n",
       "146215                                       Toxoplasmose                              \n",
       "146216                                       Toxoplasmose                              \n",
       "146217                                       Toxoplasmose                              \n",
       "\n",
       "                    [Symptome].[ID].[ID].[MEMBER_CAPTION]  \\\n",
       "0                                      Karunkelschwellung   \n",
       "1                                      Karunkelschwellung   \n",
       "2                                      Karunkelschwellung   \n",
       "3                                      Karunkelschwellung   \n",
       "4                                      Karunkelschwellung   \n",
       "5                                      Karunkelschwellung   \n",
       "6                                      Karunkelschwellung   \n",
       "7                                      Karunkelschwellung   \n",
       "8                                      Karunkelschwellung   \n",
       "9                                      Karunkelschwellung   \n",
       "10                                     Karunkelschwellung   \n",
       "11                                     Karunkelschwellung   \n",
       "12                                     Karunkelschwellung   \n",
       "13                                     Karunkelschwellung   \n",
       "14                                     Karunkelschwellung   \n",
       "15                                     Karunkelschwellung   \n",
       "16                                     Karunkelschwellung   \n",
       "17                                     Karunkelschwellung   \n",
       "18                                     Karunkelschwellung   \n",
       "19                                     Karunkelschwellung   \n",
       "20                                     Karunkelschwellung   \n",
       "21                                     Karunkelschwellung   \n",
       "22                                     Karunkelschwellung   \n",
       "23                                     Karunkelschwellung   \n",
       "24                                     Karunkelschwellung   \n",
       "25                                     Karunkelschwellung   \n",
       "26                                     Karunkelschwellung   \n",
       "27                                     Karunkelschwellung   \n",
       "28                                     Karunkelschwellung   \n",
       "29                                     Karunkelschwellung   \n",
       "...                                                   ...   \n",
       "146188                           zentralnervöse Störungen   \n",
       "146189                           zentralnervöse Störungen   \n",
       "146190                           zentralnervöse Störungen   \n",
       "146191                           zentralnervöse Störungen   \n",
       "146192                           zentralnervöse Störungen   \n",
       "146193                           zentralnervöse Störungen   \n",
       "146194                           zentralnervöse Störungen   \n",
       "146195                           zentralnervöse Störungen   \n",
       "146196                           zentralnervöse Störungen   \n",
       "146197                           zentralnervöse Störungen   \n",
       "146198                           zentralnervöse Störungen   \n",
       "146199                           zentralnervöse Störungen   \n",
       "146200                           zentralnervöse Störungen   \n",
       "146201  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146202  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146203  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146204  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146205  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146206  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146207  Chorioretinitis (Entzündung der Netzhaut und A...   \n",
       "146208                    Enzephalitis (Gehirnentzündung)   \n",
       "146209                    Enzephalitis (Gehirnentzündung)   \n",
       "146210                    Enzephalitis (Gehirnentzündung)   \n",
       "146211                    Enzephalitis (Gehirnentzündung)   \n",
       "146212                    Enzephalitis (Gehirnentzündung)   \n",
       "146213                    Enzephalitis (Gehirnentzündung)   \n",
       "146214                    Enzephalitis (Gehirnentzündung)   \n",
       "146215                    Enzephalitis (Gehirnentzündung)   \n",
       "146216                    Enzephalitis (Gehirnentzündung)   \n",
       "146217                    Enzephalitis (Gehirnentzündung)   \n",
       "\n",
       "       [ReportingDate].[YearWeek].[YearWeek].[MEMBER_CAPTION]  \\\n",
       "0                                               2015-KW13       \n",
       "1                                               2015-KW33       \n",
       "2                                               2015-KW44       \n",
       "3                                               2015-KW53       \n",
       "4                                               2016-KW05       \n",
       "5                                               2016-KW06       \n",
       "6                                               2016-KW07       \n",
       "7                                               2016-KW08       \n",
       "8                                               2016-KW10       \n",
       "9                                               2016-KW11       \n",
       "10                                              2016-KW22       \n",
       "11                                              2016-KW25       \n",
       "12                                              2016-KW26       \n",
       "13                                              2016-KW28       \n",
       "14                                              2016-KW29       \n",
       "15                                              2016-KW30       \n",
       "16                                              2016-KW31       \n",
       "17                                              2016-KW32       \n",
       "18                                              2016-KW36       \n",
       "19                                              2016-KW37       \n",
       "20                                              2016-KW40       \n",
       "21                                              2016-KW45       \n",
       "22                                              2016-KW46       \n",
       "23                                              2016-KW47       \n",
       "24                                              2016-KW48       \n",
       "25                                              2016-KW49       \n",
       "26                                              2016-KW50       \n",
       "27                                              2017-KW01       \n",
       "28                                              2017-KW04       \n",
       "29                                              2017-KW06       \n",
       "...                                                   ...       \n",
       "146188                                          2003-KW50       \n",
       "146189                                          2005-KW48       \n",
       "146190                                          2006-KW51       \n",
       "146191                                          2009-KW26       \n",
       "146192                                          2011-KW13       \n",
       "146193                                          2011-KW37       \n",
       "146194                                          2012-KW33       \n",
       "146195                                          2012-KW38       \n",
       "146196                                          2013-KW07       \n",
       "146197                                          2014-KW07       \n",
       "146198                                          2016-KW07       \n",
       "146199                                          2016-KW20       \n",
       "146200                                          2017-KW31       \n",
       "146201                                          2005-KW50       \n",
       "146202                                          2010-KW47       \n",
       "146203                                          2011-KW02       \n",
       "146204                                          2013-KW35       \n",
       "146205                                          2014-KW11       \n",
       "146206                                          2016-KW18       \n",
       "146207                                          2017-KW23       \n",
       "146208                                          2002-KW25       \n",
       "146209                                          2004-KW07       \n",
       "146210                                          2011-KW16       \n",
       "146211                                          2011-KW37       \n",
       "146212                                          2012-KW27       \n",
       "146213                                          2013-KW24       \n",
       "146214                                          2014-KW24       \n",
       "146215                                          2015-KW51       \n",
       "146216                                          2016-KW20       \n",
       "146217                                          2017-KW35       \n",
       "\n",
       "        [Measures].[FallCount]  \n",
       "0                            1  \n",
       "1                            1  \n",
       "2                            1  \n",
       "3                            1  \n",
       "4                            1  \n",
       "5                            5  \n",
       "6                            3  \n",
       "7                            4  \n",
       "8                            7  \n",
       "9                            5  \n",
       "10                           1  \n",
       "11                           1  \n",
       "12                           5  \n",
       "13                           5  \n",
       "14                           1  \n",
       "15                           1  \n",
       "16                           5  \n",
       "17                           1  \n",
       "18                           1  \n",
       "19                           1  \n",
       "20                           1  \n",
       "21                           1  \n",
       "22                          10  \n",
       "23                           2  \n",
       "24                           3  \n",
       "25                           1  \n",
       "26                           4  \n",
       "27                           2  \n",
       "28                          15  \n",
       "29                           1  \n",
       "...                        ...  \n",
       "146188                       1  \n",
       "146189                       1  \n",
       "146190                       1  \n",
       "146191                       1  \n",
       "146192                       1  \n",
       "146193                       1  \n",
       "146194                       1  \n",
       "146195                       1  \n",
       "146196                       1  \n",
       "146197                       1  \n",
       "146198                       1  \n",
       "146199                       1  \n",
       "146200                       1  \n",
       "146201                       1  \n",
       "146202                       1  \n",
       "146203                       2  \n",
       "146204                       1  \n",
       "146205                       1  \n",
       "146206                       1  \n",
       "146207                       1  \n",
       "146208                       1  \n",
       "146209                       1  \n",
       "146210                       1  \n",
       "146211                       1  \n",
       "146212                       1  \n",
       "146213                       1  \n",
       "146214                       1  \n",
       "146215                       1  \n",
       "146216                       1  \n",
       "146217                       1  \n",
       "\n",
       "[146218 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Drop unecessary columns\n",
    "df = df.drop(columns=['Unnamed: 0','[PathogenMitKrankheit].[Meldeweg Web71].[Meldeweg ID Web71].[MEMBER_CAPTION]'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Give columns more comprehensible names\n",
    "df = df.rename(columns={'[PathogenMitKrankheit].[Meldeweg Web71].[Krankheit ID Web71].[MEMBER_CAPTION]': 'Krankheit', '[Symptome].[ID].[ID].[MEMBER_CAPTION]': 'Symptom', '[ReportingDate].[YearWeek].[YearWeek].[MEMBER_CAPTION]': 'Kalenderwoche', '[Measures].[FallCount]': 'Anzahl'}) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Since we want to focus only on Norovirus, Infulenza, and Windpocken, we throw all the other data away\n",
    "df_selection = df[df.isin({'Krankheit': ['Norovirus-Gastroenteritis','Influenza', 'Windpocken']})['Krankheit']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#df_selection_symptoms = df_selection.groupby(['Krankheit','Symptom'])['Anzahl'].sum().reset_index()\n",
    "#df_selection_symptoms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Further dropping\n",
    "df_selection = df_selection.drop(columns='Krankheit')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Symptom</th>\n",
       "      <th>Kalenderwoche</th>\n",
       "      <th>Anzahl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3118</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2001-KW01</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6105</th>\n",
       "      <td>andere Symptome</td>\n",
       "      <td>2001-KW01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2055</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3904</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2001-KW01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2001-KW01</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3905</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2001-KW02</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3119</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2001-KW02</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2001-KW02</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2056</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW02</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3120</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2001-KW03</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2001-KW03</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3906</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2001-KW03</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6106</th>\n",
       "      <td>andere Symptome</td>\n",
       "      <td>2001-KW03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2057</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW03</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3907</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2001-KW04</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3121</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2001-KW04</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6107</th>\n",
       "      <td>andere Symptome</td>\n",
       "      <td>2001-KW04</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1623</th>\n",
       "      <td>Durchfall und/oder Erbrechen</td>\n",
       "      <td>2001-KW04</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2058</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW04</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3122</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2001-KW05</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2059</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW05</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2001-KW05</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6108</th>\n",
       "      <td>andere Symptome</td>\n",
       "      <td>2001-KW05</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3908</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2001-KW05</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2060</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW06</td>\n",
       "      <td>291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3909</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2001-KW06</td>\n",
       "      <td>230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2001-KW06</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3123</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2001-KW06</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6109</th>\n",
       "      <td>andere Symptome</td>\n",
       "      <td>2001-KW06</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2061</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2001-KW07</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3116</th>\n",
       "      <td>Gliederschmerzen</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5399</th>\n",
       "      <td>Schmerzen, einseitig auf ein Hautsegment lokal...</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1109</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6103</th>\n",
       "      <td>akutes schweres Atemnotsyndrom (ARDS)</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3902</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>10900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4892</th>\n",
       "      <td>Pneumonie (Lungenentzündung)</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6015</th>\n",
       "      <td>akuter Krankheitsbeginn</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>10018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4688</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>8240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1368</th>\n",
       "      <td>Ausschlag, einseitig auf Hautsegment beschränk...</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5150</th>\n",
       "      <td>Schmerzen im betroffenen Bereich (Zosterneural...</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6714</th>\n",
       "      <td>beatmungspflichtige Atemwegserkrankung</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1621</th>\n",
       "      <td>Brennen, Juckreiz</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>Durchfall und/oder Erbrechen</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>3371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit Flecken...</td>\n",
       "      <td>2018-KW05</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1110</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit gleichz...</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2872</th>\n",
       "      <td>Fieber</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>9524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2054</th>\n",
       "      <td>Fetales (kongenitales) Varizellensyndrom</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>Durchfall und/oder Erbrechen</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>2268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3903</th>\n",
       "      <td>Husten</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>9077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1622</th>\n",
       "      <td>Brennen, Juckreiz</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1369</th>\n",
       "      <td>Ausschlag, einseitig auf Hautsegment beschränk...</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>Ausschlag an Haut oder Schleimhaut mit Flecken...</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4689</th>\n",
       "      <td>Muskel-, Glieder-, Rücken- oder Kopfschmerzen</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>6543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4893</th>\n",
       "      <td>Pneumonie (Lungenentzündung)</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6104</th>\n",
       "      <td>akutes schweres Atemnotsyndrom (ARDS)</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6016</th>\n",
       "      <td>akuter Krankheitsbeginn</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>8253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5151</th>\n",
       "      <td>Schmerzen im betroffenen Bereich (Zosterneural...</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5400</th>\n",
       "      <td>Schmerzen, einseitig auf ein Hautsegment lokal...</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3117</th>\n",
       "      <td>Gliederschmerzen</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6715</th>\n",
       "      <td>beatmungspflichtige Atemwegserkrankung</td>\n",
       "      <td>2018-KW06</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6716 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Symptom Kalenderwoche  Anzahl\n",
       "3118                                             Husten     2001-KW01       2\n",
       "6105                                    andere Symptome     2001-KW01       1\n",
       "2055                                             Fieber     2001-KW01       3\n",
       "3904      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW01       1\n",
       "253   Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW01      10\n",
       "3905      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW02       5\n",
       "3119                                             Husten     2001-KW02       3\n",
       "254   Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW02      15\n",
       "2056                                             Fieber     2001-KW02       6\n",
       "3120                                             Husten     2001-KW03       7\n",
       "255   Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW03      26\n",
       "3906      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW03       6\n",
       "6106                                    andere Symptome     2001-KW03       2\n",
       "2057                                             Fieber     2001-KW03      13\n",
       "3907      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW04      34\n",
       "3121                                             Husten     2001-KW04      34\n",
       "6107                                    andere Symptome     2001-KW04      10\n",
       "1623                       Durchfall und/oder Erbrechen     2001-KW04       1\n",
       "2058                                             Fieber     2001-KW04      48\n",
       "3122                                             Husten     2001-KW05     100\n",
       "2059                                             Fieber     2001-KW05     151\n",
       "256   Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW05       7\n",
       "6108                                    andere Symptome     2001-KW05      50\n",
       "3908      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW05      96\n",
       "2060                                             Fieber     2001-KW06     291\n",
       "3909      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW06     230\n",
       "257   Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW06       7\n",
       "3123                                             Husten     2001-KW06     220\n",
       "6109                                    andere Symptome     2001-KW06      44\n",
       "2061                                             Fieber     2001-KW07     181\n",
       "...                                                 ...           ...     ...\n",
       "3116                                   Gliederschmerzen     2018-KW05      22\n",
       "5399  Schmerzen, einseitig auf ein Hautsegment lokal...     2018-KW05      10\n",
       "1109  Ausschlag an Haut oder Schleimhaut mit gleichz...     2018-KW05     488\n",
       "6103              akutes schweres Atemnotsyndrom (ARDS)     2018-KW05      63\n",
       "3902                                             Husten     2018-KW05   10900\n",
       "4892                       Pneumonie (Lungenentzündung)     2018-KW05     143\n",
       "6015                            akuter Krankheitsbeginn     2018-KW05   10018\n",
       "4688      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2018-KW05    8240\n",
       "1368  Ausschlag, einseitig auf Hautsegment beschränk...     2018-KW05     134\n",
       "5150  Schmerzen im betroffenen Bereich (Zosterneural...     2018-KW05     109\n",
       "6714             beatmungspflichtige Atemwegserkrankung     2018-KW05      50\n",
       "1621                                  Brennen, Juckreiz     2018-KW05      68\n",
       "1999                       Durchfall und/oder Erbrechen     2018-KW05    3371\n",
       "251   Ausschlag an Haut oder Schleimhaut mit Flecken...     2018-KW05      14\n",
       "1110  Ausschlag an Haut oder Schleimhaut mit gleichz...     2018-KW06     398\n",
       "2872                                             Fieber     2018-KW06    9524\n",
       "2054           Fetales (kongenitales) Varizellensyndrom     2018-KW06       3\n",
       "2000                       Durchfall und/oder Erbrechen     2018-KW06    2268\n",
       "3903                                             Husten     2018-KW06    9077\n",
       "1622                                  Brennen, Juckreiz     2018-KW06      40\n",
       "1369  Ausschlag, einseitig auf Hautsegment beschränk...     2018-KW06      89\n",
       "252   Ausschlag an Haut oder Schleimhaut mit Flecken...     2018-KW06      27\n",
       "4689      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2018-KW06    6543\n",
       "4893                       Pneumonie (Lungenentzündung)     2018-KW06     138\n",
       "6104              akutes schweres Atemnotsyndrom (ARDS)     2018-KW06      49\n",
       "6016                            akuter Krankheitsbeginn     2018-KW06    8253\n",
       "5151  Schmerzen im betroffenen Bereich (Zosterneural...     2018-KW06      77\n",
       "5400  Schmerzen, einseitig auf ein Hautsegment lokal...     2018-KW06       8\n",
       "3117                                   Gliederschmerzen     2018-KW06      19\n",
       "6715             beatmungspflichtige Atemwegserkrankung     2018-KW06      28\n",
       "\n",
       "[6716 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_selection_pivot = df_selection.groupby(['Symptom','Kalenderwoche'])['Anzahl'].sum().reset_index()\n",
    "df_selection_pivot.sort_values('Kalenderwoche')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#get a list of all relevant symptoms\n",
    "df_selection_all_symptoms = df_selection.groupby(['Symptom'])['Anzahl'].sum().reset_index()['Symptom'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#for each combination of symptom and week:\n",
    "#if the combination is not in the dataframe, then insert a row with this combination and 'Anzahl' = 0\n",
    "new_rows = []\n",
    "for week in df['Kalenderwoche'].drop_duplicates().sort_values():\n",
    "    for symptom in df_selection_all_symptoms:\n",
    "        sample_week = df_selection_pivot[df_selection_pivot['Kalenderwoche'] == week]\n",
    "        if not sample_week.isin({'Symptom': [symptom]})['Symptom'].any():\n",
    "            new_row = [symptom, week, 0]\n",
    "            new_rows.append(new_row)\n",
    "            \n",
    "            \n",
    "new_values = pd.DataFrame(new_rows, columns=['Symptom','Kalenderwoche','Anzahl'])\n",
    "\n",
    "#append these illnesses with 0 'Anzahl'\n",
    "df_selection_pivot= df_selection_pivot.append(new_values)\n",
    "df_weekly_symptoms = df_selection_pivot.sort_values('Kalenderwoche').reset_index(drop=True)\n",
    "df_weekly_symptoms = df_weekly_symptoms.sort_values(['Kalenderwoche','Symptom'],ascending=[True,True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                              Symptom Kalenderwoche  Anzahl\n",
      "0   Ausschlag an Haut oder Schleimhaut mit Flecken...     2001-KW01       0\n",
      "1   Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW01      10\n",
      "2   Ausschlag, einseitig auf Hautsegment beschränk...     2001-KW01       0\n",
      "3                                   Brennen, Juckreiz     2001-KW01       0\n",
      "4                        Durchfall und/oder Erbrechen     2001-KW01       0\n",
      "5                   Durchfall, nicht näher bezeichnet     2001-KW01       0\n",
      "6            Fetales (kongenitales) Varizellensyndrom     2001-KW01       0\n",
      "7                                              Fieber     2001-KW01       3\n",
      "8                                    Gliederschmerzen     2001-KW01       0\n",
      "9                                              Husten     2001-KW01       2\n",
      "10      Muskel-, Glieder-, Rücken- oder Kopfschmerzen     2001-KW01       1\n",
      "11                       Pneumonie (Lungenentzündung)     2001-KW01       0\n",
      "12  Schmerzen im betroffenen Bereich (Zosterneural...     2001-KW01       0\n",
      "13  Schmerzen, einseitig auf ein Hautsegment lokal...     2001-KW01       0\n",
      "14                            akuter Krankheitsbeginn     2001-KW01       0\n",
      "15              akutes schweres Atemnotsyndrom (ARDS)     2001-KW01       0\n",
      "16                                    andere Symptome     2001-KW01       1\n",
      "17             beatmungspflichtige Atemwegserkrankung     2001-KW01       0\n",
      "18  Ausschlag an Haut oder Schleimhaut mit Flecken...     2001-KW02       0\n",
      "19  Ausschlag an Haut oder Schleimhaut mit gleichz...     2001-KW02      15\n"
     ]
    }
   ],
   "source": [
    "# Reset the index for ease of further processing\n",
    "df_weekly_symptoms = df_weekly_symptoms.reset_index(drop=True)\n",
    "print(df_weekly_symptoms.head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_weekly_symptoms.to_pickle('weekly_symptoms.pkl')"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}