{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"![Urgences - Image CC0 - pexels.com](img/pexels-pixabay-263402.jpg \"Urgences\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Challenge - [ED Lab Prediction]\n",
"_Nom à trouver_"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Objectif"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Ce notebook effectue le pre-processing des données. \n",
"Il exploite les données stockées dans la base sqlite, téléchargées à partir de `download_data.py` et les exporte dans un fichier csv à destination de l'entrainement de la data-visualisation l'algorithme."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Chargement des données"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"import sqlite3\n",
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# Sqlite connection\n",
"conn = sqlite3.connect(\"./data/mimic-iv.sqlite\")\n",
"\n",
"# Classification des items de biologie\n",
"items = pd.read_csv(\"./config/lab_items.csv\").dropna()\n",
"items_list = items[\"item_id\"].astype(\"str\").tolist()\n",
"\n",
"# Classification ATC des médicaments\n",
"drugs_rules = pd.read_csv(\"./config/atc_items.csv\")\n",
"drugs_rules_list = drugs_rules[\"gsn\"].drop_duplicates().astype(\"str\").tolist()"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"# Création d'un index pour accélérer les requêtes\n",
"conn.execute(\"CREATE INDEX IF NOT EXISTS biological_index ON labevents (stay_id, itemid)\");"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Stays dataset"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"stays = pd.read_sql(f\"\"\"\n",
" SELECT \n",
" s.stay_id,\n",
" s.intime intime,\n",
" p.gender gender,\n",
" p.anchor_age age,\n",
" t.temperature,\n",
" t.heartrate,\n",
" t.resprate,\n",
" t.o2sat,\n",
" t.sbp,\n",
" t.dbp,\n",
" t.pain,\n",
" t.chiefcomplaint\n",
" FROM edstays s\n",
" LEFT JOIN patients p\n",
" ON p.subject_id = s.subject_id\n",
" LEFT Join triage t\n",
" ON t.stay_id = s.stay_id\n",
"\"\"\", conn)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"stays[\"intime\"] = pd.to_datetime(stays[\"intime\"])\n",
"stays[\"gender\"] = stays[\"gender\"].astype(\"string\") # Pas de valeurs manquantes en gender\n",
"stays[\"chiefcomplaint\"] = stays[\"chiefcomplaint\"].fillna(\"\").astype(\"string\") # ¨Chiefcomplaint manquant = chiefcomplaint vide"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"drugs = pd.read_sql(f\"\"\"\n",
" SELECT stay_id, gsn\n",
" FROM medrecon\n",
" WHERE gsn IN ({','.join(drugs_rules_list)})\n",
"\"\"\", conn)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"# Liste des codes ATC pour chaque séjour\n",
"atc_stays = pd.merge(\n",
" drugs,\n",
" drugs_rules,\n",
" left_on=\"gsn\",\n",
" right_on=\"gsn\"\n",
").drop_duplicates([\"stay_id\",\"atc\"])\n",
"\n",
"atc_stays[\"atc_2\"] = atc_stays[\"atc\"].str.slice(0, 3)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Considérons 2 niveaux de granularité\n",
"## Le code ATC complet (Anatomique, Thérapeutique et Pharmacologique), ATC IV\n",
"\n",
"atc_stays_pivoted_4 = pd.pivot_table(\n",
" atc_stays[[\"stay_id\",\"atc\"]] \\\n",
" .assign(value=1),\n",
" columns=[\"atc\"],\n",
" index=[\"stay_id\"],\n",
" values=\"value\"\n",
").fillna(0).reset_index()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"## Le code ATC 2 (Anatomique et Thérapeutique)\n",
"\n",
"atc_stays_pivoted_2 = pd.pivot_table(\n",
" atc_stays[[\"stay_id\",\"atc_2\"]] \\\n",
" .drop_duplicates() \\\n",
" .rename(columns={\"atc_2\":\"atc\"}) \\\n",
" .assign(value=1),\n",
" columns=[\"atc\"],\n",
" index=[\"stay_id\"],\n",
" values=\"value\"\n",
").fillna(0).reset_index()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"stays_atc_4 = pd.merge(\n",
" stays,\n",
" atc_stays_pivoted_4,\n",
" left_on=\"stay_id\",\n",
" right_on=\"stay_id\",\n",
" how=\"left\"\n",
").fillna(0)\n",
"\n",
"stays_atc_2 = pd.merge(\n",
" stays,\n",
" atc_stays_pivoted_2,\n",
" left_on=\"stay_id\",\n",
" right_on=\"stay_id\",\n",
" how=\"left\"\n",
").fillna(0)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"ename": "NameError",
"evalue": "name 'stays_atc_2' is not defined",
"output_type": "error",
"traceback": [
"\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[1;31mNameError\u001b[0m Traceback (most recent call last)",
"\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_10104/535576780.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m 2\u001b[0m \u001b[1;31m# On écrit en parquet pour optimiser le stockage et les temps d'io\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mstays_atc_2\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"stay_id\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_parquet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"./data/features_atc2.parquet\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"pyarrow\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 5\u001b[0m \u001b[0mstays_atc_4\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"stay_id\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_parquet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"./data/features_atc4.parquet\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"pyarrow\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
"\u001b[1;31mNameError\u001b[0m: name 'stays_atc_2' is not defined"
]
}
],
"source": [
"# Ecriture du featues dataset\n",
"# On écrit en parquet pour optimiser le stockage et les temps d'io\n",
"\n",
"stays_atc_2.sort_values(\"stay_id\").reset_index(drop=True).to_parquet(\"./data/features_atc2.parquet\", engine=\"pyarrow\", index=False)\n",
"stays_atc_4.sort_values(\"stay_id\").reset_index(drop=True).to_parquet(\"./data/features_atc4.parquet\", engine=\"pyarrow\", index=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Lab dataset"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"labs = pd.read_sql(f\"\"\"\n",
" SELECT \n",
" le.stay_id,\n",
" le.itemid item_id\n",
" FROM labevents le\n",
" WHERE le.itemid IN ('{\"','\".join(items_list)}')\n",
" GROUP BY\n",
" le.stay_id,\n",
" le.itemid\n",
"\"\"\", conn)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"labs_deduplicate = pd.merge(\n",
" items[[\"item_id\",\"3\"]].rename(columns={\"3\":\"label\"}),\n",
" labs,\n",
" left_on=\"item_id\",\n",
" right_on=\"item_id\"\n",
") \\\n",
" .drop_duplicates([\"stay_id\", \"label\"])[[\"stay_id\",\"label\"]] \\\n",
" .reset_index(drop=True)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"labs_deduplicate_pivot = pd.pivot_table(\n",
" labs_deduplicate.assign(value=1),\n",
" index=[\"stay_id\"],\n",
" columns=[\"label\"],\n",
" values=\"value\"\n",
").fillna(0)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"labs_deduplicate_pivot_final = labs_deduplicate_pivot.join(\n",
" stays[[\"stay_id\"]].set_index(\"stay_id\"),\n",
" how=\"right\"\n",
").fillna(0).astype(\"int8\").reset_index()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"labs_deduplicate_pivot_final.sort_values(\"stay_id\").reset_index(drop=True).to_parquet(\"./data/labels.parquet\", index=False)"
]
}
],
"metadata": {
"interpreter": {
"hash": "28b293e0c0671e44c7281dde6399c7c7419d3faca031d22494da8635907ada72"
},
"kernelspec": {
"display_name": "Python 3.9.7 ('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.9.7"
},
"orig_nbformat": 4
},
"nbformat": 4,
"nbformat_minor": 2
}