EMR Data Exploration

[gatechke]

• Obtain access to Neptune
• Contact Patrick to help with getting login
• Install sqldeveloper to connect to Neptune
• Understanding Neptune
• Ask Patrick to show you data summarization tool that he has built
• Focus on diagnosis and medication tables in Neptune and understand the columns
• Use SQL to conduct exploratory data analysis to understand type, amount and distribution of data on diagnosis and medication tables
• Generate plot, summary and reports for EDA

[shyamvis]

Give Patrick (https://github.com/pohalloran) access here.

[kayhan-batmanghelich]

@gatechke what is the latest status on this?

[gatechke]

@kayhan-batmanghelich

I had a meeting with Patrick on last Thursday. He was still working with another person to get me the access to Neptune. I also asked Patrick to send existing data dictionary to me.

Currently, Diagnosis table, procedure table and a few reference tables are available on Neptune. Medication table hasn't been loaded yet.

Shyam and I will have one-o-one meeting on Monday morning.

[gatechke]

@shyamvis
Table below: counts of distinct procedure code by procedure coding system. ~1.2 million records from 806 procedure codes have missing value in the filed of procedure coding system.

[gatechke]

@shyamvis
Top 10 procedure codes that don't have procedure coding system populated

[gatechke]

@shyamvis
Good news is that no missing value has been found in procedure code filed.

select count(*) from SAM_ETL.PROCEDURES
where PROC_CODE is null;

[shyamvis]

Ke, Create a document that gives a brief description of the columns in the Diagnosis table fro future reference. You can include the counts table in it. Do the same for Procedures table. Upload them to dropbox.

[shyamvis]

Ke, Talk to Patrick and Brian McLay (who Patrick can introduce) to get a description of each of the 11 procedure coding systems.

[gatechke]

@shyamvis
I contacted Brian McLay to find out the description of the 11 procedure coding systems. Based on his reply

• CPT(R) is the same as CPT4 which is the Level I of HCPCS
• HCPCS, ICD10PC, ICD9CM, CPT(R), CPT4 are standard code types
• The other small procedure code types are custom values in the source systems. So we don’t know their exact mapping to standard code types.