Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The lab_black extension is already loaded. To reload it, use:\n",
      "  %reload_ext lab_black\n"
     ]
    }
   ],
   "source": [
    "%load_ext lab_black"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Baden-Württem­berg\n",
      "498.911\n",
      "289\n",
      "1.857\n",
      "17\n",
      "10.136\n",
      "Bayern\n",
      "645.785\n",
      "236\n",
      "1.778\n",
      "14\n",
      "15.150\n",
      "Berlin\n",
      "179.818\n",
      "64\n",
      "392\n",
      "11\n",
      "3.535\n",
      "Branden­burg\n",
      "108.608\n",
      "3\n",
      "131\n",
      "5\n",
      "3.765\n",
      "Bremen\n",
      "27.363\n",
      "8\n",
      "72\n",
      "11\n",
      "481\n",
      "Hamburg\n",
      "77.015\n",
      "52\n",
      "221\n",
      "12\n",
      "1.582\n",
      "Hessen\n",
      "289.817\n",
      "127\n",
      "855\n",
      "14\n",
      "7.460\n",
      "Meck­lenburg-\n",
      "Vor­pommern\n",
      "44.097\n",
      "8\n",
      "65\n",
      "4\n",
      "1.148\n",
      "Nieder­sachsen\n",
      "260.448\n",
      "91\n",
      "508\n",
      "6\n",
      "5.721\n",
      "Nord­rhein-West­falen\n",
      "814.114\n",
      "236\n",
      "2.278\n",
      "13\n",
      "17.022\n",
      "Rhein­land-Pfalz\n",
      "154.714\n",
      "90\n",
      "517\n",
      "13\n",
      "3.830\n",
      "Saarland\n",
      "41.324\n",
      "20\n",
      "162\n",
      "16\n",
      "1.018\n",
      "Sachsen\n",
      "285.537\n",
      "23\n",
      "288\n",
      "7\n",
      "9.998\n",
      "Sachsen-Anhalt\n",
      "99.133\n",
      "23\n",
      "111\n",
      "5\n",
      "3.408\n",
      "Schles­wig-Holstein\n",
      "63.692\n",
      "27\n",
      "173\n",
      "6\n",
      "1.618\n",
      "Thüringen\n",
      "128.579\n",
      "33\n",
      "236\n",
      "11\n",
      "4.307\n",
      "Gesamt\n",
      "3.718.955\n",
      "1.330\n",
      "9.644\n",
      "12\n",
      "90.179\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from lxml import html\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# from pymaran import gSheets,mail\n",
    "import datetime\n",
    "\n",
    "url = \"https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html\"\n",
    "xPath = '//*[@id=\"main\"]/div[1]/table/*'\n",
    "tableElement = 2\n",
    "autoDate = True\n",
    "manualDate = \"\"\n",
    "n_cols = 6\n",
    "\n",
    "\n",
    "### https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html\n",
    "pageContent = requests.get(url)\n",
    "tree = html.fromstring(pageContent.content)\n",
    "table = tree.xpath(xPath)\n",
    "tbody = table[tableElement]\n",
    "df_col = range(n_cols)\n",
    "\n",
    "for tr in table[2]:\n",
    "    for td in tr:\n",
    "        print(td.text_content())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<!DOCTYPE html>\n",
      "<!--[if lt IE 7]> <html class=\"no-js ie6 oldie\" lang=\"en-US\"> <![endif]-->\n",
      "<!--[if IE 7]>    <html class=\"no-js ie7 oldie\" lang=\"en-US\"> <![endif]-->\n",
      "<!--[if IE 8]>    <html class=\"no-js ie8 oldie\" lang=\"en-US\"> <![endif]-->\n",
      "<!--[if gt IE 8]><!--> <html class=\"no-js\" lang=\"en-US\"> <!--<![endif]-->\n",
      "<head>\n",
      "<title>Attention Required! | Cloudflare</title>\n",
      "<meta name=\"captcha-bypass\" id=\"captcha-bypass\" />\n",
      "<meta charset=\"UTF-8\" />\n",
      "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />\n",
      "<meta http-equiv=\"X-UA-Compatible\" content=\"IE=Edge,chrome=1\" />\n",
      "<meta name=\"robots\" content=\"noindex, nofollow\" />\n",
      "<meta name=\"viewport\" content=\"width=device-width,initial-scale=1\" />\n",
      "<link rel=\"stylesheet\" id=\"cf_styles-css\" href=\"/cdn-cgi/styles/cf.errors.css\" type=\"text/css\" media=\"screen,projection\" />\n",
      "<!--[if lt IE 9]><link rel=\"stylesheet\" id='cf_styles-ie-css' href=\"/cdn-cgi/styles/cf.errors.ie.css\" type=\"text/css\" media=\"screen,projection\" /><![endif]-->\n",
      "<style type=\"text/css\">body{margin:0;padding:0}</style>\n",
      "\n",
      "\n",
      "<!--[if gte IE 10]><!-->\n",
      "<script>\n",
      "  if (!navigator.cookieEnabled) {\n",
      "    window.addEventListener('DOMContentLoaded', function () {\n",
      "      var cookieEl = document.getElementById('cookie-alert');\n",
      "      cookieEl.style.display = 'block';\n",
      "    })\n",
      "  }\n",
      "</script>\n",
      "<!--<![endif]-->\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "<style type=\"text/css\">\n",
      "  #cf-wrapper #spinner {width:69px; margin:  auto;}\n",
      "  #cf-wrapper #cf-please-wait{text-align:center}\n",
      "  .attribution {margin-top: 32px;}\n",
      "  .bubbles { background-color: #f58220; width:20px; height: 20px; margin:2px; border-radius:100%; display:inline-block; }\n",
      "  #cf-wrapper #challenge-form { padding-top:25px; padding-bottom:25px; }\n",
      "  #cf-hcaptcha-container { text-align:center;}\n",
      "  #cf-hcaptcha-container iframe { display: inline-block;}\n",
      "  @keyframes fader     { 0% {opacity: 0.2;} 50% {opacity: 1.0;} 100% {opacity: 0.2;} }\n",
      "  #cf-wrapper #cf-bubbles { width:69px; }\n",
      "  @-webkit-keyframes fader { 0% {opacity: 0.2;} 50% {opacity: 1.0;} 100% {opacity: 0.2;} }\n",
      "  #cf-bubbles > .bubbles { animation: fader 1.6s infinite;}\n",
      "  #cf-bubbles > .bubbles:nth-child(2) { animation-delay: .2s;}\n",
      "  #cf-bubbles > .bubbles:nth-child(3) { animation-delay: .4s;}\n",
      "</style>\n",
      "</head>\n",
      "<body>\n",
      "  <div id=\"cf-wrapper\">\n",
      "    <div class=\"cf-alert cf-alert-error cf-cookie-error\" id=\"cookie-alert\" data-translate=\"enable_cookies\">Please enable cookies.</div>\n",
      "    <div id=\"cf-error-details\" class=\"cf-error-details-wrapper\">\n",
      "      <div class=\"cf-wrapper cf-header cf-error-overview\">\n",
      "        <h1 data-translate=\"challenge_headline\">One more step</h1>\n",
      "        <h2 class=\"cf-subheadline\"><span data-translate=\"complete_sec_check\">Please complete the security check to access</span> www.rki.de</h2>\n",
      "      </div><!-- /.header -->\n",
      "      \n",
      "      <div class=\"cf-section cf-highlight cf-captcha-container\">\n",
      "        <div class=\"cf-wrapper\">\n",
      "          <div class=\"cf-columns two\">\n",
      "            <div class=\"cf-column\">\n",
      "            \n",
      "              <div class=\"cf-highlight-inverse cf-form-stacked\">\n",
      "                <form class=\"challenge-form\" id=\"challenge-form\" action=\"/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html?__cf_chl_captcha_tk__=0c8d4e129ffa9b5ee39102fc83aef459f9aaded0-1603624995-0-Ab6GSdKr0ftByfmZdXMQilEuchQFicgf0pL69ZgtkHcwCIfiOM8vGP0I08gPLBMYjTYhW39B2TN0ZxNnWASJ3O7AbXb-0QNOTHB706GW5VRd1BKHEPMGAA6hMIHQtAh3xgOFel5UzrPz7ANLYVnO03L0vLjtG9GBT8XB23JmTc8PQxtxVBsXx-iu5HiNl1X2ojdts1mi87nmFhQM3DCygWTSha9nsgXlyzbm9TS80xnFj-2vorsdfcw_O-rA3iWA1vLrwKlEi-TlZojkm1Vq7pdGd0ZlM6eUJII4jKX3ADo9XBSaVwKzLkFRSvgGyLennLFdIsIrGEzwxYguz9wJ62Wt4MsELvTaTzKhKCEjz54IsM9COJSYOkmZ65epnEgXBKzCMAyZBGWB0NxTwhSrV0MjuGFfF5CCZqnosEcccKZHToGl5UylcwBXGBrrjSADFwPYdtOh0DCee5edGZHsXAnCjbDgpJMEeZx7A071sI73yJ0jgz1_8JxH4mNbBAE-nTf0Wo5chOo5eOqRRkw7H53tWtZhlXktOtekFCVFNF_IfDJVpcx1M7Yjt0jfxL4UglHt_ji2lozv-q9GtqkiQBeclvlDUyrx2oH9l14R_XYJLj2vnn36ff0H3u-IpeQfig\" method=\"POST\" enctype=\"application/x-www-form-urlencoded\">\n",
      "  \n",
      "  <input type=\"hidden\" name=\"r\" value=\"a0a7e47ee2310d88427a967a6ad6e3d754512f93-1603624995-0-AQ21TASuxwuzGyitO03hY3RcIHSFqOyfn7L2ZVGZoEi1lyxmKw9awYzyDBixHLAK+D6PmVeoskDSG3MhVP0U9BHDCI2lnxiDlgkFW32iwIMo0mEayesO/jUmC9gej5o5e2V5YOw3m1DD0BonYzgvFFTzSrGSr73GdTy9knpp18WPJ0winx/OU9nZV0xKdYhQ3R//HztzPpWWo2cGNJ3ceRJz5xEcnGKN4M5DNxBmpzlf7ULdsEXtc/ucLpoTr8RDIe65AljNxwUqpn1FunS97CEG8ejcqmyEKal5ak+BMIj4GaO+2ZM/o1uOMTwcMlKcr/2z8b8SazLDOZ/B0vlPlU0O3z4jvbkSHCXuxn1gt9/8Hqaa7E0ecCoQtNsJs7U6qXSPFrH9PEZJJDLO8x/CZCCK43nBxbPw6sEpIU/zGXIet7jveRreMIlVdgHV6xFwrn4mLsUr1CYCSaMhN3kEJYM3R7sWPZIfYBVgNKr7eZprSccapPHyxrV3cYcBY4twpDjBLQTOu0C6QBcWR7gdlmYe+XqJvKCJkC4af812JW66EA/2K9b4jHPni7zj9u2uzrkoSVc/7WPFVXTBpYCR8IHXBm7xtnHcS932CMdgh1JiNsyBtYnR5EV8sfp5D4DLc5XgniXmDXfsOb3dZfXdmVht1DTCB6m4hMtTYlUk0pHXIJKHlZUUsPqctIeJ36+ugU6Yym81GlLSQ9ye1j5NYOT86yAU/mWFFdLdeEmdAlFlBy2KY+GSEfajlWMD3RUMGICmwtOQhPJukoVuWt8yucqlTTYVePN2D0hi/InuGC8l9UAT2seuz2kCRfg3rk/SWpvfUdJOTQPGaT8hykoY+Xdj3DA8dOvZzszbv0TLfTUDZcjQtGaXxTONLRChvT1GUelzZaIoyGAfikQucP7pUhnASF1Pj9/XdgOb9jTKyBoLmiDXOxMbWijI3xUCvkTb06oeqP+lp/58l+QMB0wtwsvj9c+dtBKj4kfcacCFhDVBY7q5TliGa/iUlaHPDmN3bXtCVRIKrH4AjgT1GAZ+RgCg2Fbm4olNLnyuPM62aLDZ2j07AiYAGIko/gjz1W9Pkai0ZftXzKwUREt6JrDqDJopfZHTK7MNyIHpLWufMUWpqP6U6uLGzpEcBt0OREpIPEQFZjvvMOQ5zeEoUmBsdX5WK4Df8mwWeRycMBrSrXIOk9zH3m8gi/vd0UDK6jbwj/eW6ChZAYnVlXxkHmrYIob1Yth2xSqJeGUnjd01hoLg\">\n",
      "  <input type=\"hidden\" name=\"cf_captcha_kind\" value=\"h\">\n",
      "  <input type=\"hidden\" name=\"vc\" value=\"\">\n",
      "  \n",
      "  <script type=\"text/javascript\" src=\"/cdn-cgi/scripts/hcaptcha.challenge.js\" data-type=\"normal\" data-ray=\"5e7b907ebe8bf589\" async data-sitekey=\"33f96e6a-38cd-421b-bb68-7806e1764460\"></script>\n",
      "  \n",
      "  <noscript id=\"cf-captcha-bookmark\" class=\"cf-captcha-info\">\n",
      "  <h1 data-translate=\"turn_on_js\" style=\"color:#bd2426;\">Please turn JavaScript on and reload the page.</h1>\n",
      "  </noscript>\n",
      "  <div id=\"no-cookie-warning\" data-translate=\"turn_on_cookies\" style=\"display:none\">\n",
      "    <h1 data-translate=\"turn_on_cookies\" style=\"color:#bd2426;\">Please enable Cookies.</h1>\n",
      "  </div>\n",
      "  <script type=\"text/javascript\">\n",
      "  //<![CDATA[\n",
      "    var a = function() {try{return !!window.addEventListener} catch(e) {return !1} },\n",
      "      b = function(b, c) {a() ? document.addEventListener(\"DOMContentLoaded\", b, c) : document.attachEvent(\"onreadystatechange\", b)};\n",
      "      b(function(){\n",
      "        var cookiesEnabled=(navigator.cookieEnabled)? true : false;\n",
      "        if(!cookiesEnabled){\n",
      "          var q = document.getElementById('no-cookie-warning');q.style.display = 'block';\n",
      "        }\n",
      "      });\n",
      "  //]]>\n",
      "  </script>\n",
      "  <div id=\"trk_captcha_js\" style=\"background-image:url('/cdn-cgi/images/trace/captcha/nojs/h/transparent.gif?ray=5e7b907ebe8bf589')\"></div>\n",
      "</form>\n",
      "\n",
      "              </div>\n",
      "            </div>\n",
      "\n",
      "            <div class=\"cf-column\">\n",
      "              <div class=\"cf-screenshot-container\">\n",
      "              \n",
      "                <span class=\"cf-no-screenshot\"></span>\n",
      "              \n",
      "              </div>\n",
      "            </div>\n",
      "          </div><!-- /.columns -->\n",
      "        </div>\n",
      "      </div><!-- /.captcha-container -->\n",
      "\n",
      "      <div class=\"cf-section cf-wrapper\">\n",
      "        <div class=\"cf-columns two\">\n",
      "          <div class=\"cf-column\">\n",
      "            <h2 data-translate=\"why_captcha_headline\">Why do I have to complete a CAPTCHA?</h2>\n",
      "            \n",
      "            <p data-translate=\"why_captcha_detail\">Completing the CAPTCHA proves you are a human and gives you temporary access to the web property.</p>\n",
      "          </div>\n",
      "\n",
      "          <div class=\"cf-column\">\n",
      "            <h2 data-translate=\"resolve_captcha_headline\">What can I do to prevent this in the future?</h2>\n",
      "            \n",
      "\n",
      "            <p data-translate=\"resolve_captcha_antivirus\">If you are on a personal connection, like at home, you can run an anti-virus scan on your device to make sure it is not infected with malware.</p>\n",
      "\n",
      "            <p data-translate=\"resolve_captcha_network\">If you are at an office or shared network, you can ask the network administrator to run a scan across the network looking for misconfigured or infected devices.</p>\n",
      "            \n",
      "              \n",
      "            \n",
      "          </div>\n",
      "        </div>\n",
      "      </div><!-- /.section -->\n",
      "      \n",
      "\n",
      "      <div class=\"cf-error-footer cf-wrapper w-240 lg:w-full py-10 sm:py-4 sm:px-8 mx-auto text-center sm:text-left border-solid border-0 border-t border-gray-300\">\n",
      "  <p class=\"text-13\">\n",
      "    <span class=\"cf-footer-item sm:block sm:mb-1\">Cloudflare Ray ID: <strong class=\"font-semibold\">5e7b907ebe8bf589</strong></span>\n",
      "    <span class=\"cf-footer-separator sm:hidden\">&bull;</span>\n",
      "    <span class=\"cf-footer-item sm:block sm:mb-1\"><span>Your IP</span>: 35.163.68.171</span>\n",
      "    <span class=\"cf-footer-separator sm:hidden\">&bull;</span>\n",
      "    <span class=\"cf-footer-item sm:block sm:mb-1\"><span>Performance &amp; security by</span> <a rel=\"noopener noreferrer\" href=\"https://www.cloudflare.com/5xx-error-landing\" id=\"brand_link\" target=\"_blank\">Cloudflare</a></span>\n",
      "    \n",
      "  </p>\n",
      "</div><!-- /.error-footer -->\n",
      "\n",
      "\n",
      "    </div><!-- /#cf-error-details -->\n",
      "  </div><!-- /#cf-wrapper -->\n",
      "\n",
      "  <script type=\"text/javascript\">\n",
      "  window._cf_translation = {};\n",
      "  \n",
      "  \n",
      "</script>\n",
      "\n",
      "\n",
      "</body>\n",
      "</html>\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    '<!DOCTYPE html>\\n<!--[if lt IE 7]> <html class=\"no-js ie6 oldie\" lang=\"en-US\"> <![endif]-->\\n<!--[if IE 7]>    <html class=\"no-js ie7 oldie\" lang=\"en-US\"> <![endif]-->\\n<!--[if IE 8]>    <html class=\"no-js ie8 oldie\" lang=\"en-US\"> <![endif]-->\\n<!--[if gt IE 8]><!--> <html class=\"no-js\" lang=\"en-US\"> <!--<![endif]-->\\n<head>\\n<title>Attention Required! | Cloudflare</title>\\n<meta name=\"captcha-bypass\" id=\"captcha-bypass\" />\\n<meta charset=\"UTF-8\" />\\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />\\n<meta http-equiv=\"X-UA-Compatible\" content=\"IE=Edge,chrome=1\" />\\n<meta name=\"robots\" content=\"noindex, nofollow\" />\\n<meta name=\"viewport\" content=\"width=device-width,initial-scale=1\" />\\n<link rel=\"stylesheet\" id=\"cf_styles-css\" href=\"/cdn-cgi/styles/cf.errors.css\" type=\"text/css\" media=\"screen,projection\" />\\n<!--[if lt IE 9]><link rel=\"stylesheet\" id=\\'cf_styles-ie-css\\' href=\"/cdn-cgi/styles/cf.errors.ie.css\" type=\"text/css\" media=\"screen,projection\" /><![endif]-->\\n<style type=\"text/css\">body{margin:0;padding:0}</style>\\n\\n\\n<!--[if gte IE 10]><!-->\\n<script>\\n  if (!navigator.cookieEnabled) {\\n    window.addEventListener(\\'DOMContentLoaded\\', function () {\\n      var cookieEl = document.getElementById(\\'cookie-alert\\');\\n      cookieEl.style.display = \\'block\\';\\n    })\\n  }\\n</script>\\n<!--<![endif]-->\\n\\n\\n\\n\\n<style type=\"text/css\">\\n  #cf-wrapper #spinner {width:69px; margin:  auto;}\\n  #cf-wrapper #cf-please-wait{text-align:center}\\n  .attribution {margin-top: 32px;}\\n  .bubbles { background-color: #f58220; width:20px; height: 20px; margin:2px; border-radius:100%; display:inline-block; }\\n  #cf-wrapper #challenge-form { padding-top:25px; padding-bottom:25px; }\\n  #cf-hcaptcha-container { text-align:center;}\\n  #cf-hcaptcha-container iframe { display: inline-block;}\\n  @keyframes fader     { 0% {opacity: 0.2;} 50% {opacity: 1.0;} 100% {opacity: 0.2;} }\\n  #cf-wrapper #cf-bubbles { width:69px; }\\n  @-webkit-keyframes fader { 0% {opacity: 0.2;} 50% {opacity: 1.0;} 100% {opacity: 0.2;} }\\n  #cf-bubbles > .bubbles { animation: fader 1.6s infinite;}\\n  #cf-bubbles > .bubbles:nth-child(2) { animation-delay: .2s;}\\n  #cf-bubbles > .bubbles:nth-child(3) { animation-delay: .4s;}\\n</style>\\n</head>\\n<body>\\n  <div id=\"cf-wrapper\">\\n    <div class=\"cf-alert cf-alert-error cf-cookie-error\" id=\"cookie-alert\" data-translate=\"enable_cookies\">Please enable cookies.</div>\\n    <div id=\"cf-error-details\" class=\"cf-error-details-wrapper\">\\n      <div class=\"cf-wrapper cf-header cf-error-overview\">\\n        <h1 data-translate=\"challenge_headline\">One more step</h1>\\n        <h2 class=\"cf-subheadline\"><span data-translate=\"complete_sec_check\">Please complete the security check to access</span> www.rki.de</h2>\\n      </div><!-- /.header -->\\n      \\n      <div class=\"cf-section cf-highlight cf-captcha-container\">\\n        <div class=\"cf-wrapper\">\\n          <div class=\"cf-columns two\">\\n            <div class=\"cf-column\">\\n            \\n              <div class=\"cf-highlight-inverse cf-form-stacked\">\\n                <form class=\"challenge-form\" id=\"challenge-form\" action=\"/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Fallzahlen.html?__cf_chl_captcha_tk__=0c8d4e129ffa9b5ee39102fc83aef459f9aaded0-1603624995-0-Ab6GSdKr0ftByfmZdXMQilEuchQFicgf0pL69ZgtkHcwCIfiOM8vGP0I08gPLBMYjTYhW39B2TN0ZxNnWASJ3O7AbXb-0QNOTHB706GW5VRd1BKHEPMGAA6hMIHQtAh3xgOFel5UzrPz7ANLYVnO03L0vLjtG9GBT8XB23JmTc8PQxtxVBsXx-iu5HiNl1X2ojdts1mi87nmFhQM3DCygWTSha9nsgXlyzbm9TS80xnFj-2vorsdfcw_O-rA3iWA1vLrwKlEi-TlZojkm1Vq7pdGd0ZlM6eUJII4jKX3ADo9XBSaVwKzLkFRSvgGyLennLFdIsIrGEzwxYguz9wJ62Wt4MsELvTaTzKhKCEjz54IsM9COJSYOkmZ65epnEgXBKzCMAyZBGWB0NxTwhSrV0MjuGFfF5CCZqnosEcccKZHToGl5UylcwBXGBrrjSADFwPYdtOh0DCee5edGZHsXAnCjbDgpJMEeZx7A071sI73yJ0jgz1_8JxH4mNbBAE-nTf0Wo5chOo5eOqRRkw7H53tWtZhlXktOtekFCVFNF_IfDJVpcx1M7Yjt0jfxL4UglHt_ji2lozv-q9GtqkiQBeclvlDUyrx2oH9l14R_XYJLj2vnn36ff0H3u-IpeQfig\" method=\"POST\" enctype=\"application/x-www-form-urlencoded\">\\n  \\n  <input type=\"hidden\" name=\"r\" value=\"a0a7e47ee2310d88427a967a6ad6e3d754512f93-1603624995-0-AQ21TASuxwuzGyitO03hY3RcIHSFqOyfn7L2ZVGZoEi1lyxmKw9awYzyDBixHLAK+D6PmVeoskDSG3MhVP0U9BHDCI2lnxiDlgkFW32iwIMo0mEayesO/jUmC9gej5o5e2V5YOw3m1DD0BonYzgvFFTzSrGSr73GdTy9knpp18WPJ0winx/OU9nZV0xKdYhQ3R//HztzPpWWo2cGNJ3ceRJz5xEcnGKN4M5DNxBmpzlf7ULdsEXtc/ucLpoTr8RDIe65AljNxwUqpn1FunS97CEG8ejcqmyEKal5ak+BMIj4GaO+2ZM/o1uOMTwcMlKcr/2z8b8SazLDOZ/B0vlPlU0O3z4jvbkSHCXuxn1gt9/8Hqaa7E0ecCoQtNsJs7U6qXSPFrH9PEZJJDLO8x/CZCCK43nBxbPw6sEpIU/zGXIet7jveRreMIlVdgHV6xFwrn4mLsUr1CYCSaMhN3kEJYM3R7sWPZIfYBVgNKr7eZprSccapPHyxrV3cYcBY4twpDjBLQTOu0C6QBcWR7gdlmYe+XqJvKCJkC4af812JW66EA/2K9b4jHPni7zj9u2uzrkoSVc/7WPFVXTBpYCR8IHXBm7xtnHcS932CMdgh1JiNsyBtYnR5EV8sfp5D4DLc5XgniXmDXfsOb3dZfXdmVht1DTCB6m4hMtTYlUk0pHXIJKHlZUUsPqctIeJ36+ugU6Yym81GlLSQ9ye1j5NYOT86yAU/mWFFdLdeEmdAlFlBy2KY+GSEfajlWMD3RUMGICmwtOQhPJukoVuWt8yucqlTTYVePN2D0hi/InuGC8l9UAT2seuz2kCRfg3rk/SWpvfUdJOTQPGaT8hykoY+Xdj3DA8dOvZzszbv0TLfTUDZcjQtGaXxTONLRChvT1GUelzZaIoyGAfikQucP7pUhnASF1Pj9/XdgOb9jTKyBoLmiDXOxMbWijI3xUCvkTb06oeqP+lp/58l+QMB0wtwsvj9c+dtBKj4kfcacCFhDVBY7q5TliGa/iUlaHPDmN3bXtCVRIKrH4AjgT1GAZ+RgCg2Fbm4olNLnyuPM62aLDZ2j07AiYAGIko/gjz1W9Pkai0ZftXzKwUREt6JrDqDJopfZHTK7MNyIHpLWufMUWpqP6U6uLGzpEcBt0OREpIPEQFZjvvMOQ5zeEoUmBsdX5WK4Df8mwWeRycMBrSrXIOk9zH3m8gi/vd0UDK6jbwj/eW6ChZAYnVlXxkHmrYIob1Yth2xSqJeGUnjd01hoLg\">\\n  <input type=\"hidden\" name=\"cf_captcha_kind\" value=\"h\">\\n  <input type=\"hidden\" name=\"vc\" value=\"\">\\n  \\n  <script type=\"text/javascript\" src=\"/cdn-cgi/scripts/hcaptcha.challenge.js\" data-type=\"normal\" data-ray=\"5e7b907ebe8bf589\" async data-sitekey=\"33f96e6a-38cd-421b-bb68-7806e1764460\"></script>\\n  \\n  <noscript id=\"cf-captcha-bookmark\" class=\"cf-captcha-info\">\\n  <h1 data-translate=\"turn_on_js\" style=\"color:#bd2426;\">Please turn JavaScript on and reload the page.</h1>\\n  </noscript>\\n  <div id=\"no-cookie-warning\" data-translate=\"turn_on_cookies\" style=\"display:none\">\\n    <h1 data-translate=\"turn_on_cookies\" style=\"color:#bd2426;\">Please enable Cookies.</h1>\\n  </div>\\n  <script type=\"text/javascript\">\\n  //<![CDATA[\\n    var a = function() {try{return !!window.addEventListener} catch(e) {return !1} },\\n      b = function(b, c) {a() ? document.addEventListener(\"DOMContentLoaded\", b, c) : document.attachEvent(\"onreadystatechange\", b)};\\n      b(function(){\\n        var cookiesEnabled=(navigator.cookieEnabled)? true : false;\\n        if(!cookiesEnabled){\\n          var q = document.getElementById(\\'no-cookie-warning\\');q.style.display = \\'block\\';\\n        }\\n      });\\n  //]]>\\n  </script>\\n  <div id=\"trk_captcha_js\" style=\"background-image:url(\\'/cdn-cgi/images/trace/captcha/nojs/h/transparent.gif?ray=5e7b907ebe8bf589\\')\"></div>\\n</form>\\n\\n              </div>\\n            </div>\\n\\n            <div class=\"cf-column\">\\n              <div class=\"cf-screenshot-container\">\\n              \\n                <span class=\"cf-no-screenshot\"></span>\\n              \\n              </div>\\n            </div>\\n          </div><!-- /.columns -->\\n        </div>\\n      </div><!-- /.captcha-container -->\\n\\n      <div class=\"cf-section cf-wrapper\">\\n        <div class=\"cf-columns two\">\\n          <div class=\"cf-column\">\\n            <h2 data-translate=\"why_captcha_headline\">Why do I have to complete a CAPTCHA?</h2>\\n            \\n            <p data-translate=\"why_captcha_detail\">Completing the CAPTCHA proves you are a human and gives you temporary access to the web property.</p>\\n          </div>\\n\\n          <div class=\"cf-column\">\\n            <h2 data-translate=\"resolve_captcha_headline\">What can I do to prevent this in the future?</h2>\\n            \\n\\n            <p data-translate=\"resolve_captcha_antivirus\">If you are on a personal connection, like at home, you can run an anti-virus scan on your device to make sure it is not infected with malware.</p>\\n\\n            <p data-translate=\"resolve_captcha_network\">If you are at an office or shared network, you can ask the network administrator to run a scan across the network looking for misconfigured or infected devices.</p>\\n            \\n              \\n            \\n          </div>\\n        </div>\\n      </div><!-- /.section -->\\n      \\n\\n      <div class=\"cf-error-footer cf-wrapper w-240 lg:w-full py-10 sm:py-4 sm:px-8 mx-auto text-center sm:text-left border-solid border-0 border-t border-gray-300\">\\n  <p class=\"text-13\">\\n    <span class=\"cf-footer-item sm:block sm:mb-1\">Cloudflare Ray ID: <strong class=\"font-semibold\">5e7b907ebe8bf589</strong></span>\\n    <span class=\"cf-footer-separator sm:hidden\">&bull;</span>\\n    <span class=\"cf-footer-item sm:block sm:mb-1\"><span>Your IP</span>: 35.163.68.171</span>\\n    <span class=\"cf-footer-separator sm:hidden\">&bull;</span>\\n    <span class=\"cf-footer-item sm:block sm:mb-1\"><span>Performance &amp; security by</span> <a rel=\"noopener noreferrer\" href=\"https://www.cloudflare.com/5xx-error-landing\" id=\"brand_link\" target=\"_blank\">Cloudflare</a></span>\\n    \\n  </p>\\n</div><!-- /.error-footer -->\\n\\n\\n    </div><!-- /#cf-error-details -->\\n  </div><!-- /#cf-wrapper -->\\n\\n  <script type=\"text/javascript\">\\n  window._cf_translation = {};\\n  \\n  \\n</script>\\n\\n\\n</body>\\n</html>\\n'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_col = ['city','total_cases','daily_cases','cases_per_M','total_deaths','']\n",
    "df = pd.DataFrame(columns=df_col)\n",
    "count = 0\n",
    "for tr in tbody:\n",
    "    temp_data = []\n",
    "    for td in tr:\n",
    "        # print(td.text_content())\n",
    "        temp_data.append(td.text_content())\n",
    "    df.loc[count] = temp_data\n",
    "    count += 1\n",
    "if autoDate:\n",
    "    df.loc[:, \"date\"] = datetime.date.today()\n",
    "    df[\"date\"] = df[\"date\"].astype(str)\n",
    "else:\n",
    "    df[\"date\"] = manualDate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'gSheets' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-10-b0128b5b76f1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     14\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     15\u001b[0m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'date'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmanualDate\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 16\u001b[0;31m \u001b[0msheet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgSheets\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreadSheet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'covidCrawlen'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     17\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     18\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'gSheets' is not defined"
     ]
    }
   ],
   "source": [
    "sheet = gSheets.readSheet(\"covidCrawlen\")\n",
    "\n",
    "\n",
    "### Save parsed data\n",
    "\n",
    "df[1] = pd.to_numeric(df[1])\n",
    "df[4] = pd.to_numeric(df[4])\n",
    "df.loc[df[1].apply(lambda x: ~(float(x) == np.floor(x))), 1] = df[1] * 1000\n",
    "df.columns = [\n",
    "    \"city\",\n",
    "    \"total_cases\",\n",
    "    \"difference\",\n",
    "    \"cases_last_7\",\n",
    "    \"insidence_7\",\n",
    "    \"total_deaths\",\n",
    "    \"date\",\n",
    "]\n",
    "df = df.reindex([\"city\", \"total_cases\", \"total_deaths\", \"date\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'gSheets' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-9-9090c062febf>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     14\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     15\u001b[0m     \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'date'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmanualDate\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 16\u001b[0;31m \u001b[0msheet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgSheets\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreadSheet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'covidCrawlen'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     17\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     18\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'gSheets' is not defined"
     ]
    }
   ],
   "source": [
    "sheet = gSheets.readSheet(\"covidCrawlen\")\n",
    "worksheet = sheet.worksheet(\"data\")\n",
    "for i, r in df.iterrows():\n",
    "    worksheet.append_row(list(r))\n",
    "print(\"success\" + str(datetime.date.today()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-12-0c3cae02c126>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Fallzahlen_Kum_Tab.xlsx'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'BL_7-Tage-Fallzahlen'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/miniconda3/lib/python3.8/site-packages/pandas/util/_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    294\u001b[0m                 )\n\u001b[1;32m    295\u001b[0m                 \u001b[0mwarnings\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwarn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mFutureWarning\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 296\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    297\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    298\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.8/site-packages/pandas/io/excel/_base.py\u001b[0m in \u001b[0;36mread_excel\u001b[0;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols)\u001b[0m\n\u001b[1;32m    302\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    303\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mio\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 304\u001b[0;31m         \u001b[0mio\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mio\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    305\u001b[0m     \u001b[0;32melif\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mio\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    306\u001b[0m         raise ValueError(\n",
      "\u001b[0;32m~/miniconda3/lib/python3.8/site-packages/pandas/io/excel/_base.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, path_or_buffer, engine)\u001b[0m\n\u001b[1;32m    865\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_io\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mstringify_path\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath_or_buffer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    866\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 867\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engines\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_io\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    868\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    869\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__fspath__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.8/site-packages/pandas/io/excel/_xlrd.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[1;32m     19\u001b[0m         \"\"\"\n\u001b[1;32m     20\u001b[0m         \u001b[0merr_msg\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"Install xlrd >= 1.0.0 for Excel support\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 21\u001b[0;31m         \u001b[0mimport_optional_dependency\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"xlrd\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mextra\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0merr_msg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     22\u001b[0m         \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__init__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     23\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.8/site-packages/pandas/compat/_optional.py\u001b[0m in \u001b[0;36mimport_optional_dependency\u001b[0;34m(name, extra, raise_on_missing, on_version)\u001b[0m\n\u001b[1;32m     91\u001b[0m     \u001b[0;32mexcept\u001b[0m \u001b[0mImportError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     92\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mraise_on_missing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 93\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mImportError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     94\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     95\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mImportError\u001b[0m: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd."
     ]
    }
   ],
   "source": [
    "df = pd.read_excel(\"Fallzahlen_Kum_Tab.xlsx\", \"BL_7-Tage-Fallzahlen\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.loc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'sub' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-15-ab4337bda713>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msub\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'sub' is not defined"
     ]
    }
   ],
   "source": [
    "df.drop(sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = [\"state\", \"a\", \"b\", \"c\", \"d\", \"e\", \"f\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Baden-Württem­berg</th>\n",
       "      <td>498.911</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bayern</th>\n",
       "      <td>645.785</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Berlin</th>\n",
       "      <td>179.818</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Branden­burg</th>\n",
       "      <td>108.608</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bremen</th>\n",
       "      <td>27.363</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gesamt</th>\n",
       "      <td>3.718.955</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hamburg</th>\n",
       "      <td>77.015</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hessen</th>\n",
       "      <td>289.817</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meck­lenburg-\\nVor­pommern</th>\n",
       "      <td>44.097</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nieder­sachsen</th>\n",
       "      <td>260.448</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nord­rhein-West­falen</th>\n",
       "      <td>814.114</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rhein­land-Pfalz</th>\n",
       "      <td>154.714</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saarland</th>\n",
       "      <td>41.324</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sachsen</th>\n",
       "      <td>285.537</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sachsen-Anhalt</th>\n",
       "      <td>99.133</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schles­wig-Holstein</th>\n",
       "      <td>63.692</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thüringen</th>\n",
       "      <td>128.579</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    a  b\n",
       "state                                   \n",
       "Baden-Württem­berg            498.911  1\n",
       "Bayern                        645.785  1\n",
       "Berlin                        179.818  1\n",
       "Branden­burg                  108.608  1\n",
       "Bremen                         27.363  1\n",
       "Gesamt                      3.718.955  1\n",
       "Hamburg                        77.015  1\n",
       "Hessen                        289.817  1\n",
       "Meck­lenburg-\\nVor­pommern     44.097  1\n",
       "Nieder­sachsen                260.448  1\n",
       "Nord­rhein-West­falen         814.114  1\n",
       "Rhein­land-Pfalz              154.714  1\n",
       "Saarland                       41.324  1\n",
       "Sachsen                       285.537  1\n",
       "Sachsen-Anhalt                 99.133  1\n",
       "Schles­wig-Holstein            63.692  1\n",
       "Thüringen                     128.579  1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"state\").agg({\"a\": \"sum\", \"b\": \"count\"})"
   ]
  },
  {
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}