search

MIT-LCP / mimic-code

MIMIC Code Repository: Code shared by the research community for the MIMIC family of databases
https://mimic.mit.edu
MIT License
2.58k stars 1.52k forks source link

Missing Fio2 #232

Closed will4906 closed 7 years ago

will4906 commented 7 years ago

We try to extract the data of fio2 from labevents by running the script of following,

select * from labevents where subject_id = 36 and hadm_id = 165660 and
itemid in(50816, 3420, 190, 223835 , 3422);
+--------+------------+---------+--------+---------------------+-------+----------+----------+----------+
| row_id | subject_id | hadm_id | itemid | charttime           | value | valuenum | valueuom | flag     |
+--------+------------+---------+--------+---------------------+-------+----------+----------+----------+
|  16096 |         36 |  165660 |  50816 | 2134-05-15 07:43:00 | 93    |       93 | %        | NULL     |
|  15964 |         36 |  165660 |  50821 | 2134-05-12 11:35:00 | 58    |       58 | mm Hg    | abnormal |
|  15970 |         36 |  165660 |  50821 | 2134-05-12 14:22:00 | 70    |       70 | mm Hg    | abnormal |
|  15980 |         36 |  165660 |  50821 | 2134-05-12 17:48:00 | 84    |       84 | mm Hg    | abnormal |
|  16023 |         36 |  165660 |  50821 | 2134-05-13 10:38:00 | 54    |       54 | mm Hg    | abnormal |
|  16028 |         36 |  165660 |  50821 | 2134-05-13 16:32:00 | 68    |       68 | mm Hg    | abnormal |
|  16066 |         36 |  165660 |  50821 | 2134-05-14 07:49:00 | 70    |       70 | mm Hg    | abnormal |
|  16099 |         36 |  165660 |  50821 | 2134-05-15 07:43:00 | 82    |       82 | mm Hg    | abnormal |
|  16109 |         36 |  165660 |  50821 | 2134-05-15 13:50:00 | 49    |       49 | mm Hg    | abnormal |
|  15932 |         36 |  165660 |  50821 | 2134-05-12 05:06:00 | 59    |       59 | mm Hg    | abnormal |
|  16114 |         36 |  165660 |  50821 | 2134-05-15 14:06:00 | 64    |       64 | mm Hg    | abnormal |
+--------+------------+---------+--------+---------------------+-------+----------+----------+----------+

The patient has receive many times of blood gas test, should have several results, but only return one. Could someone give us advice? thanks.

pszolovits commented 7 years ago

When I look at my copy of MIMIC-III v1.4, below are the only labs reported for patient 36 at hospital admission 165660. You are asking for 50816, 3420, 190, 223835 , 3422, of which only one entry for 50816 exists.

mysql> select x.itemid, x.c, y.label, y.fluid, y.category, y.loinc_code from (select itemid, count(*) c from labevents where subject_id = 36 and hadm_id = 165660 group by itemid order by c desc) x join d_labitems y on x.itemid=y.itemid;
+--------+----+---------------------------------+-------+------------+------------+
| itemid | c  | label                           | fluid | category   | loinc_code |
+--------+----+---------------------------------+-------+------------+------------+
|  51275 | 21 | PTT                             | Blood | Hematology | 3173-2     |
|  51221 | 15 | Hematocrit                      | Blood | Hematology | 4544-3     |
|  51237 | 12 | INR(PT)                         | Blood | Hematology | 5895-7     |
|  51274 | 12 | PT                              | Blood | Hematology | 5902-2     |
|  50868 | 11 | Anion Gap                       | Blood | Chemistry  | 1863-0     |
|  50882 | 11 | Bicarbonate                     | Blood | Chemistry  | 1963-8     |
|  50902 | 11 | Chloride                        | Blood | Chemistry  | 2075-0     |
|  50912 | 11 | Creatinine                      | Blood | Chemistry  | 2160-0     |
|  50931 | 11 | Glucose                         | Blood | Chemistry  | 2345-7     |
|  50971 | 11 | Potassium                       | Blood | Chemistry  | 2823-3     |
|  50983 | 11 | Sodium                          | Blood | Chemistry  | 2951-2     |
|  51006 | 11 | Urea Nitrogen                   | Blood | Chemistry  | 3094-0     |
|  50821 | 10 | pO2                             | Blood | Blood Gas  | 11556-8    |
|  50960 | 10 | Magnesium                       | Blood | Chemistry  | 2601-3     |
|  50802 | 10 | Base Excess                     | Blood | Blood Gas  | 11555-0    |
|  50804 | 10 | Calculated Total CO2            | Blood | Blood Gas  | 34728-6    |
|  50818 | 10 | pCO2                            | Blood | Blood Gas  | 11557-6    |
|  50820 | 10 | pH                              | Blood | Blood Gas  | 11558-4    |
|  51249 |  9 | MCHC                            | Blood | Hematology | 786-4      |
|  51250 |  9 | MCV                             | Blood | Hematology | 787-2      |
|  51265 |  9 | Platelet Count                  | Blood | Hematology | 777-3      |
|  51277 |  9 | RDW                             | Blood | Hematology | 788-0      |
|  50893 |  9 | Calcium, Total                  | Blood | Chemistry  | 2000-8     |
|  51279 |  9 | Red Blood Cells                 | Blood | Hematology | 789-8      |
|  51301 |  9 | White Blood Cells               | Blood | Hematology | 804-5      |
|  50970 |  9 | Phosphate                       | Blood | Chemistry  | 2777-1     |
|  51222 |  9 | Hemoglobin                      | Blood | Hematology | 718-7      |
|  51248 |  9 | MCH                             | Blood | Hematology | 785-6      |
|  50813 |  5 | Lactate                         | Blood | Blood Gas  | 32693-4    |
|  50862 |  2 | Albumin                         | Blood | Chemistry  | 1751-7     |
|  50910 |  2 | Creatine Kinase (CK)            | Blood | Chemistry  | 2157-6     |
|  50911 |  2 | Creatine Kinase, MB Isoenzyme   | Blood | Chemistry  | 6773-6     |
|  51003 |  2 | Troponin T                      | Blood | Chemistry  | 6598-7     |
|  51082 |  2 | Creatinine, Urine               | Urine | Chemistry  | 2161-8     |
|  51104 |  1 | Urea Nitrogen, Urine            | Urine | Chemistry  | 3095-7     |
|  50861 |  1 | Alanine Aminotransferase (ALT)  | Blood | Chemistry  | 1742-6     |
|  50863 |  1 | Alkaline Phosphatase            | Blood | Chemistry  | 6768-6     |
|  50878 |  1 | Asparate Aminotransferase (AST) | Blood | Chemistry  | 1920-8     |
|  51078 |  1 | Chloride, Urine                 | Urine | Chemistry  | 2078-4     |
|  50885 |  1 | Bilirubin, Total                | Blood | Chemistry  | 1975-2     |
|  50945 |  1 | Homocysteine                    | Blood | Chemistry  | 13965-9    |
|  51100 |  1 | Sodium, Urine                   | Urine | Chemistry  | 2955-3     |
|  50817 |  1 | Oxygen Saturation               | Blood | Blood Gas  | 20564-1    |
|  50801 |  1 | Alveolar-arterial Gradient      | Blood | Blood Gas  | 19991-9    |
|  50816 |  1 | Oxygen                          | Blood | Blood Gas  | 19994-3    |
|  50823 |  1 | Required O2                     | Blood | Blood Gas  | NULL       |
|  50826 |  1 | Tidal Volume                    | Blood | Blood Gas  | 20112-9    |
+--------+----+---------------------------------+-------+------------+------------+
47 rows in set (0.00 sec)
alistairewj commented 7 years ago

Key thing to note here is that you are only looking in the labevents table.

Originally, the labevents table is sourced from a completely independent database in the hospital which only tracks labs. However, it's sometimes convenient to have non-laboratory measurements available in the lab database, such as FiO2, as they help with interpretation.

Note that only 50816 only appears in the d_labitems table, meaning only 50816 can be used to get data in labevents:

select itemid, label         
from d_labitems
where itemid in (50816, 3420, 190, 223835 , 3422);
 itemid | label  
--------+--------
  50816 | Oxygen
(1 row)

All of the itemid in the labevents table are in the range [50000, 59999]. So you will not find data for the other itemid in that table. You can confirm this by querying the d_items table, including the linksto column:

select itemid, label, linksto
from d_items
where itemid in (50816, 3420, 190, 223835 , 3422);
 itemid |        label         |   linksto   
--------+----------------------+-------------
    190 | FiO2 Set             | chartevents
   3420 | FIO2                 | chartevents
   3422 | FIO2 [Meas]          | chartevents
 223835 | Inspired O2 Fraction | chartevents
(4 rows)

If we look for these events in the correct table, we end up finding many observations.

select hadm_id, charttime, value, valueuom
from chartevents 
where hadm_id = 165660
and itemid in (50816, 3420, 190, 223835 , 3422)
order by charttime;
 hadm_id |      charttime      | value | valueuom 
---------+---------------------+-------+----------
  165660 | 2134-05-12 07:09:00 | 100   | 
  165660 | 2134-05-12 12:00:00 | 100   | 
  165660 | 2134-05-12 16:13:00 | 100   | 
  165660 | 2134-05-12 16:27:00 | 100   | 
  165660 | 2134-05-12 17:00:00 | 60    | 
  165660 | 2134-05-12 21:00:00 | 60    | 
  165660 | 2134-05-13 01:00:00 | 60    | 
  165660 | 2134-05-13 04:00:00 | 60    | 
  165660 | 2134-05-13 08:00:00 | 60    | 
  165660 | 2134-05-13 11:00:00 | 50    | 
  165660 | 2134-05-13 12:00:00 | 50    | 
  165660 | 2134-05-13 16:00:00 | 50    | 
  165660 | 2134-05-13 20:00:00 | 50    | 
  165660 | 2134-05-13 21:00:00 | 50    | 
  165660 | 2134-05-14 00:00:00 | 50    | 
  165660 | 2134-05-14 04:00:00 | 50    | 
  165660 | 2134-05-14 08:00:00 | 50    | 
  165660 | 2134-05-14 12:00:00 | 50    | 
  165660 | 2134-05-14 17:00:00 | 50    | 
  165660 | 2134-05-14 20:00:00 | 50    | 
  165660 | 2134-05-15 01:00:00 | 50    | 
  165660 | 2134-05-15 05:00:00 | 50    | 
  165660 | 2134-05-15 08:00:00 | 50    | 
  165660 | 2134-05-15 09:00:00 | 50    | 
  165660 | 2134-05-15 09:30:00 | 50    | 
  165660 | 2134-05-15 12:00:00 | 50    | 
  165660 | 2134-05-15 20:00:00 | 40    | 
  165660 | 2134-05-15 21:00:00 | 40    | 
  165660 | 2134-05-16 00:00:00 | 40    | 
(29 rows)

Joining these together can be a bit tricky - I would check out the blood gas scripts available in the repository: https://github.com/MIT-LCP/mimic-code/blob/fac79ba4ab3a3ca7fc37b24603c0f5021fcc4106/concepts/firstday/blood-gas-first-day.sql and https://github.com/MIT-LCP/mimic-code/blob/fac79ba4ab3a3ca7fc37b24603c0f5021fcc4106/concepts/firstday/blood-gas-first-day-arterial.sql

santinon commented 7 months ago

Could you clarify how to address the missing values in FiO2 data within the chartevents table ? Is it feasible to integrate these values with those from the labevents, or would that compromise the fidelity of the original data collection methods employed by the source hospital ?