Is the patient's height available?

Question

Is the patient's height available in MIMIC-III? In my use case, I would be interested in having it in order to compute the patient's ideal body weight.

On Oracle DB:

SELECT  *
FROM all_tab_columns
WHERE LOWER(column_name) = 'height'
AND owner = 'MIMIC';

or on PostgreSQL:

select table_name 
from information_schema.columns
where LOWER(column_name) LIKE '%height%';

didn't return anything.

Looking at the D_ITEMS:

SELECT *
FROM mimiciii.D_ITEMS
-- not ideal, but %ht% will find labels containing 'Ht' and 'Height'
WHERE lower(label) like '%ht%'
ORDER BY label ASC;

there are a few itemid of interest:

But using them to look for heights in chartevents table only returns the heights for a fraction of patients (mostly neonates):

-- Result: 12958 rows 
SELECT ICUSTAY_ID, MIN(VALUENUM) mini, MAX(VALUENUM), 
  AVG(VALUENUM), STDDEV(VALUENUM)
FROM mimiciii.CHARTEVENTS 
WHERE ITEMID IN (920)
AND VALUENUM IS NOT NULL
AND VALUENUM > 0
GROUP BY ICUSTAY_ID
ORDER BY mini ASC
;


-- Result: 11385 rows 
SELECT subject_id, MIN(VALUENUM) mini, MAX(VALUENUM), 
  AVG(VALUENUM), STDDEV(VALUENUM), COUNT(*) cnt
FROM mimiciii.CHARTEVENTS 
WHERE ITEMID IN (920)
AND VALUENUM IS NOT NULL
AND VALUENUM > 0
GROUP BY subject_id
ORDER BY mini ASC
;

Are the patients' heights available somewhere else?

Franck Dernoncourt · Accepted Answer · 2015-12-04 23:33:13Z

You can use this query (tested on MIMIC-III v1.1, took around 80 seconds to run on my computer):

-- ------------------------------------------------------------------
-- Title: Extract height and weight for ICUSTAY_IDs
-- Description: This query gets the first, minimum, and maximum weight and height
--        for a single ICUSTAY_ID. It extracts data from the CHARTEVENTS table.
-- MIMIC version: MIMIC-III v1.2
-- Created by: Erin Hong, Alistair Johnson
-- ------------------------------------------------------------------

-- DROP VIEW franck.heightweight;
-- DROP TABLE franck.heightweight;

CREATE TABLE franck.heightweight
AS
WITH FirstVRawData AS
  (SELECT c.charttime,
    c.itemid,c.subject_id,c.icustay_id,
    CASE
      WHEN c.itemid IN (762, 763, 3723, 3580, 3581, 3582)
      THEN 'WEIGHT'
      WHEN c.itemid IN (920, 1394, 4187, 3486, 3485, 4188)
      THEN 'HEIGHT'
    END AS parameter,
    CASE
      WHEN c.itemid   IN (3581)
      THEN c.valuenum * 0.45359237
      WHEN c.itemid   IN (3582)
      THEN c.valuenum * 0.0283495231
      WHEN c.itemid   IN (920, 1394, 4187, 3486)
      THEN c.valuenum * 2.54
      ELSE c.valuenum
    END AS valuenum
  FROM mimiciii.chartevents c
  WHERE c.valuenum   IS NOT NULL
  AND ( ( c.itemid  IN (762, 763, 3723, 3580, -- Weight Kg
    3581,                                     -- Weight lb
    3582,                                     -- Weight oz
    920, 1394, 4187, 3486,                    -- Height inches
    3485, 4188                                -- Height cm
    )
  AND c.valuenum <> 0 )
    ) )
  --)

  --select * from FirstVRawData
, SingleParameters AS (
  SELECT DISTINCT subject_id,
         icustay_id,
         parameter,
         first_value(valuenum) over (partition BY subject_id, icustay_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_valuenum,
         MIN(valuenum) over (partition BY subject_id, icustay_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS min_valuenum,
         MAX(valuenum) over (partition BY subject_id, icustay_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS max_valuenum
    FROM FirstVRawData



--   ORDER BY subject_id,
--            icustay_id,
--            parameter
  )
--select * from SingleParameters
, PivotParameters AS (SELECT subject_id, icustay_id,
    MAX(case when parameter = 'HEIGHT' then first_valuenum else NULL end) AS height_first,
    MAX(case when parameter =  'HEIGHT' then min_valuenum else NULL end)   AS height_min,
    MAX(case when parameter =  'HEIGHT' then max_valuenum else NULL end)   AS height_max,
    MAX(case when parameter =  'WEIGHT' then first_valuenum else NULL end) AS weight_first,
    MAX(case when parameter =  'WEIGHT' then min_valuenum else NULL end)   AS weight_min,
    MAX(case when parameter =  'WEIGHT' then max_valuenum else NULL end)   AS weight_max
  FROM SingleParameters
  GROUP BY subject_id,
    icustay_id
  )
--select * from PivotParameters
SELECT f.icustay_id,
  f.subject_id,
  ROUND( cast(f.height_first as numeric), 2) AS height_first,
  ROUND(cast(f.height_min as numeric),2) AS height_min,
  ROUND(cast(f.height_max as numeric),2) AS height_max,
  ROUND(cast(f.weight_first as numeric), 2) AS weight_first,
  ROUND(cast(f.weight_min as numeric), 2)   AS weight_min,
  ROUND(cast(f.weight_max as numeric), 2)   AS weight_max

FROM PivotParameters f
ORDER BY subject_id, icustay_id;

--COMMENT ON MATERIALIZED VIEW mimiciii.icustay_detail IS
-- '
--   Expands the table "ICUSTAYEVENTS" to show:
-- 
--      +  Each ICU stay is order by the column HOSPITAL_ICUSTAY_SEQ per
--         hospitalization
--      +  Each ICU stay is order by the column ICUSTAY_SEQ
--      +  The first and last ICU stays per hospitalization
--      +  First/last hospitalizations
--      +  The icu expiration flag is assigned to the last icu_stay in the last
--         hospitalization.
--  ';

--COMMENT ON COLUMN mimic2v26.icustay_detail.subject_id is 'Unique subject identifier';
--COMMENT ON COLUMN mimic2v26.icustay_detail.gender is 'Subject''s gender "M" or "F"';
--COMMENT ON COLUMN mimic2v26.icustay_detail.dob is 'Subject''s date of birth';
--COMMENT ON COLUMN mimic2v26.icustay_detail.icustay_id is 'Unique ICU stay identifier';
--COMMENT ON COLUMN mimic2v26.icustay_detail.height is 'The first entered height of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_first is 'The first entered weight of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_max is 'The maximum entered weight of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_min is 'The minimum entered weight of the patient';


--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.icustay_detail','c');
--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.d_chartitems_detail','c');
--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.icustay_days','c');


SELECT * FROM franck.heightweight LIMIT 100;

Some statistics:

-- Some statistics:

SELECT COUNT(DISTINCT(icustay_id)) FROM mimiciii.icustayevents; -- returns 61532
SELECT COUNT(DISTINCT(icustay_id)) FROM franck.heightweight; -- returns 30299
SELECT COUNT(DISTINCT(icustay_id)) FROM franck.heightweight WHERE height_first IS NULL; -- returns 13203
SELECT COUNT(DISTINCT(icustay_id)) FROM franck.heightweight WHERE weight_first IS NULL; -- returns 29

It will create the following table:

If you prefer to group by hadm_id instead of icustay_id, you can simply replace icustay_id by hadm_id in the table creation query:

-- ------------------------------------------------------------------
-- Title: Extract height and weight for hadm_ids
-- Description: This query gets the first, minimum, and maximum weight and height
--        for a single hadm_id. It extracts data from the CHARTEVENTS table.
-- MIMIC version: MIMIC-III v1.2
-- Created by: Erin Hong, Alistair Johnson
-- ------------------------------------------------------------------

-- DROP VIEW franck.heightweight;
DROP TABLE franck.heightweight2;

CREATE TABLE franck.heightweight2
AS
WITH FirstVRawData AS
  (SELECT c.charttime,
    c.itemid,c.subject_id,c.hadm_id,
    CASE
      WHEN c.itemid IN (762, 763, 3723, 3580, 3581, 3582)
      THEN 'WEIGHT'
      WHEN c.itemid IN (920, 1394, 4187, 3486, 3485, 4188)
      THEN 'HEIGHT'
    END AS parameter,
    CASE
      WHEN c.itemid   IN (3581)
      THEN c.valuenum * 0.45359237
      WHEN c.itemid   IN (3582)
      THEN c.valuenum * 0.0283495231
      WHEN c.itemid   IN (920, 1394, 4187, 3486)
      THEN c.valuenum * 2.54
      ELSE c.valuenum
    END AS valuenum
  FROM mimiciii.chartevents c
  WHERE c.valuenum   IS NOT NULL
  AND ( ( c.itemid  IN (762, 763, 3723, 3580, -- Weight Kg
    3581,                                     -- Weight lb
    3582,                                     -- Weight oz
    920, 1394, 4187, 3486,                    -- Height inches
    3485, 4188                                -- Height cm
    )
  AND c.valuenum <> 0 )
    ) )
  --)

  --select * from FirstVRawData
, SingleParameters AS (
  SELECT DISTINCT subject_id,
         hadm_id,
         parameter,
         first_value(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_valuenum,
         MIN(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS min_valuenum,
         MAX(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS max_valuenum
    FROM FirstVRawData



--   ORDER BY subject_id,
--            hadm_id,
--            parameter
  )
--select * from SingleParameters
, PivotParameters AS (SELECT subject_id, hadm_id,
    MAX(case when parameter = 'HEIGHT' then first_valuenum else NULL end) AS height_first,
    MAX(case when parameter =  'HEIGHT' then min_valuenum else NULL end)   AS height_min,
    MAX(case when parameter =  'HEIGHT' then max_valuenum else NULL end)   AS height_max,
    MAX(case when parameter =  'WEIGHT' then first_valuenum else NULL end) AS weight_first,
    MAX(case when parameter =  'WEIGHT' then min_valuenum else NULL end)   AS weight_min,
    MAX(case when parameter =  'WEIGHT' then max_valuenum else NULL end)   AS weight_max
  FROM SingleParameters
  GROUP BY subject_id,
    hadm_id
  )
--select * from PivotParameters
SELECT f.hadm_id,
  f.subject_id,
  ROUND( cast(f.height_first as numeric), 2) AS height_first,
  ROUND(cast(f.height_min as numeric),2) AS height_min,
  ROUND(cast(f.height_max as numeric),2) AS height_max,
  ROUND(cast(f.weight_first as numeric), 2) AS weight_first,
  ROUND(cast(f.weight_min as numeric), 2)   AS weight_min,
  ROUND(cast(f.weight_max as numeric), 2)   AS weight_max

FROM PivotParameters f
ORDER BY subject_id, hadm_id;

--COMMENT ON MATERIALIZED VIEW mimiciii.icustay_detail IS
-- '
--   Expands the table "ICUSTAYEVENTS" to show:
-- 
--      +  Each ICU stay is order by the column HOSPITAL_ICUSTAY_SEQ per
--         hospitalization
--      +  Each ICU stay is order by the column ICUSTAY_SEQ
--      +  The first and last ICU stays per hospitalization
--      +  First/last hospitalizations
--      +  The icu expiration flag is assigned to the last icu_stay in the last
--         hospitalization.
--  ';

--COMMENT ON COLUMN mimic2v26.icustay_detail.subject_id is 'Unique subject identifier';
--COMMENT ON COLUMN mimic2v26.icustay_detail.gender is 'Subject''s gender "M" or "F"';
--COMMENT ON COLUMN mimic2v26.icustay_detail.dob is 'Subject''s date of birth';
--COMMENT ON COLUMN mimic2v26.icustay_detail.hadm_id is 'Unique ICU stay identifier';
--COMMENT ON COLUMN mimic2v26.icustay_detail.height is 'The first entered height of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_first is 'The first entered weight of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_max is 'The maximum entered weight of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_min is 'The minimum entered weight of the patient';


--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.icustay_detail','c');
--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.d_chartitems_detail','c');
--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.icustay_days','c');


SELECT * FROM franck.heightweight2 LIMIT 100;

Some statistics:

-- Some statistics:

SELECT COUNT(DISTINCT(hadm_id)) FROM mimiciii.icustayevents; -- returns 57786
SELECT COUNT(DISTINCT(hadm_id)) FROM franck.heightweight2; -- returns 28946
SELECT COUNT(DISTINCT(hadm_id)) FROM franck.heightweight2 WHERE height_first IS NULL; -- returns 12282
SELECT COUNT(DISTINCT(hadm_id)) FROM franck.heightweight2 WHERE weight_first IS NULL; -- returns 27

This will give you:

If you want to all get the means of heights and weights for each patient:

-- ------------------------------------------------------------------
-- Title: Extract height and weight for hadm_ids
-- Description: This query gets the first, minimum, and maximum weight and height
--        for a single hadm_id. It extracts data from the CHARTEVENTS table.
-- MIMIC version: MIMIC-III v1.2
-- Created by: Erin Hong, Alistair Johnson
-- ------------------------------------------------------------------

-- DROP VIEW franck.heightweight;
DROP TABLE franck.heightweight2;

CREATE TABLE franck.heightweight2
AS
WITH FirstVRawData AS
  (SELECT c.charttime,
    c.itemid,c.subject_id,c.hadm_id,
    CASE
      WHEN c.itemid IN (762, 763, 3723, 3580, 3581, 3582)
      THEN 'WEIGHT'
      WHEN c.itemid IN (920, 1394, 4187, 3486, 3485, 4188)
      THEN 'HEIGHT'
    END AS parameter,
    CASE
      WHEN c.itemid   IN (3581)
      THEN c.valuenum * 0.45359237
      WHEN c.itemid   IN (3582)
      THEN c.valuenum * 0.0283495231
      WHEN c.itemid   IN (920, 1394, 4187, 3486)
      THEN c.valuenum * 2.54
      ELSE c.valuenum
    END AS valuenum
  FROM mimiciii.chartevents c
  WHERE c.valuenum   IS NOT NULL
  AND ( ( c.itemid  IN (762, 763, 3723, 3580, -- Weight Kg
    3581,                                     -- Weight lb
    3582,                                     -- Weight oz
    920, 1394, 4187, 3486,                    -- Height inches
    3485, 4188                                -- Height cm
    )
  AND c.valuenum <> 0 )
    ) )
  --)

  --select * from FirstVRawData
, SingleParameters AS (
  SELECT DISTINCT subject_id,
         hadm_id,
         parameter,
         first_value(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_valuenum,
         MIN(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS min_valuenum,
         AVG(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS mean_valuenum,
         MAX(valuenum) over (partition BY subject_id, hadm_id, parameter order by charttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)         AS max_valuenum
    FROM FirstVRawData



--   ORDER BY subject_id,
--            hadm_id,
--            parameter
  )
--select * from SingleParameters
, PivotParameters AS (SELECT subject_id, hadm_id,
    MAX(case when parameter = 'HEIGHT' then first_valuenum else NULL end) AS height_first,
    MAX(case when parameter =  'HEIGHT' then min_valuenum else NULL end)   AS height_min,
    AVG(case when parameter =  'HEIGHT' then mean_valuenum else NULL end)   AS height_mean,
    MAX(case when parameter =  'HEIGHT' then max_valuenum else NULL end)   AS height_max,
    MAX(case when parameter =  'WEIGHT' then first_valuenum else NULL end) AS weight_first,
    MAX(case when parameter =  'WEIGHT' then min_valuenum else NULL end)   AS weight_min,
    AVG(case when parameter =  'WEIGHT' then mean_valuenum else NULL end)   AS weight_mean,
    MAX(case when parameter =  'WEIGHT' then max_valuenum else NULL end)   AS weight_max
  FROM SingleParameters
  GROUP BY subject_id,
    hadm_id
  )
--select * from PivotParameters
SELECT f.hadm_id,
  f.subject_id,
  ROUND( cast(f.height_first as numeric), 2) AS height_first,
  ROUND(cast(f.height_min as numeric),2) AS height_min,
  ROUND(cast(f.height_mean as numeric),2) AS height_mean,
  ROUND(cast(f.height_max as numeric),2) AS height_max,
  ROUND(cast(f.weight_first as numeric), 2) AS weight_first,
  ROUND(cast(f.weight_min as numeric), 2)   AS weight_min,
  ROUND(cast(f.weight_mean as numeric), 2)   AS weight_mean,
  ROUND(cast(f.weight_max as numeric), 2)   AS weight_max

FROM PivotParameters f
ORDER BY subject_id, hadm_id;

--COMMENT ON MATERIALIZED VIEW mimiciii.icustay_detail IS
-- '
--   Expands the table "ICUSTAYEVENTS" to show:
-- 
--      +  Each ICU stay is order by the column HOSPITAL_ICUSTAY_SEQ per
--         hospitalization
--      +  Each ICU stay is order by the column ICUSTAY_SEQ
--      +  The first and last ICU stays per hospitalization
--      +  First/last hospitalizations
--      +  The icu expiration flag is assigned to the last icu_stay in the last
--         hospitalization.
--  ';

--COMMENT ON COLUMN mimic2v26.icustay_detail.subject_id is 'Unique subject identifier';
--COMMENT ON COLUMN mimic2v26.icustay_detail.gender is 'Subject''s gender "M" or "F"';
--COMMENT ON COLUMN mimic2v26.icustay_detail.dob is 'Subject''s date of birth';
--COMMENT ON COLUMN mimic2v26.icustay_detail.hadm_id is 'Unique ICU stay identifier';
--COMMENT ON COLUMN mimic2v26.icustay_detail.height is 'The first entered height of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_first is 'The first entered weight of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_max is 'The maximum entered weight of the patient';
--COMMENT ON COLUMN mimic2v26.icustay_detail.weight_min is 'The minimum entered weight of the patient';


--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.icustay_detail','c');
--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.d_chartitems_detail','c');
--execute DBMS_SNAPSHOT.REFRESH( 'mimiciii.icustay_days','c');


SELECT * FROM franck.heightweight2 LIMIT 100;

output:

To add the ideal body weight:

ALTER TABLE franck.heightweight2 ADD COLUMN ideal_body_weight_devine NUMERIC;

-- Formula from https://mail.google.com/mail/u/0/#search/Anuj.Mehta2%40bmc.org/151658cd363c4e9a
-- Males
UPDATE franck.heightweight2
SET ideal_body_weight_devine= 50 + 0.91 * (height_mean - 152.4) 
FROM mimiciii.patients
WHERE patients.subject_id=heightweight2.subject_id
AND patients.gender = 'M';

-- Females
UPDATE franck.heightweight2
SET ideal_body_weight_devine= 45.5 + 0.91 * (height_mean - 152.4) 
FROM mimiciii.patients
WHERE patients.subject_id=heightweight2.subject_id
AND patients.gender = 'F';

Dr D.C. Ross · Accepted Answer · 2018-01-30 15:40:58Z

1

see this link from the GITHUB code repository for MIMIC iii

https://github.com/MIT-LCP/mimic-code/blob/master/concepts/firstday/height-first-day.sql

answered Jan 30, 2018 at 15:40

Dr D.C. Ross

666 bronze badges

Add a comment |

Stack Exchange Network

Is the patient's height available?

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
mimic-iii
or ask your own question.

Linked

Hot Network Questions

Is the patient's height available?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mimic-iii or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mimic-iii
or ask your own question.