241104_aspergillus_revision_redacted.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb83897c-0a13-4899-b65a-0dab58338e5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.patheffects\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import itertools\n",
    "import scipy.stats\n",
    "import statsmodels.stats.multitest\n",
    "import statannotations.Annotator\n",
    "import math\n",
    "import os\n",
    "from scipy.stats import mannwhitneyu\n",
    "import sys\n",
    "\n",
    "sys.path.append('./../src/')\n",
    "\n",
    "pd.options.display.max_columns = 200\n",
    "pd.options.display.max_rows = 200\n",
    "mpl.rcParams[\"figure.figsize\"] = (10, 8)\n",
    "mpl.rcParams['pdf.fonttype'] = 42  # edit-able in illustrator\n",
    "\n",
    "\n",
    "import IPython.display\n",
    "IPython.display.display(IPython.display.HTML(\"<style>.container { width:90% !important; }</style>\"))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "343138a4",
   "metadata": {},
   "source": [
    "# load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b65a0989",
   "metadata": {
    "vscode": {
     "languageId": "plaintext"
    }
   },
   "outputs": [],
   "source": [
    "cd = pd.read_csv(\"carpediem.csv\", index_col=0)\n",
    "cd.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "550fe9cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f596a1f2-16f0-4e1d-845b-319a7043a31b",
   "metadata": {},
   "source": [
    "# pulldown new data/updates\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc60594f-28c2-4361-a033-761a12c7670f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pull down categories  \n",
    "\n",
    "red = pd.read_csv(\"redcap_4339_pneumonia_episode_category_assessment.csv\")\n",
    "first_ep = red.loc[red.category_num==1, :]\n",
    "first_ep=first_ep.fillna('')\n",
    "first_ep['type'] = first_ep['clin_cap']+first_ep['clin_hap']+first_ep['clin_vap']\n",
    "first_ep['virus']=first_ep['clin_cap_viral_npop']+first_ep['clin_hap_viral_npop']+first_ep['clin_vap_viral_npop']\n",
    "new_dem = first_ep[['pt_study_id','ir_id','pt_category','virus','type']]\n",
    "new_dem['virus'] = new_dem['virus'].astype(str)\n",
    "new_dem['type'] = new_dem['type'].astype(str)\n",
    "\n",
    "def category(row): \n",
    "    if (row['pt_category'].strip()=='Non-pneumonia control'):\n",
    "        return 'Non-Pneumonia Control'\n",
    "    elif 'SARS-Cov-2' in row['virus']:\n",
    "        return 'COVID-19'\n",
    "    elif 'Influenza' in row['virus']:\n",
    "        return 'Influenza'\n",
    "    elif (row['type'].strip()=='Viral/Etiology defined'):\n",
    "        return 'Other Viral Pneumonia'\n",
    "    elif (row['type'].strip()=='Bacterial/viral co-infection'):\n",
    "        return 'Other Viral Pneumonia'    \n",
    "    else:\n",
    "        return 'Other Pneumonia'\n",
    "new_dem['Patient_category'] = new_dem.apply(category, axis=1)\n",
    "\n",
    "new_dem['COVID_status'] = np.where(new_dem['Patient_category'] == 'COVID-19', True, False)\n",
    "new_dem['Influenza_status'] = np.where(new_dem['Patient_category'] == 'Influenza', True, False)\n",
    "\n",
    "new_dem = new_dem[['pt_study_id','Patient_category', 'COVID_status','Influenza_status']]\n",
    "cd = pd.merge(cd, new_dem, how='left', left_on=['patient'], right_on='pt_study_id')\n",
    "\n",
    "#rename columns\n",
    "cd.drop(columns=['Patient_category_x'], inplace=True)\n",
    "\n",
    "# Rename the 'Patient_category_y' column to 'Patient_category'\n",
    "cd.rename(columns={'Patient_category_y': 'Patient_category'}, inplace=True)\n",
    "\n",
    "#rename columns\n",
    "cd.drop(columns=['COVID_status_x'], inplace=True)\n",
    "\n",
    "cd.rename(columns={'COVID_status_y': 'COVID_status'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75cdc96f",
   "metadata": {},
   "source": [
    "# new immunocomp data/breakout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d3e8e68-b876-406c-8439-0653bb13c2b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pull down immunocompromised flag \n",
    "\n",
    "dem = pd.read_csv(\"redcap_4339_demographics.csv\")\n",
    "\n",
    "dem = dem[[ 'pt_study_id','pt_immunocomp', 'type_immunocomp','organ_transplant', ]]\n",
    "dem=dem.rename(columns={'pt_study_id':'patient'})\n",
    "dem=dem.rename(columns={'pt_immunocomp':'Immunocompromised_flag'})\n",
    "\n",
    "def create_additional_columns(df):\n",
    "    # Initialize new columns with False\n",
    "    df['solid_organ_transplant'] = False\n",
    "    df['stem_cell_transplant'] = False\n",
    "    df['acute_leukemia'] = False\n",
    "    df['chemotherapy'] = False\n",
    "    \n",
    "    # Check for 'Solid organ transplant' and 'Acute leukemia' in 'type_immunocomp' column\n",
    "    df['solid_organ_transplant'] = df['type_immunocomp'].str.contains('Solid organ transplant', case=False)\n",
    "    df['stem_cell_transplant'] = df['type_immunocomp'].str.contains('Stem cell transplant', case=False)\n",
    "    df['acute_leukemia'] = df['type_immunocomp'].str.contains('Acute leukemia', case=False)\n",
    "    df['chemotherapy'] = df['type_immunocomp'].str.contains('Myelosuppressive chemotherapy', case=False)\n",
    "\n",
    "    return df\n",
    "\n",
    "# Apply the function to the 'dem' DataFrame\n",
    "dem = create_additional_columns(dem)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c448bda-9abf-49b9-8b25-784048b91f0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd = pd.merge(cd, dem, how='left')\n",
    "cd[['chemotherapy','acute_leukemia','stem_cell_transplant','solid_organ_transplant']]=cd[['chemotherapy','acute_leukemia','stem_cell_transplant','solid_organ_transplant']].fillna(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1aaf1323",
   "metadata": {},
   "outputs": [],
   "source": [
    "wbc_over_admission = cd.groupby('patient').agg({'Neutrophils':'min', 'WBC_count':'min'}).reset_index()\n",
    "wbc_over_admission['neutropenic_during_admission'] = np.where(wbc_over_admission['Neutrophils']<1, True, False)\n",
    "wbc_over_admission= wbc_over_admission[['patient', 'neutropenic_during_admission']]\n",
    "cd = pd.merge(cd, wbc_over_admission, how='left', on='patient')\n",
    "\n",
    "median_wbc_over_admission = cd.groupby('patient').agg({'WBC_count':'median', 'Neutrophils':'median'}).reset_index().rename(columns={'WBC_count':\"median_WBC_over_admission\",'Neutrophils':\"median_PMNs_over_admission\"})\n",
    "cd = pd.merge(cd, median_wbc_over_admission, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45873292",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c71bb223",
   "metadata": {},
   "source": [
    "# serum galactomannan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d0d0faf",
   "metadata": {},
   "outputs": [],
   "source": [
    "serumgm = pd.read_excel(\"SCRIPT blood Aspegillus Galactomannan.xlsx\")\n",
    "serumgm = serumgm[['pt study id','specimen taken datetime','result txt']]\n",
    "# Define a function to clean the 'gm' column\n",
    "def clean_galactomannan(column):\n",
    "    column = column.str.replace('>=', '')\n",
    "    column = column.str.replace('>', '')\n",
    "    column = column.str.replace(' ', '')\n",
    "    column = column.str.replace('Negative', '')\n",
    "    column = column.str.replace('Positive', '')\n",
    "    column = column.str.replace(' ', '')\n",
    "    return column\n",
    "\n",
    "serumgm['result txt'] = clean_galactomannan(serumgm['result txt'])\n",
    "serumgm['serumgm']=pd.to_numeric(serumgm['result txt'], errors='coerce')\n",
    "\n",
    "\n",
    "#recode errors based on Epic checks \n",
    "serumgm.loc[serumgm['serumgm']==2.60, 'serumgm'] = np.nan\n",
    "serumgm.loc[serumgm['serumgm']==1.09, 'serumgm']= np.nan\n",
    "serumgm.loc[serumgm['serumgm']==10.07, 'serumgm']= np.nan\n",
    "\n",
    "serumgm['specimen taken datetime']=pd.to_datetime(serumgm['specimen taken datetime'])\n",
    "serumgm['day_bucket_starts']=serumgm['specimen taken datetime'].dt.date\n",
    "serumgm['day_bucket_starts']=pd.to_datetime(serumgm['day_bucket_starts'])\n",
    "serumgm= serumgm.dropna()\n",
    "serumgm = serumgm.drop(columns=['specimen taken datetime','result txt'])\n",
    "serumgm = serumgm.rename(columns={'pt study id':'patient'})\n",
    "serumgm = serumgm.drop_duplicates(subset=['patient', 'day_bucket_starts'])\n",
    "serumgm.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a1f4f76",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['day_bucket_starts']=pd.to_datetime(cd['day_bucket_starts'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6afe23a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd = pd.merge(cd, serumgm, how='left', on=['patient', 'day_bucket_starts'])\n",
    "cd.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccd982ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.serumgm.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e18935a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungitell = pd.read_csv(\"SCRIPT Aspegillus Galactomannan.csv\")\n",
    "#only select fungitell results\n",
    "fungitell = fungitell[fungitell.procedure_name=='FUNGITELL(1-3)-BETA-D-GLUCAN ASSAY']\n",
    "\n",
    "# Define a function to clean the 'gm' column\n",
    "def clean_column(column):\n",
    "    column = column.str.replace('>=', '')\n",
    "    column = column.str.replace('>', '')\n",
    "    column = column.str.replace(' ', '')\n",
    "    column = column.str.replace('<', '')\n",
    "    column = column.str.replace('Negative', '')\n",
    "    column = column.str.replace('Positive', '')\n",
    "    column = column.str.replace('NEGATIVE', '')\n",
    "    column = column.str.replace('POSITIVE', '')\n",
    "    column = column.str.replace(' ', '')\n",
    "    return column\n",
    "\n",
    "fungitell['result_cleaned'] = clean_column(fungitell['result_txt'])\n",
    "\n",
    "fungitell['result_numeric']=pd.to_numeric(fungitell['result_cleaned'], errors='coerce')\n",
    "fungitell['fungitell'] = fungitell['result_numeric']\n",
    "\n",
    "fungitell['day_bucket_starts'] = pd.to_datetime(pd.to_datetime(fungitell.specimen_taken_datetime).dt.date)\n",
    "fungitell= fungitell[['pt_study_id', 'day_bucket_starts','fungitell']]\n",
    "fungitell=fungitell.dropna()\n",
    "fungitell = fungitell.rename(columns={'pt_study_id':'patient'})\n",
    "\n",
    "fungitell.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "663aadea",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd = pd.merge(cd, fungitell, how='left', on=['patient', 'day_bucket_starts'])\n",
    "cd.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f17616b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.fungitell.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e79aa52",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.serumgm.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18a442fd-9704-4c28-920f-d84b2d4151a5",
   "metadata": {},
   "source": [
    "# add medication data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3570810-27d2-4e80-b8be-d62b3455199d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in meds results\n",
    "meds = pd.read_csv('medication_administration.csv.gz', encoding= 'unicode_escape')\n",
    "antifungal=meds[meds['catalog_type']=='Antifungal']\n",
    "antifungal = antifungal[['patient_ir_id','base_medication_name','administration_date',]].drop_duplicates() #just care yes/no for each day\n",
    "# antifungal.pivot_table(values='base_medication_name', index=[\"patient_ir_id\", \"administration_date\"]).reset_index()\n",
    "# antifungal = antifungal.pivot_table(values='base_medication_name', index=[\"patient_ir_id\", \"administration_date\"]).reset_index()\n",
    "antifungal['received_antifungal_thisday'] = 1\n",
    "antifungal = antifungal[['patient_ir_id', 'administration_date', 'received_antifungal_thisday']].drop_duplicates()\n",
    "\n",
    "#link study ID to ir_id\n",
    "patient = pd.read_csv('patient.csv.gz')\n",
    "patient = patient.rename(columns={'case_number':'patient'})\n",
    "patient = patient[['patient_ir_id','patient']]\n",
    "cd = pd.merge(cd, patient, how='left', on='patient')\n",
    "\n",
    "#joinback\n",
    "cd['day_bucket_starts'] = pd.to_datetime(cd['day_bucket_starts'])\n",
    "antifungal.administration_date = pd.to_datetime(antifungal.administration_date)\n",
    "antifungal=antifungal.rename(columns={'administration_date':'day_bucket_starts'})\n",
    "\n",
    "cd = pd.merge(cd, antifungal, how='left', on=['patient_ir_id','day_bucket_starts'])\n",
    "\n",
    "#summarize over each admission\n",
    "received_af_during_admission = cd.groupby('patient').agg(received_antifungal_thisadmission=('received_antifungal_thisday', 'max')).reset_index()\n",
    "\n",
    "cd = pd.merge(cd, received_af_during_admission, how='left', on='patient')\n",
    "\n",
    "cd.received_antifungal_thisday=cd.received_antifungal_thisday.fillna(0)\n",
    "cd.received_antifungal_thisadmission=cd.received_antifungal_thisadmission.fillna(0)\n",
    "\n",
    "cd['days_of_antifungal_bytoday'] = cd.groupby('patient')['received_antifungal_thisday'].cumsum().values\n",
    "cd['sum_steroids_bytoday'] = cd.groupby('patient')['Steroid_dose'].cumsum().values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2384b47d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7017559d",
   "metadata": {},
   "source": [
    "## only anti-asp antifungal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63774483",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in meds results\n",
    "meds = pd.read_csv('medication_administration.csv.gz', encoding= 'unicode_escape')\n",
    "antifungal=meds[meds['catalog_type']=='Antifungal']\n",
    "antifungal = antifungal[['patient_ir_id','base_medication_name','administration_date',]].drop_duplicates() #just care yes/no for each day\n",
    "anti_asp= antifungal[antifungal['base_medication_name'].str.contains(\n",
    "    'posaconazole|voriconazole|Isavuconazonium|amphotericin', case=False, na=False)]\n",
    "anti_asp['received_antiasp_antifungals'] =1\n",
    "anti_asp = anti_asp[['patient_ir_id', 'administration_date','received_antiasp_antifungals']].drop_duplicates()\n",
    "anti_asp.administration_date = pd.to_datetime(anti_asp.administration_date)\n",
    "anti_asp=anti_asp.rename(columns={'administration_date':'day_bucket_starts'})\n",
    "\n",
    "cd = pd.merge(cd, anti_asp, how='left', on=['patient_ir_id','day_bucket_starts'])\n",
    "\n",
    "cd.shape\n",
    "\n",
    "#summarize over each admission\n",
    "received_antiasp_thisadmission = cd.groupby('patient').agg(received_antiasp_thisadmission=('received_antiasp_antifungals', 'max')).reset_index()\n",
    "\n",
    "cd = pd.merge(cd, received_antiasp_thisadmission, how='left', on='patient')\n",
    "\n",
    "cd.received_antiasp_antifungals=cd.received_antiasp_antifungals.fillna(0)\n",
    "cd.received_antiasp_thisadmission=cd.received_antiasp_thisadmission.fillna(0)\n",
    "\n",
    "cd['days_of_antiasp_bytoday'] = cd.groupby('patient')['received_antiasp_antifungals'].cumsum().values\n",
    "\n",
    "cd.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d47d5e9-94bc-43f9-bb50-437e0fc2c65b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#count days after first ICU day\n",
    "\n",
    "cd.day_bucket_starts = pd.to_datetime(cd.day_bucket_starts)\n",
    "first_icu_stay_day = cd.groupby(['patient']).agg({\"day_bucket_starts\": \"min\"}).rename(columns={'day_bucket_starts':'first_icu_date'})\n",
    "last_icu_stay_day = cd.groupby(['patient']).agg({\"day_bucket_starts\": \"max\"}).rename(columns={'day_bucket_starts':'last_icu_date'})\n",
    "cd = pd.merge(cd, first_icu_stay_day, how='left', on='patient')\n",
    "cd = pd.merge(cd, last_icu_stay_day, how='left', on='patient')\n",
    "cd['day_after_first_icu_day'] = cd.day_bucket_starts-cd.first_icu_date\n",
    "cd['day_after_first_icu_day']=cd['day_after_first_icu_day'].dt.days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f1ba0c2-8002-41cb-953e-5cf740e636d5",
   "metadata": {},
   "source": [
    "# was fungal culture sent "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12bdd3bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "bal = pd.read_excel(\"SCRIPT BAL Results_1-9-24.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d21a325",
   "metadata": {},
   "outputs": [],
   "source": [
    "bal.culture_fungal_w_smear_bal_organism_id_1.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7c29555",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungal_sent = bal[['ir_id', 'BAL_collection_date','culture_fungal_w_smear_bal_organism_id_1',]].dropna()\n",
    "fungal_sent['fungal_culture_done']=1\n",
    "fungal_sent=fungal_sent.drop_duplicates(subset=['ir_id', 'BAL_collection_date',])\n",
    "\n",
    "fungal_sent['BAL_collection_date']=pd.to_datetime(fungal_sent['BAL_collection_date'])\n",
    "cd = pd.merge(cd, fungal_sent, how='left', left_on=['patient_ir_id','day_bucket_starts'], right_on=['ir_id','BAL_collection_date'])\n",
    "\n",
    "cd.fungal_culture_done.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e774bf74-c9fa-4d81-a978-19bfc36d9910",
   "metadata": {},
   "source": [
    "# new columns of interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db34e527-4716-446b-810c-f814af267297",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['days_on_ventilator'] = cd.groupby('patient')['Intubation_flag'].cumsum().values\n",
    "cd['summed_nat_score_to_today'] = cd.groupby('patient')['NAT_score'].cumsum().values\n",
    "cd['received_abx_thisday'] = np.where(cd['NAT_score'] == -2, False, True)\n",
    "cd['summed_days_of_abx_to_today'] = cd.groupby('patient')['received_abx_thisday'].cumsum().values\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "985711ab-043c-4023-80b7-8b2ed7208865",
   "metadata": {},
   "outputs": [],
   "source": [
    "def mean_nat_score(patient_df):\n",
    "    patient_df['n_days'] = range(1, patient_df.shape[0] + 1)\n",
    "    return patient_df.summed_nat_score_to_today / patient_df.n_days\n",
    "cd['mean_nat_score_to_today'] =cd.groupby('patient').apply(mean_nat_score).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3ca8493-c386-4fdb-89d6-66880f555b87",
   "metadata": {},
   "outputs": [],
   "source": [
    "def flag_first_row_above_05(dataframe, threshold=0.5):\n",
    "    flagged_row_index = None\n",
    "\n",
    "    for index, row in dataframe.iterrows():\n",
    "        if row['BAL_galactomannan'] > threshold:\n",
    "            flagged_row_index = index\n",
    "            break  # Exit the loop after the first match\n",
    "    \n",
    "    if flagged_row_index is not None:\n",
    "        # Create a new column 'flagged_row' where the first matching row is 'Yes' and others are 'No'\n",
    "        dataframe['first_elevated_BAL_GM_05'] = 'No'\n",
    "        dataframe.at[flagged_row_index, 'first_elevated_BAL_GM_05'] = 'Yes'\n",
    "    else:\n",
    "        # If no row matches the condition, set 'flagged_row' to 'No' for all rows\n",
    "        dataframe['first_elevated_BAL_GM_05'] = 'No'\n",
    "\n",
    "    return dataframe\n",
    "\n",
    "cd = cd.groupby('patient').apply(flag_first_row_above_05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "779a19cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def flag_first_row_above_1(dataframe, threshold=1):\n",
    "    flagged_row_index = None\n",
    "\n",
    "    for index, row in dataframe.iterrows():\n",
    "        if row['BAL_galactomannan'] > threshold:\n",
    "            flagged_row_index = index\n",
    "            break  # Exit the loop after the first match\n",
    "    \n",
    "    if flagged_row_index is not None:\n",
    "        # Create a new column 'flagged_row' where the first matching row is 'Yes' and others are 'No'\n",
    "        dataframe['first_elevated_BAL_GM_1'] = 'No'\n",
    "        dataframe.at[flagged_row_index, 'first_elevated_BAL_GM_1'] = 'Yes'\n",
    "    else:\n",
    "        # If no row matches the condition, set 'flagged_row' to 'No' for all rows\n",
    "        dataframe['first_elevated_BAL_GM_1'] = 'No'\n",
    "\n",
    "    return dataframe\n",
    "\n",
    "cd = cd.groupby('patient').apply(flag_first_row_above_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1adf64bf-03f6-423c-af17-84b1f93bf54e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_elevated_BAL_GM_05=='Yes'].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70ad7e8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_elevated_BAL_GM_1=='Yes'].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93697902-e24a-4c1c-9d60-924a8989c791",
   "metadata": {},
   "outputs": [],
   "source": [
    "def flag_first_row_aspergillus_growth(dataframe):\n",
    "    flagged_row_index = None\n",
    "\n",
    "    for index, row in dataframe.iterrows():\n",
    "        if row['Pathogen_aspergillus_detected'] == True:\n",
    "            flagged_row_index = index\n",
    "            break  # Exit the loop after the first match\n",
    "    \n",
    "    if flagged_row_index is not None:\n",
    "        # Create a new column 'flagged_row' where the first matching row is 'Yes' and others are 'No'\n",
    "        dataframe['first_row_aspergillus_growth'] = 'No'\n",
    "        dataframe.at[flagged_row_index, 'first_row_aspergillus_growth'] = 'Yes'\n",
    "    else:\n",
    "        # If no row matches the condition, set 'flagged_row' to 'No' for all rows\n",
    "        dataframe['first_row_aspergillus_growth'] = 'No'\n",
    "\n",
    "    return dataframe\n",
    "\n",
    "cd = cd.groupby('patient').apply(flag_first_row_aspergillus_growth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8268a187-0a62-40e5-9f0f-779aff286389",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.BAL_galactomannan.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "767dd7de-8263-4e43-8a68-4586406d381d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.Pathogen_aspergillus_detected.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4406da4d-609a-48cd-91c6-7d62c859fad9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.BAL_performed.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d65d37e-80ea-447d-87e4-02ea2b436c58",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.groupby('Pathogen_aspergillus_detected')['BAL_galactomannan'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2e6cbef-c2f3-4b16-b1f8-8f60f0437cc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#flag as long as one BAL grew aspergillus\n",
    "grew_asp_during_admission = cd.sort_values(by=['Pathogen_aspergillus_detected'], ascending=False).drop_duplicates(subset=['patient'], keep='first')\n",
    "\n",
    "#columns of interest\n",
    "grew_asp_during_admission = grew_asp_during_admission[['patient', 'Pathogen_aspergillus_detected']]\n",
    "\n",
    "#rename \n",
    "grew_asp_during_admission=grew_asp_during_admission.rename(columns={'Pathogen_aspergillus_detected':'grew_asp_during_admission'})\n",
    "\n",
    "#join back to main df\n",
    "cd = pd.merge(cd, grew_asp_during_admission, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "728e4785-bb41-4ca4-9baa-150eec0bcb68",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['gm_greaterthan_1'] = np.where(cd['BAL_galactomannan']>1.0, 1, 0)\n",
    "cd['gm_greaterthan_08'] = np.where(cd['BAL_galactomannan']>0.8, 1, 0)\n",
    "cd['gm_greaterthan_05'] = np.where(cd['BAL_galactomannan']>0.5, 1, 0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21ca024f-7589-41d7-94f7-add60db45ff2",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['gm_greaterthan_1'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33abf42c-812a-4db6-9497-5069af0525d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['gm_greaterthan_08'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7882c8b1-444b-4ecf-9681-e6e818518187",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['gm_greaterthan_05'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0757853-b724-495f-ae7e-24edde9c9ac4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[(cd['gm_greaterthan_1']==1)&(cd['Pathogen_aspergillus_detected']==1)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd9f177c-2b39-41de-a8d9-98a9aee5b605",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[(cd['gm_greaterthan_08']==1)&(cd['Pathogen_aspergillus_detected']==1)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fd3332b-4e0b-4372-8f17-80d63509fcd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[(cd['gm_greaterthan_05']==1)&(cd['Pathogen_aspergillus_detected']==1)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b337acd9-bb60-499e-9216-7e40e861ce82",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_grew = cd[(cd['grew_asp_during_admission']==1)][['patient', 'BAL_galactomannan','day_bucket_starts']].sort_values(by='patient', ascending=True).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4993d85a-c584-4cb1-996c-f5d6118914ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_grew[gm_grew.BAL_galactomannan>1].patient.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb413e5e-4bb2-4693-bc4a-ec0d6fd5ce9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_grew[gm_grew.BAL_galactomannan>0.8].patient.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da4f804f-db86-46f5-b5ae-8a425f6f9ca4",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_grew[gm_grew.BAL_galactomannan>0.5].patient.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74931122-387b-4221-a698-7f1b737a8d6c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29dc1aa0-defc-4df9-bc31-51be34bc2e81",
   "metadata": {},
   "outputs": [],
   "source": [
    "#flag as long as one BAL greater than 0.5\n",
    "gm_greaterthan_05 = cd.sort_values(by=['gm_greaterthan_05'], ascending=False).drop_duplicates(subset=['patient'], keep='first')\n",
    "\n",
    "#columns of interest\n",
    "gm_greaterthan_05 = gm_greaterthan_05[['patient', 'gm_greaterthan_05']]\n",
    "\n",
    "#rename \n",
    "gm_greaterthan_05=gm_greaterthan_05.rename(columns={'gm_greaterthan_05':'atleastonebal_gm_greaterthan_05'})\n",
    "\n",
    "#join back to main df\n",
    "cd = pd.merge(cd, gm_greaterthan_05, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfa46b69-914c-4834-8419-f2551ad90c8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_greaterthan_05.atleastonebal_gm_greaterthan_05.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90af00d3-2710-4a29-ba78-c871e14d54a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#flag as long as one BAL greater than 0.8\n",
    "gm_greaterthan_08 = cd.sort_values(by=['gm_greaterthan_08'], ascending=False).drop_duplicates(subset=['patient'], keep='first')\n",
    "\n",
    "#columns of interest\n",
    "gm_greaterthan_08 = gm_greaterthan_08[['patient', 'gm_greaterthan_08']]\n",
    "\n",
    "#rename \n",
    "gm_greaterthan_08=gm_greaterthan_08.rename(columns={'gm_greaterthan_08':'atleastonebal_gm_greaterthan_08'})\n",
    "\n",
    "#join back to main df\n",
    "cd = pd.merge(cd, gm_greaterthan_08, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb009ee9-acac-45ea-93c1-6fe652271a17",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_greaterthan_08.atleastonebal_gm_greaterthan_08.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da36b49f-b258-4b6d-a2ae-bb49c21c72d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#flag as long as one BAL greater than 1\n",
    "gm_greaterthan_1 = cd.sort_values(by=['gm_greaterthan_1'], ascending=False).drop_duplicates(subset=['patient'], keep='first')\n",
    "\n",
    "#columns of interest\n",
    "gm_greaterthan_1 = gm_greaterthan_1[['patient', 'gm_greaterthan_1']]\n",
    "\n",
    "#rename \n",
    "gm_greaterthan_1=gm_greaterthan_1.rename(columns={'gm_greaterthan_1':'atleastonebal_gm_greaterthan_1'})\n",
    "\n",
    "#join back to main df\n",
    "cd = pd.merge(cd, gm_greaterthan_1, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42627659-4f90-43ba-9737-5a3210d5da78",
   "metadata": {},
   "outputs": [],
   "source": [
    "gm_greaterthan_1.atleastonebal_gm_greaterthan_1.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f749ab3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['serumgm'].describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30f3a8c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.groupby('grew_asp_during_admission')['serumgm'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c93351b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "177+9"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38c0e13b",
   "metadata": {},
   "source": [
    "## fungitell by growth\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49ac0c9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.drop_duplicates(subset='patient').grew_asp_during_admission.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee9bc230",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.groupby('grew_asp_during_admission')['fungitell'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21827f94",
   "metadata": {},
   "outputs": [],
   "source": [
    "123+6\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c7b13ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# what else did they grow at time of aspergillus?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cea8d91",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "# Function to parse JSON and extract PCR and culture bacteria\n",
    "def extract_bacteria(json_str):\n",
    "    if isinstance(json_str, str):\n",
    "        try:\n",
    "            json_obj = json.loads(json_str)\n",
    "            pcr_bacteria = json_obj['pcr'].get('bacteria', [])\n",
    "            culture_bacteria = [organism['name'] for organism in json_obj['culture'].get('organisms', [])]\n",
    "            return pcr_bacteria, culture_bacteria\n",
    "        except json.JSONDecodeError:\n",
    "            return None, None\n",
    "    else:\n",
    "        return None, None\n",
    "\n",
    "# Apply the function to the 'json_data' column\n",
    "cd[['pcr_bacteria', 'culture_bacteria']] = cd['Pathogen_results'].apply(lambda x: pd.Series(extract_bacteria(x)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cd8f74a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.loc[cd.Pathogen_aspergillus_detected==True,['patient','day_bucket_starts','pcr_bacteria', 'culture_bacteria']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a87ab29",
   "metadata": {},
   "source": [
    "# other fungi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a526901",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_fungus(pathogen_results):\n",
    "    if not isinstance(pathogen_results, str):\n",
    "        return []\n",
    "    pathogens = json.loads(pathogen_results)\n",
    "    result = []\n",
    "    for pathogen in pathogens['fungal']:\n",
    "        if pathogen['name']:\n",
    "            result.append(pathogen['name'])\n",
    "    return result\n",
    "\n",
    "cd.Pathogen_results.apply(get_fungus).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24e0b991",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['fungal_results']=cd.Pathogen_results.apply(get_fungus)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f8916e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['fungal_results'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a11102c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['fungal_results'].explode().value_counts().to_csv(\"fungal_culture_results10-14-24.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25d0d7d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['fungal_results'].explode().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3c990c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['fungal_results_str'] = cd['fungal_results'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88613db9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.loc[cd.fungal_results_str.str.contains('Blastomyces', na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ad263dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.loc[cd.patient.isin(blasto_pt), 'BAL_galactomannan'].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6338dbce",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.loc[cd.patient.isin(blasto_pt), 'fungitell'].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92308db7",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.fungitell.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d69fb35",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[['serumgm','BAL_galactomannan']].corr(method='spearman')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17a55d13",
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import spearmanr\n",
    "\n",
    "stat_calc = cd[['serumgm','BAL_galactomannan']].dropna()\n",
    "\n",
    "# Calculate Spearman correlation and p-value\n",
    "correlation, p_value = spearmanr(stat_calc['serumgm'], stat_calc['BAL_galactomannan'])\n",
    "\n",
    "# Display the results\n",
    "print(f\"Spearman correlation: {correlation}\")\n",
    "print(f\"P-value: {p_value}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f09c3ef2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# figure size \n",
    "plt.figure(figsize=(5, 5))\n",
    "\n",
    "data = cd\n",
    "x = 'serumgm'\n",
    "y = 'BAL_galactomannan'\n",
    "\n",
    "data2=data[[x,y]].dropna()\n",
    "                \n",
    "# calculate the correlation value\n",
    "corr, p = scipy.stats.spearmanr(data2[x], data2[y])\n",
    "\n",
    "# create the regplot\n",
    "sns.regplot(data=data2,x=x, y=y, scatter_kws={'s': 3})\n",
    "\n",
    "\n",
    "# add the correlation value to the plot\n",
    "plt.text(0.5, .95, f'Spearman Correlation: {corr:.2f}, p={p:.2f}\\n ', ha='center', va='center', transform=plt.gca().transAxes)\n",
    "\n",
    "plt.title(\"BAL Galactomannan vs. Serum Galactomannan correlation \\n when checked on same day\") \n",
    "plt.xlabel(\"serumgm\")\n",
    "plt.ylabel(\"BAL_galactomannan\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c7772b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "serumgm_overadmission = cd.groupby('patient').agg({'serumgm':'median'}).reset_index()\n",
    "BAL_galactomannan_overadmission = cd.groupby('patient').agg({'BAL_galactomannan':'median'}).reset_index()\n",
    "serum_BAL_GM_overadmission = pd.merge(serumgm_overadmission, BAL_galactomannan_overadmission, on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8c7c64a",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "plt.figure(figsize=(5, 5))\n",
    "\n",
    "data = serum_BAL_GM_overadmission\n",
    "x = 'serumgm'\n",
    "y = 'BAL_galactomannan'\n",
    "\n",
    "data2=data[[x,y]].dropna()\n",
    "                \n",
    "# calculate the correlation value\n",
    "corr, p = scipy.stats.spearmanr(data2[x], data2[y])\n",
    "\n",
    "# create the regplot\n",
    "sns.regplot(data=data2,x=x, y=y, scatter_kws={'s': 5})\n",
    "\n",
    "# add the correlation value to the plot\n",
    "plt.text(0.5, .95, f'Spearman Correlation: {corr:.2f}, {p:.2e} \\n ', ha='center', va='center', transform=plt.gca().transAxes)\n",
    "\n",
    "plt.title(\"BAL Galactomannan vs. Serum Galactomannan \\n correlation when aggregated over admission\") \n",
    "plt.xlabel(\"serumgm_overadmission\")\n",
    "plt.ylabel(\"BAL_galactomannan_overadmission\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "214bb4d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "serum_bal_ffill = cd[['day_bucket_starts', 'serumgm', 'BAL_galactomannan']]\n",
    "# build in 5 day buffer \n",
    "serum_bal_ffill['serumgm']=serum_bal_ffill['serumgm'].fillna(method='ffill', limit=2)\n",
    "serum_bal_ffill['BAL_galactomannan']=serum_bal_ffill['BAL_galactomannan'].fillna(method='ffill', limit=2)\n",
    "serum_bal_ffill['serumgm']=serum_bal_ffill['serumgm'].fillna(method='bfill', limit=2)\n",
    "serum_bal_ffill['BAL_galactomannan']=serum_bal_ffill['BAL_galactomannan'].fillna(method='bfill', limit=2)\n",
    "serum_bal_ffill=serum_bal_ffill.dropna().drop_duplicates(subset=['serumgm', 'BAL_galactomannan'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70c5a3c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# figure size \n",
    "plt.figure(figsize=(5, 5))\n",
    "\n",
    "data = serum_bal_ffill\n",
    "x = 'serumgm'\n",
    "y = 'BAL_galactomannan'\n",
    "\n",
    "data2=data[[x,y]].dropna().drop_duplicates()\n",
    "                \n",
    "# calculate the correlation value\n",
    "corr, p = scipy.stats.spearmanr(data2[x], data2[y])\n",
    "\n",
    "# create the regplot\n",
    "sns.regplot(data=data2,x=x, y=y, scatter_kws={'s': 3})\n",
    "\n",
    "\n",
    "# add the correlation value to the plot\n",
    "plt.text(0.5, .95, f'Spearman Correlation: {corr:.2f}, p={p:.2e}\\n ', ha='center', va='center', transform=plt.gca().transAxes)\n",
    "\n",
    "plt.title(\"BAL Galactomannan vs. Serum Galactomannan correlation \\n when checked within 5 days of each other\") \n",
    "plt.xlabel(\"Serum GM\")\n",
    "plt.ylabel(\"BAL galactomannan\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fe5bc1a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f2517bcd",
   "metadata": {},
   "source": [
    "## serumgm over 0.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8591cec0",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.serumgm.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59448efb",
   "metadata": {},
   "outputs": [],
   "source": [
    "serumgm_over05_overadmission = cd.loc[cd.serumgm>0.5]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc8fbc5d-b2b5-44b7-92dd-7a385181b8aa",
   "metadata": {},
   "source": [
    "# descriptors "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdaccd40-2e12-441d-85f8-6d180fe0f6a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.drop_duplicates(subset='patient').COVID_status.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ed19519-3a9f-4b52-a12c-634b0fe51b3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.drop_duplicates(subset='patient').Immunocompromised_flag.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49d0a925",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.loc[(cd.BAL_performed) & (cd.COVID_status)].Pathogen_aspergillus_detected.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42a10675",
   "metadata": {},
   "outputs": [],
   "source": [
    "14/(14+683)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87033af2-99ec-4beb-b30d-d6e176abfead",
   "metadata": {},
   "outputs": [],
   "source": [
    "# numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea20fafe-e176-4c45-8219-dc33415ee076",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd.drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfb251fb-1df6-4b71-9dfb-4107668291d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#had a bal \n",
    "len(cd[cd.BAL_performed.notna()].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6249759-1f4d-412a-ab99-45eab7df09e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#had a bal with GM sent\n",
    "len(cd[cd.BAL_galactomannan.notna()].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "859438c7-2712-45f3-b848-74a8e630f3a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#grew aspergillus\n",
    "len(cd[cd.grew_asp_during_admission==1].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "121bdb03-259f-4bba-9ef3-eeabeb5cec5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd[cd.atleastonebal_gm_greaterthan_05==1].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f65cfd64-8ec8-4536-a8b9-ce674ef017bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd[cd.atleastonebal_gm_greaterthan_08==1].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70d245ce-ef6a-435a-90c8-ab846dae1195",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd[cd.atleastonebal_gm_greaterthan_1==1].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a8b747a-f307-48a1-b1d3-b963bd92f350",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd[(cd.atleastonebal_gm_greaterthan_05==1)&(cd['Pathogen_aspergillus_detected']==1)].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d382d76-51f0-4980-b2e6-0f7d446b0332",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd[(cd.atleastonebal_gm_greaterthan_08==1)&(cd['Pathogen_aspergillus_detected']==1)].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac89856a-c380-41b1-9a89-13e87d886b19",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cd[(cd.atleastonebal_gm_greaterthan_1==1)&(cd['Pathogen_aspergillus_detected']==1)].drop_duplicates(subset='patient'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3126b24-04b7-47ab-81a0-37f5f9d1320a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['icu_stay_start_datetime']=pd.to_datetime(cd['icu_stay_start_datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "014488c5-b83c-49c1-a3d2-fa739b3b6f7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.icu_stay_start_datetime.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54ad309f-e104-4175-8676-c1250134753b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.grew_asp_during_admission==1].drop_duplicates(subset='patient')['COVID_status'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6b47f2d-9e5e-4dbb-a754-25a989e2d5cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.grew_asp_during_admission==1].drop_duplicates(subset='patient')['Immunocompromised_flag'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "708ce78b-aa5c-406a-a514-dc7b898b2473",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.grew_asp_during_admission==1].drop_duplicates(subset='patient')['type_immunocomp'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afc8feb5-ae9b-4a95-99c3-8d4c63733808",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.grew_asp_during_admission==1].drop_duplicates(subset='patient')[['patient','COVID_status','Immunocompromised_flag',]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b2db87b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.grew_asp_during_admission==1].drop_duplicates(subset='patient')[['patient','COVID_status','type_immunocomp',]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5e8b406",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.loc[(cd.grew_asp_during_admission==True) & (cd.received_antiasp_thisadmission==0)].drop_duplicates(subset='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14d24d7b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0acfbeca-59f3-4c93-8d97-c5af97e5b167",
   "metadata": {
    "tags": []
   },
   "source": [
    "# BALs per patient graphs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e310231f-c5bf-478b-b6a0-b4dd3538d278",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd['gm_sent'] = np.where(cd.BAL_galactomannan.notna(),1,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e69cf356-53fe-4991-95e7-5afc673a122e",
   "metadata": {},
   "outputs": [],
   "source": [
    "bals_per_patient = cd.groupby('patient').agg({'BAL_performed':'sum', 'gm_sent':'sum','fungal_culture_done':'sum','BAL_galactomannan':'median'}).reset_index().rename(columns={'BAL_performed':'number_of_bals_sent','gm_sent':'number_of_gms_sent', 'fungal_culture_done':'number_of_fungal_cultures', 'BAL_galactomannan':'median_BALGM_over_admission'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6ababd1-70d2-4a4f-a5c0-657527721308",
   "metadata": {},
   "outputs": [],
   "source": [
    "bals_per_patient.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f4366e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd = pd.merge(cd, bals_per_patient, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80eb1270",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.groupby('grew_asp_during_admission')['BAL_galactomannan'].describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5894bdf-49f1-4e3e-b237-79cc10d3c852",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(12,4))\n",
    "\n",
    "data= bals_per_patient\n",
    "x = 'number_of_gms_sent'\n",
    "\n",
    "fig = sns.histplot(data=data,  x=x, bins=17, discrete=True)\n",
    "\n",
    "plt.xticks([0,1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17],)\n",
    "\n",
    "ax.set_xlabel(\"Number of BAL GMs sent per patient\", size=16)\n",
    "\n",
    "\n",
    "plt.savefig('Aspergillus/1_GM_per_patient.pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ee202da-f68d-4c95-a061-10e3fc60b5b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(12,4))\n",
    "\n",
    "data= bals_per_patient\n",
    "x = 'number_of_bals_sent'\n",
    "\n",
    "fig = sns.histplot(data=data,  x=x, bins=19, discrete=True)\n",
    "\n",
    "plt.xticks([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,20],)\n",
    "\n",
    "ax.set_xlabel(\"Number of BALs sent per patient\", size=16)\n",
    "\n",
    "\n",
    "plt.savefig('Aspergillus/1_BALs_per_patient.pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dea842a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(12,4))\n",
    "\n",
    "data= bals_per_patient\n",
    "x = 'number_of_fungal_cultures'\n",
    "\n",
    "fig = sns.histplot(data=data,  x=x, bins=19, discrete=True)\n",
    "\n",
    "plt.xticks([0,1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,20],)\n",
    "\n",
    "ax.set_xlabel(\"Number of Fungal Cultures sent per patient\", size=16)\n",
    "\n",
    "\n",
    "plt.savefig('Aspergillus/1_fungals_per_patient.pdf')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08b6cc03-1d61-419d-8cf6-2ff7fa5fba7d",
   "metadata": {},
   "source": [
    "# BAL GM by growth graphs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc9ed9bf-d176-4ced-9044-eac8df963f15",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.groupby('grew_asp_during_admission').BAL_galactomannan.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17d76313",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.BAL_galactomannan.describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cff0cff-7b4f-4acb-832b-bdbb791e0ff5",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(6,6))\n",
    "\n",
    "data = cd\n",
    "x = 'grew_asp_during_admission'\n",
    "y = 'BAL_galactomannan'\n",
    "# color='asper_growth_flag'\n",
    "\n",
    "stats_results = []\n",
    "\n",
    "for d1, d2 in itertools.combinations(data[x].unique(), 2):\n",
    "        days1 = data[y][data[x]==d1].dropna()\n",
    "        days2 = data[y][data[x]==d2].dropna()\n",
    "        if days1.size == 0 or days2.size == 0:\n",
    "            continue\n",
    "        pval = scipy.stats.mannwhitneyu(days1, days2).pvalue\n",
    "        stats_results.append([d1, d2, days1.size, days2.size, pval])\n",
    "\n",
    "stats_results = pd.DataFrame(stats_results, columns=[\"group1\", \"group2\",\n",
    "                                                   \"group1_size\", \"group2_size\", \"pval\"])\n",
    "stats_results[\"pval_adj\"] = statsmodels.stats.multitest.fdrcorrection(stats_results.pval, alpha=0.05)[1]\n",
    "stat_results_sign = stats_results.loc[stats_results.pval_adj < 0.05, :]\n",
    "pairs = []\n",
    "for _, r in stat_results_sign.iterrows():\n",
    "        pairs.append((r.group1, r.group2))\n",
    "\n",
    "\n",
    "sns.boxplot(data=data, x=x, y=y, \n",
    "            # color=color, \n",
    "            showfliers=True)\n",
    "\n",
    "# for a in ax.collections:\n",
    "#     if isinstance(a, mpl.collections.PatchCollection):\n",
    "#         # remove line surround each box\n",
    "#         a.set_linewidth(0)\n",
    "ax.set_ylabel(\"BAL galactomannan (ODI)\", size=18)\n",
    "ax.set_xlabel(\" \", size=16)\n",
    "ax.set_xticklabels([\"Patient did not \\n grow Aspergillus\", \"Patient grew Aspergillus \\n at least once\"], size=16)\n",
    "# #ax.legend(loc=\"upper left\")\n",
    "# #ax.legend_.set_bbox_to_anchor((1, 0.8))\n",
    "# ax.tick_params(axis='x', labelsize=12)\n",
    "# trans = mpl.transforms.Affine2D().translate(6, 0)\n",
    "# for t in ax.get_xticklabels():\n",
    "#     t.set_rotation(30)\n",
    "#     t.set_horizontalalignment(\"right\")\n",
    "#     t.set_transform(t.get_transform() + trans)\n",
    "# ax.set_title(\"ICU day first intubated\", size=16);\n",
    "\n",
    "annotator = statannotations.Annotator.Annotator(\n",
    "    ax, \n",
    "    pairs, \n",
    "    data=data, \n",
    "    x=x,\n",
    "    y=y, \n",
    "    verbose=False\n",
    ")\n",
    "annotator._verbose = False\n",
    "annotator.configure(line_width=1)\n",
    "annotator.set_custom_annotations([f\"p={x:.2e}\" for x in stat_results_sign.pval_adj])\n",
    "annotator.annotate();\n",
    "\n",
    "\n",
    "plt.savefig('Aspergillus/2_BALGM_bypatient.pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78765a4d-4823-4304-8402-38ba5073c0f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.groupby('Pathogen_aspergillus_detected').BAL_galactomannan.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20d2945e-7df9-4568-b15b-ea31f6e3305f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_row_aspergillus_growth=='Yes'][['day_after_first_icu_day','days_on_ventilator','summed_days_of_abx_to_today','summed_nat_score_to_today','days_of_antifungal_bytoday','sum_steroids_bytoday']].describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f8a2880-91ee-4c59-a22a-c8b383ec5d43",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_row_aspergillus_growth=='Yes'][['days_of_antifungal_bytoday',]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2847f617-a469-4316-92ab-9bbf19f2383c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_elevated_BAL_GM_05=='Yes'][['day_after_first_icu_day','days_on_ventilator','summed_days_of_abx_to_today','summed_nat_score_to_today','days_of_antifungal_bytoday','sum_steroids_bytoday']].describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1312b92f-3a8e-4393-bfba-22447bc5eda9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[(cd.first_elevated_BAL_GM_05=='Yes') & (cd.days_of_antifungal_bytoday!=0)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65e3cd73-3eeb-4bf2-b1a9-88645eea7311",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(6,6))\n",
    "\n",
    "data = cd\n",
    "x = 'Pathogen_aspergillus_detected'\n",
    "y = 'BAL_galactomannan'\n",
    "# color='asper_growth_flag'\n",
    "\n",
    "stats_results = []\n",
    "\n",
    "for d1, d2 in itertools.combinations(data[x].unique(), 2):\n",
    "        days1 = data[y][data[x]==d1].dropna()\n",
    "        days2 = data[y][data[x]==d2].dropna()\n",
    "        if days1.size == 0 or days2.size == 0:\n",
    "            continue\n",
    "        pval = scipy.stats.mannwhitneyu(days1, days2).pvalue\n",
    "        stats_results.append([d1, d2, days1.size, days2.size, pval])\n",
    "\n",
    "stats_results = pd.DataFrame(stats_results, columns=[\"group1\", \"group2\",\n",
    "                                                   \"group1_size\", \"group2_size\", \"pval\"])\n",
    "stats_results[\"pval_adj\"] = statsmodels.stats.multitest.fdrcorrection(stats_results.pval, alpha=0.05)[1]\n",
    "stat_results_sign = stats_results.loc[stats_results.pval_adj < 0.05, :]\n",
    "pairs = []\n",
    "for _, r in stat_results_sign.iterrows():\n",
    "        pairs.append((r.group1, r.group2))\n",
    "\n",
    "\n",
    "sns.boxplot(data=data, x=x, y=y, \n",
    "            # color=color, \n",
    "            showfliers=True)\n",
    "\n",
    "# for a in ax.collections:\n",
    "#     if isinstance(a, mpl.collections.PatchCollection):\n",
    "#         # remove line surround each box\n",
    "#         a.set_linewidth(0)\n",
    "ax.set_ylabel(\"BAL galactomannan (ODI)\", size=18)\n",
    "ax.set_xlabel(\" \", size=16)\n",
    "ax.set_xticklabels([\"BAL did not \\n grow Aspergillus\", \"BAL grew Aspergillus\"], size=16)\n",
    "# #ax.legend(loc=\"upper left\")\n",
    "# #ax.legend_.set_bbox_to_anchor((1, 0.8))\n",
    "# ax.tick_params(axis='x', labelsize=12)\n",
    "# trans = mpl.transforms.Affine2D().translate(6, 0)\n",
    "# for t in ax.get_xticklabels():\n",
    "#     t.set_rotation(30)\n",
    "#     t.set_horizontalalignment(\"right\")\n",
    "#     t.set_transform(t.get_transform() + trans)\n",
    "# ax.set_title(\"ICU day first intubated\", size=16);\n",
    "\n",
    "annotator = statannotations.Annotator.Annotator(\n",
    "    ax, \n",
    "    pairs, \n",
    "    data=data, \n",
    "    x=x,\n",
    "    y=y, \n",
    "    verbose=False\n",
    ")\n",
    "annotator._verbose = False\n",
    "annotator.configure(line_width=1)\n",
    "annotator.set_custom_annotations([f\"p={x:.2e}\" for x in stat_results_sign.pval_adj])\n",
    "annotator.annotate();\n",
    "\n",
    "\n",
    "plt.savefig('Aspergillus/2_BALGM_byBAL.pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e72bbdb-0d31-44cf-8b4a-81a031ea68a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.Age.isna()] #good, everyone has admission age "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "077a5365",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7a2f972",
   "metadata": {},
   "source": [
    "# categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04906180",
   "metadata": {},
   "outputs": [],
   "source": [
    "#define categories\n",
    "\n",
    "#elevated GM but didn't grow aspergillus\n",
    "cd['elevated_gm08_nogrowth_by_admission'] = np.where((cd.atleastonebal_gm_greaterthan_08==1) & (cd.grew_asp_during_admission==0),1,0)\n",
    "\n",
    "def category_08(row): \n",
    "    if (row['elevated_gm08_nogrowth_by_admission']==1):\n",
    "        return 'elevated_gm08_nogrowth_by_admission'\n",
    "    elif (row['grew_asp_during_admission']==1):\n",
    "        return 'grew_asp_during_admission'\n",
    "    # elif (row['atleastonebal_gm_greaterthan_05']==1):\n",
    "    #     return 'atleastonebal_gm_greaterthan_05'\n",
    "    else:\n",
    "        return 'no_elevated_gm'\n",
    "cd['category_08'] = cd.apply(category_08, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa7bf0c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#define categories\n",
    "\n",
    "#elevated GM 05 but didn't grow aspergillus\n",
    "cd['elevated_gm05_nogrowth_by_admission'] = np.where((cd.atleastonebal_gm_greaterthan_05==1) & (cd.grew_asp_during_admission==0),1,0)\n",
    "\n",
    "def category_05(row): \n",
    "    if (row['elevated_gm05_nogrowth_by_admission']==1):\n",
    "        return 'elevated_gm05_nogrowth_by_admission'\n",
    "    elif (row['grew_asp_during_admission']==1):\n",
    "        return 'grew_asp_during_admission'\n",
    "    # elif (row['atleastonebal_gm_greaterthan_05']==1):\n",
    "    #     return 'atleastonebal_gm_greaterthan_05'\n",
    "    else:\n",
    "        return 'no_elevated_gm'\n",
    "cd['category_05'] = cd.apply(category_05, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24fb0eec",
   "metadata": {},
   "outputs": [],
   "source": [
    "#define categories\n",
    "cd['elevated_gm1_nogrowth_by_admission'] = np.where((cd.atleastonebal_gm_greaterthan_1==1) & (cd.grew_asp_during_admission==0),1,0)\n",
    "\n",
    "def category_1(row): \n",
    "    if (row['elevated_gm1_nogrowth_by_admission']==1):\n",
    "        return 'elevated_gm1_nogrowth_by_admission'\n",
    "    elif (row['grew_asp_during_admission']==1):\n",
    "        return 'grew_asp_during_admission'\n",
    "    # elif (row['atleastonebal_gm_greaterthan_05']==1):\n",
    "    #     return 'atleastonebal_gm_greaterthan_05'\n",
    "    else:\n",
    "        return 'no_elevated_gm'\n",
    "cd['category_1'] = cd.apply(category_1, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "136886df",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "52f629d5",
   "metadata": {},
   "source": [
    "# bronch vs nonbronch "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb6ddd8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "redcap_bals = pd.read_csv(\"redcap_4339_bal_sample.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3456dc36",
   "metadata": {},
   "outputs": [],
   "source": [
    "redcap_bals = redcap_bals[['pt_study_id','bal_dt','bal_barcode','bal_method']]\n",
    "redcap_bals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18f18835",
   "metadata": {},
   "outputs": [],
   "source": [
    "redcap_bals.drop_duplicates(subset=['pt_study_id','bal_dt']).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b62f623c",
   "metadata": {},
   "outputs": [],
   "source": [
    "redcap_bals.bal_method.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3756eee6",
   "metadata": {},
   "outputs": [],
   "source": [
    "balsonly = cd.loc[cd.BAL_performed, ['patient','patient_ir_id','day_bucket_starts','Pathogen_aspergillus_detected','BAL_galactomannan']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ff54812",
   "metadata": {},
   "outputs": [],
   "source": [
    "balsonly['day_bucket_starts']=pd.to_datetime(balsonly['day_bucket_starts'])\n",
    "redcap_bals['bal_dt']=pd.to_datetime(redcap_bals['bal_dt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3177a31e",
   "metadata": {},
   "outputs": [],
   "source": [
    "balsonly.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84bfc498",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge = pd.merge(balsonly,\n",
    "                 redcap_bals,\n",
    "                 how='left',\n",
    "                 left_on=['patient', 'day_bucket_starts'],\n",
    "                 right_on=['pt_study_id','bal_dt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06fb732a",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83bde69b",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge.bal_method.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe336a5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "old_bal_report = pd.read_csv(\"SCRIPT BAL Results.csv\", skiprows=2)\n",
    "old_bal_report = old_bal_report[['ir_id','BAL_collection_date','procedure_name']]\n",
    "old_bal_report['BAL_collection_date'] = pd.to_datetime(old_bal_report['BAL_collection_date'])\n",
    "merge = pd.merge(balsonly,\n",
    "                 old_bal_report,\n",
    "                 how='left',\n",
    "                 left_on=['patient_ir_id', 'day_bucket_starts'],\n",
    "                 right_on=['ir_id','BAL_collection_date'])\n",
    "merge = merge.drop_duplicates(subset=['patient','day_bucket_starts'])\n",
    "merge.procedure_name.value_counts(dropna=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4300b8f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2 = pd.merge(merge,\n",
    "                 redcap_bals,\n",
    "                 how='left',\n",
    "                 left_on=['patient', 'day_bucket_starts'],\n",
    "                 right_on=['pt_study_id','bal_dt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "346ddd59",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1285f808",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2['nbbal'] = merge2.apply(lambda row: True if 'Nonbronchoscopic' in str(row['bal_method']) or 'NON-BRONCHOSCOPY' in str(row['procedure_name']) else '', axis=1)\n",
    "merge2['nbbal'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b01ae0fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2['bbal'] = merge2.apply(lambda row: True if 'Bronchoscopic' in str(row['bal_method']) or 'BRONCHALVEOLAR LAVAGE' in str(row['procedure_name']) else '', axis=1)\n",
    "merge2['bbal'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c2ffbe0",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.loc[(merge2.nbbal==True) & (merge2.bbal==True)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da14b8ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a342b98b",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2['bal_method'] = 'undocumented'  # Default value if neither is True\n",
    "\n",
    "# Updating 'bal_method' based on conditions\n",
    "merge2.loc[(merge2['nbbal'] == True), 'bal_method'] = 'nbbal'\n",
    "merge2.loc[(merge2['bbal'] == True), 'bal_method'] = 'bbal'\n",
    "merge2.loc[(merge2['nbbal'] == True) & (merge2['bbal'] == True), 'bal_method'] = 'conflicting'\n",
    "\n",
    "merge2['bal_method'] = merge2['bal_method'].astype('category')\n",
    "\n",
    "merge2.bal_method.value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95774a81",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.groupby('bal_method')['Pathogen_aspergillus_detected'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16d648cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.groupby('bal_method')['BAL_galactomannan'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcecc021",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.loc[merge2.nbbal==True].BAL_galactomannan.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73e042fa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "1f304293",
   "metadata": {},
   "source": [
    "# Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "705b9046",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tableone import TableOne"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75017b51",
   "metadata": {},
   "outputs": [],
   "source": [
    "ecmo_during_admission = cd.groupby('patient').ECMO_flag.max().reset_index().rename(columns={'ECMO_flag':'ecmo_during_admission'})\n",
    "cd = pd.merge(cd, ecmo_during_admission, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8d6a369",
   "metadata": {},
   "outputs": [],
   "source": [
    "crrt_during_admission = cd.groupby('patient').CRRT_flag.max().reset_index().rename(columns={'CRRT_flag':'crrt_during_admission'})\n",
    "cd = pd.merge(cd, crrt_during_admission, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb80355e",
   "metadata": {},
   "outputs": [],
   "source": [
    "levo_during_admission = cd.groupby('patient').Norepinephrine_flag.max().reset_index().rename(columns={'Norepinephrine_flag':'levo_during_admission'})\n",
    "cd = pd.merge(cd, levo_during_admission, how='left', on='patient')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d303f4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail = cd.groupby('patient').tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17f867cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#hide rare races \n",
    "\n",
    "tail.loc[tail.Race=='American Indian or Alaska Native','Race']='Unknown or Not Reported'\n",
    "tail.loc[tail.Race=='Native Hawaiian or Other Pacific Islander','Race']='Unknown or Not Reported'\n",
    "tail.loc[tail.Race=='Asian Indian','Race']='Asian'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "557c5e22",
   "metadata": {},
   "outputs": [],
   "source": [
    "#hide old ages\n",
    "tail.loc[tail.Age>89, 'Age']=91"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f157aa9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail=tail.fillna(False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ee826c8",
   "metadata": {},
   "source": [
    "## hospital vs ICU mortality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9816e8a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = tail.copy()\n",
    "\n",
    "# Convert to datetime first if not already done\n",
    "df['Death_date'] = pd.to_datetime(df['Death_date'], errors='coerce')\n",
    "df['icu_stay_stop_datetime'] = pd.to_datetime(df['icu_stay_stop_datetime'], errors='coerce')\n",
    "\n",
    "# Collapse datetime to date for both columns\n",
    "df['Death_date'] = df['Death_date'].dt.date\n",
    "df['icu_stay_stop_datetime'] = df['icu_stay_stop_datetime'].dt.date\n",
    "\n",
    "# Now apply the same logic as before to check if a patient died within the ICU stay\n",
    "df['died_within_icu_stay'] = df.apply(lambda row: 1 if pd.notnull(row['Death_date']) and row['Death_date'] <= row['icu_stay_stop_datetime'] else 0, axis=1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "313f86b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Display the resulting dataframe with the new column\n",
    "df.loc[df.Discharge_disposition=='Died',['patient_ir_id', 'Death_date', 'icu_stay_stop_datetime', 'died_within_icu_stay', 'Discharge_disposition']].dropna().died_within_icu_stay.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01d5f7fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "253+25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6880839",
   "metadata": {},
   "outputs": [],
   "source": [
    "253/287"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0851561",
   "metadata": {},
   "outputs": [],
   "source": [
    "25/287"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "793dff12",
   "metadata": {},
   "source": [
    "## basic cohort demographics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6291f62",
   "metadata": {},
   "outputs": [],
   "source": [
    "#basic demographics entire cohort\n",
    "\n",
    "mytable = TableOne(tail, \n",
    "columns=[\n",
    "\n",
    "    'Age',\n",
    "    'Gender',\n",
    "    'Race',\n",
    "    'Ethnicity',\n",
    "    'Admit_SOFA_score',\n",
    "    'Admit_APS_score',\n",
    "    'COVID_status',\n",
    "    'Influenza_status',\n",
    "    'Immunocompromised_flag',\n",
    "    'solid_organ_transplant',\n",
    "    'stem_cell_transplant',\n",
    "    'acute_leukemia',\n",
    "    'chemotherapy',\n",
    "    'neutropenic_during_admission',\n",
    "    'levo_during_admission',\n",
    "    'crrt_during_admission',\n",
    "    'ecmo_during_admission',\n",
    "\n",
    "       \n",
    "        ], \n",
    "categorical=[ \n",
    "    'Ethnicity',\n",
    "    'Gender',\n",
    "    'Race',\n",
    "    'COVID_status',\n",
    "    'Influenza_status',\n",
    "    'Immunocompromised_flag',\n",
    "    'solid_organ_transplant',\n",
    "    'stem_cell_transplant',\n",
    "    'acute_leukemia',\n",
    "    'chemotherapy',\n",
    "    'neutropenic_during_admission',\n",
    "    'levo_during_admission',\n",
    "    'crrt_during_admission',\n",
    "    'ecmo_during_admission',\n",
    "            ], \n",
    "nonnormal=['Age', \n",
    "    'Admit_SOFA_score',\n",
    "    'Admit_APS_score',],\n",
    "\n",
    "rename={ \n",
    "\n",
    "    'Age': 'Age',\n",
    "    'Ethnicity': 'Ethnicity',\n",
    "    'Gender': 'Gender',\n",
    "    'Race': 'Race',\n",
    "    'COVID_status': 'Admitted with COVID-19',\n",
    "    'Influenza_status': 'Admitted with Influenza',\n",
    "    'Immunocompromised_flag': 'Immunocompromised',\n",
    "    'solid_organ_transplant': 'Solid Organ Transplant',\n",
    "    'stem_cell_transplant': 'Stem Cell Transplant',\n",
    "    'acute_leukemia': 'Leukemia',\n",
    "    'chemotherapy': 'Chemotherapy',\n",
    "    'neutropenic_during_admission': 'Neutropenic During Admission'\n",
    "\n",
    "},\n",
    "\n",
    "    missing=True, \n",
    "# groupby='grew_asp_during_admission', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/cohort_demographics.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8cb873f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "mytable = TableOne(serumgm_over05_overadmission.drop_duplicates(subset='patient'), \n",
    "columns=[\n",
    "\n",
    "    'Age',\n",
    "    'Gender',\n",
    "    'Race',\n",
    "    'Ethnicity',\n",
    "    'Admit_SOFA_score',\n",
    "    'Admit_APS_score',\n",
    "    'COVID_status',\n",
    "    'Influenza_status',\n",
    "    'Immunocompromised_flag',\n",
    "    'solid_organ_transplant',\n",
    "    'stem_cell_transplant',\n",
    "    'acute_leukemia',\n",
    "    'chemotherapy',\n",
    "    'neutropenic_during_admission',\n",
    "       \n",
    "        ], \n",
    "categorical=[ \n",
    "    'Ethnicity',\n",
    "    'Gender',\n",
    "    'Race',\n",
    "    'COVID_status',\n",
    "    'Influenza_status',\n",
    "    'Immunocompromised_flag',\n",
    "    'solid_organ_transplant',\n",
    "    'stem_cell_transplant',\n",
    "    'acute_leukemia',\n",
    "    'chemotherapy',\n",
    "    'neutropenic_during_admission',\n",
    "            ], \n",
    "nonnormal=['Age', \n",
    "    'Admit_SOFA_score',\n",
    "    'Admit_APS_score',],\n",
    "\n",
    "rename={ \n",
    "\n",
    "    'Age': 'Age',\n",
    "    'Ethnicity': 'Ethnicity',\n",
    "    'Gender': 'Gender',\n",
    "    'Race': 'Race',\n",
    "    'COVID_status': 'Admitted with COVID-19',\n",
    "    'Influenza_status': 'Admitted with Influenza',\n",
    "    'Immunocompromised_flag': 'Immunocompromised',\n",
    "    'solid_organ_transplant': 'Solid Organ Transplant',\n",
    "    'stem_cell_transplant': 'Stem Cell Transplant',\n",
    "    'acute_leukemia': 'Leukemia',\n",
    "    'chemotherapy': 'Chemotherapy',\n",
    "    'neutropenic_during_admission': 'Neutropenic During Admission'\n",
    "\n",
    "},\n",
    "\n",
    "    missing=True, \n",
    "# groupby='grew_asp_during_admission', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/cohort_serumgm05.csv\")\n",
    "mytable"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eed6a79",
   "metadata": {},
   "source": [
    "## COVID patients immunocomp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0d9835a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36f0ed54",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail.groupby('COVID_status')['Immunocompromised_flag'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e45d310e",
   "metadata": {},
   "outputs": [],
   "source": [
    "37/(37+172)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90259556",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail.loc[tail.grew_asp_during_admission].groupby('COVID_status')['Immunocompromised_flag'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc4c0fa9",
   "metadata": {},
   "source": [
    "## breakdown grew asp or not"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0bc828b-1e99-4f5a-a18a-909c743e5ca5",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(tail, \n",
    "columns=[\n",
    "      'Age', 'COVID_status', 'Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'median_PMNs_over_admission',\n",
    "       'Cumulative_ICU_days',   'Cumulative_intubation_days',     'Tracheostomy_flag',\n",
    "       'hydrocortisone_equiv_over_admission','received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "       'Discharge_disposition','Binary_outcome',\n",
    "        ], \n",
    "categorical=['COVID_status','Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'Tracheostomy_flag','received_antifungal_thisadmission',\n",
    "       'received_antiasp_thisadmission','Discharge_disposition','Binary_outcome',\n",
    "            ], \n",
    "nonnormal=['Age',   'Cumulative_ICU_days','median_PMNs_over_admission',\n",
    "        'Cumulative_intubation_days','hydrocortisone_equiv_over_admission','days_of_antifungal_bytoday','days_of_antiasp_bytoday', 'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "          ],\n",
    "\n",
    "rename={ \n",
    "\n",
    "    'COVID_status': 'Admitted with COVID-19',\n",
    "    'Influenza_status': 'Admitted with Influenza',\n",
    "    'Immunocompromised_flag': 'Immunocompromised',\n",
    "    'solid_organ_transplant': 'Solid Organ Transplant',\n",
    "    'stem_cell_transplant': 'Stem Cell Transplant',\n",
    "    'acute_leukemia': 'Leukemia',\n",
    "    'chemotherapy': 'Chemotherapy',\n",
    "    'neutropenic_during_admission': 'Neutropenic During Admission',\n",
    "      'received_tocilizumab_during_admission': 'Received Tocilizumab',\n",
    "    'median_PMNs_over_admission': 'Median PMNs over Admission',\n",
    "    'Cumulative_ICU_days': 'Cumulative ICU Days',\n",
    "    'Cumulative_intubation_days': 'Cumulative Intubation Days',\n",
    "    'Tracheostomy_flag': 'Received Tracheostomy',\n",
    "    'hydrocortisone_equiv_over_admission': 'Steroid dose over ICU admission (in Hydrocortisone Equivalents)',\n",
    "    'received_antifungal_thisadmission': 'Treated with Antifungals',\n",
    "    'days_of_antifungal_bytoday': 'Days of Antifungal Therapy',\n",
    "    'received_antiasp_thisadmission': 'Treated with Anti-Aspergillus Antifungals',\n",
    "    'days_of_antiasp_bytoday':'Days of Anti-Aspergillus Antifungal Therapy',\n",
    "    'summed_nat_score_to_today': 'Summed NAT Score',\n",
    "    'summed_days_of_abx_to_today': 'Days of Antibiotics Therapy',\n",
    "    'Discharge_disposition': 'Discharge Disposition',\n",
    "    'Binary_outcome': 'Unfavorable Outcome',   \n",
    "},\n",
    "\n",
    "\n",
    "          missing=False,\n",
    "groupby='grew_asp_during_admission', pval=True, #htest_name=True, \n",
    "overall=False,\n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/grew_asp_during_admission.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "269925db",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail['risk_factor'] = tail[['Immunocompromised_flag', 'neutropenic_during_admission', 'COVID_status', 'Influenza_status']].any(axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d04f27d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail.loc[tail.grew_asp_during_admission==True, ['patient','risk_factor', 'Patient_category','neutropenic_during_admission','Immunocompromised_flag','COVID_status', 'Influenza_status']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e6daf1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail.loc[((tail.atleastonebal_gm_greaterthan_08==True)&(tail.received_antifungal_thisadmission==1)), ['patient','risk_factor', 'Patient_category','neutropenic_during_admission','Immunocompromised_flag','COVID_status', 'Influenza_status']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9a4c3a8-7196-4db3-97ff-3b144633307c",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(tail,\n",
    "\n",
    "columns=[\n",
    "      'Age', 'COVID_status', 'Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'median_PMNs_over_admission',\n",
    "       'Cumulative_ICU_days',   'Cumulative_intubation_days',     'Tracheostomy_flag',\n",
    "       'hydrocortisone_equiv_over_admission','received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "       'Discharge_disposition','Binary_outcome',\n",
    "        ], \n",
    "categorical=['COVID_status','Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'Tracheostomy_flag','received_antifungal_thisadmission',\n",
    "       'received_antiasp_thisadmission','Discharge_disposition','Binary_outcome',\n",
    "            ], \n",
    "nonnormal=['Age',   'Cumulative_ICU_days','median_PMNs_over_admission',\n",
    "        'Cumulative_intubation_days','hydrocortisone_equiv_over_admission','days_of_antifungal_bytoday','days_of_antiasp_bytoday', 'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "          ],\n",
    "\n",
    "rename={ \n",
    "\n",
    "    'COVID_status': 'Admitted with COVID-19',\n",
    "    'Influenza_status': 'Admitted with Influenza',\n",
    "    'Immunocompromised_flag': 'Immunocompromised',\n",
    "    'solid_organ_transplant': 'Solid Organ Transplant',\n",
    "    'stem_cell_transplant': 'Stem Cell Transplant',\n",
    "    'acute_leukemia': 'Leukemia',\n",
    "    'chemotherapy': 'Chemotherapy',\n",
    "    'neutropenic_during_admission': 'Neutropenic During Admission',\n",
    "      'received_tocilizumab_during_admission': 'Received Tocilizumab',\n",
    "    'median_PMNs_over_admission': 'Median PMNs over Admission',\n",
    "    'Cumulative_ICU_days': 'Cumulative ICU Days',\n",
    "    'Cumulative_intubation_days': 'Cumulative Intubation Days',\n",
    "    'Tracheostomy_flag': 'Received Tracheostomy',\n",
    "    'hydrocortisone_equiv_over_admission': 'Steroid dose over ICU admission (in Hydrocortisone Equivalents)',\n",
    "    'received_antifungal_thisadmission': 'Treated with Antifungals',\n",
    "    'days_of_antifungal_bytoday': 'Days of Antifungal Therapy',\n",
    "    'received_antiasp_thisadmission': 'Treated with Anti-Aspergillus Antifungals',\n",
    "    'days_of_antiasp_bytoday':'Days of Anti-Aspergillus Antifungal Therapy',\n",
    "    'summed_nat_score_to_today': 'Summed NAT Score',\n",
    "    'summed_days_of_abx_to_today': 'Days of Antibiotics Therapy',\n",
    "    'Discharge_disposition': 'Discharge Disposition',\n",
    "    'Binary_outcome': 'Unfavorable Outcome',   \n",
    "},\n",
    "\n",
    "          missing=False,overall=False,\n",
    "groupby='category_05', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/category_05.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55fb33b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(tail,\n",
    "columns=[\n",
    "      'Age', 'COVID_status', 'Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'median_PMNs_over_admission',\n",
    "       'Cumulative_ICU_days',   'Cumulative_intubation_days',     'Tracheostomy_flag',\n",
    "       'hydrocortisone_equiv_over_admission','received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "       'Discharge_disposition','Binary_outcome',\n",
    "        ], \n",
    "categorical=['COVID_status','Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'Tracheostomy_flag','received_antifungal_thisadmission',\n",
    "       'received_antiasp_thisadmission','Discharge_disposition','Binary_outcome',\n",
    "            ], \n",
    "nonnormal=['Age',   'Cumulative_ICU_days','median_PMNs_over_admission',\n",
    "        'Cumulative_intubation_days','hydrocortisone_equiv_over_admission','days_of_antifungal_bytoday','days_of_antiasp_bytoday', 'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "          ],\n",
    "\n",
    "rename={ \n",
    "\n",
    "    'COVID_status': 'Admitted with COVID-19',\n",
    "    'Influenza_status': 'Admitted with Influenza',\n",
    "    'Immunocompromised_flag': 'Immunocompromised',\n",
    "    'solid_organ_transplant': 'Solid Organ Transplant',\n",
    "    'stem_cell_transplant': 'Stem Cell Transplant',\n",
    "    'acute_leukemia': 'Leukemia',\n",
    "    'chemotherapy': 'Chemotherapy',\n",
    "    'neutropenic_during_admission': 'Neutropenic During Admission',\n",
    "      'received_tocilizumab_during_admission': 'Received Tocilizumab',\n",
    "    'median_PMNs_over_admission': 'Median PMNs over Admission',\n",
    "    'Cumulative_ICU_days': 'Cumulative ICU Days',\n",
    "    'Cumulative_intubation_days': 'Cumulative Intubation Days',\n",
    "    'Tracheostomy_flag': 'Received Tracheostomy',\n",
    "    'hydrocortisone_equiv_over_admission': 'Steroid dose over ICU admission (in Hydrocortisone Equivalents)',\n",
    "    'received_antifungal_thisadmission': 'Treated with Antifungals',\n",
    "    'days_of_antifungal_bytoday': 'Days of Antifungal Therapy',\n",
    "    'received_antiasp_thisadmission': 'Treated with Anti-Aspergillus Antifungals',\n",
    "    'days_of_antiasp_bytoday':'Days of Anti-Aspergillus Antifungal Therapy',\n",
    "    'summed_nat_score_to_today': 'Summed NAT Score',\n",
    "    'summed_days_of_abx_to_today': 'Days of Antibiotics Therapy',\n",
    "    'Discharge_disposition': 'Discharge Disposition',\n",
    "    'Binary_outcome': 'Unfavorable Outcome',   \n",
    "},\n",
    "\n",
    "          missing=False,overall=False,\n",
    "groupby='category_08', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/category_08.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55888617",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(tail,\n",
    "columns=[\n",
    "      'Age', 'COVID_status', 'Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'median_PMNs_over_admission',\n",
    "       'Cumulative_ICU_days',   'Cumulative_intubation_days',     'Tracheostomy_flag',\n",
    "       'hydrocortisone_equiv_over_admission','received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "       'Discharge_disposition','Binary_outcome',\n",
    "        ], \n",
    "categorical=['COVID_status','Influenza_status','Immunocompromised_flag','solid_organ_transplant',\n",
    " 'stem_cell_transplant',\n",
    " 'acute_leukemia',\n",
    " 'chemotherapy',\n",
    " 'neutropenic_during_admission','received_tocilizumab_during_admission',\n",
    "       'Tracheostomy_flag','received_antifungal_thisadmission',\n",
    "       'received_antiasp_thisadmission','Discharge_disposition','Binary_outcome',\n",
    "            ], \n",
    "nonnormal=['Age',   'Cumulative_ICU_days','median_PMNs_over_admission',\n",
    "        'Cumulative_intubation_days','hydrocortisone_equiv_over_admission','days_of_antifungal_bytoday','days_of_antiasp_bytoday', 'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "          ],\n",
    "\n",
    "rename={ \n",
    "\n",
    "    'COVID_status': 'Admitted with COVID-19',\n",
    "    'Influenza_status': 'Admitted with Influenza',\n",
    "    'Immunocompromised_flag': 'Immunocompromised',\n",
    "    'solid_organ_transplant': 'Solid Organ Transplant',\n",
    "    'stem_cell_transplant': 'Stem Cell Transplant',\n",
    "    'acute_leukemia': 'Leukemia',\n",
    "    'chemotherapy': 'Chemotherapy',\n",
    "    'neutropenic_during_admission': 'Neutropenic During Admission',\n",
    "      'received_tocilizumab_during_admission': 'Received Tocilizumab',\n",
    "    'median_PMNs_over_admission': 'Median PMNs over Admission',\n",
    "    'Cumulative_ICU_days': 'Cumulative ICU Days',\n",
    "    'Cumulative_intubation_days': 'Cumulative Intubation Days',\n",
    "    'Tracheostomy_flag': 'Received Tracheostomy',\n",
    "    'hydrocortisone_equiv_over_admission': 'Steroid dose over ICU admission (in Hydrocortisone Equivalents)',\n",
    "    'received_antifungal_thisadmission': 'Treated with Antifungals',\n",
    "    'days_of_antifungal_bytoday': 'Days of Antifungal Therapy',\n",
    "    'received_antiasp_thisadmission': 'Treated with Anti-Aspergillus Antifungals',\n",
    "    'days_of_antiasp_bytoday':'Days of Anti-Aspergillus Antifungal Therapy',\n",
    "    'summed_nat_score_to_today': 'Summed NAT Score',\n",
    "    'summed_days_of_abx_to_today': 'Days of Antibiotics Therapy',\n",
    "    'Discharge_disposition': 'Discharge Disposition',\n",
    "    'Binary_outcome': 'Unfavorable Outcome',   \n",
    "},\n",
    "\n",
    "          missing=False,overall=False,\n",
    "groupby='category_1', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/category_1.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55fde223",
   "metadata": {},
   "outputs": [],
   "source": [
    "tail.loc[tail.category_1=='elevated_gm1_nogrowth_by_admission'].groupby('received_antiasp_thisadmission').Binary_outcome.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0f7d2c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from scipy.stats import chi2_contingency\n",
    "data = pd.DataFrame({\n",
    "    'received_antiasp_thisadmission': [0.0, 0.0, 1.0, 1.0],\n",
    "    'Binary_outcome': [0, 1, 0, 1],\n",
    "    'count': [8, 8, 15, 11]\n",
    "})\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4875aa31",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Pivot the table to create a contingency matrix for the Chi-squared test\n",
    "contingency_table = data.pivot(index='received_antiasp_thisadmission', columns='Binary_outcome', values='count')\n",
    "\n",
    "# Perform the Chi-squared test\n",
    "chi2, p, dof, expected = chi2_contingency(contingency_table)\n",
    "\n",
    "# Prepare results for display\n",
    "chi2, p, dof, expected"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2bbe9d3",
   "metadata": {},
   "source": [
    "## (A)\tFeatures from a patient’s first day growing Aspergillus "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d117e98",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(cd[cd.first_row_aspergillus_growth=='Yes'],\n",
    "columns=['day_after_first_icu_day','days_on_ventilator','sum_steroids_bytoday',\n",
    "    'received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "       \n",
    "        ], \n",
    "categorical=[], \n",
    "nonnormal=[\n",
    "    'day_after_first_icu_day','days_on_ventilator','sum_steroids_bytoday',\n",
    "    'received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "],\n",
    "\n",
    "rename={ \n",
    "\n",
    "},\n",
    "\n",
    "          missing=False,overall=False,\n",
    "# groupby='category_1', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/first_day_growth.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29a1b261",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_row_aspergillus_growth=='Yes'].days_of_antifungal_bytoday.ne(0).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47415470",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_row_aspergillus_growth=='Yes'].days_of_antiasp_bytoday.ne(0).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "531200ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(cd[cd.first_elevated_BAL_GM_1=='Yes'],\n",
    "columns=['day_after_first_icu_day','days_on_ventilator','sum_steroids_bytoday',\n",
    "    'received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "       \n",
    "        ], \n",
    "categorical=[], \n",
    "nonnormal=[\n",
    "    'day_after_first_icu_day','days_on_ventilator','sum_steroids_bytoday',\n",
    "    'received_antifungal_thisadmission','days_of_antifungal_bytoday', \n",
    "       'received_antiasp_thisadmission','days_of_antiasp_bytoday',\n",
    "\n",
    "       'summed_nat_score_to_today',  'summed_days_of_abx_to_today',\n",
    "],\n",
    "\n",
    "rename={ \n",
    "\n",
    "},\n",
    "\n",
    "          missing=False,overall=False,\n",
    "# groupby='category_1', pval=True, #htest_name=True, \n",
    "                  )\n",
    "mytable.to_csv(\"Aspergillus/first_day_elevatedGM1.csv\")\n",
    "mytable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8f665f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_elevated_BAL_GM_1=='Yes'].days_of_antifungal_bytoday.ne(0).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b5e2a3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd[cd.first_elevated_BAL_GM_1=='Yes'].days_of_antiasp_bytoday.ne(0).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "684adefc",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd.to_csv(\"aspergillus_script_10-26-24.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c69614a-c77c-40b6-a13f-edfccea893ca",
   "metadata": {},
   "source": [
    "# example patient graphs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88107aae",
   "metadata": {},
   "source": [
    "## more readable graphs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0f59c04",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# Filter data for the specific patient\n",
    "patient = ****\n",
    "data = cd[cd.patient == patient]\n",
    "\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(10, 4))  # Adjusted figure size for better readability\n",
    "\n",
    "\n",
    "# Plot line for BAL_galactomannan\n",
    "sns.lineplot(\n",
    "    data=data,\n",
    "    x='ICU_day',\n",
    "    y='BAL_galactomannan',\n",
    "    color='black',  # Use a neutral color for the line\n",
    "    linewidth=2,  # Thicker line for better visibility\n",
    "    ax=ax\n",
    ")\n",
    "\n",
    "# Scatter plot for Pathogen_aspergillus_detected\n",
    "sns.scatterplot(\n",
    "    data=data,\n",
    "    x='ICU_day',\n",
    "    y='BAL_galactomannan',\n",
    "    hue='Pathogen_aspergillus_detected',\n",
    "    palette=['skyblue', 'tab:red'],  # Distinct colors for aspergillus detection\n",
    "    markers=['o', 's'],  # Different markers: circle for no detection, square for detection\n",
    "    style='Pathogen_aspergillus_detected',  # Use different styles\n",
    "    s=200,  # Increase marker size\n",
    "    ax=ax\n",
    ")\n",
    "\n",
    "# Scatter plot for received_antifungal_thisday, plotting at y=-0.5\n",
    "sns.scatterplot(\n",
    "    data=data,\n",
    "    x='ICU_day',\n",
    "    y=-0.4,  # Fixed y-value to show antifungal treatment events\n",
    "    hue='received_antiasp_antifungals',\n",
    "    palette=['lightgrey', 'tab:green'],  # Different colors for no/yes antifungal treatment\n",
    "    markers=['X', 'P'],  # Use 'X' for no treatment, 'P' (plus symbol) for treatment\n",
    "    style='received_antiasp_antifungals',\n",
    "    s=150,  # Larger marker size for visibility\n",
    "    ax=ax\n",
    ")\n",
    "\n",
    "# Customize the axis labels\n",
    "ax.set_ylabel(\"BAL galactomannan (ODI)\", size=18)\n",
    "ax.set_xlabel(\"ICU day\", size=16)\n",
    "\n",
    "# Customize y-ticks\n",
    "plt.yticks([0, 1, 2, 3, 4, 5, 6, 7, 8], fontsize=12)\n",
    "ax.set_ylim(-1, 8)\n",
    "\n",
    "\n",
    "plt.tight_layout()  # Automatically adjust the subplot parameters to give space to the legends\n",
    "\n",
    "# Add separate legends for clarity\n",
    "handles, labels = ax.get_legend_handles_labels()\n",
    "\n",
    "# Flip the order of handles and labels for 'Pathogen_aspergillus_detected'\n",
    "new_labels = ['Grew Aspergillus', 'Did not grow Aspergillus']  # Reversed order\n",
    "legend1 = ax.legend(handles[:2][::-1], new_labels, bbox_to_anchor=(1.05, 1.0), loc='upper left', title='Aspergillus Detection', fontsize=12)\n",
    "ax.add_artist(legend1)\n",
    "\n",
    "# Legend for Antifungal Treatment\n",
    "new_labels = ['Received anti-Aspergillus Antifungal', 'Did not receive antifungals']  # Reversed order\n",
    "legend2 = ax.legend(handles[2:][::-1], new_labels,  bbox_to_anchor=(1.05, 0.6), loc='upper left', title='Received Anti-Aspergillus Antifungal', fontsize=12)\n",
    "\n",
    "# Final adjustments and saving the plot\n",
    "plt.savefig('Aspergillus/3_****.pdf')\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbeef0b3",
   "metadata": {},
   "source": [
    "# days of therapy before BALs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af87c67f",
   "metadata": {},
   "outputs": [],
   "source": [
    "balsonly = cd.loc[cd.BAL_performed, ['patient','patient_ir_id','day_bucket_starts','Pathogen_aspergillus_detected','BAL_galactomannan', 'days_of_antifungal_bytoday', 'days_of_antiasp_bytoday']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d183a637",
   "metadata": {},
   "outputs": [],
   "source": [
    "balsonly.BAL_galactomannan.describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46d0ed50",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_bal = balsonly.drop_duplicates(subset='patient')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efef21dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_bal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e6bdd54",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum(first_bal.days_of_antiasp_bytoday==0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1369be6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(balsonly) - sum(balsonly.days_of_antifungal_bytoday==0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21dac48a",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(balsonly) - sum(balsonly.days_of_antiasp_bytoday==0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "105ee1c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(balsonly.loc[balsonly.BAL_galactomannan.notna()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d7b18fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(balsonly.loc[balsonly.BAL_galactomannan.notna()]) - sum(balsonly.loc[balsonly.BAL_galactomannan.notna()].days_of_antifungal_bytoday==0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6dd1449",
   "metadata": {},
   "outputs": [],
   "source": [
    "403/len(balsonly.loc[balsonly.BAL_galactomannan.notna()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ee39b09",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(balsonly.loc[balsonly.BAL_galactomannan.notna()]) - sum(balsonly.loc[balsonly.BAL_galactomannan.notna()].days_of_antiasp_bytoday==0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c3c7d39",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungitell = cd[['pt_study_id',\n",
    " 'patient_ir_id',\n",
    " 'first_icu_date',\n",
    " 'last_icu_date',\n",
    " 'day_bucket_starts',\n",
    "     'serumgm',\n",
    " 'fungitell',\n",
    " 'BAL_collection_date',\n",
    " 'Pathogen_aspergillus_detected',\n",
    " 'grew_asp_during_admission',\n",
    " 'gm_greaterthan_1',\n",
    " 'gm_greaterthan_08',\n",
    " 'gm_greaterthan_05',\n",
    " 'atleastonebal_gm_greaterthan_05',\n",
    " 'atleastonebal_gm_greaterthan_08',\n",
    " 'atleastonebal_gm_greaterthan_1',\n",
    "\n",
    "]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a15cdf53",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = cd[[\n",
    " 'pt_study_id',\n",
    " 'patient_ir_id',\n",
    " 'first_icu_date',\n",
    " 'last_icu_date',\n",
    " 'BAL_collection_date',\n",
    " 'gm_greaterthan_1',\n",
    " 'Pathogen_aspergillus_detected',\n",
    " 'atleastonebal_gm_greaterthan_1',\n",
    " 'grew_asp_during_admission',]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc2ff65a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f57ce122",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df.Pathogen_aspergillus_detected==True,'patient_ir_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5552f0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pd.read_csv(\"script_pathology_10-26-24.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce97ea7b",
   "metadata": {},
   "source": [
    "# pathology results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e2cf916",
   "metadata": {},
   "outputs": [],
   "source": [
    "autopsy = path.loc[path.report_description.str.contains('autopsy',case=False, na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00c2e330",
   "metadata": {},
   "outputs": [],
   "source": [
    "autopsy.loc[autopsy.patient_ir_id.isin(df.loc[df.Pathogen_aspergillus_detected==True,'patient_ir_id'].tolist())] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "957a05d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "surg_path = path.loc[path.report_description.str.contains('surg',case=False, na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae54d3b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "surg_path.loc[surg_path.patient_ir_id.isin(df.loc[df.Pathogen_aspergillus_detected==True,'patient_ir_id'].tolist())] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f579195a",
   "metadata": {},
   "outputs": [],
   "source": [
    "grew_asp = df.loc[df.Pathogen_aspergillus_detected==True]\n",
    "grew_asp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3c0ff3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "grew_asp.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30b973d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(grew_asp, surg_path, how='left', on='patient_ir_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0920f64b",
   "metadata": {},
   "source": [
    "# surg path results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f394025",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Converting date columns to datetime format\n",
    "surg_path['report_date'] = pd.to_datetime(surg_path['report_date'])\n",
    "grew_asp['first_icu_date'] = pd.to_datetime(grew_asp['first_icu_date'])\n",
    "grew_asp['last_icu_date'] = pd.to_datetime(grew_asp['last_icu_date'])\n",
    "\n",
    "# Performing the left join on 'patient_ir_id'\n",
    "merged_df = pd.merge(surg_path, grew_asp, on=\"patient_ir_id\", how=\"left\")\n",
    "\n",
    "# Filtering rows where 'report_date' is within 'first_icu_date' and 'last_icu_date'\n",
    "filtered_df = merged_df[\n",
    "    (merged_df['report_date'] >= merged_df['first_icu_date']) &\n",
    "    (merged_df['report_date'] <= merged_df['last_icu_date'])\n",
    "]\n",
    "\n",
    "filtered_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c0fa0a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df['BAL_collection_date'] = pd.to_datetime(merged_df['BAL_collection_date'])\n",
    "\n",
    "filtered_df = merged_df[\n",
    "    (merged_df['report_date'] >= merged_df['BAL_collection_date'] - pd.Timedelta(days=30)) &\n",
    "    (merged_df['report_date'] <= merged_df['BAL_collection_date'] + pd.Timedelta(days=30))\n",
    "]\n",
    "\n",
    "filtered_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "080a3b1d",
   "metadata": {},
   "source": [
    "# time before ICU stay started"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb472fa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpts = pd.read_csv(\"basic_endpoints.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19369f80",
   "metadata": {},
   "outputs": [],
   "source": [
    "cd_pts = cd.patient.drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01ee110b",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpts = endpts.loc[endpts.pt_study_id.isin(cd_pts)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb8d184d",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpts['index_icu_start'] = pd.to_datetime(endpts['index_icu_start'])\n",
    "endpts['admission_datetime'] = pd.to_datetime(endpts['admission_datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29dc53f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpts['time_diff'] = (endpts['index_icu_start'] - endpts['admission_datetime'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82b579da",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpts['time_diff'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "719660c5",
   "metadata": {},
   "source": [
    "# blasto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0837792b",
   "metadata": {},
   "outputs": [],
   "source": [
    "blasto_pt = cd.loc[cd.fungal_results_str.str.contains('Blastomyces', na=False)].patient.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ca7e22b",
   "metadata": {},
   "outputs": [],
   "source": [
    "blasto_pt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90f9d660",
   "metadata": {},
   "source": [
    "# PJP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43bc498e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pjp = pd.read_excel(\"script1.0 pjp results20241018.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "145c27f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pjp.loc[pjp.result_txt.str.contains('positive', case=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7576d676",
   "metadata": {},
   "outputs": [],
   "source": [
    "pjp.NARRATIVE.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e18a4a46",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungitell.loc[fungitell.pt_study_id.isin([pjp_pos])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "816ce539",
   "metadata": {},
   "source": [
    "# fungemia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69ed5705",
   "metadata": {},
   "outputs": [],
   "source": [
    "bcx = pd.read_excel('blood culture results.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "122de250",
   "metadata": {},
   "outputs": [],
   "source": [
    "bcx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f07ded7",
   "metadata": {},
   "outputs": [],
   "source": [
    "bcx.loc[bcx.result_txt.str.contains('candida', case=False)].pt_study_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cc615ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "candidemia= bcx.loc[bcx.result_txt.str.contains('candida', case=False)].pt_study_id.drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd547c96",
   "metadata": {},
   "outputs": [],
   "source": [
    "candidemia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9b4550b",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungitell.loc[fungitell.pt_study_id.isin(candidemia), ['pt_study_id','day_bucket_starts','fungitell']].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f35fd4ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungitell.loc[fungitell.fungitell>80].pt_study_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "947bde56",
   "metadata": {},
   "outputs": [],
   "source": [
    "fungitell.loc[fungitell.fungitell>80].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2318d347",
   "metadata": {},
   "outputs": [],
   "source": [
    "not_other_fungus = fungitell.loc[~fungitell.pt_study_id.isin([pos_pjp, candidemia, blasto_pt])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "584d2708",
   "metadata": {},
   "outputs": [],
   "source": [
    "not_other_fungus.groupby('grew_asp_during_admission').fungitell.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dffd0ba3",
   "metadata": {},
   "outputs": [],
   "source": [
    "not_other_fungus.groupby('atleastonebal_gm_greaterthan_05')['fungitell'].describe()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}