1_data_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "from sklearn.ensemble import RandomForestRegressor, RandomForestClassifier\n",
    "import gc\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'included_cohort.csv.gz'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[3], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m data \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_csv\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mincluded_cohort.csv.gz\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/envs/python3.10/lib/python3.10/site-packages/pandas/io/parsers/readers.py:1026\u001b[0m, in \u001b[0;36mread_csv\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)\u001b[0m\n\u001b[1;32m   1013\u001b[0m kwds_defaults \u001b[38;5;241m=\u001b[39m _refine_defaults_read(\n\u001b[1;32m   1014\u001b[0m     dialect,\n\u001b[1;32m   1015\u001b[0m     delimiter,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1022\u001b[0m     dtype_backend\u001b[38;5;241m=\u001b[39mdtype_backend,\n\u001b[1;32m   1023\u001b[0m )\n\u001b[1;32m   1024\u001b[0m kwds\u001b[38;5;241m.\u001b[39mupdate(kwds_defaults)\n\u001b[0;32m-> 1026\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43m_read\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfilepath_or_buffer\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mkwds\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/envs/python3.10/lib/python3.10/site-packages/pandas/io/parsers/readers.py:620\u001b[0m, in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    617\u001b[0m _validate_names(kwds\u001b[38;5;241m.\u001b[39mget(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnames\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28;01mNone\u001b[39;00m))\n\u001b[1;32m    619\u001b[0m \u001b[38;5;66;03m# Create the parser.\u001b[39;00m\n\u001b[0;32m--> 620\u001b[0m parser \u001b[38;5;241m=\u001b[39m \u001b[43mTextFileReader\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfilepath_or_buffer\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwds\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    622\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m chunksize \u001b[38;5;129;01mor\u001b[39;00m iterator:\n\u001b[1;32m    623\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m parser\n",
      "File \u001b[0;32m~/miniconda3/envs/python3.10/lib/python3.10/site-packages/pandas/io/parsers/readers.py:1620\u001b[0m, in \u001b[0;36mTextFileReader.__init__\u001b[0;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[1;32m   1617\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39moptions[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mhas_index_names\u001b[39m\u001b[38;5;124m\"\u001b[39m] \u001b[38;5;241m=\u001b[39m kwds[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mhas_index_names\u001b[39m\u001b[38;5;124m\"\u001b[39m]\n\u001b[1;32m   1619\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mhandles: IOHandles \u001b[38;5;241m|\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[0;32m-> 1620\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_make_engine\u001b[49m\u001b[43m(\u001b[49m\u001b[43mf\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mengine\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/miniconda3/envs/python3.10/lib/python3.10/site-packages/pandas/io/parsers/readers.py:1880\u001b[0m, in \u001b[0;36mTextFileReader._make_engine\u001b[0;34m(self, f, engine)\u001b[0m\n\u001b[1;32m   1878\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mb\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;129;01min\u001b[39;00m mode:\n\u001b[1;32m   1879\u001b[0m         mode \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mb\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m-> 1880\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mhandles \u001b[38;5;241m=\u001b[39m \u001b[43mget_handle\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1881\u001b[0m \u001b[43m    \u001b[49m\u001b[43mf\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1882\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmode\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1883\u001b[0m \u001b[43m    \u001b[49m\u001b[43mencoding\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moptions\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mencoding\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mNone\u001b[39;49;00m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1884\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcompression\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moptions\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mcompression\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mNone\u001b[39;49;00m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1885\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmemory_map\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moptions\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mmemory_map\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1886\u001b[0m \u001b[43m    \u001b[49m\u001b[43mis_text\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mis_text\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1887\u001b[0m \u001b[43m    \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moptions\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mencoding_errors\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mstrict\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1888\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moptions\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mstorage_options\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mNone\u001b[39;49;00m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1889\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1890\u001b[0m \u001b[38;5;28;01massert\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mhandles \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m   1891\u001b[0m f \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mhandles\u001b[38;5;241m.\u001b[39mhandle\n",
      "File \u001b[0;32m~/miniconda3/envs/python3.10/lib/python3.10/site-packages/pandas/io/common.py:765\u001b[0m, in \u001b[0;36mget_handle\u001b[0;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[1;32m    761\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m compression \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mgzip\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[1;32m    762\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(handle, \u001b[38;5;28mstr\u001b[39m):\n\u001b[1;32m    763\u001b[0m         \u001b[38;5;66;03m# error: Incompatible types in assignment (expression has type\u001b[39;00m\n\u001b[1;32m    764\u001b[0m         \u001b[38;5;66;03m# \"GzipFile\", variable has type \"Union[str, BaseBuffer]\")\u001b[39;00m\n\u001b[0;32m--> 765\u001b[0m         handle \u001b[38;5;241m=\u001b[39m \u001b[43mgzip\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mGzipFile\u001b[49m\u001b[43m(\u001b[49m\u001b[43m  \u001b[49m\u001b[38;5;66;43;03m# type: ignore[assignment]\u001b[39;49;00m\n\u001b[1;32m    766\u001b[0m \u001b[43m            \u001b[49m\u001b[43mfilename\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mhandle\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    767\u001b[0m \u001b[43m            \u001b[49m\u001b[43mmode\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mioargs\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mmode\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    768\u001b[0m \u001b[43m            \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mcompression_args\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    769\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    770\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m    771\u001b[0m         handle \u001b[38;5;241m=\u001b[39m gzip\u001b[38;5;241m.\u001b[39mGzipFile(\n\u001b[1;32m    772\u001b[0m             \u001b[38;5;66;03m# No overload variant of \"GzipFile\" matches argument types\u001b[39;00m\n\u001b[1;32m    773\u001b[0m             \u001b[38;5;66;03m# \"Union[str, BaseBuffer]\", \"str\", \"Dict[str, Any]\"\u001b[39;00m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    776\u001b[0m             \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mcompression_args,\n\u001b[1;32m    777\u001b[0m         )\n",
      "File \u001b[0;32m~/miniconda3/envs/python3.10/lib/python3.10/gzip.py:174\u001b[0m, in \u001b[0;36mGzipFile.__init__\u001b[0;34m(self, filename, mode, compresslevel, fileobj, mtime)\u001b[0m\n\u001b[1;32m    172\u001b[0m     mode \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mb\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[1;32m    173\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m fileobj \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m--> 174\u001b[0m     fileobj \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmyfileobj \u001b[38;5;241m=\u001b[39m \u001b[43mbuiltins\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mopen\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfilename\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmode\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;129;43;01mor\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mrb\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m    175\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m filename \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m    176\u001b[0m     filename \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mgetattr\u001b[39m(fileobj, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mname\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'included_cohort.csv.gz'"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv('included_cohort.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7499081"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7499081, 84)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['NIA', 'sexe', 'data_naixement', 'abs_c', 'abs', 'pais_c',\n",
       "       'N_vaccine_total', 'VACUNA_1_DATA', 'VACUNA_1_MOTIU', 'VACUNA_2_DATA',\n",
       "       'VACUNA_2_MOTIU', 'VACUNA_3_DATA', 'VACUNA_3_MOTIU', 'VACUNA_1_DATA_pp',\n",
       "       'VACUNA_2_DATA_pp', 'VACUNA_3_DATA_pp', 'DATA_DM_min', 'DM',\n",
       "       'covid_bef_vax', 'test_date', 'test_res', 'test_date_covid_2',\n",
       "       'test_res_covid_2', 'test_date_covid_3', 'test_res_covid_3',\n",
       "       'test_date_imc_1', 'test_res_imc_1', 'test_date_imc_2',\n",
       "       'test_res_imc_2', 'test_date_imc_3', 'test_res_imc_3', 'test_date_sp_1',\n",
       "       'test_res_sp_1', 'test_date_sp_2', 'test_res_sp_2', 'test_date_sp_3',\n",
       "       'test_res_sp_3', 'test_date_dp_1', 'test_res_dp_1', 'test_date_dp_2',\n",
       "       'test_res_dp_2', 'test_date_dp_3', 'test_res_dp_3', 'test_date_abdo_1',\n",
       "       'test_res_abdo_1', 'test_date_abdo_2', 'test_res_abdo_2',\n",
       "       'test_date_abdo_3', 'test_res_abdo_3', 'test_date_bg_1',\n",
       "       'test_res_bg_1', 'test_date_bg_2', 'test_res_bg_2', 'test_date_bg_3',\n",
       "       'test_res_bg_3', 'test_date_chol_1', 'test_res_chol_1',\n",
       "       'test_date_chol_2', 'test_res_chol_2', 'test_date_chol_3',\n",
       "       'test_res_chol_3', 'test_date_smoking_1', 'test_res_smoking_1',\n",
       "       'test_date_smoking_2', 'test_res_smoking_2', 'test_date_smoking_3',\n",
       "       'test_res_smoking_3', 'test_date_gma_1', 'test_res_gma_1',\n",
       "       'test_date_gma_2', 'test_res_gma_2', 'test_date_gma_3',\n",
       "       'test_res_gma_3', 'test_date_socio_stat_1', 'test_res_socio_stat_1',\n",
       "       'test_date_socio_stat_2', 'test_res_socio_stat_2',\n",
       "       'test_date_socio_stat_3', 'test_res_socio_stat_3', 'age_1', 'age_2',\n",
       "       'age_3', 'idabs', 'ISC reescalat'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.columns = ['NIA', 'sexe', 'data_naixement', 'abs_c', 'abs', 'pais_c',\n",
    "       'N_vaccine_total', 'VACUNA_1_DATA', 'VACUNA_1_MOTIU', 'VACUNA_2_DATA',\n",
    "       'VACUNA_2_MOTIU', 'VACUNA_3_DATA', 'VACUNA_3_MOTIU', 'VACUNA_1_DATA_pp',\n",
    "       'VACUNA_2_DATA_pp', 'VACUNA_3_DATA_pp', 'DATA_DM_min', 'DM',\n",
    "       'covid_bef_vax', 'test_date_covid_1', 'test_res_covid_1', 'test_date_covid_2',\n",
    "       'test_res_covid_2', 'test_date_covid_3', 'test_res_covid_3',\n",
    "       'test_date_imc_1', 'test_res_imc_1', 'test_date_imc_2',\n",
    "       'test_res_imc_2', 'test_date_imc_3', 'test_res_imc_3', 'test_date_sp_1',\n",
    "       'test_res_sp_1', 'test_date_sp_2', 'test_res_sp_2', 'test_date_sp_3',\n",
    "       'test_res_sp_3', 'test_date_dp_1', 'test_res_dp_1', 'test_date_dp_2',\n",
    "       'test_res_dp_2', 'test_date_dp_3', 'test_res_dp_3', 'test_date_abdo_1',\n",
    "       'test_res_abdo_1', 'test_date_abdo_2', 'test_res_abdo_2',\n",
    "       'test_date_abdo_3', 'test_res_abdo_3', 'test_date_bg_1',\n",
    "       'test_res_bg_1', 'test_date_bg_2', 'test_res_bg_2', 'test_date_bg_3',\n",
    "       'test_res_bg_3', 'test_date_chol_1', 'test_res_chol_1',\n",
    "       'test_date_chol_2', 'test_res_chol_2', 'test_date_chol_3',\n",
    "       'test_res_chol_3', 'test_date_smoking_1', 'test_res_smoking_1',\n",
    "       'test_date_smoking_2', 'test_res_smoking_2', 'test_date_smoking_3',\n",
    "       'test_res_smoking_3', 'test_date_gma_1', 'test_res_gma_1',\n",
    "       'test_date_gma_2', 'test_res_gma_2', 'test_date_gma_3',\n",
    "       'test_res_gma_3', 'test_date_sociostat_1', 'test_res_sociostat_1',\n",
    "       'test_date_sociostat_2', 'test_res_sociostat_2',\n",
    "       'test_date_sociostat_3', 'test_res_sociostat_3', 'age_1', 'age_2',\n",
    "       'age_3', 'idabs', 'ISC reescalat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create useful lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_covars = ['test_date_covid_1', 'test_res_covid_1', 'test_date_covid_2',\n",
    "'test_res_covid_2', 'test_date_covid_3', 'test_res_covid_3',\n",
    "'test_date_imc_1', 'test_res_imc_1', 'test_date_imc_2',\n",
    "'test_res_imc_2', 'test_date_imc_3', 'test_res_imc_3', 'test_date_sp_1',\n",
    "'test_res_sp_1', 'test_date_sp_2', 'test_res_sp_2', 'test_date_sp_3',\n",
    "'test_res_sp_3', 'test_date_dp_1', 'test_res_dp_1', 'test_date_dp_2',\n",
    "'test_res_dp_2', 'test_date_dp_3', 'test_res_dp_3', 'test_date_abdo_1',\n",
    "'test_res_abdo_1', 'test_date_abdo_2', 'test_res_abdo_2',\n",
    "'test_date_abdo_3', 'test_res_abdo_3', 'test_date_bg_1',\n",
    "'test_res_bg_1', 'test_date_bg_2', 'test_res_bg_2', 'test_date_bg_3',\n",
    "'test_res_bg_3', 'test_date_chol_1', 'test_res_chol_1',\n",
    "'test_date_chol_2', 'test_res_chol_2', 'test_date_chol_3',\n",
    "'test_res_chol_3', 'test_date_smoking_1', 'test_res_smoking_1',\n",
    "'test_date_smoking_2', 'test_res_smoking_2', 'test_date_smoking_3',\n",
    "'test_res_smoking_3', 'test_date_gma_1', 'test_res_gma_1',\n",
    "'test_date_gma_2', 'test_res_gma_2', 'test_date_gma_3',\n",
    "'test_res_gma_3', 'test_date_sociostat_1', 'test_res_sociostat_1',\n",
    "'test_date_sociostat_2', 'test_res_sociostat_2',\n",
    "'test_date_sociostat_3', 'test_res_sociostat_3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "tests = list(set([x.split('_')[2] for x in time_covars]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preprocess"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.test_res_covid_1 = data.test_res_covid_1.map({'Positiu':'Positiu', 'Negatiu': 'Negatiu', 'Probable': 'Positiu', 'No valorat': 'Negatiu'})\n",
    "data.test_res_covid_1 = data.test_res_covid_1.map({'Positiu': int(1), 'Negatiu': int(0)})\n",
    "#data.test_res_covid_1 = data.test_res_covid_1.astype(int)\n",
    "\n",
    "data.test_res_covid_2 = data.test_res_covid_2.map({'Positiu':'Positiu', 'Negatiu': 'Negatiu', 'Probable': 'Positiu', 'No valorat': 'Negatiu'})\n",
    "data.test_res_covid_2 = data.test_res_covid_2.map({'Positiu': int(1), 'Negatiu': int(0)})\n",
    "\n",
    "data.test_res_covid_3 = data.test_res_covid_3.map({'Positiu':'Positiu', 'Negatiu': 'Negatiu', 'Probable': 'Positiu', 'No valorat': 'Negatiu'})\n",
    "data.test_res_covid_3 = data.test_res_covid_3.map({'Positiu': int(1), 'Negatiu': int(0)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Vacuna_1'] = np.zeros(len(data))\n",
    "data['Vacuna_2'] = np.zeros(len(data))\n",
    "data['Vacuna_3'] = np.zeros(len(data))\n",
    "\n",
    "data.loc[~data.VACUNA_1_DATA.isna(), 'Vacuna_1'] = int(1)\n",
    "data.loc[~data.VACUNA_2_DATA.isna(), 'Vacuna_2'] = int(1)\n",
    "data.loc[~data.VACUNA_3_DATA.isna(), 'Vacuna_3'] = int(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Correct treatment \"line\" for those with DM date before last vax date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>NIA</th>\n",
       "      <th>sexe</th>\n",
       "      <th>data_naixement</th>\n",
       "      <th>abs_c</th>\n",
       "      <th>abs</th>\n",
       "      <th>pais_c</th>\n",
       "      <th>N_vaccine_total</th>\n",
       "      <th>VACUNA_1_DATA</th>\n",
       "      <th>VACUNA_1_MOTIU</th>\n",
       "      <th>...</th>\n",
       "      <th>test_date_sociostat_3</th>\n",
       "      <th>test_res_sociostat_3</th>\n",
       "      <th>age_1</th>\n",
       "      <th>age_2</th>\n",
       "      <th>age_3</th>\n",
       "      <th>idabs</th>\n",
       "      <th>ISC reescalat</th>\n",
       "      <th>Vacuna_1</th>\n",
       "      <th>Vacuna_2</th>\n",
       "      <th>Vacuna_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 88 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Unnamed: 0, NIA, sexe, data_naixement, abs_c, abs, pais_c, N_vaccine_total, VACUNA_1_DATA, VACUNA_1_MOTIU, VACUNA_2_DATA, VACUNA_2_MOTIU, VACUNA_3_DATA, VACUNA_3_MOTIU, VACUNA_1_DATA_pp, VACUNA_2_DATA_pp, VACUNA_3_DATA_pp, DATA_DM_min, DM, covid_bef_vax, test_date_covid_1, test_res_covid_1, test_date_covid_2, test_res_covid_2, test_date_covid_3, test_res_covid_3, test_date_imc_1, test_res_imc_1, test_date_imc_2, test_res_imc_2, test_date_imc_3, test_res_imc_3, test_date_sp_1, test_res_sp_1, test_date_sp_2, test_res_sp_2, test_date_sp_3, test_res_sp_3, test_date_dp_1, test_res_dp_1, test_date_dp_2, test_res_dp_2, test_date_dp_3, test_res_dp_3, test_date_abdo_1, test_res_abdo_1, test_date_abdo_2, test_res_abdo_2, test_date_abdo_3, test_res_abdo_3, test_date_bg_1, test_res_bg_1, test_date_bg_2, test_res_bg_2, test_date_bg_3, test_res_bg_3, test_date_chol_1, test_res_chol_1, test_date_chol_2, test_res_chol_2, test_date_chol_3, test_res_chol_3, test_date_smoking_1, test_res_smoking_1, test_date_smoking_2, test_res_smoking_2, test_date_smoking_3, test_res_smoking_3, test_date_gma_1, test_res_gma_1, test_date_gma_2, test_res_gma_2, test_date_gma_3, test_res_gma_3, test_date_sociostat_1, test_res_sociostat_1, test_date_sociostat_2, test_res_sociostat_2, test_date_sociostat_3, test_res_sociostat_3, age_1, age_2, age_3, idabs, ISC reescalat, Vacuna_1, Vacuna_2, Vacuna_3]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 88 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1 Vax\n",
    "data[(data.N_vaccine_total==1) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_1_DATA)] # None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 2 Vaxxes\n",
    "data[(data.N_vaccine_total==2) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_2_DATA)] # These minus those in the cell below must go to Vax 1 line\n",
    "nias_to_vax1_from2 = data[(data.N_vaccine_total==2) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_2_DATA)]['NIA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6929523    69424739\n",
       "6933384    82429332\n",
       "6934700      896111\n",
       "6935245     9909676\n",
       "6935768    10951066\n",
       "             ...   \n",
       "7100832     5336221\n",
       "7106193     6441334\n",
       "7106673     5165158\n",
       "7110373     1372422\n",
       "7113477     6639167\n",
       "Name: NIA, Length: 1404, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nias_to_vax1_from2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>NIA</th>\n",
       "      <th>sexe</th>\n",
       "      <th>data_naixement</th>\n",
       "      <th>abs_c</th>\n",
       "      <th>abs</th>\n",
       "      <th>pais_c</th>\n",
       "      <th>N_vaccine_total</th>\n",
       "      <th>VACUNA_1_DATA</th>\n",
       "      <th>VACUNA_1_MOTIU</th>\n",
       "      <th>...</th>\n",
       "      <th>test_date_sociostat_3</th>\n",
       "      <th>test_res_sociostat_3</th>\n",
       "      <th>age_1</th>\n",
       "      <th>age_2</th>\n",
       "      <th>age_3</th>\n",
       "      <th>idabs</th>\n",
       "      <th>ISC reescalat</th>\n",
       "      <th>Vacuna_1</th>\n",
       "      <th>Vacuna_2</th>\n",
       "      <th>Vacuna_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 88 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Unnamed: 0, NIA, sexe, data_naixement, abs_c, abs, pais_c, N_vaccine_total, VACUNA_1_DATA, VACUNA_1_MOTIU, VACUNA_2_DATA, VACUNA_2_MOTIU, VACUNA_3_DATA, VACUNA_3_MOTIU, VACUNA_1_DATA_pp, VACUNA_2_DATA_pp, VACUNA_3_DATA_pp, DATA_DM_min, DM, covid_bef_vax, test_date_covid_1, test_res_covid_1, test_date_covid_2, test_res_covid_2, test_date_covid_3, test_res_covid_3, test_date_imc_1, test_res_imc_1, test_date_imc_2, test_res_imc_2, test_date_imc_3, test_res_imc_3, test_date_sp_1, test_res_sp_1, test_date_sp_2, test_res_sp_2, test_date_sp_3, test_res_sp_3, test_date_dp_1, test_res_dp_1, test_date_dp_2, test_res_dp_2, test_date_dp_3, test_res_dp_3, test_date_abdo_1, test_res_abdo_1, test_date_abdo_2, test_res_abdo_2, test_date_abdo_3, test_res_abdo_3, test_date_bg_1, test_res_bg_1, test_date_bg_2, test_res_bg_2, test_date_bg_3, test_res_bg_3, test_date_chol_1, test_res_chol_1, test_date_chol_2, test_res_chol_2, test_date_chol_3, test_res_chol_3, test_date_smoking_1, test_res_smoking_1, test_date_smoking_2, test_res_smoking_2, test_date_smoking_3, test_res_smoking_3, test_date_gma_1, test_res_gma_1, test_date_gma_2, test_res_gma_2, test_date_gma_3, test_res_gma_3, test_date_sociostat_1, test_res_sociostat_1, test_date_sociostat_2, test_res_sociostat_2, test_date_sociostat_3, test_res_sociostat_3, age_1, age_2, age_3, idabs, ISC reescalat, Vacuna_1, Vacuna_2, Vacuna_3]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 88 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[(data.N_vaccine_total==2) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_1_DATA)] # None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 3 Vaxxes\n",
    "data[(data.N_vaccine_total==3) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_3_DATA)] # These minus those in the cell below must go to Vax 2 line\n",
    "nias_to_vax2_from3 = data[(data.N_vaccine_total==3) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_3_DATA)]['NIA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6919983    47884737\n",
       "6922683      631144\n",
       "6923664    70024756\n",
       "6924723    77404741\n",
       "6924783    61944749\n",
       "             ...   \n",
       "7112094     1191051\n",
       "7112154     4271220\n",
       "7112214     8582393\n",
       "7112274     1132704\n",
       "7112334     2931911\n",
       "Name: NIA, Length: 15886, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nias_to_vax2_from3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data[(data.N_vaccine_total==3) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_2_DATA)] # These minus those in the cell below must go to Vax 1 line\n",
    "nias_to_vax1_from3 = data[(data.N_vaccine_total==3) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_2_DATA)]['NIA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6930483    6705118\n",
       "6931023    4136601\n",
       "6931083    1224999\n",
       "6931263    6632324\n",
       "6931923      81760\n",
       "            ...   \n",
       "7109034    4833069\n",
       "7110714    5231260\n",
       "7111137     358971\n",
       "7111197    6912578\n",
       "7111254    5626748\n",
       "Name: NIA, Length: 3173, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nias_to_vax1_from3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>NIA</th>\n",
       "      <th>sexe</th>\n",
       "      <th>data_naixement</th>\n",
       "      <th>abs_c</th>\n",
       "      <th>abs</th>\n",
       "      <th>pais_c</th>\n",
       "      <th>N_vaccine_total</th>\n",
       "      <th>VACUNA_1_DATA</th>\n",
       "      <th>VACUNA_1_MOTIU</th>\n",
       "      <th>...</th>\n",
       "      <th>test_date_sociostat_3</th>\n",
       "      <th>test_res_sociostat_3</th>\n",
       "      <th>age_1</th>\n",
       "      <th>age_2</th>\n",
       "      <th>age_3</th>\n",
       "      <th>idabs</th>\n",
       "      <th>ISC reescalat</th>\n",
       "      <th>Vacuna_1</th>\n",
       "      <th>Vacuna_2</th>\n",
       "      <th>Vacuna_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 88 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Unnamed: 0, NIA, sexe, data_naixement, abs_c, abs, pais_c, N_vaccine_total, VACUNA_1_DATA, VACUNA_1_MOTIU, VACUNA_2_DATA, VACUNA_2_MOTIU, VACUNA_3_DATA, VACUNA_3_MOTIU, VACUNA_1_DATA_pp, VACUNA_2_DATA_pp, VACUNA_3_DATA_pp, DATA_DM_min, DM, covid_bef_vax, test_date_covid_1, test_res_covid_1, test_date_covid_2, test_res_covid_2, test_date_covid_3, test_res_covid_3, test_date_imc_1, test_res_imc_1, test_date_imc_2, test_res_imc_2, test_date_imc_3, test_res_imc_3, test_date_sp_1, test_res_sp_1, test_date_sp_2, test_res_sp_2, test_date_sp_3, test_res_sp_3, test_date_dp_1, test_res_dp_1, test_date_dp_2, test_res_dp_2, test_date_dp_3, test_res_dp_3, test_date_abdo_1, test_res_abdo_1, test_date_abdo_2, test_res_abdo_2, test_date_abdo_3, test_res_abdo_3, test_date_bg_1, test_res_bg_1, test_date_bg_2, test_res_bg_2, test_date_bg_3, test_res_bg_3, test_date_chol_1, test_res_chol_1, test_date_chol_2, test_res_chol_2, test_date_chol_3, test_res_chol_3, test_date_smoking_1, test_res_smoking_1, test_date_smoking_2, test_res_smoking_2, test_date_smoking_3, test_res_smoking_3, test_date_gma_1, test_res_gma_1, test_date_gma_2, test_res_gma_2, test_date_gma_3, test_res_gma_3, test_date_sociostat_1, test_res_sociostat_1, test_date_sociostat_2, test_res_sociostat_2, test_date_sociostat_3, test_res_sociostat_3, age_1, age_2, age_3, idabs, ISC reescalat, Vacuna_1, Vacuna_2, Vacuna_3]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 88 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[(data.N_vaccine_total==3) & (data.DM==1) & (data.DATA_DM_min<data.VACUNA_1_DATA)] # None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Update lists\n",
    "nias_to_vax2_from3 = pd.Series(list(set(nias_to_vax2_from3) - set(nias_to_vax1_from3)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Merge\n",
    "nias_to_vax2 = nias_to_vax2_from3\n",
    "nias_to_vax1 = pd.Series(list(set(nias_to_vax1_from2) | set(nias_to_vax1_from3)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Correct the required information\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'N_vaccine_total'] = 1\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'Vacuna_2'] = int(0)\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'VACUNA_2_DATA'] = np.NaN\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'Vacuna_3'] = int(0)\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'VACUNA_3_DATA'] = np.NaN\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Correct the required information\n",
    "data.loc[data.NIA.isin(nias_to_vax2), 'N_vaccine_total'] = 2\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'Vacuna_3'] = int(0)\n",
    "data.loc[data.NIA.isin(nias_to_vax1), 'VACUNA_3_DATA'] = np.NaN\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_429964/2382593605.py:1: DtypeWarning: Columns (9,10,11,12,13,14,15,16,17,18) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  data = pd.read_csv('included_cohort_prep.csv')\n"
     ]
    }
   ],
   "source": [
    "#data = pd.read_csv('included_cohort_prep.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data.data_naixement = pd.to_datetime(data.data_naixement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data.data_naixement = data.data_naixement.dt.tz_convert(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "## RECOMMENDED VACCINE TIME for those WITH vaccine. Information taken from spanish health ministry\n",
    "recomm_date_1vax_above50 = pd.to_datetime('2021-02-01')\n",
    "recomm_date_2vax_above50 = pd.to_datetime('2021-03-01')\n",
    "recomm_date_3vax_above50 = pd.to_datetime('2021-04-01')\n",
    "\n",
    "recomm_date_1vax_below50 = pd.to_datetime('2021-06-01')\n",
    "recomm_date_2vax_below50 = pd.to_datetime('2021-07-01')\n",
    "recomm_date_3vax_below50 = pd.to_datetime('2021-08-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Set 'per protocol' vaccination dates for those without that info\n",
    "\n",
    "#  above 50\n",
    "data.loc[((data.VACUNA_1_DATA_pp.isna())&(((recomm_date_1vax_above50-data.data_naixement).values/pd.Timedelta('365 days'))>49.)), 'VACUNA_1_DATA_pp'] = pd.to_datetime(recomm_date_1vax_above50)\n",
    "\n",
    "data.loc[((data.VACUNA_2_DATA_pp.isna())&(((recomm_date_1vax_above50-data.data_naixement).values/pd.Timedelta('365 days'))>49.)), 'VACUNA_2_DATA_pp'] = pd.to_datetime(recomm_date_2vax_above50)\n",
    "\n",
    "data.loc[((data.VACUNA_3_DATA_pp.isna())&(((recomm_date_1vax_above50-data.data_naixement).values/pd.Timedelta('365 days'))>49.)), 'VACUNA_3_DATA_pp'] = pd.to_datetime(recomm_date_3vax_above50)\n",
    "\n",
    "#  below 50\n",
    "data.loc[((data.VACUNA_1_DATA_pp.isna())&(((recomm_date_1vax_below50-data.data_naixement).values/pd.Timedelta('365 days'))<=50.)), 'VACUNA_1_DATA_pp'] = pd.to_datetime(recomm_date_1vax_below50)\n",
    "\n",
    "data.loc[((data.VACUNA_2_DATA_pp.isna())&(((recomm_date_1vax_below50-data.data_naixement).values/pd.Timedelta('365 days'))<=50.)), 'VACUNA_2_DATA_pp'] = pd.to_datetime(recomm_date_2vax_below50)\n",
    "\n",
    "data.loc[((data.VACUNA_3_DATA_pp.isna())&(((recomm_date_1vax_below50-data.data_naixement).values/pd.Timedelta('365 days'))<=50.)), 'VACUNA_3_DATA_pp'] = pd.to_datetime(recomm_date_3vax_below50)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Check NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "percent_missing = data.isnull().sum() * 100 / len(data)\n",
    "missing_value_df = pd.DataFrame({'column_name': data.columns,'percent_missing': percent_missing})\n",
    "missing_value_df.sort_values('percent_missing', inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>column_name</th>\n",
       "      <th>percent_missing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NIA</th>\n",
       "      <td>NIA</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vacuna_1</th>\n",
       "      <td>Vacuna_1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vacuna_2</th>\n",
       "      <td>Vacuna_2</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covid_bef_vax</th>\n",
       "      <td>covid_bef_vax</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DM</th>\n",
       "      <td>DM</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N_vaccine_total</th>\n",
       "      <td>N_vaccine_total</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vacuna_3</th>\n",
       "      <td>Vacuna_3</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>data_naixement</th>\n",
       "      <td>data_naixement</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sexe</th>\n",
       "      <td>sexe</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abs_c</th>\n",
       "      <td>abs_c</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age_1</th>\n",
       "      <td>age_1</td>\n",
       "      <td>0.061488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age_2</th>\n",
       "      <td>age_2</td>\n",
       "      <td>0.087144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age_3</th>\n",
       "      <td>age_3</td>\n",
       "      <td>0.229321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abs</th>\n",
       "      <td>abs</td>\n",
       "      <td>1.009177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISC reescalat</th>\n",
       "      <td>ISC reescalat</td>\n",
       "      <td>2.731775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idabs</th>\n",
       "      <td>idabs</td>\n",
       "      <td>2.731775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_gma_3</th>\n",
       "      <td>test_date_gma_3</td>\n",
       "      <td>9.979930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_gma_3</th>\n",
       "      <td>test_res_gma_3</td>\n",
       "      <td>9.979930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_sociostat_3</th>\n",
       "      <td>test_date_sociostat_3</td>\n",
       "      <td>9.979930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_sociostat_3</th>\n",
       "      <td>test_res_sociostat_3</td>\n",
       "      <td>9.979930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_gma_2</th>\n",
       "      <td>test_date_gma_2</td>\n",
       "      <td>10.573656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_gma_2</th>\n",
       "      <td>test_res_gma_2</td>\n",
       "      <td>10.573656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_sociostat_2</th>\n",
       "      <td>test_date_sociostat_2</td>\n",
       "      <td>10.573656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_sociostat_2</th>\n",
       "      <td>test_res_sociostat_2</td>\n",
       "      <td>10.573656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_gma_1</th>\n",
       "      <td>test_date_gma_1</td>\n",
       "      <td>10.809498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_gma_1</th>\n",
       "      <td>test_res_gma_1</td>\n",
       "      <td>10.809498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_sociostat_1</th>\n",
       "      <td>test_date_sociostat_1</td>\n",
       "      <td>10.809498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_sociostat_1</th>\n",
       "      <td>test_res_sociostat_1</td>\n",
       "      <td>10.809498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pais_c</th>\n",
       "      <td>pais_c</td>\n",
       "      <td>21.883508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_1_DATA</th>\n",
       "      <td>VACUNA_1_DATA</td>\n",
       "      <td>23.832694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_1_MOTIU</th>\n",
       "      <td>VACUNA_1_MOTIU</td>\n",
       "      <td>23.832694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_sp_3</th>\n",
       "      <td>test_date_sp_3</td>\n",
       "      <td>27.736932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_sp_3</th>\n",
       "      <td>test_res_sp_3</td>\n",
       "      <td>27.739319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_dp_3</th>\n",
       "      <td>test_date_dp_3</td>\n",
       "      <td>27.755094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_dp_3</th>\n",
       "      <td>test_res_dp_3</td>\n",
       "      <td>27.757521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_sp_2</th>\n",
       "      <td>test_date_sp_2</td>\n",
       "      <td>28.182267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_sp_2</th>\n",
       "      <td>test_res_sp_2</td>\n",
       "      <td>28.184694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_dp_2</th>\n",
       "      <td>test_date_dp_2</td>\n",
       "      <td>28.200789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_dp_2</th>\n",
       "      <td>test_res_dp_2</td>\n",
       "      <td>28.203256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_sp_1</th>\n",
       "      <td>test_date_sp_1</td>\n",
       "      <td>28.449139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_sp_1</th>\n",
       "      <td>test_res_sp_1</td>\n",
       "      <td>28.451633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_dp_1</th>\n",
       "      <td>test_date_dp_1</td>\n",
       "      <td>28.467528</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 column_name  percent_missing\n",
       "NIA                                      NIA         0.000000\n",
       "Vacuna_1                            Vacuna_1         0.000000\n",
       "Vacuna_2                            Vacuna_2         0.000000\n",
       "covid_bef_vax                  covid_bef_vax         0.000000\n",
       "DM                                        DM         0.000000\n",
       "N_vaccine_total              N_vaccine_total         0.000000\n",
       "Vacuna_3                            Vacuna_3         0.000000\n",
       "data_naixement                data_naixement         0.000000\n",
       "sexe                                    sexe         0.000000\n",
       "abs_c                                  abs_c         0.000013\n",
       "age_1                                  age_1         0.061488\n",
       "age_2                                  age_2         0.087144\n",
       "age_3                                  age_3         0.229321\n",
       "abs                                      abs         1.009177\n",
       "ISC reescalat                  ISC reescalat         2.731775\n",
       "idabs                                  idabs         2.731775\n",
       "test_date_gma_3              test_date_gma_3         9.979930\n",
       "test_res_gma_3                test_res_gma_3         9.979930\n",
       "test_date_sociostat_3  test_date_sociostat_3         9.979930\n",
       "test_res_sociostat_3    test_res_sociostat_3         9.979930\n",
       "test_date_gma_2              test_date_gma_2        10.573656\n",
       "test_res_gma_2                test_res_gma_2        10.573656\n",
       "test_date_sociostat_2  test_date_sociostat_2        10.573656\n",
       "test_res_sociostat_2    test_res_sociostat_2        10.573656\n",
       "test_date_gma_1              test_date_gma_1        10.809498\n",
       "test_res_gma_1                test_res_gma_1        10.809498\n",
       "test_date_sociostat_1  test_date_sociostat_1        10.809498\n",
       "test_res_sociostat_1    test_res_sociostat_1        10.809498\n",
       "pais_c                                pais_c        21.883508\n",
       "VACUNA_1_DATA                  VACUNA_1_DATA        23.832694\n",
       "VACUNA_1_MOTIU                VACUNA_1_MOTIU        23.832694\n",
       "test_date_sp_3                test_date_sp_3        27.736932\n",
       "test_res_sp_3                  test_res_sp_3        27.739319\n",
       "test_date_dp_3                test_date_dp_3        27.755094\n",
       "test_res_dp_3                  test_res_dp_3        27.757521\n",
       "test_date_sp_2                test_date_sp_2        28.182267\n",
       "test_res_sp_2                  test_res_sp_2        28.184694\n",
       "test_date_dp_2                test_date_dp_2        28.200789\n",
       "test_res_dp_2                  test_res_dp_2        28.203256\n",
       "test_date_sp_1                test_date_sp_1        28.449139\n",
       "test_res_sp_1                  test_res_sp_1        28.451633\n",
       "test_date_dp_1                test_date_dp_1        28.467528"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_value_df.head(42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>column_name</th>\n",
       "      <th>percent_missing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>test_date_imc_3</th>\n",
       "      <td>test_date_imc_3</td>\n",
       "      <td>31.717353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_imc_3</th>\n",
       "      <td>test_res_imc_3</td>\n",
       "      <td>31.785335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_imc_2</th>\n",
       "      <td>test_date_imc_2</td>\n",
       "      <td>32.183704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_imc_2</th>\n",
       "      <td>test_res_imc_2</td>\n",
       "      <td>32.251979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_imc_1</th>\n",
       "      <td>test_date_imc_1</td>\n",
       "      <td>32.429987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_imc_1</th>\n",
       "      <td>test_res_imc_1</td>\n",
       "      <td>32.498102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_smoking_3</th>\n",
       "      <td>test_date_smoking_3</td>\n",
       "      <td>40.677438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_smoking_3</th>\n",
       "      <td>test_res_smoking_3</td>\n",
       "      <td>40.677438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_3_DATA_pp</th>\n",
       "      <td>VACUNA_3_DATA_pp</td>\n",
       "      <td>40.857660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_smoking_2</th>\n",
       "      <td>test_res_smoking_2</td>\n",
       "      <td>41.140534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_smoking_2</th>\n",
       "      <td>test_date_smoking_2</td>\n",
       "      <td>41.140534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_smoking_1</th>\n",
       "      <td>test_res_smoking_1</td>\n",
       "      <td>41.357068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_smoking_1</th>\n",
       "      <td>test_date_smoking_1</td>\n",
       "      <td>41.357068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_covid_3</th>\n",
       "      <td>test_res_covid_3</td>\n",
       "      <td>43.964494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_covid_3</th>\n",
       "      <td>test_date_covid_3</td>\n",
       "      <td>43.964494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_covid_2</th>\n",
       "      <td>test_date_covid_2</td>\n",
       "      <td>49.629801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_covid_2</th>\n",
       "      <td>test_res_covid_2</td>\n",
       "      <td>49.629801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_covid_1</th>\n",
       "      <td>test_date_covid_1</td>\n",
       "      <td>52.068353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_covid_1</th>\n",
       "      <td>test_res_covid_1</td>\n",
       "      <td>52.068353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_bg_3</th>\n",
       "      <td>test_date_bg_3</td>\n",
       "      <td>52.578123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_bg_3</th>\n",
       "      <td>test_res_bg_3</td>\n",
       "      <td>52.578349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_bg_2</th>\n",
       "      <td>test_date_bg_2</td>\n",
       "      <td>52.584990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_bg_2</th>\n",
       "      <td>test_res_bg_2</td>\n",
       "      <td>52.585217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_bg_1</th>\n",
       "      <td>test_date_bg_1</td>\n",
       "      <td>52.587324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_bg_1</th>\n",
       "      <td>test_res_bg_1</td>\n",
       "      <td>52.587550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_chol_3</th>\n",
       "      <td>test_date_chol_3</td>\n",
       "      <td>53.518291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_chol_3</th>\n",
       "      <td>test_res_chol_3</td>\n",
       "      <td>53.518344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_chol_2</th>\n",
       "      <td>test_date_chol_2</td>\n",
       "      <td>53.518638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_chol_2</th>\n",
       "      <td>test_res_chol_2</td>\n",
       "      <td>53.518691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_chol_1</th>\n",
       "      <td>test_date_chol_1</td>\n",
       "      <td>53.518731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_chol_1</th>\n",
       "      <td>test_res_chol_1</td>\n",
       "      <td>53.518785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_3_DATA</th>\n",
       "      <td>VACUNA_3_DATA</td>\n",
       "      <td>59.142340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_3_MOTIU</th>\n",
       "      <td>VACUNA_3_MOTIU</td>\n",
       "      <td>59.142340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_2_DATA_pp</th>\n",
       "      <td>VACUNA_2_DATA_pp</td>\n",
       "      <td>69.970387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VACUNA_1_DATA_pp</th>\n",
       "      <td>VACUNA_1_DATA_pp</td>\n",
       "      <td>76.167306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_abdo_3</th>\n",
       "      <td>test_date_abdo_3</td>\n",
       "      <td>93.990597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_abdo_3</th>\n",
       "      <td>test_res_abdo_3</td>\n",
       "      <td>93.990610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_abdo_2</th>\n",
       "      <td>test_date_abdo_2</td>\n",
       "      <td>94.079168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_abdo_2</th>\n",
       "      <td>test_res_abdo_2</td>\n",
       "      <td>94.079181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_date_abdo_1</th>\n",
       "      <td>test_date_abdo_1</td>\n",
       "      <td>94.112439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test_res_abdo_1</th>\n",
       "      <td>test_res_abdo_1</td>\n",
       "      <td>94.112452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DATA_DM_min</th>\n",
       "      <td>DATA_DM_min</td>\n",
       "      <td>98.393697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             column_name  percent_missing\n",
       "test_date_imc_3          test_date_imc_3        31.717353\n",
       "test_res_imc_3            test_res_imc_3        31.785335\n",
       "test_date_imc_2          test_date_imc_2        32.183704\n",
       "test_res_imc_2            test_res_imc_2        32.251979\n",
       "test_date_imc_1          test_date_imc_1        32.429987\n",
       "test_res_imc_1            test_res_imc_1        32.498102\n",
       "test_date_smoking_3  test_date_smoking_3        40.677438\n",
       "test_res_smoking_3    test_res_smoking_3        40.677438\n",
       "VACUNA_3_DATA_pp        VACUNA_3_DATA_pp        40.857660\n",
       "test_res_smoking_2    test_res_smoking_2        41.140534\n",
       "test_date_smoking_2  test_date_smoking_2        41.140534\n",
       "test_res_smoking_1    test_res_smoking_1        41.357068\n",
       "test_date_smoking_1  test_date_smoking_1        41.357068\n",
       "test_res_covid_3        test_res_covid_3        43.964494\n",
       "test_date_covid_3      test_date_covid_3        43.964494\n",
       "test_date_covid_2      test_date_covid_2        49.629801\n",
       "test_res_covid_2        test_res_covid_2        49.629801\n",
       "test_date_covid_1      test_date_covid_1        52.068353\n",
       "test_res_covid_1        test_res_covid_1        52.068353\n",
       "test_date_bg_3            test_date_bg_3        52.578123\n",
       "test_res_bg_3              test_res_bg_3        52.578349\n",
       "test_date_bg_2            test_date_bg_2        52.584990\n",
       "test_res_bg_2              test_res_bg_2        52.585217\n",
       "test_date_bg_1            test_date_bg_1        52.587324\n",
       "test_res_bg_1              test_res_bg_1        52.587550\n",
       "test_date_chol_3        test_date_chol_3        53.518291\n",
       "test_res_chol_3          test_res_chol_3        53.518344\n",
       "test_date_chol_2        test_date_chol_2        53.518638\n",
       "test_res_chol_2          test_res_chol_2        53.518691\n",
       "test_date_chol_1        test_date_chol_1        53.518731\n",
       "test_res_chol_1          test_res_chol_1        53.518785\n",
       "VACUNA_3_DATA              VACUNA_3_DATA        59.142340\n",
       "VACUNA_3_MOTIU            VACUNA_3_MOTIU        59.142340\n",
       "VACUNA_2_DATA_pp        VACUNA_2_DATA_pp        69.970387\n",
       "VACUNA_1_DATA_pp        VACUNA_1_DATA_pp        76.167306\n",
       "test_date_abdo_3        test_date_abdo_3        93.990597\n",
       "test_res_abdo_3          test_res_abdo_3        93.990610\n",
       "test_date_abdo_2        test_date_abdo_2        94.079168\n",
       "test_res_abdo_2          test_res_abdo_2        94.079181\n",
       "test_date_abdo_1        test_date_abdo_1        94.112439\n",
       "test_res_abdo_1          test_res_abdo_1        94.112452\n",
       "DATA_DM_min                  DATA_DM_min        98.393697"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_value_df.tail(42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Check covariate variety and nans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Variety"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for test_i in tests:\n",
    "  cols = ['test_date_{}_1'.format(test_i), 'test_date_{}_2'.format(test_i), 'test_date_{}_3'.format(test_i)]\n",
    "  data[test_i] = data[cols].apply(lambda x: sum( [x[cols[0]]==x[cols[1]], x[cols[0]]==x[cols[2]], x[cols[1]]==x[cols[2]]] ), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sp\n",
      "3    4534088\n",
      "0    2189567\n",
      "1     775426\n",
      "Name: count, dtype: int64\n",
      "sociostat\n",
      "3    4573326\n",
      "1    2132596\n",
      "0     793159\n",
      "Name: count, dtype: int64\n",
      "abdo\n",
      "0    7055621\n",
      "3     430187\n",
      "1      13273\n",
      "Name: count, dtype: int64\n",
      "chol\n",
      "0    4013406\n",
      "3    3485596\n",
      "1         79\n",
      "Name: count, dtype: int64\n",
      "smoking\n",
      "3    4357858\n",
      "0    3085519\n",
      "1      55704\n",
      "Name: count, dtype: int64\n",
      "gma\n",
      "3    4573326\n",
      "1    2132596\n",
      "0     793159\n",
      "Name: count, dtype: int64\n",
      "imc\n",
      "3    4541895\n",
      "0    2444032\n",
      "1     513154\n",
      "Name: count, dtype: int64\n",
      "covid\n",
      "0    3954341\n",
      "3    2337201\n",
      "1    1207539\n",
      "Name: count, dtype: int64\n",
      "bg\n",
      "0    3943544\n",
      "3    3552977\n",
      "1       2560\n",
      "Name: count, dtype: int64\n",
      "dp\n",
      "3    4532960\n",
      "0    2190918\n",
      "1     775203\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "for test in tests:\n",
    "  print(data[test].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Nans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "for test_i in tests:\n",
    "  cols = ['test_date_{}_1'.format(test_i), 'test_date_{}_2'.format(test_i), 'test_date_{}_3'.format(test_i)]\n",
    "  data['{}_notcarried'.format(test_i)] = data[cols].apply(lambda x: 1 if pd.isna(x[cols[0]])\n",
    "                                                                    else ( 2 if (pd.isna(x[cols[1]]))\n",
    "                                                                    else ( 3 if (pd.isna(x[cols[1]]))\n",
    "                                                                    else 4)), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n",
      "dp_notcarried\n",
      "4    5364278\n",
      "1    2134803\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "for test in tests:\n",
    "  print(data['{}_notcarried'.format(test_i)].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Outlier removal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IMC above 70 becomes NaN\n",
    "data.loc[data.test_res_imc_1>70, 'test_res_imc_1'] = np.NaN\n",
    "data.loc[data.test_res_imc_2>70, 'test_res_imc_2'] = np.NaN\n",
    "data.loc[data.test_res_imc_2>70, 'test_res_imc_2'] = np.NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# People of more than 110 years must be dead\n",
    "data.data_naixement = data.data_naixement.str[0:4].astype(int)\n",
    "data = data[data.data_naixement>1914]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Baseline cov NaN imputation (based on other covariates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time-invariant pre-baseline covars (with nans): country of origin, abs, isc. Sample from prob. distrib, with frequencies equal to those in the present data. \n",
    "# In this way, we end up with the value that is \"most likely\", and ¿errors will cancel each other out?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "pais_c_sampled = np.random.choice(data.pais_c.value_counts().index.values, size = len(data.pais_c.isna()), p = data.pais_c.value_counts().values/sum(data.pais_c.value_counts().values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[data.pais_c.isna(), 'pais_c'] = pd.Series(pais_c_sampled)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "abs_sampled = np.random.choice(data.abs_c.value_counts().index.values, size = len(data.abs_c.isna()), p = data.abs_c.value_counts().values/sum(data.abs_c.value_counts().values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[data.abs_c.isna(), 'abs_c'] = pd.Series(abs_sampled)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time-varying baseline covars "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "BMI -> Blood_pressure\n",
    "BMI -> Charlson_i\n",
    "BMI -> Covid_infection\n",
    "BMI -> Pre_diabetes\n",
    "BMI -> Waist_size\n",
    "\n",
    "Birth_year -> BMI\n",
    "Birth_year -> Blood_pressure\n",
    "Birth_year -> Charlson_i\n",
    "Birth_year -> Cholesterol\n",
    "Birth_year -> Pre_diabetes\n",
    "Birth_year -> Smoking\n",
    "Birth_year -> Socio_status\n",
    "Birth_year -> Waist_size\n",
    "\n",
    "Blood_pressure -> Charlson_i\n",
    "\n",
    "Charlson_i -> Covid_infection\n",
    "\n",
    "Cholesterol -> Charlson_i\n",
    "\n",
    "Country_origin -> BMI\n",
    "Country_origin -> Blood_pressure\n",
    "Country_origin -> Charlson_i\n",
    "Country_origin -> Cholesterol\n",
    "Country_origin -> Pre_diabetes\n",
    "Country_origin -> Smoking\n",
    "Country_origin -> Socio_status\n",
    "Country_origin -> Waist_size\n",
    "\n",
    "Pre_diabetes -> Charlson_i\n",
    "Pre_diabetes -> Covid_infection\n",
    "\n",
    "Smoking -> BMI\n",
    "Smoking -> Blood_pressure\n",
    "Smoking -> Charlson_i\n",
    "Smoking -> Covid_infection\n",
    "Smoking -> Waist_size\n",
    "\n",
    "Socio_status -> BMI\n",
    "Socio_status -> Blood_pressure\n",
    "Socio_status -> Cholesterol\n",
    "Socio_status -> Covid_infection\n",
    "Socio_status -> Pre_diabetes\n",
    "Socio_status -> Smoking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "dag_list = ['BMI -> Blood_pressure_dp',\n",
    "'BMI -> Blood_pressure_sp',\n",
    "'BMI -> Charlson_i',\n",
    "'BMI -> Covid_infection',\n",
    "'BMI -> Pre_diabetes',\n",
    "'BMI -> Waist_size',\n",
    "'Birth_year -> BMI',\n",
    "'Birth_year -> Blood_pressure_dp',\n",
    "'Birth_year -> Blood_pressure_sp',\n",
    "'Birth_year -> Charlson_i',\n",
    "'Birth_year -> Cholesterol',\n",
    "'Birth_year -> Pre_diabetes',\n",
    "'Birth_year -> Smoking',\n",
    "'Birth_year -> Socio_status',\n",
    "'Birth_year -> Waist_size',\n",
    "'Blood_pressure_dp -> Charlson_i',\n",
    "'Blood_pressure_sp -> Charlson_i',\n",
    "'Charlson_i -> Covid_infection',\n",
    "'Cholesterol -> Charlson_i',\n",
    "'Country_origin -> BMI',\n",
    "'Country_origin -> Blood_pressure_dp',\n",
    "'Country_origin -> Blood_pressure_sp',\n",
    "'Country_origin -> Charlson_i',\n",
    "'Country_origin -> Cholesterol',\n",
    "'Country_origin -> Pre_diabetes',\n",
    "'Country_origin -> Smoking',\n",
    "'Country_origin -> Socio_status',\n",
    "'Country_origin -> Waist_size',\n",
    "'Pre_diabetes -> Charlson_i',\n",
    "'Pre_diabetes -> Covid_infection',\n",
    "'Smoking -> BMI',\n",
    "'Smoking -> Blood_pressure_dp',\n",
    "'Smoking -> Blood_pressure_sp',\n",
    "'Smoking -> Charlson_i',\n",
    "'Smoking -> Covid_infection',\n",
    "'Smoking -> Waist_size',\n",
    "'Socio_status -> BMI',\n",
    "'Socio_status -> Blood_pressure_dp',\n",
    "'Socio_status -> Blood_pressure_sp',\n",
    "'Socio_status -> Cholesterol',\n",
    "'Socio_status -> Covid_infection',\n",
    "'Socio_status -> Pre_diabetes',\n",
    "'Socio_status -> Smoking']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "pair_list = []\n",
    "for pair in dag_list:\n",
    "  pair_list.append(pair.split(' -> '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "pair_list = pd.DataFrame(pair_list).replace({'BMI': 'imc',\n",
    " 'Birth_year': 'data_naixement',\n",
    " 'Waist_size':  'abdo',\n",
    " 'Cholesterol':  'chol',\n",
    " 'Socio_status':  'sociostat',\n",
    " 'Charlson_i':  'gma',\n",
    " 'Blood_pressure_dp':  'dp',\n",
    " 'Blood_pressure_sp':  'sp',\n",
    " 'Covid_infection': 'covid',\n",
    " 'Pre_diabetes': 'bg',\n",
    " 'Country_origin': 'pais_c',\n",
    " 'Smoking': 'smoking'}).values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Imputting with random forest strategy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "smoking\n",
      "['data_naixement', 'pais_c', 'sociostat']\n",
      "sp\n",
      "['imc', 'data_naixement', 'pais_c', 'smoking', 'sociostat']\n",
      "chol\n",
      "['data_naixement', 'pais_c', 'sociostat']\n",
      "abdo\n",
      "['imc', 'data_naixement', 'pais_c', 'smoking']\n",
      "bg\n",
      "['imc', 'data_naixement', 'pais_c', 'sociostat']\n",
      "covid\n",
      "['imc', 'gma', 'bg', 'smoking', 'sociostat']\n",
      "dp\n",
      "['imc', 'data_naixement', 'pais_c', 'smoking', 'sociostat']\n",
      "sociostat\n",
      "['data_naixement', 'pais_c']\n",
      "imc\n",
      "['data_naixement', 'pais_c', 'smoking', 'sociostat']\n",
      "gma\n",
      "['imc', 'data_naixement', 'dp', 'sp', 'chol', 'pais_c', 'bg', 'smoking']\n"
     ]
    }
   ],
   "source": [
    "# 'Flatten' list \n",
    "flat_list = [x for inner_list in pair_list for x in inner_list]\n",
    "\n",
    "for elem in set(flat_list)-set(['pais_c', 'data_naixement']):\n",
    "  print(elem)\n",
    "  affecting_vars = []\n",
    "  for pair in pair_list:\n",
    "    if pair[1]==elem:\n",
    "      #print(pair)\n",
    "      affecting_vars.append(pair[0])\n",
    "  print(affecting_vars)\n",
    "    \n",
    "  affecting_vars_fullname = []\n",
    "  for var in affecting_vars:\n",
    "    #print(var)\n",
    "    if ((var!='pais_c') and (var!='data_naixement')):\n",
    "      affecting_vars_fullname.append('test_res_{}_1'.format(var))\n",
    "    else:\n",
    "      affecting_vars_fullname.append(var)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "smoking\n",
      "sp\n",
      "chol\n",
      "abdo\n",
      "bg\n",
      "covid\n",
      "classif\n",
      "dp\n",
      "sociostat\n",
      "classif\n",
      "imc\n",
      "gma\n"
     ]
    }
   ],
   "source": [
    "# 'Flatten' list \n",
    "flat_list = [x for inner_list in pair_list for x in inner_list]\n",
    "\n",
    "for elem in set(flat_list)-set(['pais_c', 'data_naixement']):\n",
    "  print(elem)\n",
    "  affecting_vars = []\n",
    "  for pair in pair_list:\n",
    "    if pair[1]==elem:\n",
    "      #print(pair)\n",
    "      affecting_vars.append(pair[0])\n",
    "  #print(affecting_vars)\n",
    "    \n",
    "  affecting_vars_fullname = []\n",
    "  for var in affecting_vars:\n",
    "    #print(var)\n",
    "    if ((var!='pais_c') and (var!='data_naixement')):\n",
    "      affecting_vars_fullname.append('test_res_{}_1'.format(var))\n",
    "    else:\n",
    "      affecting_vars_fullname.append(var)\n",
    "  \n",
    "  if (elem=='covid')|(elem=='sociostat'):\n",
    "    regr = RandomForestClassifier(n_jobs=36)\n",
    "    regr.fit(data.loc[~data['test_res_{}_1'.format(elem)].isna(), affecting_vars_fullname], data.loc[~data['test_res_{}_1'.format(elem)].isna(), 'test_res_{}_1'.format(elem)])\n",
    "  else:\n",
    "    if elem=='gma':\n",
    "      njobs=18\n",
    "    else:\n",
    "      njobs=36\n",
    "    regr = RandomForestRegressor(n_jobs=njobs)\n",
    "    regr.fit(data.loc[~data['test_res_{}_1'.format(elem)].isna(), affecting_vars_fullname], data.loc[~data['test_res_{}_1'.format(elem)].isna(), 'test_res_{}_1'.format(elem)])\n",
    "    gc.collect()\n",
    "    \n",
    "  data.loc[data['test_res_{}_1'.format(elem)].isna(), 'test_res_{}_1'.format(elem)] = regr.predict(data.loc[data['test_res_{}_1'.format(elem)].isna(), affecting_vars_fullname])\n",
    "\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Problem: GMA blocks the whole memory and gives memory errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test date. Input all at 2021-01-01. Is this the best approach?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "for test in tests:\n",
    "  data['test_date_{}_1'.format(test)] = data['test_date_{}_1'.format(test)].fillna(value='2021-01-01T00:00:00.000Z')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Carry-forward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "for test in tests:\n",
    "  for i in range(2,4):\n",
    "    data.loc[data['test_res_{}_{}'.format(test,i)].isna(), 'test_res_{}_{}'.format(test,i)] = data.loc[data['test_res_{}_{}'.format(test,i)].isna(), 'test_res_{}_{}'.format(test,i-1)]\n",
    "    data.loc[data['test_date_{}_{}'.format(test,i)].isna(), 'test_date_{}_{}'.format(test,i)] = data.loc[data['test_date_{}_{}'.format(test,i)].isna(), 'test_date_{}_{}'.format(test,i-1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NIA                  False\n",
       "sexe                 False\n",
       "data_naixement       False\n",
       "abs_c                False\n",
       "abs                   True\n",
       "pais_c               False\n",
       "N_vaccine_total      False\n",
       "VACUNA_1_DATA         True\n",
       "VACUNA_1_MOTIU        True\n",
       "VACUNA_2_DATA         True\n",
       "VACUNA_2_MOTIU        True\n",
       "VACUNA_3_DATA         True\n",
       "VACUNA_3_MOTIU        True\n",
       "VACUNA_1_DATA_pp      True\n",
       "VACUNA_2_DATA_pp      True\n",
       "VACUNA_3_DATA_pp      True\n",
       "DATA_DM_min           True\n",
       "DM                   False\n",
       "covid_bef_vax        False\n",
       "test_date_covid_1    False\n",
       "test_res_covid_1     False\n",
       "test_date_covid_2    False\n",
       "test_res_covid_2     False\n",
       "test_date_covid_3    False\n",
       "test_res_covid_3     False\n",
       "test_date_imc_1      False\n",
       "test_res_imc_1       False\n",
       "test_date_imc_2      False\n",
       "test_res_imc_2       False\n",
       "test_date_imc_3      False\n",
       "test_res_imc_3       False\n",
       "test_date_sp_1       False\n",
       "test_res_sp_1        False\n",
       "test_date_sp_2       False\n",
       "test_res_sp_2        False\n",
       "test_date_sp_3       False\n",
       "test_res_sp_3        False\n",
       "test_date_dp_1       False\n",
       "test_res_dp_1        False\n",
       "test_date_dp_2       False\n",
       "test_res_dp_2        False\n",
       "test_date_dp_3       False\n",
       "test_res_dp_3        False\n",
       "test_date_abdo_1     False\n",
       "test_res_abdo_1      False\n",
       "test_date_abdo_2     False\n",
       "test_res_abdo_2      False\n",
       "test_date_abdo_3     False\n",
       "test_res_abdo_3      False\n",
       "test_date_bg_1       False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isna().any().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "test_date_dp_1           False\n",
       "test_res_dp_1            False\n",
       "test_date_dp_2           False\n",
       "test_res_dp_2            False\n",
       "test_date_dp_3           False\n",
       "test_res_dp_3            False\n",
       "test_date_abdo_1         False\n",
       "test_res_abdo_1          False\n",
       "test_date_abdo_2         False\n",
       "test_res_abdo_2          False\n",
       "test_date_abdo_3         False\n",
       "test_res_abdo_3          False\n",
       "test_date_bg_1           False\n",
       "test_res_bg_1            False\n",
       "test_date_bg_2           False\n",
       "test_res_bg_2            False\n",
       "test_date_bg_3           False\n",
       "test_res_bg_3            False\n",
       "test_date_chol_1         False\n",
       "test_res_chol_1          False\n",
       "test_date_chol_2         False\n",
       "test_res_chol_2          False\n",
       "test_date_chol_3         False\n",
       "test_res_chol_3          False\n",
       "test_date_smoking_1      False\n",
       "test_res_smoking_1       False\n",
       "test_date_smoking_2      False\n",
       "test_res_smoking_2       False\n",
       "test_date_smoking_3      False\n",
       "test_res_smoking_3       False\n",
       "test_date_gma_1          False\n",
       "test_res_gma_1           False\n",
       "test_date_gma_2          False\n",
       "test_res_gma_2           False\n",
       "test_date_gma_3          False\n",
       "test_res_gma_3           False\n",
       "test_date_sociostat_1    False\n",
       "test_res_sociostat_1     False\n",
       "test_date_sociostat_2    False\n",
       "test_res_sociostat_2     False\n",
       "test_date_sociostat_3    False\n",
       "test_res_sociostat_3     False\n",
       "age_1                     True\n",
       "age_2                     True\n",
       "age_3                     True\n",
       "idabs                     True\n",
       "ISC reescalat             True\n",
       "Vacuna_1                 False\n",
       "Vacuna_2                 False\n",
       "Vacuna_3                 False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isna().any().tail(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some final checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data.sexe = data.sexe.map({'H':0, 'D':1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data.to_csv('included_cohort_prep.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Save sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data_sample = data.sample(frac=0.05, random_state=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data_sample.to_csv('included_cohort_sample_prep.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python3.10-kernel",
   "language": "python",
   "name": "python3.10"
  },
  "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}