Nom à trouver
Ce notebook effectue le pre-processing des données.
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.
import sqlite3
import pandas as pd
# Sqlite connection
conn = sqlite3.connect("./data/mimic-iv.sqlite")
# Classification des items de biologie
items = pd.read_csv("./config/lab_items.csv").dropna()
items_list = items["item_id"].astype("str").tolist()
# Classification ATC des médicaments
drugs_rules = pd.read_csv("./config/atc_items.csv")
drugs_rules_list = drugs_rules["gsn"].drop_duplicates().astype("str").tolist()
# Création d'un index pour accélérer les requêtes
conn.execute("CREATE INDEX IF NOT EXISTS biological_index ON labevents (stay_id, itemid)");
stays = pd.read_sql(f"""
SELECT
s.stay_id,
s.intime intime,
p.gender gender,
p.anchor_age age,
t.temperature,
t.heartrate,
t.resprate,
t.o2sat,
t.sbp,
t.dbp,
t.pain,
t.chiefcomplaint
FROM edstays s
LEFT JOIN patients p
ON p.subject_id = s.subject_id
LEFT Join triage t
ON t.stay_id = s.stay_id
""", conn)
stays["intime"] = pd.to_datetime(stays["intime"])
stays["gender"] = stays["gender"].astype("string") # Pas de valeurs manquantes en gender
stays["chiefcomplaint"] = stays["chiefcomplaint"].fillna("").astype("string") # ¨Chiefcomplaint manquant = chiefcomplaint vide
drugs = pd.read_sql(f"""
SELECT stay_id, gsn, etccode, 1 n
FROM medrecon
WHERE gsn IN ({','.join(drugs_rules_list)})
""", conn)
# Liste des codes ATC pour chaque séjour
atc_stays = pd.merge(
drugs,
drugs_rules,
left_on="gsn",
right_on="gsn"
).groupby(["stay_id","atc"])["n"].sum() \
.reset_index()
atc_stays["atc_2"] = atc_stays["atc"].str.slice(0, 3)
# Considérons 2 niveaux de granularité
## Le code ATC complet (Anatomique, Thérapeutique et Pharmacologique), ATC IV
atc_stays_pivoted_4 = pd.pivot_table(
atc_stays[["stay_id","atc", "n"]],
columns=["atc"],
index=["stay_id"],
values="n"
).fillna(0).reset_index()
## Le code ATC 2 (Anatomique et Thérapeutique)
atc_stays_pivoted_2 = pd.pivot_table(
atc_stays[["stay_id","atc_2", "n"]] \
.groupby(["stay_id","atc_2"])["n"].sum() \
.reset_index() \
.rename(columns={"atc_2":"atc"}),
columns=["atc"],
index=["stay_id"],
values="n"
).fillna(0).reset_index()
## Les codes ETC
etc_pivoted = pd.pivot_table(
drugs[["stay_id","etccode", "n"]].dropna() \
.assign(etccode = lambda x: x["etccode"].astype("int").astype("str")) \
.groupby(["stay_id","etccode"])["n"].sum() \
.reset_index() \
.rename(columns={"etccode":"atc"}),
columns=["atc"],
index=["stay_id"],
values="n"
).fillna(0).reset_index()
stays_atc_4 = pd.merge(
stays,
atc_stays_pivoted_4,
left_on="stay_id",
right_on="stay_id",
how="left"
)
stays_atc_2 = pd.merge(
stays,
atc_stays_pivoted_2,
left_on="stay_id",
right_on="stay_id",
how="left"
)
stays_etc = pd.merge(
stays,
etc_pivoted,
left_on="stay_id",
right_on="stay_id",
how="left"
)
stays_atc_4[atc_stays_pivoted_4.columns[1:]] = stays_atc_4[atc_stays_pivoted_4.columns[1:]].fillna(0)
stays_atc_2[atc_stays_pivoted_2.columns[1:]] = stays_atc_2[atc_stays_pivoted_2.columns[1:]].fillna(0)
stays_etc[etc_pivoted.columns[1:]] = stays_etc[etc_pivoted.columns[1:]].fillna(0)
# Ecriture du featues dataset
# On écrit en parquet pour optimiser le stockage et les temps d'io
stays_atc_2.sort_values("stay_id").reset_index(drop=True).to_parquet("./data/features_atc2.parquet", engine="pyarrow", index=False)
stays_atc_4.sort_values("stay_id").reset_index(drop=True).to_parquet("./data/features_atc4.parquet", engine="pyarrow", index=False)
stays_etc.sort_values("stay_id").reset_index(drop=True).to_parquet("./data/features_etc.parquet", engine="pyarrow", index=False)
labs = pd.read_sql(f"""
SELECT
le.stay_id,
le.itemid item_id
FROM labevents le
WHERE le.itemid IN ('{"','".join(items_list)}')
GROUP BY
le.stay_id,
le.itemid
""", conn)
[0;31m---------------------------------------------------------------------------[0m [0;31mNameError[0m Traceback (most recent call last) Input [0;32mIn [1][0m, in [0;36m<module>[0;34m[0m [0;32m----> 1[0m labs [38;5;241m=[39m [43mpd[49m[38;5;241m.[39mread_sql([38;5;124mf[39m[38;5;124m"""[39m [1;32m 2[0m [38;5;124m SELECT [39m [1;32m 3[0m [38;5;124m le.stay_id,[39m [1;32m 4[0m [38;5;124m le.itemid item_id[39m [1;32m 5[0m [38;5;124m FROM labevents le[39m [1;32m 6[0m [38;5;124m WHERE le.itemid IN ([39m[38;5;124m'[39m[38;5;132;01m{[39;00m[38;5;124m"[39m[38;5;124m'[39m[38;5;124m,[39m[38;5;124m'[39m[38;5;124m"[39m[38;5;241m.[39mjoin(items_list)[38;5;132;01m}[39;00m[38;5;124m'[39m[38;5;124m)[39m [1;32m 7[0m [38;5;124m GROUP BY[39m [1;32m 8[0m [38;5;124m le.stay_id,[39m [1;32m 9[0m [38;5;124m le.itemid[39m [1;32m 10[0m [38;5;124m"""[39m, conn) [0;31mNameError[0m: name 'pd' is not defined
labs_deduplicate = pd.merge(
items[["item_id","3"]].rename(columns={"3":"label"}),
labs,
left_on="item_id",
right_on="item_id"
) \
.drop_duplicates(["stay_id", "label"])[["stay_id","label"]] \
.reset_index(drop=True)
labs_deduplicate_pivot = pd.pivot_table(
labs_deduplicate.assign(value=1),
index=["stay_id"],
columns=["label"],
values="value"
).fillna(0)
labs_deduplicate_pivot_final = labs_deduplicate_pivot.join(
stays[["stay_id"]].set_index("stay_id"),
how="right"
).fillna(0).astype("int8").reset_index()
labs_deduplicate_pivot_final.sort_values("stay_id").reset_index(drop=True).to_parquet("./data/labels.parquet", index=False)