Documment reshaping

[theonesp]

based on https://github.com/theonesp/or_data/tree/master/reshaping

[xborrat]

broken link

[theonesp]

Reshaping the data

mimic_or_phi structure

How every table and column from aims is being mapped to mimic structure

• bolusdrugs -> inputevents_or

• infusiondrugs -> d_items _Each unique label and dose has its own itemid

  • Auto Increment id -> d_items.itemid _Each unique combination of 'Label' and 'dose' has its own itemid
  • infusiondrugs."Case_Name" NOT USED
  • infusiondrugs."Time" NOT USED
  • infusiondrugs."ItemID" NOT USED
  • infusiondrugs."Label" = 'Phenylephrine' -> d_items.label
  • infusiondrugs."Label" = 'Norepinephrine' -> d_items.label
  • infusiondrugs."Label" = 'Epinephrine' -> d_items.label
  • infusiondrugs."Label" = 'Vasopressin' -> d_items.label
  • infusiondrugs."Label" = 'Norepinephrine (800/50)' -> d_items.label
  • infusiondrugs."Label" = 'Milrinone' -> d_items.label
  • infusiondrugs."Label" = 'Methylene blue' -> d_items.label
  • infusiondrugs."Mix_Amount" NOT USED
  • infusiondrugs."Mix_Units" NOT USED
  • infusiondrugs."Mix_Volume" NOT USED
  • infusiondrugs."Dose_Amount" NOT USED
  • infusiondrugs."Dose_Units" -> d_items.unitname
  • infusiondrugs."Container" NOT USED
  • infusiondrugs."Route" NOT USED

• infusiondrugs -> inputevents_or

  • Auto Increment id -> inputevents_or.itemid
  • text."Label" = 'Medical Record #' THEN text."Text" -> inputevents_or.mrn
  • deidentified(text."Label" = 'Medical Record #' THEN text."Text") -> inputevents_or.subject_id
  • infusiondrugs."Case_Name" -> inputevents_or.orstay_id
  • infusiondrugs."Time" -> inputevents_or.storetime
  • infusiondrugs."ItemID" NOT USED
  • infusiondrugs."Label" NOT USED
  • infusiondrugs."Mix_Amount" -> inputevents_or.amount
  • infusiondrugs."Mix_Units" -> inputevents_or.amountuom
  • infusiondrugs."Mix_Volume" -> inputevents_or.totalamount
  • infusiondrugs."Dose_Amount" -> inputevents_or.rate
  • infusiondrugs."Dose_Units" -> inputevents_or.rateuom
  • infusiondrugs."Container" ????
  • infusiondrugs."Route" -> inputevents_or.originalroute

• combo -> caregivers

  • On mimic_or_phi.caregivers

  • caregivers.cgid Auto Increment id Associated with each provider.

  • caregivers.description EMPTY

  • From combo to caregivers

  • text."Label" = 'Medical Record #' THEN text."Text" -> caregivers.mrn

  • deidentified(text."Label" = 'Medical Record #' THEN text."Text") -> caregivers.subject_id

  • combo."Case_Name" -> caregivers.orstay_id

  • combo."Time" -> when the provider info was stored, is this relevant?

  • combo."ItemID" NOT USED

  • combo."Label" = 'Attending Anesthesiologist.' -> caregivers.description

  • combo."Label" = 'Surgeon' -> caregivers.description

  • combo."Label" = 'Resident/CRNA' -> caregivers.description

  • combo."ItemText" = After Last Name and coma -> caregivers.label

  • combo."C_Type" NOT USED

  • combo."C_Type_Label" NOT USED

  • combo."C_Item" PROVIDER INFO

  • combo."C_Association" NOT USED

  • combo."ComboText" NOT USED

  • combo."ESIG" PROVIDER INFO

  • combo."ESIGNAMETEXT" PROVIDER INFO

  • combo."ESIGDATEANDTIME" NOT USED

• events -> chartevents

  • events."Case_Name" -> chartevents.orstay_id
  • text."Label" = 'Medical Record #' THEN text."Text" -> chartevents.mrn
  • deidentified(text."Label" = 'Medical Record #' THEN text."Text") -> chartevents.subject_id
  • events."Label" = 'Anesthesia Induction ( I )' AND events."TIME" -> chartevents.d_item AND chartevents.storetime
  • events."Label" = 'EndoTrach tube/ LMA placed (E)' AND events."TIME" -> chartevents.d_item AND chartevents.storetime
  • events."Label" = 'Bypass Begin' AND events."TIME" -> chartevents.d_item AND chartevents.storetime
  • events."Label" = 'Bypass End' AND events."TIME" -> chartevents.d_item AND chartevents.storetime
  • events."ItemID" NOT USED

• events -> d_items

  • Auto Increment id -> d_items.itemid
  • events."Label" = 'Anesthesia Induction ( I )' -> d_items.label
  • events."Label" = 'EndoTrach tube/ LMA placed (E)' -> d_items.label
  • events."Label" = 'Bypass Begin' -> d_items.label
  • events."Label" = 'Bypass End' -> d_items.label
  • events."ItemID" NOT USED

• vitalsigns -> chartevents

  • vitalsigns."Index" -> chartevents.row_id
  • vitalsigns."Case_Name" -> chartevents.orstay_id
  • text."Label" = 'Medical Record #' THEN text."Text" -> chartevents.mrn
  • deidentified(text."Label" = 'Medical Record #' THEN text."Text") -> chartevents.subject_id
  • vitalsigns."Time" -> chartevents.charttime
  • vitalsigns."ItemID" NOT USED
  • vitalsigns."Label" NOT USED
  • vitalsigns."Value" -> chartevents.value AND chartevents.valuenum
  • vitalsigns."Units" -> chartevents.valueuom
  • vitalsigns."Source_Name" NOT USED
  • vitalsigns."Priority" NOT USED
  • vitalsigns."Device_Name" NOT USED
  • vitalsigns."Port#" NOT USED
  • vitalsigns."Display_Value" NOT USED
  • vitalsigns."Valid" NOT USED
  • vitalsigns."User_Invalid" NOT USED
  • vitalsigns."Limits_Invalid" NOT USED
  • vitalsigns."Value_Questionable" NOT USED

• vitalsigns -> d_items

  • Auto Increment id -> d_items.itemid _Each unique combination of "Label", "Source_Name", "Units" and "Device_Name" has its own itemid
  • vitalsigns."Units" -> d_items.unitname
  • vitalsigns."Device_Name" -> d_items.dbsource
  • vitalsigns."Source_Name" -> d_items.label
  • vitalsigns."Label" -> d_items.category
  • vitalsigns."ItemID" NOT USED
  • vitalsigns."Priority" NOT USED
  • vitalsigns."Port#" NOT USED
  • vitalsigns."Display_Value" NOT USED
  • vitalsigns."Valid" boolean NOT USED
  • vitalsigns."User_Invalid" NOT USED
  • vitalsigns."Limits_Invalid" NOT USED
  • vitalsigns."Value_Questionable" NOT USED

• text -> orstays

  • text."Case_Name" -> orstays.orstay_id
  • text."Time" -> orstays.intime
  • text."ItemID" NOT USED
  • text."Label" = 'Medical Record #' THEN text."Text" -> orstays.mrn
  • deidentified(text."Label" = 'Medical Record #' THEN text."Text") -> orstays.subject_id
  • text."Label" = 'Performed Procedure' THEN text."Text" -> NOT USED???