MIMIC Database Check

setwd("~/MIMIC-III/mimic-code/buildmimic/postgres")

system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f postgres_checks.sql")

Generate Requisite Data on Day of Intake

Note: drawing heavily from MIMIC github.

Generate necessary materialized views:

  1. echodata - generated by /concepts/echo-data.sql
  2. ventdurations - generated by /concepts/durations/ventilation-durations.sql - (needed for subsequent view)
  3. vitalsfirstday - generated by /concepts/firstday/vitals-first-day.sql
  4. uofirstday - generated by /concepts/firstday/urine-output-first-day.sql
  5. ventfirstday - generated by /concepts/firstday/ventilated-first-day.sql
  6. gcsfirstday - generated by /concepts/firstday/gcs-first-day.sql
  7. labsfirstday - generated by /concepts/firstday/labs-first-day.sql
  8. bloodgasfirstday - generated by /concepts/firstday/blood-gas-first-day.sql - (needed for subsequent view)
  9. bloodgasfirstdayarterial - generated by /concepts/firstday/blood-gas-first-day-arterial.sql
setwd("~/MIMIC-III/mimic-code/concepts")

## (1)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f echo-data.sql")

## (2)
setwd("~/MIMIC-III/mimic-code/concepts/durations")
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f ventilation-durations.sql")

## (3)
setwd("~/MIMIC-III/mimic-code/concepts/firstday")
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f vitals-first-day.sql")

## (4)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f urine-output-first-day.sql")

## (5)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f ventilation-first-day.sql")

## (6)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f gcs-first-day.sql")

## (7)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f labs-first-day.sql")

## (8)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f blood-gas-first-day.sql")

## (9)
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f blood-gas-first-day-arterial.sql")

Generate Severity of Illness Scores From Newly Materialized Tables

setwd("~/MIMIC-III/mimic-code/concepts/severityscores")

system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f oasis.sql")
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f saps.sql")
system("psql 'dbname=mimic user=kai-ou options=--search_path=mimiciii' -f sofa.sql")

Write Tables

## Show Materialized Views

#SELECT oid::regclass::text
#FROM   pg_class
#WHERE  relkind = 'm';

## Write from them
#\copy (SELECT * FROM mimiciii.oasis) to '/Users/kai-ou/nqf_caregivers/data/oasis.csv' with csv header
#\copy (SELECT * FROM mimiciii.saps) to '/Users/kai-ou/nqf_caregivers/data/saps.csv' with csv header
#\copy (SELECT * FROM mimiciii.sofa) to '/Users/kai-ou/nqf_caregivers/data/sofa.csv' with csv header

View Tables

SOFA

sofa <- read.csv("~/nqf_caregivers/data/sofa.csv", header = T, stringsAsFactors = F)
dim(sofa)
## [1] 61532    10
head(sofa)
##   subject_id hadm_id icustay_id sofa respiration coagulation liver
## 1      55973  152234     200001    6          NA           1     0
## 2      27513  163557     200003    6           2           1     2
## 3      10950  189514     200006    1          NA           0    NA
## 4      20707  129310     200007    1          NA           0    NA
## 5      29904  129607     200009    3           0           2    NA
## 6      11861  192256     200010    0          NA           0     0
##   cardiovascular cns renal
## 1              1   1     3
## 2              1   0     0
## 3              1   0     0
## 4              1   0     0
## 5              1   0     0
## 6              0   0     0

OASIS

oasis <- read.csv("~/nqf_caregivers/data/oasis.csv", header = T, stringsAsFactors = F)
dim(oasis)
## [1] 61532    28
head(oasis)
##   subject_id hadm_id icustay_id icustay_age_group hospital_expire_flag
## 1      55973  152234     200001             adult                    0
## 2      27513  163557     200003             adult                    0
## 3      10950  189514     200006             adult                    0
## 4      20707  129310     200007             adult                    0
## 5      29904  129607     200009             adult                    0
## 6      11861  192256     200010             adult                    0
##   icustay_expire_flag oasis oasis_prob age age_score       preiculos
## 1                   0    42 0.30584858  61         6 7 days 03:02:12
## 2                   0    35 0.15289153  48         3        02:48:04
## 3                   0    32 0.10962278  54         6        00:01:14
## 4                   0    26 0.05418720  43         3        00:01:37
## 5                   0    25 0.04801207  47         3       -00:10:28
## 6                   0    21 0.02939486  27         3        00:01:19
##   preiculos_score gcs gcs_score heartrate heartrate_score   meanbp
## 1               1  14         3       134               6  60.0000
## 2               3  15         0       122               3 179.0000
## 3               5  15         0        73               0  61.0000
## 4               5  15         0       104               1  50.6667
## 5               5  15         0       106               1  60.0000
## 6               5  15         0       115               3 104.0000
##   meanbp_score resprate resprate_score     temp temp_score urineoutput
## 1            2     32.0              6 36.38889          2         250
## 2            3     39.0              6 36.38889          2        3652
## 3            2     27.0              1 36.16667          2        1955
## 4            3     29.0              1 36.38889          2        1295
## 5            2     17.5              0 34.60000          4        1570
## 6            0     23.0              1 36.05556          2        2050
##   urineoutput_score mechvent mechvent_score electivesurgery
## 1                10        0              0               0
## 2                 0        1              9               0
## 3                 1        1              9               0
## 4                 5        0              0               0
## 5                 1        1              9               1
## 6                 1        0              0               0
##   electivesurgery_score
## 1                     6
## 2                     6
## 3                     6
## 4                     6
## 5                     0
## 6                     6

SAPS

saps <- read.csv("~/nqf_caregivers/data/saps.csv", header = T, stringsAsFactors = F)
dim(saps)
## [1] 61532    19
head(saps)
##   subject_id hadm_id icustay_id saps age_score hr_score sysbp_score
## 1      55973  152234     200001   20         2        2           0
## 2      27513  163557     200003   24         1        2           2
## 3      10950  189514     200006   15         1        2           0
## 4      20707  129310     200007    6         0        0           0
## 5      29904  129607     200009   13         1        0           0
## 6      11861  192256     200010   13         0        2           2
##   resp_score temp_score uo_score vent_score bun_score hematocrit_score
## 1          1          0        3          0         4                2
## 2          3          1        1          3         1                0
## 3          1          0        0          3         1                2
## 4          1          0        0          0         1                0
## 5          0          1        0          3         1                2
## 6          2          0        0          0         1                2
##   wbc_score glucose_score potassium_score sodium_score bicarbonate_score
## 1         2             4               0            0                 0
## 2         4             4               1            0                 1
## 3         0             4               0            0                 1
## 4         0             4               0            0                 0
## 5         0             4               1            0                 0
## 6         0             4               0            0                 0
##   gcs_score
## 1         0
## 2         0
## 3         0
## 4         0
## 5         0
## 6         0