Opened 4 years ago

Last modified 2 years ago

#2713 reopened enhancement

Bring Cardiology labs data into HERON

Reported by: rwaitman Owned by: schandaka
Priority: major Milestone: heron-butternut-update
Component: data-repository Keywords: Cardiology Labs MAC public-web
Cc: tmcmahon, bzschoche, mhoag Blocked By: 20, 501, 3506
Blocking: 2714 Sensitive: no

Description

Bring Cardiology labs data into HERON.

(split out ticket:501#comment:27)

Sravani, reporting for Russ

Change History (47)

comment:1 Changed 4 years ago by schandaka

These will be the order types that need to brought into HERON(under a new folder 'Cardiology Labs'.

Order_TypeOrder Type Name
1785Device Check
29ECHO
5Imaging
1783Nuclear Cardiology
1781CV Peripheral Vascular

Meeting notes from 05/15/2014 (Russ,Tamara,Sravani,Dr.Gupta,Dr.Porter,Dr.Chen)

Dr. Porter: AMGEN is potential client

Russ: Order type 'Device Check' is very cardiology centric and will be very valuable to bring it into HERON.

Dr.Porter:This has the pace maker/defbrillator data.(Component Name: EP Generator model #, EP Generator implant date).Components are linked to Medtronic data. Order type'ECHO' is good for cardio/vascular research. Dr.Gupta or Dr.Jayant Nath can be reached if any questions on ECHO data.ECHO is the fundamental decission point while triaging.
Kevin Mulhern is the clinical director of EChoCardiogram. Mary Chivington is the key person who knows the system and how the data is interfaced into Epic.

Russ: Is it possible that the same component is used by multiple tests?
Dr. Gupta: Yes, one such example is Ejection Fraction.

MRI Cardiology in Order type 'Imaging' shows test has been ordered but looks like results are not reported back into Epic.

Component ID 1180030539(MPI EF is valuable)

Order type 'Nuclear Cardiology is good stuff to bring into HERON. Mike Fieldcamp can be reached about the questions on Nuclear Cardio data.

Russ: Concept path could be Cardiology Labs\Order types\Procedure Orders (no modifiers)

Order type 'Electrophysiology' is also good stuff to bring it into HERON depending on the type of clinical research.

Dr.Gupta: Order type 'Perivascular' should be renamed to Vascular Ultrasound while bringing it into HERON
Rashmi Thapar and Zuber Shah are the fellows(under Dr.Gupta) that can help us validating the data in HERON.

Sravani's next steps: Work with Dr.Gupta and his fellows to bring in Order types - 1785,29,1781.

comment:2 Changed 4 years ago by dconnolly

  • Component changed from dev-framework to data-repository

comment:3 Changed 4 years ago by dconnolly

  • Blocking set to 2714

comment:4 Changed 3 years ago by dconnolly

Sravani, Tamara,

It looks like this didn't make it in the June 12th timeframe that we discussed earlier (ticket:2714#comment:4). I expect ETL to start this afternoon/evening. Should I keep this ticket on the critical path?

comment:5 Changed 3 years ago by schandaka

Dan,
There is no enough data to test this on <the sample data server - server name removed>. I am testing my queries against <production data server - server name removed> now. Cardio folks need this data. Hoping to complete this before the ETL kicks off.

comment:6 follow-up: Changed 3 years ago by schandaka

  • Owner changed from schandaka to mhoag
  • Status changed from new to assigned

Queries to determine the resulttypes for the cardiology labs is as below

select distinct componentname,componentid,datatype,
count(order_proc_id) over (partition by componentname,componentid,datatype) as subtotal,
count(order_proc_id) over (partition by componentname,componentid) as total,
round(count(order_proc_id) over (partition by componentname,componentid,datatype)/count(order_proc_id) over (partition by componentname,componentid),2) as percentage
--sum(order_proc_id) as total
--count(order_proc_id)/sum(order_proc_id) as perc
from
(
select cop.description as copdescription,
cor.order_proc_id,
cop.order_type_c  as copordertype,
ccc.name as componentname,
ccc.component_id as componentid,
cor.ord_value as ordvalue,
case when LENGTH(TRIM(TRANSLATE(cor.ord_value, ' <>+-.0123456789', ' '))) is null
         then 'numeric' 
         else 'text'
   end as datatype,
count(cor.ord_value) as resultcount
from CLARITY.clarity_component ccc join CLARITY.order_results cor on cor.component_id=ccc.component_id
join clarity.order_proc cop on cor.order_proc_id = cop.order_proc_id
join clarity.clarity_eap  ceap on cop.proc_id=ceap.proc_id
where cop.order_type_c in(1785,29,5,1783,1781) 
group by cop.description,ccc.component_id,ccc.name,cor.order_proc_id,cor.ord_value,cop.order_type_c
order by cop.description
)

Result status is not populated in clarity for these tests.One explanation could be that these orders(cop.order_type_c in(1785,29,5,1783,1781)) are not lab orders but are treated as procedure orders. So result_status_c=3 is not included in the 'where' in the above query.
(Source of this information: Mary Chivington)

Query to populate the concept hierarchy has been created using the following query.

select distinct ccc.component_id as ID,
ccc.name as DESCRIPTION,
ccc.name as COMMON_NAME,
ceap.proc_name as SUB_CATEGORY,
zcot.name as CATEGORY,
'' as RESULTTYPE -- is populated later by using the resultant data set from the query above
from CLARITY.clarity_component ccc join CLARITY.order_results cor on cor.component_id=ccc.component_id
join clarity.order_proc cop on cor.order_proc_id = cop.order_proc_id
join clarity.zc_order_type zcot on cop.order_type_c=zcot.order_type_c
join clarity.clarity_eap  ceap on cop.proc_id=ceap.proc_id
where cop.order_type_c in(1785,29,5,1783,1781) 
order by zcot.name,ceap.proc_name

White list components are manually identified by analyzing the resultant data from the query below.

select cop.description as copdescription,
cor.order_proc_id,
cop.order_type_c  as copordertype,
ccc.name as componentname,
ccc.component_id as componentid,
cor.ord_value as ordvalue,
case when LENGTH(TRIM(TRANSLATE(cor.ord_value, ' <>+-.0123456789', ' '))) is null
         then 'numeric' 
         else 'text'
   end as datatype,
count(cor.ord_value) as resultcount
from CLARITY.clarity_component ccc join CLARITY.order_results cor on cor.component_id=ccc.component_id
join clarity.order_proc cop on cor.order_proc_id = cop.order_proc_id
join clarity.clarity_eap  ceap on cop.proc_id=ceap.proc_id
where cop.order_type_c in(1785,29,5,1783,1781) 
group by cop.description,ccc.component_id,ccc.name,cor.order_proc_id,cor.ord_value,cop.order_type_c
order by cop.description

  • local-labs.csv file in the curated_data folder has been updated with the cardiolabs hierarchy.
  • componentids_whitelist in the curated_data folder has been updated with the cardiolabs components.
  • Code to bring in the cardiology lab results has been committed in [a13566721558].
  • Tested it on <dev server, server name removed>.

Test case is yet to be added.

Matt, sending this your way for code review.

comment:7 Changed 3 years ago by dconnolly

In af4804702bed, why change epic_etl.py only to add a blank line? (in a way that's not consistent with PEP8, by the way). That sort of thing makes me nervous... it makes me wonder if you're carefully reviewing your diffs as you check them in.

comment:8 Changed 3 years ago by schandaka

Dan, Thanks for pointing that out. I was trying to load the cardio curated data before into seperate files. But later I realized it is good to maintain all the order types in one place. So I added and removed a piece of code. That is the reason for that PEP8 violation. I will fix it.

comment:9 in reply to: ↑ 6 Changed 3 years ago by ngraham

  • Owner changed from mhoag to ngraham

Replying to schandaka:

Matt, sending this your way for code review.

Sravani,

Matt is working on Housecalls right now so I'll review this.

comment:10 Changed 3 years ago by schandaka

Nathan,
I fixed and committed the PEP8 violation issue in [73c98f56e285]. You can use this changeset while you review the code.

comment:11 Changed 3 years ago by ngraham

  • Owner changed from ngraham to schandaka

With wiki:CodeReviewNotes in mind...

  • No automated tests. At least add a test to source:heron_load/test_heron_query.py to make sure there is at least one cardio lab.
  • It would be nice to document the use case for the cardiology labs (in the transform code perhaps?). I know we've not always done this in the past, but it's nice to have some indication of what's going on in the code. "cardio" is mentioned in the transform code, but it's not obvious what in (1785,29,5,1783,1781) means.
  • epic_concepts_load.sql
    • Line 1364 - consider putting the list of strings in curated data somewhere? Or, how about a view that is referenced multiple places.
      'Device Check','Echo','Imaging','Nuclear Cardiology','Vascular Ultrasound'
      
      • That same list is duplicated 6 places in that file! Not DRY!!
    • Also, the same c_fullname string (\i2b2\CardioLabtests\) is duplicated 5 times - also not DRY. Not saying this kind of thing doesn't already exist in our code, but it would be nice to avoid when possible.
    • the kuh_lab_scheme is created again - I assume that's a copy/paste error?
    • The insert for Lab concepts is almost identical to Cardio lab concepts (I diff'd the two). It seems that it's just the path strings, visual attributes (hidden vs. active) and the levels that are different between cardio labs and "normal" labs. Don't you think the similarities could be factored out to eliminate duplicate code?
  • epic_etl.py
    • Why was this committed with just adding a single newline? I assume that's a mistake? It happens sometimes...but please review all diffs before committing code to help avoid this kind of thing. Ah, I see you removed the line in [73c98f56e285]...I started this review before you added comment:10.
  • epic_labs_transform.sql
    • Not sure of the meaning of the comment ---,1785,29,5,1783,1781? Just leftover from exploration?
    • Duplicated code again for the cardiology - I wonder if this could be factored out? It seems that the differences are cop.order_type_c and where cor.result_status_c = 3. Seems like copy/paste again.
    • Commented out code? I think you might as well delete as opposed to commenting out.
  • Perhaps document how the curated date is created (componentids_whitelist.csv and local-labs.csv. Maybe you've done that already somewhere and I missed it. I'm just thinking about peers who may try to use our code and wonder where those files came from.
  • As for performance, I ran the select statement in epic_labs_transform.sql. It took about 10 minutes or so which I guess is reasonable. It only came up with 105 rows from the latest staged Clarity data. Is that expected? Query below:
    select 
         cop.pat_enc_csn_id,
         cor.pat_id,
         cor.component_id,
         (case when cor.result_time is null then cpe.contact_date
               else cor.result_time
         end) as result_time,
         cor.order_proc_id,
         cor.line,
         cor.ord_num_value,
         cor.ord_value,
         cor.reference_unit,
         cop.order_type_c
    
    from clarity.order_results cor
      join clarity.order_proc cop on cor.order_proc_id = cop.order_proc_id
      join clarity.pat_enc cpe on cpe.pat_enc_csn_id = cop.pat_enc_csn_id
    where cop.order_type_c in (1785,29,5,1783,1781)
       /*select order_type_c from clarity.zc_order_type where
        abbr in ('Device Check','ECHO','Imaging','Nuclear Cardiology','CV Peripheral Vascular')*/
      and cor.component_id in (select distinct componentid from component_whitelist) 
        --select components from the labs whitelist in the curated data
      and cor.ord_num_value is not null;
    

comment:12 Changed 3 years ago by schandaka

  • Added a test query to make sure we have at least one 2-D DOPPLER ECHO QUERY result .

epic_concepts_load_sql:

  • Created a view for 'Device Check','Echo','Imaging','Nuclear Cardiology','Vascular Ultrasound' from local-labs. Therefore, eliminated the duplicate strings in multiple places.
  • Fixed the issue with duplicate kuh_lab_scheme.

epic_labs_transform.sql

  • Removed the comment mentioned in the code review.
  • Added a comment to show the descriptions for the order_types 1785,29,5,1783,1781.

Steps to create the curated_data have been documented in comment:6. The creation of whitelist components curated data is a manual process as documented in comment:6.

The query above in the code review that is run on latest staged clarity data is not using the updated component_whitelist. The older component_whitelist is missing almost all of the cardiology components. Hence, the count 105.

Committed these changes in [444ba8ef28cc].

comment:13 Changed 3 years ago by ngraham

Sravani,

I tried to address my DRY concerns for the cardiology concepts in [4964e592e481].

To make sure I got the same result, I ran the following query before and after my change and diffed the exported CSVs:

select 

  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
  rtrim(c_visualattributes),c_tooltip, --c_metadataxml,
  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
  c_operator, m_applied_path,
  sourcesystem_cd

from blueheronmetadata.heron_terms 
where c_fullname like '\i2b2\Labtests\%' or c_fullname like '\i2b2\CardioLabtests\%'
order by c_fullname;

heron_etl_tiny_no_DBA (Jenkins build 48).

comment:14 Changed 3 years ago by schandaka

  • Resolution set to fixed
  • Status changed from assigned to closed

Merged into default in [ffe2d1de2c73].

comment:15 Changed 3 years ago by dconnolly

  • Type changed from task to enhancement

for the purposes of the release (#2732), this is an enhancement

comment:16 Changed 3 years ago by ngraham

  • Keywords public-web added
  • Sensitive unset

After talking with Sravani, we agreed to publish this ticket.

comment:17 follow-up: Changed 3 years ago by schandaka

  • Milestone changed from heron-marmaton-update to heron-hackberry-update
  • Resolution fixed deleted
  • Status changed from closed to reopened

After reviewing the cardiology lab results on HERON with the cardiology fellow(Zubair Shah) I have noticed that there are some result interpretation texts that are currently missing in HERON. They are available on the O2 patient charts. Zubair mentions those interpretation texts(Example: Right Carotid Interpretation and Left Carotid Interpretation) are valuable for their research.

Here is a sample of Right carotid interpretation text sample from O2.

Right Carotid Interpretation
- The proximal common carotid artery demonstrates no significant stenosis.
- The distal common carotid artery demonstrates <50% stenosis with mild heterogeneous plaque.
- The proximal internal carotid artery demonstrates 0-49% stenosis with mild heterogeneous plaque.
- The middle internal carotid artery demonstrates no significant stenosis.
- The distal internal carotid artery demonstrates no significant stenosis.
- The external carotid artery demonstrates <50% stenosis with mild heterogeneous plaque.
- The vertebral artery demonstrates antegrade flow.
- The subclavian artery demonstrates no significant stenosis. The subclavian artery flow is biphasic.

While generating the whitelist components for the cardio labs, I have eliminated some result interpretation components as they could be potential PHI violations. I initially thought that adding these new components to the whitelist might resolve the issue.However,after further analysis I noted that is not the case.

Those specific order result interpretation summaries are not in clarity.order_results. I have also checked clarity.order_res_comment and clarity.order_res_comp_cmt tables.
I have reached out to Dolores Boring(who manages clarity reporting for the hospital) to see if she can help me find those fields in clarity.

I am reopening this ticket as those interpretation texts are critical for Dr.Gupta's research. Russ wants to get this fixed as soon as we can.I will try to fix this for heron-hackberry release(if possible).

comment:18 Changed 3 years ago by schandaka

I spoke to Russ about this. While I wait to hear back from Dolores,he suggested I should look at hno_info and hno_note_text tables in clarity to find these result interpretations. A sample query on a patient showed that NOTE_TEXT column in clarity.hno_info has the information that I was looking for earlier. Next step for me is to see how these notes can be linked back to the parent orders.

comment:19 in reply to: ↑ 17 Changed 3 years ago by dconnolly

  • Blocking changed from 2714 to 2714, 2792
  • Cc ssuman added

Replying to schandaka:

[...] I am reopening this ticket as those interpretation texts are critical for Dr.Gupta's research. Russ wants to get this fixed as soon as we can.I will try to fix this for heron-hackberry release(if possible).

That means ETL shouldn't start without this. Be sure to coordinate with Suman.

comment:20 Changed 3 years ago by schandaka

I think ETL can proceed without this work for this release. Tamara and I are meeting with him on Wednesday(07/16). We will know more about the interpretation summaries after that. Here is an email from Dr.Gupta regarding this.

From: Kamal Gupta 
Sent: Sunday, July 13, 2014 5:22 PM
To: Sravani Chandaka; Russ Waitman
Cc: Zubair Shah; John Chen
Subject: RE: Cardiovascular data validation with Zubair

I think we can do without the interpretation summary for now. 

How far back do we go in terms of dates? 

Thanks

comment:21 Changed 3 years ago by dconnolly

  • Blocking changed from 2714, 2792 to 2714
  • Milestone changed from heron-hackberry-update to heron-wakarusa-update

comment:22 Changed 3 years ago by schandaka

During the HERON fishing trip,Russ asked to rename the Cardiology Lab Results folder to Cardiology Lab Results(DRAFT).

comment:23 Changed 3 years ago by schandaka

In order to fix some of the issues with the Cardio lab results, Russ suggested to bring the lab hierarchy from Chronicles(as opposed to creating the hierarchy using order_proc and order_results). Russ has reached out to hospital folks several times in the past few weeks in order to get us(Matt and Bhargav) access to Chronicles. Here is the recent update.

From: Russ Waitman 
Sent: Wednesday, July 30, 2014 8:59 AM
To: Sravani Chandaka
Cc: Greg Ator; Bhargav Adagarla; Chris Harper
Subject: Re: Cardio labs hierarchy

I sent that email yesterday asking for Record Viewer access for the team/you and POC Chronicles for Bhargav and Matt.  hopefully that will get us going.  

Russ
On Jul 29, 2014, at 11:24 AM, Sravani Chandaka <schandaka@kumc.edu> wrote:


Hi Russ,
I am not sure if we ever received any response from the hospital folks about getting Matt and Bhargav access to Chronicles. I have looked at this ticket from the past. https://bmi-work.kumc.edu/work/ticket/311. I would like to put a similar request again for these order types(below). Do you think I should wait for the Chronicles access? Thoughts? I would like to get the cardio folder straightened out in this coming release if possible.
 
Order_Type	Order Type Name
1785	Device Check
29	ECHO
5	Imaging
1783	Nuclear Cardiology
1781	CV Peripheral Vascular
 
 
Thanks
Sravani 

comment:24 Changed 3 years ago by dconnolly

  • Milestone changed from heron-wakarusa-update to heron-delaware-update

comment:25 Changed 3 years ago by dconnolly

  • Milestone changed from heron-delaware-update to heron-saline-update

Batch update from file delaware-1.xls

comment:26 Changed 3 years ago by schandaka

I have not received any update about the lab hierarchy(from the hospital folks) to proceed further on this. As per Russ's comment in https://bmi-work.kumc.edu/work/ticket/2947#comment:5 sounded like this can wait.

comment:27 Changed 3 years ago by ngraham

  • Priority changed from major to minor

Batch update from file HERON_saline_meeting.csv

comment:28 Changed 3 years ago by schandaka

  • Blocked By set to 2935

comment:29 Changed 3 years ago by dconnolly

  • Milestone changed from heron-verdigris-update to heron-kanopolis-update

postponing tickets other than those identified as Verdigris priorities in today's heron-weekly meeting

comment:30 Changed 3 years ago by mhoag

  • Blocked By changed from 2935 to 20, 2935

comment:31 Changed 3 years ago by mhoag

  • Blocked By changed from 20, 2935 to 20

comment:32 Changed 3 years ago by dconnolly

  • Milestone changed from heron-kanopolis-update to dconnolly

Milestone renamed

comment:33 Changed 3 years ago by dconnolly

  • Milestone changed from dconnolly to heron-kanopolis-update

Milestone renamed

comment:34 Changed 3 years ago by dconnolly

  • Milestone changed from heron-kanopolis-update to dconnolly

Milestone renamed

comment:35 Changed 3 years ago by dconnolly

  • Milestone changed from dconnolly to heron-kanopolis-update

Milestone renamed

comment:36 Changed 3 years ago by dconnolly

  • Milestone changed from heron-kanopolis-update to heron-webster-update

closing Kanopolis, postponing remaining minor tickets without discussion

comment:37 Changed 3 years ago by ngraham

  • Milestone changed from heron-webster-update to heron-mcmillan-marsh-update

Ticket retargeted after milestone closed

comment:38 Changed 3 years ago by schandaka

  • Milestone changed from heron-mcmillan-marsh-update to heron-quivira-update

No updates after https://bmi-work.kumc.edu/work/ticket/2713#comment:26. Moving to heron-quivira.

comment:39 Changed 3 years ago by mhoag

  • Milestone changed from heron-quivira-update to heron-jamestown-update

Ticket retargeted after milestone closed

comment:40 Changed 2 years ago by ngraham

  • Milestone changed from heron-jamestown-update to heron-calhoun-update

Ticket retargeted after milestone closed

comment:41 Changed 2 years ago by ngraham

  • Milestone changed from heron-calhoun-update to heron-riverwalk-update

Ticket retargeted after milestone closed

comment:42 Changed 2 years ago by dconnolly

  • Blocked By changed from 20 to 20, 3506
  • Cc rwaitman removed
  • Reporter changed from schandaka to rwaitman

heron-weekly:
Sravani was reporting for Russ

comment:43 Changed 2 years ago by mhoag

  • Milestone changed from heron-riverwalk-update to heron-ray-hubbard-update

Ticket retargeted after milestone closed

comment:44 Changed 2 years ago by dconnolly

  • Milestone changed from heron-ray-hubbard-update to heron-michigan-update

heron-weekly: postpone

comment:45 Changed 2 years ago by bzschoche

  • Cc bzschoche mhoag added; ssuman removed
  • Keywords heron-weekly added

comment:46 Changed 2 years ago by ngraham

  • Priority changed from minor to major

heron-weekly:
SC: After getting the lab hierarchy updated, I'd like to tackle this one as it is relevant to ADAPTABLE. I have a meeting with Dr. Gupta today and will ask for specific use cases for this.

comment:47 Changed 2 years ago by bzschoche

  • Blocked By changed from 20, 3506 to 20, 501, 3506
  • Keywords heron-weekly removed
  • Milestone changed from heron-michigan-update to heron-butternut-update
Note: See TracTickets for help on using tickets.