01_Selected_Patients.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a6653d41",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9aee6928",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "175182f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat = pd.read_csv(\"d_patients.csv\",  usecols=['subject_id', 'sex', 'dob'], index_col='subject_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6ebddb12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4000, 2)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "886a49f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1/1/2605 00:00:00'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat[\"dob\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "83fbd4c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'9/9/3293 00:00:00'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat[\"dob\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "543e4f82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4000 entries, 3 to 32763\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   sex     3987 non-null   object\n",
      " 1   dob     4000 non-null   object\n",
      "dtypes: object(2)\n",
      "memory usage: 93.8+ KB\n"
     ]
    }
   ],
   "source": [
    "pat.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4c87ad49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat.index.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5a088366",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat[\"dob\"] = pat[\"dob\"].str.replace(\"00:00:00\", \"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5dea2d30",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat[[0,1,2]] = pat[\"dob\"].str.split(\"/\", expand =True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65db8ea0",
   "metadata": {},
   "source": [
    "# Converting date format into YYYY-MM-DD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7f3dac54",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat[0] = pat[0].apply(lambda x: \"0\"+x if len(x)==1 else x)\n",
    "pat[1] = pat[1].apply(lambda x: \"0\"+x if len(x)==1 else x)\n",
    "pat[\"new\"] = pat[2]+\"-\"+pat[1]+\"-\"+pat[0]\n",
    "pat[\"new\"] = pat[\"new\"].str.replace(\" \", \"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a72415a5",
   "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>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M</td>\n",
       "      <td>28/2/2606</td>\n",
       "      <td>28</td>\n",
       "      <td>02</td>\n",
       "      <td>2606</td>\n",
       "      <td>2606-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M</td>\n",
       "      <td>14/5/2803</td>\n",
       "      <td>14</td>\n",
       "      <td>05</td>\n",
       "      <td>2803</td>\n",
       "      <td>2803-05-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>M</td>\n",
       "      <td>22/5/3051</td>\n",
       "      <td>22</td>\n",
       "      <td>05</td>\n",
       "      <td>3051</td>\n",
       "      <td>3051-05-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>M</td>\n",
       "      <td>2/3/3007</td>\n",
       "      <td>02</td>\n",
       "      <td>03</td>\n",
       "      <td>3007</td>\n",
       "      <td>3007-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>M</td>\n",
       "      <td>17/5/2606</td>\n",
       "      <td>17</td>\n",
       "      <td>05</td>\n",
       "      <td>2606</td>\n",
       "      <td>2606-05-17</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32686</th>\n",
       "      <td>M</td>\n",
       "      <td>6/12/2543</td>\n",
       "      <td>06</td>\n",
       "      <td>12</td>\n",
       "      <td>2543</td>\n",
       "      <td>2543-12-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32705</th>\n",
       "      <td>F</td>\n",
       "      <td>5/3/3257</td>\n",
       "      <td>05</td>\n",
       "      <td>03</td>\n",
       "      <td>3257</td>\n",
       "      <td>3257-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32706</th>\n",
       "      <td>M</td>\n",
       "      <td>14/2/2595</td>\n",
       "      <td>14</td>\n",
       "      <td>02</td>\n",
       "      <td>2595</td>\n",
       "      <td>2595-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>F</td>\n",
       "      <td>21/12/3057</td>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>3057</td>\n",
       "      <td>3057-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32763</th>\n",
       "      <td>M</td>\n",
       "      <td>10/4/2645</td>\n",
       "      <td>10</td>\n",
       "      <td>04</td>\n",
       "      <td>2645</td>\n",
       "      <td>2645-04-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           sex          dob   0   1      2         new\n",
       "subject_id                                            \n",
       "3            M   28/2/2606   28  02  2606   2606-02-28\n",
       "12           M   14/5/2803   14  05  2803   2803-05-14\n",
       "21           M   22/5/3051   22  05  3051   3051-05-22\n",
       "26           M    2/3/3007   02  03  3007   3007-03-02\n",
       "31           M   17/5/2606   17  05  2606   2606-05-17\n",
       "...         ..          ...  ..  ..    ...         ...\n",
       "32686        M   6/12/2543   06  12  2543   2543-12-06\n",
       "32705        F    5/3/3257   05  03  3257   3257-03-05\n",
       "32706        M   14/2/2595   14  02  2595   2595-02-14\n",
       "32711        F  21/12/3057   21  12  3057   3057-12-21\n",
       "32763        M   10/4/2645   10  04  2645   2645-04-10\n",
       "\n",
       "[4000 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7cc17a7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat[\"new\"] = pat[\"new\"].apply(lambda x: np.datetime64(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "112a742b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat.drop([0,1,2], axis= 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "aa50eb81",
   "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>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M</td>\n",
       "      <td>28/2/2606</td>\n",
       "      <td>2606-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M</td>\n",
       "      <td>14/5/2803</td>\n",
       "      <td>2803-05-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>M</td>\n",
       "      <td>22/5/3051</td>\n",
       "      <td>3051-05-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>M</td>\n",
       "      <td>2/3/3007</td>\n",
       "      <td>3007-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>M</td>\n",
       "      <td>17/5/2606</td>\n",
       "      <td>2606-05-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32686</th>\n",
       "      <td>M</td>\n",
       "      <td>6/12/2543</td>\n",
       "      <td>2543-12-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32705</th>\n",
       "      <td>F</td>\n",
       "      <td>5/3/3257</td>\n",
       "      <td>3257-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32706</th>\n",
       "      <td>M</td>\n",
       "      <td>14/2/2595</td>\n",
       "      <td>2595-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>F</td>\n",
       "      <td>21/12/3057</td>\n",
       "      <td>3057-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32763</th>\n",
       "      <td>M</td>\n",
       "      <td>10/4/2645</td>\n",
       "      <td>2645-04-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           sex          dob         new\n",
       "subject_id                             \n",
       "3            M   28/2/2606   2606-02-28\n",
       "12           M   14/5/2803   2803-05-14\n",
       "21           M   22/5/3051   3051-05-22\n",
       "26           M    2/3/3007   3007-03-02\n",
       "31           M   17/5/2606   2606-05-17\n",
       "...         ..          ...         ...\n",
       "32686        M   6/12/2543   2543-12-06\n",
       "32705        F    5/3/3257   3257-03-05\n",
       "32706        M   14/2/2595   2595-02-14\n",
       "32711        F  21/12/3057   3057-12-21\n",
       "32763        M   10/4/2645   2645-04-10\n",
       "\n",
       "[4000 rows x 3 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6653875",
   "metadata": {},
   "source": [
    "## Admission File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ab56e3bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "adm = pd.read_csv(\"admissions.csv\", usecols=['subject_id', 'admit_dt', 'hadm_id'], index_col='subject_id', parse_dates=['admit_dt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0f8eadc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5074, 2)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "905cbcc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3952"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm.index.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "cf568ca7",
   "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>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24807</th>\n",
       "      <td>2</td>\n",
       "      <td>8/7/3033 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>3</td>\n",
       "      <td>16/5/3388 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23547</th>\n",
       "      <td>6</td>\n",
       "      <td>3/4/3381 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14884</th>\n",
       "      <td>10</td>\n",
       "      <td>28/8/3015 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8652</th>\n",
       "      <td>12</td>\n",
       "      <td>11/9/3125 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            hadm_id            admit_dt\n",
       "subject_id                             \n",
       "24807             2   8/7/3033 00:00:00\n",
       "7675              3  16/5/3388 00:00:00\n",
       "23547             6   3/4/3381 00:00:00\n",
       "14884            10  28/8/3015 00:00:00\n",
       "8652             12  11/9/3125 00:00:00"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5cd4ddc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "adm[\"admit_dt\"] = adm[\"admit_dt\"].str.replace(\"00:00:00\", \"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "598caa40",
   "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>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24807</th>\n",
       "      <td>2</td>\n",
       "      <td>8/7/3033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>3</td>\n",
       "      <td>16/5/3388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23547</th>\n",
       "      <td>6</td>\n",
       "      <td>3/4/3381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14884</th>\n",
       "      <td>10</td>\n",
       "      <td>28/8/3015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8652</th>\n",
       "      <td>12</td>\n",
       "      <td>11/9/3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29309</th>\n",
       "      <td>36005</td>\n",
       "      <td>11/12/2541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32667</th>\n",
       "      <td>36071</td>\n",
       "      <td>18/2/2866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31134</th>\n",
       "      <td>36077</td>\n",
       "      <td>24/4/2724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28191</th>\n",
       "      <td>36103</td>\n",
       "      <td>27/4/2675</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5074 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            hadm_id     admit_dt\n",
       "subject_id                      \n",
       "24807             2    8/7/3033 \n",
       "7675              3   16/5/3388 \n",
       "23547             6    3/4/3381 \n",
       "14884            10   28/8/3015 \n",
       "8652             12   11/9/3125 \n",
       "...             ...          ...\n",
       "29309         36005  11/12/2541 \n",
       "32711         36069   20/5/3143 \n",
       "32667         36071   18/2/2866 \n",
       "31134         36077   24/4/2724 \n",
       "28191         36103   27/4/2675 \n",
       "\n",
       "[5074 rows x 2 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c6d4c0ce",
   "metadata": {},
   "outputs": [],
   "source": [
    " adm[[0,1,2]] = adm[\"admit_dt\"].str.split(\"/\", expand =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ac68af13",
   "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>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24807</th>\n",
       "      <td>2</td>\n",
       "      <td>8/7/3033</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>3033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>3</td>\n",
       "      <td>16/5/3388</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>3388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23547</th>\n",
       "      <td>6</td>\n",
       "      <td>3/4/3381</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14884</th>\n",
       "      <td>10</td>\n",
       "      <td>28/8/3015</td>\n",
       "      <td>28</td>\n",
       "      <td>8</td>\n",
       "      <td>3015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8652</th>\n",
       "      <td>12</td>\n",
       "      <td>11/9/3125</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29309</th>\n",
       "      <td>36005</td>\n",
       "      <td>11/12/2541</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>2541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>3143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32667</th>\n",
       "      <td>36071</td>\n",
       "      <td>18/2/2866</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>2866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31134</th>\n",
       "      <td>36077</td>\n",
       "      <td>24/4/2724</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>2724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28191</th>\n",
       "      <td>36103</td>\n",
       "      <td>27/4/2675</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>2675</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5074 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            hadm_id     admit_dt   0   1      2\n",
       "subject_id                                     \n",
       "24807             2    8/7/3033    8   7  3033 \n",
       "7675              3   16/5/3388   16   5  3388 \n",
       "23547             6    3/4/3381    3   4  3381 \n",
       "14884            10   28/8/3015   28   8  3015 \n",
       "8652             12   11/9/3125   11   9  3125 \n",
       "...             ...          ...  ..  ..    ...\n",
       "29309         36005  11/12/2541   11  12  2541 \n",
       "32711         36069   20/5/3143   20   5  3143 \n",
       "32667         36071   18/2/2866   18   2  2866 \n",
       "31134         36077   24/4/2724   24   4  2724 \n",
       "28191         36103   27/4/2675   27   4  2675 \n",
       "\n",
       "[5074 rows x 5 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "002e2e94",
   "metadata": {},
   "source": [
    "# Converting date format into YYYY-MM-DD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3b81c0a6",
   "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>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>new1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24807</th>\n",
       "      <td>2</td>\n",
       "      <td>8/7/3033</td>\n",
       "      <td>08</td>\n",
       "      <td>07</td>\n",
       "      <td>3033</td>\n",
       "      <td>3033-07-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>3</td>\n",
       "      <td>16/5/3388</td>\n",
       "      <td>16</td>\n",
       "      <td>05</td>\n",
       "      <td>3388</td>\n",
       "      <td>3388-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23547</th>\n",
       "      <td>6</td>\n",
       "      <td>3/4/3381</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>3381</td>\n",
       "      <td>3381-04-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14884</th>\n",
       "      <td>10</td>\n",
       "      <td>28/8/3015</td>\n",
       "      <td>28</td>\n",
       "      <td>08</td>\n",
       "      <td>3015</td>\n",
       "      <td>3015-08-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8652</th>\n",
       "      <td>12</td>\n",
       "      <td>11/9/3125</td>\n",
       "      <td>11</td>\n",
       "      <td>09</td>\n",
       "      <td>3125</td>\n",
       "      <td>3125-09-11</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29309</th>\n",
       "      <td>36005</td>\n",
       "      <td>11/12/2541</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>2541</td>\n",
       "      <td>2541-12-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>20</td>\n",
       "      <td>05</td>\n",
       "      <td>3143</td>\n",
       "      <td>3143-05-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32667</th>\n",
       "      <td>36071</td>\n",
       "      <td>18/2/2866</td>\n",
       "      <td>18</td>\n",
       "      <td>02</td>\n",
       "      <td>2866</td>\n",
       "      <td>2866-02-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31134</th>\n",
       "      <td>36077</td>\n",
       "      <td>24/4/2724</td>\n",
       "      <td>24</td>\n",
       "      <td>04</td>\n",
       "      <td>2724</td>\n",
       "      <td>2724-04-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28191</th>\n",
       "      <td>36103</td>\n",
       "      <td>27/4/2675</td>\n",
       "      <td>27</td>\n",
       "      <td>04</td>\n",
       "      <td>2675</td>\n",
       "      <td>2675-04-27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5074 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            hadm_id     admit_dt   0   1      2        new1\n",
       "subject_id                                                 \n",
       "24807             2    8/7/3033   08  07  3033   3033-07-08\n",
       "7675              3   16/5/3388   16  05  3388   3388-05-16\n",
       "23547             6    3/4/3381   03  04  3381   3381-04-03\n",
       "14884            10   28/8/3015   28  08  3015   3015-08-28\n",
       "8652             12   11/9/3125   11  09  3125   3125-09-11\n",
       "...             ...          ...  ..  ..    ...         ...\n",
       "29309         36005  11/12/2541   11  12  2541   2541-12-11\n",
       "32711         36069   20/5/3143   20  05  3143   3143-05-20\n",
       "32667         36071   18/2/2866   18  02  2866   2866-02-18\n",
       "31134         36077   24/4/2724   24  04  2724   2724-04-24\n",
       "28191         36103   27/4/2675   27  04  2675   2675-04-27\n",
       "\n",
       "[5074 rows x 6 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm[0] = adm[0].apply(lambda x: \"0\"+x if len(x)==1 else x)\n",
    "adm[1] = adm[1].apply(lambda x: \"0\"+x if len(x)==1 else x)\n",
    "adm[\"new1\"] = adm[2]+\"-\"+adm[1]+\"-\"+adm[0]\n",
    "adm[\"new1\"] = adm[\"new1\"].str.replace(\" \", \"\")\n",
    "adm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "40ff73d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "adm[\"new1\"] = adm[\"new1\"].apply(lambda x: np.datetime64(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a0939b72",
   "metadata": {},
   "outputs": [],
   "source": [
    "adm.drop([0,1,2], axis= 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a835a576",
   "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>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>new1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24807</th>\n",
       "      <td>2</td>\n",
       "      <td>8/7/3033</td>\n",
       "      <td>3033-07-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7675</th>\n",
       "      <td>3</td>\n",
       "      <td>16/5/3388</td>\n",
       "      <td>3388-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23547</th>\n",
       "      <td>6</td>\n",
       "      <td>3/4/3381</td>\n",
       "      <td>3381-04-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14884</th>\n",
       "      <td>10</td>\n",
       "      <td>28/8/3015</td>\n",
       "      <td>3015-08-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8652</th>\n",
       "      <td>12</td>\n",
       "      <td>11/9/3125</td>\n",
       "      <td>3125-09-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29309</th>\n",
       "      <td>36005</td>\n",
       "      <td>11/12/2541</td>\n",
       "      <td>2541-12-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>3143-05-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32667</th>\n",
       "      <td>36071</td>\n",
       "      <td>18/2/2866</td>\n",
       "      <td>2866-02-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31134</th>\n",
       "      <td>36077</td>\n",
       "      <td>24/4/2724</td>\n",
       "      <td>2724-04-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28191</th>\n",
       "      <td>36103</td>\n",
       "      <td>27/4/2675</td>\n",
       "      <td>2675-04-27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5074 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            hadm_id     admit_dt        new1\n",
       "subject_id                                  \n",
       "24807             2    8/7/3033   3033-07-08\n",
       "7675              3   16/5/3388   3388-05-16\n",
       "23547             6    3/4/3381   3381-04-03\n",
       "14884            10   28/8/3015   3015-08-28\n",
       "8652             12   11/9/3125   3125-09-11\n",
       "...             ...          ...         ...\n",
       "29309         36005  11/12/2541   2541-12-11\n",
       "32711         36069   20/5/3143   3143-05-20\n",
       "32667         36071   18/2/2866   2866-02-18\n",
       "31134         36077   24/4/2724   2724-04-24\n",
       "28191         36103   27/4/2675   2675-04-27\n",
       "\n",
       "[5074 rows x 3 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5be33a44",
   "metadata": {},
   "source": [
    "# Getting first admission ID for each subject, by sorting the new1 (Numpy date) column and dropping the duplicates in subject_id "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "8580a003",
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>new1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>7/9/2682</td>\n",
       "      <td>2682-09-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1962</th>\n",
       "      <td>12</td>\n",
       "      <td>12532</td>\n",
       "      <td>26/9/2875</td>\n",
       "      <td>2875-09-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3280</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>29/10/3138</td>\n",
       "      <td>3138-10-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2363</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>3/3/3079</td>\n",
       "      <td>3079-03-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2410</th>\n",
       "      <td>31</td>\n",
       "      <td>15325</td>\n",
       "      <td>21/8/2678</td>\n",
       "      <td>2678-08-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4743</th>\n",
       "      <td>32686</td>\n",
       "      <td>30102</td>\n",
       "      <td>27/5/2612</td>\n",
       "      <td>2612-05-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4958</th>\n",
       "      <td>32705</td>\n",
       "      <td>33462</td>\n",
       "      <td>6/4/3337</td>\n",
       "      <td>3337-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5052</th>\n",
       "      <td>32706</td>\n",
       "      <td>35651</td>\n",
       "      <td>20/3/2676</td>\n",
       "      <td>2676-03-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5070</th>\n",
       "      <td>32711</td>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>3143-05-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4891</th>\n",
       "      <td>32763</td>\n",
       "      <td>33007</td>\n",
       "      <td>5/6/2724</td>\n",
       "      <td>2724-06-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3952 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id  hadm_id     admit_dt        new1\n",
       "362            3     2075    7/9/2682   2682-09-07\n",
       "1962          12    12532   26/9/2875   2875-09-26\n",
       "3280          21    20666  29/10/3138   3138-10-29\n",
       "2363          26    15067    3/3/3079   3079-03-03\n",
       "2410          31    15325   21/8/2678   2678-08-21\n",
       "...          ...      ...          ...         ...\n",
       "4743       32686    30102   27/5/2612   2612-05-27\n",
       "4958       32705    33462    6/4/3337   3337-04-06\n",
       "5052       32706    35651   20/3/2676   2676-03-20\n",
       "5070       32711    36069   20/5/3143   3143-05-20\n",
       "4891       32763    33007    5/6/2724   2724-06-05\n",
       "\n",
       "[3952 rows x 4 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6 = adm.reset_index().sort_values([\"subject_id\", \"new1\"]).drop_duplicates(\"subject_id\")\n",
    "df6"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38911bc8",
   "metadata": {},
   "source": [
    "# This is for check, that there must be 3952 records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7b85c6bb",
   "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>first_admittime</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2682-09-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2875-09-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3138-10-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3079-03-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2678-08-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32686</th>\n",
       "      <td>2612-05-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32705</th>\n",
       "      <td>3337-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32706</th>\n",
       "      <td>2676-03-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32711</th>\n",
       "      <td>3143-05-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32763</th>\n",
       "      <td>2724-06-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3952 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           first_admittime\n",
       "subject_id                \n",
       "3               2682-09-07\n",
       "12              2875-09-26\n",
       "21              3138-10-29\n",
       "26              3079-03-03\n",
       "31              2678-08-21\n",
       "...                    ...\n",
       "32686           2612-05-27\n",
       "32705           3337-04-06\n",
       "32706           2676-03-20\n",
       "32711           3143-05-20\n",
       "32763           2724-06-05\n",
       "\n",
       "[3952 rows x 1 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = adm.groupby([\"subject_id\"]).agg(first_admittime=('new1',np.min))\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "25a07edb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# subject ids which are unique in Patient's table = 4000, but in Admissions table unique are 3952 after sorting for\n",
    "# first admission date. One may think they also must be 4000, but in the guide of MIMIC, they have stated that some of \n",
    "# the patients' data can be missing due to human error. so it is okay to go with 3952 ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0c769c43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "362       7/9/2682 \n",
       "1962     26/9/2875 \n",
       "3280    29/10/3138 \n",
       "2363      3/3/3079 \n",
       "2410     21/8/2678 \n",
       "           ...     \n",
       "4743     27/5/2612 \n",
       "4958      6/4/3337 \n",
       "5052     20/3/2676 \n",
       "5070     20/5/3143 \n",
       "4891      5/6/2724 \n",
       "Name: admit_dt, Length: 3952, dtype: object"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6['admit_dt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "79f963f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "subject_id\n",
       "3        2606-02-28\n",
       "12       2803-05-14\n",
       "21       3051-05-22\n",
       "26       3007-03-02\n",
       "31       2606-05-17\n",
       "            ...    \n",
       "32686    2543-12-06\n",
       "32705    3257-03-05\n",
       "32706    2595-02-14\n",
       "32711    3057-12-21\n",
       "32763    2645-04-10\n",
       "Name: new, Length: 4000, dtype: object"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat[\"new\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "4be7c7c4",
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>new1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>7/9/2682</td>\n",
       "      <td>2682-09-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     subject_id  hadm_id   admit_dt        new1\n",
       "362           3     2075  7/9/2682   2682-09-07"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d489bf8e",
   "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>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subject_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M</td>\n",
       "      <td>28/2/2606</td>\n",
       "      <td>2606-02-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           sex         dob         new\n",
       "subject_id                            \n",
       "3            M  28/2/2606   2606-02-28"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pat.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "aaeb5aea",
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>new1</th>\n",
       "      <th>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>7/9/2682</td>\n",
       "      <td>2682-09-07</td>\n",
       "      <td>M</td>\n",
       "      <td>28/2/2606</td>\n",
       "      <td>2606-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>12532</td>\n",
       "      <td>26/9/2875</td>\n",
       "      <td>2875-09-26</td>\n",
       "      <td>M</td>\n",
       "      <td>14/5/2803</td>\n",
       "      <td>2803-05-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>29/10/3138</td>\n",
       "      <td>3138-10-29</td>\n",
       "      <td>M</td>\n",
       "      <td>22/5/3051</td>\n",
       "      <td>3051-05-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>3/3/3079</td>\n",
       "      <td>3079-03-03</td>\n",
       "      <td>M</td>\n",
       "      <td>2/3/3007</td>\n",
       "      <td>3007-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>15325</td>\n",
       "      <td>21/8/2678</td>\n",
       "      <td>2678-08-21</td>\n",
       "      <td>M</td>\n",
       "      <td>17/5/2606</td>\n",
       "      <td>2606-05-17</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3947</th>\n",
       "      <td>32686</td>\n",
       "      <td>30102</td>\n",
       "      <td>27/5/2612</td>\n",
       "      <td>2612-05-27</td>\n",
       "      <td>M</td>\n",
       "      <td>6/12/2543</td>\n",
       "      <td>2543-12-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3948</th>\n",
       "      <td>32705</td>\n",
       "      <td>33462</td>\n",
       "      <td>6/4/3337</td>\n",
       "      <td>3337-04-06</td>\n",
       "      <td>F</td>\n",
       "      <td>5/3/3257</td>\n",
       "      <td>3257-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3949</th>\n",
       "      <td>32706</td>\n",
       "      <td>35651</td>\n",
       "      <td>20/3/2676</td>\n",
       "      <td>2676-03-20</td>\n",
       "      <td>M</td>\n",
       "      <td>14/2/2595</td>\n",
       "      <td>2595-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3950</th>\n",
       "      <td>32711</td>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>3143-05-20</td>\n",
       "      <td>F</td>\n",
       "      <td>21/12/3057</td>\n",
       "      <td>3057-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3951</th>\n",
       "      <td>32763</td>\n",
       "      <td>33007</td>\n",
       "      <td>5/6/2724</td>\n",
       "      <td>2724-06-05</td>\n",
       "      <td>M</td>\n",
       "      <td>10/4/2645</td>\n",
       "      <td>2645-04-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3952 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id  hadm_id     admit_dt        new1 sex          dob  \\\n",
       "0              3     2075    7/9/2682   2682-09-07   M   28/2/2606    \n",
       "1             12    12532   26/9/2875   2875-09-26   M   14/5/2803    \n",
       "2             21    20666  29/10/3138   3138-10-29   M   22/5/3051    \n",
       "3             26    15067    3/3/3079   3079-03-03   M    2/3/3007    \n",
       "4             31    15325   21/8/2678   2678-08-21   M   17/5/2606    \n",
       "...          ...      ...          ...         ...  ..          ...   \n",
       "3947       32686    30102   27/5/2612   2612-05-27   M   6/12/2543    \n",
       "3948       32705    33462    6/4/3337   3337-04-06   F    5/3/3257    \n",
       "3949       32706    35651   20/3/2676   2676-03-20   M   14/2/2595    \n",
       "3950       32711    36069   20/5/3143   3143-05-20   F  21/12/3057    \n",
       "3951       32763    33007    5/6/2724   2724-06-05   M   10/4/2645    \n",
       "\n",
       "             new  \n",
       "0     2606-02-28  \n",
       "1     2803-05-14  \n",
       "2     3051-05-22  \n",
       "3     3007-03-02  \n",
       "4     2606-05-17  \n",
       "...          ...  \n",
       "3947  2543-12-06  \n",
       "3948  3257-03-05  \n",
       "3949  2595-02-14  \n",
       "3950  3057-12-21  \n",
       "3951  2645-04-10  \n",
       "\n",
       "[3952 rows x 7 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7 = df6.merge(pat, left_on = \"subject_id\", right_on = pat.index, how = \"left\")\n",
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "55c474b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7[\"age_at_adm\"] = df7[\"new1\"] - df7[\"new\"]\n",
    "df7[\"age_at_adm\"] = df7[\"age_at_adm\"].astype(\"timedelta64[Y]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "14366eff",
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>new1</th>\n",
       "      <th>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>new</th>\n",
       "      <th>age_at_adm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>7/9/2682</td>\n",
       "      <td>2682-09-07</td>\n",
       "      <td>M</td>\n",
       "      <td>28/2/2606</td>\n",
       "      <td>2606-02-28</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>12532</td>\n",
       "      <td>26/9/2875</td>\n",
       "      <td>2875-09-26</td>\n",
       "      <td>M</td>\n",
       "      <td>14/5/2803</td>\n",
       "      <td>2803-05-14</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>29/10/3138</td>\n",
       "      <td>3138-10-29</td>\n",
       "      <td>M</td>\n",
       "      <td>22/5/3051</td>\n",
       "      <td>3051-05-22</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>3/3/3079</td>\n",
       "      <td>3079-03-03</td>\n",
       "      <td>M</td>\n",
       "      <td>2/3/3007</td>\n",
       "      <td>3007-03-02</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>15325</td>\n",
       "      <td>21/8/2678</td>\n",
       "      <td>2678-08-21</td>\n",
       "      <td>M</td>\n",
       "      <td>17/5/2606</td>\n",
       "      <td>2606-05-17</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3947</th>\n",
       "      <td>32686</td>\n",
       "      <td>30102</td>\n",
       "      <td>27/5/2612</td>\n",
       "      <td>2612-05-27</td>\n",
       "      <td>M</td>\n",
       "      <td>6/12/2543</td>\n",
       "      <td>2543-12-06</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3948</th>\n",
       "      <td>32705</td>\n",
       "      <td>33462</td>\n",
       "      <td>6/4/3337</td>\n",
       "      <td>3337-04-06</td>\n",
       "      <td>F</td>\n",
       "      <td>5/3/3257</td>\n",
       "      <td>3257-03-05</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3949</th>\n",
       "      <td>32706</td>\n",
       "      <td>35651</td>\n",
       "      <td>20/3/2676</td>\n",
       "      <td>2676-03-20</td>\n",
       "      <td>M</td>\n",
       "      <td>14/2/2595</td>\n",
       "      <td>2595-02-14</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3950</th>\n",
       "      <td>32711</td>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>3143-05-20</td>\n",
       "      <td>F</td>\n",
       "      <td>21/12/3057</td>\n",
       "      <td>3057-12-21</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3951</th>\n",
       "      <td>32763</td>\n",
       "      <td>33007</td>\n",
       "      <td>5/6/2724</td>\n",
       "      <td>2724-06-05</td>\n",
       "      <td>M</td>\n",
       "      <td>10/4/2645</td>\n",
       "      <td>2645-04-10</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3952 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id  hadm_id     admit_dt        new1 sex          dob  \\\n",
       "0              3     2075    7/9/2682   2682-09-07   M   28/2/2606    \n",
       "1             12    12532   26/9/2875   2875-09-26   M   14/5/2803    \n",
       "2             21    20666  29/10/3138   3138-10-29   M   22/5/3051    \n",
       "3             26    15067    3/3/3079   3079-03-03   M    2/3/3007    \n",
       "4             31    15325   21/8/2678   2678-08-21   M   17/5/2606    \n",
       "...          ...      ...          ...         ...  ..          ...   \n",
       "3947       32686    30102   27/5/2612   2612-05-27   M   6/12/2543    \n",
       "3948       32705    33462    6/4/3337   3337-04-06   F    5/3/3257    \n",
       "3949       32706    35651   20/3/2676   2676-03-20   M   14/2/2595    \n",
       "3950       32711    36069   20/5/3143   3143-05-20   F  21/12/3057    \n",
       "3951       32763    33007    5/6/2724   2724-06-05   M   10/4/2645    \n",
       "\n",
       "             new  age_at_adm  \n",
       "0     2606-02-28        76.0  \n",
       "1     2803-05-14        72.0  \n",
       "2     3051-05-22        87.0  \n",
       "3     3007-03-02        72.0  \n",
       "4     2606-05-17        72.0  \n",
       "...          ...         ...  \n",
       "3947  2543-12-06        68.0  \n",
       "3948  3257-03-05        80.0  \n",
       "3949  2595-02-14        81.0  \n",
       "3950  3057-12-21        85.0  \n",
       "3951  2645-04-10        79.0  \n",
       "\n",
       "[3952 rows x 8 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab65a344",
   "metadata": {},
   "source": [
    "# Dropping patients elder than 99 years of age and younger than 15 years of age. so as to get data of adults."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "230f6266",
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>new1</th>\n",
       "      <th>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>new</th>\n",
       "      <th>age_at_adm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>7/9/2682</td>\n",
       "      <td>2682-09-07</td>\n",
       "      <td>M</td>\n",
       "      <td>28/2/2606</td>\n",
       "      <td>2606-02-28</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>12532</td>\n",
       "      <td>26/9/2875</td>\n",
       "      <td>2875-09-26</td>\n",
       "      <td>M</td>\n",
       "      <td>14/5/2803</td>\n",
       "      <td>2803-05-14</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>29/10/3138</td>\n",
       "      <td>3138-10-29</td>\n",
       "      <td>M</td>\n",
       "      <td>22/5/3051</td>\n",
       "      <td>3051-05-22</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>3/3/3079</td>\n",
       "      <td>3079-03-03</td>\n",
       "      <td>M</td>\n",
       "      <td>2/3/3007</td>\n",
       "      <td>3007-03-02</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>15325</td>\n",
       "      <td>21/8/2678</td>\n",
       "      <td>2678-08-21</td>\n",
       "      <td>M</td>\n",
       "      <td>17/5/2606</td>\n",
       "      <td>2606-05-17</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3947</th>\n",
       "      <td>32686</td>\n",
       "      <td>30102</td>\n",
       "      <td>27/5/2612</td>\n",
       "      <td>2612-05-27</td>\n",
       "      <td>M</td>\n",
       "      <td>6/12/2543</td>\n",
       "      <td>2543-12-06</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3948</th>\n",
       "      <td>32705</td>\n",
       "      <td>33462</td>\n",
       "      <td>6/4/3337</td>\n",
       "      <td>3337-04-06</td>\n",
       "      <td>F</td>\n",
       "      <td>5/3/3257</td>\n",
       "      <td>3257-03-05</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3949</th>\n",
       "      <td>32706</td>\n",
       "      <td>35651</td>\n",
       "      <td>20/3/2676</td>\n",
       "      <td>2676-03-20</td>\n",
       "      <td>M</td>\n",
       "      <td>14/2/2595</td>\n",
       "      <td>2595-02-14</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3950</th>\n",
       "      <td>32711</td>\n",
       "      <td>36069</td>\n",
       "      <td>20/5/3143</td>\n",
       "      <td>3143-05-20</td>\n",
       "      <td>F</td>\n",
       "      <td>21/12/3057</td>\n",
       "      <td>3057-12-21</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3951</th>\n",
       "      <td>32763</td>\n",
       "      <td>33007</td>\n",
       "      <td>5/6/2724</td>\n",
       "      <td>2724-06-05</td>\n",
       "      <td>M</td>\n",
       "      <td>10/4/2645</td>\n",
       "      <td>2645-04-10</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3923 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id  hadm_id     admit_dt        new1 sex          dob  \\\n",
       "0              3     2075    7/9/2682   2682-09-07   M   28/2/2606    \n",
       "1             12    12532   26/9/2875   2875-09-26   M   14/5/2803    \n",
       "2             21    20666  29/10/3138   3138-10-29   M   22/5/3051    \n",
       "3             26    15067    3/3/3079   3079-03-03   M    2/3/3007    \n",
       "4             31    15325   21/8/2678   2678-08-21   M   17/5/2606    \n",
       "...          ...      ...          ...         ...  ..          ...   \n",
       "3947       32686    30102   27/5/2612   2612-05-27   M   6/12/2543    \n",
       "3948       32705    33462    6/4/3337   3337-04-06   F    5/3/3257    \n",
       "3949       32706    35651   20/3/2676   2676-03-20   M   14/2/2595    \n",
       "3950       32711    36069   20/5/3143   3143-05-20   F  21/12/3057    \n",
       "3951       32763    33007    5/6/2724   2724-06-05   M   10/4/2645    \n",
       "\n",
       "             new  age_at_adm  \n",
       "0     2606-02-28        76.0  \n",
       "1     2803-05-14        72.0  \n",
       "2     3051-05-22        87.0  \n",
       "3     3007-03-02        72.0  \n",
       "4     2606-05-17        72.0  \n",
       "...          ...         ...  \n",
       "3947  2543-12-06        68.0  \n",
       "3948  3257-03-05        80.0  \n",
       "3949  2595-02-14        81.0  \n",
       "3950  3057-12-21        85.0  \n",
       "3951  2645-04-10        79.0  \n",
       "\n",
       "[3923 rows x 8 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7.drop(df7[df7[\"age_at_adm\"]<=15].index, inplace = True)\n",
    "df7"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57f43751",
   "metadata": {},
   "source": [
    "# Dropping redundant columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "cd9ecf27",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.drop([\"admit_dt\", \"dob\"], axis =1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "168e3577",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.rename(columns = {\"new1\":\"admit_dt\", \"new\": \"dob\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "e3938cde",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.reset_index(drop = True, inplace = True )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "53f84128",
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>admit_dt</th>\n",
       "      <th>sex</th>\n",
       "      <th>dob</th>\n",
       "      <th>age_at_adm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>2682-09-07</td>\n",
       "      <td>M</td>\n",
       "      <td>2606-02-28</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>12532</td>\n",
       "      <td>2875-09-26</td>\n",
       "      <td>M</td>\n",
       "      <td>2803-05-14</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>3138-10-29</td>\n",
       "      <td>M</td>\n",
       "      <td>3051-05-22</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>3079-03-03</td>\n",
       "      <td>M</td>\n",
       "      <td>3007-03-02</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>15325</td>\n",
       "      <td>2678-08-21</td>\n",
       "      <td>M</td>\n",
       "      <td>2606-05-17</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3918</th>\n",
       "      <td>32686</td>\n",
       "      <td>30102</td>\n",
       "      <td>2612-05-27</td>\n",
       "      <td>M</td>\n",
       "      <td>2543-12-06</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3919</th>\n",
       "      <td>32705</td>\n",
       "      <td>33462</td>\n",
       "      <td>3337-04-06</td>\n",
       "      <td>F</td>\n",
       "      <td>3257-03-05</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3920</th>\n",
       "      <td>32706</td>\n",
       "      <td>35651</td>\n",
       "      <td>2676-03-20</td>\n",
       "      <td>M</td>\n",
       "      <td>2595-02-14</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3921</th>\n",
       "      <td>32711</td>\n",
       "      <td>36069</td>\n",
       "      <td>3143-05-20</td>\n",
       "      <td>F</td>\n",
       "      <td>3057-12-21</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3922</th>\n",
       "      <td>32763</td>\n",
       "      <td>33007</td>\n",
       "      <td>2724-06-05</td>\n",
       "      <td>M</td>\n",
       "      <td>2645-04-10</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3923 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      subject_id  hadm_id    admit_dt sex         dob  age_at_adm\n",
       "0              3     2075  2682-09-07   M  2606-02-28        76.0\n",
       "1             12    12532  2875-09-26   M  2803-05-14        72.0\n",
       "2             21    20666  3138-10-29   M  3051-05-22        87.0\n",
       "3             26    15067  3079-03-03   M  3007-03-02        72.0\n",
       "4             31    15325  2678-08-21   M  2606-05-17        72.0\n",
       "...          ...      ...         ...  ..         ...         ...\n",
       "3918       32686    30102  2612-05-27   M  2543-12-06        68.0\n",
       "3919       32705    33462  3337-04-06   F  3257-03-05        80.0\n",
       "3920       32706    35651  2676-03-20   M  2595-02-14        81.0\n",
       "3921       32711    36069  3143-05-20   F  3057-12-21        85.0\n",
       "3922       32763    33007  2724-06-05   M  2645-04-10        79.0\n",
       "\n",
       "[3923 rows x 6 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "cdc2f4f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.to_csv(\"selected_ids.csv\", index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}