<jupyter_start><jupyter_text>Documentation of Study Plans Image viewing parameters/environment1. Custom viewer from gaze point2. No windowing3. No dicoms (images will be in .png)4. Full screen images (max 24inch screen)5. No portrait6. Routine radiology read with free dictation (no typing)7. Key anatomies and devices need to be reported about Inclusion criteria considerations1. Age range: ? depends on final target population - should do some exploratory analysis of metadata first2. Gender: raw ED population distribution or half-half? Balanced dataset vs original population distribution? Given limited number of images in the pilot dataset, balanced dataset is more likely to capture a wider feature variation within different strata of target population.3. Admission severity: same as for age - may be an useful indicator of CHF severity if we want to grade CHF4. Technically not too hard...and...not too easy cases.5. PA only - AP may introduce more heterogeneity amongst annotators due to poorer quality of images. PA only is a good idea for a pilot after consulting radiologists. Exclusion criteria considerations1. Patient admitted to ICU (too sick possibly too difficult, bad quality imaging (portables))2. No laterals for this pilot project since can only view 1 image at a time Diagnoses selected for eye gaze dataset after above considerations1. CHF (congestive heart failure) - grading severity, both Arjun and Ali think this will be really interesting to focus on2. Pneumonia - over annotated by existing efforts already3. Normals - must have Other interesting diseases that were initially considered but got dropped1. Pneumothorax - requires windowing capability (not allowed by eye gaze software) to report accurately2. Nodules - not enough cases in ED population3. Tubes and lines placement (endotracheal tube, central vascular lines, enteric tubes) - very difficult problem technically Notes from MIMIC contact1. All docs are online now: https://mimic-iv.mit.edu/ - getting comprehensive, but please raise any issues you find here http://github.com/mit-lcp/mimic-iv-website2. Recommend using subject_id - everybody will have the same subject_id across datasets. If you want to match a stay_id, then use subject_id and the intime/outtime in your join from the stays table.  Setups<jupyter_code># Required files
import os
import sys
import pandas as pd
import pandas_gbq
import re

# Connect colab with google bigquery stuff
from google.colab import auth
from google.cloud import bigquery
from google.colab import files
from google.colab import drive<jupyter_output><empty_output><jupyter_text>Required MIMIC derived tablesYou will need to get access to the following Eye Gaze project related derived tables to run this notebook.1. eye_gaze.report_normal_cxr2. eye_gaze.icd_criteria  Authentications<jupyter_code>auth.authenticate_user()
# Specify your project_id on Google Cloud
project_id='mimic-projects'
os.environ["GOOGLE_CLOUD_PROJECT"]=project_id<jupyter_output><empty_output><jupyter_text>Selecting patients with ED meta data<jupyter_code># Summarises mimic_ed patient and stay info
sql = """
SELECT 
count(distinct subject_id) as patients
,count(distinct stay_id) as ed_stays
FROM `physionet-data.mimic_ed.diagnosis`
"""
df = pandas_gbq.read_gbq(sql, project_id=project_id, dialect = 'standard')
df
# Summarises unique ED patients in MIMIC CXR dataset
sql = """
SELECT  
count(distinct subject_id) as patients
, count(distinct study_id) as exams
, count(distinct dicom_id) as images
FROM `physionet-data.mimic_cxr.record_list`
"""
df = pandas_gbq.read_gbq(sql, project_id=project_id, dialect = 'standard')
df
# Linking MIMIC CXR with MIMIC ED data, along with ICD-9 diagnoses
# This query no longer runs as one of the MIMIC source tables has been updated.
sql = """
WITH

-- Get the useful dicom metadata for image level data selection
dicom_meta AS (
SELECT dicom AS dicom_id, rec.path
    , CAST(StudyID AS int64) AS study_id
    , CAST(PatientID AS int64) AS patient_id
    , StudyDate
    , StudyTime
    --, CONCAT(StudyDate,SPLIT(StudyTime, '.')[OFFSET(0)]) AS StudyDateTime
    , PARSE_TIMESTAMP('%Y%m%d%H%M%S', CONCAT(StudyDate,SPLIT(StudyTime, '.')[OFFSET(0)])) AS StudyDateTime
    , EXTRACT(DATE FROM PARSE_TIMESTAMP('%Y%m%d%H%M%S', CONCAT(StudyDate,SPLIT(StudyTime, '.')[OFFSET(0)]))) AS cxrdate
    , ViewPosition
    , RequestedProcedureDescription
    , RequestedProcedurePriority
    , ExposedArea, PatientOrientation
    , Modality, BodyPartExamined, WindowCenter, WindowWidth, RescaleIntercept, Rescaleslope, RescaleType, BurnedInAnnotation
FROM `physionet-data.mimic_cxr.dicom_metadata_string` AS di
INNER JOIN `physionet-data.mimic_cxr.record_list` AS rec
    ON di.dicom = rec.dicom_id
WHERE BodyPartExamined LIKE '%CHEST%'
    AND BurnedInAnnotation LIKE 'NO'
)

-- Get ED admit info
, ed_admit AS (
SELECT
    * --, could add the dx codes here in future
    , DENSE_RANK() OVER (PARTITION BY subject_id ORDER BY intime) AS edstay_seq
    , EXTRACT(DATE FROM intime) AS indate
    , EXTRACT(DATE FROM outtime) AS outdate
FROM `physionet-data.mimic_ed.main`
--FROM `physionet-data.mimic_core.transfers`
)

-- Match CXR study_id with ED stay_id
, cxr2ed_admit AS (
SELECT cxr.*
    , ed.* --, could add the dx codes here in future
FROM dicom_meta AS cxr
INNER JOIN ed_admit AS ed
    ON ed.subject_id = cxr.patient_id
    --AND cxr.StudyDateTime BETWEEN ed.intime AND ed.outtime
    AND TIMESTAMP(cxr.cxrdate) BETWEEN TIMESTAMP(ed.indate) AND TIMESTAMP(ed.outdate)
)

-- Construct final table
, final AS (
SELECT cxr.*
    , adm.gender, adm.anchor_age, adm.anchor_year, adm.anchor_year_shifted, adm.dod
FROM cxr2ed_admit AS cxr
-- include some admission level information
INNER JOIN `physionet-data.mimic_core.patients` AS adm
    ON cxr.patient_id = adm.subject_id
)

SELECT * FROM final
--WHERE ViewPosition LIKE "AP" OR ViewPosition LIKE "PA"

--SELECT StudyDateTime FROM dicom_meta
--SELECT PARSE_TIMESTAMP('%Y%m%d%H%M%S', StudyDateTime) FROM dicom_meta
--SELECT CAST(StudyDate AS TIME) FROM dicom_meta
--SELECT intime FROM `physionet-data.mimic_ed.main`
--WHERE dicom_id IN (SELECT dicom_id FROM `physionet-data.mimic_cxr.record_list`)

--SELECT * FROM dicom_meta
--SELECT * FROM cxr2ed_admit

"""


# This query is updated to adapt to changes with the new MIMIC ed tables in BigQuery
sql = """
WITH

dx_keys AS(
SELECT 
  subject_id, stay_id, icd_title, icd_code
  , CONCAT('dx',seq_num) as key1
  , CONCAT('dx',seq_num,'_icd') as key2
FROM `physionet-data.mimic_ed.diagnosis`
)

, diagnosis AS(
SELECT 
  subject_id, stay_id, 
  MAX(IF(key1 = "dx1", icd_title, NULL)) AS dx1,
  MAX(IF(key2 = "dx1_icd", icd_code, NULL)) AS dx1_icd,
  MAX(IF(key1 = "dx2", icd_title, NULL)) AS dx2,
  MAX(IF(key2 = "dx2_icd", icd_code, NULL)) AS dx2_icd,
  MAX(IF(key1 = "dx3", icd_title, NULL)) AS dx3,
  MAX(IF(key2 = "dx3_icd", icd_code, NULL)) AS dx3_icd,
  MAX(IF(key1 = "dx4", icd_title, NULL)) AS dx4,
  MAX(IF(key2 = "dx4_icd", icd_code, NULL)) AS dx4_icd,
  MAX(IF(key1 = "dx5", icd_title, NULL)) AS dx5,
  MAX(IF(key2 = "dx5_icd", icd_code, NULL)) AS dx5_icd,
  MAX(IF(key1 = "dx6", icd_title, NULL)) AS dx6,
  MAX(IF(key2 = "dx6_icd", icd_code, NULL)) AS dx6_icd,
  MAX(IF(key1 = "dx7", icd_title, NULL)) AS dx7,
  MAX(IF(key2 = "dx7_icd", icd_code, NULL)) AS dx7_icd,
  MAX(IF(key1 = "dx8", icd_title, NULL)) AS dx8,
  MAX(IF(key2 = "dx8_icd", icd_code, NULL)) AS dx8_icd,
  MAX(IF(key1 = "dx9", icd_title, NULL)) AS dx9,
  MAX(IF(key2 = "dx9_icd", icd_code, NULL)) AS dx9_icd,
FROM dx_keys 
GROUP BY subject_id, stay_id 
ORDER BY subject_id, stay_id
)
-- SELECT
-- count(distinct subject_id) --200529
-- , count(distinct stay_id) -- 408856
-- FROM diagnosis
--SELECT * FROM diagnosis

-- Get ED admit info
, ed_admit AS (
SELECT
    ed.* --, could add the dx codes here in future
    , DENSE_RANK() OVER (PARTITION BY ed.subject_id ORDER BY ed.intime) AS edstay_seq
    , EXTRACT(DATE FROM ed.intime) AS indate
    , EXTRACT(DATE FROM ed.outtime) AS outdate
    , dx.dx1, dx.dx1_icd
    , dx.dx2, dx.dx2_icd
    , dx.dx3, dx.dx3_icd
    , dx.dx4, dx.dx4_icd
    , dx.dx5, dx.dx5_icd
    , dx.dx6, dx.dx6_icd
    , dx.dx7, dx.dx7_icd
    , dx.dx8, dx.dx8_icd
    , dx.dx9, dx.dx9_icd
FROM `physionet-data.mimic_ed.edstays` as ed
LEFT JOIN diagnosis as dx
ON ed.stay_id = dx.stay_id
--FROM `physionet-data.mimic_ed.main`
--FROM `physionet-data.mimic_core.transfers`
)
--SELECT * FROM ed_admit

-- Get the useful dicom metadata for image level data selection
, dicom_meta AS (
SELECT dicom AS dicom_id, rec.path
    , CAST(StudyID AS int64) AS study_id
    , CAST(PatientID AS int64) AS patient_id
    , StudyDate
    , StudyTime
    --, CONCAT(StudyDate,SPLIT(StudyTime, '.')[OFFSET(0)]) AS StudyDateTime
    , PARSE_TIMESTAMP('%Y%m%d%H%M%S', CONCAT(StudyDate,SPLIT(StudyTime, '.')[OFFSET(0)])) AS StudyDateTime
    , EXTRACT(DATE FROM PARSE_TIMESTAMP('%Y%m%d%H%M%S', CONCAT(StudyDate,SPLIT(StudyTime, '.')[OFFSET(0)]))) AS cxrdate
    , ViewPosition
    , RequestedProcedureDescription
    , RequestedProcedurePriority
    , ExposedArea, PatientOrientation
    , Modality, BodyPartExamined, WindowCenter, WindowWidth, RescaleIntercept, Rescaleslope, RescaleType, BurnedInAnnotation
FROM `physionet-data.mimic_cxr.dicom_metadata_string` AS di
INNER JOIN `physionet-data.mimic_cxr.record_list` AS rec
    ON di.dicom = rec.dicom_id
WHERE BodyPartExamined LIKE '%CHEST%'
    AND BurnedInAnnotation LIKE 'NO'
)

-- Match CXR study_id with ED stay_id
, cxr2ed_admit AS (
SELECT cxr.*
    , ed.* --, could add the dx codes here in future
FROM dicom_meta AS cxr
INNER JOIN ed_admit AS ed
    ON ed.subject_id = cxr.patient_id
    --AND cxr.StudyDateTime BETWEEN ed.intime AND ed.outtime
    AND TIMESTAMP(cxr.cxrdate) BETWEEN TIMESTAMP(ed.indate) AND TIMESTAMP(ed.outdate)
)

-- Construct final table
, final AS (
SELECT cxr.*
    , adm.gender, adm.anchor_age, adm.anchor_year, adm.anchor_year_group, adm.dod
FROM cxr2ed_admit AS cxr
-- include some admission level information
INNER JOIN `physionet-data.mimic_core.patients` AS adm
    ON cxr.patient_id = adm.subject_id
)

SELECT * FROM final
"""

data = pandas_gbq.read_gbq(sql, project_id=project_id, dialect = 'standard')
print(data.shape)
data.head()<jupyter_output><empty_output><jupyter_text>Derive normal class from CXR reports and ICD diagnosis code<jupyter_code># Get a list of "normal" CXR according to prior NLP analysis of the associated CXR reports

sqlnorm = """
SELECT * FROM `"""+project_id+""".eye_gaze.report_normal_cxr`
"""

normal = pandas_gbq.read_gbq(sqlnorm, project_id=project_id, dialect = 'standard')
print(normal.shape)
normal.head()
overlap = set(data.study_id).intersection(set(normal.rad_id))
print(len(overlap))
normIDs = list(set(normal.rad_id))
print(len(normIDs))

print(data.shape)
data['normal_reports'] = [1 if x in normIDs else 0 for x in data.study_id]
print(data.shape)
data.head(5)
# Exclude cases where the ICD code associated with the ED admission indicate a disease diagnosis that would most likely make the patient's CXR abnormal
# The list of exclusion ICD were manually picked by a clinician (Joy)
sqlexclude = """
SELECT * FROM `"""+project_id+""".eye_gaze.icd_criteria`
"""

exclude = pandas_gbq.read_gbq(sqlexclude, project_id=project_id, dialect = 'standard')

print(exclude.shape)

exclude.head()
excludeICD = list(set(exclude[exclude['exclude_normal_cxr']==1].icd))
print(len(excludeICD))
print(excludeICD[:10])

exclude[exclude['exclude_normal_cxr']==1].head()
# Add a column to indicate which row/stay_id should be excluded from the final "Normal" class
print(data.shape)
data['exclude_normal_cxr'] = 0*data.shape[1]
for n in range(1,10):
    data['exclude_normal_cxr'] = [1 if str(x) in excludeICD else y for (x,y) in zip(data['dx'+str(n)+'_icd'],data['exclude_normal_cxr'])]
    print(n)
print(data.shape)
# Add final normal class column
print(data.shape)
data['Normal'] = [1 if (x==1)&(y==0) else 0 for (x,y) in zip(data['normal_reports'],data['exclude_normal_cxr'])]
print(data.shape)
normals = data[data['Normal'] == 1].reset_index(drop=True).copy()
print(normals.shape)
normals.head()
print(len(data[data['normal_reports'] == 1]))
print(len(data[data['exclude_normal_cxr'] == 1]))
print(len(data[data['Normal'] == 1]))<jupyter_output>52893
23128
49262<jupyter_text>Add columns for other target disease classes<jupyter_code># Print unique top 3 diagnoses
for dx in list(set(data['dx1']).union(set(data['dx2'])).union(set(data['dx3']))):
    print(dx)
# Spot with word boundary detection
def reSpotter(text, lexicons):
    for term in lexicons:
        mre = r'\b' + re.escape(term) + r'\b'
        if (re.search(mre, text, flags=re.IGNORECASE) != None):
            #spot = text.find(term)
            return term
    return False

diagnosis = { #match phrases all in lower cases
             
         'CHF':['heart failure','chf','congestive failure','cardiac failure']
         , 'pneumonia':['pneumonia','chest infection','lung infection']
         , 'copd' : ['copd','chronic obstructive']
         , 'sob' : ['shortness of breath','dyspnea','sob','short of breath']
       }

for i in data.index:
    dxlist = []
    for j in range(1,9):
        col = 'dx' + str(j)
        dx = data.loc[i,col]
        if (dx != None)&(str(dx) != ''):
            dxlist.append(dx.lower()) #lower case before matching
    for key, phrases in diagnosis.items():
        present = 0
        for dx in dxlist:
            if reSpotter(' '+dx+' ', phrases) != False:
                # Only include none negated cases
                if reSpotter(' '+dx+' ', ['without','no','negative']) == False:
                    present = 1
        if present == 1:
            data.loc[i,key] = 1
        else:
            data.loc[i,key] = 0
    if i % 1000 ==0:
        print(i)

print('Done')

print(data.shape)
data['CHF'] = [int(x) for x in data['CHF']]
data['pneumonia'] = [int(x) for x in data['pneumonia']]
data['copd'] = [int(x) for x in data['copd']]
data['sob'] = [int(x) for x in data['sob']]
data.head(5)<jupyter_output>(147808, 59)<jupyter_text>Exporting "data"<jupyter_code># TODO: Set table_id to the full destination table ID (including the
#       dataset ID).
table_id = 'eye_gaze.cxr2ed_admit'

# #Uncomment to export
# pandas_gbq.to_gbq(data, table_id, project_id=project_id)
# Summarizes ED patients who had a CXR exam
sql = """
SELECT 
count(distinct subject_id) as patients
,count(distinct stay_id) as ed_stays
,count(distinct study_id) as cxr_studies
FROM `"""+project_id+""".eye_gaze.cxr2ed_admit` as ed
--WHERE pneumonia = 1
--WHERE CHF = 1
--WHERE Normal = 1
"""
df = pandas_gbq.read_gbq(sql, project_id=project_id, dialect = 'standard')
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11346506416785496, 'fraction_numerical': 0.01351259591226998, 'mean_word_length': 4.456835865135235, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '87495', 'license': 'Apache-2.0', 'n_tokens_mistral': 5370, 'n_tokens_neox': 5124, 'n_words': 1507, 'path': 'DataProcessing/DataPreparation/Linking_CXRs_to_ED_admissions.ipynb', 'repo_name': 'amamamahmood/eye-gaze-dataset', 'size': '14727'}