find-headings.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import os\n",
    "db_path = 'data/mimic-iv-clinical-database-demo-2.2/collated_data.db'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'admissions': ['subject_id', 'hadm_id', 'admittime', 'dischtime', 'deathtime', 'admission_type', 'admit_provider_id', 'admission_location', 'discharge_location', 'insurance', 'language', 'marital_status', 'race', 'edregtime', 'edouttime', 'hospital_expire_flag'], 'diagnoses_icd': ['subject_id', 'hadm_id', 'seq_num', 'icd_code', 'icd_version'], 'drgcodes': ['subject_id', 'hadm_id', 'drg_type', 'drg_code', 'description', 'drg_severity', 'drg_mortality'], 'd_hcpcs': ['code', 'category', 'long_description', 'short_description'], 'd_icd_diagnoses': ['icd_code', 'icd_version', 'long_title'], 'd_icd_procedures': ['icd_code', 'icd_version', 'long_title'], 'd_labitems': ['itemid', 'label', 'fluid', 'category'], 'emar': ['subject_id', 'hadm_id', 'emar_id', 'emar_seq', 'poe_id', 'pharmacy_id', 'enter_provider_id', 'charttime', 'medication', 'event_txt', 'scheduletime', 'storetime'], 'emar_detail': ['subject_id', 'emar_id', 'emar_seq', 'parent_field_ordinal', 'administration_type', 'pharmacy_id', 'barcode_type', 'reason_for_no_barcode', 'complete_dose_not_given', 'dose_due', 'dose_due_unit', 'dose_given', 'dose_given_unit', 'will_remainder_of_dose_be_given', 'product_amount_given', 'product_unit', 'product_code', 'product_description', 'product_description_other', 'prior_infusion_rate', 'infusion_rate', 'infusion_rate_adjustment', 'infusion_rate_adjustment_amount', 'infusion_rate_unit', 'route', 'infusion_complete', 'completion_interval', 'new_iv_bag_hung', 'continued_infusion_in_other_location', 'restart_interval', 'side', 'site', 'non_formulary_visual_verification'], 'hcpcsevents': ['subject_id', 'hadm_id', 'chartdate', 'hcpcs_cd', 'seq_num', 'short_description'], 'labevents': ['labevent_id', 'subject_id', 'hadm_id', 'specimen_id', 'itemid', 'order_provider_id', 'charttime', 'storetime', 'value', 'valuenum', 'valueuom', 'ref_range_lower', 'ref_range_upper', 'flag', 'priority', 'comments'], 'microbiologyevents': ['microevent_id', 'subject_id', 'hadm_id', 'micro_specimen_id', 'order_provider_id', 'chartdate', 'charttime', 'spec_itemid', 'spec_type_desc', 'test_seq', 'storedate', 'storetime', 'test_itemid', 'test_name', 'org_itemid', 'org_name', 'isolate_num', 'quantity', 'ab_itemid', 'ab_name', 'dilution_text', 'dilution_comparison', 'dilution_value', 'interpretation', 'comments'], 'omr': ['subject_id', 'chartdate', 'seq_num', 'result_name', 'result_value'], 'patients': ['subject_id', 'gender', 'anchor_age', 'anchor_year', 'anchor_year_group', 'dod'], 'pharmacy': ['subject_id', 'hadm_id', 'pharmacy_id', 'poe_id', 'starttime', 'stoptime', 'medication', 'proc_type', 'status', 'entertime', 'verifiedtime', 'route', 'frequency', 'disp_sched', 'infusion_type', 'sliding_scale', 'lockout_interval', 'basal_rate', 'one_hr_max', 'doses_per_24_hrs', 'duration', 'duration_interval', 'expiration_value', 'expiration_unit', 'expirationdate', 'dispensation', 'fill_quantity'], 'poe': ['poe_id', 'poe_seq', 'subject_id', 'hadm_id', 'ordertime', 'order_type', 'order_subtype', 'transaction_type', 'discontinue_of_poe_id', 'discontinued_by_poe_id', 'order_provider_id', 'order_status'], 'poe_detail': ['poe_id', 'poe_seq', 'subject_id', 'field_name', 'field_value'], 'prescriptions': ['subject_id', 'hadm_id', 'pharmacy_id', 'poe_id', 'poe_seq', 'order_provider_id', 'starttime', 'stoptime', 'drug_type', 'drug', 'formulary_drug_cd', 'gsn', 'ndc', 'prod_strength', 'form_rx', 'dose_val_rx', 'dose_unit_rx', 'form_val_disp', 'form_unit_disp', 'doses_per_24_hrs', 'route'], 'procedures_icd': ['subject_id', 'hadm_id', 'seq_num', 'chartdate', 'icd_code', 'icd_version'], 'provider': ['provider_id'], 'services': ['subject_id', 'hadm_id', 'transfertime', 'prev_service', 'curr_service'], 'transfers': ['subject_id', 'hadm_id', 'transfer_id', 'eventtype', 'careunit', 'intime', 'outtime']}\n"
     ]
    }
   ],
   "source": [
    "\n",
    "def get_table_headings(db_path):\n",
    "    # Connect to the SQLite database\n",
    "    conn = sqlite3.connect(db_path)\n",
    "    cursor = conn.cursor()\n",
    "    \n",
    "    # Get the list of all tables\n",
    "    cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "    tables = cursor.fetchall()\n",
    "  \n",
    "    table_headings = {}\n",
    "    \n",
    "    # Iterate through the tables and get the headings\n",
    "    for table in tables:\n",
    "        table_name = table[0]\n",
    "        # cursor.execute(f\"PRAGMA table_info({table_name});\")\n",
    "        # columns = cursor.fetchall()\n",
    "        # headings = [column[1] for column in columns]\n",
    "        cursor = conn.execute('select * from ' + table_name)\n",
    "        headings = [description[0] for description in cursor.description]\n",
    "        table_headings[table_name] = headings\n",
    "    \n",
    "    # Close the connection\n",
    "    conn.close()\n",
    "    \n",
    "    return table_headings\n",
    "\n",
    "headings = get_table_headings(db_path)\n",
    "print(headings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of tables\n",
    "len(os.listdir('data/mimic-iv-clinical-database-demo-2.2/hosp'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find out which tables contain the subject_id column\n",
    "tables_with_subj_id_in = [a for a in headings if 'subject_id' in headings[a]]\n",
    "tables_without_subj_id_in = [a for a in headings if 'subject_id' not in headings[a]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['admissions', 'diagnoses_icd', 'drgcodes', 'emar', 'emar_detail', 'hcpcsevents', 'labevents', 'microbiologyevents', 'omr', 'patients', 'pharmacy', 'poe', 'poe_detail', 'prescriptions', 'procedures_icd', 'services', 'transfers']\n",
      "['d_hcpcs', 'd_icd_diagnoses', 'd_icd_procedures', 'd_labitems', 'provider']\n"
     ]
    }
   ],
   "source": [
    "print(tables_with_subj_id_in)\n",
    "len(tables_with_subj_id_in)\n",
    "print(tables_without_subj_id_in)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F 21 None\n"
     ]
    }
   ],
   "source": [
    "conn=sqlite3.connect(db_path)\n",
    "cursor=conn.cursor()\n",
    "\n",
    "# Truncate table\n",
    "# cursor.execute('SELECT subject_id, gender, anchor_age, dod FROM patients')\n",
    "# selected_cols = cursor.fetchall()\n",
    "# print(selected_cols)\n",
    "\n",
    "# Get ID of first subject\n",
    "cursor.execute('SELECT subject_id FROM patients LIMIT 1')\n",
    "first_subject_id = cursor.fetchone()[0]\n",
    "\n",
    "# Get selected columns\n",
    "cursor.execute('SELECT gender, anchor_age, dod FROM patients WHERE subject_id = ?', (first_subject_id,))\n",
    "gender, anchor_age, dod = cursor.fetchone()\n",
    "print(gender, anchor_age, dod)\n",
    "\n",
    "for t in tables_with_subj_id_in:\n",
    "    cursor.execute(f'SELECT * FROM {t} WHERE subject_id = ?', (first_subject_id,))\n",
    "\n",
    "    output_dir = 'data/mimic-iv-clinical-database-demo-2.2/subject-data'\n",
    "    if not os.path.exists(output_dir):\n",
    "        os.makedirs(output_dir)\n",
    "\n",
    "    # Write output\n",
    "    with open(os.path.join(output_dir, f'{first_subject_id}.txt'), 'a') as f:\n",
    "        for row in cursor.fetchall():\n",
    "            f.write(str(row) + '\\n')\n",
    "\n",
    "    # print(t)\n",
    "    # print(cursor.fetchall())\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iterate over patients and generate output files\n",
    "conn = sqlite3.connect(db_path)\n",
    "cursor=conn.cursor()\n",
    "cursor2=conn.cursor()\n",
    "cursor.execute('SELECT subject_id FROM patients')\n",
    "\n",
    "for patient in cursor:\n",
    "    for t in tables_with_subj_id_in:\n",
    "        cursor2.execute(f'SELECT * FROM {t} WHERE subject_id = ?', (patient[0],))\n",
    "\n",
    "        output_dir = 'data/mimic-iv-clinical-database-demo-2.2/subject-data'\n",
    "        if not os.path.exists(output_dir):\n",
    "            os.makedirs(output_dir)\n",
    "\n",
    "        # Write output\n",
    "        with open(os.path.join(output_dir, f'{patient[0]}.txt'), 'a') as f:\n",
    "            for row in cursor2.fetchall():\n",
    "                f.write(str(row) + '\\n')\n",
    "\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".myvenv",
   "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.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}