src/BD_w15_hw.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HW\n",
    "\n",
    "Get statistic from MedHistory"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prep Data"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 470,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import random\n",
    "import math\n",
    "import json\n",
    "import time\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import copy as copy\n",
    "import matplotlib.pyplot as plt\n",
    "import translators as ts\n",
    "import translators.server as tss\n",
    "from translate import Translator\n",
    "from pandas import Timestamp\n",
    "from datetime import datetime\n",
    "from os import getcwd\n",
    "from os.path import join\n",
    "%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 471,
   "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>公布日</th>\n",
       "      <th>案號</th>\n",
       "      <th>性別</th>\n",
       "      <th>年齡</th>\n",
       "      <th>慢性病史</th>\n",
       "      <th>活動接觸史</th>\n",
       "      <th>發病日</th>\n",
       "      <th>症狀</th>\n",
       "      <th>採檢日</th>\n",
       "      <th>住院/隔離日</th>\n",
       "      <th>確診日</th>\n",
       "      <th>死亡日</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>4397</td>\n",
       "      <td>男</td>\n",
       "      <td>60多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-12-15</td>\n",
       "      <td>10511</td>\n",
       "      <td>男</td>\n",
       "      <td>50多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>8724</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>12235</td>\n",
       "      <td>男</td>\n",
       "      <td>80多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-09</td>\n",
       "      <td>8748</td>\n",
       "      <td>女</td>\n",
       "      <td>70多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         公布日     案號 性別    年齡 慢性病史    活動接觸史                  發病日       症狀  \\\n",
       "0 2022-12-19   4397  男  60多歲    有        無  2022-05-22 00:00:00       發燒   \n",
       "1 2022-12-15  10511  男  50多歲    有        有  2022-06-03 00:00:00      呼吸喘   \n",
       "2 2022-11-09   8724  女  90多歲    有        有  2022-05-24 00:00:00   發燒、喉嚨痛   \n",
       "3 2022-10-25  12235  男  80多歲    有  接觸其他確診者  2022-06-08 00:00:00  食慾不振、昏睡   \n",
       "4 2022-10-09   8748  女  70多歲    有  接觸其他確診者                   --       --   \n",
       "\n",
       "                   採檢日               住院/隔離日                  確診日  \\\n",
       "0  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "                   死亡日  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 471,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_path_1 = os.path.join(\n",
    "    os.getcwd().rstrip('src'), 'data', 'TaiwanDeathDetail2021.xlsx')\n",
    "data_path_2 = os.path.join(\n",
    "    os.getcwd().rstrip('src'), 'data', 'TaiwanDeathDetail20220611.xlsx')\n",
    "data1 = pd.read_excel(data_path_1, sheet_name='TaiwanDeathDetail2021.csv')\n",
    "data2 = pd.read_excel(data_path_2, sheet_name='TaiwanDeathDetail20220611.csv')\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 472,
   "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>日期</th>\n",
       "      <th>案號</th>\n",
       "      <th>性別</th>\n",
       "      <th>年齡</th>\n",
       "      <th>慢性病史</th>\n",
       "      <th>是否接種COVID19疫苗</th>\n",
       "      <th>發病日</th>\n",
       "      <th>症狀</th>\n",
       "      <th>採檢日</th>\n",
       "      <th>住院／隔離日</th>\n",
       "      <th>確診日</th>\n",
       "      <th>死亡日</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>男</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1劑</td>\n",
       "      <td>調查中</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-20 00:00:00</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期  案號 性別      年齡     慢性病史 是否接種COVID19疫苗                  發病日  \\\n",
       "0 2022-06-11 NaN  男  90多歲以上     慢性肺病            3劑  2022-05-18 00:00:00   \n",
       "1 2022-06-11 NaN  女  90多歲以上     高血壓等             無  2022-06-01 00:00:00   \n",
       "2 2022-06-11 NaN  女  90多歲以上    心血管疾病             無  2022-05-30 00:00:00   \n",
       "3 2022-06-11 NaN  女  90多歲以上   心血管疾病等            1劑                  調查中   \n",
       "4 2022-06-11 NaN  女  90多歲以上  神經系統疾病等            3劑  2022-05-20 00:00:00   \n",
       "\n",
       "                       症狀  採檢日 住院／隔離日                  確診日        死亡日  \n",
       "0                      發燒  NaN    NaN  2022-05-18 00:00:00 2022-06-06  \n",
       "1               發燒、咳嗽、呼吸喘  NaN    NaN  2022-06-01 00:00:00 2022-06-07  \n",
       "2              咳嗽、呼吸困難、顫抖  NaN    NaN  2022-05-30 00:00:00 2022-06-06  \n",
       "3                     調查中  NaN    NaN  2022-06-06 00:00:00 2022-06-07  \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤  NaN    NaN  2022-05-22 00:00:00 2022-05-21  "
      ]
     },
     "execution_count": 472,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Translate Column Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 473,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'公布日': 'Date', '案號': 'CaseID', '性別': 'Gender', '年齡': 'Age', '慢性病史': 'MedHistory', '活動接觸史': 'Vac', '發病日': 'SickDate', '症狀': 'Symptoms', '採檢日': 'SpecimenDate', '住院/隔離日': 'QuarantineDate', '確診日': 'ConfirmDate', '死亡日': 'DeceaseDate'}\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>4397</td>\n",
       "      <td>男</td>\n",
       "      <td>60多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-12-15</td>\n",
       "      <td>10511</td>\n",
       "      <td>男</td>\n",
       "      <td>50多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>8724</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>12235</td>\n",
       "      <td>男</td>\n",
       "      <td>80多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-09</td>\n",
       "      <td>8748</td>\n",
       "      <td>女</td>\n",
       "      <td>70多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender   Age MedHistory      Vac             SickDate  \\\n",
       "0 2022-12-19    4397      男  60多歲          有        無  2022-05-22 00:00:00   \n",
       "1 2022-12-15   10511      男  50多歲          有        有  2022-06-03 00:00:00   \n",
       "2 2022-11-09    8724      女  90多歲          有        有  2022-05-24 00:00:00   \n",
       "3 2022-10-25   12235      男  80多歲          有  接觸其他確診者  2022-06-08 00:00:00   \n",
       "4 2022-10-09    8748      女  70多歲          有  接觸其他確診者                   --   \n",
       "\n",
       "  Symptoms         SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0       發燒  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1      呼吸喘  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2   發燒、喉嚨痛  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  食慾不振、昏睡  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4       --  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 473,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_ts = ['Date', 'CaseID', 'Gender', 'Age', 'MedHistory', 'Vac', 'SickDate', 'Symptoms', 'SpecimenDate', 'QuarantineDate', 'ConfirmDate', 'DeceaseDate']\n",
    "columns_dict = dict(zip(data1.columns, columns_ts))\n",
    "print(columns_dict)\n",
    "data1.rename(columns=columns_dict, inplace=True)\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'日期': 'Date', '案號': 'CaseID', '性別': 'Gender', '年齡': 'Age', '慢性病史': 'MedHistory', '是否接種COVID19疫苗': 'Vac', '發病日': 'SickDate', '症狀': 'Symptoms', '採檢日': 'SpecimenDate', '住院／隔離日': 'QuarantineDate', '確診日': 'ConfirmDate', '死亡日': 'DeceaseDate'}\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>男</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1劑</td>\n",
       "      <td>調查中</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>女</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-20 00:00:00</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender     Age MedHistory Vac             SickDate  \\\n",
       "0 2022-06-11     NaN      男  90多歲以上       慢性肺病  3劑  2022-05-18 00:00:00   \n",
       "1 2022-06-11     NaN      女  90多歲以上       高血壓等   無  2022-06-01 00:00:00   \n",
       "2 2022-06-11     NaN      女  90多歲以上      心血管疾病   無  2022-05-30 00:00:00   \n",
       "3 2022-06-11     NaN      女  90多歲以上     心血管疾病等  1劑                  調查中   \n",
       "4 2022-06-11     NaN      女  90多歲以上    神經系統疾病等  3劑  2022-05-20 00:00:00   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 474,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_ts = ['Date', 'CaseID', 'Gender', 'Age', 'MedHistory', 'Vac', 'SickDate',\n",
    "              'Symptoms', 'SpecimenDate', 'QuarantineDate', 'ConfirmDate', 'DeceaseDate']\n",
    "columns_dict = dict(zip(data2.columns, columns_ts))\n",
    "print(columns_dict)\n",
    "data2.rename(columns=columns_dict, inplace=True)\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Translate Column 'Gender'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 475,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-12-15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70多歲</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender   Age MedHistory      Vac             SickDate  \\\n",
       "0 2022-12-19    4397      M  60多歲          有        無  2022-05-22 00:00:00   \n",
       "1 2022-12-15   10511      M  50多歲          有        有  2022-06-03 00:00:00   \n",
       "2 2022-11-09    8724      F  90多歲          有        有  2022-05-24 00:00:00   \n",
       "3 2022-10-25   12235      M  80多歲          有  接觸其他確診者  2022-06-08 00:00:00   \n",
       "4 2022-10-09    8748      F  70多歲          有  接觸其他確診者                   --   \n",
       "\n",
       "  Symptoms         SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0       發燒  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1      呼吸喘  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2   發燒、喉嚨痛  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  食慾不振、昏睡  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4       --  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 475,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gender_dict = {'男': 'M', '女': 'F'}\n",
    "data1['Gender'] = data1['Gender'].map(gender_dict)\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 476,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1劑</td>\n",
       "      <td>調查中</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90多歲以上</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-20 00:00:00</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender     Age MedHistory Vac             SickDate  \\\n",
       "0 2022-06-11     NaN      M  90多歲以上       慢性肺病  3劑  2022-05-18 00:00:00   \n",
       "1 2022-06-11     NaN      F  90多歲以上       高血壓等   無  2022-06-01 00:00:00   \n",
       "2 2022-06-11     NaN      F  90多歲以上      心血管疾病   無  2022-05-30 00:00:00   \n",
       "3 2022-06-11     NaN      F  90多歲以上     心血管疾病等  1劑                  調查中   \n",
       "4 2022-06-11     NaN      F  90多歲以上    神經系統疾病等  3劑  2022-05-20 00:00:00   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 476,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2['Gender'] = data2['Gender'].map(gender_dict)\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Translate Column 'Age'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 477,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['100多歲', '30多歲', '40多歲', '50多歲', '60多歲', '70多歲', '80多歲', '90多歲']\n",
      "{'100多歲': 100.5, '30多歲': 30.5, '40多歲': 40.5, '50多歲': 50.5, '60多歲': 60.5, '70多歲': 70.5, '80多歲': 80.5, '90多歲': 90.5}\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-12-15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>有</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>接觸其他確診者</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender   Age MedHistory      Vac             SickDate  \\\n",
       "0 2022-12-19    4397      M  60.5          有        無  2022-05-22 00:00:00   \n",
       "1 2022-12-15   10511      M  50.5          有        有  2022-06-03 00:00:00   \n",
       "2 2022-11-09    8724      F  90.5          有        有  2022-05-24 00:00:00   \n",
       "3 2022-10-25   12235      M  80.5          有  接觸其他確診者  2022-06-08 00:00:00   \n",
       "4 2022-10-09    8748      F  70.5          有  接觸其他確診者                   --   \n",
       "\n",
       "  Symptoms         SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0       發燒  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1      呼吸喘  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2   發燒、喉嚨痛  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  食慾不振、昏睡  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4       --  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 477,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ages = data1['Age'].unique().tolist()\n",
    "ages.sort()\n",
    "print(ages)\n",
    "ages_ts = [100.5, 30.5, 40.5, 50.5, 60.5, 70.5, 80.5, 90.5]\n",
    "ages_dict = dict(zip(ages, ages_ts))\n",
    "print(ages_dict)\n",
    "data1['Age'] = data1['Age'].map(ages_dict)\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 478,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['90多歲以上', '80多歲', '70多歲', '60多歲', '50多歲', '40多歲', '30多歲', '90歲以上', '20多歲', '10多歲', '未滿5歲', '未滿10歲', nan, '102歲', '101歲', '100歲', '98歲', '97歲', '95歲', '94歲', '93歲', '92歲', '91歲', '90歲', '89歲', '88歲', '87歲', '86歲', '85歲', '84歲', '83歲', '82歲', '81歲', '80歲', '79歲', '78歲', '77歲', '76歲', '75歲', '74歲', '73歲', '72歲', '71歲', '70歲', '69歲', '68歲', '67歲', '64歲', '62歲', '61歲', '60歲', '55歲', '48歲', '38歲', '22歲', '4歲', '1歲', '90多歲']\n",
      "{'90多歲以上': 90.5, '80多歲': 80.5, '70多歲': 70.5, '60多歲': 60.5, '50多歲': 50.5, '40多歲': 40.5, '30多歲': 30.5, '90歲以上': 90.5, '20多歲': 20.5, '10多歲': 10.5, '未滿5歲': 4.5, '未滿10歲': 9.5, nan: nan, '102歲': 102, '101歲': 101, '100歲': 100, '98歲': 98, '97歲': 97, '95歲': 95, '94歲': 94, '93歲': 96, '92歲': 92, '91歲': 91, '90歲': 90, '89歲': 89, '88歲': 88, '87歲': 87, '86歲': 86, '85歲': 85, '84歲': 84, '83歲': 83, '82歲': 82, '81歲': 81, '80歲': 80, '79歲': 79, '78歲': 78, '77歲': 77, '76歲': 76, '75歲': 75, '74歲': 74, '73歲': 73, '72歲': 72, '71歲': 71, '70歲': 70, '69歲': 69, '68歲': 68, '67歲': 67, '64歲': 64, '62歲': 62, '61歲': 61, '60歲': 60, '55歲': 55, '48歲': 48, '38歲': 38, '22歲': 22, '4歲': 4, '1歲': 1, '90多歲': 90.5}\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>無</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1劑</td>\n",
       "      <td>調查中</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3劑</td>\n",
       "      <td>2022-05-20 00:00:00</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender   Age MedHistory Vac             SickDate  \\\n",
       "0 2022-06-11     NaN      M  90.5       慢性肺病  3劑  2022-05-18 00:00:00   \n",
       "1 2022-06-11     NaN      F  90.5       高血壓等   無  2022-06-01 00:00:00   \n",
       "2 2022-06-11     NaN      F  90.5      心血管疾病   無  2022-05-30 00:00:00   \n",
       "3 2022-06-11     NaN      F  90.5     心血管疾病等  1劑                  調查中   \n",
       "4 2022-06-11     NaN      F  90.5    神經系統疾病等  3劑  2022-05-20 00:00:00   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 478,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ages = data2['Age'].unique().tolist()\n",
    "print(ages)\n",
    "ages_ts = [90.5, 80.5, 70.5, 60.5, 50.5, 40.5, 30.5, 90.5, 20.5, 10.5, 4.5, 9.5, np.NaN, 102, 101, 100, 98, 97, 95, 94, 96, 92, 91, 90, 89, 88, 87, 86, 85, 84, 83, 82, 81, 80, 79, 78, 77, 76, 75, 74, 73, 72, 71, 70, 69, 68, 67, 64, 62, 61, 60, 55, 48, 38, 22, 4, 1, 90.5]\n",
    "ages_dict = dict(zip(ages, ages_ts))\n",
    "print(ages_dict)\n",
    "data2['Age'] = data2['Age'].map(ages_dict)\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Translate Column 'Vac'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 479,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['無', '有', '接觸其他確診者', '萬華活動史', '--', '8月6日自越南入境', '萬華區活動史', nan, '相關活動接觸史', '接觸其他確診案', '調查中', '接觸其他確診個案', '4/11境外移入', '菲律賓回台', '緬甸回台', '境外移入（英國變種病毒）']\n",
      "['N', 'Y', nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan]\n",
      "{'無': 'N', '有': 'Y', '接觸其他確診者': nan, '萬華活動史': nan, '--': nan, '8月6日自越南入境': nan, '萬華區活動史': nan, nan: nan, '相關活動接觸史': nan, '接觸其他確診案': nan, '調查中': nan, '接觸其他確診個案': nan, '4/11境外移入': nan, '菲律賓回台': nan, '緬甸回台': nan, '境外移入（英國變種病毒）': nan}\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-12-15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender   Age MedHistory  Vac             SickDate  \\\n",
       "0 2022-12-19    4397      M  60.5          有    N  2022-05-22 00:00:00   \n",
       "1 2022-12-15   10511      M  50.5          有    Y  2022-06-03 00:00:00   \n",
       "2 2022-11-09    8724      F  90.5          有    Y  2022-05-24 00:00:00   \n",
       "3 2022-10-25   12235      M  80.5          有  NaN  2022-06-08 00:00:00   \n",
       "4 2022-10-09    8748      F  70.5          有  NaN                   --   \n",
       "\n",
       "  Symptoms         SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0       發燒  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1      呼吸喘  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2   發燒、喉嚨痛  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  食慾不振、昏睡  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4       --  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 479,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = data1['Vac'].unique().tolist()\n",
    "print(columns)\n",
    "columns_ts = []\n",
    "for element in columns:\n",
    "    if element == '有':\n",
    "        columns_ts.append('Y')\n",
    "    elif element == '無':\n",
    "        columns_ts.append('N')\n",
    "    else:\n",
    "        columns_ts.append(np.NaN)\n",
    "print(columns_ts)\n",
    "columns_dict = dict(zip(columns, columns_ts))\n",
    "print(columns_dict)\n",
    "data1['Vac'] = data1['Vac'].map(columns_dict)\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 480,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['3劑', '無', '1劑', '2劑', '4劑', '調查中', nan]\n",
      "{'3劑': 3, '無': 0, '1劑': 1, '2劑': 2, '4劑': 4, '調查中': nan, nan: nan}\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>調查中</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-05-20 00:00:00</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  CaseID Gender   Age MedHistory  Vac             SickDate  \\\n",
       "0 2022-06-11     NaN      M  90.5       慢性肺病  3.0  2022-05-18 00:00:00   \n",
       "1 2022-06-11     NaN      F  90.5       高血壓等  0.0  2022-06-01 00:00:00   \n",
       "2 2022-06-11     NaN      F  90.5      心血管疾病  0.0  2022-05-30 00:00:00   \n",
       "3 2022-06-11     NaN      F  90.5     心血管疾病等  1.0                  調查中   \n",
       "4 2022-06-11     NaN      F  90.5    神經系統疾病等  3.0  2022-05-20 00:00:00   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 480,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = data2['Vac'].unique().tolist()\n",
    "print(columns)\n",
    "columns_ts = [3, 0, 1, 2, 4, np.NaN, np.NaN]\n",
    "columns_dict = dict(zip(columns, columns_ts))\n",
    "print(columns_dict)\n",
    "data2['Vac'] = data2['Vac'].map(columns_dict)\n",
    "data2.head()\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert Column 'Date'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 481,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac             SickDate  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N  2022-05-22 00:00:00   \n",
       "1  2022/12/15   10511      M  50.5          有    Y  2022-06-03 00:00:00   \n",
       "2  2022/11/09    8724      F  90.5          有    Y  2022-05-24 00:00:00   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN  2022-06-08 00:00:00   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN                   --   \n",
       "\n",
       "  Symptoms         SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0       發燒  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1      呼吸喘  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2   發燒、喉嚨痛  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  食慾不振、昏睡  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4       --  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 481,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1['Date'] = data1['Date'].dt.strftime('%Y/%m/%d')\n",
    "data1.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 482,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>調查中</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-05-20 00:00:00</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac             SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0  2022-05-18 00:00:00   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0  2022-06-01 00:00:00   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0  2022-05-30 00:00:00   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0                  調查中   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0  2022-05-20 00:00:00   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 482,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2['Date'] = data2['Date'].dt.strftime('%Y/%m/%d')\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert Column 'SickDate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 483,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[datetime.datetime(2022, 5, 22, 0, 0), datetime.datetime(2022, 6, 3, 0, 0), datetime.datetime(2022, 5, 24, 0, 0), datetime.datetime(2022, 6, 8, 0, 0), '--', datetime.datetime(2022, 6, 30, 0, 0), datetime.datetime(2022, 8, 10, 0, 0), datetime.datetime(2022, 5, 19, 0, 0), datetime.datetime(2022, 6, 19, 0, 0), datetime.datetime(2022, 8, 17, 0, 0), datetime.datetime(2022, 7, 9, 0, 0), datetime.datetime(2022, 9, 7, 0, 0), datetime.datetime(2022, 5, 21, 0, 0), datetime.datetime(2022, 5, 30, 0, 0), datetime.datetime(2022, 8, 26, 0, 0), datetime.datetime(2022, 6, 26, 0, 0), datetime.datetime(2022, 6, 27, 0, 0), datetime.datetime(2022, 6, 23, 0, 0), nan, datetime.datetime(2022, 7, 27, 0, 0), datetime.datetime(2022, 5, 25, 0, 0), datetime.datetime(2022, 6, 1, 0, 0), datetime.datetime(2022, 6, 2, 0, 0), datetime.datetime(2022, 6, 13, 0, 0), datetime.datetime(2022, 5, 17, 0, 0), datetime.datetime(2022, 5, 28, 0, 0), datetime.datetime(2022, 6, 17, 0, 0), datetime.datetime(2022, 6, 14, 0, 0), datetime.datetime(2022, 5, 29, 0, 0), datetime.datetime(2022, 7, 28, 0, 0), datetime.datetime(2022, 5, 31, 0, 0), datetime.datetime(2022, 6, 28, 0, 0), datetime.datetime(2022, 5, 26, 0, 0), datetime.datetime(2022, 6, 12, 0, 0), datetime.datetime(2022, 6, 16, 0, 0), datetime.datetime(2022, 6, 15, 0, 0), datetime.datetime(2022, 6, 20, 0, 0), datetime.datetime(2022, 6, 18, 0, 0), datetime.datetime(2022, 5, 18, 0, 0), datetime.datetime(2022, 6, 4, 0, 0), datetime.datetime(2022, 6, 24, 0, 0), datetime.datetime(2022, 6, 6, 0, 0), datetime.datetime(2022, 5, 27, 0, 0), datetime.datetime(2022, 6, 29, 0, 0), datetime.datetime(2022, 6, 25, 0, 0), datetime.datetime(2022, 7, 16, 0, 0), datetime.datetime(2022, 6, 9, 0, 0), datetime.datetime(2022, 5, 23, 0, 0), datetime.datetime(2022, 5, 15, 0, 0), datetime.datetime(2022, 6, 10, 0, 0), datetime.datetime(2022, 7, 3, 0, 0), datetime.datetime(2022, 5, 12, 0, 0), datetime.datetime(2022, 5, 11, 0, 0), datetime.datetime(2022, 6, 22, 0, 0), datetime.datetime(2022, 5, 14, 0, 0), datetime.datetime(2022, 7, 4, 0, 0), datetime.datetime(2022, 6, 5, 0, 0), datetime.datetime(2022, 5, 13, 0, 0), datetime.datetime(2022, 7, 2, 0, 0), datetime.datetime(2022, 6, 21, 0, 0), datetime.datetime(2022, 6, 11, 0, 0), datetime.datetime(2022, 5, 20, 0, 0), datetime.datetime(2022, 5, 16, 0, 0), datetime.datetime(2022, 6, 7, 0, 0), datetime.datetime(2022, 5, 9, 0, 0), datetime.datetime(2022, 4, 11, 0, 0), datetime.datetime(2022, 5, 6, 0, 0), datetime.datetime(2022, 5, 8, 0, 0), datetime.datetime(2022, 5, 7, 0, 0), datetime.datetime(2022, 5, 10, 0, 0), datetime.datetime(2022, 5, 3, 0, 0), '調查中', datetime.datetime(2022, 5, 2, 0, 0), '無症狀', datetime.datetime(2021, 5, 18, 0, 0), datetime.datetime(2022, 4, 19, 0, 0), datetime.datetime(2022, 1, 19, 0, 0), datetime.datetime(2020, 12, 24, 0, 0), datetime.datetime(2020, 12, 26, 0, 0), datetime.datetime(2022, 1, 28, 0, 0)]\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-05-22</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-06-03</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-05-24</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-08</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac   SickDate Symptoms  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N 2022-05-22       發燒   \n",
       "1  2022/12/15   10511      M  50.5          有    Y 2022-06-03      呼吸喘   \n",
       "2  2022/11/09    8724      F  90.5          有    Y 2022-05-24   發燒、喉嚨痛   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN 2022-06-08  食慾不振、昏睡   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN        NaT       --   \n",
       "\n",
       "          SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 483,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sds = data1['SickDate'].unique().tolist()\n",
    "print(sds)\n",
    "sds_ts = []\n",
    "for element in sds:\n",
    "    if element == '無症狀' or element == '調查中' or element == '--':\n",
    "        sds_ts.append(np.NaN)\n",
    "    else:\n",
    "        sds_ts.append(element)\n",
    "# print(sds_ts)\n",
    "sds_dict = dict(zip(sds, sds_ts))\n",
    "# print(sds_dict)\n",
    "data1['SickDate'] = data1['SickDate'].map(sds_dict)\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 484,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022-06-03 00:00:00</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022-05-31 00:00:00</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022-06-08 00:00:00</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate Symptoms  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N  2022/05/22       發燒   \n",
       "1  2022/12/15   10511      M  50.5          有    Y  2022/06/03      呼吸喘   \n",
       "2  2022/11/09    8724      F  90.5          有    Y  2022/05/24   發燒、喉嚨痛   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN  2022/06/08  食慾不振、昏睡   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN         NaN       --   \n",
       "\n",
       "          SpecimenDate       QuarantineDate          ConfirmDate  \\\n",
       "0  2022-05-22 00:00:00  2022-05-23 00:00:00  2022-05-24 00:00:00   \n",
       "1  2022-06-03 00:00:00      6/4住院，8月14日解除隔離  2022-06-05 00:00:00   \n",
       "2  2022-05-31 00:00:00    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00   \n",
       "3  2022-06-08 00:00:00        6/8\\n7/14解除隔離  2022-06-10 00:00:00   \n",
       "4  2022-05-30 00:00:00     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00   \n",
       "\n",
       "           DeceaseDate  \n",
       "0  2022-12-01 00:00:00  \n",
       "1  2022-11-19 00:00:00  \n",
       "2  2022-10-11 00:00:00  \n",
       "3  2022-10-22 00:00:00  \n",
       "4  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 484,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1['SickDate'] = data1['SickDate'].dt.strftime('%Y/%m/%d')\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 485,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[datetime.datetime(2022, 5, 18, 0, 0), datetime.datetime(2022, 6, 1, 0, 0), datetime.datetime(2022, 5, 30, 0, 0), '調查中', datetime.datetime(2022, 5, 20, 0, 0), '機構住民，篩檢確診', datetime.datetime(2022, 5, 24, 0, 0), '長期臥床', datetime.datetime(2022, 5, 28, 0, 0), datetime.datetime(2022, 5, 31, 0, 0), datetime.datetime(2022, 5, 22, 0, 0), datetime.datetime(2022, 6, 4, 0, 0), datetime.datetime(2022, 5, 23, 0, 0), datetime.datetime(2022, 5, 13, 0, 0), datetime.datetime(2022, 6, 5, 0, 0), datetime.datetime(2022, 5, 29, 0, 0), datetime.datetime(2022, 6, 7, 0, 0), datetime.datetime(2022, 6, 2, 0, 0), datetime.datetime(2022, 5, 27, 0, 0), datetime.datetime(2022, 6, 6, 0, 0), datetime.datetime(2022, 5, 14, 0, 0), datetime.datetime(2022, 5, 2, 0, 0), datetime.datetime(2022, 5, 19, 0, 0), datetime.datetime(2022, 5, 26, 0, 0), datetime.datetime(2022, 5, 12, 0, 0), '到院前無生命徵象', datetime.datetime(2022, 6, 3, 0, 0), datetime.datetime(2022, 5, 17, 0, 0), datetime.datetime(2022, 5, 15, 0, 0), datetime.datetime(2022, 5, 21, 0, 0), datetime.datetime(2022, 5, 9, 0, 0), datetime.datetime(2022, 5, 25, 0, 0), datetime.datetime(2022, 5, 11, 0, 0), nan, datetime.datetime(2022, 5, 7, 0, 0), datetime.datetime(2022, 5, 8, 0, 0), datetime.datetime(2022, 5, 10, 0, 0), '到院前已無生命徵象', '昏迷送醫急救死亡', datetime.datetime(2022, 5, 16, 0, 0), datetime.datetime(2022, 4, 30, 0, 0), datetime.datetime(2022, 4, 26, 0, 0), datetime.datetime(2022, 5, 3, 0, 0), datetime.datetime(2022, 4, 22, 0, 0), datetime.datetime(2022, 5, 6, 0, 0), datetime.datetime(2022, 5, 5, 0, 0), datetime.datetime(2022, 5, 4, 0, 0), '5月19日', datetime.datetime(2022, 4, 28, 0, 0), datetime.datetime(2022, 4, 10, 0, 0), '居住機構採檢確診', '就醫需求採檢確診', '因其他原因就醫採檢確診', datetime.datetime(2022, 4, 1, 0, 0), '住院期間採檢確診', datetime.datetime(2022, 4, 27, 0, 0), datetime.datetime(2022, 4, 25, 0, 0), '因跌倒送醫採檢確診', datetime.datetime(2022, 5, 1, 0, 0), '家中昏迷送醫採檢確診', datetime.datetime(2022, 4, 29, 0, 0), '疫調中', datetime.datetime(2022, 4, 19, 0, 0), '就醫採檢確診', '死後採檢確診', '無', datetime.datetime(2022, 4, 24, 0, 0), '死亡後採檢確診', '--', '接觸者採檢確診', datetime.datetime(2022, 4, 21, 0, 0), '昏迷送醫急救無效死亡，採檢確診', '心肌梗塞送醫急救採檢確診', datetime.datetime(2022, 4, 20, 0, 0), datetime.datetime(2022, 4, 7, 0, 0), '自行就醫採檢確診', '居住機構接觸者採檢確診', '家中昏迷送醫急救無效死亡， 採檢確診', '家中昏迷送醫急救無效，採檢確診', '因跌倒外傷就醫治療，採檢確診', '癌症安寧照護，住院期間採檢確診', '於家中昏迷送醫，急救無效死亡，採檢確診', '因接觸者採檢確診', '於居住機構昏迷送醫，急救無效死亡，採檢確診', '因症就醫採檢確診', '於住家跌倒後意識改變送醫採檢確診', datetime.datetime(2022, 4, 15, 0, 0), datetime.datetime(2022, 4, 23, 0, 0), datetime.datetime(2022, 4, 18, 0, 0), '接觸者採檢', '居住機構配合疫情採檢確診', datetime.datetime(2022, 4, 17, 0, 0), datetime.datetime(2022, 4, 13, 0, 0), '1/28入境採檢確診', '2021/12於越南確診']\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-05-18</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022-06-01</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022-05-30</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-05-20</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac   SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0 2022-05-18   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0 2022-06-01   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0 2022-05-30   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0        NaT   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0 2022-05-20   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 485,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sds = data2['SickDate'].unique().tolist()\n",
    "print(sds)\n",
    "sds_ts = []\n",
    "for element in sds:\n",
    "    if type(element) == str:\n",
    "        sds_ts.append(np.NaN)\n",
    "    else:\n",
    "        sds_ts.append(element)\n",
    "# print(sds_ts)\n",
    "sds_dict = dict(zip(sds, sds_ts))\n",
    "# print(sds_dict)\n",
    "data2['SickDate'] = data2['SickDate'].map(sds_dict)\n",
    "data2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 486,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/20</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0  2022/05/18   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0  2022/06/01   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0  2022/05/30   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0         NaN   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0  2022/05/20   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 486,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2['SickDate'] = data2['SickDate'].dt.strftime('%Y/%m/%d')\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert Column 'SpecimenDate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 487,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>2022-05-23 00:00:00</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>6/4住院，8月14日解除隔離</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>5/30住院、\\n7/26解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>6/8\\n7/14解除隔離</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>5/31住院\\n9/17解除隔離</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate Symptoms  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N  2022/05/22       發燒   \n",
       "1  2022/12/15   10511      M  50.5          有    Y  2022/06/03      呼吸喘   \n",
       "2  2022/11/09    8724      F  90.5          有    Y  2022/05/24   發燒、喉嚨痛   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN  2022/06/08  食慾不振、昏睡   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN         NaN       --   \n",
       "\n",
       "  SpecimenDate       QuarantineDate          ConfirmDate          DeceaseDate  \n",
       "0   2022/05/22  2022-05-23 00:00:00  2022-05-24 00:00:00  2022-12-01 00:00:00  \n",
       "1   2022/06/03      6/4住院，8月14日解除隔離  2022-06-05 00:00:00  2022-11-19 00:00:00  \n",
       "2   2022/05/31    5/30住院、\\n7/26解除隔離  2022-06-01 00:00:00  2022-10-11 00:00:00  \n",
       "3   2022/06/08        6/8\\n7/14解除隔離  2022-06-10 00:00:00  2022-10-22 00:00:00  \n",
       "4   2022/05/30     5/31住院\\n9/17解除隔離  2022-06-01 00:00:00  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 487,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sds = data1['SpecimenDate'].unique().tolist()\n",
    "# print(sds)\n",
    "sds_ts = []\n",
    "for element in sds:\n",
    "    if type(element) == str:\n",
    "        sds_ts.append(np.NaN)\n",
    "    else:\n",
    "        sds_ts.append(element)\n",
    "# print(sds_ts)\n",
    "sds_dict = dict(zip(sds, sds_ts))\n",
    "# print(sds_dict)\n",
    "data1['SpecimenDate'] = data1['SpecimenDate'].map(sds_dict)\n",
    "data1['SpecimenDate'] = data1['SpecimenDate'].dt.strftime('%Y/%m/%d')\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 488,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[nan, '2022/05/13', '2022/05/30', '2022/05/29', '2022/05/16', '2022/05/22', '2022/05/20', '2022/05/17', '2022/06/01', '2022/05/11', '2022/05/21', '2022/05/08', '2022/05/23', '2022/05/27', '2022/05/25', '2022/05/31', '2022/05/24', '2022/05/19', '2022/05/28', '2022/04/26', '2022/05/10', '2022/05/18', '2022/06/02', '2022/05/03', '2022/05/14', '2022/05/12', '2022/06/03', '2022/04/22', '2022/05/26', '2022/05/06', '2022/05/05', '2022/05/09', '2022/05/02', '2022/05/15', '2022/04/28', '2022/05/07', '2022/05/01', '2022/04/30', '2022/05/04', '2022/04/29', '2022/04/19', '2022/04/12', '2022/04/16', '2022/04/25', '2022/04/21', '2022/04/27', '2022/04/20', '2022/04/07', '2022/04/24', '2022/04/14', '2022/04/23', '2022/04/09', '2022/04/18', '2022/04/13', '2022/04/17', '2022/01/28', '2022/01/19']\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/20</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0  2022/05/18   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0  2022/06/01   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0  2022/05/30   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0         NaN   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0  2022/05/20   \n",
       "\n",
       "                 Symptoms SpecimenDate QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN            NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN            NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN            NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN            NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN            NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 488,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sds = data2['SpecimenDate'].unique().tolist()\n",
    "# print(sds)\n",
    "sds_ts = []\n",
    "for element in sds:\n",
    "    if type(element) == float:\n",
    "        sds_ts.append(np.NaN)\n",
    "    elif type(element) == 'datetime.datetime':\n",
    "        sds_ts.append(element)\n",
    "    elif type(element) == str:\n",
    "        if '陽' in element:\n",
    "            element = element.split('(')[0]\n",
    "            element = '2022/' + element\n",
    "            element = pd.to_datetime(element)\n",
    "            sds_ts.append(element)\n",
    "        else:\n",
    "            sds_ts.append(np.NaN)\n",
    "    else:\n",
    "        sds_ts.append(element)\n",
    "# print(sds_ts)\n",
    "sds_dict = dict(zip(sds, sds_ts))\n",
    "# print(sds_dict)\n",
    "data2['SpecimenDate'] = data2['SpecimenDate'].map(sds_dict)\n",
    "data2['SpecimenDate'] = data2['SpecimenDate'].dt.strftime('%Y/%m/%d')\n",
    "print(data2['SpecimenDate'].unique().tolist())\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert Column 'QuarantineDate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 489,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>2022/05/23</td>\n",
       "      <td>2022-05-24 00:00:00</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>2022/06/04</td>\n",
       "      <td>2022-06-05 00:00:00</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>2022-06-10 00:00:00</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022/06/24</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate Symptoms  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N  2022/05/22       發燒   \n",
       "1  2022/12/15   10511      M  50.5          有    Y  2022/06/03      呼吸喘   \n",
       "2  2022/11/09    8724      F  90.5          有    Y  2022/05/24   發燒、喉嚨痛   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN  2022/06/08  食慾不振、昏睡   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN         NaN       --   \n",
       "\n",
       "  SpecimenDate QuarantineDate          ConfirmDate          DeceaseDate  \n",
       "0   2022/05/22     2022/05/23  2022-05-24 00:00:00  2022-12-01 00:00:00  \n",
       "1   2022/06/03     2022/06/04  2022-06-05 00:00:00  2022-11-19 00:00:00  \n",
       "2   2022/05/31     2022/05/30  2022-06-01 00:00:00  2022-10-11 00:00:00  \n",
       "3   2022/06/08     2022/05/31  2022-06-10 00:00:00  2022-10-22 00:00:00  \n",
       "4   2022/05/30     2022/06/24  2022-06-01 00:00:00  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 489,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qds = data1['QuarantineDate'].unique().tolist()\n",
    "# print(qds)\n",
    "qds_ts = []\n",
    "for element in qds:\n",
    "    if type(element) == str:\n",
    "        try:\n",
    "            if '住院' in element:\n",
    "                element = element.split('住院')[0]\n",
    "                element = '2022/' + element\n",
    "                element = pd.to_datetime(element)\n",
    "                qds_ts.append(element)\n",
    "        except:\n",
    "            qds_ts.append(np.NaN)\n",
    "    else:\n",
    "        qds_ts.append(element)\n",
    "# print(qds_ts)\n",
    "qds_dict = dict(zip(qds, qds_ts))\n",
    "# print(qds_dict)\n",
    "data1['QuarantineDate'] = data1['QuarantineDate'].map(qds_dict)\n",
    "data1['QuarantineDate'] = data1['QuarantineDate'].dt.strftime('%Y/%m/%d')\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 490,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[nan, '--', '使用高流量氧氣鼻管及瑞德西韋等藥物治療', '於家中昏倒緊急送醫', '出現咳嗽、發燒等症狀住院採檢', '採安寧治療，未住加護病房', '4/19日於住家經家人發現昏迷送醫，急救無效', '4/14下午陷入昏迷，直到晚間7點半才被送入醫院', '已接種3劑疫苗', '居家隔離期間因症採檢確診，未接種COVID-19疫苗', '入境時因身體不適送醫，同日採檢確診並安排住院隔離治療', '因有就醫需求，於1/10由衛生單位安排住院隔離，1/19隔離期滿前採檢確診', '2021/12/17專機接回住院隔離治療']\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-18 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-01 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-30 00:00:00</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-06 00:00:00</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/20</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-22 00:00:00</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0  2022/05/18   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0  2022/06/01   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0  2022/05/30   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0         NaN   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0  2022/05/20   \n",
       "\n",
       "                 Symptoms SpecimenDate  QuarantineDate          ConfirmDate  \\\n",
       "0                      發燒          NaN             NaN  2022-05-18 00:00:00   \n",
       "1               發燒、咳嗽、呼吸喘          NaN             NaN  2022-06-01 00:00:00   \n",
       "2              咳嗽、呼吸困難、顫抖          NaN             NaN  2022-05-30 00:00:00   \n",
       "3                     調查中          NaN             NaN  2022-06-06 00:00:00   \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN             NaN  2022-05-22 00:00:00   \n",
       "\n",
       "  DeceaseDate  \n",
       "0  2022-06-06  \n",
       "1  2022-06-07  \n",
       "2  2022-06-06  \n",
       "3  2022-06-07  \n",
       "4  2022-05-21  "
      ]
     },
     "execution_count": 490,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qds = data2['QuarantineDate'].unique().tolist()\n",
    "print(qds)\n",
    "qds_ts = []\n",
    "for element in qds:\n",
    "    if type(element) == float:\n",
    "        qds_ts.append(np.NaN)\n",
    "    elif type(element) == str:\n",
    "        qds_ts.append(np.NaN)\n",
    "    else:\n",
    "        qds_ts.append(element)\n",
    "# print(qds_ts)\n",
    "qds_dict = dict(zip(qds, qds_ts))\n",
    "# print(qds_dict)\n",
    "data2['QuarantineDate'] = data2['QuarantineDate'].map(qds_dict)\n",
    "data2.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert Column 'ConfirmDate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 491,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>2022/05/23</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>2022/06/04</td>\n",
       "      <td>2022/06/05</td>\n",
       "      <td>2022-11-19 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>2022-10-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>2022/06/10</td>\n",
       "      <td>2022-10-22 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022/06/24</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>2022-10-04 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate Symptoms  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N  2022/05/22       發燒   \n",
       "1  2022/12/15   10511      M  50.5          有    Y  2022/06/03      呼吸喘   \n",
       "2  2022/11/09    8724      F  90.5          有    Y  2022/05/24   發燒、喉嚨痛   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN  2022/06/08  食慾不振、昏睡   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN         NaN       --   \n",
       "\n",
       "  SpecimenDate QuarantineDate ConfirmDate          DeceaseDate  \n",
       "0   2022/05/22     2022/05/23  2022/05/24  2022-12-01 00:00:00  \n",
       "1   2022/06/03     2022/06/04  2022/06/05  2022-11-19 00:00:00  \n",
       "2   2022/05/31     2022/05/30  2022/06/01  2022-10-11 00:00:00  \n",
       "3   2022/06/08     2022/05/31  2022/06/10  2022-10-22 00:00:00  \n",
       "4   2022/05/30     2022/06/24  2022/06/01  2022-10-04 00:00:00  "
      ]
     },
     "execution_count": 491,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cfds = data1['ConfirmDate'].unique().tolist()\n",
    "# print(cfds)\n",
    "cfds_ts = []\n",
    "for element in cfds:\n",
    "    if type(element) == str:\n",
    "        cfds_ts.append(np.NaN)\n",
    "    else:\n",
    "        cfds_ts.append(element)\n",
    "# print(cfds_ts)\n",
    "cfds_dict = dict(zip(cfds, cfds_ts))\n",
    "# print(cfds_dict)\n",
    "data1['ConfirmDate'] = data1['ConfirmDate'].map(cfds_dict)\n",
    "data1['ConfirmDate'] = data1['ConfirmDate'].dt.strftime('%Y/%m/%d')\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 492,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/06</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/20</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0  2022/05/18   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0  2022/06/01   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0  2022/05/30   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0         NaN   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0  2022/05/20   \n",
       "\n",
       "                 Symptoms SpecimenDate  QuarantineDate ConfirmDate DeceaseDate  \n",
       "0                      發燒          NaN             NaN  2022/05/18  2022-06-06  \n",
       "1               發燒、咳嗽、呼吸喘          NaN             NaN  2022/06/01  2022-06-07  \n",
       "2              咳嗽、呼吸困難、顫抖          NaN             NaN  2022/05/30  2022-06-06  \n",
       "3                     調查中          NaN             NaN  2022/06/06  2022-06-07  \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN             NaN  2022/05/22  2022-05-21  "
      ]
     },
     "execution_count": 492,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cfds = data2['ConfirmDate'].unique().tolist()\n",
    "# print(cfds)\n",
    "cfds_ts = []\n",
    "for element in cfds:\n",
    "    if type(element) == float:\n",
    "        cfds_ts.append(np.NaN)\n",
    "    elif type(element) == str:\n",
    "        cfds_ts.append(np.NaN)\n",
    "    else:\n",
    "        cfds_ts.append(element)\n",
    "# print(cfds_ts)\n",
    "cfds_dict = dict(zip(cfds, cfds_ts))\n",
    "# print(cfds_dict)\n",
    "data2['ConfirmDate'] = data2['ConfirmDate'].map(cfds_dict)\n",
    "data2['ConfirmDate'] = data2['ConfirmDate'].dt.strftime('%Y/%m/%d')\n",
    "data2.head()\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert Column 'DeceaseDate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 493,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/12/19</td>\n",
       "      <td>4397</td>\n",
       "      <td>M</td>\n",
       "      <td>60.5</td>\n",
       "      <td>有</td>\n",
       "      <td>N</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>發燒</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>2022/05/23</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>2022/12/01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/12/15</td>\n",
       "      <td>10511</td>\n",
       "      <td>M</td>\n",
       "      <td>50.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>呼吸喘</td>\n",
       "      <td>2022/06/03</td>\n",
       "      <td>2022/06/04</td>\n",
       "      <td>2022/06/05</td>\n",
       "      <td>2022/11/19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/11/09</td>\n",
       "      <td>8724</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>有</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022/05/24</td>\n",
       "      <td>發燒、喉嚨痛</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>2022/10/11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/10/25</td>\n",
       "      <td>12235</td>\n",
       "      <td>M</td>\n",
       "      <td>80.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>食慾不振、昏睡</td>\n",
       "      <td>2022/06/08</td>\n",
       "      <td>2022/05/31</td>\n",
       "      <td>2022/06/10</td>\n",
       "      <td>2022/10/22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/10/09</td>\n",
       "      <td>8748</td>\n",
       "      <td>F</td>\n",
       "      <td>70.5</td>\n",
       "      <td>有</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>--</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022/06/24</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>2022/10/04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate Symptoms  \\\n",
       "0  2022/12/19    4397      M  60.5          有    N  2022/05/22       發燒   \n",
       "1  2022/12/15   10511      M  50.5          有    Y  2022/06/03      呼吸喘   \n",
       "2  2022/11/09    8724      F  90.5          有    Y  2022/05/24   發燒、喉嚨痛   \n",
       "3  2022/10/25   12235      M  80.5          有  NaN  2022/06/08  食慾不振、昏睡   \n",
       "4  2022/10/09    8748      F  70.5          有  NaN         NaN       --   \n",
       "\n",
       "  SpecimenDate QuarantineDate ConfirmDate DeceaseDate  \n",
       "0   2022/05/22     2022/05/23  2022/05/24  2022/12/01  \n",
       "1   2022/06/03     2022/06/04  2022/06/05  2022/11/19  \n",
       "2   2022/05/31     2022/05/30  2022/06/01  2022/10/11  \n",
       "3   2022/06/08     2022/05/31  2022/06/10  2022/10/22  \n",
       "4   2022/05/30     2022/06/24  2022/06/01  2022/10/04  "
      ]
     },
     "execution_count": 493,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dds = data1['DeceaseDate'].unique().tolist()\n",
    "# print(dds)\n",
    "dds_ts = []\n",
    "for element in dds:\n",
    "    if type(element) == str:\n",
    "        dds_ts.append(np.NaN)\n",
    "    else:\n",
    "        dds_ts.append(element)\n",
    "# print(dds_ts)\n",
    "dds_dict = dict(zip(dds, dds_ts))\n",
    "# print(dds_dict)\n",
    "data1['DeceaseDate'] = data1['DeceaseDate'].map(dds_dict)\n",
    "data1['DeceaseDate'] = data1['DeceaseDate'].dt.strftime('%Y/%m/%d')\n",
    "data1.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 494,
   "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>Date</th>\n",
       "      <th>CaseID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>MedHistory</th>\n",
       "      <th>Vac</th>\n",
       "      <th>SickDate</th>\n",
       "      <th>Symptoms</th>\n",
       "      <th>SpecimenDate</th>\n",
       "      <th>QuarantineDate</th>\n",
       "      <th>ConfirmDate</th>\n",
       "      <th>DeceaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>90.5</td>\n",
       "      <td>慢性肺病</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>發燒</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/05/18</td>\n",
       "      <td>2022/06/06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>高血壓等</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>發燒、咳嗽、呼吸喘</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/01</td>\n",
       "      <td>2022/06/07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>咳嗽、呼吸困難、顫抖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/05/30</td>\n",
       "      <td>2022/06/06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>心血管疾病等</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>調查中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/06/06</td>\n",
       "      <td>2022/06/07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022/06/11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>90.5</td>\n",
       "      <td>神經系統疾病等</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022/05/20</td>\n",
       "      <td>呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022/05/22</td>\n",
       "      <td>2022/05/21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  CaseID Gender   Age MedHistory  Vac    SickDate  \\\n",
       "0  2022/06/11     NaN      M  90.5       慢性肺病  3.0  2022/05/18   \n",
       "1  2022/06/11     NaN      F  90.5       高血壓等  0.0  2022/06/01   \n",
       "2  2022/06/11     NaN      F  90.5      心血管疾病  0.0  2022/05/30   \n",
       "3  2022/06/11     NaN      F  90.5     心血管疾病等  1.0         NaN   \n",
       "4  2022/06/11     NaN      F  90.5    神經系統疾病等  3.0  2022/05/20   \n",
       "\n",
       "                 Symptoms SpecimenDate  QuarantineDate ConfirmDate DeceaseDate  \n",
       "0                      發燒          NaN             NaN  2022/05/18  2022/06/06  \n",
       "1               發燒、咳嗽、呼吸喘          NaN             NaN  2022/06/01  2022/06/07  \n",
       "2              咳嗽、呼吸困難、顫抖          NaN             NaN  2022/05/30  2022/06/06  \n",
       "3                     調查中          NaN             NaN  2022/06/06  2022/06/07  \n",
       "4  呼吸困難、血氧下降、胸部X光片肺部 雙側浸潤          NaN             NaN  2022/05/22  2022/05/21  "
      ]
     },
     "execution_count": 494,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dds = data2['DeceaseDate'].unique().tolist()\n",
    "# print(dds)\n",
    "data2['DeceaseDate'] = data2['DeceaseDate'].dt.strftime('%Y/%m/%d')\n",
    "data2.head()\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export Datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 495,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path_1 = os.path.join(\n",
    "    os.getcwd().rstrip('src'), 'data', 'TaiwanDeathDetail2021_ts.csv')\n",
    "data_path_2 = os.path.join(\n",
    "    os.getcwd().rstrip('src'), 'data', 'TaiwanDeathDetail20220611_ts.csv')\n",
    "data1.to_csv(data_path_1, index=False)\n",
    "data2.to_csv(data_path_2, index=False)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}