notebooks/srovnani.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys  \n",
    "sys.path.insert(0, '..')\n",
    "\n",
    "from datetime import date, timedelta\n",
    "\n",
    "from config import Config\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "db = create_engine(Config.SQLALCHEMY_DATABASE_URI)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pozitivity = pd.read_excel('../data/ockovani-pozitivity-hospitalizace-jip.xlsx', sheet_name=0, usecols=['tyden', 'vek_kategorie', 'pozitivni_celkem', 'pozitivni_bez_ockovani', 'pozitivni_po_prvni', 'pozitivni_po_ukonceni', 'pozitivni_po_ukonceni_s_posilujici'])\n",
    "hospitalizace = pd.read_excel('../data/ockovani-pozitivity-hospitalizace-jip.xlsx', sheet_name=1, usecols=['tyden', 'vek_kategorie', 'hospitalizace_celkem', 'hospitalizace_bez_ockovani', 'hospitalizace_po_prvni', 'hospitalizace_po_ukonceni', 'hospitalizace_po_ukonceni_s_posilujici'])\n",
    "jip = pd.read_excel('../data/ockovani-pozitivity-hospitalizace-jip.xlsx', sheet_name=2, usecols=['tyden', 'vek_kategorie', 'JIP_celkem', 'JIP_bez_ockovani', 'JIP_po_prvni', 'JIP_po_ukonceni', 'JIP_po_ukonceni_s_posilujici'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>tyden</th>\n",
       "      <th>vek_kategorie</th>\n",
       "      <th>pozitivni_celkem</th>\n",
       "      <th>pozitivni_bez_ockovani</th>\n",
       "      <th>pozitivni_po_prvni</th>\n",
       "      <th>pozitivni_po_ukonceni</th>\n",
       "      <th>pozitivni_po_ukonceni_s_posilujici</th>\n",
       "      <th>hospitalizace_celkem</th>\n",
       "      <th>hospitalizace_bez_ockovani</th>\n",
       "      <th>hospitalizace_po_prvni</th>\n",
       "      <th>hospitalizace_po_ukonceni</th>\n",
       "      <th>hospitalizace_po_ukonceni_s_posilujici</th>\n",
       "      <th>JIP_celkem</th>\n",
       "      <th>JIP_bez_ockovani</th>\n",
       "      <th>JIP_po_prvni</th>\n",
       "      <th>JIP_po_ukonceni</th>\n",
       "      <th>JIP_po_ukonceni_s_posilujici</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-53</td>\n",
       "      <td>50-54 let</td>\n",
       "      <td>5959</td>\n",
       "      <td>5946</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>219</td>\n",
       "      <td>219</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-34</td>\n",
       "      <td>75-79 let</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11</td>\n",
       "      <td>40-44 let</td>\n",
       "      <td>5988</td>\n",
       "      <td>5862</td>\n",
       "      <td>112</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>207</td>\n",
       "      <td>199</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>49</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-12</td>\n",
       "      <td>20-24 let</td>\n",
       "      <td>2783</td>\n",
       "      <td>2745</td>\n",
       "      <td>33</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-29</td>\n",
       "      <td>85-89 let</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>2021-01</td>\n",
       "      <td>65-69 let</td>\n",
       "      <td>4996</td>\n",
       "      <td>4984</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>693</td>\n",
       "      <td>693</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>174</td>\n",
       "      <td>174</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>80-84 let</td>\n",
       "      <td>1062</td>\n",
       "      <td>1021</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>524</td>\n",
       "      <td>509</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>2021-41</td>\n",
       "      <td>70-74 let</td>\n",
       "      <td>198</td>\n",
       "      <td>52</td>\n",
       "      <td>5</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>2021-15</td>\n",
       "      <td>65-69 let</td>\n",
       "      <td>1132</td>\n",
       "      <td>1068</td>\n",
       "      <td>57</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>327</td>\n",
       "      <td>311</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>2021-24</td>\n",
       "      <td>85-89 let</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>539 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       tyden vek_kategorie  pozitivni_celkem  pozitivni_bez_ockovani  \\\n",
       "0    2020-53     50-54 let              5959                    5946   \n",
       "1    2021-34     75-79 let                10                       1   \n",
       "2    2021-11     40-44 let              5988                    5862   \n",
       "3    2021-12     20-24 let              2783                    2745   \n",
       "4    2021-29     85-89 let                 5                       3   \n",
       "..       ...           ...               ...                     ...   \n",
       "534  2021-01     65-69 let              4996                    4984   \n",
       "535  2021-03     80-84 let              1062                    1021   \n",
       "536  2021-41     70-74 let               198                      52   \n",
       "537  2021-15     65-69 let              1132                    1068   \n",
       "538  2021-24     85-89 let                12                       6   \n",
       "\n",
       "     pozitivni_po_prvni  pozitivni_po_ukonceni  \\\n",
       "0                    13                      0   \n",
       "1                     0                      9   \n",
       "2                   112                     14   \n",
       "3                    33                      5   \n",
       "4                     0                      2   \n",
       "..                  ...                    ...   \n",
       "534                  12                      0   \n",
       "535                  41                      0   \n",
       "536                   5                    141   \n",
       "537                  57                      7   \n",
       "538                   4                      2   \n",
       "\n",
       "     pozitivni_po_ukonceni_s_posilujici  hospitalizace_celkem  \\\n",
       "0                                     0                   219   \n",
       "1                                     0                     4   \n",
       "2                                     0                   207   \n",
       "3                                     0                    21   \n",
       "4                                     0                     4   \n",
       "..                                  ...                   ...   \n",
       "534                                   0                   693   \n",
       "535                                   0                   524   \n",
       "536                                   0                    47   \n",
       "537                                   0                   327   \n",
       "538                                   0                     6   \n",
       "\n",
       "     hospitalizace_bez_ockovani  hospitalizace_po_prvni  \\\n",
       "0                           219                       0   \n",
       "1                             3                       0   \n",
       "2                           199                       6   \n",
       "3                            20                       0   \n",
       "4                             3                       0   \n",
       "..                          ...                     ...   \n",
       "534                         693                       0   \n",
       "535                         509                      15   \n",
       "536                          22                       4   \n",
       "537                         311                      14   \n",
       "538                           3                       2   \n",
       "\n",
       "     hospitalizace_po_ukonceni  hospitalizace_po_ukonceni_s_posilujici  \\\n",
       "0                            0                                       0   \n",
       "1                            1                                       0   \n",
       "2                            2                                       0   \n",
       "3                            1                                       0   \n",
       "4                            1                                       0   \n",
       "..                         ...                                     ...   \n",
       "534                          0                                       0   \n",
       "535                          0                                       0   \n",
       "536                         21                                       0   \n",
       "537                          2                                       0   \n",
       "538                          1                                       0   \n",
       "\n",
       "     JIP_celkem  JIP_bez_ockovani  JIP_po_prvni  JIP_po_ukonceni  \\\n",
       "0            42                42             0                0   \n",
       "1             1                 1             0                0   \n",
       "2            49                47             1                1   \n",
       "3             2                 2             0                0   \n",
       "4             1                 1             0                0   \n",
       "..          ...               ...           ...              ...   \n",
       "534         174               174             0                0   \n",
       "535          74                70             4                0   \n",
       "536          13                 8             2                3   \n",
       "537          92                90             2                0   \n",
       "538           2                 1             0                1   \n",
       "\n",
       "     JIP_po_ukonceni_s_posilujici  \n",
       "0                               0  \n",
       "1                               0  \n",
       "2                               0  \n",
       "3                               0  \n",
       "4                               0  \n",
       "..                            ...  \n",
       "534                             0  \n",
       "535                             0  \n",
       "536                             0  \n",
       "537                             0  \n",
       "538                             0  \n",
       "\n",
       "[539 rows x 17 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(pozitivity, hospitalizace)\n",
    "df = pd.merge(df, jip)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>tyden</th>\n",
       "      <th>vekova_skupina</th>\n",
       "      <th>pozitivni_celkem</th>\n",
       "      <th>pozitivni_bez_ockovani</th>\n",
       "      <th>pozitivni_po_prvni</th>\n",
       "      <th>pozitivni_po_ukonceni</th>\n",
       "      <th>pozitivni_po_ukonceni_s_posilujici</th>\n",
       "      <th>hospitalizace_celkem</th>\n",
       "      <th>hospitalizace_bez_ockovani</th>\n",
       "      <th>hospitalizace_po_prvni</th>\n",
       "      <th>hospitalizace_po_ukonceni</th>\n",
       "      <th>hospitalizace_po_ukonceni_s_posilujici</th>\n",
       "      <th>JIP_celkem</th>\n",
       "      <th>JIP_bez_ockovani</th>\n",
       "      <th>JIP_po_prvni</th>\n",
       "      <th>JIP_po_ukonceni</th>\n",
       "      <th>JIP_po_ukonceni_s_posilujici</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-53</td>\n",
       "      <td>50-54</td>\n",
       "      <td>5959</td>\n",
       "      <td>5946</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>219</td>\n",
       "      <td>219</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-34</td>\n",
       "      <td>75-79</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11</td>\n",
       "      <td>40-44</td>\n",
       "      <td>5988</td>\n",
       "      <td>5862</td>\n",
       "      <td>112</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>207</td>\n",
       "      <td>199</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>49</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-12</td>\n",
       "      <td>20-24</td>\n",
       "      <td>2783</td>\n",
       "      <td>2745</td>\n",
       "      <td>33</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-29</td>\n",
       "      <td>85-89</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>2021-01</td>\n",
       "      <td>65-69</td>\n",
       "      <td>4996</td>\n",
       "      <td>4984</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>693</td>\n",
       "      <td>693</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>174</td>\n",
       "      <td>174</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>80-84</td>\n",
       "      <td>1062</td>\n",
       "      <td>1021</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>524</td>\n",
       "      <td>509</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>2021-41</td>\n",
       "      <td>70-74</td>\n",
       "      <td>198</td>\n",
       "      <td>52</td>\n",
       "      <td>5</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>2021-15</td>\n",
       "      <td>65-69</td>\n",
       "      <td>1132</td>\n",
       "      <td>1068</td>\n",
       "      <td>57</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>327</td>\n",
       "      <td>311</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>2021-24</td>\n",
       "      <td>85-89</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>539 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       tyden vekova_skupina  pozitivni_celkem  pozitivni_bez_ockovani  \\\n",
       "0    2020-53          50-54              5959                    5946   \n",
       "1    2021-34          75-79                10                       1   \n",
       "2    2021-11          40-44              5988                    5862   \n",
       "3    2021-12          20-24              2783                    2745   \n",
       "4    2021-29          85-89                 5                       3   \n",
       "..       ...            ...               ...                     ...   \n",
       "534  2021-01          65-69              4996                    4984   \n",
       "535  2021-03          80-84              1062                    1021   \n",
       "536  2021-41          70-74               198                      52   \n",
       "537  2021-15          65-69              1132                    1068   \n",
       "538  2021-24          85-89                12                       6   \n",
       "\n",
       "     pozitivni_po_prvni  pozitivni_po_ukonceni  \\\n",
       "0                    13                      0   \n",
       "1                     0                      9   \n",
       "2                   112                     14   \n",
       "3                    33                      5   \n",
       "4                     0                      2   \n",
       "..                  ...                    ...   \n",
       "534                  12                      0   \n",
       "535                  41                      0   \n",
       "536                   5                    141   \n",
       "537                  57                      7   \n",
       "538                   4                      2   \n",
       "\n",
       "     pozitivni_po_ukonceni_s_posilujici  hospitalizace_celkem  \\\n",
       "0                                     0                   219   \n",
       "1                                     0                     4   \n",
       "2                                     0                   207   \n",
       "3                                     0                    21   \n",
       "4                                     0                     4   \n",
       "..                                  ...                   ...   \n",
       "534                                   0                   693   \n",
       "535                                   0                   524   \n",
       "536                                   0                    47   \n",
       "537                                   0                   327   \n",
       "538                                   0                     6   \n",
       "\n",
       "     hospitalizace_bez_ockovani  hospitalizace_po_prvni  \\\n",
       "0                           219                       0   \n",
       "1                             3                       0   \n",
       "2                           199                       6   \n",
       "3                            20                       0   \n",
       "4                             3                       0   \n",
       "..                          ...                     ...   \n",
       "534                         693                       0   \n",
       "535                         509                      15   \n",
       "536                          22                       4   \n",
       "537                         311                      14   \n",
       "538                           3                       2   \n",
       "\n",
       "     hospitalizace_po_ukonceni  hospitalizace_po_ukonceni_s_posilujici  \\\n",
       "0                            0                                       0   \n",
       "1                            1                                       0   \n",
       "2                            2                                       0   \n",
       "3                            1                                       0   \n",
       "4                            1                                       0   \n",
       "..                         ...                                     ...   \n",
       "534                          0                                       0   \n",
       "535                          0                                       0   \n",
       "536                         21                                       0   \n",
       "537                          2                                       0   \n",
       "538                          1                                       0   \n",
       "\n",
       "     JIP_celkem  JIP_bez_ockovani  JIP_po_prvni  JIP_po_ukonceni  \\\n",
       "0            42                42             0                0   \n",
       "1             1                 1             0                0   \n",
       "2            49                47             1                1   \n",
       "3             2                 2             0                0   \n",
       "4             1                 1             0                0   \n",
       "..          ...               ...           ...              ...   \n",
       "534         174               174             0                0   \n",
       "535          74                70             4                0   \n",
       "536          13                 8             2                3   \n",
       "537          92                90             2                0   \n",
       "538           2                 1             0                1   \n",
       "\n",
       "     JIP_po_ukonceni_s_posilujici  \n",
       "0                               0  \n",
       "1                               0  \n",
       "2                               0  \n",
       "3                               0  \n",
       "4                               0  \n",
       "..                            ...  \n",
       "534                             0  \n",
       "535                             0  \n",
       "536                             0  \n",
       "537                             0  \n",
       "538                             0  \n",
       "\n",
       "[539 rows x 17 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.rename({'vek_kategorie': 'vekova_skupina'}, axis='columns')\n",
    "df['vekova_skupina'] = df['vekova_skupina'].str.replace(' let', '')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>tyden</th>\n",
       "      <th>vekova_skupina</th>\n",
       "      <th>pozitivni_celkem</th>\n",
       "      <th>pozitivni_bez_ockovani</th>\n",
       "      <th>pozitivni_po_prvni</th>\n",
       "      <th>pozitivni_po_ukonceni</th>\n",
       "      <th>pozitivni_po_ukonceni_s_posilujici</th>\n",
       "      <th>hospitalizace_celkem</th>\n",
       "      <th>hospitalizace_bez_ockovani</th>\n",
       "      <th>hospitalizace_po_prvni</th>\n",
       "      <th>hospitalizace_po_ukonceni</th>\n",
       "      <th>hospitalizace_po_ukonceni_s_posilujici</th>\n",
       "      <th>JIP_celkem</th>\n",
       "      <th>JIP_bez_ockovani</th>\n",
       "      <th>JIP_po_prvni</th>\n",
       "      <th>JIP_po_ukonceni</th>\n",
       "      <th>JIP_po_ukonceni_s_posilujici</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-53</td>\n",
       "      <td>50-54</td>\n",
       "      <td>5959</td>\n",
       "      <td>5946</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>219</td>\n",
       "      <td>219</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-34</td>\n",
       "      <td>75-79</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11</td>\n",
       "      <td>40-44</td>\n",
       "      <td>5988</td>\n",
       "      <td>5862</td>\n",
       "      <td>112</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>207</td>\n",
       "      <td>199</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>49</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-12</td>\n",
       "      <td>20-24</td>\n",
       "      <td>2783</td>\n",
       "      <td>2745</td>\n",
       "      <td>33</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-29</td>\n",
       "      <td>85-89</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>2021-01</td>\n",
       "      <td>65-69</td>\n",
       "      <td>4996</td>\n",
       "      <td>4984</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>693</td>\n",
       "      <td>693</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>174</td>\n",
       "      <td>174</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>80-84</td>\n",
       "      <td>1062</td>\n",
       "      <td>1021</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>524</td>\n",
       "      <td>509</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>2021-41</td>\n",
       "      <td>70-74</td>\n",
       "      <td>198</td>\n",
       "      <td>52</td>\n",
       "      <td>5</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>2021-15</td>\n",
       "      <td>65-69</td>\n",
       "      <td>1132</td>\n",
       "      <td>1068</td>\n",
       "      <td>57</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>327</td>\n",
       "      <td>311</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>2021-24</td>\n",
       "      <td>85-89</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>539 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       tyden vekova_skupina  pozitivni_celkem  pozitivni_bez_ockovani  \\\n",
       "0    2020-53          50-54              5959                    5946   \n",
       "1    2021-34          75-79                10                       1   \n",
       "2    2021-11          40-44              5988                    5862   \n",
       "3    2021-12          20-24              2783                    2745   \n",
       "4    2021-29          85-89                 5                       3   \n",
       "..       ...            ...               ...                     ...   \n",
       "534  2021-01          65-69              4996                    4984   \n",
       "535  2021-03          80-84              1062                    1021   \n",
       "536  2021-41          70-74               198                      52   \n",
       "537  2021-15          65-69              1132                    1068   \n",
       "538  2021-24          85-89                12                       6   \n",
       "\n",
       "     pozitivni_po_prvni  pozitivni_po_ukonceni  \\\n",
       "0                    13                      0   \n",
       "1                     0                      9   \n",
       "2                   112                     14   \n",
       "3                    33                      5   \n",
       "4                     0                      2   \n",
       "..                  ...                    ...   \n",
       "534                  12                      0   \n",
       "535                  41                      0   \n",
       "536                   5                    141   \n",
       "537                  57                      7   \n",
       "538                   4                      2   \n",
       "\n",
       "     pozitivni_po_ukonceni_s_posilujici  hospitalizace_celkem  \\\n",
       "0                                     0                   219   \n",
       "1                                     0                     4   \n",
       "2                                     0                   207   \n",
       "3                                     0                    21   \n",
       "4                                     0                     4   \n",
       "..                                  ...                   ...   \n",
       "534                                   0                   693   \n",
       "535                                   0                   524   \n",
       "536                                   0                    47   \n",
       "537                                   0                   327   \n",
       "538                                   0                     6   \n",
       "\n",
       "     hospitalizace_bez_ockovani  hospitalizace_po_prvni  \\\n",
       "0                           219                       0   \n",
       "1                             3                       0   \n",
       "2                           199                       6   \n",
       "3                            20                       0   \n",
       "4                             3                       0   \n",
       "..                          ...                     ...   \n",
       "534                         693                       0   \n",
       "535                         509                      15   \n",
       "536                          22                       4   \n",
       "537                         311                      14   \n",
       "538                           3                       2   \n",
       "\n",
       "     hospitalizace_po_ukonceni  hospitalizace_po_ukonceni_s_posilujici  \\\n",
       "0                            0                                       0   \n",
       "1                            1                                       0   \n",
       "2                            2                                       0   \n",
       "3                            1                                       0   \n",
       "4                            1                                       0   \n",
       "..                         ...                                     ...   \n",
       "534                          0                                       0   \n",
       "535                          0                                       0   \n",
       "536                         21                                       0   \n",
       "537                          2                                       0   \n",
       "538                          1                                       0   \n",
       "\n",
       "     JIP_celkem  JIP_bez_ockovani  JIP_po_prvni  JIP_po_ukonceni  \\\n",
       "0            42                42             0                0   \n",
       "1             1                 1             0                0   \n",
       "2            49                47             1                1   \n",
       "3             2                 2             0                0   \n",
       "4             1                 1             0                0   \n",
       "..          ...               ...           ...              ...   \n",
       "534         174               174             0                0   \n",
       "535          74                70             4                0   \n",
       "536          13                 8             2                3   \n",
       "537          92                90             2                0   \n",
       "538           2                 1             0                1   \n",
       "\n",
       "     JIP_po_ukonceni_s_posilujici  \n",
       "0                               0  \n",
       "1                               0  \n",
       "2                               0  \n",
       "3                               0  \n",
       "4                               0  \n",
       "..                            ...  \n",
       "534                             0  \n",
       "535                             0  \n",
       "536                             0  \n",
       "537                             0  \n",
       "538                             0  \n",
       "\n",
       "[539 rows x 17 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['vekova_skupina'] = df['vekova_skupina'].str.replace(' let', '')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tyden</th>\n",
       "      <th>od</th>\n",
       "      <th>do</th>\n",
       "      <th>vekova_skupina</th>\n",
       "      <th>nakazeni_celkem</th>\n",
       "      <th>nakazeni_bez</th>\n",
       "      <th>nakazeni_castecne</th>\n",
       "      <th>nakazeni_plne</th>\n",
       "      <th>nakazeni_posilujici</th>\n",
       "      <th>hospitalizace_celkem</th>\n",
       "      <th>hospitalizace_bez</th>\n",
       "      <th>hospitalizace_castecne</th>\n",
       "      <th>hospitalizace_plne</th>\n",
       "      <th>hospitalizace_posilujici</th>\n",
       "      <th>hospitalizace_jip_celkem</th>\n",
       "      <th>hospitalizace_jip_bez</th>\n",
       "      <th>hospitalizace_jip_castecne</th>\n",
       "      <th>hospitalizace_jip_plne</th>\n",
       "      <th>hospitalizace_jip_posilujici</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>15-19</td>\n",
       "      <td>1998</td>\n",
       "      <td>1791</td>\n",
       "      <td>42</td>\n",
       "      <td>165</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>25-29</td>\n",
       "      <td>1145</td>\n",
       "      <td>894</td>\n",
       "      <td>23</td>\n",
       "      <td>228</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>30-34</td>\n",
       "      <td>1335</td>\n",
       "      <td>1015</td>\n",
       "      <td>18</td>\n",
       "      <td>302</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>35-39</td>\n",
       "      <td>1583</td>\n",
       "      <td>1070</td>\n",
       "      <td>25</td>\n",
       "      <td>488</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>40-44</td>\n",
       "      <td>2032</td>\n",
       "      <td>1202</td>\n",
       "      <td>22</td>\n",
       "      <td>808</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>45-49</td>\n",
       "      <td>1858</td>\n",
       "      <td>1107</td>\n",
       "      <td>20</td>\n",
       "      <td>730</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>50-54</td>\n",
       "      <td>1117</td>\n",
       "      <td>633</td>\n",
       "      <td>12</td>\n",
       "      <td>472</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>55-59</td>\n",
       "      <td>932</td>\n",
       "      <td>499</td>\n",
       "      <td>10</td>\n",
       "      <td>422</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>60-64</td>\n",
       "      <td>632</td>\n",
       "      <td>268</td>\n",
       "      <td>5</td>\n",
       "      <td>358</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>65-69</td>\n",
       "      <td>515</td>\n",
       "      <td>202</td>\n",
       "      <td>6</td>\n",
       "      <td>307</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>43</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>70-74</td>\n",
       "      <td>478</td>\n",
       "      <td>129</td>\n",
       "      <td>3</td>\n",
       "      <td>346</td>\n",
       "      <td>0</td>\n",
       "      <td>108</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>75-79</td>\n",
       "      <td>431</td>\n",
       "      <td>126</td>\n",
       "      <td>5</td>\n",
       "      <td>299</td>\n",
       "      <td>1</td>\n",
       "      <td>133</td>\n",
       "      <td>54</td>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>80+</td>\n",
       "      <td>431</td>\n",
       "      <td>130</td>\n",
       "      <td>5</td>\n",
       "      <td>296</td>\n",
       "      <td>0</td>\n",
       "      <td>182</td>\n",
       "      <td>79</td>\n",
       "      <td>3</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tyden          od          do vekova_skupina  nakazeni_celkem  \\\n",
       "0   2021-42  2021-10-18  2021-10-24          15-19             1998   \n",
       "1   2021-42  2021-10-18  2021-10-24          25-29             1145   \n",
       "2   2021-42  2021-10-18  2021-10-24          30-34             1335   \n",
       "3   2021-42  2021-10-18  2021-10-24          35-39             1583   \n",
       "4   2021-42  2021-10-18  2021-10-24          40-44             2032   \n",
       "5   2021-42  2021-10-18  2021-10-24          45-49             1858   \n",
       "6   2021-42  2021-10-18  2021-10-24          50-54             1117   \n",
       "7   2021-42  2021-10-18  2021-10-24          55-59              932   \n",
       "8   2021-42  2021-10-18  2021-10-24          60-64              632   \n",
       "9   2021-42  2021-10-18  2021-10-24          65-69              515   \n",
       "10  2021-42  2021-10-18  2021-10-24          70-74              478   \n",
       "11  2021-42  2021-10-18  2021-10-24          75-79              431   \n",
       "12  2021-42  2021-10-18  2021-10-24            80+              431   \n",
       "\n",
       "    nakazeni_bez  nakazeni_castecne  nakazeni_plne  nakazeni_posilujici  \\\n",
       "0           1791                 42            165                    0   \n",
       "1            894                 23            228                    0   \n",
       "2           1015                 18            302                    0   \n",
       "3           1070                 25            488                    0   \n",
       "4           1202                 22            808                    0   \n",
       "5           1107                 20            730                    1   \n",
       "6            633                 12            472                    0   \n",
       "7            499                 10            422                    1   \n",
       "8            268                  5            358                    1   \n",
       "9            202                  6            307                    0   \n",
       "10           129                  3            346                    0   \n",
       "11           126                  5            299                    1   \n",
       "12           130                  5            296                    0   \n",
       "\n",
       "    hospitalizace_celkem  hospitalizace_bez  hospitalizace_castecne  \\\n",
       "0                      5                  5                       0   \n",
       "1                     15                 15                       0   \n",
       "2                     18                 16                       1   \n",
       "3                     28                 25                       1   \n",
       "4                     24                 20                       0   \n",
       "5                     50                 45                       0   \n",
       "6                     39                 29                       1   \n",
       "7                     44                 33                       2   \n",
       "8                     50                 39                       0   \n",
       "9                     73                 43                       1   \n",
       "10                   108                 55                       1   \n",
       "11                   133                 54                       4   \n",
       "12                   182                 79                       3   \n",
       "\n",
       "    hospitalizace_plne  hospitalizace_posilujici  hospitalizace_jip_celkem  \\\n",
       "0                    0                         0                         1   \n",
       "1                    0                         0                         1   \n",
       "2                    1                         0                         2   \n",
       "3                    2                         0                         1   \n",
       "4                    4                         0                         3   \n",
       "5                    4                         1                         8   \n",
       "6                    9                         0                         7   \n",
       "7                    8                         1                         8   \n",
       "8                   11                         0                        10   \n",
       "9                   29                         0                        15   \n",
       "10                  52                         0                        18   \n",
       "11                  74                         1                        14   \n",
       "12                 100                         0                        12   \n",
       "\n",
       "    hospitalizace_jip_bez  hospitalizace_jip_castecne  hospitalizace_jip_plne  \\\n",
       "0                       1                           0                       0   \n",
       "1                       1                           0                       0   \n",
       "2                       1                           0                       1   \n",
       "3                       1                           0                       0   \n",
       "4                       2                           0                       1   \n",
       "5                       6                           0                       1   \n",
       "6                       4                           0                       3   \n",
       "7                       7                           0                       1   \n",
       "8                      10                           0                       0   \n",
       "9                      10                           1                       4   \n",
       "10                      7                           1                      10   \n",
       "11                      5                           1                       8   \n",
       "12                      3                           0                       9   \n",
       "\n",
       "    hospitalizace_jip_posilujici  \n",
       "0                              0  \n",
       "1                              0  \n",
       "2                              0  \n",
       "3                              0  \n",
       "4                              0  \n",
       "5                              1  \n",
       "6                              0  \n",
       "7                              0  \n",
       "8                              0  \n",
       "9                              0  \n",
       "10                             0  \n",
       "11                             0  \n",
       "12                             0  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "srovnani = pd.read_sql_query(\n",
    "    f\"\"\"\n",
    "    select * from srovnani_ockovani s \n",
    "    where s.od < '2021-10-28'\n",
    "    \"\"\",\n",
    "    db\n",
    ")\n",
    "\n",
    "srovnani = srovnani[srovnani['od'] == srovnani['od'].max()]\n",
    "srovnani['vekova_skupina'] = srovnani['vekova_skupina'].replace({'80-84': '80+', '85-89': '80+', '90+': '80+'})\n",
    "srovnani = srovnani.groupby(['tyden', 'od', 'do', 'vekova_skupina']).sum().reset_index()\n",
    "\n",
    "srovnani"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>vekova_skupina</th>\n",
       "      <th>populace</th>\n",
       "      <th>min_vek</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60-64</td>\n",
       "      <td>625465.0</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18-24</td>\n",
       "      <td>669492.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25-29</td>\n",
       "      <td>620925.0</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>65-69</td>\n",
       "      <td>672418.0</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45-49</td>\n",
       "      <td>882586.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>40-44</td>\n",
       "      <td>893321.0</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>50-54</td>\n",
       "      <td>691083.0</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16-17</td>\n",
       "      <td>195016.0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>75-79</td>\n",
       "      <td>417201.0</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70-74</td>\n",
       "      <td>621177.0</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>80+</td>\n",
       "      <td>447526.0</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>55-59</td>\n",
       "      <td>669733.0</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>35-39</td>\n",
       "      <td>753310.0</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>12-15</td>\n",
       "      <td>456488.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0-11</td>\n",
       "      <td>1367105.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>30-34</td>\n",
       "      <td>718931.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vekova_skupina   populace  min_vek\n",
       "0           60-64   625465.0       60\n",
       "1           18-24   669492.0       18\n",
       "2           25-29   620925.0       25\n",
       "3           65-69   672418.0       65\n",
       "4           45-49   882586.0       45\n",
       "5           40-44   893321.0       40\n",
       "6           50-54   691083.0       50\n",
       "7           16-17   195016.0       16\n",
       "8           75-79   417201.0       75\n",
       "9           70-74   621177.0       70\n",
       "10            80+   447526.0       80\n",
       "11          55-59   669733.0       55\n",
       "12          35-39   753310.0       35\n",
       "13          12-15   456488.0       12\n",
       "14           0-11  1367105.0        0\n",
       "15          30-34   718931.0       30"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "populace = pd.read_sql_query(\n",
    "    \"\"\"\n",
    "    select vekova_skupina, sum(pocet) populace, min_vek\n",
    "        from populace p \n",
    "        join populace_kategorie k on (k.min_vek <= vek and k.max_vek >= vek)\n",
    "        where orp_kod = 'CZ0'\n",
    "        group by vekova_skupina\n",
    "    \"\"\", \n",
    "db)\n",
    "populace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vekova_skupina</th>\n",
       "      <th>datum</th>\n",
       "      <th>populace_ockovani</th>\n",
       "      <th>populace_plne</th>\n",
       "      <th>populace_posilujici</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0-11</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0-11</td>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12-15</td>\n",
       "      <td>2021-01-19</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12-15</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12-15</td>\n",
       "      <td>2021-02-23</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4404</th>\n",
       "      <td>80+</td>\n",
       "      <td>2021-10-20</td>\n",
       "      <td>364046</td>\n",
       "      <td>362548</td>\n",
       "      <td>19637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4405</th>\n",
       "      <td>80+</td>\n",
       "      <td>2021-10-21</td>\n",
       "      <td>364162</td>\n",
       "      <td>362661</td>\n",
       "      <td>24502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4406</th>\n",
       "      <td>80+</td>\n",
       "      <td>2021-10-22</td>\n",
       "      <td>364262</td>\n",
       "      <td>362723</td>\n",
       "      <td>29384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4407</th>\n",
       "      <td>80+</td>\n",
       "      <td>2021-10-23</td>\n",
       "      <td>364273</td>\n",
       "      <td>362735</td>\n",
       "      <td>30350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4408</th>\n",
       "      <td>80+</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>364283</td>\n",
       "      <td>362744</td>\n",
       "      <td>31073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4409 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     vekova_skupina       datum  populace_ockovani  populace_plne  \\\n",
       "0              0-11  2021-10-01                  1              0   \n",
       "1              0-11  2021-10-12                  2              0   \n",
       "2             12-15  2021-01-19                  1              0   \n",
       "3             12-15  2021-02-17                  1              1   \n",
       "4             12-15  2021-02-23                  2              1   \n",
       "...             ...         ...                ...            ...   \n",
       "4404            80+  2021-10-20             364046         362548   \n",
       "4405            80+  2021-10-21             364162         362661   \n",
       "4406            80+  2021-10-22             364262         362723   \n",
       "4407            80+  2021-10-23             364273         362735   \n",
       "4408            80+  2021-10-24             364283         362744   \n",
       "\n",
       "      populace_posilujici  \n",
       "0                       0  \n",
       "1                       0  \n",
       "2                       0  \n",
       "3                       0  \n",
       "4                       0  \n",
       "...                   ...  \n",
       "4404                19637  \n",
       "4405                24502  \n",
       "4406                29384  \n",
       "4407                30350  \n",
       "4408                31073  \n",
       "\n",
       "[4409 rows x 5 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ockovani = pd.read_sql_query(\n",
    "    f\"\"\"\n",
    "    select datum, vekova_skupina,\n",
    "        sum(case when poradi_davky = 1 then pocet else 0 end) populace_ockovani, \n",
    "        sum(case when poradi_davky = v.davky then pocet else 0 end) populace_plne, \n",
    "        sum(case when poradi_davky = 3 then pocet else 0 end) populace_posilujici\n",
    "    from ockovani_lide o\n",
    "    join vakciny v on (o.vakcina = v.vakcina)\n",
    "    where datum < '2021-10-25'\n",
    "    group by datum, vekova_skupina\n",
    "    \"\"\",\n",
    "    db\n",
    ")\n",
    "ockovani = ockovani.groupby(['vekova_skupina', 'datum']).sum().groupby(level=0).cumsum().reset_index()\n",
    "ockovani\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>datum</th>\n",
       "      <th>tyden</th>\n",
       "      <th>od</th>\n",
       "      <th>do</th>\n",
       "      <th>vekova_skupina</th>\n",
       "      <th>min_vek</th>\n",
       "      <th>populace_ockovani</th>\n",
       "      <th>populace_plne</th>\n",
       "      <th>populace_posilujici</th>\n",
       "      <th>populace</th>\n",
       "      <th>...</th>\n",
       "      <th>hospitalizace_bez</th>\n",
       "      <th>hospitalizace_castecne</th>\n",
       "      <th>hospitalizace_plne</th>\n",
       "      <th>hospitalizace_posilujici</th>\n",
       "      <th>hospitalizace_jip_celkem</th>\n",
       "      <th>hospitalizace_jip_bez</th>\n",
       "      <th>hospitalizace_jip_castecne</th>\n",
       "      <th>hospitalizace_jip_plne</th>\n",
       "      <th>hospitalizace_jip_posilujici</th>\n",
       "      <th>populace_bez</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>25-39</td>\n",
       "      <td>25.0</td>\n",
       "      <td>301584</td>\n",
       "      <td>291189</td>\n",
       "      <td>1504</td>\n",
       "      <td>620925.0</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>319341.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>40-49</td>\n",
       "      <td>40.0</td>\n",
       "      <td>368809</td>\n",
       "      <td>358206</td>\n",
       "      <td>1861</td>\n",
       "      <td>718931.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>350122.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>50-59</td>\n",
       "      <td>50.0</td>\n",
       "      <td>416756</td>\n",
       "      <td>406978</td>\n",
       "      <td>2494</td>\n",
       "      <td>753310.0</td>\n",
       "      <td>...</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>336554.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>60-69</td>\n",
       "      <td>60.0</td>\n",
       "      <td>554958</td>\n",
       "      <td>543546</td>\n",
       "      <td>3988</td>\n",
       "      <td>893321.0</td>\n",
       "      <td>...</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>338363.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>70-79</td>\n",
       "      <td>70.0</td>\n",
       "      <td>604812</td>\n",
       "      <td>599844</td>\n",
       "      <td>5093</td>\n",
       "      <td>882586.0</td>\n",
       "      <td>...</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>277774.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>80+</td>\n",
       "      <td>80.0</td>\n",
       "      <td>485788</td>\n",
       "      <td>481580</td>\n",
       "      <td>4455</td>\n",
       "      <td>691083.0</td>\n",
       "      <td>...</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>205295.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        datum    tyden          od          do vekova_skupina  min_vek  \\\n",
       "0  2021-10-18  2021-42  2021-10-18  2021-10-24          25-39     25.0   \n",
       "1  2021-10-18  2021-42  2021-10-18  2021-10-24          40-49     40.0   \n",
       "2  2021-10-18  2021-42  2021-10-18  2021-10-24          50-59     50.0   \n",
       "3  2021-10-18  2021-42  2021-10-18  2021-10-24          60-69     60.0   \n",
       "4  2021-10-18  2021-42  2021-10-18  2021-10-24          70-79     70.0   \n",
       "5  2021-10-18  2021-42  2021-10-18  2021-10-24            80+     80.0   \n",
       "\n",
       "   populace_ockovani  populace_plne  populace_posilujici  populace  ...  \\\n",
       "0             301584         291189                 1504  620925.0  ...   \n",
       "1             368809         358206                 1861  718931.0  ...   \n",
       "2             416756         406978                 2494  753310.0  ...   \n",
       "3             554958         543546                 3988  893321.0  ...   \n",
       "4             604812         599844                 5093  882586.0  ...   \n",
       "5             485788         481580                 4455  691083.0  ...   \n",
       "\n",
       "   hospitalizace_bez  hospitalizace_castecne  hospitalizace_plne  \\\n",
       "0                 15                       0                   0   \n",
       "1                 16                       1                   1   \n",
       "2                 25                       1                   2   \n",
       "3                 20                       0                   4   \n",
       "4                 45                       0                   4   \n",
       "5                 29                       1                   9   \n",
       "\n",
       "   hospitalizace_posilujici  hospitalizace_jip_celkem  hospitalizace_jip_bez  \\\n",
       "0                         0                         1                      1   \n",
       "1                         0                         2                      1   \n",
       "2                         0                         1                      1   \n",
       "3                         0                         3                      2   \n",
       "4                         1                         8                      6   \n",
       "5                         0                         7                      4   \n",
       "\n",
       "   hospitalizace_jip_castecne  hospitalizace_jip_plne  \\\n",
       "0                           0                       0   \n",
       "1                           0                       1   \n",
       "2                           0                       0   \n",
       "3                           0                       1   \n",
       "4                           0                       1   \n",
       "5                           0                       3   \n",
       "\n",
       "   hospitalizace_jip_posilujici  populace_bez  \n",
       "0                             0      319341.0  \n",
       "1                             0      350122.0  \n",
       "2                             0      336554.0  \n",
       "3                             0      338363.0  \n",
       "4                             1      277774.0  \n",
       "5                             0      205295.0  \n",
       "\n",
       "[6 rows x 26 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(ockovani, populace)\n",
    "df = pd.merge(df, srovnani, left_on=['datum', 'vekova_skupina'], right_on=['od', 'vekova_skupina'])\n",
    "\n",
    "\n",
    "\n",
    "df['vekova_skupina'] = df['vekova_skupina'].replace({'25-29': '25-39', '30-34': '25-39', '35-39': '25-39', '40-44': '40-49', '45-49': '40-49', '50-54': '50-59', '55-59': '50-59', \n",
    "                                                     '60-64': '60-69', '65-69': '60-69', '70-74': '70-79', '75-79': '70-79'})\n",
    "\n",
    "df[['vekova_skupina', 'min_vek']] = df[['vekova_skupina', 'min_vek']].groupby('vekova_skupina').min().reset_index()\n",
    "df = df.groupby(['datum', 'tyden', 'od', 'do', 'vekova_skupina', 'min_vek']).sum().reset_index()\n",
    "\n",
    "df = df[df['datum'] == df['datum'].max()]\n",
    "\n",
    "df['populace_bez'] = df['populace'] - df['populace_ockovani']\n",
    "df['populace_plne'] = df['populace_plne']\n",
    "\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datum</th>\n",
       "      <th>tyden</th>\n",
       "      <th>od</th>\n",
       "      <th>do</th>\n",
       "      <th>vekova_skupina</th>\n",
       "      <th>min_vek</th>\n",
       "      <th>populace_ockovani</th>\n",
       "      <th>populace_plne</th>\n",
       "      <th>populace_posilujici</th>\n",
       "      <th>populace</th>\n",
       "      <th>...</th>\n",
       "      <th>hospitalizace_jip_castecne</th>\n",
       "      <th>hospitalizace_jip_plne</th>\n",
       "      <th>hospitalizace_jip_posilujici</th>\n",
       "      <th>populace_bez</th>\n",
       "      <th>nakazeni_bez_norm</th>\n",
       "      <th>nakazeni_plne_norm</th>\n",
       "      <th>hospitalizace_bez_norm</th>\n",
       "      <th>hospitalizace_plne_norm</th>\n",
       "      <th>hospitalizace_jip_bez_norm</th>\n",
       "      <th>hospitalizace_jip_plne_norm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>25-39</td>\n",
       "      <td>25.0</td>\n",
       "      <td>301584</td>\n",
       "      <td>291189</td>\n",
       "      <td>1504</td>\n",
       "      <td>620925.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>319341.0</td>\n",
       "      <td>279.951525</td>\n",
       "      <td>78.299661</td>\n",
       "      <td>4.697173</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.313145</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>40-49</td>\n",
       "      <td>40.0</td>\n",
       "      <td>368809</td>\n",
       "      <td>358206</td>\n",
       "      <td>1861</td>\n",
       "      <td>718931.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>350122.0</td>\n",
       "      <td>289.898950</td>\n",
       "      <td>84.309029</td>\n",
       "      <td>4.569836</td>\n",
       "      <td>0.279169</td>\n",
       "      <td>0.285615</td>\n",
       "      <td>0.279169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>50-59</td>\n",
       "      <td>50.0</td>\n",
       "      <td>416756</td>\n",
       "      <td>406978</td>\n",
       "      <td>2494</td>\n",
       "      <td>753310.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>336554.0</td>\n",
       "      <td>317.928178</td>\n",
       "      <td>119.908201</td>\n",
       "      <td>7.428228</td>\n",
       "      <td>0.491427</td>\n",
       "      <td>0.297129</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>60-69</td>\n",
       "      <td>60.0</td>\n",
       "      <td>554958</td>\n",
       "      <td>543546</td>\n",
       "      <td>3988</td>\n",
       "      <td>893321.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>338363.0</td>\n",
       "      <td>355.239787</td>\n",
       "      <td>148.653472</td>\n",
       "      <td>5.910812</td>\n",
       "      <td>0.735908</td>\n",
       "      <td>0.591081</td>\n",
       "      <td>0.183977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>70-79</td>\n",
       "      <td>70.0</td>\n",
       "      <td>604812</td>\n",
       "      <td>599844</td>\n",
       "      <td>5093</td>\n",
       "      <td>882586.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>277774.0</td>\n",
       "      <td>398.525420</td>\n",
       "      <td>121.865018</td>\n",
       "      <td>16.200220</td>\n",
       "      <td>0.833550</td>\n",
       "      <td>2.160029</td>\n",
       "      <td>0.333420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-42</td>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>2021-10-24</td>\n",
       "      <td>80+</td>\n",
       "      <td>80.0</td>\n",
       "      <td>485788</td>\n",
       "      <td>481580</td>\n",
       "      <td>4455</td>\n",
       "      <td>691083.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>205295.0</td>\n",
       "      <td>308.336784</td>\n",
       "      <td>98.010715</td>\n",
       "      <td>14.126014</td>\n",
       "      <td>1.868848</td>\n",
       "      <td>1.948416</td>\n",
       "      <td>0.622949</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        datum    tyden          od          do vekova_skupina  min_vek  \\\n",
       "0  2021-10-18  2021-42  2021-10-18  2021-10-24          25-39     25.0   \n",
       "1  2021-10-18  2021-42  2021-10-18  2021-10-24          40-49     40.0   \n",
       "2  2021-10-18  2021-42  2021-10-18  2021-10-24          50-59     50.0   \n",
       "3  2021-10-18  2021-42  2021-10-18  2021-10-24          60-69     60.0   \n",
       "4  2021-10-18  2021-42  2021-10-18  2021-10-24          70-79     70.0   \n",
       "5  2021-10-18  2021-42  2021-10-18  2021-10-24            80+     80.0   \n",
       "\n",
       "   populace_ockovani  populace_plne  populace_posilujici  populace  ...  \\\n",
       "0             301584         291189                 1504  620925.0  ...   \n",
       "1             368809         358206                 1861  718931.0  ...   \n",
       "2             416756         406978                 2494  753310.0  ...   \n",
       "3             554958         543546                 3988  893321.0  ...   \n",
       "4             604812         599844                 5093  882586.0  ...   \n",
       "5             485788         481580                 4455  691083.0  ...   \n",
       "\n",
       "   hospitalizace_jip_castecne  hospitalizace_jip_plne  \\\n",
       "0                           0                       0   \n",
       "1                           0                       1   \n",
       "2                           0                       0   \n",
       "3                           0                       1   \n",
       "4                           0                       2   \n",
       "5                           0                       3   \n",
       "\n",
       "   hospitalizace_jip_posilujici  populace_bez  nakazeni_bez_norm  \\\n",
       "0                             0      319341.0         279.951525   \n",
       "1                             0      350122.0         289.898950   \n",
       "2                             0      336554.0         317.928178   \n",
       "3                             0      338363.0         355.239787   \n",
       "4                             1      277774.0         398.525420   \n",
       "5                             0      205295.0         308.336784   \n",
       "\n",
       "   nakazeni_plne_norm  hospitalizace_bez_norm  hospitalizace_plne_norm  \\\n",
       "0           78.299661                4.697173                 0.000000   \n",
       "1           84.309029                4.569836                 0.279169   \n",
       "2          119.908201                7.428228                 0.491427   \n",
       "3          148.653472                5.910812                 0.735908   \n",
       "4          121.865018               16.200220                 0.833550   \n",
       "5           98.010715               14.126014                 1.868848   \n",
       "\n",
       "   hospitalizace_jip_bez_norm  hospitalizace_jip_plne_norm  \n",
       "0                    0.313145                     0.000000  \n",
       "1                    0.285615                     0.279169  \n",
       "2                    0.297129                     0.000000  \n",
       "3                    0.591081                     0.183977  \n",
       "4                    2.160029                     0.333420  \n",
       "5                    1.948416                     0.622949  \n",
       "\n",
       "[6 rows x 32 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_norm = df.copy()\n",
    "\n",
    "datasets = ['nakazeni', 'hospitalizace', 'hospitalizace_jip']\n",
    "groups = ['bez', 'plne']\n",
    "\n",
    "for d in datasets:\n",
    "    df_norm[d + '_plne'] = df_norm[d + '_plne'] + df_norm[d + '_posilujici']\n",
    "    for g in groups:\n",
    "        df_norm[d + '_' + g + '_norm'] = ((100000 * df_norm[d + '_' + g]) / df_norm['populace_' + g]).replace({np.nan: 0})\n",
    "        \n",
    "df_norm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "language": "python",
   "name": "venv"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}