CovMexico_Data_Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`It is worth noting that the user, who want to use this notebook to process the data from Spanish to English, does not need to understand these codes but run the entire notebook directly will be enough`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The newest \"COVID19_MEXICO.csv\" file  can be installed from: https://www.gob.mx/salud/documentos/datos-abiertos-152127\n",
    "\n",
    "As one column in the original database was written in UTF-8 whereas other was written in a different format which made impossible to read the dataset directly, consequently it was necessary to solve this problem by the following Python codes to convert all datas in the same format and also translate to English.\n",
    "\n",
    "We will need the following function in order to transform the column 'MUNICIPAL_RES' which contains more than 2500 municipalities which were encoded by numbers.\n",
    "\n",
    "To use the following function you will need the file **Municipals_Catalog.csv**, whose name ('**Municipals_Catalog.csv**') was **copied and pasted separately by author** from the original datasheet which called \"**Catálogo MUNICIPIOS**\". This file can be found in: **dictionary_data_COVID19 >> Catalogos_0412.csv** in the link provided from the outset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv \n",
    "\n",
    "Dic_Entity = {1:'AGUASCALIENTES',2:'BAJA CALIFORNIA',3:'BAJA CALIFORNIA SUR',4:'CAMPECHE',5:'COAHUILA DE ZARAGOZA',\n",
    "              6:'COLIMA',7:'CHIAPAS',8:'CHIHUAHUA',9:'MEXICO CITY',10:'DURANGO',11:'GUANAJUATO',12:'GUERRERO',\n",
    "              13:'HIDALGO',14:'JALISCO',15:'STATE OF MEXICO',16:'MICHOACÁN DE OCAMPO',17:'MORELOS',18:'NAYARIT',19:'NUEVO LEÓN',\n",
    "              20:'OAXACA',21:'PUEBLA',22:'QUERÉTARO',23:'QUINTANA ROO',24:'SAN LUIS POTOSÍ',25:'SINALOA',26:'SONORA',\n",
    "              27:'TABASCO',28:'TAMAULIPAS',29:'TLAXCALA',30:'VERACRUZ',31:'YUCATÁN',32:'ZACATECAS',\n",
    "              36:'UNITED MEXICAN STATES',97:'NOT APLIED',98:'IGNORED',99:float('nan')}\n",
    "\n",
    "def Transfor_Mun_Res(filename = 'Municipals_Catalog.csv', Dic_Entity = Dic_Entity):\n",
    "    \"\"\"\n",
    "    Return a dictionary where the key is the Entity name and the value is another subdictionary; \n",
    "    the key in subdictionary is the Municipal number and the value is the Municipal Name\n",
    "    \"\"\"\n",
    "    dic = {}\n",
    "    with open(filename, encoding='utf-8') as csvfile:\n",
    "        reader = csv.reader(csvfile, delimiter=';')\n",
    "        next(reader) # we skip the header\n",
    "        \n",
    "        for line in reader:\n",
    "            Municipal_Code, Municipal, Entity_Code = line\n",
    "            \n",
    "            if Entity_Code < '10':                 # if the Entity_Code is '0X' where X is a number in [1-9]\n",
    "                Entity_Code = int(Entity_Code[-1]) # transform '0X' into an integer X\n",
    "        \n",
    "            else:                              # If the Entity_Code is already a number > 9\n",
    "                Entity_Code = int(Entity_Code) # Transform directly 'XX' into an integer XX / X is a natural number in [1-9]\n",
    "            \n",
    "            dic_value = dic.setdefault(Entity_Code, dict())\n",
    "            dic_value[int(Municipal_Code)] = Municipal\n",
    "            \n",
    "    for elem in dic:\n",
    "        dic[elem][999] = float('nan')          # The value 99 or 999 mean Unspecified, which we will consider as NaN value \n",
    "        \n",
    "    return dic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "Dic_Municipals = Transfor_Mun_Res() #We will need this dictionary in the next block"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reformatting the column **'MUNICIPAL_RES'**, **'COUNTRY_NATIONALITY'** and **'COUNTRY_ORIGIN'** due to the convenience, with them we then create a new csv file.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv     # Import csv library\n",
    "\n",
    "### Create the Dictionary which contains the pair (key, value) where the key is the original format and \n",
    "### the value is the correspondent transformation word in English.\n",
    "\n",
    "Dic = {'ArchipiÃ©lago de Svalbard':'Svalbard', 'AscensiÃ³n':'Ascension Island', 'AzerbaiyÃ¡n - Islas Azores':'Azores',\n",
    "      'BÃ©lgica':'Belgium', 'CamerÃºn':'kamerun', 'CanadÃ¡':'Canada', 'EspaÃ±a':'Spain', \n",
    "      'Estados Unidos de AmÃ©rica':'United States', 'Gran BretaÃ±a (Reino Unido)':'Great Britain',\n",
    "      'HaitÃ­':'Haiti', 'HungrÃ­a':'Hungary', 'IrÃ¡n':'Iran', \n",
    "      'Islas VÃ­rgenes de Estados Unidos de AmÃ©rica':'United States Virgin Islands', 'JapÃ³n':'Japan',\n",
    "      'LÃ­bano':'Lebanon','MÃ©xico':'Mexico','PakistÃ¡n':'Pakistan','PerÃº':'Peru','Principado de MÃ³naco':'Monaco',\n",
    "      'RepÃºblica Checa y RepÃºblica Eslovaca':'Czech Republic and Slovak Republic', \n",
    "      'RepÃºblica de Angola': 'Angola', 'RepÃºblica de Corea':'South Korea', 'RepÃºblica de Costa Rica':'Costa Rica',\n",
    "      'RepÃºblica de Guyana':'Guyana', 'RepÃºblica de Honduras':'Honduras', 'RepÃºblica de Mauricio':'Mauritius',\n",
    "      'RepÃºblica de PanamÃ¡':'Panama','RepÃºblica de Trinidad y Tobago':'Trinidad and Tobago',\n",
    "      'RepÃºblica DemocrÃ¡tica de Corea':'North Korea', 'RepÃºblica Dominicana':'Dominican Republic',\n",
    "      'RepÃºblica Oriental del Uruguay':'Uruguay', 'Republica DemocrÃ¡tica del Congo':'Congo', 'SudÃ¡frica':'South Africa',\n",
    "      'TaiwÃ¡n':'Taiwan', 'TurquÃ­a':'Turkey',\n",
    "        'Alemania':'German', 'Archipiélago de Svalbard':'Svalbard','Argelia':'Algeria', 'Argentina':'Argentina', \n",
    "        'Ascensión':'Ascension Island', 'Australia':'Australia', 'Austria':'Austria',\n",
    "        'Azerbaiyán - Islas Azores':'Azores', 'Bangladesh':'Bangladesh', 'Belice':'Belice', 'Bolivia':'Bolivia',\n",
    "        'Bosnia y Herzegovina':'Bosnia and Herzegovina', 'Brasil':'Brazil', 'Bélgica':'Gallia Belgica', \n",
    "        'Camerún':'Cameroon', 'Canadá':'Canada', 'Chile':'Chile', 'China':'China', 'Colombia':'Columbia',\n",
    "        'Commonwealth de Dominica':'Dominica', 'Congo':'Congo', 'Costa de Marfil':'Ivory Coast', 'Croacia':'Croatia',\n",
    "        'Cuba':'Cuba', 'Dinamarca':'Denmark', 'Ecuador':'Ecuador', 'Egipto':'Egypt', 'El Salvador':'El Salvador',\n",
    "        'Eritrea':'Eritrea', 'Eslovaquia':'Eslovaquia', 'Eslovenia':'Slovenia', 'España':'Spain',\n",
    "        'Estados Unidos de América':'United States', 'Filipinas':'Philippines', 'Finlandia':'Filandia',\n",
    "        'Francia':'France', 'Ghana':'Ghana', 'Gran Bretaña (Reino Unido)':'Great Britain', 'Grecia':'Greece', \n",
    "        'Guatemala':'Guatemala', 'Guinea Ecuatorial':'Equatorial Guinea', 'Haití':'Haiti', 'Holanda':'Holland', \n",
    "        'Hong Kong':'Hong Kong', 'Hungría':'Hungary', 'India':'India', 'Indonesia':'Indonesia',\n",
    "        'Irlanda':'Ireland', 'Irán':'Iran', 'Islandia':'Iceland',\n",
    "        'Islas Vírgenes de Estados Unidos de América':'United States Virgin Islands', 'Israel':'Israel',\n",
    "        'Italia':'Italy', 'Jamaica':'Jamaica', 'Japón':'Japan', 'Letonia':'Latvia', 'Libia':'Libya', \n",
    "        'Lituania':'Lithuania', 'Líbano':'Lebanon', 'Macao':'Macao', 'Malasia':'Malaysia', 'Marruecos':'Morocco',\n",
    "        'Micronesia':'Micronesia', 'Moldavia':'Moldova', 'México':'Mexico','Nepal':'Nepal', 'Nicaragua':'Nicaragua',\n",
    "        'Noruega':'Norway', 'Nueva Zelandia':'New Zealand', 'Otro':'Other', 'Pakistán':'Pakistan', 'Paraguay':'Paraguay',\n",
    "        'Perú':'Peru', 'Polonia':'Poland', 'Portugal':'Portugal', 'Principado de Mónaco':'Monaco', \n",
    "        'Republica Democrática del Congo':'Congo', 'República Checa y República Eslovaca':'Czech Republic and Slovak Republic',\n",
    "        'República Democrática de Corea':'North Korea', 'República Dominicana':'Dominican Republic',\n",
    "        'República Oriental del Uruguay':'Uruguay', 'República de Angola':'Angola', 'República de Corea':'South Korea',\n",
    "        'República de Costa Rica':'Costa Rica', 'República de Guyana':'Guyana', 'República de Honduras':'Honduras',\n",
    "        'República de Mauricio':'Mauritius', 'República de Panamá':'Panama',\n",
    "        'República de Trinidad y Tobago':'Trinidad and Tobago', 'Rumania':'Romania', 'Rusia':'Russia', \n",
    "        'SE DESCONOCE':'Unknown', 'Siria':'Syria', 'Sudáfrica':'South Africa', 'Suecia':'Sweden', 'Suiza':'Switzerland',\n",
    "        'Taiwán':'Taiwan', 'Trieste':'Trieste', 'Turquía':'Turkey', 'Ucrania':'Ukraine', 'Venezuela':'Venezuela', \n",
    "        'Vietnam':'Vietnam', 'Zimbabwe':'Zimbabwe', 'Zona Neutral':'Demilitarized zone', '99':float('nan')}\n",
    "\n",
    "\n",
    "def formatting_countries_entities_municipals(filename, dic = Dic, dic_Municipals = Dic_Municipals):\n",
    "    \n",
    "    new_csvfile = 'Trans_COVID19_MEXICO.csv'\n",
    "    with open(new_csvfile,'w', encoding='utf-8') as new_csvfile:\n",
    "        writer = csv.writer(new_csvfile, delimiter=',', lineterminator = '\\n') #lineterminator to avoid skipping lines when writing to csv\n",
    "        # write the header:\n",
    "        writer.writerow([\"LAST_UPDATE\",             # The last update date about the information regarding the patient\n",
    "                             \"ID_REGISTRATION\",     # ID Number of the patient\n",
    "                             \"ORIGIN\",              # Whether Sentinel surveillance was carried out through the respiratory disease monitoring health unit system (USMER) or not.\n",
    "                             \"SECTOR\",\n",
    "                             \"ENTITY_UM\",\n",
    "                             \"SEX\",\n",
    "                             \"ENTITY_NAT\",         \n",
    "                             \"ENTITY_RES\",\n",
    "                             \"MUNICIPALITY_RES\",\n",
    "                             \"PATIENT_TYPE\",       # Whether the patient was hospitalized after being diagnosticated\n",
    "                             \"ADMISSION_DATE\", \n",
    "                             \"SYMPTOMS_DATE\",\n",
    "                             \"DEATH_DATE\",\n",
    "                             \"INTUBATED\",\n",
    "                             \"PNEUMONIA\",\n",
    "                             \"AGE\",\n",
    "                             \"NATIONALITY\",         # Whether the patient is Mexican or Foreigner\n",
    "                             \"PREGNANT\",\n",
    "                             \"SPEAK_INDIGENOUS_LANGUAGE\",\n",
    "                             \"INDIGENOUS\",          # Indigenous patient\n",
    "                             \"DIABETES\",\n",
    "                             \"COPD\",                # Chronic obstructive pulmonary disease\n",
    "                             \"ASTHMA\",           \n",
    "                             \"INMUSUPR\",            # Immunosuppression\n",
    "                             \"HYPERTENSION\",\n",
    "                             \"OTHER_DISEASE\",\n",
    "                             \"CARDIOVASCULAR\",\n",
    "                             \"OBESITY\",\n",
    "                             \"CHRONIC_KIDNEY_DISEASE\",\n",
    "                             \"SMOKE\",\n",
    "                             \"OTHER_CASE_CONTACT\",  # Whether the patient contacted with other diagnosticated case with SARS CoV-2\n",
    "                             \"TESTED\",              # Whether the patient were took a sample to test coronavirus possession\n",
    "                             \"RESULT_LAB\",          # Positivo SARS-CoV-2 or not (Result of laboratory)\n",
    "                             \"FINAL_CLASIFICATION\", #\n",
    "                             \"MIGRANT\",\n",
    "                             \"COUNTRY_NATIONALITY\", # The nationality of the foreigners\n",
    "                             \"COUNTRY_ORIGIN\",      # The via where the infected patients came from\n",
    "                             \"UCI\"])\n",
    "        \n",
    "        with open(filename) as csvfile:\n",
    "            reader = csv.reader(csvfile, delimiter=',')\n",
    "            \n",
    "            next(reader) # skip the heading\n",
    "            for line in reader:\n",
    "                ### Reformatting the wrong string which was written in Spanish\n",
    "                line[-3] = dic[line[-3]] if line[-3] in dic else 'Other'    # Reformatting the Nationality\n",
    "                line[-2] = dic[line[-2]] if line[-2] in dic else 'Other'    # Reformatting the Origin Country\n",
    "                try:\n",
    "                    Municipals = dic_Municipals[int(line[7])] #Find the municipals which a determinated Entity contains\n",
    "                    line[8] = Municipals[int(line[8])]\n",
    "                except KeyError: \n",
    "                    # However, there are a tiny portion of rows which represent incoherence between Entity_Res and Municipal_Res, \n",
    "                    # probably due to artificial error, then we decided to assign those small proportion into NaN value\n",
    "                    line[8] = float('nan')\n",
    "                \n",
    "                ### Reformatting all Date column, 9999-99-99 is considered as NaN value in the original Date Variable\n",
    "                line[0] = float('nan') if line[0] == '9999-99-99' else line[0]\n",
    "                line[10] = float('nan') if line[10] == '9999-99-99' else line[10]\n",
    "                line[11] = float('nan') if line[11] == '9999-99-99' else line[11]\n",
    "                line[12] = float('nan') if line[12] == '9999-99-99' else line[12]\n",
    "                \n",
    "                ### Write the lines in csv file with corrected version\n",
    "                writer.writerow(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reformatting the datasheet into one new csvfile ''Trans_COVID19_MEXICO.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "formatting_countries_entities_municipals(\"201102COVID19MEXICO.csv\", Dic) # Transformation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the new csv file generated before, we now refformate the rest of columns which can be done easily and efficiently by the function **map()**, given a python **dictionary** (see the following chunks).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>LAST_UPDATE</th>\n",
       "      <th>ID_REGISTRATION</th>\n",
       "      <th>ORIGIN</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>ENTITY_UM</th>\n",
       "      <th>SEX</th>\n",
       "      <th>ENTITY_NAT</th>\n",
       "      <th>ENTITY_RES</th>\n",
       "      <th>MUNICIPALITY_RES</th>\n",
       "      <th>PATIENT_TYPE</th>\n",
       "      <th>...</th>\n",
       "      <th>CHRONIC_KIDNEY_DISEASE</th>\n",
       "      <th>SMOKE</th>\n",
       "      <th>OTHER_CASE_CONTACT</th>\n",
       "      <th>TESTED</th>\n",
       "      <th>RESULT_LAB</th>\n",
       "      <th>FINAL_CLASIFICATION</th>\n",
       "      <th>MIGRANT</th>\n",
       "      <th>COUNTRY_NATIONALITY</th>\n",
       "      <th>COUNTRY_ORIGIN</th>\n",
       "      <th>UCI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>002371</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>LOS CABOS</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>11fb00</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>CUAUHTÉMOC</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>092521</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>VENUSTIANO CARRANZA</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>0955a5</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>VILLA DE ÁLVAREZ</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>1a1f12</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>PUERTO VALLARTA</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2414877</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>222039</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>CUAJIMALPA DE MORELOS</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2414878</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>35a5b0</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>CUAJIMALPA DE MORELOS</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2414879</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>3c74bf</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>ZAPOPAN</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2414880</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>33ccb9</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>9</td>\n",
       "      <td>GUSTAVO A. MADERO</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2414881</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>27eecd</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>SALTILLO</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>99</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Other</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2414882 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        LAST_UPDATE ID_REGISTRATION  ORIGIN  SECTOR  ENTITY_UM  SEX  \\\n",
       "0        2020-11-02          002371       1       4          3    2   \n",
       "1        2020-11-02          11fb00       1      12          9    2   \n",
       "2        2020-11-02          092521       1      12          9    1   \n",
       "3        2020-11-02          0955a5       2      12          6    1   \n",
       "4        2020-11-02          1a1f12       2       4         14    2   \n",
       "...             ...             ...     ...     ...        ...  ...   \n",
       "2414877  2020-11-02          222039       1      12          9    1   \n",
       "2414878  2020-11-02          35a5b0       2      12          9    1   \n",
       "2414879  2020-11-02          3c74bf       2       9         14    1   \n",
       "2414880  2020-11-02          33ccb9       1      12          9    2   \n",
       "2414881  2020-11-02          27eecd       2       9          5    1   \n",
       "\n",
       "         ENTITY_NAT  ENTITY_RES       MUNICIPALITY_RES  PATIENT_TYPE  ...  \\\n",
       "0                 3           3              LOS CABOS             1  ...   \n",
       "1                 9           9             CUAUHTÉMOC             1  ...   \n",
       "2                 9           9    VENUSTIANO CARRANZA             1  ...   \n",
       "3                14           6       VILLA DE ÁLVAREZ             1  ...   \n",
       "4                 9          14        PUERTO VALLARTA             2  ...   \n",
       "...             ...         ...                    ...           ...  ...   \n",
       "2414877           9           9  CUAJIMALPA DE MORELOS             1  ...   \n",
       "2414878          11           9  CUAJIMALPA DE MORELOS             1  ...   \n",
       "2414879          14          14                ZAPOPAN             1  ...   \n",
       "2414880          17           9      GUSTAVO A. MADERO             1  ...   \n",
       "2414881           5           5               SALTILLO             1  ...   \n",
       "\n",
       "        CHRONIC_KIDNEY_DISEASE SMOKE OTHER_CASE_CONTACT  TESTED  RESULT_LAB  \\\n",
       "0                            2     2                 99       1           1   \n",
       "1                            2     2                  1       1           1   \n",
       "2                            2     2                  2       1           1   \n",
       "3                            2     2                  2       1           1   \n",
       "4                            2     2                  2       1           1   \n",
       "...                        ...   ...                ...     ...         ...   \n",
       "2414877                      2     2                  2       1           3   \n",
       "2414878                      2     2                  2       1           3   \n",
       "2414879                      2     2                  2       1           4   \n",
       "2414880                      2     2                  1       1           4   \n",
       "2414881                      2     2                  2       1           4   \n",
       "\n",
       "         FINAL_CLASIFICATION  MIGRANT  COUNTRY_NATIONALITY  COUNTRY_ORIGIN  \\\n",
       "0                          3       99               Mexico           Other   \n",
       "1                          3       99               Mexico           Other   \n",
       "2                          3       99               Mexico           Other   \n",
       "3                          3       99               Mexico           Other   \n",
       "4                          3       99               Mexico           Other   \n",
       "...                      ...      ...                  ...             ...   \n",
       "2414877                    6       99               Mexico           Other   \n",
       "2414878                    6       99               Mexico           Other   \n",
       "2414879                    6       99               Mexico           Other   \n",
       "2414880                    6       99               Mexico           Other   \n",
       "2414881                    6       99               Mexico           Other   \n",
       "\n",
       "         UCI  \n",
       "0         97  \n",
       "1         97  \n",
       "2         97  \n",
       "3         97  \n",
       "4          2  \n",
       "...      ...  \n",
       "2414877   97  \n",
       "2414878   97  \n",
       "2414879   97  \n",
       "2414880   97  \n",
       "2414881   97  \n",
       "\n",
       "[2414882 rows x 38 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"Trans_COVID19_MEXICO.csv\", delimiter = ',') # Read the new datasheet, wholly coded with UTF-8\n",
    "df  # Browse the generated dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can observe, the majority of variables were encoded with numbers, in order in improve the visualization we then transform them to the original value and translate them in English\n",
    "(see **\"dictionary_data_COVID19\"** for further information)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOT USMER    1571207\n",
      "USMER         843675\n",
      "Name: ORIGIN, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Reformatting the variable Origin:\n",
    "Dic_Origin = {1:'USMER', 2:'NOT USMER', 99:float('nan')} # The value 99 means Unspecified, which we will consider as NaN value\n",
    "df['ORIGIN'] = df['ORIGIN'].map(Dic_Origin)\n",
    "print(df.ORIGIN.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SSA               1479681\n",
      "IMSS               650031\n",
      "ISSSTE              95203\n",
      "PRIVATE             85097\n",
      "STATE               37554\n",
      "PEMEX               19899\n",
      "SEDENA              16456\n",
      "IMSS-BIENESTAR      15752\n",
      "SEMAR                9869\n",
      "UNIVERSITARY         1938\n",
      "MUNICIPAL            1876\n",
      "DIF                  1172\n",
      "RED CROSS             344\n",
      "Name: SECTOR, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Reformatting the variable Sector. Note: Some of them are agency from Mexico and are writtin in abbreviation\n",
    "Dic_Sector = {1:'RED CROSS',  2:'DIF', 3:'STATE', 4:'IMSS', 5:'IMSS-BIENESTAR', 6:'ISSSTE', 7:'MUNICIPAL', \n",
    "              8:'PEMEX', 9:'PRIVATE', 10:'SEDENA', 11:'SEMAR', 12:'SSA', 13:'UNIVERSITARY', 99:float('nan')} \n",
    "\n",
    "df['SECTOR'] = df['SECTOR'].map(Dic_Sector)\n",
    "print(df.SECTOR.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MEXICO CITY             605727\n",
      "STATE OF MEXICO         199076\n",
      "NUEVO LEÓN              130863\n",
      "GUANAJUATO              130382\n",
      "JALISCO                  96544\n",
      "PUEBLA                   91829\n",
      "COAHUILA DE ZARAGOZA     88162\n",
      "TABASCO                  84673\n",
      "TAMAULIPAS               82101\n",
      "SAN LUIS POTOSÍ          71649\n",
      "SONORA                   69858\n",
      "VERACRUZ                 67423\n",
      "BAJA CALIFORNIA          62874\n",
      "MICHOACÁN DE OCAMPO      62712\n",
      "YUCATÁN                  50143\n",
      "SINALOA                  48738\n",
      "CHIHUAHUA                43934\n",
      "GUERRERO                 39996\n",
      "CHIAPAS                  39816\n",
      "DURANGO                  38504\n",
      "AGUASCALIENTES           36171\n",
      "BAJA CALIFORNIA SUR      34861\n",
      "OAXACA                   33434\n",
      "QUERÉTARO                30726\n",
      "HIDALGO                  29787\n",
      "QUINTANA ROO             27694\n",
      "ZACATECAS                25200\n",
      "TLAXCALA                 23424\n",
      "CAMPECHE                 19891\n",
      "MORELOS                  18674\n",
      "NAYARIT                  16511\n",
      "COLIMA                   13505\n",
      "Name: ENTITY_UM, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Reformatting the variables related to Entity area.\n",
    "Dic_Entity = {1:'AGUASCALIENTES',2:'BAJA CALIFORNIA',3:'BAJA CALIFORNIA SUR',4:'CAMPECHE',5:'COAHUILA DE ZARAGOZA',\n",
    "              6:'COLIMA',7:'CHIAPAS',8:'CHIHUAHUA',9:'MEXICO CITY',10:'DURANGO',11:'GUANAJUATO',12:'GUERRERO',\n",
    "              13:'HIDALGO',14:'JALISCO',15:'STATE OF MEXICO',16:'MICHOACÁN DE OCAMPO',17:'MORELOS',18:'NAYARIT',19:'NUEVO LEÓN',\n",
    "              20:'OAXACA',21:'PUEBLA',22:'QUERÉTARO',23:'QUINTANA ROO',24:'SAN LUIS POTOSÍ',25:'SINALOA',26:'SONORA',\n",
    "              27:'TABASCO',28:'TAMAULIPAS',29:'TLAXCALA',30:'VERACRUZ',31:'YUCATÁN',32:'ZACATECAS',\n",
    "              36:'UNITED MEXICAN STATES',97:'NOT APLIED',98:'IGNORED',99:float('nan')}\n",
    "\n",
    "df['ENTITY_UM'] = df['ENTITY_UM'].map(Dic_Entity)\n",
    "df['ENTITY_RES'] = df['ENTITY_RES'].map(Dic_Entity)\n",
    "df['ENTITY_NAT'] = df['ENTITY_NAT'].map(Dic_Entity)\n",
    "print(df.ENTITY_UM.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Female    1238985\n",
      "Male      1175897\n",
      "Name: SEX, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "Dic_Sex = {1:'Female', 2:'Male', 99:float('nan')}\n",
    "df['SEX'] = df['SEX'].map(Dic_Sex)\n",
    "print(df.SEX.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOT HOSPITALIZED    2037318\n",
      "HOSPITALIZED         377564\n",
      "Name: PATIENT_TYPE, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "Dic_Patient_Type = {1:'NOT HOSPITALIZED', 2:'HOSPITALIZED', 99:float('nan')}\n",
    "df['PATIENT_TYPE'] = df['PATIENT_TYPE'].map(Dic_Patient_Type)\n",
    "print(df.PATIENT_TYPE.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Reformate the variables which were coded in binary form\n",
    "# Dic_Yes_No = {1:'Yes', 2:'No',  97:'NOT APLIED',98:'IGNORED',99:float('nan')}\n",
    "Dic_Yes_No = {1:'Yes', 2:'No',  97:'NOT APLIED',98:float('nan'),99:float('nan')} # Ignored is considered as missing data\n",
    "Dic_Pregnant = {1:'Yes', 2:'No',  97:float('nan'),98:float('nan'),99:float('nan')}\n",
    "df['INTUBATED'] = df['INTUBATED'].map(Dic_Yes_No) \n",
    "df['PNEUMONIA'] = df['PNEUMONIA'].map(Dic_Yes_No)\n",
    "df['PREGNANT'] = df['PREGNANT'].map(Dic_Pregnant)\n",
    "df['SPEAK_INDIGENOUS_LANGUAGE'] = df['SPEAK_INDIGENOUS_LANGUAGE'].map(Dic_Yes_No)\n",
    "df['DIABETES'] = df['DIABETES'].map(Dic_Yes_No)\n",
    "df['COPD'] = df['COPD'].map(Dic_Yes_No)\n",
    "df['ASTHMA'] = df['ASTHMA'].map(Dic_Yes_No)\n",
    "df['INMUSUPR'] = df['INMUSUPR'].map(Dic_Yes_No)\n",
    "df['HYPERTENSION'] = df['HYPERTENSION'].map(Dic_Yes_No)\n",
    "df['OTHER_DISEASE'] = df['OTHER_DISEASE'].map(Dic_Yes_No)\n",
    "df['CARDIOVASCULAR'] = df['CARDIOVASCULAR'].map(Dic_Yes_No)\n",
    "df['OBESITY'] = df['OBESITY'].map(Dic_Yes_No)\n",
    "df['CHRONIC_KIDNEY_DISEASE'] = df['CHRONIC_KIDNEY_DISEASE'].map(Dic_Yes_No)\n",
    "df['SMOKE'] = df['SMOKE'].map(Dic_Yes_No)\n",
    "df['OTHER_CASE_CONTACT'] = df['OTHER_CASE_CONTACT'].map(Dic_Yes_No)\n",
    "df['MIGRANT'] = df['MIGRANT'].map(Dic_Yes_No)\n",
    "df['UCI'] = df['UCI'].map(Dic_Yes_No)\n",
    "df['INDIGENOUS'] = df['INDIGENOUS'].map(Dic_Yes_No)\n",
    "df['TESTED'] = df['TESTED'].map(Dic_Yes_No)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mexican      2403418\n",
      "Foreigner      11464\n",
      "Name: NATIONALITY, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "Dic_Nationality = {1:'Mexican', 2:'Foreigner', 99:float('nan')}\n",
    "df['NATIONALITY'] = df['NATIONALITY'].map(Dic_Nationality)\n",
    "print(df.NATIONALITY.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non-Positive SARS-CoV-2    1132159\n",
      "Positive SARS-CoV-2         905579\n",
      "Inadequate Result           112780\n",
      "pending result               56331\n",
      "Name: RESULT_LAB, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "Dic_Result = {1:'Positive SARS-CoV-2', 2:'Non-Positive SARS-CoV-2', 3:'pending result', 4:'Inadequate Result',5:'Not applied'}\n",
    "df['RESULT_LAB'] = df['RESULT_LAB'].map(Dic_Result)\n",
    "print(df.RESULT_LAB.value_counts()) # A brief information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Negative tested by laboratory                        1132159\n",
       "Confirmed by laboratory test                          905579\n",
       "Suspected case                                        300003\n",
       "Not tested by laboratory                               43000\n",
       "Confirmed by epidemiological clinical association      26886\n",
       "Invalid by laboratory                                   6565\n",
       "Confirmed by ruling committee                            690\n",
       "Name: FINAL_CLASIFICATION, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Dic_Clasif = {1:'Confirmed by epidemiological clinical association', 2:'Confirmed by ruling committee',\n",
    "             3:'Confirmed by laboratory test', 4:'Invalid by laboratory', 5:'Not tested by laboratory',\n",
    "             6:'Suspected case', 7:'Negative tested by laboratory'}\n",
    "df['FINAL_CLASIFICATION'] = df['FINAL_CLASIFICATION'].map(Dic_Clasif)\n",
    "df['FINAL_CLASIFICATION'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DEATH_DATE'] = pd.to_datetime(df['DEATH_DATE'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**You could preserve the original file by following codes:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('CovMexico_lastestdata.csv', sep=',', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data preparation for \"Clustering Analysis\" \n",
    "### In this section, we establish some extra columns by reformatting existent variables which are essential in order for clustering analysis.  \n",
    "\n",
    "### Preprocessing of comorbidities and outcomes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non-Deceased    2283433\n",
      "Deceased         131449\n",
      "Name: OUTCOME, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv(\"CovMexico_lastestdata.csv\", delimiter = ',') # Read the datasheet which we formatted before\n",
    "df = df[(df['RESULT_LAB']=='Positive SARS-CoV-2')|(df['RESULT_LAB']=='Non-Positive SARS-CoV-2')] # Only positive and negative patients\n",
    "\n",
    "def Outcome(date):\n",
    "    # As there is only date of Death, we cannot deduce whether the patient is recovered or still in the hospital\n",
    "    if pd.isna(date): # If there is not decease date about the patient, we consider them as non-deceased individual\n",
    "        return \"Non-Deceased\" \n",
    "    return \"Deceased\"\n",
    "\n",
    "# df = df[df[\"RESULT\"] == \"Positive SAR-CoV-2\"]  # Obtanin only the patients who were tested as positive with SAR-CoV-2\n",
    "df[\"OUTCOME\"] = df[\"DEATH_DATE\"].map(Outcome)\n",
    "print(df.OUTCOME.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the required variables, for clustering analysis, into **Binary** format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "Dic_Yes_No = {'Yes':str(1), 'No':str(0),'NOT APLIED':str(0)}\n",
    "Dic_Yes_No2 = {'Yes':str(1), 'No':str(0)}\n",
    "\n",
    "# We reformate these variables into Binary formz in order to use clustering.\n",
    "df['INTUBATED'] = df['INTUBATED'].map(Dic_Yes_No) \n",
    "df['PNEUMONIA'] = df['PNEUMONIA'].map(Dic_Yes_No)\n",
    "\n",
    "df['SPEAK_INDIGENOUS_LANGUAGE'] = df['SPEAK_INDIGENOUS_LANGUAGE'].map(Dic_Yes_No)\n",
    "df['DIABETES'] = df['DIABETES'].map(Dic_Yes_No)\n",
    "df['COPD'] = df['COPD'].map(Dic_Yes_No)\n",
    "df['ASTHMA'] = df['ASTHMA'].map(Dic_Yes_No)\n",
    "df['INMUSUPR'] = df['INMUSUPR'].map(Dic_Yes_No)\n",
    "df['HYPERTENSION'] = df['HYPERTENSION'].map(Dic_Yes_No)\n",
    "df['OTHER_DISEASE'] = df['OTHER_DISEASE'].map(Dic_Yes_No)\n",
    "df['CARDIOVASCULAR'] = df['CARDIOVASCULAR'].map(Dic_Yes_No)\n",
    "df['OBESITY'] = df['OBESITY'].map(Dic_Yes_No)\n",
    "df['CHRONIC_KIDNEY_DISEASE'] = df['CHRONIC_KIDNEY_DISEASE'].map(Dic_Yes_No)\n",
    "df['SMOKE'] = df['SMOKE'].map(Dic_Yes_No)\n",
    "df['UCI'] = df['UCI'].map(Dic_Yes_No)\n",
    "\n",
    "df['OTHER_CASE_CONTACT'] = df['OTHER_CASE_CONTACT'].map(Dic_Yes_No2)\n",
    "df['OTHER_CASE_CONTACT'].fillna('0', inplace=True)\n",
    "df['INDIGENOUS'] = df['INDIGENOUS'].map(Dic_Yes_No)\n",
    "df['TESTED'] = df['TESTED'].map(Dic_Yes_No)\n",
    "df['PREGNANT'] = df['PREGNANT'].map(Dic_Yes_No2)\n",
    "df['INDIGENOUS'] = df['INDIGENOUS'].map(Dic_Yes_No)\n",
    "\n",
    "\n",
    "df['PREGNANT'].fillna('0', inplace=True)\n",
    "df['UCI'].fillna('0', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Obtain the patients of the first wave in Mexico who presented at least one month the symptoms\n",
    "from datetime import date, timedelta\n",
    "import time\n",
    "\n",
    "df[\"SYMPTOMS_DATE\"] = pd.to_datetime(df[\"SYMPTOMS_DATE\"])\n",
    "df = df[(df['SYMPTOMS_DATE'] < '2020-10-1')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove some missing data related to the following variables to prevent analysis from disturbance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(subset = ['INTUBATED','PNEUMONIA','DIABETES','COPD', 'ASTHMA','INMUSUPR',\n",
    "       'HYPERTENSION','OTHER_DISEASE','CARDIOVASCULAR','OBESITY','CHRONIC_KIDNEY_DISEASE','SMOKE'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new variable **Survival days** is calculated by the **date of death** minus the **date of Symptoms**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1717439"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"DEATH_DATE\"] = pd.to_datetime(df[\"DEATH_DATE\"])\n",
    "df[\"Survival_days\"] = df[\"DEATH_DATE\"] - df[\"SYMPTOMS_DATE\"]\n",
    "\n",
    "# Convert date into days number\n",
    "df[\"Survival_days\"] = df.Survival_days.apply(lambda x: x.days)\n",
    "df[df['Survival_days']<0]\n",
    "df = df[(df['Survival_days']>0)|(df['Survival_days'].isnull())]\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new variable **FromSymptomToHospital days** is calculated by the **date of Admission**  minus the **date of Symptoms** (only for the patients who were hospitalized)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ADMISSION_DATE'] = pd.to_datetime(df['ADMISSION_DATE'])\n",
    "df[\"FromSymptomToHospital_days\"] = df['ADMISSION_DATE'] - df[\"SYMPTOMS_DATE\"]\n",
    "\n",
    "# Convert date into days number\n",
    "df[\"FromSymptomToHospital_days\"] = df[df['PATIENT_TYPE'] == 'HOSPITALIZED'].FromSymptomToHospital_days.apply(lambda x: x.days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['Unnamed: 0'] # Remove duplicated index columns\n",
    "df1 = df[df['RESULT_LAB']=='Positive SARS-CoV-2']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save the dataset into csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('COVID19_MEXICO_LATEST_DATA_POPULATION.csv', sep=',', encoding='utf-8') # Both valid positive and Negative patients.\n",
    "df1.to_csv('COVID19_MEXICO_LATEST_DATA.csv', sep=',', encoding='utf-8')           # Only valid positive patients"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}