OBSERVATION

Link to CommonDataModel

• OBSERVATION

Source Tables (mimic)

datetimeevents

• observation_concept_id is equal to 4085802 ("Referred by nurse")
• visit_detail_id is assigned

admissions

• observation_concept_id is equal to 4052017 ("Religious affiliation")
• observation_concept_id is equal to 40758030 ("Language.preferred")
• observation_concept_id is equal to 40766231 ("Marital status")
• observation_concept_id is equal to 44803968 ("Ethnicity - National Public Health Classification")
• observation_concept_id is equal to 46235654 ("Primary insurance")

chartevents

• Textual data from chartevents are stored here
• Categorical variables are note considered as free text and are then stored in the measurement table

drgcodes

Mapping used

insurance_to_concept

• it maps insurance in standard concept

ethnicity_to_concept

• it maps ethnicity in standard concept

religion_to_concept

• it maps religion in standard concept

marital_status_to_concept

• it maps marital_status in standard concept

Examples

different date types

SELECT observation_type_concept_id, concept_name, count(1)
FROM observation
JOIN concept on observation_type_concept_id = concept_id
GROUP BY observation_type_concept_id, concept_name ORDER BY count(1) desc;

observation_type_concept_id | concept_name | count -----------------------------+-------------------------------+---------- 581413 | Observation from Measurement | 69367755 38000280 | Observation recorded from EHR | 4847060

Repartition of different concepts in observation table

SELECT observation_concept_id, concept_name, count(1) 
FROM observation 
JOIN concept on observation_concept_id = concept_id 
GROUP BY observation_concept_id, concept_name ORDER BY count(1) desc;

observation_concept_id	concept_name	count
0	No matching concept	160011
4085802	Referred by nurse	10342
4296248	Cost containment	278
44803968	Ethnicity - National Public Health Classification	127
46235654	Primary insurance	127
40766231	Marital status	101
40758030	Language.preferred	73
4052017	Religious affiliation	67