跟着 mimic-code 探索 MIMIC 数据之 notebooks CRRT (一)

1
2
3
4
5
6
7
8
9

# Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
# used to print out pretty pandas dataframes
from IPython.display import display, HTML
import matplotlib.dates as dates
import matplotlib.lines as mlines

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

%matplotlib inline
plt.style.use('ggplot') 

# specify user/password/where the database is
sqluser = 'postgres'
dbname = 'mimic'
schema_name = 'mimiciii'
host = 'localhost'

query_schema = 'SET search_path to ' + schema_name + ';'

# connect to the database
con = psycopg2.connect(dbname=dbname, user=sqluser, password=getpass.getpass(prompt='Password:'.format(user)), host=host)

1
2
3

OperationalError: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

query = query_schema + """
select itemid
, label
, category
, linksto
from d_items
where dbsource = 'metavision'
and lower(label) like '%crrt%'
"""

df = pd.read_sql_query(query,con)

df

1
2
3
4
5
6
7
8
9

# A tibble: 6 x 4
  itemid label                         category    linksto           
*  <int> <chr>                         <chr>       <chr>             
1 227290 CRRT mode                     Dialysis    chartevents       
2 225436 CRRT Filter Change            Dialysis    procedureevents_mv
3 227525 Calcium Gluconate (CRRT)      Medications inputevents_mv    
4 225802 Dialysis - CRRT               Dialysis    procedureevents_mv
5 227536 KCl (CRRT)                    Medications inputevents_mv    
6 225956 Reason for CRRT Filter Change Dialysis    chartevents  

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

query("SELECT itemid, label, category, linksto FROM d_items di
       WHERE dbsource = 'metavision' 
          AND (lower(label) LIKE '%dialy%'
          OR category = 'Dialysis'
          OR lower(label) LIKE '%crrt%')
       ORDER BY linksto, category, label;")

# A tibble: 65 x 4
   itemid label                                          category                linksto    
 *  <int> <chr>                                          <chr>                   <chr>      
 1 225740 Dialysis Catheter Discontinued                 Access Lines - Invasive chartevents
 2 227357 Dialysis Catheter Dressing Occlusive           Access Lines - Invasive chartevents
 3 225776 Dialysis Catheter Dressing Type                Access Lines - Invasive chartevents
 4 226118 Dialysis Catheter placed in outside facility   Access Lines - Invasive chartevents
 5 227753 Dialysis Catheter Placement Confirmed by X-ray Access Lines - Invasive chartevents
 6 225323 Dialysis Catheter Site Appear                  Access Lines - Invasive chartevents
 7 225725 Dialysis Catheter Tip Cultured                 Access Lines - Invasive chartevents
 8 227124 Dialysis Catheter Type                         Access Lines - Invasive chartevents
 9 225126 Dialysis patient                               Adm History/FHPA        chartevents
10 224149 Access Pressure                                Dialysis                chartevents
# ... with 55 more rows

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

query("SELECT ce.icustay_id, di.label, ce.charttime, ce.value, ce.valueuom
       FROM chartevents ce INNER JOIN d_items di ON
          ce.itemid = di.itemid
       WHERE ce.icustay_id = 246866
       AND ce.itemid in
       (
          224404, -- | ART Lumen Volume
          224406, -- | VEN Lumen Volume
          228004, -- | Citrate (ACD-A)
          224145, -- | Heparin Dose (per hour)
          225183, -- | Current Goal
          224149, -- | Access Pressure
          224144, -- | Blood Flow (ml/min)
          224154, -- | Dialysate Rate
          224151, -- | Effluent Pressure
          224150, -- | Filter Pressure
          224191, -- | Hourly Patient Fluid Removal
          228005, -- | PBP (Prefilter) Replacement Rate
          228006, -- | Post Filter Replacement Rate
          224153, -- | Replacement Rate
          224152, -- | Return Pressure
          226457  -- | Ultrafiltrate Output
      )
      ORDER BY ce.icustay_id, ce.charttime, di.label;")

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

query_item <- function(item_id){
  qur <- stringr::str_replace_all(paste("
         SELECT value
         , COUNT(distinct icustay_id) AS number_of_patients
         , COUNT(icustay_id) AS number_of_observations
         FROM chartevents
         WHERE itemid = '",item_id,
         "' GROUP BY value ORDER BY value;", sep = ""), "[\n]", "")

  query(qur)
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

   value                      number_of_patients number_of_observations
 * <chr>                                   <dbl>                  <dbl>
 1 Active                                    539                  48072
 2 Clots Increasing                          245                   1419
 3 Clots Present                             427                  16836
 4 Clotted                                   233                    441
 5 Discontinued                              339                    771
 6 Line pressure inconsistent                127                    431
 7 New Filter                                357                   1040
 8 No Clot Present                           275                   2615
 9 Recirculating                             172                    466
10 Reinitiated                               336                   1207

1
2
3
4
5

  value        number_of_patients number_of_observations
* <chr>                     <dbl>                  <dbl>
1 Clotted                      50                     69
2 Line changed                  9                     11
3 Procedure                    20                     31

1
2
3
4
5

  value          number_of_patients number_of_observations
* <chr>                       <dbl>                  <dbl>
1 100                            16                    995
2 1000                           41                     94
3 Not applicable                120                   8796

1
2
3
4
5
6
7
8
9

  value                   number_of_patients number_of_observations
* <chr>                                <dbl>                  <dbl>
1 None                                    14                     19
2 Normal Saline 0.9%                       1                     12
3 Prismasate K0                           78                    201
4 Prismasate K2                          459                  27603
5 Prismasate K4                          387                  30872
6 Sodium Bicarb 150/D5W                    2                      8
7 Sodium Bicarb 75/0.45NS                  6                     48

1
2
3
4
5
6
7

  value         number_of_patients number_of_observations
* <chr>                      <dbl>                  <dbl>
1 None                          97                   6025
2 Normal Saline                 32                    695
3 Prismasate K0                 89                    231
4 Prismasate K2                438                  24271
5 Prismasate K4                357                  27320

1
2
3
4
5
6

  value  number_of_patients number_of_observations
* <chr>               <dbl>                  <dbl>
1 CVVH                   40                   1280
2 CVVHD                  24                    583
3 CVVHDF                498                  25533
4 SCUF                    1                      7

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

query("WITH t1 AS
(
  SELECT icustay_id,
  MAX(CASE WHEN
          itemid = 227290 THEN 1
      ELSE 0 END) AS HasMode
  FROM chartevents ce
  WHERE itemid IN
  (
  227290, --  CRRT mode
  228004, --  Citrate (ACD-A)
  225958, --  Heparin Concentration (units/mL)
  224145, --  Heparin Dose (per hour)
  225183, --  Current Goal -- always there
  224149, --  Access Pressure
  224144, --  Blood Flow (ml/min)
  225977, --  Dialysate Fluid
  224154, --  Dialysate Rate
  224151, --  Effluent Pressure
  224150, --  Filter Pressure
  224191, --  Hourly Patient Fluid Removal
  228005, --  PBP (Prefilter) Replacement Rate
  228006, --  Post Filter Replacement Rate
  225976, --  Replacement Fluid
  224153, --  Replacement Rate
  224152, --  Return Pressure
  226457  --  Ultrafiltrate Output
  )
  GROUP BY icustay_id
)
  SELECT COUNT(icustay_id) AS Num_ICUSTAY_ID
  , SUM(hasmode) AS Num_With_Mode
  FROM t1;")

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

query("
WITH t1 AS 
  (
    SELECT icustay_id, charttime
    , MAX(CASE WHEN
            itemid = 227290 THEN 1
          ELSE 0 END) AS HasCRRTMode
    , MAX(CASE WHEN
            itemid != 227290 THEN 1
          ELSE 0 END) AS OtherITEMID
    FROM chartevents ce
    WHERE itemid in
    (
      227290, --  CRRT mode
      228004, --  Citrate (ACD-A)
      225958, --  Heparin Concentration (units/mL)
      224145, --  Heparin Dose (per hour)
      225183, --  Current Goal -- always there
      224149, --  Access Pressure
      224144, --  Blood Flow (ml/min)
      225977, --  Dialysate Fluid
      224154, --  Dialysate Rate
      224151, --  Effluent Pressure
      224150, --  Filter Pressure
      224191, --  Hourly Patient Fluid Removal
      228005, --  PBP (Prefilter) Replacement Rate
      228006, --  Post Filter Replacement Rate
      225976, --  Replacement Fluid
      224153, --  Replacement Rate
      224152, --  Return Pressure
      226457  --  Ultrafiltrate Output
    )
    GROUP BY icustay_id, charttime
  )
  SELECT count(icustay_id) AS NumObs
  , SUM(CASE WHEN HasCRRTMode = 1 AND OtherITEMID = 1 THEN 1 ELSE 0 END) AS Both
  , SUM(CASE WHEN HasCRRTMode = 1 AND OtherITEMID = 0 THEN 1 ELSE 0 END) AS OnlyCRRTMode
  , SUM(CASE WHEN HasCRRTMode = 0 AND OtherITEMID = 1 THEN 1 ELSE 0 END) AS NoCRRTMode
  FROM t1;"
)