02-variable-selection.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 🚿 Filtering column names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📒 Reading dataframes and filtering columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_path = './datasets/'\n",
    "file_name_convention = 'visit'\n",
    "extension = '.tsv'\n",
    "\n",
    "# Load the data, scanning in the base path, but load files as needed, not all at once\n",
    "\n",
    "def get_visit_records():\n",
    "\n",
    "    filenames = []\n",
    "\n",
    "    for root, dirs, files in os.walk(base_path):\n",
    "\n",
    "        for filename in files:\n",
    "            if filename.startswith(file_name_convention) and filename.endswith(extension):\n",
    "                filenames.append(filename)\n",
    "        \n",
    "    for filename in filenames:\n",
    "        df = pd.read_csv(\n",
    "            base_path + filename,\n",
    "            sep='\\t',\n",
    "            index_col=False\n",
    "        )\n",
    "        yield df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ABBLEED', 'AGE1', 'AGE10', 'AGE11', 'AGE12', 'AGE2', 'AGE3', 'AGE4', 'AGE5', 'AGE6', 'AGE7', 'AGE8', 'AGE9', 'AGE', 'ALCHL24', 'WORSE', 'FOODPNA', 'AVCIGDA', 'GLASBEE', 'GLASLIQ', 'GLASWIN', 'BONES1', 'BONES2', 'BONES3', 'BOTHOTF', 'LEKBOTH', 'COMBIN1', 'COMBIN2', 'NOREMEB', 'BATCARB', 'DTTDFIB', 'BATKCAL', 'DTTALCH', 'BATNIAC', 'BATPHOS', 'BATPOTS', 'BATPROT', 'BATRIBO', 'BATTFAT', 'HOMEXPD', 'DIABETE', 'HAVEPER', 'LIKEFEL', 'ALLBCAR', 'ALLCALC', 'ALLFOL', 'ALLIRON', 'ALLARE', 'ALLB1', 'ALLB12', 'ALLB6', 'ALLVITC', 'ALLVITD', 'ALLVITE', 'ALLZINC', 'ALLB2', 'NOLIKE', 'EXPECT', 'DNTKNOW', 'DONTKNO', 'MENODEP', 'IMEDTHR', 'EMBDDEV', 'E2AVE', 'ESTRDA1', 'ESTRDA2', 'ESTRNJ1', 'ESTRNJ2', 'ESTROG1', 'ESTROG2', 'EFPDFIB', 'EFPB1', 'EFPB12', 'EFPB6', 'EFPARE', 'EFP', 'LEKDISC', 'EXERCIS', 'EXERGEN', 'EXERHAR', 'EXERMEM', 'EXERMEN', 'EXEROST', 'EXEROTH', 'EXERSPE', 'EXERADV', 'EXERPER', 'EXERWGH', 'EXERLOO', 'EXER12H', 'FACEI1', 'FACEI10', 'FACEI11', 'FACEI12', 'FACEI13', 'FACEI14', 'FACEI15', 'FACEI16', 'FACEI17', 'FACEI18', 'FACEI19', 'FACEI2', 'FACEI20', 'FACEI21', 'FACEI22', 'FACEI23', 'FACEI24', 'FACEI25', 'FACEI26', 'FACEI27', 'FACEI28', 'FACEI29', 'FACEI3', 'FACEI30', 'FACEI31', 'FACEI32', 'FACEI33', 'FACEI34', 'FACEI35', 'FACEI36', 'FACEI37', 'FACEI38', 'FACEI39', 'FACEI4', 'FACEI40', 'FACEI41', 'FACEI42', 'FACEI43', 'FACEI44', 'FACEI45', 'FACEI46', 'FACEI47', 'FACEI48', 'FACEI5', 'FACEI6', 'FACEI7', 'FACEI8', 'FACEI9', 'AGEWORS', 'LANGFFQ', 'LANGSPK', 'FRNADVI', 'FEAROLD', 'RXTRMLK', 'HCPADVI', 'ADVISTO', 'FODPNHP', 'PROTEIN', 'HOTFLAS', 'NUMHOTF', 'HOMEXPH', 'WRKEXPH', 'HHMEMSM', 'HOURSPA', 'WLOSSDT', 'IMPRMEM', 'INSTSHK', 'FODPNJC', 'JENCRAI', 'LANGBIO', 'LANGCOG', 'LANGINT', 'LANGSAA', 'HORMDAY', 'LRXKEGL', 'ATTRACT', 'H_NOTIC', 'FODPNLF', 'LOWFATD', 'FODPNLS', 'LOWSALT', 'STRTEST', 'STPESTR', 'NO_MED', 'ONMEASS', 'ALCHMON', 'ALCO24H', 'FREE', 'LOOKSAG', 'LTXMYSF', 'LDRNASK', 'LNRMCHD', 'LNRMOLD', 'LEAKOR', 'LEAKORS', 'LNOTBAD', 'LNOEFTX', 'LEMBARR', 'LWYSURG', 'BROKEBO', 'WRKEXPE', 'ALCHSRV', 'DIETNUT', 'DIETGEN', 'DIETHAR', 'DIETMEM', 'DIETMEN', 'DIETOST', 'DIETOTH', 'DIETSPE', 'DIETADV', 'DIETPER', 'DIETWGH', 'DIETLOO', 'OLD_VAL', 'OSTEIV1', 'OSTEON1', 'OSTEON2', 'OSTEOPR', 'OHRM_1', 'OHRM_2', 'OHRM_3', 'OHRM_4', 'OTHSPOR', 'OUTCOME', 'POSITIV', 'WRKEXPD', 'PROLAPS', 'PCTALCH', 'PRGNANT', 'PRBBLEE', 'RACE', 'PHYSACT', 'REDUHAR', 'OSTEOPO', 'REGRET', 'REGPERI', 'MENOSYM', 'PHYSILL', 'SKELMM', 'FOODBNS', 'SOUTHBC', 'SOYYSNO', 'SPECFOO', 'HRSACT1', 'HRSACT2', 'SPOREX1', 'SPOREX2', 'YOUNGLK', 'NEWSRPT', 'NOREASO', 'STOPOTH', 'HORMOTH', 'NERVES', 'PROVTIM', 'CARING', 'SPORTS', 'EXPENSI', 'TOTEXPH', 'ESTLSTV', 'VAGDISH', 'VAGINDR', 'VAGIRRT', 'VAGSORE', 'FODPNVD', 'VEGDIET', 'ALCHWK', 'PHYSWOR', 'CANCER', 'SIDEEFF']\n"
     ]
    }
   ],
   "source": [
    "# Load variable names\n",
    "\n",
    "variables = []\n",
    "\n",
    "with open('./lista_variables/variables_selectas.txt', ) as file:\n",
    "    variables = [var[:-1] for var in file.readlines()]\n",
    "\n",
    "print(variables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ⚖ Normalizing variable names to find common variables\n",
    "\n",
    "Given that the column names have in their name the visit id at the end, we have to take it away to proceed more easily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrador\\AppData\\Local\\Temp\\ipykernel_11060\\3460416426.py:18: DtypeWarning: Columns (3) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(\n",
      "C:\\Users\\Administrador\\AppData\\Local\\Temp\\ipykernel_11060\\3460416426.py:18: DtypeWarning: Columns (3) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(\n",
      "C:\\Users\\Administrador\\AppData\\Local\\Temp\\ipykernel_11060\\3460416426.py:18: DtypeWarning: Columns (3,4,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,51,52,53,54,57,58,59,60,61,63,64,65,66,67,68,69,70,71,72,73,74,75,85,87,88,90,91,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,477,478,482,483,484,485,486,490,491,495,504,506,507,515,522,530,536,537,538,539,577) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(\n",
      "C:\\Users\\Administrador\\AppData\\Local\\Temp\\ipykernel_11060\\3460416426.py:18: DtypeWarning: Columns (3,4,5,51,57,63,67,71,504,522,1277) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(\n",
      "C:\\Users\\Administrador\\AppData\\Local\\Temp\\ipykernel_11060\\3460416426.py:18: DtypeWarning: Columns (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,46,47,48,49,50,51,52,53,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,84,86,87,90,91,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,477,478,482,483,484,485,486,490,491,492,495,504,506,507,515,522,523,525,530,535,536,537,538,563,567) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    Normalize the variable names.\n",
    "\n",
    "    This requires getting rid of the last character in each column name, which, as stated\n",
    "    above, is the visit ID, so we can concatenate all the dataframes together.\n",
    "\n",
    "    Note: not all the dataframes have the same columns, so we need to get the union of all\n",
    "    the columns in all the dataframes.\n",
    "\"\"\"\n",
    "\n",
    "visit_dfs = [*get_visit_records()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>SWANID</th>\n",
       "      <th>VISIT</th>\n",
       "      <th>INTDAY1</th>\n",
       "      <th>AGE1</th>\n",
       "      <th>LANGINT1</th>\n",
       "      <th>RACE</th>\n",
       "      <th>PREGNAN1</th>\n",
       "      <th>PREVBLO1</th>\n",
       "      <th>EATDRIN1</th>\n",
       "      <th>STRTPER1</th>\n",
       "      <th>...</th>\n",
       "      <th>SPSCDAY1</th>\n",
       "      <th>SPSCTIM1</th>\n",
       "      <th>SPSCMOD1</th>\n",
       "      <th>HPSCDAY1</th>\n",
       "      <th>HPSCTIM1</th>\n",
       "      <th>HPSCMOD1</th>\n",
       "      <th>SPBMDT1</th>\n",
       "      <th>HPBMDT1</th>\n",
       "      <th>BMDFLG1</th>\n",
       "      <th>STATUS1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10046</td>\n",
       "      <td>1</td>\n",
       "      <td>413</td>\n",
       "      <td>53</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>413</td>\n",
       "      <td>0:10:08</td>\n",
       "      <td>5</td>\n",
       "      <td>413</td>\n",
       "      <td>0:09:52</td>\n",
       "      <td>5</td>\n",
       "      <td>1.1277</td>\n",
       "      <td>.9619</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10056</td>\n",
       "      <td>1</td>\n",
       "      <td>357</td>\n",
       "      <td>52</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>441</td>\n",
       "      <td>0:12:55</td>\n",
       "      <td>11</td>\n",
       "      <td>441</td>\n",
       "      <td>0:13:03</td>\n",
       "      <td>11</td>\n",
       "      <td>.914</td>\n",
       "      <td>.8499</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10092</td>\n",
       "      <td>1</td>\n",
       "      <td>364</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>364</td>\n",
       "      <td>0:18:01</td>\n",
       "      <td>5</td>\n",
       "      <td>364</td>\n",
       "      <td>0:17:39</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0377</td>\n",
       "      <td>.8412</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10126</td>\n",
       "      <td>1</td>\n",
       "      <td>442</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>2</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>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10153</td>\n",
       "      <td>1</td>\n",
       "      <td>374</td>\n",
       "      <td>52</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>402</td>\n",
       "      <td>0:10:47</td>\n",
       "      <td>11</td>\n",
       "      <td>402</td>\n",
       "      <td>0:10:40</td>\n",
       "      <td>11</td>\n",
       "      <td>1.0673</td>\n",
       "      <td>1.0313</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 576 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SWANID  VISIT INTDAY1 AGE1 LANGINT1  RACE PREGNAN1 PREVBLO1 EATDRIN1  \\\n",
       "0   10046      1     413   53        1     2        1        1        1   \n",
       "1   10056      1     357   52        1     4        1                 1   \n",
       "2   10092      1     364   46        1     4        1                 1   \n",
       "3   10126      1     442   50        1     1        1                 1   \n",
       "4   10153      1     374   52        1     3        1                 1   \n",
       "\n",
       "  STRTPER1  ... SPSCDAY1      SPSCTIM1 SPSCMOD1 HPSCDAY1      HPSCTIM1  \\\n",
       "0        2  ...      413       0:10:08        5      413       0:09:52   \n",
       "1        2  ...      441       0:12:55       11      441       0:13:03   \n",
       "2        1  ...      364       0:18:01        5      364       0:17:39   \n",
       "3        2  ...                      .                               .   \n",
       "4        2  ...      402       0:10:47       11      402       0:10:40   \n",
       "\n",
       "  HPSCMOD1 SPBMDT1 HPBMDT1 BMDFLG1 STATUS1  \n",
       "0        5  1.1277   .9619       0       7  \n",
       "1       11    .914   .8499       0       4  \n",
       "2        5  1.0377   .8412       0       4  \n",
       "3                                        4  \n",
       "4       11  1.0673  1.0313       0       4  \n",
       "\n",
       "[5 rows x 576 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visit_dfs[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "291"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def normalize_columns(df, suffix: str):\n",
    "    # Remove last digit (matching index + 1) at the end, if it exists\n",
    "    return [col.removesuffix(suffix) if col.endswith(suffix) else col for col in df.columns]\n",
    "\n",
    "\n",
    "unique_variables = set(normalize_columns(visit_dfs[0], '1'))\n",
    "\n",
    "\n",
    "for index, df in enumerate(visit_dfs[1:]):\n",
    "\n",
    "    index_str = str(index + 2)  # Index starts at 0, but we continue at 2\n",
    "    columns_no_visit = normalize_columns(df, index_str)\n",
    "    unique_variables &= set(columns_no_visit)\n",
    "\n",
    "variable_inter = list(unique_variables)\n",
    "len(variable_inter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['HAVEPER',\n",
       " 'NERVTW2',\n",
       " 'BP1',\n",
       " 'SPSCMOD',\n",
       " 'DNTKNOW',\n",
       " 'FLGCV',\n",
       " 'OTHTW14',\n",
       " 'HPBMDT',\n",
       " 'HEART2',\n",
       " 'COLDSWE',\n",
       " 'OTHME15',\n",
       " 'DHAS',\n",
       " 'INSULN2',\n",
       " 'OTHMED7',\n",
       " 'PREGNAN',\n",
       " 'FLGDIF',\n",
       " 'UNFRNDL',\n",
       " 'PROGES1',\n",
       " 'ESTRDA2',\n",
       " 'HOPEFUL',\n",
       " 'SITESPE',\n",
       " 'REGPERI',\n",
       " 'ESTRNJ2',\n",
       " 'SPSCDAY',\n",
       " 'OTHMED4',\n",
       " 'OTHRTW3',\n",
       " 'OUTCOME',\n",
       " 'REDUHAR',\n",
       " 'PHYSILL',\n",
       " 'OTHTW13',\n",
       " 'HPSCTIM',\n",
       " 'BLDDRAW',\n",
       " 'OOPHORE',\n",
       " 'SLEEP1',\n",
       " 'VAGINDR',\n",
       " 'OTC4',\n",
       " 'NUMDAND',\n",
       " 'WORSREL',\n",
       " 'E2AVE',\n",
       " 'OTCTW3',\n",
       " 'THYROID',\n",
       " 'WAIST',\n",
       " 'ABILITY',\n",
       " 'DIURTW1',\n",
       " 'HARTRAC',\n",
       " 'NERVS2',\n",
       " 'CHOLTW2',\n",
       " 'BOTHER',\n",
       " 'WORKTRB',\n",
       " 'PAINTW1',\n",
       " 'HPSCDAY',\n",
       " 'OTC1',\n",
       " 'EATDRIN',\n",
       " 'HARTTW2',\n",
       " 'THYRREM',\n",
       " 'SLEPTW1',\n",
       " 'DIZZY',\n",
       " 'MAJEVEN',\n",
       " 'NOLIKE',\n",
       " 'OTHMED5',\n",
       " 'ADVISTO',\n",
       " 'DONTKNO',\n",
       " 'OTHMED9',\n",
       " 'THYROI2',\n",
       " 'OTHMED3',\n",
       " 'PULSE',\n",
       " 'OTHME10',\n",
       " 'OTHTW15',\n",
       " 'PAPSMEA',\n",
       " 'DIURTW2',\n",
       " 'BRSTEXA',\n",
       " 'STERTW1',\n",
       " 'EXPENSI',\n",
       " 'OTHMED6',\n",
       " 'BROKEBO',\n",
       " 'STRTPER',\n",
       " 'LEGALPR',\n",
       " 'MAMOGRA',\n",
       " 'COMBTW2',\n",
       " 'BCPTWI2',\n",
       " 'PROGES2',\n",
       " 'OTHRTW1',\n",
       " 'OSTEOAR',\n",
       " 'MONEYPR',\n",
       " 'SAADAY',\n",
       " 'OTHRTW6',\n",
       " 'OTC3',\n",
       " 'EINJTW1',\n",
       " 'RACE',\n",
       " 'COMBIN1',\n",
       " 'TALKLES',\n",
       " 'GETGOIN',\n",
       " 'APPETIT',\n",
       " 'HYSTERE',\n",
       " 'LANGSAA',\n",
       " 'CLOSDIE',\n",
       " 'FRTLTW1',\n",
       " 'OTHMED2',\n",
       " 'NERVS1',\n",
       " 'HCPADVI',\n",
       " 'CHILDMO',\n",
       " 'PILING',\n",
       " 'HLTHSER',\n",
       " 'THYRTW2',\n",
       " 'HIGHBP',\n",
       " 'OTHTW12',\n",
       " 'HDACHE',\n",
       " 'NOREMEB',\n",
       " 'HAPPY',\n",
       " 'HARTTW1',\n",
       " 'HAPPEN2',\n",
       " 'OVERHLT',\n",
       " 'SYSBP1',\n",
       " 'BPTW1',\n",
       " 'DEPRESS',\n",
       " 'BMDFLG',\n",
       " 'SCALE',\n",
       " 'NERVTW1',\n",
       " 'DIABP1',\n",
       " 'INSULN1',\n",
       " 'CYCDAY',\n",
       " 'HAPPEN1',\n",
       " 'NITESWE',\n",
       " 'WAKEARL',\n",
       " 'MDTALK',\n",
       " 'MENOSYM',\n",
       " 'CHNGJOB',\n",
       " 'CRYING',\n",
       " 'VISIT',\n",
       " 'EINJTW2',\n",
       " 'INSUTW2',\n",
       " 'RESPCAR',\n",
       " 'WASTMEA',\n",
       " 'OTHME14',\n",
       " 'BONES3',\n",
       " 'HEART1',\n",
       " 'ESTRNJ1',\n",
       " 'ACOATW1',\n",
       " 'ANGINA',\n",
       " 'ONEOVAR',\n",
       " 'OTHME13',\n",
       " 'BCPTWI1',\n",
       " 'OTHRTW5',\n",
       " 'ENJOY',\n",
       " 'SERIPRO',\n",
       " 'HBCHOLE',\n",
       " 'HOURSPA',\n",
       " 'OTHMED1',\n",
       " 'AGE',\n",
       " 'FEARFULA',\n",
       " 'HPSCMOD',\n",
       " 'NOREASO',\n",
       " 'BRSTFEE',\n",
       " 'OTHTW10',\n",
       " 'SELFVIO',\n",
       " 'INCOME',\n",
       " 'GOOD',\n",
       " 'RELATEN',\n",
       " 'OTHRTW8',\n",
       " 'NRVOUS',\n",
       " 'BCP1',\n",
       " 'FEARFUL',\n",
       " 'QLTYLIF',\n",
       " 'SLEPTW2',\n",
       " 'WORKLOA',\n",
       " 'LONELY',\n",
       " 'DIURET1',\n",
       " 'EFFORT',\n",
       " 'ACOATW2',\n",
       " 'TAMOXIF',\n",
       " 'SYSBP2',\n",
       " 'BMI',\n",
       " 'STARTNE',\n",
       " 'HORMOTH',\n",
       " 'SAD',\n",
       " 'PAINTW2',\n",
       " 'FRNADVI',\n",
       " 'QUITJOB',\n",
       " 'CHEMOTH',\n",
       " 'ANTICO1',\n",
       " 'INTDAY',\n",
       " 'ANTICO2',\n",
       " 'COMBTW1',\n",
       " 'ESTRTW2',\n",
       " 'YOUNGLK',\n",
       " 'PROGTW1',\n",
       " 'CONTROL',\n",
       " 'OTHME11',\n",
       " 'DISLIKE',\n",
       " 'MARITAL',\n",
       " 'DANDC',\n",
       " 'ALCHL24',\n",
       " 'OTHME12',\n",
       " 'FERTIL2',\n",
       " 'LANGINT',\n",
       " 'RESTLES',\n",
       " 'PROGDA1',\n",
       " 'THYRTW1',\n",
       " 'TRBLSLE',\n",
       " 'OTHTW11',\n",
       " 'CANCER',\n",
       " 'DIURET2',\n",
       " 'FSH',\n",
       " 'HTMETHO',\n",
       " 'OTHMED8',\n",
       " 'BLD3MON',\n",
       " 'SPBMDT',\n",
       " 'HAPPEN3',\n",
       " 'MIGRAIN',\n",
       " 'T',\n",
       " 'SLEEP2',\n",
       " 'PROGTW2',\n",
       " 'SMOKERE',\n",
       " 'DIABP2',\n",
       " 'FRTLTW2',\n",
       " 'HRMDAY',\n",
       " 'CHOLST2',\n",
       " 'OTHRTW7',\n",
       " 'STOPOTH',\n",
       " 'OTHRTW4',\n",
       " 'PHYDAY',\n",
       " 'BONES2',\n",
       " 'PAIN2',\n",
       " 'HEARTAT',\n",
       " 'YOURWAY',\n",
       " 'PROGDA2',\n",
       " 'SIDEEFF',\n",
       " 'AVCIGDA',\n",
       " 'OSTEOPO',\n",
       " 'FERTIL1',\n",
       " 'BP2',\n",
       " 'ESTRDA1',\n",
       " 'DESCPER',\n",
       " 'ACHES',\n",
       " 'JOB',\n",
       " 'STEROI2',\n",
       " 'ESTROG1',\n",
       " 'ESTRTW1',\n",
       " 'SHBG',\n",
       " 'ANEMIA',\n",
       " 'IRRITAB',\n",
       " 'CHOLTW1',\n",
       " 'HEIGHT',\n",
       " 'IMPRMEM',\n",
       " 'STERTW2',\n",
       " 'OTHRTW2',\n",
       " 'PRBBLEE',\n",
       " 'FEELBLU',\n",
       " 'ESTROG2',\n",
       " 'THYROI1',\n",
       " 'MOODCHG',\n",
       " 'BPTW2',\n",
       " 'DIABETE',\n",
       " 'WAKEUP',\n",
       " 'WEIGHT',\n",
       " 'HYSTDAY',\n",
       " 'BLEEDNG',\n",
       " 'STROKE',\n",
       " 'STIFF',\n",
       " 'KEEPMIN',\n",
       " 'BLUES',\n",
       " 'CHOLST1',\n",
       " 'OTCTW2',\n",
       " 'CANCERS',\n",
       " 'OSTEOPR',\n",
       " 'OTCTW1',\n",
       " 'HIP',\n",
       " 'HOTFLAS',\n",
       " 'BONES1',\n",
       " 'STATUS',\n",
       " 'STEROI1',\n",
       " 'FORGET',\n",
       " 'HOSPSTA',\n",
       " 'INSUTW1',\n",
       " 'PAIN1',\n",
       " 'BLDRWAT',\n",
       " 'LIKEFEL',\n",
       " 'BCP2',\n",
       " 'LMPDAY',\n",
       " 'PRTUNEM',\n",
       " 'COMBIN2',\n",
       " 'PRGNANT',\n",
       " 'OTCTW4',\n",
       " 'HIPMEAS',\n",
       " 'SPSCTIM',\n",
       " 'OTHRTW9',\n",
       " 'SWANID',\n",
       " 'FAILURE',\n",
       " 'UTERPRO',\n",
       " 'FAMLVIO',\n",
       " 'OTC2']"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "variable_inter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Having variables shared across all the dataframes, we can now filter them out by\n",
    "# our list of selected variables\n",
    "\n",
    "selected_shared_variables = (set(variables) & set(variable_inter)) |\\\n",
    "    set(\n",
    "        [   # 2023-12-02: Added the manually selected variables.\n",
    "            'VISIT', # Will not be part of the training, but could be necessary\n",
    "            'STATUS',\n",
    "            'WEIGHT',\n",
    "            'HEIGHT',\n",
    "            'EXERCIS',\n",
    "            'DIETNUT',\n",
    "            'EMOCTDW',\n",
    "            'EMOCARE',\n",
    "            'SOCIAL',\n",
    "            'CANCERS',\n",
    "            'TRBLSLE',\n",
    "            'HAPPY',\n",
    "            'SMOKERE',\n",
    "            'MENODEP',\n",
    "            'NUMHOTF'\n",
    "        ]\n",
    "    )\n",
    "len(selected_shared_variables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ADVISTO',\n",
       " 'AGE',\n",
       " 'ALCHL24',\n",
       " 'AVCIGDA',\n",
       " 'BONES1',\n",
       " 'BONES2',\n",
       " 'BONES3',\n",
       " 'BROKEBO',\n",
       " 'CANCER',\n",
       " 'CANCERS',\n",
       " 'COMBIN1',\n",
       " 'COMBIN2',\n",
       " 'DIABETE',\n",
       " 'DIETNUT',\n",
       " 'DNTKNOW',\n",
       " 'DONTKNO',\n",
       " 'E2AVE',\n",
       " 'EMOCARE',\n",
       " 'EMOCTDW',\n",
       " 'ESTRDA1',\n",
       " 'ESTRDA2',\n",
       " 'ESTRNJ1',\n",
       " 'ESTRNJ2',\n",
       " 'ESTROG1',\n",
       " 'ESTROG2',\n",
       " 'EXERCIS',\n",
       " 'EXPENSI',\n",
       " 'FRNADVI',\n",
       " 'HAPPY',\n",
       " 'HAVEPER',\n",
       " 'HCPADVI',\n",
       " 'HEIGHT',\n",
       " 'HORMOTH',\n",
       " 'HOTFLAS',\n",
       " 'HOURSPA',\n",
       " 'IMPRMEM',\n",
       " 'LANGINT',\n",
       " 'LANGSAA',\n",
       " 'LIKEFEL',\n",
       " 'MENODEP',\n",
       " 'MENOSYM',\n",
       " 'NOLIKE',\n",
       " 'NOREASO',\n",
       " 'NOREMEB',\n",
       " 'NUMHOTF',\n",
       " 'OSTEOPO',\n",
       " 'OSTEOPR',\n",
       " 'OUTCOME',\n",
       " 'PHYSILL',\n",
       " 'PRBBLEE',\n",
       " 'PRGNANT',\n",
       " 'RACE',\n",
       " 'REDUHAR',\n",
       " 'REGPERI',\n",
       " 'SIDEEFF',\n",
       " 'SMOKERE',\n",
       " 'SOCIAL',\n",
       " 'STATUS',\n",
       " 'STOPOTH',\n",
       " 'TRBLSLE',\n",
       " 'VAGINDR',\n",
       " 'VISIT',\n",
       " 'WEIGHT',\n",
       " 'YOUNGLK'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selected_shared_variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📝 Renaming columns and final dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import DataFrame\n",
    "\n",
    "\n",
    "def rename_df_columns_to_normalized(df: DataFrame, selected_variables: set, suffix: str):\n",
    "\n",
    "    normalized_columns = normalize_columns(df, suffix)\n",
    "\n",
    "    # Check each column name in each dataframe, and see if it's in the\n",
    "    # list of selected variables\n",
    "    for col, normal_col in zip(df.columns, normalized_columns):\n",
    "\n",
    "        columns_to_rename = {}\n",
    "\n",
    "        if normal_col in selected_variables:\n",
    "            columns_to_rename[col] = normal_col\n",
    "\n",
    "        # Substitute the column name with the normalized one\n",
    "        df.rename(columns=columns_to_rename, inplace=True)\n",
    "\n",
    "\n",
    "def add_missing_columns(df: DataFrame, selected_variables: set):\n",
    "\n",
    "    # Check each column name in each dataframe, and see if it's in the\n",
    "    # list of selected variables\n",
    "    for col in selected_variables:\n",
    "\n",
    "        if col not in df.columns:\n",
    "            df[col] = None\n",
    "\n",
    "for index, df in enumerate(visit_dfs):\n",
    "\n",
    "    index_str = str(index + 1)\n",
    "    rename_df_columns_to_normalized(df, selected_shared_variables, index_str)\n",
    "    add_missing_columns(df, selected_shared_variables)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>HAVEPER</th>\n",
       "      <th>EXPENSI</th>\n",
       "      <th>BROKEBO</th>\n",
       "      <th>AGE</th>\n",
       "      <th>EXERCIS</th>\n",
       "      <th>DNTKNOW</th>\n",
       "      <th>AVCIGDA</th>\n",
       "      <th>NOREASO</th>\n",
       "      <th>SIDEEFF</th>\n",
       "      <th>OSTEOPO</th>\n",
       "      <th>...</th>\n",
       "      <th>DIETNUT</th>\n",
       "      <th>LIKEFEL</th>\n",
       "      <th>COMBIN2</th>\n",
       "      <th>BONES3</th>\n",
       "      <th>PRGNANT</th>\n",
       "      <th>SMOKERE</th>\n",
       "      <th>ESTRNJ1</th>\n",
       "      <th>STOPOTH</th>\n",
       "      <th>BONES2</th>\n",
       "      <th>HOURSPA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>46</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  HAVEPER EXPENSI BROKEBO AGE EXERCIS DNTKNOW AVCIGDA NOREASO SIDEEFF OSTEOPO  \\\n",
       "0      -1      -1       0  53    None      -1      -1      -1      -1       1   \n",
       "1      -1      -1       0  52    None      -1      -1      -1      -1      -1   \n",
       "2      -1      -1       1  46    None      -1      -1      -1      -1      -1   \n",
       "3      -1      -1       0  50    None      -1      -1      -1      -1      -1   \n",
       "4      -1      -1       0  52    None      -1      -1      -1      -1      -1   \n",
       "\n",
       "   ... DIETNUT LIKEFEL COMBIN2 BONES3 PRGNANT SMOKERE ESTRNJ1 STOPOTH  BONES2  \\\n",
       "0  ...    None      -1             -1       1       1       1      -1      -1   \n",
       "1  ...    None      -1             -1       1       1       1      -1      -1   \n",
       "2  ...    None      -1             -1       1       1       1      -1      -1   \n",
       "3  ...    None      -1             -1       1       1       1      -1      -1   \n",
       "4  ...    None      -1             -1       1       1       1      -1      -1   \n",
       "\n",
       "  HOURSPA  \n",
       "0      -1  \n",
       "1      -1  \n",
       "2      -1  \n",
       "3       4  \n",
       "4      -1  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the columns have been renamed correctly\n",
    "visit_dfs[0][list(selected_shared_variables)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>HAVEPER</th>\n",
       "      <th>EXPENSI</th>\n",
       "      <th>BROKEBO</th>\n",
       "      <th>AGE</th>\n",
       "      <th>EXERCIS</th>\n",
       "      <th>DNTKNOW</th>\n",
       "      <th>AVCIGDA</th>\n",
       "      <th>NOREASO</th>\n",
       "      <th>SIDEEFF</th>\n",
       "      <th>OSTEOPO</th>\n",
       "      <th>...</th>\n",
       "      <th>DIETNUT</th>\n",
       "      <th>LIKEFEL</th>\n",
       "      <th>COMBIN2</th>\n",
       "      <th>BONES3</th>\n",
       "      <th>PRGNANT</th>\n",
       "      <th>SMOKERE</th>\n",
       "      <th>ESTRNJ1</th>\n",
       "      <th>STOPOTH</th>\n",
       "      <th>BONES2</th>\n",
       "      <th>HOURSPA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4</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>2743</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2744</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2745</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2746</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>49</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2748 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     HAVEPER EXPENSI BROKEBO AGE EXERCIS DNTKNOW AVCIGDA NOREASO SIDEEFF  \\\n",
       "0                          0  54    None              -1                   \n",
       "1                          0  53    None              -1                   \n",
       "2                          0  50    None              -1                   \n",
       "3                          0  53    None              -1                   \n",
       "4                          0  48    None              -1                   \n",
       "...      ...     ...     ...  ..     ...     ...     ...     ...     ...   \n",
       "2743                       0  45    None              -1                   \n",
       "2744                       0  50    None              20                   \n",
       "2745                       0  47    None              -1                   \n",
       "2746                       0  49    None              -1                   \n",
       "2747                       0  45    None              -1                   \n",
       "\n",
       "     OSTEOPO  ... DIETNUT LIKEFEL COMBIN2 BONES3 PRGNANT SMOKERE ESTRNJ1  \\\n",
       "0          1  ...    None                              1       1       1   \n",
       "1             ...    None                              1       1       1   \n",
       "2             ...    None                              1       1       1   \n",
       "3             ...    None                              1       1       1   \n",
       "4             ...    None                              1       1       1   \n",
       "...      ...  ...     ...     ...     ...    ...     ...     ...     ...   \n",
       "2743       1  ...    None                              1       1       1   \n",
       "2744          ...    None                              1       2       1   \n",
       "2745          ...    None                              1       1       1   \n",
       "2746          ...    None                              1       1       1   \n",
       "2747          ...    None                              1       1       1   \n",
       "\n",
       "     STOPOTH  BONES2 HOURSPA  \n",
       "0                          4  \n",
       "1                             \n",
       "2                             \n",
       "3                          1  \n",
       "4                          4  \n",
       "...      ...     ...     ...  \n",
       "2743                          \n",
       "2744                       4  \n",
       "2745                          \n",
       "2746                          \n",
       "2747                          \n",
       "\n",
       "[2748 rows x 64 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visit_dfs[1][list(selected_shared_variables)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1', ' ', '2'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Explore unique values in the column.\n",
    "visit_dfs[1]['OSTEOPO'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ✨ Encoding empty values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Visit 1 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2881 entries, 0 to 2880\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2864 non-null   object\n",
      " 1   EXPENSI  2864 non-null   object\n",
      " 2   BROKEBO  2864 non-null   object\n",
      " 3   AGE      2872 non-null   object\n",
      " 4   EXERCIS  0 non-null      object\n",
      " 5   DNTKNOW  2864 non-null   object\n",
      " 6   AVCIGDA  2863 non-null   object\n",
      " 7   NOREASO  2864 non-null   object\n",
      " 8   SIDEEFF  2864 non-null   object\n",
      " 9   OSTEOPO  2867 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2867 non-null   object\n",
      " 12  REGPERI  2867 non-null   object\n",
      " 13  ESTRNJ2  14 non-null     object\n",
      " 14  ESTROG1  2865 non-null   object\n",
      " 15  ESTRDA2  2867 non-null   object\n",
      " 16  SOCIAL   2862 non-null   object\n",
      " 17  PHYSILL  2862 non-null   object\n",
      " 18  RACE     2881 non-null   int64 \n",
      " 19  COMBIN1  2866 non-null   object\n",
      " 20  REDUHAR  2867 non-null   object\n",
      " 21  OUTCOME  11 non-null     object\n",
      " 22  IMPRMEM  2867 non-null   object\n",
      " 23  HEIGHT   2777 non-null   object\n",
      " 24  LANGSAA  2863 non-null   object\n",
      " 25  PRBBLEE  2864 non-null   object\n",
      " 26  ESTROG2  98 non-null     object\n",
      " 27  VAGINDR  2861 non-null   object\n",
      " 28  EMOCTDW  2863 non-null   object\n",
      " 29  EMOCARE  2863 non-null   object\n",
      " 30  HCPADVI  2867 non-null   object\n",
      " 31  HORMOTH  2866 non-null   object\n",
      " 32  DIABETE  2864 non-null   object\n",
      " 33  FRNADVI  2867 non-null   object\n",
      " 34  E2AVE    2752 non-null   object\n",
      " 35  NUMHOTF  0 non-null      object\n",
      " 36  WEIGHT   2791 non-null   object\n",
      " 37  YOUNGLK  2867 non-null   object\n",
      " 38  NOREMEB  2864 non-null   object\n",
      " 39  HAPPY    2864 non-null   object\n",
      " 40  ALCHL24  2715 non-null   object\n",
      " 41  OSTEOPR  2864 non-null   object\n",
      " 42  CANCERS  2864 non-null   object\n",
      " 43  LANGINT  2867 non-null   object\n",
      " 44  HOTFLAS  2862 non-null   object\n",
      " 45  NOLIKE   2864 non-null   object\n",
      " 46  BONES1   2864 non-null   object\n",
      " 47  ADVISTO  2864 non-null   object\n",
      " 48  CANCER   2864 non-null   object\n",
      " 49  TRBLSLE  2863 non-null   object\n",
      " 50  DONTKNO  2867 non-null   object\n",
      " 51  MENOSYM  2867 non-null   object\n",
      " 52  STATUS   2859 non-null   object\n",
      " 53  VISIT    2881 non-null   int64 \n",
      " 54  DIETNUT  0 non-null      object\n",
      " 55  LIKEFEL  2864 non-null   object\n",
      " 56  COMBIN2  48 non-null     object\n",
      " 57  BONES3   2864 non-null   object\n",
      " 58  PRGNANT  2863 non-null   object\n",
      " 59  SMOKERE  2862 non-null   object\n",
      " 60  ESTRNJ1  2866 non-null   object\n",
      " 61  STOPOTH  2864 non-null   object\n",
      " 62  BONES2   2864 non-null   object\n",
      " 63  HOURSPA  2864 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.4+ MB\n",
      "------------------------------------\n",
      "Visit 2 information:\n",
      "\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2748 entries, 0 to 2747\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  94 non-null     object\n",
      " 1   EXPENSI  94 non-null     object\n",
      " 2   BROKEBO  2724 non-null   object\n",
      " 3   AGE      2739 non-null   object\n",
      " 4   EXERCIS  0 non-null      object\n",
      " 5   DNTKNOW  94 non-null     object\n",
      " 6   AVCIGDA  2698 non-null   object\n",
      " 7   NOREASO  94 non-null     object\n",
      " 8   SIDEEFF  94 non-null     object\n",
      " 9   OSTEOPO  348 non-null    object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  145 non-null    object\n",
      " 12  REGPERI  348 non-null    object\n",
      " 13  ESTRNJ2  21 non-null     object\n",
      " 14  ESTROG1  2724 non-null   object\n",
      " 15  ESTRDA2  10 non-null     object\n",
      " 16  SOCIAL   2698 non-null   object\n",
      " 17  PHYSILL  2698 non-null   object\n",
      " 18  RACE     2748 non-null   int64 \n",
      " 19  COMBIN1  2725 non-null   object\n",
      " 20  REDUHAR  348 non-null    object\n",
      " 21  OUTCOME  8 non-null      object\n",
      " 22  IMPRMEM  348 non-null    object\n",
      " 23  HEIGHT   2627 non-null   object\n",
      " 24  LANGSAA  2698 non-null   object\n",
      " 25  PRBBLEE  95 non-null     object\n",
      " 26  ESTROG2  149 non-null    object\n",
      " 27  VAGINDR  2698 non-null   object\n",
      " 28  EMOCTDW  2698 non-null   object\n",
      " 29  EMOCARE  2698 non-null   object\n",
      " 30  HCPADVI  348 non-null    object\n",
      " 31  HORMOTH  348 non-null    object\n",
      " 32  DIABETE  2724 non-null   object\n",
      " 33  FRNADVI  348 non-null    object\n",
      " 34  E2AVE    2509 non-null   object\n",
      " 35  NUMHOTF  0 non-null      object\n",
      " 36  WEIGHT   2632 non-null   object\n",
      " 37  YOUNGLK  348 non-null    object\n",
      " 38  NOREMEB  94 non-null     object\n",
      " 39  HAPPY    2723 non-null   object\n",
      " 40  ALCHL24  2485 non-null   object\n",
      " 41  OSTEOPR  2724 non-null   object\n",
      " 42  CANCERS  2723 non-null   object\n",
      " 43  LANGINT  2725 non-null   object\n",
      " 44  HOTFLAS  2698 non-null   object\n",
      " 45  NOLIKE   94 non-null     object\n",
      " 46  BONES1   48 non-null     object\n",
      " 47  ADVISTO  94 non-null     object\n",
      " 48  CANCER   94 non-null     object\n",
      " 49  TRBLSLE  2698 non-null   object\n",
      " 50  DONTKNO  344 non-null    object\n",
      " 51  MENOSYM  348 non-null    object\n",
      " 52  STATUS   2725 non-null   object\n",
      " 53  VISIT    2748 non-null   int64 \n",
      " 54  DIETNUT  0 non-null      object\n",
      " 55  LIKEFEL  94 non-null     object\n",
      " 56  COMBIN2  95 non-null     object\n",
      " 57  BONES3   2 non-null      object\n",
      " 58  PRGNANT  2723 non-null   object\n",
      " 59  SMOKERE  2698 non-null   object\n",
      " 60  ESTRNJ1  2725 non-null   object\n",
      " 61  STOPOTH  94 non-null     object\n",
      " 62  BONES2   7 non-null      object\n",
      " 63  HOURSPA  840 non-null    object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.3+ MB\n",
      "------------------------------------\n",
      "Visit 3 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2709 entries, 0 to 2708\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2694 non-null   object\n",
      " 1   EXPENSI  2694 non-null   object\n",
      " 2   BROKEBO  2694 non-null   object\n",
      " 3   AGE      2702 non-null   object\n",
      " 4   EXERCIS  0 non-null      object\n",
      " 5   DNTKNOW  2694 non-null   object\n",
      " 6   AVCIGDA  2615 non-null   object\n",
      " 7   NOREASO  2694 non-null   object\n",
      " 8   SIDEEFF  2694 non-null   object\n",
      " 9   OSTEOPO  2694 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2694 non-null   object\n",
      " 12  REGPERI  2694 non-null   object\n",
      " 13  ESTRNJ2  2694 non-null   object\n",
      " 14  ESTROG1  2694 non-null   object\n",
      " 15  ESTRDA2  2694 non-null   object\n",
      " 16  SOCIAL   2615 non-null   object\n",
      " 17  PHYSILL  2615 non-null   object\n",
      " 18  RACE     2709 non-null   int64 \n",
      " 19  COMBIN1  2694 non-null   object\n",
      " 20  REDUHAR  2694 non-null   object\n",
      " 21  OUTCOME  2694 non-null   object\n",
      " 22  IMPRMEM  2694 non-null   object\n",
      " 23  HEIGHT   2524 non-null   object\n",
      " 24  LANGSAA  2615 non-null   object\n",
      " 25  PRBBLEE  2694 non-null   object\n",
      " 26  ESTROG2  2694 non-null   object\n",
      " 27  VAGINDR  2615 non-null   object\n",
      " 28  EMOCTDW  2615 non-null   object\n",
      " 29  EMOCARE  2615 non-null   object\n",
      " 30  HCPADVI  2694 non-null   object\n",
      " 31  HORMOTH  2694 non-null   object\n",
      " 32  DIABETE  2694 non-null   object\n",
      " 33  FRNADVI  2694 non-null   object\n",
      " 34  E2AVE    2341 non-null   object\n",
      " 35  NUMHOTF  0 non-null      object\n",
      " 36  WEIGHT   2521 non-null   object\n",
      " 37  YOUNGLK  2694 non-null   object\n",
      " 38  NOREMEB  2694 non-null   object\n",
      " 39  HAPPY    2694 non-null   object\n",
      " 40  ALCHL24  2330 non-null   object\n",
      " 41  OSTEOPR  2694 non-null   object\n",
      " 42  CANCERS  2694 non-null   object\n",
      " 43  LANGINT  2694 non-null   object\n",
      " 44  HOTFLAS  2615 non-null   object\n",
      " 45  NOLIKE   2694 non-null   object\n",
      " 46  BONES1   2694 non-null   object\n",
      " 47  ADVISTO  2694 non-null   object\n",
      " 48  CANCER   2694 non-null   object\n",
      " 49  TRBLSLE  2615 non-null   object\n",
      " 50  DONTKNO  2694 non-null   object\n",
      " 51  MENOSYM  2694 non-null   object\n",
      " 52  STATUS   2693 non-null   object\n",
      " 53  VISIT    2709 non-null   int64 \n",
      " 54  DIETNUT  0 non-null      object\n",
      " 55  LIKEFEL  2694 non-null   object\n",
      " 56  COMBIN2  2694 non-null   object\n",
      " 57  BONES3   2694 non-null   object\n",
      " 58  PRGNANT  2694 non-null   object\n",
      " 59  SMOKERE  2615 non-null   object\n",
      " 60  ESTRNJ1  2694 non-null   object\n",
      " 61  STOPOTH  2694 non-null   object\n",
      " 62  BONES2   2694 non-null   object\n",
      " 63  HOURSPA  2694 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.3+ MB\n",
      "------------------------------------\n",
      "Visit 4 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2679 entries, 0 to 2678\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2658 non-null   object\n",
      " 1   EXPENSI  2658 non-null   object\n",
      " 2   BROKEBO  2658 non-null   object\n",
      " 3   AGE      2671 non-null   object\n",
      " 4   EXERCIS  0 non-null      object\n",
      " 5   DNTKNOW  2658 non-null   object\n",
      " 6   AVCIGDA  2566 non-null   object\n",
      " 7   NOREASO  2658 non-null   object\n",
      " 8   SIDEEFF  2658 non-null   object\n",
      " 9   OSTEOPO  2658 non-null   object\n",
      " 10  MENODEP  2566 non-null   object\n",
      " 11  ESTRDA1  2658 non-null   object\n",
      " 12  REGPERI  2658 non-null   object\n",
      " 13  ESTRNJ2  2658 non-null   object\n",
      " 14  ESTROG1  2658 non-null   object\n",
      " 15  ESTRDA2  2658 non-null   object\n",
      " 16  SOCIAL   0 non-null      object\n",
      " 17  PHYSILL  2566 non-null   object\n",
      " 18  RACE     2679 non-null   int64 \n",
      " 19  COMBIN1  2658 non-null   object\n",
      " 20  REDUHAR  2658 non-null   object\n",
      " 21  OUTCOME  2658 non-null   object\n",
      " 22  IMPRMEM  2658 non-null   object\n",
      " 23  HEIGHT   2459 non-null   object\n",
      " 24  LANGSAA  2566 non-null   object\n",
      " 25  PRBBLEE  2658 non-null   object\n",
      " 26  ESTROG2  2658 non-null   object\n",
      " 27  VAGINDR  2566 non-null   object\n",
      " 28  EMOCTDW  0 non-null      object\n",
      " 29  EMOCARE  0 non-null      object\n",
      " 30  HCPADVI  2658 non-null   object\n",
      " 31  HORMOTH  2658 non-null   object\n",
      " 32  DIABETE  2658 non-null   object\n",
      " 33  FRNADVI  2658 non-null   object\n",
      " 34  E2AVE    2265 non-null   object\n",
      " 35  NUMHOTF  0 non-null      object\n",
      " 36  WEIGHT   2489 non-null   object\n",
      " 37  YOUNGLK  2658 non-null   object\n",
      " 38  NOREMEB  2658 non-null   object\n",
      " 39  HAPPY    2658 non-null   object\n",
      " 40  ALCHL24  2282 non-null   object\n",
      " 41  OSTEOPR  2658 non-null   object\n",
      " 42  CANCERS  2658 non-null   object\n",
      " 43  LANGINT  2658 non-null   object\n",
      " 44  HOTFLAS  2566 non-null   object\n",
      " 45  NOLIKE   2658 non-null   object\n",
      " 46  BONES1   2658 non-null   object\n",
      " 47  ADVISTO  2658 non-null   object\n",
      " 48  CANCER   2658 non-null   object\n",
      " 49  TRBLSLE  2566 non-null   object\n",
      " 50  DONTKNO  2658 non-null   object\n",
      " 51  MENOSYM  2658 non-null   object\n",
      " 52  STATUS   2658 non-null   object\n",
      " 53  VISIT    2679 non-null   int64 \n",
      " 54  DIETNUT  0 non-null      object\n",
      " 55  LIKEFEL  2658 non-null   object\n",
      " 56  COMBIN2  2658 non-null   object\n",
      " 57  BONES3   2658 non-null   object\n",
      " 58  PRGNANT  2658 non-null   object\n",
      " 59  SMOKERE  2566 non-null   object\n",
      " 60  ESTRNJ1  2658 non-null   object\n",
      " 61  STOPOTH  2658 non-null   object\n",
      " 62  BONES2   2658 non-null   object\n",
      " 63  HOURSPA  2658 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.3+ MB\n",
      "------------------------------------\n",
      "Visit 5 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2617 entries, 0 to 2616\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2604 non-null   object\n",
      " 1   EXPENSI  2604 non-null   object\n",
      " 2   BROKEBO  2604 non-null   object\n",
      " 3   AGE      2617 non-null   int64 \n",
      " 4   EXERCIS  0 non-null      object\n",
      " 5   DNTKNOW  2604 non-null   object\n",
      " 6   AVCIGDA  2496 non-null   object\n",
      " 7   NOREASO  2604 non-null   object\n",
      " 8   SIDEEFF  2604 non-null   object\n",
      " 9   OSTEOPO  2604 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2604 non-null   object\n",
      " 12  REGPERI  2604 non-null   object\n",
      " 13  ESTRNJ2  2604 non-null   object\n",
      " 14  ESTROG1  2604 non-null   object\n",
      " 15  ESTRDA2  2604 non-null   object\n",
      " 16  SOCIAL   0 non-null      object\n",
      " 17  PHYSILL  2496 non-null   object\n",
      " 18  RACE     2617 non-null   int64 \n",
      " 19  COMBIN1  2604 non-null   object\n",
      " 20  REDUHAR  2604 non-null   object\n",
      " 21  OUTCOME  2604 non-null   object\n",
      " 22  IMPRMEM  2604 non-null   object\n",
      " 23  HEIGHT   2292 non-null   object\n",
      " 24  LANGSAA  2496 non-null   object\n",
      " 25  PRBBLEE  2604 non-null   object\n",
      " 26  ESTROG2  2604 non-null   object\n",
      " 27  VAGINDR  2496 non-null   object\n",
      " 28  EMOCTDW  0 non-null      object\n",
      " 29  EMOCARE  0 non-null      object\n",
      " 30  HCPADVI  2604 non-null   object\n",
      " 31  HORMOTH  2604 non-null   object\n",
      " 32  DIABETE  2604 non-null   object\n",
      " 33  FRNADVI  2604 non-null   object\n",
      " 34  E2AVE    2097 non-null   object\n",
      " 35  NUMHOTF  0 non-null      object\n",
      " 36  WEIGHT   2398 non-null   object\n",
      " 37  YOUNGLK  2604 non-null   object\n",
      " 38  NOREMEB  2604 non-null   object\n",
      " 39  HAPPY    2604 non-null   object\n",
      " 40  ALCHL24  2163 non-null   object\n",
      " 41  OSTEOPR  2604 non-null   object\n",
      " 42  CANCERS  2604 non-null   object\n",
      " 43  LANGINT  2604 non-null   object\n",
      " 44  HOTFLAS  2496 non-null   object\n",
      " 45  NOLIKE   2604 non-null   object\n",
      " 46  BONES1   2604 non-null   object\n",
      " 47  ADVISTO  2604 non-null   object\n",
      " 48  CANCER   2604 non-null   object\n",
      " 49  TRBLSLE  2496 non-null   object\n",
      " 50  DONTKNO  2604 non-null   object\n",
      " 51  MENOSYM  2604 non-null   object\n",
      " 52  STATUS   2603 non-null   object\n",
      " 53  VISIT    2617 non-null   int64 \n",
      " 54  DIETNUT  0 non-null      object\n",
      " 55  LIKEFEL  2604 non-null   object\n",
      " 56  COMBIN2  2604 non-null   object\n",
      " 57  BONES3   2604 non-null   object\n",
      " 58  PRGNANT  2604 non-null   object\n",
      " 59  SMOKERE  2496 non-null   object\n",
      " 60  ESTRNJ1  2604 non-null   object\n",
      " 61  STOPOTH  2604 non-null   object\n",
      " 62  BONES2   2604 non-null   object\n",
      " 63  HOURSPA  2604 non-null   object\n",
      "dtypes: int64(3), object(61)\n",
      "memory usage: 1.3+ MB\n",
      "------------------------------------\n",
      "Visit 6 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2448 entries, 0 to 2447\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2441 non-null   object\n",
      " 1   EXPENSI  2441 non-null   object\n",
      " 2   BROKEBO  2441 non-null   object\n",
      " 3   AGE      2447 non-null   object\n",
      " 4   EXERCIS  2172 non-null   object\n",
      " 5   DNTKNOW  2441 non-null   object\n",
      " 6   AVCIGDA  2332 non-null   object\n",
      " 7   NOREASO  2441 non-null   object\n",
      " 8   SIDEEFF  2441 non-null   object\n",
      " 9   OSTEOPO  2441 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2441 non-null   object\n",
      " 12  REGPERI  2441 non-null   object\n",
      " 13  ESTRNJ2  2441 non-null   object\n",
      " 14  ESTROG1  2441 non-null   object\n",
      " 15  ESTRDA2  2441 non-null   object\n",
      " 16  SOCIAL   2201 non-null   object\n",
      " 17  PHYSILL  2332 non-null   object\n",
      " 18  RACE     2448 non-null   int64 \n",
      " 19  COMBIN1  2441 non-null   object\n",
      " 20  REDUHAR  2441 non-null   object\n",
      " 21  OUTCOME  2441 non-null   object\n",
      " 22  IMPRMEM  2441 non-null   object\n",
      " 23  HEIGHT   2150 non-null   object\n",
      " 24  LANGSAA  2332 non-null   object\n",
      " 25  PRBBLEE  2441 non-null   object\n",
      " 26  ESTROG2  2441 non-null   object\n",
      " 27  VAGINDR  2332 non-null   object\n",
      " 28  EMOCTDW  2201 non-null   object\n",
      " 29  EMOCARE  2201 non-null   object\n",
      " 30  HCPADVI  2441 non-null   object\n",
      " 31  HORMOTH  2441 non-null   object\n",
      " 32  DIABETE  2441 non-null   object\n",
      " 33  FRNADVI  2441 non-null   object\n",
      " 34  E2AVE    2054 non-null   object\n",
      " 35  NUMHOTF  0 non-null      object\n",
      " 36  WEIGHT   2295 non-null   object\n",
      " 37  YOUNGLK  2441 non-null   object\n",
      " 38  NOREMEB  2441 non-null   object\n",
      " 39  HAPPY    2441 non-null   object\n",
      " 40  ALCHL24  2067 non-null   object\n",
      " 41  OSTEOPR  2441 non-null   object\n",
      " 42  CANCERS  2441 non-null   object\n",
      " 43  LANGINT  2441 non-null   object\n",
      " 44  HOTFLAS  2332 non-null   object\n",
      " 45  NOLIKE   2441 non-null   object\n",
      " 46  BONES1   2441 non-null   object\n",
      " 47  ADVISTO  2441 non-null   object\n",
      " 48  CANCER   2441 non-null   object\n",
      " 49  TRBLSLE  2332 non-null   object\n",
      " 50  DONTKNO  2441 non-null   object\n",
      " 51  MENOSYM  2441 non-null   object\n",
      " 52  STATUS   2441 non-null   object\n",
      " 53  VISIT    2448 non-null   int64 \n",
      " 54  DIETNUT  2172 non-null   object\n",
      " 55  LIKEFEL  2441 non-null   object\n",
      " 56  COMBIN2  2441 non-null   object\n",
      " 57  BONES3   2441 non-null   object\n",
      " 58  PRGNANT  2441 non-null   object\n",
      " 59  SMOKERE  2332 non-null   object\n",
      " 60  ESTRNJ1  2441 non-null   object\n",
      " 61  STOPOTH  2441 non-null   object\n",
      " 62  BONES2   2441 non-null   object\n",
      " 63  HOURSPA  2441 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.2+ MB\n",
      "------------------------------------\n",
      "Visit 7 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2413 entries, 0 to 2412\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2288 non-null   object\n",
      " 1   EXPENSI  2288 non-null   object\n",
      " 2   BROKEBO  2406 non-null   object\n",
      " 3   AGE      2411 non-null   object\n",
      " 4   EXERCIS  2288 non-null   object\n",
      " 5   DNTKNOW  2288 non-null   object\n",
      " 6   AVCIGDA  2380 non-null   object\n",
      " 7   NOREASO  2288 non-null   object\n",
      " 8   SIDEEFF  2288 non-null   object\n",
      " 9   OSTEOPO  2288 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2406 non-null   object\n",
      " 12  REGPERI  2288 non-null   object\n",
      " 13  ESTRNJ2  2406 non-null   object\n",
      " 14  ESTROG1  2406 non-null   object\n",
      " 15  ESTRDA2  2406 non-null   object\n",
      " 16  SOCIAL   0 non-null      object\n",
      " 17  PHYSILL  2380 non-null   object\n",
      " 18  RACE     2413 non-null   int64 \n",
      " 19  COMBIN1  2406 non-null   object\n",
      " 20  REDUHAR  2288 non-null   object\n",
      " 21  OUTCOME  2288 non-null   object\n",
      " 22  IMPRMEM  2288 non-null   object\n",
      " 23  HEIGHT   2126 non-null   object\n",
      " 24  LANGSAA  2380 non-null   object\n",
      " 25  PRBBLEE  2288 non-null   object\n",
      " 26  ESTROG2  2406 non-null   object\n",
      " 27  VAGINDR  2380 non-null   object\n",
      " 28  EMOCTDW  0 non-null      object\n",
      " 29  EMOCARE  0 non-null      object\n",
      " 30  HCPADVI  2288 non-null   object\n",
      " 31  HORMOTH  2288 non-null   object\n",
      " 32  DIABETE  2406 non-null   object\n",
      " 33  FRNADVI  2288 non-null   object\n",
      " 34  E2AVE    2047 non-null   object\n",
      " 35  NUMHOTF  2380 non-null   object\n",
      " 36  WEIGHT   2233 non-null   object\n",
      " 37  YOUNGLK  2288 non-null   object\n",
      " 38  NOREMEB  2288 non-null   object\n",
      " 39  HAPPY    2267 non-null   object\n",
      " 40  ALCHL24  2057 non-null   object\n",
      " 41  OSTEOPR  2406 non-null   object\n",
      " 42  CANCERS  2406 non-null   object\n",
      " 43  LANGINT  2406 non-null   object\n",
      " 44  HOTFLAS  2380 non-null   object\n",
      " 45  NOLIKE   2288 non-null   object\n",
      " 46  BONES1   65 non-null     object\n",
      " 47  ADVISTO  2288 non-null   object\n",
      " 48  CANCER   2288 non-null   object\n",
      " 49  TRBLSLE  2380 non-null   object\n",
      " 50  DONTKNO  2288 non-null   object\n",
      " 51  MENOSYM  2288 non-null   object\n",
      " 52  STATUS   2406 non-null   object\n",
      " 53  VISIT    2413 non-null   int64 \n",
      " 54  DIETNUT  2288 non-null   object\n",
      " 55  LIKEFEL  2288 non-null   object\n",
      " 56  COMBIN2  2406 non-null   object\n",
      " 57  BONES3   65 non-null     object\n",
      " 58  PRGNANT  2288 non-null   object\n",
      " 59  SMOKERE  2380 non-null   object\n",
      " 60  ESTRNJ1  2406 non-null   object\n",
      " 61  STOPOTH  2288 non-null   object\n",
      " 62  BONES2   65 non-null     object\n",
      " 63  HOURSPA  2267 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.2+ MB\n",
      "------------------------------------\n",
      "Visit 8 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2278 entries, 0 to 2277\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2176 non-null   object\n",
      " 1   EXPENSI  2176 non-null   object\n",
      " 2   BROKEBO  2274 non-null   object\n",
      " 3   AGE      2276 non-null   object\n",
      " 4   EXERCIS  1593 non-null   object\n",
      " 5   DNTKNOW  1593 non-null   object\n",
      " 6   AVCIGDA  2244 non-null   object\n",
      " 7   NOREASO  1593 non-null   object\n",
      " 8   SIDEEFF  2176 non-null   object\n",
      " 9   OSTEOPO  2176 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2274 non-null   object\n",
      " 12  REGPERI  2176 non-null   object\n",
      " 13  ESTRNJ2  2274 non-null   object\n",
      " 14  ESTROG1  2274 non-null   object\n",
      " 15  ESTRDA2  1691 non-null   object\n",
      " 16  SOCIAL   2244 non-null   object\n",
      " 17  PHYSILL  2244 non-null   object\n",
      " 18  RACE     2278 non-null   int64 \n",
      " 19  COMBIN1  2274 non-null   object\n",
      " 20  REDUHAR  2176 non-null   object\n",
      " 21  OUTCOME  1593 non-null   object\n",
      " 22  IMPRMEM  2176 non-null   object\n",
      " 23  HEIGHT   1695 non-null   object\n",
      " 24  LANGSAA  2244 non-null   object\n",
      " 25  PRBBLEE  2176 non-null   object\n",
      " 26  ESTROG2  2274 non-null   object\n",
      " 27  VAGINDR  2244 non-null   object\n",
      " 28  EMOCTDW  2244 non-null   object\n",
      " 29  EMOCARE  2244 non-null   object\n",
      " 30  HCPADVI  2176 non-null   object\n",
      " 31  HORMOTH  2176 non-null   object\n",
      " 32  DIABETE  2274 non-null   object\n",
      " 33  FRNADVI  2176 non-null   object\n",
      " 34  E2AVE    1450 non-null   object\n",
      " 35  NUMHOTF  2244 non-null   object\n",
      " 36  WEIGHT   2109 non-null   object\n",
      " 37  YOUNGLK  2176 non-null   object\n",
      " 38  NOREMEB  2176 non-null   object\n",
      " 39  HAPPY    1558 non-null   object\n",
      " 40  ALCHL24  1454 non-null   object\n",
      " 41  OSTEOPR  2274 non-null   object\n",
      " 42  CANCERS  2274 non-null   object\n",
      " 43  LANGINT  2274 non-null   object\n",
      " 44  HOTFLAS  2244 non-null   object\n",
      " 45  NOLIKE   2176 non-null   object\n",
      " 46  BONES1   45 non-null     object\n",
      " 47  ADVISTO  2176 non-null   object\n",
      " 48  CANCER   2176 non-null   object\n",
      " 49  TRBLSLE  2244 non-null   object\n",
      " 50  DONTKNO  1593 non-null   object\n",
      " 51  MENOSYM  2176 non-null   object\n",
      " 52  STATUS   2268 non-null   object\n",
      " 53  VISIT    2278 non-null   int64 \n",
      " 54  DIETNUT  1593 non-null   object\n",
      " 55  LIKEFEL  2176 non-null   object\n",
      " 56  COMBIN2  2274 non-null   object\n",
      " 57  BONES3   45 non-null     object\n",
      " 58  PRGNANT  1593 non-null   object\n",
      " 59  SMOKERE  2244 non-null   object\n",
      " 60  ESTRNJ1  2274 non-null   object\n",
      " 61  STOPOTH  2176 non-null   object\n",
      " 62  BONES2   45 non-null     object\n",
      " 63  HOURSPA  1558 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.1+ MB\n",
      "------------------------------------\n",
      "Visit 9 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2469 entries, 0 to 2468\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2313 non-null   object\n",
      " 1   EXPENSI  2313 non-null   object\n",
      " 2   BROKEBO  2448 non-null   object\n",
      " 3   AGE      2467 non-null   object\n",
      " 4   EXERCIS  2080 non-null   object\n",
      " 5   DNTKNOW  2277 non-null   object\n",
      " 6   AVCIGDA  2409 non-null   object\n",
      " 7   NOREASO  2277 non-null   object\n",
      " 8   SIDEEFF  2313 non-null   object\n",
      " 9   OSTEOPO  2313 non-null   object\n",
      " 10  MENODEP  2286 non-null   object\n",
      " 11  ESTRDA1  2448 non-null   object\n",
      " 12  REGPERI  2313 non-null   object\n",
      " 13  ESTRNJ2  2448 non-null   object\n",
      " 14  ESTROG1  2460 non-null   object\n",
      " 15  ESTRDA2  2412 non-null   object\n",
      " 16  SOCIAL   0 non-null      object\n",
      " 17  PHYSILL  2409 non-null   object\n",
      " 18  RACE     2469 non-null   int64 \n",
      " 19  COMBIN1  2460 non-null   object\n",
      " 20  REDUHAR  2313 non-null   object\n",
      " 21  OUTCOME  2277 non-null   object\n",
      " 22  IMPRMEM  2313 non-null   object\n",
      " 23  HEIGHT   2111 non-null   object\n",
      " 24  LANGSAA  2409 non-null   object\n",
      " 25  PRBBLEE  2313 non-null   object\n",
      " 26  ESTROG2  2448 non-null   object\n",
      " 27  VAGINDR  2409 non-null   object\n",
      " 28  EMOCTDW  0 non-null      object\n",
      " 29  EMOCARE  0 non-null      object\n",
      " 30  HCPADVI  2313 non-null   object\n",
      " 31  HORMOTH  2313 non-null   object\n",
      " 32  DIABETE  2448 non-null   object\n",
      " 33  FRNADVI  2313 non-null   object\n",
      " 34  E2AVE    2018 non-null   object\n",
      " 35  NUMHOTF  2409 non-null   object\n",
      " 36  WEIGHT   2271 non-null   object\n",
      " 37  YOUNGLK  2313 non-null   object\n",
      " 38  NOREMEB  2313 non-null   object\n",
      " 39  HAPPY    2037 non-null   object\n",
      " 40  ALCHL24  2052 non-null   object\n",
      " 41  OSTEOPR  2448 non-null   object\n",
      " 42  CANCERS  2448 non-null   object\n",
      " 43  LANGINT  2460 non-null   object\n",
      " 44  HOTFLAS  2409 non-null   object\n",
      " 45  NOLIKE   2313 non-null   object\n",
      " 46  BONES1   59 non-null     object\n",
      " 47  ADVISTO  2313 non-null   object\n",
      " 48  CANCER   2313 non-null   object\n",
      " 49  TRBLSLE  2409 non-null   object\n",
      " 50  DONTKNO  2277 non-null   object\n",
      " 51  MENOSYM  2313 non-null   object\n",
      " 52  STATUS   2459 non-null   object\n",
      " 53  VISIT    2469 non-null   int64 \n",
      " 54  DIETNUT  2080 non-null   object\n",
      " 55  LIKEFEL  2313 non-null   object\n",
      " 56  COMBIN2  2448 non-null   object\n",
      " 57  BONES3   59 non-null     object\n",
      " 58  PRGNANT  2277 non-null   object\n",
      " 59  SMOKERE  2409 non-null   object\n",
      " 60  ESTRNJ1  2460 non-null   object\n",
      " 61  STOPOTH  2313 non-null   object\n",
      " 62  BONES2   59 non-null     object\n",
      " 63  HOURSPA  2037 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.2+ MB\n",
      "------------------------------------\n",
      "Visit 10 information:\n",
      "\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2245 entries, 0 to 2244\n",
      "Data columns (total 64 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   HAVEPER  2107 non-null   object\n",
      " 1   EXPENSI  2107 non-null   object\n",
      " 2   BROKEBO  2237 non-null   object\n",
      " 3   AGE      2244 non-null   object\n",
      " 4   EXERCIS  2107 non-null   object\n",
      " 5   DNTKNOW  2107 non-null   object\n",
      " 6   AVCIGDA  2206 non-null   object\n",
      " 7   NOREASO  2107 non-null   object\n",
      " 8   SIDEEFF  2107 non-null   object\n",
      " 9   OSTEOPO  2107 non-null   object\n",
      " 10  MENODEP  0 non-null      object\n",
      " 11  ESTRDA1  2237 non-null   object\n",
      " 12  REGPERI  2107 non-null   object\n",
      " 13  ESTRNJ2  2237 non-null   object\n",
      " 14  ESTROG1  2239 non-null   object\n",
      " 15  ESTRDA2  2237 non-null   object\n",
      " 16  SOCIAL   2206 non-null   object\n",
      " 17  PHYSILL  2206 non-null   object\n",
      " 18  RACE     2245 non-null   int64 \n",
      " 19  COMBIN1  2239 non-null   object\n",
      " 20  REDUHAR  2107 non-null   object\n",
      " 21  OUTCOME  2107 non-null   object\n",
      " 22  IMPRMEM  2107 non-null   object\n",
      " 23  HEIGHT   1966 non-null   object\n",
      " 24  LANGSAA  2206 non-null   object\n",
      " 25  PRBBLEE  2107 non-null   object\n",
      " 26  ESTROG2  2237 non-null   object\n",
      " 27  VAGINDR  2206 non-null   object\n",
      " 28  EMOCTDW  2206 non-null   object\n",
      " 29  EMOCARE  2206 non-null   object\n",
      " 30  HCPADVI  2107 non-null   object\n",
      " 31  HORMOTH  2107 non-null   object\n",
      " 32  DIABETE  2237 non-null   object\n",
      " 33  FRNADVI  2107 non-null   object\n",
      " 34  E2AVE    1907 non-null   object\n",
      " 35  NUMHOTF  2206 non-null   object\n",
      " 36  WEIGHT   2093 non-null   object\n",
      " 37  YOUNGLK  2107 non-null   object\n",
      " 38  NOREMEB  2107 non-null   object\n",
      " 39  HAPPY    2091 non-null   object\n",
      " 40  ALCHL24  1912 non-null   object\n",
      " 41  OSTEOPR  2237 non-null   object\n",
      " 42  CANCERS  2237 non-null   object\n",
      " 43  LANGINT  2239 non-null   object\n",
      " 44  HOTFLAS  2206 non-null   object\n",
      " 45  NOLIKE   2107 non-null   object\n",
      " 46  BONES1   54 non-null     object\n",
      " 47  ADVISTO  2107 non-null   object\n",
      " 48  CANCER   2107 non-null   object\n",
      " 49  TRBLSLE  2206 non-null   object\n",
      " 50  DONTKNO  2107 non-null   object\n",
      " 51  MENOSYM  2107 non-null   object\n",
      " 52  STATUS   2238 non-null   object\n",
      " 53  VISIT    2245 non-null   int64 \n",
      " 54  DIETNUT  2107 non-null   object\n",
      " 55  LIKEFEL  2107 non-null   object\n",
      " 56  COMBIN2  2237 non-null   object\n",
      " 57  BONES3   54 non-null     object\n",
      " 58  PRGNANT  2107 non-null   object\n",
      " 59  SMOKERE  2206 non-null   object\n",
      " 60  ESTRNJ1  2239 non-null   object\n",
      " 61  STOPOTH  2107 non-null   object\n",
      " 62  BONES2   54 non-null     object\n",
      " 63  HOURSPA  2091 non-null   object\n",
      "dtypes: int64(2), object(62)\n",
      "memory usage: 1.1+ MB\n",
      "------------------------------------\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    It was chosen that missing values would be represented by -9999;\n",
    "    however, values here are still strings (object, according to pandas),\n",
    "    so we need to replace them with the actual value.\n",
    "\"\"\"\n",
    "\n",
    "# Before continuing, check null values\n",
    "for index, df in enumerate(visit_dfs):\n",
    "    df.replace(' ', None, inplace=True)\n",
    "    print(f'Visit {index + 1} information:\\n\\n')\n",
    "    df[list(selected_shared_variables)].info()\n",
    "    print('------------------------------------')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in visit_dfs:\n",
    "    df.fillna(-9999, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>HAVEPER</th>\n",
       "      <th>EXPENSI</th>\n",
       "      <th>BROKEBO</th>\n",
       "      <th>AGE</th>\n",
       "      <th>EXERCIS</th>\n",
       "      <th>DNTKNOW</th>\n",
       "      <th>AVCIGDA</th>\n",
       "      <th>NOREASO</th>\n",
       "      <th>SIDEEFF</th>\n",
       "      <th>OSTEOPO</th>\n",
       "      <th>...</th>\n",
       "      <th>DIETNUT</th>\n",
       "      <th>LIKEFEL</th>\n",
       "      <th>COMBIN2</th>\n",
       "      <th>BONES3</th>\n",
       "      <th>PRGNANT</th>\n",
       "      <th>SMOKERE</th>\n",
       "      <th>ESTRNJ1</th>\n",
       "      <th>STOPOTH</th>\n",
       "      <th>BONES2</th>\n",
       "      <th>HOURSPA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  HAVEPER EXPENSI BROKEBO AGE  EXERCIS DNTKNOW AVCIGDA NOREASO SIDEEFF  \\\n",
       "0   -9999   -9999       0  54    -9999   -9999      -1   -9999   -9999   \n",
       "1   -9999   -9999       0  53    -9999   -9999      -1   -9999   -9999   \n",
       "2   -9999   -9999       0  50    -9999   -9999      -1   -9999   -9999   \n",
       "3   -9999   -9999       0  53    -9999   -9999      -1   -9999   -9999   \n",
       "4   -9999   -9999       0  48    -9999   -9999      -1   -9999   -9999   \n",
       "\n",
       "  OSTEOPO  ...  DIETNUT LIKEFEL COMBIN2 BONES3 PRGNANT SMOKERE ESTRNJ1  \\\n",
       "0       1  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "1   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "2   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "3   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "4   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "\n",
       "  STOPOTH  BONES2 HOURSPA  \n",
       "0   -9999   -9999       4  \n",
       "1   -9999   -9999   -9999  \n",
       "2   -9999   -9999   -9999  \n",
       "3   -9999   -9999       1  \n",
       "4   -9999   -9999       4  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visit_dfs[1][list(selected_shared_variables)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>HAVEPER</th>\n",
       "      <th>EXPENSI</th>\n",
       "      <th>BROKEBO</th>\n",
       "      <th>AGE</th>\n",
       "      <th>EXERCIS</th>\n",
       "      <th>DNTKNOW</th>\n",
       "      <th>AVCIGDA</th>\n",
       "      <th>NOREASO</th>\n",
       "      <th>SIDEEFF</th>\n",
       "      <th>OSTEOPO</th>\n",
       "      <th>...</th>\n",
       "      <th>DIETNUT</th>\n",
       "      <th>LIKEFEL</th>\n",
       "      <th>COMBIN2</th>\n",
       "      <th>BONES3</th>\n",
       "      <th>PRGNANT</th>\n",
       "      <th>SMOKERE</th>\n",
       "      <th>ESTRNJ1</th>\n",
       "      <th>STOPOTH</th>\n",
       "      <th>BONES2</th>\n",
       "      <th>HOURSPA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  HAVEPER EXPENSI BROKEBO AGE  EXERCIS DNTKNOW AVCIGDA NOREASO SIDEEFF  \\\n",
       "0   -9999   -9999       0  54    -9999   -9999      -1   -9999   -9999   \n",
       "1   -9999   -9999       0  53    -9999   -9999      -1   -9999   -9999   \n",
       "2   -9999   -9999       0  50    -9999   -9999      -1   -9999   -9999   \n",
       "3   -9999   -9999       0  53    -9999   -9999      -1   -9999   -9999   \n",
       "4   -9999   -9999       0  48    -9999   -9999      -1   -9999   -9999   \n",
       "\n",
       "  OSTEOPO  ...  DIETNUT LIKEFEL COMBIN2 BONES3 PRGNANT SMOKERE ESTRNJ1  \\\n",
       "0       1  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "1   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "2   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "3   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "4   -9999  ...    -9999   -9999   -9999  -9999       1       1       1   \n",
       "\n",
       "  STOPOTH  BONES2 HOURSPA  \n",
       "0   -9999   -9999       4  \n",
       "1   -9999   -9999   -9999  \n",
       "2   -9999   -9999   -9999  \n",
       "3   -9999   -9999       1  \n",
       "4   -9999   -9999       4  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For time columns, empty values are coded ad '.'. Replace them with ''\n",
    "\n",
    "for df in visit_dfs:\n",
    "    df.replace('.', '', inplace=True)\n",
    "\n",
    "visit_dfs[1][list(selected_shared_variables)].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 🔗 Concatenating dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concatenate all the dataframes together\n",
    "full_df = pd.concat((df[list(selected_shared_variables)] for df in visit_dfs))\n",
    "\n",
    "# Write it to a file\n",
    "full_df.to_csv('./datasets/visit_dfs.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ❌ Removing columns with several missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for each column how many -9999 values there are\n",
    "# Make a dictionary with the column name and the number of -9999 values\n",
    "missing_values = {}\n",
    "\n",
    "for var in full_df.columns:\n",
    "    missing_values[var] = len(full_df[full_df[var] == -9999])\n",
    "\n",
    "# Sort the dictionary by the number of missing values\n",
    "sorted_missing_values = {k: v for k, v in reversed(sorted(missing_values.items(), key=lambda item: item[1]))}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([   0,    1,    2,    3,    4,    5,    6,    7,    8,    9,\n",
       "            ...\n",
       "            2235, 2236, 2237, 2238, 2239, 2240, 2241, 2242, 2243, 2244],\n",
       "           dtype='int64', length=25487)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MENODEP    80.962844\n",
       "NUMHOTF    63.750147\n",
       "DIETNUT    59.822655\n",
       "EXERCIS    59.822655\n",
       "BONES3     47.086750\n",
       "             ...    \n",
       "COMBIN1     0.470828\n",
       "LANGINT     0.466905\n",
       "AGE         0.160866\n",
       "VISIT       0.000000\n",
       "RACE        0.000000\n",
       "Name: null_ratio, Length: 64, dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the percentage of missing values for each column\n",
    "missing_ratio = pd.Series({k: v/len(full_df.index) * 100 for k,v in sorted_missing_values.items()})\n",
    "missing_ratio.name = 'null_ratio'\n",
    "missing_ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>nombre</th>\n",
       "      <th>desc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FORMPHY</th>\n",
       "      <td>Abbreviated or Full Physical Measures</td>\n",
       "      <td>Abbreviated or Full Physical Measures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FORMINT</th>\n",
       "      <td>Abbreviated, mailed or regular interview</td>\n",
       "      <td>Which version of the interview was administered:</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABBLEED</th>\n",
       "      <td>Abnormal vaginal bleeding since last visit</td>\n",
       "      <td>Since your last study visit, have you had any ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACTIPAN</th>\n",
       "      <td>Active this week</td>\n",
       "      <td>Indicate in the space next to each item how st...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACUPUNC</th>\n",
       "      <td>Acupuncture</td>\n",
       "      <td>During the past 12 months, have you used any o...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YOGASPE</th>\n",
       "      <td>Yoga - Other specify</td>\n",
       "      <td>If YES to YOGA9: Please tell me whether or not...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YOGAADV</th>\n",
       "      <td>Yoga - Provider advice</td>\n",
       "      <td>If YES to YOGA9: Please tell me whether or not...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YOGAPER</th>\n",
       "      <td>Yoga - Regulate Periods</td>\n",
       "      <td>If YES to YOGA9: Please tell me whether or not...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YOGAWGH</th>\n",
       "      <td>Yoga - Weight</td>\n",
       "      <td>If YES to YOGA9: Please tell me whether or not...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YOGALOO</th>\n",
       "      <td>Yoga - Young Looking</td>\n",
       "      <td>If YES to YOGA9: Please tell me whether or not...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1280 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             nombre  \\\n",
       "id                                                    \n",
       "FORMPHY       Abbreviated or Full Physical Measures   \n",
       "FORMINT    Abbreviated, mailed or regular interview   \n",
       "ABBLEED  Abnormal vaginal bleeding since last visit   \n",
       "ACTIPAN                            Active this week   \n",
       "ACUPUNC                                 Acupuncture   \n",
       "...                                             ...   \n",
       "YOGASPE                        Yoga - Other specify   \n",
       "YOGAADV                      Yoga - Provider advice   \n",
       "YOGAPER                     Yoga - Regulate Periods   \n",
       "YOGAWGH                               Yoga - Weight   \n",
       "YOGALOO                        Yoga - Young Looking   \n",
       "\n",
       "                                                      desc  \n",
       "id                                                          \n",
       "FORMPHY              Abbreviated or Full Physical Measures  \n",
       "FORMINT   Which version of the interview was administered:  \n",
       "ABBLEED  Since your last study visit, have you had any ...  \n",
       "ACTIPAN  Indicate in the space next to each item how st...  \n",
       "ACUPUNC  During the past 12 months, have you used any o...  \n",
       "...                                                    ...  \n",
       "YOGASPE  If YES to YOGA9: Please tell me whether or not...  \n",
       "YOGAADV  If YES to YOGA9: Please tell me whether or not...  \n",
       "YOGAPER  If YES to YOGA9: Please tell me whether or not...  \n",
       "YOGAWGH  If YES to YOGA9: Please tell me whether or not...  \n",
       "YOGALOO  If YES to YOGA9: Please tell me whether or not...  \n",
       "\n",
       "[1280 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv with variable names and descriptions, and join by variable ID\n",
    "var_names = pd.read_csv('./lista_variables/variables.csv')\n",
    "normalized_colnames = {var:var.removesuffix('9') if var[-1].isdigit() else var for var in var_names['id']}\n",
    "var_names['id'] = var_names['id'].map(normalized_colnames)\n",
    "var_names.set_index('id', inplace=True)\n",
    "var_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratio_comparison_df = pd.concat(\n",
    "    [\n",
    "        var_names['nombre'],\n",
    "        missing_ratio\n",
    "    ],\n",
    "    axis=1,\n",
    "    join='inner'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>nombre</th>\n",
       "      <th>null_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MENODEP</th>\n",
       "      <td>During menopause, depressed</td>\n",
       "      <td>80.962844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUMHOTF</th>\n",
       "      <td>Hot flashes/flushes times/day</td>\n",
       "      <td>63.750147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DIETNUT</th>\n",
       "      <td>Nutritious diet</td>\n",
       "      <td>59.822655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXERCIS</th>\n",
       "      <td>Exercise</td>\n",
       "      <td>59.822655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BONES3</th>\n",
       "      <td>Bone #3 broken</td>\n",
       "      <td>47.086750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COMBIN1</th>\n",
       "      <td>Combination estrogen/progestin #1</td>\n",
       "      <td>0.470828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LANGINT</th>\n",
       "      <td>Language of Interview</td>\n",
       "      <td>0.466905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGE</th>\n",
       "      <td>Age At Current Visit (Integer)</td>\n",
       "      <td>0.160866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RACE</th>\n",
       "      <td>Race/Ethnicity</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VISIT</th>\n",
       "      <td>Study Visit</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    nombre  null_ratio\n",
       "MENODEP        During menopause, depressed   80.962844\n",
       "NUMHOTF      Hot flashes/flushes times/day   63.750147\n",
       "DIETNUT                    Nutritious diet   59.822655\n",
       "EXERCIS                           Exercise   59.822655\n",
       "BONES3                      Bone #3 broken   47.086750\n",
       "...                                    ...         ...\n",
       "COMBIN1  Combination estrogen/progestin #1    0.470828\n",
       "LANGINT              Language of Interview    0.466905\n",
       "AGE         Age At Current Visit (Integer)    0.160866\n",
       "RACE                        Race/Ethnicity    0.000000\n",
       "VISIT                          Study Visit    0.000000\n",
       "\n",
       "[61 rows x 2 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratio_comparison_df.sort_values(by='null_ratio', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check here the columns to preserve\n",
    "\n",
    "| id      | name                                        | null_ratio | preserved |\n",
    "|---------|---------------------------------------------|------------|-----------|\n",
    "| MENODEP | During menopause, depressed                 | 80.962844  | ✅        |\n",
    "| NUMHOTF | Hot flashes/flushes times/day               | 63.750147  | ✅        |\n",
    "| EXERCIS | Exercise                                    | 59.822655  | ✅        |\n",
    "| DIETNUT | Nutritious diet                             | 59.822655  | ✅        |\n",
    "| BONES3  | Bone #3 broken                              | 47.086750  | ❌        |\n",
    "| BONES2  | Bone #2 broken                              | 47.067132  | ❌        |\n",
    "| BONES1  | Bone #1 broken                              | 46.906266  | ❌        |\n",
    "| OUTCOME | Outcome of pregnancy                        | 26.703810  | ✅        |\n",
    "| ESTRNJ2 | Estrogen injection/patch #2                | 22.325107  | ✅        |\n",
    "| COMBIN2 | Combination estrogen/progestin #2          | 21.901361  | ✅        |\n",
    "| ESTROG2 | Estrogen pills #2                          | 21.493310  | ✅        |\n",
    "| E2AVE   | Estradiol (average, pg/mL)                 | 15.878683  | ✅        |\n",
    "| ALCHL24 | Alcohol in Last 24 hours                   | 15.576568  | ✅        |\n",
    "| DNTKNOW | Don't know why stopped hormones            | 15.172441  | ❌        |\n",
    "| NOREASO | Stopped hormones no reason given           | 15.172441  | ❌        |\n",
    "| DONTKNO | Don't know/remember why take hormones      | 14.179778  | ❌        |\n",
    "| ESTRDA2 | Estrogen #2 prescription daily or off & on | 13.603013  | ✅        |\n",
    "| HOURSPA | How many hours/week work for pay           | 13.469612  | ✅        |\n",
    "| NOLIKE  | Do not like taking any medications         | 12.743752  | ❌        |\n",
    "| EXPENSI | Too expensive                              | 12.743752  | ❌        |\n",
    "| CANCER  | Worried about cancer                       | 12.743752  | ❌        |\n",
    "| ADVISTO | Health care provider advised to stop       | 12.743752  | ❌        |\n",
    "| SIDEEFF | Worried about possible side effects        | 12.743752  | ❌        |\n",
    "| NOREMEB | Couldn't remember to take them             | 12.743752  | ❌        |\n",
    "| STOPOTH | Stopped hormones other reason              | 12.743752  | ❌        |\n",
    "| HAVEPER | Did not like having periods                | 12.743752  | ❌        |\n",
    "| LIKEFEL | Did not like how felt on them              | 12.743752  | ❌        |\n",
    "| PRBBLEE | Problems with bleeding                     | 12.739828  | ❌        |\n",
    "| HORMOTH | Take hormones for other reasons            | 11.739318  | ❌        |\n",
    "| MENOSYM | Relieve menopausal symptoms                | 11.735395  | ❌        |\n",
    "| YOUNGLK | Stay young-looking                         | 11.735395  | ❌        |\n",
    "| REGPERI | Regulate periods                           | 11.735395  | ❌        |\n",
    "| HCPADVI | Health care provider advised               | 11.735395  | ❌        |\n",
    "| OSTEOPR | Reduce risk of osteoporosis                | 11.735395  | ❌        |\n",
    "| IMPRMEM | Improve memory                             | 11.735395  | ❌        |\n",
    "| REDUHAR | Reduce risk of heart disease               | 11.735395  | ❌        |\n",
    "| FRNADVI | Friend/relative advised                    | 11.735395  | ❌        |\n",
    "| HEIGHT  | Height (in cm)                             | 10.829050  | ✅        |\n",
    "| ESTRDA1 | Estrogen #1 prescription daily or off & on | 10.644642  | ✅        |\n",
    "| WEIGHT  | Weight (in kg)                             | 6.493506   | ✅        |\n",
    "| HAPPY   | Happy past week                            | 6.081532   | ✅        |\n",
    "| PRGNANT | Pregnant since last visit                  | 4.861302   | ✅        |\n",
    "| VAGINDR | Vaginal dryness past 2 weeks               | 2.668027   | ✅        |\n",
    "| SMOKERE | Smoked regularly since last visit          | 2.664103   | ✅        |\n",
    "| HOTFLAS | Hot flashes past 2 weeks                   | 2.664103   | ✅        |\n",
    "| PHYSILL | Serious illness family upsetting since last visit | 2.664103   | ✅        |\n",
    "| AVCIGDA | Average cigarettes/day since last visit    | 2.660180   | ✅        |\n",
    "| LANGSAA | Language of Self-A                         | 2.660180   | ❌        |\n",
    "| TRBLSLE | Trouble falling asleep past 2 weeks        | 2.660180   | ✅        |\n",
    "| CANCERS | Cancer since last visit                    | 0.541453   | ✅        |\n",
    "| DIABETE | Diabetes since last visit                  | 0.537529   | ✅        |\n",
    "| BROKEBO | Number events where bone(s) broken since last visit | 0.537529   | ✅        |\n",
    "| OSTEOPR | Osteoporosis since last visit              | 0.537529   | ✅        |\n",
    "| STATUS  | Menopausal Status                          | 0.537529   | ✅        |\n",
    "| ESTROG1 | Estrogen pills #1                          | 0.478675   | ✅        |\n",
    "| ESTRNJ1 | Estrogen injection/patch #1                | 0.470828   | ✅        |\n",
    "| COMBIN1 | Combination estrogen/progestin #1          | 0.470828   | ❌        |\n",
    "| LANGINT | Language of Interview                      | 0.466905   | ❌        |\n",
    "| AGE     | Age At Current Visit (Integer)             | 0.160866   | ✅        |\n",
    "| RACE    | Race/Ethnicity                             | 0.000000   | ✅        |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> During the process of manual selection of variables, we have found that the following variables are not present in all the visits (or, perhaps, were not detected given by the prompt asked to the LLM used to filter variables), but do appear in most of them, and are relevant to the study:\n",
    "\n",
    "- **STATUS:** menopause status.\n",
    "- **WEIGHT:** weight in kg.\n",
    "- **HEIGHT:** height in cm.\n",
    "- **EXERCIS:** exercise or not in past 12 months.\n",
    "- **DIETNUT:** if diet carried out is considered healthy.\n",
    "- **EMOCTDW:** emotional cutdown due to menopause symptoms.\n",
    "- **EMOCARE:** how careful you've been given how symptoms make you feel.\n",
    "- **SOCIAL:** how much menopause symptoms have affected social life.\n",
    "- **CANCERS:** has had or not cancer since last visit and which type.\n",
    "- **TRBLSLE:** trouble falling asleep past 2 weeks.\n",
    "- **HAPPY:** happy with life last week.\n",
    "- **SMOKERE:** regular smoker.\n",
    "- **MENODEP:** depression status due to menopause symptoms.\n",
    "- **NUMHOTF:** On the days that you have hot flashes or flushes, how many times each day do you usually have them?\n",
    "\n",
    "> ... therefore the notebook will be rerun with these variables included in the final dataframe."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}