Iron Deficiency Anemia Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T20:15:36.962159Z",
     "start_time": "2022-01-24T20:15:35.331739Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from __future__ import division\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "#Data crunch and visualize\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "\n",
    "#Data formats\n",
    "import csv\n",
    "from collections import defaultdict\n",
    "import re\n",
    "\n",
    "#Modeling\n",
    "import statsmodels.api as sm\n",
    "from patsy import dmatrices\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.metrics import roc_auc_score\n",
    "\n",
    "#SQL\n",
    "# from sqlalchemy import MetaData\n",
    "# from sqlalchemy import create_engine\n",
    "# from sqlalchemy.engine.url import URL\n",
    "\n",
    "#Gen\n",
    "#import settings\n",
    "import time\n",
    "import glob\n",
    "import os\n",
    "from datetime import datetime, timedelta\n",
    "import traceback\n",
    "import pickle\n",
    "import scipy.io as sio\n",
    "import scipy\n",
    "import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T20:15:36.978263Z",
     "start_time": "2022-01-24T20:15:36.963896Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.metrics import f1_score, precision_score, accuracy_score, recall_score, precision_recall_curve, roc_curve, roc_auc_score\n",
    "# from sklearn.cross_validation import train_test_split\n",
    "from sklearn.naive_bayes import GaussianNB\n",
    "from sklearn.svm import SVC\n",
    "\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "# from sklearn.preprocessing import Imputer\n",
    "from sklearn.preprocessing import Normalizer\n",
    "from sklearn.preprocessing import scale\n",
    "from sklearn.preprocessing import normalize\n",
    "from sklearn import preprocessing\n",
    "from sklearn import feature_selection \n",
    "\n",
    "# import xgboost\n",
    "from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.metrics import f1_score, precision_score, accuracy_score, recall_score, precision_recall_curve, roc_curve, roc_auc_score\n",
    "# from sklearn.cross_validation import train_test_split\n",
    "from sklearn.naive_bayes import GaussianNB\n",
    "from sklearn.svm import SVC\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "# from sklearn.cross_validation import cross_val_score\n",
    "# from sklearn.grid_search import GridSearchCV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T20:15:36.983722Z",
     "start_time": "2022-01-24T20:15:36.980032Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.set_option(\"display.max_rows\",300)\n",
    "pd.set_option(\"display.max_columns\",300)\n",
    "np.set_printoptions(threshold=1000)\n",
    "pd.set_option('display.date_yearfirst', True)\n",
    "pd.set_option('display.float_format', lambda x: '%.5f' % x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T20:15:36.988612Z",
     "start_time": "2022-01-24T20:15:36.985405Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import matplotlib.dates as mdates\n",
    "\n",
    "import matplotlib\n",
    "font = {'family' : 'serif',\n",
    "        'weight' : 'normal',\n",
    "        'size'   : 14}\n",
    "\n",
    "matplotlib.rc('font', **font)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T20:15:37.110165Z",
     "start_time": "2022-01-24T20:15:36.990124Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0m\u001b[01;32mreq1512_CCI_detail_2010.csv\u001b[0m*    req1512_lab_2008.csv   req1512_lab_2015.csv\r\n",
      "\u001b[01;32mreq1512_CCI_summary_2010.csv\u001b[0m*   req1512_lab_2009.csv   req1512_lab_2016.csv\r\n",
      "\u001b[01;32mreq1512_demographics.csv\u001b[0m*       \u001b[01;32mreq1512_lab_2010.csv\u001b[0m*  req1512_lab_2017.csv\r\n",
      "\u001b[01;32mreq1512_ENC_DX_beyond10dx.csv\u001b[0m*  req1512_lab_2011.csv   req1512_lab_2018.csv\r\n",
      "\u001b[01;32mreq1512_encounter.csv\u001b[0m*          req1512_lab_2012.csv   req1512_lab_2019.csv\r\n",
      "req1512_lab_2006.csv            req1512_lab_2013.csv   \u001b[01;32mreq1512_lab_2020.csv\u001b[0m*\r\n",
      "req1512_lab_2007.csv            req1512_lab_2014.csv   \u001b[01;32mreq1512_med_data.csv\u001b[0m*\r\n"
     ]
    }
   ],
   "source": [
    "ls ../../../work/InternalMedicine/s176036/Anemia/final_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Demographics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:18:26.422478Z",
     "start_time": "2022-01-24T16:18:25.668976Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dem = pd.read_csv('../../../work/InternalMedicine/s176036/Anemia/final_data/req1512_demographics.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:18:28.345669Z",
     "start_time": "2022-01-24T16:18:28.342187Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Removes extra spaces from column names for easier future access\n",
    "dem.columns = [x.strip().lower() for x in dem.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:19:20.565238Z",
     "start_time": "2022-01-24T16:18:28.524959Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dem.birth_date = pd.to_datetime(dem.birth_date)\n",
    "dem.death_date = pd.to_datetime(dem.death_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-12T19:06:33.211395Z",
     "start_time": "2022-01-12T19:06:33.097031Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "509807"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(dem.mrn))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-18T21:14:44.890184Z",
     "start_time": "2020-10-18T21:14:44.888030Z"
    }
   },
   "source": [
    "# Encounters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:19:24.893028Z",
     "start_time": "2022-01-24T16:19:20.567856Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home2/s176036/.conda/envs/py36-test/lib/python3.6/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (6,7,8,9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "enc = pd.read_csv('../../../work/InternalMedicine/s176036/Anemia/final_data/req1512_encounter.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:19:24.899141Z",
     "start_time": "2022-01-24T16:19:24.895472Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "enc.columns = [x.strip().lower() for x in enc.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:19:26.545145Z",
     "start_time": "2022-01-24T16:19:24.961611Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "enc.contact_date = pd.to_datetime(enc.contact_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:19:26.941744Z",
     "start_time": "2022-01-24T16:19:26.547118Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Remove Hospital Encounters\n",
    "enc = enc[enc.enc_type != 'Hospital Encounter']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Labs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-03T03:21:31.005007Z",
     "start_time": "2021-03-03T03:21:31.003214Z"
    },
    "collapsed": true
   },
   "source": [
    "\n",
    "## Exploration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load all labs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T16:43:15.754644Z",
     "start_time": "2022-01-24T16:19:26.943715Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2009\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home2/s176036/.conda/envs/py36-test/lib/python3.6/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (22,26) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2009 deleted\n",
      "2010\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home2/s176036/.conda/envs/py36-test/lib/python3.6/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (21,25) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2010 deleted\n",
      "2011\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home2/s176036/.conda/envs/py36-test/lib/python3.6/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (21,22,26) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2011 deleted\n",
      "2012\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home2/s176036/.conda/envs/py36-test/lib/python3.6/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (21,22) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012 deleted\n",
      "2013\n",
      "2013 deleted\n",
      "2014\n",
      "2014 deleted\n",
      "2015\n",
      "2015 deleted\n",
      "2016\n",
      "2016 deleted\n",
      "2017\n",
      "2017 deleted\n",
      "2018\n",
      "2018 deleted\n",
      "2019\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "b'Skipping line 1835963: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 2854966: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 2936398: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 3426864: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 3528034: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 4197661: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 4857875: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 5496388: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 5950186: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 7605266: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 8033330: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 8447064: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 8759073: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 9507719: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 11010034: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 11608262: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 11867144: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12699714: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12938343: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 13433622: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 14694511: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 14877317: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 15110713: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 15127596: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 15231867: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 16142863: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 16209062: expected 39 fields, saw 40\\n'\n",
      "/home2/s176036/.conda/envs/py36-test/lib/python3.6/site-packages/IPython/core/interactiveshell.py:3051: DtypeWarning: Columns (22) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019 deleted\n",
      "2020\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "b'Skipping line 1026118: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 1237673: expected 39 fields, saw 42\\n'\n",
      "b'Skipping line 1425322: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 1429946: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 1730257: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 1975121: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 2317794: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 2402724: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 2616483: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 3767802: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 3878752: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 4149871: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 4365979: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 4574085: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 4957117: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 5408879: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 5496207: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 5580172: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 5670923: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 6435813: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 6553332: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 6872577: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 7844291: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 10196466: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 10371613: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 10660944: expected 39 fields, saw 40\\nSkipping line 10662144: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 10718332: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 10744775: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 11667804: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 11754521: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12276573: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12377189: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12455757: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12565391: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 12672731: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 13639040: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 13784576: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 14178116: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 14334048: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 14379154: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 14616836: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 15767933: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 15814083: expected 39 fields, saw 40\\n'\n",
      "b'Skipping line 15890067: expected 39 fields, saw 41\\n'\n",
      "b'Skipping line 15916000: expected 39 fields, saw 40\\n'\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020 deleted\n"
     ]
    }
   ],
   "source": [
    "lab_all = pd.DataFrame()\n",
    "for year in np.arange(2009, 2021):\n",
    "    print(str(year))\n",
    "    lab_cur =pd.read_csv('../../../work/InternalMedicine/s176036/Anemia/final_data/req1512_lab_' + str(year)+'.csv',  error_bad_lines=False)\n",
    "    lab_cur.columns = [x.strip().lower() for x in lab_cur.columns]\n",
    "#     lab_cur = lab_cur[lab_cur.common_name == lab_name]\n",
    "    lab_cur.result_date = pd.to_datetime(lab_cur.result_date)\n",
    "    lab_cur.ordering_date = pd.to_datetime(lab_cur.ordering_date)\n",
    "    lab_all = lab_all.append(lab_cur)\n",
    "    print(str(year) + \" deleted\")\n",
    "    del lab_cur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T17:42:39.717429Z",
     "start_time": "2022-01-24T17:42:26.897307Z"
    }
   },
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-27-7416cdfd32be>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mlab_all\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'../../../work/InternalMedicine/s176036/Anemia/all_labs_2009_2020.csv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.local/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_csv\u001b[0;34m(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, date_format, doublequote, escapechar, decimal)\u001b[0m\n\u001b[1;32m   3202\u001b[0m             \u001b[0mdecimal\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdecimal\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3203\u001b[0m         )\n\u001b[0;32m-> 3204\u001b[0;31m         \u001b[0mformatter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3206\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mpath_or_buf\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.6/site-packages/pandas/io/formats/csvs.py\u001b[0m in \u001b[0;36msave\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    202\u001b[0m             )\n\u001b[1;32m    203\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 204\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_save\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.6/site-packages/pandas/io/formats/csvs.py\u001b[0m in \u001b[0;36m_save\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    323\u001b[0m                 \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    324\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 325\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_save_chunk\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstart_i\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend_i\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    326\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    327\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_save_chunk\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstart_i\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend_i\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.6/site-packages/pandas/io/formats/csvs.py\u001b[0m in \u001b[0;36m_save_chunk\u001b[0;34m(self, start_i, end_i)\u001b[0m\n\u001b[1;32m    354\u001b[0m         )\n\u001b[1;32m    355\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 356\u001b[0;31m         \u001b[0mlibwriters\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwrite_csv_rows\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mix\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnlevels\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcols\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwriter\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32mpandas/_libs/writers.pyx\u001b[0m in \u001b[0;36mpandas._libs.writers.write_csv_rows\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "lab_all.to_csv('../../../work/InternalMedicine/s176036/Anemia/all_labs_2009_2020.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary Stats of all common names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T17:46:29.116758Z",
     "start_time": "2022-01-24T17:46:29.099881Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Alphabetical Componenet</th>\n",
       "      <th>Related Components</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>% SATURATION</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Need to see data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>% SATURATION (FE)</td>\n",
       "      <td>IRON SATURATION</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>% SATURATION TIBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Need to see data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25-HYDROXY D TOTAL</td>\n",
       "      <td>VIT D, 25-HYDROXY</td>\n",
       "      <td>VITAMIN D, 25 OH TOTAL</td>\n",
       "      <td>VITAMIN D, 25-HYDROXY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A/G RATIO</td>\n",
       "      <td>ALB/GLOBULIN RATIO</td>\n",
       "      <td>ALBUMIN/GLOB SERPL</td>\n",
       "      <td>ALBUMIN/GLOBULIN RATIO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  First Alphabetical Componenet  Related Components              Unnamed: 2  \\\n",
       "0                  % SATURATION                 NaN                     NaN   \n",
       "1             % SATURATION (FE)     IRON SATURATION                     NaN   \n",
       "2             % SATURATION TIBC                 NaN                     NaN   \n",
       "3            25-HYDROXY D TOTAL   VIT D, 25-HYDROXY  VITAMIN D, 25 OH TOTAL   \n",
       "4                     A/G RATIO  ALB/GLOBULIN RATIO      ALBUMIN/GLOB SERPL   \n",
       "\n",
       "               Unnamed: 3 Unnamed: 4          Comments  \n",
       "0                     NaN        NaN  Need to see data  \n",
       "1                     NaN        NaN               NaN  \n",
       "2                     NaN        NaN  Need to see data  \n",
       "3   VITAMIN D, 25-HYDROXY        NaN               NaN  \n",
       "4  ALBUMIN/GLOBULIN RATIO        NaN               NaN  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "componentDataDF = pd.read_csv('../../../work/InternalMedicine/s176036/Anemia/Lab_Crosswalk.csv')\n",
    "componentDataDF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T17:46:29.293058Z",
     "start_time": "2022-01-24T17:46:29.289078Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "componentDataDF.rename({'First Alphabetical Componenet':'First Alphabetical Component', 'Related Components':'Related Components 0', 'Unnamed: 2':'Related Components 1', 'Unnamed: 3':'Related Components 2', 'Unnamed: 4':'Related Components 3'}, axis = 1, inplace =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T17:49:13.323901Z",
     "start_time": "2022-01-24T17:49:13.275905Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of lab components: 300\n",
      "PROTIME\n"
     ]
    }
   ],
   "source": [
    "listOfLabComponents = []\n",
    "for eachIndex, eachRow in componentDataDF.iterrows():\n",
    "    listOfLabComponents.append(eachRow['First Alphabetical Component'])\n",
    "    for eachColumnName in ['Related Components 0', 'Related Components 1', 'Related Components 2', 'Related Components 3']:\n",
    "        if isinstance(eachRow[eachColumnName], str):\n",
    "            listOfLabComponents.append(eachRow[eachColumnName])\n",
    "\n",
    "print('number of lab components:', len(listOfLabComponents))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2022-01-24T23:06:52.405Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "% SATURATION\n",
      "% SATURATION (FE)\n",
      "IRON SATURATION\n",
      "% SATURATION TIBC\n",
      "25-HYDROXY D TOTAL\n",
      "VIT D, 25-HYDROXY\n",
      "VITAMIN D, 25 OH TOTAL\n",
      "VITAMIN D, 25-HYDROXY\n",
      "A/G RATIO\n",
      "ALB/GLOBULIN RATIO\n",
      "ALBUMIN/GLOB SERPL\n",
      "ALBUMIN/GLOBULIN RATIO\n",
      "ABSOLUTE LYMPHOCYTES\n",
      "LYMPHOCYTE\n",
      "LYMPHS\n",
      "ABSOLUTE PLASMA CELLS\n",
      "PLASMA CELL ABS\n",
      "ABSOLUTE REACTIVE LYMPHOCYTES\n",
      "LYMPHS REACTIVE\n",
      "REAC LYMPHS ABS\n",
      "ACANTHOCYTES\n",
      "ACANTHOCYTES NUMERIC\n",
      "ALBUMIN\n",
      "ALK PHOS\n",
      "ALK PHOSPHATASE\n",
      "ALKALINE PHOSPHATASE\n",
      "ALKALINE PHOSPHATASE, S\n",
      "OSH ALP SERUM\n",
      "ALT\n",
      "ALT (SGPT)\n",
      "OSH ALT SERUM\n",
      "AMIKACIN TR\n",
      "AMYLASE\n",
      "ANION GAP\n",
      "ANISOCYTOSIS\n",
      "AST\n",
      "OSH AST SERUM\n",
      "B-TYPE NATRIURETIC PEPTIDE\n",
      "BNP\n",
      "BANDS\n",
      "BANDS ABS\n",
      "BANDS PCT\n",
      "BASO (%) AUTO\n",
      "BASO%(AUTO)\n",
      "BASOPHILS, %\n",
      "BASOS PCT\n",
      "BASO # AUTO\n",
      "BASO#(AUTO)\n",
      "BASOPHILS # BLD AUTO\n",
      "BASOS ABS\n",
      "BASO STIPPLING\n",
      "BASOPHIL\n",
      "BILIRUBIN\n",
      "BILIRUBIN, TOTAL\n",
      "OSH TBIL SERUM\n",
      "TOTAL BILIRUBIN\n",
      "BILIRUBIN, DIRECT\n",
      "BILIRUBIN, INDIRECT\n",
      "INDIRECT BILIRUBIN\n",
      "BLAST\n",
      "BLASTS\n",
      "BLAST ABS\n",
      "BLAST CELLS #\n",
      "BLASTS ABS\n",
      "BLAST AND ABN LYMPHO\n",
      "BLASTS/BLAST LIKE CELLS\n",
      "BLASTS PCT\n",
      "BLOOD UREA NITROGEN\n",
      "BUN\n",
      "OSH BUN SERUM\n",
      "UREA NITROGEN (BUN)\n",
      "BONE ALK PHOSPHATASE\n",
      "BUN/CR\n",
      "BUN/CREAT RATIO\n",
      "BUN/CREATININE RATIO\n",
      "BURR CELLS\n",
      "C-REACTIVE PROTEIN, QUANT\n",
      "CALCIUM\n",
      "CALCIUM, ION\n",
      "CHLORIDE\n",
      "CHOL/HDL RATIO\n",
      "CHOLESTEROL\n",
      "CK MB\n",
      "CK, TOTAL\n",
      "CO2\n",
      "CREAT\n",
      "CREAT CONC\n",
      "CREATINE KINASE, TOTAL\n",
      "CREATINE, S/P MG/DL\n"
     ]
    }
   ],
   "source": [
    "lab_summaries_numeric_only = pd.DataFrame()\n",
    "for eachComponent in listOfLabComponents:\n",
    "    print(eachComponent)\n",
    "    cur = lab_all[(lab_all.common_name == eachComponent) & (lab_all_copy.ord_num_value != 9999999)]\n",
    "    lab_summaries_numeric_only = lab_summaries_numeric_only.append(pd.DataFrame(cur['ord_num_value'].describe().to_dict(), index = [eachComponent]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-24T20:52:49.824003Z",
     "start_time": "2022-01-24T20:18:31.012386Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "% SATURATION\n",
      "% SATURATION (FE)\n",
      "IRON SATURATION\n",
      "% SATURATION TIBC\n",
      "25-HYDROXY D TOTAL\n",
      "VIT D, 25-HYDROXY\n",
      "VITAMIN D, 25 OH TOTAL\n",
      "VITAMIN D, 25-HYDROXY\n",
      "A/G RATIO\n",
      "ALB/GLOBULIN RATIO\n",
      "ALBUMIN/GLOB SERPL\n",
      "ALBUMIN/GLOBULIN RATIO\n",
      "ABSOLUTE LYMPHOCYTES\n",
      "LYMPHOCYTE\n",
      "LYMPHS\n",
      "ABSOLUTE PLASMA CELLS\n",
      "PLASMA CELL ABS\n",
      "ABSOLUTE REACTIVE LYMPHOCYTES\n",
      "LYMPHS REACTIVE\n",
      "REAC LYMPHS ABS\n",
      "ACANTHOCYTES\n",
      "ACANTHOCYTES NUMERIC\n",
      "ALBUMIN\n",
      "ALK PHOS\n",
      "ALK PHOSPHATASE\n",
      "ALKALINE PHOSPHATASE\n",
      "ALKALINE PHOSPHATASE, S\n",
      "OSH ALP SERUM\n",
      "ALT\n",
      "ALT (SGPT)\n",
      "OSH ALT SERUM\n",
      "AMIKACIN TR\n",
      "AMYLASE\n",
      "ANION GAP\n",
      "ANISOCYTOSIS\n",
      "AST\n",
      "OSH AST SERUM\n",
      "B-TYPE NATRIURETIC PEPTIDE\n",
      "BNP\n",
      "BANDS\n",
      "BANDS ABS\n",
      "BANDS PCT\n",
      "BASO (%) AUTO\n",
      "BASO%(AUTO)\n",
      "BASOPHILS, %\n",
      "BASOS PCT\n",
      "BASO # AUTO\n",
      "BASO#(AUTO)\n",
      "BASOPHILS # BLD AUTO\n",
      "BASOS ABS\n",
      "BASO STIPPLING\n",
      "BASOPHIL\n",
      "BILIRUBIN\n",
      "BILIRUBIN, TOTAL\n",
      "OSH TBIL SERUM\n",
      "TOTAL BILIRUBIN\n",
      "BILIRUBIN, DIRECT\n",
      "BILIRUBIN, INDIRECT\n",
      "INDIRECT BILIRUBIN\n",
      "BLAST\n",
      "BLASTS\n",
      "BLAST ABS\n",
      "BLAST CELLS #\n",
      "BLASTS ABS\n",
      "BLAST AND ABN LYMPHO\n",
      "BLASTS/BLAST LIKE CELLS\n",
      "BLASTS PCT\n",
      "BLOOD UREA NITROGEN\n",
      "BUN\n",
      "OSH BUN SERUM\n",
      "UREA NITROGEN (BUN)\n",
      "BONE ALK PHOSPHATASE\n",
      "BUN/CR\n",
      "BUN/CREAT RATIO\n",
      "BUN/CREATININE RATIO\n",
      "BURR CELLS\n",
      "C-REACTIVE PROTEIN, QUANT\n",
      "CALCIUM\n",
      "CALCIUM, ION\n",
      "CHLORIDE\n",
      "CHOL/HDL RATIO\n",
      "CHOLESTEROL\n",
      "CK MB\n",
      "CK, TOTAL\n",
      "CO2\n",
      "CREAT\n",
      "CREAT CONC\n",
      "CREATINE KINASE, TOTAL\n",
      "CREATINE, S/P MG/DL\n",
      "CREATINE, SERUM\n",
      "CREATINE, SERUM/PLASMA\n",
      "CREATININE\n",
      "OSH CREATININE SERUM\n",
      "CRP\n",
      "CRP-HS\n",
      "CRP, HIGH SENSITIVITY\n",
      "DIGOXIN LVL\n",
      "DIMORPHIC RBC\n",
      "DIMORPHIC RBC POP\n",
      "DIMORPHIC RBC POPULATION\n",
      "EGFR\n",
      "EGFR AFRICAN AMERICAN\n",
      "EGFR NON-AFRICAN AMERICAN\n",
      "GFR\n",
      "ELLIPTOCYTES\n",
      "EOS\n",
      "EOSINOPHIL\n",
      "EOS (%) AUTO\n",
      "EOS PCT\n",
      "EOS%(AUTO)\n",
      "EOSINOPHIL %\n",
      "EOSINOPHILS, %\n",
      "EOS # AUTO\n",
      "EOS ABS\n",
      "EOS#(AUTO)\n",
      "EOSINOPHIL # BLD AUTO\n",
      "EOSINOPHIL COUNT\n",
      "ERYTHROCYE COUNT\n",
      "RBC\n",
      "RBC # BLD AUTO\n",
      "RED BLOOD CELL COUNT\n",
      "FECAL OCCULT BLD\n",
      "FERRITIN\n",
      "FERRITIN, S\n",
      "FERRITIN, SERUM\n",
      "FETAL HEMOGLOBIN\n",
      "FOLATE\n",
      "FOLATE (FOLIC ACID), SERUM\n",
      "FREE HEMOGLOBIN, PLAS\n",
      "FREE T4\n",
      "GGT\n",
      "GLUCOSE\n",
      "GRANS ABS\n",
      "GRANULOCYTE\n",
      "GRANULOCYTES\n",
      "GRANULOCYTE %\n",
      "HCT\n",
      "HCT VFR BLD AUTO\n",
      "HEMATOCRIT\n",
      "OSH HCT\n",
      "HDL\n",
      "HELMET CELLS\n",
      "HEM\n",
      "HEMOGLOBIN\n",
      "HGB\n",
      "OSH HGB\n",
      "HEP B S AG\n",
      "HOMOCYSTEINE\n",
      "HOWELL JOLLY BODIES\n",
      "HYPERSEG NEUTROS\n",
      "HYPERSEG POLYS\n",
      "HYPOSEG NEUTROS\n",
      "HYPOCHROMIC\n",
      "IBC\n",
      "IRON BIND. CAP.(TIBC)\n",
      "IRON BINDING CAPACITY\n",
      "TIBC\n",
      "UNSAT IBC\n",
      "IMMATURE GRANULOCYTES\n",
      "IMMATURE GRANULOCYTES (38518-7)\n",
      "INR\n",
      "IRON\n",
      "IRON, SERUM\n",
      "LACTATE\n",
      "LACTATE DEHYDROGENASE (LD), S\n",
      "LD\n",
      "LDH\n",
      "LDH TOTAL\n",
      "LAMOTRIGINE, SERUM\n",
      "LDL\n",
      "LDL, DIRECT\n",
      "LDL/HDL RATIO\n",
      "LITHIUM LVL\n",
      "LYMPH (%) AUTO\n",
      "LYMPH%(AUTO)\n",
      "LYMPHOCYTE %\n",
      "LYMPHOCYTES, %\n",
      "LYMPHS PCT\n",
      "LYMPH # AUTO\n",
      "LYMPH#(AUTO)\n",
      "LYMPHOCYTES # BLD AUTO\n",
      "LYMPHS (ABSOLUTE)\n",
      "LYMPHS ABS\n",
      "MACRO OVALOCYTES\n",
      "MACROCYTOSIS\n",
      "OVALOCYTES\n",
      "MAGNESIUM\n",
      "MAGNESIUM, SERUM\n",
      "MCH\n",
      "MCH RBC QN AUTO\n",
      "MCHC\n",
      "MCV\n",
      "MCV RBC AUTO\n",
      "MEAN PLATELET VOLUME\n",
      "MPV\n",
      "MEGAKARYOCYTE\n",
      "MEGAKARYOCYTES\n",
      "META ABS\n",
      "METAMYELOCYTES PCT\n",
      "MICROCYTOSIS\n",
      "MONO (%) AUTO\n",
      "MONO, MACRO PCT\n",
      "MONO%(AUTO)\n",
      "MONOS PCT\n",
      "MONO # AUTO\n",
      "MONO#(AUTO)\n",
      "MONOCYTES # BLD AUTO\n",
      "MONOS ABS\n",
      "MONOCYTES\n",
      "MONOSPOT\n",
      "MYELO ABS\n",
      "MYELOCYTES PCT\n",
      "NEURONTIN\n",
      "NEUT (%) AUTO\n",
      "NEUT%(AUTO)\n",
      "NEUTROPHILS, %\n",
      "NEUTROS PCT\n",
      "NEUT # AUTO\n",
      "NEUT#(AUTO)\n",
      "NEUTROPHILS # BLD AUTO\n",
      "NEUTROPHILS ABSOLUTE\n",
      "NEUTROS ABS\n",
      "NEUTROPHIL AB\n",
      "NEUTROPHILS\n",
      "NRBC\n",
      "NRBC ABS\n",
      "NRBC PERCENT\n",
      "NT-PRO BNP\n",
      "PAPPENHEIMER BODIES\n",
      "PHOSPHORUS\n",
      "PHOSPHORUS, SERUM\n",
      "PLASMA CELL\n",
      "PLASMA CELLS\n",
      "PLATELET # BLD AUTO\n",
      "PLATELET COUNT\n",
      "PLATELET COUNT, AUTOMATED\n",
      "PLATLET MANUAL\n",
      "PLATELET AB, SERUM\n",
      "PLATELETS\n",
      "PLT\n",
      "PLTS\n",
      "PLT POPULATION\n",
      "POIKILOCYTOSIS\n",
      "POLYCHROMASIA\n",
      "POTASSIUM\n",
      "POTASSIUM, BLD\n",
      "PROMYELO ABS\n",
      "PROMYELOCYTES PCT\n",
      "PROTEIN\n",
      "TOTAL PROTEIN\n",
      "PROTIME\n",
      "PTH\n",
      "PTH-INTACT\n",
      "PTH, INTACT\n",
      "RBC AGGLUTINATION\n",
      "RBC FOLATE\n",
      "RBC POPULATION\n",
      "RBC SCAN\n",
      "RDW\n",
      "RETIC ABS\n",
      "RETIC CT ABS\n",
      "RETIC RBC\n",
      "RETICULOCYTE COUNT\n",
      "RETICULOCYTES\n",
      "RETIC CT PCT\n",
      "RHEUMATOID FACTOR\n",
      "ROULEAUX\n",
      "SCHISTOCYTES\n",
      "SED RATE\n",
      "SED RATE BY MODIFIED WESTERGREN\n",
      "SICKLE CELLS\n",
      "SIROLIMUS\n",
      "SIROLIMUS LVL\n",
      "SODIUM\n",
      "SOLUBLE TRANSFERRIN RECEPTOR\n",
      "SOLUBLE TRANSFERRIN RECEPTOR (STFR)\n",
      "TRANSFERRIN RECEPTOR\n",
      "SPHEROCYTES\n",
      "STOMATOCYTES\n",
      "T3, FREE\n",
      "T3, TOTAL\n",
      "T4, FREE (DIRECT)\n",
      "TACROLIMUS (FK506), BLOOD\n",
      "TACROLIMUS LVL\n",
      "TACROLIMUS, BLOOD\n",
      "TARGET CELLS\n",
      "TEAR DROP CELLS\n",
      "THYROID STIMULATING HORMONE\n",
      "TSH\n",
      "TSH WITH REFLEX\n",
      "TRANSFERRIN\n",
      "TRIGLYCERIDES\n",
      "TROPONIN I\n",
      "VITAMIN B-12\n",
      "VITAMIN B12\n",
      "VITAMIN D 1, 25 DIHYDROXY\n",
      "VITAMIN D, 25 OH, D2\n",
      "WBC\n",
      "WBC # BLD AUTO\n",
      "WHITE BLOOD CELL COUNT\n"
     ]
    }
   ],
   "source": [
    "lab_summaries = pd.DataFrame()\n",
    "for eachComponent in listOfLabComponents:\n",
    "    print(eachComponent)\n",
    "    cur = lab_all[lab_all.common_name == eachComponent]\n",
    "    lab_summaries = lab_summaries.append(pd.DataFrame(cur['ord_num_value'].describe().to_dict(), index = [eachComponent]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "start_time": "2022-01-24T23:07:12.572Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lab_summaries.to_csv('../../../work/InternalMedicine/s176036/Anemia/lab_stats_summaries.csv')\n",
    "lab_summaries.to_csv('../../../work/InternalMedicine/s176036/Anemia/lab_stas_summaries_numeric_only.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lab cleaning\n",
    "* Restrict to non-hospital\n",
    "* Restrict to >9/2008\n",
    "* Remove point of care\n",
    "* Remove if no reference range (note two sources for references)\n",
    "* Remove external result (30) or external order (143)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-22T20:45:44.561850Z",
     "start_time": "2021-07-22T20:45:44.555425Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import math\n",
    "        \n",
    "# NOTE: needs enc from #2 in table of contents where hospital encounters are excluded\n",
    "def lab_clean(df, use_ref_ranges = True):\n",
    "    #Restrict to non-hospital through inner join\n",
    "    df = pd.merge(df, enc['pat_enc_csn_id'], on = 'pat_enc_csn_id', how = 'inner')\n",
    "    #Restrict to >9/2008\n",
    "    df = df[df.result_date>datetime(2008, 9,30)]\n",
    "    #Remove external orders/results\n",
    "    df = df[(df.order_class_c != 30)&(df.order_class_c != 143)]\n",
    "    #Remove point of care\n",
    "    df = df[(~df.proc_name.str.contains('(ABL)|(iStat)|(POC)|(POCT)')) & (df.order_class !='Point of Care')]\n",
    "    \n",
    "    #Remove if no ref range if TRUE\n",
    "    if use_ref_ranges == True:\n",
    "        df['ref_low_2'] = df.ref_normal_vals.apply(lambda x: x.split('-')[0] if (isinstance(x, str)) else x)\n",
    "        df['ref_high_2'] = df.ref_normal_vals.apply(lambda x: x.split('-')[1] if ((isinstance(x, str)) and (len(x.split('-'))==2)) else x)\n",
    "        df.ref_low_2 = pd.to_numeric(df['ref_low_2'], errors='coerce')\n",
    "        df.ref_high_2 = pd.to_numeric(df['ref_high_2'], errors='coerce')\n",
    "        df['final_ref_low'] = df.apply(lambda x: x.ref_low_2 if ~np.isnan(x.ref_low_2) else x.reference_low, axis = 1)\n",
    "        df['final_ref_high'] = df.apply(lambda x: x.ref_high_2 if ~np.isnan(x.ref_high_2) else x.reference_high, axis = 1)\n",
    "        df = df[(~df.final_ref_low.isna())&(~df.final_ref_high.isna())]\n",
    "    return df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DLenv",
   "language": "python",
   "name": "dlenv"
  },
  "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.7.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}