<jupyter_start><jupyter_text>Data Analytics in Health Care and Connected Care/Health Information Systems *Practical Session II: Data Analytics in Healthcare and Connected Care/Health  Information Systems - working with data from a real healthcare database*------The aims of this practical session are: (a) to make you work with (a subset of) data from a real healthcare database, MIMIC-III (Medical Information Mart for Intensive Care III); (b) explore the data available using simple and complex SQL queries; (c) analyse more in details patient's data. One of the group members must submit the completed notebook of the second practical session in Canvas by March 20th, 23:59:59 (GMT+1).------*Lecturer/s: <NAME> (<EMAIL>),  <NAME> (<EMAIL>), <NAME> (<EMAIL>)**Teaching assistant/s: <NAME>(<EMAIL>), <NAME> (<EMAIL>), <NAME> (<EMAIL>)*<jupyter_code>student1_full_name = str(input('Enter your full name: '))
student1_id = int(input('Enter your student ID: '))
student2_full_name = str(input('Enter your full name: '))
student2_id = int(input('Enter your student ID: '))<jupyter_output><empty_output><jupyter_text>------Getting started with SQLite in Python------> Accessing data from a database like Structured Query Language (SQL) is not only efficient, but it allows you also to subset and import only the parts of the data that you need. During this practical session, you will use [SQLite](https://sqlite.org/docs.html) (a C-language library that implements a small, fast, self-contained SQL database engine) and the 'mimicdata.sqlite' file shared in Canvas. ------<jupyter_code>#import libraries 
import numpy as np, pandas as pd, matplotlib.pyplot as plt, seaborn as sns, matplotlib.pyplot as plt,sqlite3<jupyter_output><empty_output><jupyter_text>In this first part of the lab session, you should: *   connect to the MIMIC database using sqlite3 library.*   list all the table names present in the database.*   read [here](https://mimic.physionet.org/mimictables/) the description about the MIMIC-III tables present in the 'sqlite' file you will be using.*   choose two tables of your choice and find out for each of them, their corresponding column names and the number of records present.*   read these two tables into a dataframe.Keywords: [sqlite 3 connection](https://docs.python.org/3/library/sqlite3.htmlconnection-objects), [MIMIC tables](https://mimic.physionet.org/mimictables/) ,[query execution](https://docs.python.org/3/library/sqlite3.htmlsqlite3.Cursor.execute), [read sql query into a DataFrame.](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html)<jupyter_code>#connect to the MIMIC database using sqlite3 library.
db = sqlite3.connect('mimicdata.sqlite', uri=True)
cursor = db.cursor()
#list all the table names present in the database.
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())
#choose two tables of your choice and find out for each of them, their corresponding column names and the number of records present.
cursor.execute("SELECT * FROM PATIENTS")
column_name= [tuple[0] for tuple in cursor.description]
print(column_name)
count = cursor.execute("SELECT COUNT(*) FROM PATIENTS")
print(cursor.fetchall()[0][0])
cursor.execute("SELECT * FROM ICUSTAYS")
column_name= [tuple[0] for tuple in cursor.description]
print(column_name)
count = cursor.execute("SELECT COUNT(*) FROM ICUSTAYS")
print(cursor.fetchall()[0][0])
#read these two tables into a dataframe.
table1_query = ("SELECT * FROM PATIENTS ")
df1 = pd.read_sql(table1_query,con=db)
df1.head()

table2_query = ("SELECT * FROM ICUSTAYS ")
df2 = pd.read_sql(table2_query,con=db)
df2.head()<jupyter_output><empty_output><jupyter_text>--- Explore the data using SQL queries------The main goal of this part of the lab session is to familiarize yourself with simple and complex sql queries that you can write in order to retrieve only a part of the data you have interest to perform analysis. We suggest that you save your queries into variables to avoid having to re-think/re-write them each time you want to use again. Towards reaching the main goal mentioned, you need to: --- Write sql queries to query single tables:---*   Display only the 'microbiologyevents' information for which a blood culture was performed.*   Display the number of urine specimen tested for bacterial growth. *   Display all the distinct drug values (from prescription table) used among all the records present.*   Considering the information available at 'ransfers' table, find out the numbers of patients admitted; transfered or discharged from the hospital.*   Display only the records from outputevents table for substances whose level of injection was higher than 100 mL (ordered based on the injection value) and caregiver id was 21452 and 21108. --- Write sql queries to query multiple tables:---*   Join 'admissions' and 'patient' table. *   Join 'admissions', 'patient' and 'icustays' table (pay attention to the keys).*   Get the length of stay for all patients (measured in fractional days) and plot their distribution for male and female patients.*   Write a query that selects SUBJECT_ID, GENDER, ICUSTAY_ID and adds a column 'LOS_nominal' that converts the 'LOS' into a nominal variable ('Short' if los value <= 3 else 'Long').---Keywords: [sqlite](https://docs.python.org/3/library/sqlite3.htmlmodule-functions-and-constants), [sqlite select](https://www.sqlitetutorial.net/sqlite-select/), [sqlite where](https://www.sqlitetutorial.net/sqlite-where/), [sqlite select distinct](https://www.sqlitetutorial.net/sqlite-select-distinct), [sqlite order by](https://www.sqlitetutorial.net/sqlite-order-by/), [sqlite IN operator](https://www.sqlitetutorial.net/sqlite-in/), [sqlite group by](https://www.sqlitetutorial.net/sqlite-group-by/), [sqlite join](https://www.sqlitetutorial.net/sqlite-join/).<jupyter_code>#display only the microbiology_events information for which a blood culture was performed.
df_micro = pd.read_sql_query(" SELECT * FROM MICROBIOLOGYEVENTS",con=db)
blood_culture = df_micro[df_micro['SPEC_TYPE_DESC'] == 'BLOOD CULTURE']
blood_culture
#display the number of urine specimen tested for bacterial growth.
urine_culture = df_micro[df_micro['SPEC_TYPE_DESC'] == 'URINE']
urine_culture
#display all the distinct drug values (from prescription table) used among all the records present.
drugs=pd.read_sql(" SELECT DISTINCT DRUG_NAME_GENERIC FROM PRESCRIPTIONS",con=db)
drugs
#considering the information available at transfers table, find out the numbers of patients admitted; transfered or discharged from the hospital.
df_transfers = pd.read_sql_query("SELECT EVENTTYPE,count(*) FROM TRANSFERS GROUP BY EVENTTYPE",con=db)
df_transfers.head()
total_rows = pd.read_sql("SELECT * FROM TRANSFERS",con=db).shape[0]
print(f"total number of patients: {total_rows}")
#display only information for substances whose level of injection was higher than 100 mL (ordered based on the injection value) and caregiver id was 21452 and 21108. 
injection=pd.read_sql("SELECT * FROM OUTPUTEVENTS WHERE VALUE>100 AND CGID IN (21452,21108)",con=db)
injection
#join admissions and patient table
admission_patient = pd.read_sql_query("SELECT * FROM ADMISSIONS INNER JOIN PATIENTS ON ADMISSIONS.SUBJECT_ID=PATIENTS.SUBJECT_ID;",con=db)
admission_patient

#join admission, patient and icustays
join = pd.read_sql("SELECT * FROM ADMISSIONS INNER  JOIN PATIENTS,ICUSTAYS",con=db)
join
#get the length of stay for all patients (measured in fractional days) and plot their distribution for male and female patients.
df_length_stay = pd.read_sql_query(" SELECT SUM((JULIANDAY(DISCHTIME)-JULIANDAY(ADMITTIME))) AS 'LENGTH OF STAY',PATIENTS.GENDER  FROM ADMISSIONS INNER JOIN PATIENTS ON ADMISSIONS.SUBJECT_ID = PATIENTS.SUBJECT_ID GROUP BY PATIENTS.GENDER  ",con=db)
df_length_stay.plot(x='GENDER',y='LENGTH OF STAY',kind='bar')
#select SUBJECT_ID, ICUSTAY_ID, from icustay and add a column 'LOS_nominal' that converts the LOS into a nominal variable ('Short' if los value > 3 else 'Long')
temp = []
for i in join['LOS']:
    if i > 3:
        i = 'Long'
    else:
        i = 'Short'
    temp.append(i)
join['LOS_nominal'] = temp
join.head(10)<jupyter_output><empty_output><jupyter_text>Analysing patient's laboratory measurements Table 'LABEVENTS' contains all laboratory measurements for some specific patients. 'D_LABITEMS' contains definitions for all ITEM_ID associated with lab measurements in the MIMIC database. In this part of the lab session, you should analyse the glucose levels for subject with id 40161. Query the database to retrive only the relevant information; plot the glucose levels for the given subject and interpret the results.<jupyter_code>#query
query_1= (" SELECT * FROM (SELECT * FROM LABEVENTS WHERE SUBJECT_ID = 40161) A INNER JOIN D_LABITEMS B ON A.ITEMID = B.ITEMID AND B.LABEL = 'GLUCOSE' AND B.FLUID= 'BLOOD' ORDER BY A.CHARTTIME ") 
#select only the relevant subset of data
glucose = pd.read_sql(query_1,con=db)
glucose[['CHARTTIME','VALUE','FLAG','CATEGORY']]
#plot
glucose[['CHARTTIME','VALUENUM']].plot(x='CHARTTIME',y='VALUENUM',kind='line',figsize=(20,5))<jupyter_output>/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/plotting/_matplotlib/core.py:1235: UserWarning: FixedFormatter should only be used together with FixedLocator
  ax.set_xticklabels(xticklabels)<jupyter_text>interpret result High glucose levels have a Flag raised abnormal , which means they are diabetic  Analysing patient's vital signsDuring an intensive care unit (ICU) stay, the primary repository of a patient’s information is their electronic chart which displays patients’ routine vital signs,laboratory values, mental status, etc. 'CHARTEVENTS' table contains all the charted data available for a patient. 'ICUSTAYS' table (derived from the 'TRANSFERS' table) provides information about the date and time when the patient was transferred into/out the ICU. 'D_ITEMS' provides information about all items in the ICU databases. Considering the information you get from these three tables, find out how does the oxygen saturation level for patient with icu_stay_id 256307 change over time and interpret the results.<jupyter_code>#query
query_2 = """ SELECT * FROM (SELECT * FROM CHARTEVENTS WHERE ICUSTAY_ID = 256307) A 
INNER JOIN (SELECT * FROM ICUSTAYS WHERE ICUSTAY_id = 256307) B 
ON A.ICUSTAY_ID = B.ICUSTAY_ID AND A.CHARTTIME 
BETWEEN B.INTIME AND B.OUTTIME 
INNER JOIN (SELECT ITEMID FROM D_ITEMS WHERE LABEL ='O2 saturation pulseoxymetry') C 
ON A.ITEMID = C.ITEMID ORDER BY CHARTTIME """
#slect ony the relevant subset of data
v_sign = pd.read_sql(query_2,con=db)
v_sign[['CHARTTIME','VALUENUM']]
#plot
v_sign[['CHARTTIME','VALUENUM']].plot(x='CHARTTIME',y='VALUENUM',kind='line',figsize=(20, 10))<jupyter_output>/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/plotting/_matplotlib/core.py:1235: UserWarning: FixedFormatter should only be used together with FixedLocator
  ax.set_xticklabels(xticklabels)
StarcoderdataJupyterStructuredCleanDedup