说明:
MIMIC 是知名的临床数据库，库的结构和SQL查询的数据的方法是很好的一个建立临床数据库的模板。这个官方的SQL为了进行charlson 评分,查询了多种慢性疾病。SQL代码后有对查询到的结果进行的初步分析(python) ，使我们可以对数据库中各种慢性疾病的人数有初步的了解。
心得体会：
MIMIC存储的形式是纵向数据(窄表)，这里演示了如何将其一步转变为宽表；
可以看到一种疾病对应多个编码；
with临时表的用途；
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
--DROP TABLE IF EXISTS charlson; CREATE TABLE charlson AS
-- ------------------------------------------------------------------
-- This query extracts Charlson Comorbidity Index (CCI) based on the recorded ICD-9 and ICD-10 codes.
--
-- Reference for CCI:
-- (1) Charlson ME, Pompei P, Ales KL, MacKenzie CR. (1987) A new method of classifying prognostic
-- comorbidity in longitudinal studies: development and validation.J Chronic Dis; 40(5):373-83.
--
-- (2) Charlson M, Szatrowski TP, Peterson J, Gold J. (1994) Validation of a combined comorbidity
-- index. J Clin Epidemiol; 47(11):1245-51.
--
-- Reference for ICD-9-CM and ICD-10 Coding Algorithms for Charlson Comorbidities:
-- (3) Quan H, Sundararajan V, Halfon P, et al. Coding algorithms for defining Comorbidities in ICD-9-CM
-- and ICD-10 administrative data. Med Care. 2005 Nov; 43(11): 1130-9.
-- ------------------------------------------------------------------
WITH diag AS--建立临时表格, 诊断信息
(
SELECT
hadm_id--医院的ID
, CASE WHEN icd_version = 9 THEN icd_code ELSE NULL END AS icd9_code
, CASE WHEN icd_version = 10 THEN icd_code ELSE NULL END AS icd10_code
FROM mimic_hosp.diagnoses_icd diag
)
, com AS--另外一个临时表格，基于上个临时表；
(
SELECT
ad.hadm_id
-- Myocardial infarction
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('410','412')--substr(string,start,length)函数格式
OR
SUBSTR(icd10_code, 1, 3) IN ('I21','I22')--从第一个字符开始，匹配前3个，code的解释见d_icd_dianosis表格
OR
SUBSTR(icd10_code, 1, 4) = 'I252'
THEN 1
ELSE 0 END) AS myocardial_infarct--心肌梗死
-- Congestive heart failure
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) = '428'
OR
SUBSTR(icd9_code, 1, 5) IN ('39891','40201','40211','40291','40401','40403',
'40411','40413','40491','40493')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '4254' AND '4259'
OR
SUBSTR(icd10_code, 1, 3) IN ('I43','I50')
OR
SUBSTR(icd10_code, 1, 4) IN ('I099','I110','I130','I132','I255','I420',
'I425','I426','I427','I428','I429','P290')
THEN 1
ELSE 0 END) AS congestive_heart_failure--充血性心力衰竭
-- Peripheral vascular disease,
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('440','441')
OR
SUBSTR(icd9_code, 1, 4) IN ('0930','4373','4471','5571','5579','V434')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '4431' AND '4439'
OR
SUBSTR(icd10_code, 1, 3) IN ('I70','I71')
OR
SUBSTR(icd10_code, 1, 4) IN ('I731','I738','I739','I771','I790',
'I792','K551','K558','K559','Z958','Z959')
THEN 1
ELSE 0 END) AS peripheral_vascular_disease--周围性血管疾病
-- Cerebrovascular disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) BETWEEN '430' AND '438'
OR
SUBSTR(icd9_code, 1, 5) = '36234'
OR
SUBSTR(icd10_code, 1, 3) IN ('G45','G46')
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'I60' AND 'I69'
OR
SUBSTR(icd10_code, 1, 4) = 'H340'
THEN 1
ELSE 0 END) AS cerebrovascular_disease--脑血管疾病
-- Dementia
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) = '290'
OR
SUBSTR(icd9_code, 1, 4) IN ('2941','3312')
OR
SUBSTR(icd10_code, 1, 3) IN ('F00','F01','F02','F03','G30')
OR
SUBSTR(icd10_code, 1, 4) IN ('F051','G311')
THEN 1
ELSE 0 END) AS dementia
-- Chronic pulmonary disease,慢性肺疾病
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) BETWEEN '490' AND '505'
OR
SUBSTR(icd9_code, 1, 4) IN ('4168','4169','5064','5081','5088')
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'J40' AND 'J47'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'J60' AND 'J67'
OR
SUBSTR(icd10_code, 1, 4) IN ('I278','I279','J684','J701','J703')
THEN 1
ELSE 0 END) AS chronic_pulmonary_disease
-- Rheumatic disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) = '725'
OR
SUBSTR(icd9_code, 1, 4) IN ('4465','7100','7101','7102','7103',
'7104','7140','7141','7142','7148')
OR
SUBSTR(icd10_code, 1, 3) IN ('M05','M06','M32','M33','M34')
OR
SUBSTR(icd10_code, 1, 4) IN ('M315','M351','M353','M360')
THEN 1
ELSE 0 END) AS rheumatic_disease--风湿性疾病
-- Peptic ulcer disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('531','532','533','534')
OR
SUBSTR(icd10_code, 1, 3) IN ('K25','K26','K27','K28')
THEN 1
ELSE 0 END) AS peptic_ulcer_disease--消化性溃疡
-- Mild liver disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('570','571')
OR
SUBSTR(icd9_code, 1, 4) IN ('0706','0709','5733','5734','5738','5739','V427')
OR
SUBSTR(icd9_code, 1, 5) IN ('07022','07023','07032','07033','07044','07054')
OR
SUBSTR(icd10_code, 1, 3) IN ('B18','K73','K74')
OR
SUBSTR(icd10_code, 1, 4) IN ('K700','K701','K702','K703','K709','K713',
'K714','K715','K717','K760','K762',
'K763','K764','K768','K769','Z944')
THEN 1
ELSE 0 END) AS mild_liver_disease--轻微的肝脏疾病
-- Diabetes without chronic complication
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 4) IN ('2500','2501','2502','2503','2508','2509')
OR
SUBSTR(icd10_code, 1, 4) IN ('E100','E10l','E106','E108','E109','E110','E111',
'E116','E118','E119','E120','E121','E126','E128',
'E129','E130','E131','E136','E138','E139','E140',
'E141','E146','E148','E149')
THEN 1
ELSE 0 END) AS diabetes_without_cc--无慢性并发症的糖尿病
-- Diabetes with chronic complication
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 4) IN ('2504','2505','2506','2507')
OR
SUBSTR(icd10_code, 1, 4) IN ('E102','E103','E104','E105','E107','E112','E113',
'E114','E115','E117','E122','E123','E124','E125',
'E127','E132','E133','E134','E135','E137','E142',
'E143','E144','E145','E147')
THEN 1
ELSE 0 END) AS diabetes_with_cc--有慢性并发症的糖尿病
-- Hemiplegia or paraplegia
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('342','343')
OR
SUBSTR(icd9_code, 1, 4) IN ('3341','3440','3441','3442',
'3443','3444','3445','3446','3449')
OR
SUBSTR(icd10_code, 1, 3) IN ('G81','G82')
OR
SUBSTR(icd10_code, 1, 4) IN ('G041','G114','G801','G802','G830',
'G831','G832','G833','G834','G839')
THEN 1
ELSE 0 END) AS paraplegia--截瘫
-- Renal disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('582','585','586','V56')
OR
SUBSTR(icd9_code, 1, 4) IN ('5880','V420','V451')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '5830' AND '5837'
OR
SUBSTR(icd9_code, 1, 5) IN ('40301','40311','40391','40402','40403','40412','40413','40492','40493')
OR
SUBSTR(icd10_code, 1, 3) IN ('N18','N19')
OR
SUBSTR(icd10_code, 1, 4) IN ('I120','I131','N032','N033','N034',
'N035','N036','N037','N052','N053',
'N054','N055','N056','N057','N250',
'Z490','Z491','Z492','Z940','Z992')
THEN 1
ELSE 0 END) AS renal_disease--肾脏疾病
-- Any malignancy, including lymphoma and leukemia, except malignant neoplasm of skin
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) BETWEEN '140' AND '172'
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '1740' AND '1958'
OR
SUBSTR(icd9_code, 1, 3) BETWEEN '200' AND '208'
OR
SUBSTR(icd9_code, 1, 4) = '2386'
OR
SUBSTR(icd10_code, 1, 3) IN ('C43','C88')
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C00' AND 'C26'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C30' AND 'C34'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C37' AND 'C41'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C45' AND 'C58'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C60' AND 'C76'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C81' AND 'C85'
OR
SUBSTR(icd10_code, 1, 3) BETWEEN 'C90' AND 'C97'
THEN 1
ELSE 0 END) AS malignant_cancer--恶性肿瘤,包括淋巴瘤和白血病,不包括恶性皮肤肿瘤
-- Moderate or severe liver disease
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 4) IN ('4560','4561','4562')
OR
SUBSTR(icd9_code, 1, 4) BETWEEN '5722' AND '5728'
OR
SUBSTR(icd10_code, 1, 4) IN ('I850','I859','I864','I982','K704','K711',
'K721','K729','K765','K766','K767')
THEN 1
ELSE 0 END) AS severe_liver_disease--中重度肝疾病
-- Metastatic solid tumor
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('196','197','198','199')
OR
SUBSTR(icd10_code, 1, 3) IN ('C77','C78','C79','C80')
THEN 1
ELSE 0 END) AS metastatic_solid_tumor--转移性实体瘤
-- AIDS/HIV
, MAX(CASE WHEN
SUBSTR(icd9_code, 1, 3) IN ('042','043','044')
OR
SUBSTR(icd10_code, 1, 3) IN ('B20','B21','B22','B24')
THEN 1
ELSE 0 END) AS aids
FROM mimic_core.admissions ad
LEFT JOIN diag
ON ad.hadm_id = diag.hadm_id
GROUP BY ad.hadm_id
)
, ag AS
(
SELECT
hadm_id
, age
, CASE WHEN age <= 40 THEN 0
WHEN age <= 50 THEN 1
WHEN age <= 60 THEN 2
WHEN age <= 70 THEN 3
ELSE 4 END AS age_score
FROM mimic_derived.age
)
--以下是主查询
SELECT
ad.subject_id
, ad.hadm_id
, ag.age_score
, myocardial_infarct
, congestive_heart_failure
, peripheral_vascular_disease
, cerebrovascular_disease
, dementia
, chronic_pulmonary_disease
, rheumatic_disease
, peptic_ulcer_disease
, mild_liver_disease
, diabetes_without_cc
, diabetes_with_cc
, paraplegia
, renal_disease
, malignant_cancer
, severe_liver_disease
, metastatic_solid_tumor
, aids
-- Calculate the Charlson Comorbidity Score using the original
-- weights from Charlson, 1987.
, age_score
+ myocardial_infarct + congestive_heart_failure + peripheral_vascular_disease
+ cerebrovascular_disease + dementia + chronic_pulmonary_disease
+ rheumatic_disease + peptic_ulcer_disease
+ GREATEST(mild_liver_disease, 3*severe_liver_disease)
+ GREATEST(2*diabetes_with_cc, diabetes_without_cc)
+ GREATEST(2*malignant_cancer, 6*metastatic_solid_tumor)
+ 2*paraplegia + 2*renal_disease
+ 6*aids
AS charlson_comorbidity_index
FROM mimic_core.admissions ad
LEFT JOIN com--引用临时表
ON ad.hadm_id = com.hadm_id
LEFT JOIN ag--引用临时表
ON com.hadm_id = ag.hadm_id
;
初步分析
import pandas as pd
df=pd.read_csv('charlson_comm.csv')
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 523740 entries, 0 to 523739
Data columns (total 21 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 subject_id 523740 non-null int64
1 hadm_id 523740 non-null int64
2 age_score 523740 non-null int64
3 myocardial_infarct 523740 non-null int64
4 congestive_heart_failure 523740 non-null int64
5 peripheral_vascular_disease 523740 non-null int64
6 cerebrovascular_disease 523740 non-null int64
7 dementia 523740 non-null int64
8 chronic_pulmonary_disease 523740 non-null int64
9 rheumatic_disease 523740 non-null int64
10 peptic_ulcer_disease 523740 non-null int64
11 mild_liver_disease 523740 non-null int64
12 diabetes_without_cc 523740 non-null int64
13 diabetes_with_cc 523740 non-null int64
14 paraplegia 523740 non-null int64
15 renal_disease 523740 non-null int64
16 malignant_cancer 523740 non-null int64
17 severe_liver_disease 523740 non-null int64
18 metastatic_solid_tumor 523740 non-null int64
19 aids 523740 non-null int64
20 charlson_comorbidity_index 523740 non-null int64
dtypes: int64(21)
memory usage: 83.9 MB
columns=df.columns[2:-1]
ls_patients_num=[]
for col in df.columns[2:-1]:
value=df[col].value_counts()
ls_patients_num.append(value)
# print(ls_patients_num)
df2=pd.DataFrame(ls_patients_num)
print(df2[:])
0 1 2 3 4
age_score 155046.0 56457.0 84097.0 89482.0 138658.0
myocardial_infarct 487040.0 36700.0 NaN NaN NaN
congestive_heart_failure 455882.0 67858.0 NaN NaN NaN
peripheral_vascular_disease 492644.0 31096.0 NaN NaN NaN
cerebrovascular_disease 494102.0 29638.0 NaN NaN NaN
dementia 511610.0 12130.0 NaN NaN NaN
chronic_pulmonary_disease 439659.0 84081.0 NaN NaN NaN
rheumatic_disease 510475.0 13265.0 NaN NaN NaN
peptic_ulcer_disease 517983.0 5757.0 NaN NaN NaN
mild_liver_disease 489477.0 34263.0 NaN NaN NaN
diabetes_without_cc 440969.0 82771.0 NaN NaN NaN
diabetes_with_cc 487871.0 35869.0 NaN NaN NaN
paraplegia 516222.0 7518.0 NaN NaN NaN
renal_disease 454858.0 68882.0 NaN NaN NaN
malignant_cancer 469985.0 53755.0 NaN NaN NaN
severe_liver_disease 512935.0 10805.0 NaN NaN NaN
metastatic_solid_tumor 501534.0 22206.0 NaN NaN NaN
aids 520348.0 3392.0 NaN NaN NaN
注:其中1代表了患病的人数.
2324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
