欢迎访问诺维之舟医学科研平台,我们的课程比丁香园更香!
Language:

MIMIC课程

MIMIC数据库SCI复现课程 Day2: 数据提取到数据清洗

时间:2024-09-01 16:07:08 阅读:542

诺维医学科研官网:https://www.newboat.top 我们的课程比丁香园更香!

Bilibili:文章对应的讲解视频在此。熊大学习社 https://space.bilibili.com/475774512

微信公众号|Bilibili|抖音|小红书|知乎|全网同名:熊大学习社

医学资源网站https://med.newboat.top/ ,内有医学离线数据库、数据提取、科研神器等高质量资料库

课程相关资料:

(1)课程资料包括[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义;[Day3] 复现论文、复现代码、复现数据、学习资料、讲义[Day4]扩展学习资料和相关源码等。关注公众号“熊大学习社”,回复“mimic01”,获取全部4天MIMIC复现课程资料链接。

(2)医学公共数据数据库学习训练营已开班,论文指导精英班学员可获取推荐审稿人信息,欢迎咨询课程助理!

了解详情|医学公共数据库学习训练营


(3)关注熊大学习社。您的一键三连是我最大的动力。

0 课程的总体框架

image-20240405234019064

image-20240405234042324

  • Day1:一、MIMIC数据库零基础入门

    (1)MIMIC数据库获取

    (2)MIMIC数据库软件安装

    (3)MIMIC数据表介绍、基础数据提取

  • Day2:二、MIMIC数据库数据提取与清洗

    (1)物化视图安装与简介

    (2)关键数据提取与实操

    (3)数据清洗实操

  • Day3:三、MIMIC数据库SCI论文复现上(生存分析SCI论文研究思路复现)

    (1)数据提取

    (2)PSM倾向评分

    (3)数据提取与清洗

  • Day4:四、MIMIC数据库SCI论文复现下

    (1)多模型Logistic回归模型

    (2)限制性立方样条图RCS

    (3)亚组分析

这次直播课程的特点:上手操作+撸代码,零基础到SCI复现,随时互动交流,快速开启你的医学研究。

一、MIMIC IV物化视图

MIMIC IV物化视图如何安装,物化视图有哪些,主要有什么用?

1 补充:Navicat中的表显示不出来,怎么办?

Navicat的表显示不出来的问题已解决,是版本过低导致的。升级版本即可,已放课程资料包。

2 建立MIMIC IV数据库物化视图

在G:/program files/MIMIC/mimic-code-main/mimic-iv/concepts_postgres(你的SQL命令文件夹)目录下运行cmd.

# 访问数据库
psql -U postgres -p 5432

# 进入mimiciv数据库
c mimiciv

# 生成函数
i 'postgres-functions.sql'

# 视图
i 'postgres-make-concepts.sql'

看一下postgres-make-concepts.sql

image-20240409074151865

里面用的是相对路径,这就是为什么要在上面指定的目录。

image-20240409123313680

mimiciv_derived版块其实是在mimic_hosp及mimic_icu两个模块基础上然后在MIMIC数据库官网下载其提供的可视图化代码,然后运行而得出来的61个视图表。

image-20240409124923147

也可通过命令行查看。

-- 查看视图
SELECT * FROM information_schema.views;

-- 查看物化视图
SELECT mv.relname AS materialized_view,
      pg_get_viewdef(mv.oid) AS definition
FROM pg_class mv
WHERE mv.relkind = 'm';

从上面的命令行运行结果发现,有报错信息。最后生成的物化视图58个,还缺3个:icustay_hourlysofasepsis3

image-20240413114553053

报错的问题是UNNEST函数不存在。改写代码,不用UNNEST函数实现。

icustay_hourly.sql的代码改写后如下:

-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS mimiciv_derived.icustay_hourly; CREATE TABLE mimiciv_derived.icustay_hourly AS
/* This query generates a row for every hour the patient is in the ICU. */ /* The hours are based on clock-hours (i.e. 02:00, 03:00). */ /* The hour clock starts 24 hours before the first heart rate measurement. */ /* Note that the time of the first heart rate measurement is ceilinged to */ /* the hour. */ /* this query extracts the cohort and every possible hour they were in the ICU */ /* this table can be to other tables on stay_id and (ENDTIME - 1 hour,ENDTIME] */ /* get first/last measurement time */
WITH all_hours AS (
 SELECT
   it.stay_id,
   CASE
     WHEN DATE_TRUNC('HOUR', it.intime_hr) = it.intime_hr
     THEN it.intime_hr
     ELSE DATE_TRUNC('HOUR', it.intime_hr) + INTERVAL '1 HOUR'
   END AS endtime,
   GENERATE_SERIES(-24, CAST(CEIL(EXTRACT(EPOCH FROM it.outtime_hr - it.intime_hr) / 3600.0) AS INT)) AS hrs,
   row_number() over (partition by it.stay_id order by it.intime_hr) as rn
 FROM mimiciv_derived.icustay_times AS it
)
SELECT
 a.stay_id,
 CAST(a.hrs AS BIGINT) AS hr,
 a.endtime + CAST(b.hrs AS BIGINT) * INTERVAL '1 HOUR' AS endtime
FROM all_hours a
JOIN all_hours b ON a.stay_id = b.stay_id AND a.rn = b.rn;

进入到三个文件的上一级目录,再运行cmd,然后依次运行sql代码就行。

-- cmd
psql -U postgres -p 5432

-- 进入mimiciv数据库
c mimiciv

-- 物化视图:demographics/icustay_hourly.sql
i 'demographics/icustay_hourly.sql'


-- 物化视图:score/sofa.sql
i 'score/sofa.sql'

-- 物化视图:sepsis/sepsis3.sql
i 'sepsis/sepsis3.sql'


3 物化视图

(1)age,年龄

SELECT   
 ad.subject_id
 , ad.hadm_id
 , ad.admittime
 , pa.anchor_age
 , pa.anchor_year
 , mimiciv_derived.DATETIME_DIFF(ad.admittime, mimiciv_derived.DATETIME(pa.anchor_year, 1, 1, 0, 0,0),'YEAR') + pa.anchor_age AS age
FROM mimiciv_hosp.admissions  AS ad
INNER JOIN mimiciv_hosp.patients  AS pa
ON ad.subject_id = pa.subject_id
;

image-20240409175739871

这里就使用了官方函数 "mimiciv_derived.DATETIME_DIFF" 和 “mimiciv_derived.DATETIME”, 如果您没有安装官方函数就会报错!

(2)weight_duration,体重变化

weight_duration, ICU期间体重的变化,体重是反应患者营养状况的重要因素。具体代码看物化视图生成所调用的sql文件。

image-20240409225153266

(3)GCS, 神经系统功能评分

GCS评分, 神经系统功能评分。

image-20240409232938579

二、MIMIC IV数据提取

MIMIC IV的关键数据有哪些,常用提取命令是什么?

1 MIMIC IV关键数据与表格

mimiciv_derived模块的61个视图具有非常多的提取信息,基本上一个入住ICU病人的90%以上的信息都能在这提取!!

除了61个视图,还有15个函数。

image-20240409175616948

(1)计算患者的真实年龄

患者的真实年龄 = anchor_age + admittime - anchor_year

anchor_age,anchor_year可以在mimiciv_hosp.patients表中找到

患者的入院年龄可以在mimiciv_hosp.admissions表中找到。

  • 第一种方法:patients表和admissions表联合查询

公式用sql代码表示:

pa.anchor_age + mimiciv_derived.DATETIME_DIFF(ad.admittime, 
   mimiciv_derived.DATETIME(pa.anchor_year, 1, 1, 0, 0, 0)

这里就使用了官方函数 "mimiciv_derived.DATETIME_DIFF" 和 “mimiciv_derived.DATETIME”, 如果您没有安装官方函数就会报错!

这个算法算出来的年龄是小数,我们可以使用ROUND函数转成整数,完整SQL如下:

SELECT  ad.subject_id,  
MAX (ROUND(pa.anchor_age + mimiciv_derived.datetime_diff ( ad.admittime, mimiciv_derived.DATETIME ( pa.anchor_year, 1, 1, 0, 0, 0 ), 'YEAR' ), 0)) AS Age_real
FROM  mimiciv_hosp.admissions AS ad,  mimiciv_hosp.patients AS pa
WHERE  ad.subject_id = pa.subject_id
GROUP BY  ad.subject_id   LIMIT 200;

image-20240409192408562

第二种方法:物化视图age表

SELECT subject_id, ROUND(age,0) as age 
FROM age
LIMIT 100

image-20240409225002893


(2)排除小于18岁的患者

  • 第一种方法

先使用子查询先查询出来患者的真实年龄,再排除掉小于18岁的患者。

with base as (     SELECT ad.subject_id,     MAX(ROUND(pa.anchor_age + mimiciv_derived.DATETIME_DIFF(ad.admittime,           mimiciv_derived.DATETIME(pa.anchor_year, 1, 1, 0, 0, 0), 'YEAR'),0)) AS age     FROM mimiciv_hosp.admissions ad, mimiciv_hosp.patients pa     WHERE ad.subject_id = pa.subject_id     GROUP BY ad.subject_id ) SELECT * FROM base WHERE base.age >=18 limit 100;

image-20240409222229772

可以根据代码修改筛选出来其他年龄段的患者。

  • 第二种方法

SELECT subject_id, ROUND(age,0) as age  FROM mimic_derived.age  WHERE anchor_age >=18 LIMIT 100


(3)首次入院记录

以急性胰腺反复入院的患者为例,仅保留首次入院数据。

MIMIV IV数据库记录了29万个病人的43万条入院记录,但是在做数据分析和提取的时候,通常只需要提取某个病人的首次入院记录。

  • 入院时间排序

postgres数据库的内置函数ROW_NUMBER,可将患者进行分组,并可以按照入院时间排序。

SELECT ADM.SUBJECT_ID, ADM.HADM_ID, ADM.ADMITTIME,  -- 分组排序 ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK FROM MIMICIV_HOSP.ADMISSIONS AS ADM LIMIT 100;

image-20240409223405918

  • 获取首次入院记录

利用子查询,过滤出患者的首次入院记录

WITH base AS (     SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,       ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK     FROM MIMICIV_HOSP.ADMISSIONS AS ADM ) SELECT * FROM base WHERE base.ADMITTIME_RANK = 1 LIMIT 100;

image-20240409223738609

(4)首次进ICU记录

  • 第一种方法:icustays

一个患者可以多次住ICU,一个subject_id对应着多个icustay_id。研究通常只选择该患者第一次住ICU的记录。

入ICU记录按照intime排序,这样就可以看到每个icustay_id是对应患者的第几次入ICU。

-- 每个病人按照进icu时间排序 select stay_id, rank() over (partition by subject_id order by intime) as icu_order  from icustays

image-20240410001636372

第二种方法:物化视图icustay_detail

-- 每个病人按照进icu时间排序 select subject_id, stay_id from icustay_detail where first_icu_stay = 't'

image-20240410002044260

(5)高血压(hypertension)患者数据

d_icd_diagnoses查找高血压的ICD编号和版本。

-- 高血压的icd SELECT * FROM "D_ICD_DIAGNOSES" WHERE long_title like '%hypertension%'

结果很多个,具体哪一个呢,还是哪几个。

image-20240409075633521

如果确定高血压的具体类别是Unspecified essential hypertension(原发性高血压),就确定下来了:icd_code=4019, icd_version=9

-- 查看Unspecified essential hypertension的患者 SELECT DISTINCT (HADM_ID) FROM MIMICIV_HOSP.DIAGNOSES_ICD WHERE ICD_CODE = '4019' and ICD_VERSION = 9 LIMIT 100;

image-20240409080708705

如果不确定,查看这些高血压相关的疾病分别都多少患者手术。一种思路是选最多最广泛的群体。

-- 查看这些高血压相关的疾病分别都多少患者 -- 相关疾病保存到hypertension_diseases WITH hypertension_diseases AS (     SELECT icd_code, icd_version FROM D_ICD_DIAGNOSES WHERE long_title like '%hypertension%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, COUNT(*) AS count FROM DIAGNOSES_ICD d JOIN hypertension_diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version ORDER BY count DESC
  • 阅读SQL代码,掌握其中的知识点。

一是结果赋值;

二是别名;

三是统计数count等函数,还有sum等;

四是联合查询join on;

五是分组;

六是排序。

image-20240409123527223

  • 代码不会写怎么办,借助GPT4来助力。

提问:有两个表,一个是疾病与其编号和编号版本的表D_ICD_DIAGNOSES。二是患者与疾病编号的表DIAGNOSES_ICD。用sql语言,首先模糊查找高血压hypertension的编号和版本,然后根据查出的多个结果,查询DIAGNOSES_ICD表中这些结果对应的患者数量。

image-20240409081903902

image-20240409123615208

GPT给出的代码和最终的代码已经很接近了,稍作调整就可以用!

(6)脓毒症(sepsis)患者数据

  • 总结一下,某疾病的病人数据,可用如下通用代码。

在前面的代码基础上加上了疾病名称long_title。

-- 相关疾病信息保存到diseases WITH diseases AS (     -- %疾病名称英文%对应修改!     SELECT icd_code, icd_version, long_title FROM D_ICD_DIAGNOSES WHERE long_title like '%疾病名称英文%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

以脓毒症(Sepsis)为例。

-- 相关疾病信息保存到diseases WITH diseases AS (     -- Sepsis     SELECT icd_code, icd_version, long_title FROM D_ICD_DIAGNOSES WHERE long_title like '%Sepsis%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

image-20240409132428863

  • 百度翻译(上传截图,图文翻译)

上传截图到百度翻译。

image-20240409132651525

(7)脑梗(cerebral infarction)患者数据

脑卒中,英文stroke

-- 相关疾病信息保存到diseases WITH diseases AS (     -- stroke     SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%stroke%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM MIMICIV_HOSP.DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

image-20240410082830114

发现这个结果不太好,数据量不多,而且不怎么相关。

试试脑梗死,这是脑卒中的一种,英文,cerebral infarction

-- 相关疾病信息保存到diseases WITH diseases AS (     -- cerebral infarction`     SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%cerebral infarction%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM MIMICIV_HOSP.DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

image-20240410124330344

相关疾病类型挺多,需要筛选一下。前2个V1254、Z8673是个人病史,不相关。最后几个G43501、G43509等是没有脑梗的,不纳入。


-- 脑梗患者数据 SELECT d.subject_id,d.hadm_id from mimiciv_hosp.diagnoses_icd d WHERE d.icd_code in ('43301','43330','43331','43390','43391','G43609','43411', 'I630','I63012','I63013','I6302','I63032','I63033','I63039','I6309','I631', 'I6310','I63111','I63112','I63113','I63139','I632','I6320','I63212', 'I63213','I63219','I6322','I63231','I63232','I6330','I63311','I63312', 'I63319','I6332','I63321','I63322','I63339','I6334','I63341','I63342','I63343','I6339','I6341', 'I63411','I63412','I63413','I63419','I6342','I6343','I63431','I63432','I63433', 'I63441','I63442','I6350','I6351','I63511','I63512','I63513','I6352','I63521','I63529','I6353','I63531','I63532','I63541','I63542','I63549','I6359','I636','I69312','I69314','I69341','I69342','I69359','I69361','I69362','I69363','I69364')

image-20240410125328206

(8)生命体征信息(身高、体重、BMI)

  • 取第一天入住ICU身高

SELECT i.subject_id,i.stay_id,i.icu_intime,c.charttime charttime_height,c.height FROM mimiciv_derived.icustay_detail i INNER JOIN mimiciv_derived.height c ON  i.subject_id=c.subject_id AND i.stay_id=c.stay_id WHERE c.charttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND c.height is not NULL

image-20240410172749567

  • 第一天入住ICU体重

SELECT i.subject_id,i.stay_id,i.icu_intime,c.starttime as starttime_weight,c.weight FROM mimiciv_derived.icustay_detail i INNER JOIN mimiciv_derived.weight_durations c ON  i.stay_id=c.stay_id WHERE c.starttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND c.weight is not NULL

image-20240410175503165

  • BMI

可用R语言实现。

# BMI: bmi = weight_kg / (height_m ** 2) d$BMI <- round(d$weight_kg/(d$height_m/100)**2,2) table(d$weight_kg, useNA = 'ifan') table(d$height_m , useNA = 'ifan') table(d$BMI      , useNA = 'ifan') d1 <- subset(d, is.na(d$BMI))

后面结合数据合并进行实操。

(9)GCS神经系统功能评分

第一天入住ICU的GCS评分

with t1 as( SELECT i.subject_id,i.stay_id,i.icu_intime, g.charttime as charttime_gcs, g.gcs, ROW_NUMBER () OVER(PARTITION BY g.SUBJECT_ID ORDER BY g.charttime) AS CHARTTIME_RANK FROM mimiciv_derived.icustay_detail i INNER JOIN mimiciv_derived.gcs g ON i.stay_id=g.stay_id WHERE g.charttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND g.gcs is not NULL ) select * from t1 where CHARTTIME_RANK = 1

image-20240410194811383

GCS、APS III、SAPS II、OASIS这四个指标在物化视图mimiciv_derived中都有对应的表格。稍作修改就能提取。

  1. GCS(Glasgow Coma Scale):格拉斯哥昏迷评分,用于评估患者的意识状态和神经系统功能。该评分系统包括眼睛反应、言语能力和运动反应等项目,将每个项目的得分相加得出总分,总分越低表示患者的意识状态越低,常用于评估颅脑损伤和中风等疾病的严重程度。

  2. APS III(Acute Physiology Score III):急性生理学评分III,是一种用于评估重症患者生理状态的评分系统。APS III通过测量患者的生理指标,如血压、心率、呼吸频率等,以及评估患者的年龄、慢性疾病等因素,来评估患者的疾病严重程度和预后。

  3. SAPS II(Simplified Acute Physiology Score II):简化急性生理学评分II,也是一种用于评估重症患者生理状态的评分系统。SAPS II通过测量患者的生理指标,如血压、体温、血氧饱和度等,以及评估患者的年龄、慢性疾病等因素,来评估患者的疾病严重程度和预后。SAPS II通常用于评估重症监护患者的预后和病情监测。

  4. OASIS(Oxford Acute Severity of Illness Score):牛津急性疾病严重度评分,是一种用于评估急性疾病患者严重程度的评分系统。OASIS考虑了患者的年龄、生理指标(如血压、呼吸频率等)、病情持续时间等因素,并通过计算得出一个综合评分,用于衡量患者的疾病严重程度和预后。

  5. ICP(Intra Cranial Pressure):颅内压力,指颅骨内的压力。ICP是衡量颅脑疾病严重程度的重要指标。正常情况下,颅内压力应处于一定的范围内,但在某些情况下,如颅脑损伤、脑肿瘤等,颅内压力可能升高。监测ICP可以提供有关脑功能和血流情况的重要信息,以及指导治疗和预防并发症的发生。

  6. LODS(Logistic Organ Dysfunction Score):逻辑器官功能紊乱评分,是一种用于评估重症患者多器官功能障碍的评分系统。LODS通过测量患者的生理指标和临床数据,如血压、呼吸频率、血液学指标等,来评估患者器官功能的紊乱程度。该评分系统可用于评估患者的病情严重程度和疾病预后,并用于重症监护和临床研究中。

(10)合并症数据

物化视图charlson表为合并症常用数据表,包括18种疾病和1个指数。


列名称
subject_id:病人ID
hadm_id: 住院ID
age_score:年龄得分
myocardial infarct: 心肌梗死
congestive_heart_failure: 充血性心力衰竭
peripheral_vascular_disease: 外周血管疾病
cerebrovascular_disease:脑血管疾病
dementia:痴呆症
hronic_pulmonary_disease: 慢性肺部疾病
heumatic_disease:风湿病
charlson entic_ulcer_disease:消化性溃疡病
mild liver disease:轻度肝病
diabetsithout_.cc:无并发症的糖尿病
diabetes with ce:有并发症的糖尿病
paraplegia:截瘫
renal disease:肾脏疾病
malignant_cancer:恶性肿瘤
severe_liver_disease: 重度肝病
metastatic_solid-tumor:转移性实体痘
aids:艾滋病
charlson comorbidity index:查尔森共病指数


以Paraplegia(截瘫)、Renal disease(肾脏病)、CCI(charlson_comorbidity_index)为例。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.paraplegia,c.renal_disease,c.charlson_comorbidity_index FROM mimiciv_derived.icustay_detail i INNER JOIN mimiciv_derived.charlson c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id

image-20240410205304454

(11)呼吸衰竭

首先,查询呼吸衰竭的诊断icd-code。呼吸衰竭:respiratory failure。

-- 相关疾病信息保存到diseases WITH diseases AS (     -- respiratory failure     SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%respiratory failure%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM MIMICIV_HOSP.DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

image-20240410215932680

接着,选取ICD编号和版本,提取相应的数据。这里不妨以急性呼吸衰竭(Acute respiratory failure)为例。

with t1 as ( SELECT d.subject_id,d.hadm_id, case when d.icd_code is not null then 1 ELSE 0 END AS Respiratory_failure from mimiciv_hosp.diagnoses_icd d WHERE (d.icd_code in ('51851', '51853', '51881', '51883','51884') and d.icd_version=9) OR (d.icd_code in ('J95821', 'J95822', 'J9600', 'J9601','J9602','J9620','J9621','J9622') and d.icd_version=10) ) -- 在t1的基础上增加了mimiciv_derived.icustay_detail表中的信息 SELECT i.subject_id,i.stay_id,i.icu_intime,t1.Respiratory_failure FROM mimiciv_derived.icustay_detail i LEFT JOIN t1 on t1.subject_id=i.subject_id and t1.hadm_id=i.hadm_id

image-20240410220729879

(12)肺栓塞

首先,查询肺栓塞的诊断icd-code。肺栓塞:pulmonary embolism。

-- 相关疾病信息保存到diseases WITH diseases AS (     -- pulmonary embolism     SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%pulmonary embolism%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM MIMICIV_HOSP.DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

image-20240411080355393

接着,选取ICD编号和版本,提取相应的数据。

('41519' , 'I2699' , '41511', '41512', 'I2692', 'I2698' , '41513' , 'I2609', 'I2694', 'I2602', 'I2693', '67382')

with t1 as ( SELECT d.subject_id,d.hadm_id, case when d.icd_code is not null then 1 ELSE 0 END AS pulmonary_embolism from mimiciv_hosp.diagnoses_icd d WHERE d.icd_code in ('41519' , 'I2699' , '41511', '41512', 'I2692', 'I2698' , '41513' , 'I2609', 'I2694', 'I2602', 'I2693', '67382') ) -- 在t1的基础上增加了mimiciv_derived.icustay_detail表中的信息 SELECT i.subject_id,i.stay_id,i.icu_intime,t1.pulmonary_embolism FROM mimiciv_derived.icustay_detail i LEFT JOIN t1 on t1.subject_id=i.subject_id and t1.hadm_id=i.hadm_id

image-20240411080826732

(13)实验室检查指标:红细胞rbc、wbc、plt、hb

  • 红细胞(rbc)

  • 白细胞(wbc)

  • 血小板(plt, platelet)

  • 血红蛋白浓度(HB, hemoglobin)

这几个指标都在mimiciv_derived.complete_blood_count表中。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.charttime,c.rbc, c.wbc, c.platelet, c.hemoglobin FROM mimiciv_derived.icustay_detail i LEFT JOIN mimiciv_derived.complete_blood_count c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id WHERE c.charttime BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR)  AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND c.rbc is not null

image-20240410223514338


(14)实验室检查指标:血钠、肌酐

  • 血钠(sodium)

  • 肌酐(creatinine)

  • 葡萄糖(glucose)

这两个指标在mimiciv_derived.chemistry表中。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.charttime, c.sodium, c.creatinine, c.glucose FROM mimiciv_derived.icustay_detail i LEFT JOIN mimiciv_derived.chemistry c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id WHERE c.charttime BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR)  AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)

image-20240410223752466

(15)甘油三脂

首先,甘油三酯的编号,在MIMICIV_HOSP.D_LABITEMS中查找,为51000。

SELECT itemid, label FROM MIMICIV_HOSP.D_LABITEMS WHERE label like '%Triglyceride%'

image-20240410224723489

接着,查找检验表labevents,甘油三酯的检测值。

SELECT subject_id, hadm_id, charttime, valuenum FROM mimiciv_hosp.labevents WHERE itemid in ('51000') and valuenum is not null

image-20240410230721395

继续,获取ICU病人的数据信息。

WITH t1 as ( SELECT subject_id, hadm_id, charttime, valuenum FROM mimiciv_hosp.labevents WHERE itemid in ('51000') and valuenum is not null ) SELECT i.subject_id,i.stay_id,i.icu_intime,t1.charttime charttime_Triglyceride,t1.valuenum Triglyceride FROM mimiciv_derived.icustay_detail i INNER JOIN t1 ON i.subject_id= t1.subject_id AND i.hadm_id=t1.hadm_id WHERE t1.charttime  BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR) AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR)

image-20240410230747089


(16)葡萄糖

glucose这个指标在mimiciv_derived.chemistry表中。参考血钠和肌酐的数据提取。

(17)终末期肾功能不全

AKI:急性肾损伤,在在mimiciv_derived.kdigo_stages表中。

aki_stage :疾病状态,3表示终末期。

SELECT i.subject_id,i.stay_id,i.icu_intime,i.first_icu_stay, CASE WHEN c.aki_stage is not null THEN 1 ELSE 0 END AS end_stage_renal_disease FROM mimiciv_derived.icustay_detail i LEFT JOIN mimiciv_derived.kdigo_stages c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id WHERE c.charttime BETWEEN i.icu_intime AND i.icu_outtime AND c.aki_stage in (3)

image-20240411071649606

(18)肝硬化

首先,查询肝硬化的诊断icd-code。肝硬化:cirrhosis。

-- 相关疾病信息保存到diseases WITH diseases AS (     -- cirrhosis     SELECT icd_code, icd_version, long_title FROM MIMICIV_HOSP.D_ICD_DIAGNOSES WHERE long_title like '%cirrhosis%' ) -- 然后,我们可以使用这个子查询来联合查询用户表,并计算指定疾病编号和版本的病人手术数量 SELECT d.icd_code, d.icd_version, h.long_title, COUNT(*) AS count FROM MIMICIV_HOSP.DIAGNOSES_ICD d JOIN diseases h ON d.icd_code = h.icd_code AND d.icd_version = h.icd_version GROUP BY d.icd_code, d.icd_version, h.long_title ORDER BY count DESC

image-20240411072400632

接着,选取ICD编号和版本,提取相应的数据。

with t1 as ( SELECT d.subject_id,d.hadm_id, case when d.icd_code is not null then 1 ELSE 0 END AS cirrhosis from mimiciv_hosp.diagnoses_icd d WHERE d.icd_code in ('5712','5715','5716','K703','K7030','K7031','K717','K74','K741','K742','K743',                      'K744','K745','K746','K7460','K7469','P7881') ) -- 在t1的基础上增加了mimiciv_derived.icustay_detail表中的信息 SELECT i.subject_id,i.stay_id,i.icu_intime,t1.cirrhosis FROM mimiciv_derived.icustay_detail i LEFT JOIN t1 on t1.subject_id=i.subject_id and t1.hadm_id=i.hadm_id

image-20240411073013684

(19)癌症(恶性肿瘤)

癌症,恶性肿瘤,malignant_cancer,在mimiciv_derived.charlson表中。

SELECT i.subject_id,i.stay_id,i.icu_intime,c.malignant_cancer FROM mimiciv_derived.icustay_detail i INNER JOIN mimiciv_derived.charlson c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id

image-20240411072102744


2 MIMIC IV数据提取实操

从常见的研究主题出发,考虑多个情况的组合。比如高血压患者的血液指标、首次入院时年龄不小于18岁的高血压患者。

(1)首次入院时年龄不小于18岁的患者

  • 第一种方法

WITH BASE0 AS (     SELECT AD.hadm_id,  MAX(ROUND(pa.anchor_age + mimiciv_derived.datetime_diff(ad.admittime,     mimiciv_derived.datetime(pa.anchor_year, 1, 1, 0, 0, 0), 'YEAR'), 0)) AS age     FROM mimiciv_hosp.admissions AS AD, mimiciv_hosp.patients AS PA     WHERE ad.subject_id = pa.subject_id     GROUP BY ad.hadm_id      LIMIT 1000 ), BASE1 AS (     SELECT ADM.subject_id, ADM.hadm_id, ADM.admittime,      ROW_NUMBER() OVER( PARTITION BY ADM.subject_id ORDER BY ADM.admittime) AS admittime_rank     FROM mimiciv_hosp.admissions AS ADM ) SELECT * FROM BASE0, BASE1 WHERE BASE0.age >= 18 AND BASE1.admittime_rank = 1 AND BASE0.hadm_id = base1.hadm_id LIMIT 200;

image-20240409235045475

  • 第二种方法

WITH base AS (     SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,       ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK     FROM MIMICIV_HOSP.ADMISSIONS AS ADM ) SELECT * FROM base, age WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id LIMIT 100;

image-20240409235552847

也可把提取的变量名明确。

WITH base AS (     SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,       ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK     FROM MIMICIV_HOSP.ADMISSIONS AS ADM ) SELECT base.subject_id, base.hadm_id, base.admittime, base.admittime_rank, ROUND(age.age, 0) as age FROM base, age WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id LIMIT 100;

image-20240409235849695

(2)首次进ICU时年龄不小于18岁的患者

  • 第一种方法

WITH base AS (     SELECT ICU.SUBJECT_ID,  ICU.STAY_ID,  ICU.intime,       ROW_NUMBER () OVER(PARTITION BY ICU.SUBJECT_ID ORDER BY ICU.intime) AS ADMITTIME_RANK     FROM MIMICIV_ICU.ICUSTAYS AS ICU ) SELECT base.SUBJECT_ID, base.STAY_ID, base.intime, base.ADMITTIME_RANK, ROUND(age.age, 0) as age FROM base, age WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id LIMIT 100;

image-20240410005800640

  • 第二种方法

-- 用age和icustay_detail两个物化视图 select i.subject_id, i.stay_id, ROUND(a.age,0) as age from icustay_detail as i LEFT JOIN age as a ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18

image-20240410005419547

(3)首次入院时年龄不小于18岁的脓毒症患者

WITH base AS (     SELECT ADM.SUBJECT_ID,  ADM.HADM_ID,  ADM.ADMITTIME,       ROW_NUMBER () OVER(PARTITION BY ADM.SUBJECT_ID ORDER BY ADM.ADMITTIME) AS ADMITTIME_RANK     FROM MIMICIV_HOSP.ADMISSIONS AS ADM ), t1 as ( SELECT base.subject_id, base.hadm_id, base.admittime, ROUND(age.age, 0) FROM base, age WHERE base.ADMITTIME_RANK = 1 AND age.age >= 18 AND base.hadm_id = age.hadm_id; ), t2 as ( -- 查看脓毒症的患者 SELECT DISTINCT (HADM_ID) FROM MIMICIV_HOSP.DIAGNOSES_ICD WHERE ICD_CODE = 'A419' and ICD_VERSION = 10 ) SELECT * from t1,t2  WHERE t1.HADM_ID = t2.HADM_ID

image-20240410003723551

(4)首次进ICU时年龄不小于18岁的高血压患者

with t1 as(     -- 用age和icustay_detail两个物化视图     select i.subject_id, i.hadm_id, i.stay_id, ROUND(a.age,0) as age     from icustay_detail as i     LEFT JOIN age as a     ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18 ), t2 as ( -- 查看高血压的患者 SELECT DISTINCT (HADM_ID) FROM MIMICIV_HOSP.DIAGNOSES_ICD WHERE ICD_CODE = '4019' and ICD_VERSION = 9 ) SELECT * from t1,t2  WHERE t1.HADM_ID = t2.HADM_ID

image-20240410010400625

(5)首次进ICU时年龄不小于18岁的脑梗患者

with t1 as(     -- 用age和icustay_detail两个物化视图     select i.subject_id, i.hadm_id, i.stay_id, ROUND(a.age,0) as age     from icustay_detail as i     LEFT JOIN age as a     ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18 ), t2 as ( -- 脑梗患者 SELECT d.subject_id,d.hadm_id from mimiciv_hosp.diagnoses_icd d WHERE d.icd_code in ('43301','43330','43331','43390','43391','G43609','43411','I630','I63012','I63013','I6302','I63032','I63033','I63039','I6309','I631','I6310','I63111','I63112','I63113','I63139','I632','I6320','I63212','I63213','I63219','I6322','I63231','I63232','I6330','I63311','I63312','I63319','I6332','I63321','I63322','I63339','I6334','I63341','I63342','I63343','I6339','I6341','I63411','I63412','I63413','I63419','I6342','I6343','I63431','I63432','I63433','I63441','I63442','I6350','I6351','I63511','I63512','I63513','I6352','I63521','I63529','I6353','I63531','I63532','I63541','I63542','I63549','I6359','I636','I69312','I69314','I69341','I69342','I69359','I69361','I69362','I69363','I69364') ) SELECT t1.* from t1,t2  WHERE t1.HADM_ID = t2.HADM_ID

image-20240410131451720

保存到一个表里。

-- 创建并保存到新表icu_ci CREATE TABLE icu_ci as ( with t1 as(     -- 用age和icustay_detail两个物化视图     select i.subject_id, i.hadm_id, i.stay_id, ROUND(a.age,0) as age     from mimiciv_derived.icustay_detail as i     LEFT JOIN mimiciv_derived.age as a     ON i.first_icu_stay = 't'and i.hadm_id = a.hadm_id and a.age>=18 ), t2 as ( -- 脑梗患者 SELECT d.subject_id,d.hadm_id from mimiciv_hosp.diagnoses_icd d WHERE d.icd_code in ('43301','43330','43331','43390','43391','G43609','43411','I630','I63012','I63013','I6302','I63032','I63033','I63039','I6309','I631','I6310','I63111','I63112','I63113','I63139','I632','I6320','I63212','I63213','I63219','I6322','I63231','I63232','I6330','I63311','I63312','I63319','I6332','I63321','I63322','I63339','I6334','I63341','I63342','I63343','I6339','I6341','I63411','I63412','I63413','I63419','I6342','I6343','I63431','I63432','I63433','I63441','I63442','I6350','I6351','I63511','I63512','I63513','I6352','I63521','I63529','I6353','I63531','I63532','I63541','I63542','I63549','I6359','I636','I69312','I69314','I69341','I69342','I69359','I69361','I69362','I69363','I69364') ) SELECT t1.* from t1,t2  WHERE t1.HADM_ID = t2.HADM_ID )

数据表icu_ci在哪儿?看选定的组件。

image-20240410165645586

image-20240410165657369



三、MIMIC IV数据合并和清洗

1 数据变量

研究主题:甘油三脂葡萄糖指数与肾脏疾病的关联性

确定提取哪些数据呢?如下。


变量名称表名
subject_id病人编号mimiciv_derived.icustay_detail
hadm_id住院编号mimiciv_derived.icustay_detail
stay_idICU编号mimiciv_derived.icustay_detail
age年龄mimiciv_derived.age
intimeICU进入时间mimiciv_derived.icustay_detail
gender性别mimiciv_derived.icustay_detail
height身高mimiciv_derived.height
weight体重mimiciv_derived.weight
BMI体质指数-
GCS神经系统功能评分mimiciv_derived.gcs
rbc红细胞mimiciv_derived.compl
creatinine肌酐mimiciv_derived.chemistry
renal_disease肾脏病mimiciv_derived.charlson
Triglyceride甘油三酯mimiciv_hosp.labevents
glucose葡萄糖mimiciv_derived.chemistry
TyGTyG指数--


2 数据合并

新建schema框架,用以保存我们处理的数据。

DROP SCHEMA IF EXISTS work CASCADE; CREATE SCHEMA work;

上面的数据提取后保存为csv文件。

基准数据:首次进ICU时年龄不小于18岁的患者

思路:

(a)基准数据的基础上逐个加入变量数据;

(b)检查每次加入前后的数据量变化,如果变化太多,要注意并视情况采取适当的方法;

(c)检查合并后的数据重复项。

具体实操如下:

  • STEP1:合并一部分基础数据,处理速度较快,后面保存为work.icu_gcs表。sql代码每次都从头运行,挺费时间!

CREATE TABLE work.icu_gcs AS( -- 用age和icustay_detail两个物化视图 with t1 as (     select i.subject_id, i.hadm_id, i.stay_id,i.gender, i.icu_intime, ROUND(a.age,0) as age,     rank() over (partition by i.subject_id order by i.icu_intime) as icu_order      from mimiciv_derived.icustay_detail as i     LEFT JOIN mimiciv_derived.age as a     ON i.hadm_id = a.hadm_id and a.age>=18 and a.age is not null ), -- 首次ICU且不小于18岁的人员信息,50920 icu_base as (     SELECT * FROM t1     WHERE icu_order in (1) ), -- 身高,25178 icu_height as (     SELECT i.*,c.charttime charttime_height,c.height     FROM icu_sex i     LEFT JOIN mimiciv_derived.height c ON  i.subject_id=c.subject_id AND i.stay_id=c.stay_id     WHERE c.height is not NULL ), -- 体重,25143 t2 as (     SELECT i.*,c.starttime as starttime_weight,c.weight,     rank() over (partition by c.stay_id order by c.starttime) as weight_order     FROM icu_height i     LEFT JOIN mimiciv_derived.weight_durations c ON  i.stay_id=c.stay_id     WHERE c.weight is not NULL ), -- 找出既准确又尽量多的体重数据 t3 as ( SELECT * FROM t2 WHERE (starttime_weight     BETWEEN icu_intime AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR)) OR       (starttime_weight NOT BETWEEN icu_intime AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR) AND weight_order=1)   ), -- 删除重复项,准确优先 t4 as (   SELECT *, rank() over (partition by stay_id order by weight_order DESC) as rn   FROM t3 ), icu_weight as ( SELECT * FROM t4 WHERE rn=1 ), -- GCS评分,25053 t5 as ( SELECT i.*, g.charttime as charttime_gcs, g.gcs, ROW_NUMBER () OVER(PARTITION BY g.SUBJECT_ID ORDER BY g.charttime) AS CHARTTIME_RANK FROM icu_weight i INNER JOIN mimiciv_derived.gcs g ON i.stay_id=g.stay_id WHERE g.charttime BETWEEN i.icu_intime AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND g.gcs is not NULL ) select * from t5 where CHARTTIME_RANK = 1 )
  • STEP2:加入红细胞数据,这里处理时间较长,保存为work.icu_rbc

CREATE TABLE work.icu_rbc AS( -- rbc,红细胞, 24713 with t6 as ( SELECT i.*,c.charttime as charttime_rbc,c.rbc, rank() over (partition by i.stay_id order by c.charttime) as rbc_order FROM work.icu_gcs i LEFT JOIN mimiciv_derived.complete_blood_count c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id WHERE c.charttime BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR)  AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND c.rbc is not null ) SELECT * FROM t6 WHERE rbc_order=1 )
  • STEP3:加入肌酐和葡萄糖数据,保存为work.icu_chemistry

DROP TABLE IF EXISTS work.icu_chemistry; -- 肌酐和葡萄糖,chemistry,24494 CREATE TABLE work.icu_chemistry as ( WITH t7 as ( SELECT i.*,c.charttime as charttime_chem, c.creatinine, c.glucose, rank() over (partition by i.stay_id order by c.charttime) as chem_order FROM work.icu_rbc i LEFT JOIN mimiciv_derived.chemistry c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id WHERE c.charttime BETWEEN mimiciv_derived.DATETIME_SUB(i.icu_intime, INTERVAL '6' HOUR)  AND mimiciv_derived.DATETIME_ADD(i.icu_intime, INTERVAL '24' HOUR) AND c.creatinine is not null and c.glucose is not null ) SELECT * FROM t7 WHERE chem_order=1 )
  • STEP4:加入肾脏病和甘油三脂数据,形成最后的数据集work.icu_data,保存为icu_data.csv,作为后面数据清洗的输入。

先把甘油三脂的实验数据提取,时间最长,10分钟以上。

CREATE TABLE work.icu_tg as ( -- 甘油三酯 SELECT subject_id, hadm_id, charttime, valuenum FROM mimiciv_hosp.labevents WHERE itemid in ('51000') and valuenum is not null )

最后的数据合并,先合并肾脏病,后甘油三酯。

DROP TABLE IF EXISTS work.icu_data; CREATE TABLE work.icu_data as ( with icu_renal as ( -- 肾脏病,renal_disease,24494 SELECT i.*,c.renal_disease FROM work.icu_chemistry i INNER JOIN mimiciv_derived.charlson c ON i.subject_id=c.subject_id AND i.hadm_id=c.hadm_id ), t8 as ( SELECT i.*,t.charttime as charttime_tg,t.valuenum as tg, rank() over (partition by i.subject_id order by t.charttime) as tg_order FROM icu_renal i LEFT JOIN work.icu_tg t ON i.subject_id= t.subject_id AND i.hadm_id=t.hadm_id WHERE t.valuenum is not null ), -- 找出既准确又尽量多的甘油三脂数据,4742 t9 as ( SELECT * FROM t8 WHERE (charttime_tg     BETWEEN mimiciv_derived.DATETIME_SUB(icu_intime, INTERVAL '6' HOUR)  AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR)) OR       (charttime_tg NOT BETWEEN mimiciv_derived.DATETIME_SUB(icu_intime, INTERVAL '6' HOUR)  AND mimiciv_derived.DATETIME_ADD(icu_intime, INTERVAL '24' HOUR) AND tg_order=1)   ), -- 删除重复项,准确优先 t10 as (   SELECT *, rank() over (partition by stay_id order by tg_order DESC) as rn2   FROM t9 ) SELECT * FROM t10 WHERE rn2=1 )

里面有不少空值,后面数据清洗。导出向导,选择csv文件。

image-20240413161828558

3 数据清洗-基于R

  • 数据转换和格式检查

  • BMI和TyG的计算

  • 数据纳排

  • 基线分析

####公众号:熊大学习社#### # 手动设置工作目录为代码和数据所在文件夹 # 步骤方法:点菜单栏“session”->"Set Work Directory"->"Choose Directory" # 选择代码和数据所在文件夹即可 # 查看工作目录 getwd() # 安装库 if(!require(foreign)) install.packages('foreign') if(!require(haven)) install.packages('haven') if(!require(dplyr)) install.packages('dplyr') if(!require(purrr)) install.packages('purrr') if(!require(lubridate)) install.packages('lubridate') if(!require(readxl)) install.packages('readxl') if(!require(tableone)) install.packages('tableone') # 加载库 library(foreign) library(haven) library(dplyr) library(purrr) library(lubridate) library(readxl) library(tableone) # 1 数据准备------ ## 1.1 数据转换和格式检查------ d <- read.csv('icu_data.csv') # 去除列 colnames(d) d <- subset(d, select = -c(icu_intime, charttime_height,                            starttime_weight, weight_order,                            rn, charttime_gcs,charttime_rank,                            charttime_rbc, charttime_chem,                            chem_order,charttime_tg,                            tg_order,rbc_order,rn2)) # 列名变更 colnames(d) d <- plyr::rename(d, c(renal_disease='Renal_disease',                        tg='TG',                        gender='Gender',                        age='Age',                        gcs='GCS',                        rbc='RBC',                        creatinine='Creatinine',                        glucose='Glucose')) # 数据格式 str(d) # 性别 d$Gender[d$Gender=='M'] <- 'Male' d$Gender[d$Gender=='F'] <- 'Female' d$Gender <- factor(d$Gender, levels = c('Male', 'Female')) table(d$Gender, useNA = 'ifan') str(d) ## 1.2 数据纳排------ nrow(d) # 574010 # (1)暴露因素, TyG指数 d <- subset(d, !is.na(d$TG)) 4742 # (2)结局变量,Renal_disease 肾病 d <- subset(d, !is.na(d$Renal_disease))  # 0 d$Renal_disease[d$Renal_disease==1] <- 'Renal' d$Renal_disease[d$Renal_disease==0] <- 'Non-renal' d$Renal_disease <- factor(d$Renal_disease, levels = c('Renal', 'Non-renal')) table(d$Renal_disease, useNA = 'ifan') # (3)协变量,性别 d <- subset(d, !is.na(d$Gender)) ## 1.3 BMI和TyG的计算------ # 体重:lbs # 身高:英寸 # BMI: bmi = weight_kg / (height_m ** 2) d$BMI <- round(d$weight/(d$height/100)**2,2) table(d$BMI, useNA = 'ifan') d1 <- subset(d, is.na(d$BMI)) # 删除不需要的列 d <- subset(d, select = -c(weight,height)) # TyG d$TyG <- round(log(d$TG * d$Glucose/2),2) table(d$TG, useNA = 'ifan') table(d$Glucose, useNA = 'ifan') table(d$TyG, useNA = 'ifan') # 2 基线分析------ ##2.1 基线分析 str(d) var <- c('Gender', 'Age', 'BMI','TG', 'TyG') # 结局变量 t1 <- CreateTableOne(vars = var, strata = c('Renal_disease'), data =d) print(t1, showAllLevels = TRUE, nonnormal = '', smd = F) |> write.csv('..Table 1 基线分析.csv')


四 常用SQL命令集合

1 基础sql

(1)基本查询语句

SELECT * FROM "mimiciv_hosp"."admissions" SELECT * FROM mimiciv_hosp.admissions SELECT * FROM admissions SELECT subject_id,hadm_id FROM mimiciv_hosp.admissions SELECT distinct drug FROM "mimiciv_hosp"."prescriptions"

(2)特定条件查询:where、=、>、<、between、ike、正则表达式

# 需要使用where语句 SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where long_title='hypertension' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where long_title='Secondary hypertension' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where long_title like '%hypertension%' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where lower(long_title) like '%hypertension%' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where lower(long_title) like '%acute heart failure%' SELECT * FROM "mimic_hosp"."d_icd_diagnoses" where lower(long_title) like '%heart failure%' SELECT * FROM "mimic_core"."patients" where anchor_age >50 SELECT * FROM "mimic_core"."patients" where anchor_age between 40 and 50 SELECT * FROM "mimic_core"."patients" where anchor_age>=40 and anchor_age<=50 SELECT * FROM "mimic_core"."patients" where anchor_age>40 and anchor_age<50 # 正则表达式 SELECT * FROM "mimic_hosp"."d_labitems" where label ~*'.*wbc.*|.*white.*blood.*cell.*'

(3)限制条件

SELECT * FROM "mimic_hosp"."d_icd_procedures" where lower(long_title) like '%extracorporeal membrane oxygenation%' SELECT * FROM "mimic_hosp"."d_icd_procedures" where lower(long_title) like '%extracorporeal membrane oxygenation%' and icd_version=9 SELECT * FROM "mimic_hosp"."procedures_icd" where icd_code='3965' SELECT * FROM "mimic_hosp"."procedures_icd" where icd_code='3965' or icd_code='5A15223' SELECT * FROM "mimic_hosp"."procedures_icd" where icd_code in ('3965' ,'5A15223')

(4)四个功能函数

SELECT max(los) FROM "mimic_icu"."icustays" SELECT min(los) FROM "mimic_icu"."icustays" SELECT avg(los) FROM "mimic_icu"."icustays" SELECT sum(los) FROM "mimic_icu"."icustays"

(5)排序order by、ASC/DESC

SELECT itemid,label,fluid FROM "mimic_hosp"."d_labitems" where lower(label) like '%wbc%' SELECT itemid,label,fluid FROM "mimic_hosp"."d_labitems" where lower(label) like '%wbc%' order by fluid ASC SELECT itemid,label,fluid FROM "mimic_hosp"."d_labitems" where lower(label) like '%wbc%' order by fluid DESC

(6)确定索引group by;as、having

$y$<- ("正确代码请联系VX:Yi11446699  You buy the second-hand pirated version, first-hand information please contact VX:Yi11446699") print(y)

(7)新建表格CREATE TABLE、建立临时表WITH、标记CASE

DROP TABLE IF EXISTS lung_cancer_patient; CREATE TABLE lung_cancer_patient as WITH lc as (select subject_id,hadm_id, icd_code , CASE when icd_code = '1623' then 1  when icd_code = '1624' then 1  when icd_code = '1625' then 1  when icd_code = '1628' then 1  when icd_code = '1629' then 1  when icd_code = '20921' then 1 else 0 end as lung_cancer FROM diagnoses_icd  group by subject_id, hadm_id, icd_code order by subject_id,hadm_id,  icd_code) SELECT * FROM lc where lung_cancer=1 MAX(CASE WHEN itemid in (51244, 51245) THEN valuenum ELSE NULL END) AS lymphocytes

(8)left join、right join、inner join、on

SELECT "mimic_hosp"."lung_cancer_patient".subject_id,"mimic_hosp"."lung_cancer_patient".hadm_id FROM "mimic_hosp"."lung_cancer_patient" left join "mimic_icu"."icustays" on "mimic_hosp"."lung_cancer_patient".hadm_id="mimic_icu"."icustays".hadm_id SELECT "mimic_icu"."icustays".subject_id, "mimic_icu"."icustays".hadm_id, "mimic_icu"."icustays".stay_id, "mimic_icu"."icustays".first_careunit, "mimic_icu"."icustays".last_careunit, "mimic_icu"."icustays".intime, "mimic_icu"."icustays".outtime, "mimic_icu"."icustays".los  FROM "mimic_icu"."icustays" right join  "mimic_hosp"."lung_cancer_patient"  on "mimic_hosp"."lung_cancer_patient".hadm_id="mimic_icu"."icustays".hadm_id SELECT * FROM  "mimic_hosp"."diagnoses_icd"  inner join  "mimic_hosp"."d_icd_diagnoses"  on "mimic_hosp"."diagnoses_icd".icd_code="mimic_hosp"."d_icd_diagnoses".icd_code  and "mimic_hosp"."diagnoses_icd".icd_version="mimic_hosp"."d_icd_diagnoses".icd_version order by subject_id

(9)别名设置

SELECT table_alias.column column_alias FROM table table_alias SELECT subject_id,hadm_id FROM mimic_core.admissions SELECT mca.subject_id si,hadm_id hi FROM mimic_core.admissions mca

(10)基本的时间计算函数

DATE(),提取日期或日期/时间表达式的日期部分,比如DATE()对字符串“2154-03-03 04:11:00”处理后,返回的结果是“2154-03-03”;对“2180/9/9”处理后,返回“2180-09-09”,就是标准的年、月、日格式 DATETIME_DIFF(endtime,starttime,'HOUR'):  求endtime-starttime,单位是小时,单位可以根据实际情况换成'SECOND','MINUTE','DAY','YEAR' DATETIME_ADD (datetime, INTERVAL '1' DAY):  该代码是求datetime加1天的时间点,其中,1和DAY都可以改变,比如改成'2' YEAR DATETIME_SUB (datetime, INTERVAL '6' HOUR):  该代码是求datetime减去6小时,也就是datetime前6小时的时间点

2 几个重要的应用场景

(1)提取某种procedure

以提取ECMO为例,思路:从d_icd_procedures中找到ECMO的icd_code。

SELECT * FROM "mimic_hosp"."d_icd_procedures" where lower(long_title) like '%extracorporeal membrane oxygenation%' 3965 5A15223 DROP TABLE IF EXISTS ecmo; CREATE TABLE ECMO AS with ec AS( select subject_id,hadm_id,  icd_code , CASE when icd_code = '3965' then 1  when icd_code = '5A15223' then 1  else 0 end as ecmo from "mimic_hosp"."procedures_icd" group by subject_id, hadm_id, icd_code order by subject_id,hadm_id, icd_code ) SELECT * from ec where ecmo =1

(2) 提取某种药物

以提取阿司匹林为例,并与ICU病人关联。

DROP TABLE IF EXISTS aspirin; CREATE TABLE aspirin as with asp as ( SELECT mimic_icu.icustays.subject_id, mimic_icu.icustays.hadm_id, mimic_icu.icustays.stay_id, mimic_hosp.prescriptions.starttime,  mimic_hosp.prescriptions.stoptime,  mimic_hosp.prescriptions.drug_type,  mimic_hosp.prescriptions.drug,  mimic_hosp.prescriptions.dose_val_rx, mimic_hosp.prescriptions.dose_unit_rx, mimic_hosp.prescriptions.form_val_disp, mimic_hosp.prescriptions.form_unit_disp,  mimic_hosp.prescriptions.doses_per_24_hrs,  mimic_hosp.prescriptions.route, CASE WHEN LOWER(mimic_hosp.prescriptions.drug) LIKE '%aspirin%' THEN 1 ELSE 0 END AS aspirin FROM  mimic_icu.icustays LEFT JOIN mimic_hosp.prescriptions ON mimic_hosp.prescriptions.subject_id=icustays.subject_id AND mimic_hosp.prescriptions.hadm_id=icustays.hadm_id) SELECT * from asp where aspirin=1

(3) 提取某种合并症

以提取肥胖obesity为例。

DROP TABLE IF EXISTS obesity; CREATE TABLE obesity AS with ob AS( select subject_id,hadm_id,  icd_code , CASE when icd_code = '27800' then 1  when icd_code = '27801' then 1  when icd_code = '27803' then 1  when icd_code = '64910' then 1  when icd_code = '64911' then 1  when icd_code = '64912' then 1  when icd_code = '64913' then 1  when icd_code = '64914' then 1  when icd_code = 'E66' then 1  when icd_code = 'E660' then 1  when icd_code = 'E6601' then 1  when icd_code = 'E6609' then 1  when icd_code = 'E661' then 1  when icd_code = 'E662' then 1  when icd_code = 'E668' then 1  when icd_code = 'E669' then 1  when icd_code = 'O9921' then 1  when icd_code = 'O99210' then 1  when icd_code = 'O99211' then 1  when icd_code = 'O99212' then 1  when icd_code = 'O99213' then 1  when icd_code = 'O99214' then 1  when icd_code = 'O99215' then 1  else 0 end as obesity  from "mimic_hosp"."diagnoses_icd" group by subject_id, hadm_id, icd_code order by subject_id,hadm_id, icd_code ) SELECT * from ob where obesity=1

(4)提取某个实验室检查

  • 提取物化视图没有的实验室检查(以CRP为例)

思路:首先从字典d_labitems表格中找到CRP的itemid,然后与labevents进行关联。

SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%crp%' 51652 SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%c-reactive protein%' 50889 SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%c reactive protein%' SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%reactive protein%' SELECT * FROM "mimic_hosp"."d_labitems" where lower(label) like '%protein%' DROP TABLE IF EXISTS crp; CREATE TABLE crp AS WITH crp as (SELECT subject_id,hadm_id,charttime,itemid,valuenum as crp_level FROM "mimic_hosp"."labevents" WHERE itemid in (51652, 50889) AND valuenum IS NOT NULL) SELECT subject_id,charttime,hadm_id,itemid, crp_level FROM crp GROUP BY subject_id,hadm_id,charttime,itemid,crp_level
  • 提取物化视图已有的实验室检查(以RDW为例)

SELECT icustays.stay_id , MIN(rdw) as rdw_min , MAX(rdw) as rdw_max , AVG(rdw) as rdw_mean   FROM icustays  LEFT JOIN complete_blood_count ON complete_blood_count.subject_id = icustays.subject_id AND complete_blood_count.charttime <= DATETIME_ADD(icustays.intime, INTERVAL '1' DAY) GROUP BY icustays.stay_id

(5) 提取首次住ICU的基本信息

常用思路的代码:

WITH icustay_order_view as ( SELECT subject_id,hadm_id, stay_id,intime,outtime,los, row_number() over (partition by subject_id order by intime) as icustay_order  FROM "mimic_icu"."icustays" ) select * from icustay_order_view where icustay_order = 1 SELECT * FROM "mimic_icu"."icustays"  SELECT * FROM "mimic_icu"."icustays" ORDER BY subject_id

以使用NE病人为例,首先提取一下使用NE(去甲肾上腺素)的病人

DROP TABLE IF EXISTS NE; CREATE TABLE NE AS WITH norE as ( SELECT icustays.subject_id, icustays.hadm_id, icustays.stay_id, icustays.intime,icustays.outtime,  mimic_hosp.prescriptions.starttime,  mimic_hosp.prescriptions.stoptime,  mimic_hosp.prescriptions.drug_type,  mimic_hosp.prescriptions.drug,  mimic_hosp.prescriptions.dose_val_rx, mimic_hosp.prescriptions.dose_unit_rx, mimic_hosp.prescriptions.form_val_disp, mimic_hosp.prescriptions.form_unit_disp,  mimic_hosp.prescriptions.doses_per_24_hrs,  mimic_hosp.prescriptions.route, CASE WHEN LOWER(mimic_hosp.prescriptions.drug) LIKE '%orepinephrine%' THEN 1 ELSE 0 END AS norepinephrine FROM icustays LEFT JOIN mimic_hosp.prescriptions ON mimic_hosp.prescriptions.subject_id=icustays.subject_id AND mimic_hosp.prescriptions.hadm_id=icustays.hadm_id) SELECT * from norE where norepinephrine=1 WITH stay_order_view as ( SELECT subject_id,hadm_id, stay_id,norepinephrine, row_number() over (partition by stay_id order by subject_id) as stay_order  FROM "mimic_icu"."ne" ) select * from stay_order_view where stay_order = 1

(6) 提取入ICU后每天液体入量

SELECT mi.stay_id,ceil(DATETIME_DIFF(mi.starttime, mic.intime,'DAY')) as date_num,  sum(mi.totalamount)  FROM "mimic_icu"."inputevents" mi left join "mimic_icu"."icustays" mic on mi.stay_id = mic.stay_id  where mi.totalamount is not null  and mi.totalamount > 0  and mi.starttime > mic.intime  GROUP BY mi.stay_id, date_num

五 小结

(1)MIMIC数据分析研究建议。

一是做好数据检查,有没有遗漏、有没有重复、有没有多删或少删。基础数据(如首次不小于18岁),然后用left join逐个提取合并。合并前后的数据量变化,一定要做好检查。

二是数据提取用SQL完成,数据清洗与分析用R。对于提取时间很长的数据,如实验数据(甘油三酯)、药品数据等,可把初始数据提取后保存为一个表,后面需要处理用这个表就行。

三是熟悉研究的基本思路,如数据纳排(按结局变量、自变量、协变量的顺序)作为数据选取流程图的依据,是论文的一部分,还有数据格式转化、分类变量等。另外,MIMIC数据分析不涉及权重,与NHANES不同。

(2)课程资料包括[DAY1]SCI论文复现全部代码-基于R、PostgreSql/Navicat等软件、SQL常用命令与批处理脚本、讲义;[Day2]MIMIC IV常见数据提取代码-基于sql、数据清洗-基于R讲义;[Day3] 复现论文、复现代码、复现数据、学习资料、讲义[Day4]扩展学习资料和相关源码等。关注公众号“熊大学习社”,回复“mimic01”,获取全部4天MIMIC复现课程资料链接。


(3)医学公共数据数据库学习训练营已开班,论文指导精英班学员可获取推荐审稿人信息,欢迎咨询课程助理!

了解详情|医学公共数据库学习训练营

(4)数据提取和数据分析定制,具体扫码咨询课程助理。