POSIT CLOUD

POSIT CLOUD

POSIT CLOUD

library$($tidyverse$)$

library$($DBI$)$

library$($RSQLite$)$

library$($treemapify$)$

tbl$\_$explore $<-$ function$($tbl$\_$name$)\{$

myquery $<-$ paste$("$SELECT $*$ FROM",tbl$\_$name$)$

print$($myquery$)$

dbGetQuery$($mydb$,$myquery$)|>$

glimpse$()$

$\}$

# Instructions $------------------------------------------------------------$

mydb $<-$ dbConnect$($RSQLite::SQLite$(),$ "MIMIC$-$III.db$")$

#Database Investigation Functions

dbListTables$($mydb$)$

dbListFields$($mydb$,$ "ADMISSIONS"$)$

##The MIMIC$-$III simulates a real healthcare$-$based Electronic Medical

##Record System similar to what would be used in a real hospital.

##This homework will provide you a chance to use the skills you have

##learned in an unfamiliar, real$-$world data environment.

##Here is an example query pulling some sample demographic information

##from the patients table. Notice how similar it is to what you have done

##previously. Only the table and field names have changed.

dbGetQuery$($mydb$,$ 'SELECT subject$\_$id$,$ gender

FROM PATIENTS'$)$

##Another example where I query the database and then plot the

##genders of patients with ggplot. Again, notice the similarities.

myquery $<-$ 'SELECT subject$\_$id$,$ gender FROM PATIENTS'

ggplot$($data $=$ dbGetQuery$($mydb$,$myquery$),$

aes$($gender$))+$

geom$\_$bar$()$

# Homework $----------------------------------------------------------------$

##AFTER FILLING IN YOUR ANSWERS, COPY AND PASTE THE

##SCRIPT INTO THE PROVIDED MOODLE DROPBOX.

##Create a SQL query that will pull the names of Fentanyl family drugs

##$($That is$,$ drugs that include the name "Fent" or something similar$)$

##from the prescriptions table. Create a bar chart of this data.

##Your bar chart should clearly show

##which form of Fentanyl is most often prescribed if you have

#created it correctly.

myquery$1<-\text{'}\text{'}$

dbGetQuery$($mydb$,$myquery$1)$

##Create a select statement that will pull the gender and insurance

##of patients admitted to the hospital from the tables that have this data

##$($I$\text{'}$m not telling you which tables they are, but I will tell you the information

##is on two different tables. $($So a join is necessary and the join key is subject$\_$id$).$

#Once you are happy with your SQL query, use ggplot to create a stacked bar chart showing

#the insurance data with fill $=$ gender.

myquery$2<-\text{'}\text{'}$

dbGetQuery$($mydb$,$myquery$2)$

#Join the patients and icustays tables so that you can select

#the dod$\_$hosp $($date of death in the hospital$)$ and first$\_$careunit.

#group the data by first$\_$careunit and count the number of

#dod$\_$hosp to see how many people have died in ONLY

#the CCU, MICU and SICU first$\_$careunits.

#graph this information using a geom$\_$col$().$

myquery$3<-\text{'}\text{'}$

dbGetQuery$($mydb$,$myquery$3)$

#Create a SQL query using the PRESCRIPTIONS

#table that will provide what you believe is sufficient data to create

#a useful summary of all the prescriptions ever

#prescribed to the patient with ID $=10032.$

#When you're happy with your query, save it to a variable

#called "patient$\_$history". You can then click on patient history in the

#environment window to see your report.

myquery$4<-\text{'}\text{'}$

patient$\_$history $<-$ dbGetQuery$($mydb$,$ myquery$4)$

##Create a custom query of your own using a JOIN, a GROUP BY

##and graphing your output in some way.

myquery$5<-\text{'}\text{'}$

dbGetQuery$($mydb$,$ myquery$5)$