End-to-end Report Processing and Data Extraction Example

Introduction

The purpose of this document is to demonstrate an example of end-to-end processing and data extraction using the NLP API, the Python Database Client, and an NLP API Database.

Here’s the clinical problem: we have a large number of CT scan reports, and we want to find all the reports where intracranial hemorrhage (ICH) was present. Our input CT reports are from a number of different scan types, and are stored in a database. We want to create an NLP API database for our output storage, retrieve the original CT reports, process them, and store the results in the NLP API database, and then query the database to find our cases with ICH.

In this case, our input database (where our raw/source documents are stored) will be MariaDB/MySQL and our output/NLP API database will be PostgreSQL.

The following diagram summarizes the key steps from start to finish.

Overview of the key steps

Planning for Extraction

Before we get started on the processing details and building a SQL query, we need to plan out what it is that we’re looking for. While it may sound somewhat counterintuitive to start with the last step before we get started, we need to know some information to use the appropriate processing features to make sure we get the answer we’re looking for. As well, we need to put some thought into how the physicians who created these documents may have described their findings, as we may need to search for multiple concepts to find as many positive cases as we can. Finally, because we have a mix of input report types from CT scans of different body parts, we should probably narrow down our search to just search the reports from CT scans of the head.

Let’s break it up into three questions that we need to answer:

What processing features and options should we use?
What concepts are we going to be looking for?
What metadata (e.g. report descriptions) are we going to use as search criteria?

Deciding on Processing Features and Options

This is a guide to the processing features and options available when submitting a document for processing to the NLP API. The names of the options below are what you would use in the Python Client and Python Database Client.

category - In this case, because our reports are radiology reports, we’re going to use the Radiology category.
subcategory - Because our reports are CT scan reports, we should use the CT subcategory.
features - An explanation of the features we’re going to use is included below:
- snomed-ontology - because we’re looking for general clinical findings related to ICH, a relatively common condition, SNOMED is the best ontology to use here.
- entity-polarity - because we want to find the cases where ICH is present, we need to use entity-polarity to detect if the term was negated by the author.
- entity-uncertainty - it may be helpful to know if the Radiologist thought that ICH might be present or not, so we’ll use this to detect if uncertainty in the diagnosis was present or absent.
- text - as we’re going to want relatively verbose output back from the engine to store in our database, we should generally always use this option.

As we’re not looking for follow-up recommendations, or qualifications on the size of the hemorrhage, or image links or measurements, we don’t need to ask the NLP API to produce any relations. The above options allow us to a) identify hemorrage mentions and map them to SNOMED concepts, b) identify if hemorrhage was present or absent, and c) identify if the radiologist was uncertain in making the diagnosis.

More information on the possible processing features and options is available at the API docs page

Identifying SNOMED Concepts for our Search

Our next step is to identify which concepts we should be searching for. For this, we need to have some experience with medical reports, and may need to consult a radiologist. We should also be familiar with an online SNOMED lookup tool like the SNOMED CT browser.

Unfortunately, humans aren’t necessarily consistent in how they describe clinical findings. While one radiologist might be highly specific and say, “There is a small amount of subarachnoid hemorrhage over the left frontal convexity”, another might write, “Small bleed over left frontal lobe”. Because of this, we’re going to want to search for multiple concepts related to intracranial hemorrhage. Using the SNOMED CT browser, and with some background knowledge of radiology reports, the concepts that we’re going to want to search for are:

Intracranial hemorrhage (disorder) - 1386000
Hemorrhage (morphologic abnormality) - 50960005
Bleeding (finding) - 131148009
Cerebral hemorrhage (disorder) - 274100004
Subarachnoid intracranial hemorrhage (disorder) - 21454007
Epidural intracranial hemorrhage (disorder) - 82999001
Subpial intracranial hemorrhage (disorder) - 451035002

Identifying Metadata for Searching

Because we’re processing a mix of CT scans of different body parts, and because we have to search using relatively generic concepts like “bleeding”, we’re going to want to just search the CT reports from CT scans of the head. Otherwise it’s entirely possible that the term “bleeding” would come up in a trauma CT of the abdomen, and we’d essentially have a false positive in this case for our search results.

Because the NLP API database client and database allow us to store a report description field along with the structured data output from the NLP API, in the documentmetadata table, we can apply a filter to the description column in the database when we perform our query to narrow down the reports in which we’re searching.

We could also use another search criteria to search the TECHNIQUE section of the report for mentions of concepts like 303653007 - Computed tomography of head (procedure), but due to the huge variability that radiologists use in describing the scan performed, this may only result in a smaller pool of results returned than in using the report description metadata column as a search criteria.

Processing the Reports

Prerequisites

Set up the environment variables used in this guide for access to the NLP API server. You will need to set the following environment variables for the NLP API server and your access and secret keys. If you do not have access to the appropriate values for these variables please contact us.

$ export EMTELLIPRO_SERVER=''
$ export EMTELLIPRO_ACCESS_KEY=''
$ export EMTELLIPRO_SHARED_SECRET=''

Metadata Field Mapping

In order for us to be able to constrain our database query based on the date of a report, or to a particular subject, or by report description, we want to get as much metadata as we can into the documentmetadata table in the NLP API database.

Let’s take a look at this table in PostgreSQL to talk a bit about the columns:

emtellipro_demo=> \d documentmetadata;
                                             Table "public.documentmetadata"
        Column        |            Type             | Collation | Nullable |                   Default
----------------------+-----------------------------+-----------+----------+----------------------------------------------
 id                   | integer                     |           | not null | nextval('documentmetadata_id_seq'::regclass)
 document_id          | integer                     |           |          |
 source_document_id   | character varying(255)      |           |          |
 original_category    | character varying(255)      |           |          |
 original_subcategory | character varying(255)      |           |          |
 institution          | character varying(255)      |           |          |
 subject_id           | character varying(255)      |           |          |
 description          | character varying(255)      |           |          |
 hadm_id              | character varying(255)      |           |          |
 chartdate            | timestamp without time zone |           |          |
 author_name          | character varying(255)      |           |          |
 subject_name         | character varying(255)      |           |          |
 subject_dob          | timestamp without time zone |           |          |
 subject_gender       | character varying(255)      |           |          |
 requestor            | character varying(255)      |           |          |
Indexes:
    "pk_documentmetadata" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_documentmetadata_document_id_document" FOREIGN KEY (document_id) REFERENCES document(id)

Ideally, we’d like to populate all of the columns that we can from this table, so let’s take a look first at what these columns are intended to mean:

id - this is an automatically-incremented field - just to assign a unique ID to this row, and isn’t for end-user use.
document_id - this is the unique identifier that the client has generated for this table to link it to the id field in the document table, and isn’t for end-user use.
source_document_id - this is a unique identifier that gives you the chance to link back to a source document identifier, like an accession number.
original_category - if you or your institution wants to use your own categories for document types, this column allows you to do that.
original_subcategory - again, if you or your institution wants to use your own subcategories for document types, this column allows you to do that.
institution - you may be processing records from multiple institutions -e.g. hospital_A and hospital_B - so you can use this if you want to distinguish the records from the two institutions.
subject_id - this is the patient ID, or MRN, or whatever unique identifier you want to use for the patient from whose chart this document came from.
description - intended to be a plain text description of the document, like CT ABDOMEN WITH CONTRAST (e.g. DICOM group 0008, element 1030)
hadm_id - intended to be used for a Hospital Admission ID
chartdate - intended to be used for the document original date/time
author_name - intended to be used to store the report author’s name
subject_name - intended to store the subject’s name
subject_dob - intended to store the subject’s date of birth
subject_gender - intended to store the subject’s gender
requestor - intended to store the documents’ requestor

Now let’s take a look at our source database, so we can perform the mapping between the input table columns and the documentmetadata table in the NLP API database:

MariaDB [source_reports]> describe reports;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| idreports    | int(11)      | NO   | PRI | NULL    | auto_increment |
| g0008e0080   | varchar(100) | NO   |     | NULL    |                |
| g0010e0020   | varchar(255) | YES  | MUL | NULL    |                |
| g0008e0060   | varchar(255) | NO   | MUL | NULL    |                |
| g0008e1030   | varchar(255) | NO   |     | NULL    |                |
| g0008e0050   | varchar(255) | YES  | MUL | NULL    |                |
| g0008e0020   | varchar(255) | YES  |     | NULL    |                |
| report       | text         | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

You’ll note that this table has some odd-looking field names! As these are radiology reports, these are DICOM group/element codes. PACS systems often store field attributes using this notation so as to be unambiguous. Here’s a decoded explanation of these fields:

g0008e0080 - Institution
g0010e0020 - Patient ID
g0008e0060 - Modality
g0008e1030 - Study Description
g0008e0050 - Accession number
g0008e0020 - Study date

So let’s look at the mapping that we can create between our source document table and our documentmetadata table:

idreports - unused. This is just a unique identifier field for this database for internal use.
g0008e0080 - map to documentmetadata.institution.
g0010e0020 - map to documentmetadata.subject_id.
g0008e0060 - map to documentmetadata.original_subcategory.
g0008e1030 - map to documentmetadata.description.
g0008e0050 - map to documentmetadata.source_document_id.
g0008e0020 - map to documentmetadata.chartdate.
report - the text of the report itself; we don’t need to map this but we’ll be retrieving it when we go to run the processing job.

You’ll note here that there are a few fields we didn’t use - hadm_id, author_name, subject_name, subject_dob, subject_gender, requestor, and original_category. For hadm_id, we’re not going to map anything to it. Radiology systems usually don’t contain information about unique identifiers for hospital admissions, so we likely don’t have any data on that or our other subject-related fields. Our main goal with this table is to be able to easily read produced scan descriptions, so requestor, author and subject information are not particularly relevant here. We’re going to use a constant value of Radiology for our original_category field, since we’re only looking at CT reports.

Now one problem we unfortunately frequently have to deal with in medical informatics is the translation between different date formats, and this instance is no different. If we look at the date format used by DICOM for our CT reports, it will be in the YYYYMMDD format, e.g. 20191231. If we try to store this into our chartdate field in PostgreSQL which has a data type of timestamp without time zone, we’ll likely get an error. Thankfully, pretty much every RDBMS has pretty robust date-handling features, and when we go to grab this field, we can use a MySQL function to convert it. Some examples of how we could do this would be:

1 SELECT DATE(g0008e0020) FROM reports;

Or:

1 SELECT DATE_FORMAT(g0008e0020, '%Y-%m-%d') FROM reports;

So let’s take our mappings above, and create the SQL query that we’re going to use when we run the NLP API Python Database Client. Just remember that the two pieces of information that we must give the NLP API when we process a document are an id as a unique identifier and the document text as text. In this case we’re going to use the accession number (g0008e0050) as the the unique identifier for the engine to process the document with, and for handy searching later, we’re also going to select it for insertion into the source_document_id field.

1 SELECT g0008e0050 AS id,
2     g0008e0050 AS source_document_id,
3     'Radiology' AS original_category,
4     g0008e0060 AS original_subcategory,
5     g0010e0020 AS subject_id,
6     g0008e1030 AS description,
7     g0008e0080 AS institution,
8     DATE(g0008e0020) AS chartdate,
9     report AS text
10 FROM reports;

If you want, you can add a WHERE ... clause or an ORDER BY or a LIMIT .. clause - the Python Database Client will execute your SQL command as-is against your database, so use whatever you need to get the records you want to process.

The next thing we need to create is the URL which our database client is going to use to connect to our MySQL RDBMS to get the reports from. The pieces of information we’re going to need are:

username - the username for our MySQL user that has access to the database containing the CT reports - here, we’ll use username
password - the password for our MySQL user - here, we’ll use password
hostname - the hostname or IP address of the server hosting the MySQL database - here, we’ll use 10.100.10.55
port - the TCP port of the MySQL process on the server, usually 3306 unless your DB admin has set it to be something different
database_name - the name of your database - here, we’ll use reports

The format of this URL - assuming you’re using the Python Database Client with pymysql, is:

mysql+pymysql://username:password@10.100.10.55:3306/reports

So - now we’ve got the groundwork laid for our processing run, let’s build the NLP API database, and process our reports.

Creating an NLP API Database

So before we can do a processing run, we need to make sure that we actually have a place to store all of our output from the NLP API! As mentioned above, we will use PostgreSQL to store the output, so the first thing we need to do is create a database in PostgreSQL. There are a bunch of ways we can do this; you can use a GUI like pgadmin, or you can go into the PostgreSQL command interpreter and use SQL to create the database, e.g.:

1 CREATE DATABASE ct_report_output;

Here, we’ve called our database ct_report_output, but you can call it whatever you want. Once we have our database created, our next step is to create the tables in the database to store the data itself. To do this, we’ll use the Python Database Client. In order to do this, we’ll need to first determine:

username - what username we’ll be using to connect to the database -here, we’ll use username
password - what password we’ll be using for this username to connect to the database - here, we’ll use password
hostname - the hostname or IP address of the server that is hosting PostgreSQL - here, we’ll use 10.10.100.10
port - the TCP port that PostgreSQL is listening on; for PostgreSQL this is usually 5432, but your DB admin may have set something custom up so you may need to check with them
database_name - we called our database ct_report_output above, but use whatever you used when you created the DB

So now, at our activated shell prompt, we’re going to run the Python Database Client to create the tables:

emtellipro-db-client create-db --database postgresql://username:password@10.10.100.10:5432/ct_report_output

If this is successful, it should just return Tables created. If you like, you can then log into your RDBMS and inspect the schema but otherwise we’re ready go move onto processing our reports.

Processing the Reports

So this is where we put a bunch of the above pieces together to create the run-time options for the database client. First, here’s the command that we’re going to run, and the explanations for each option will come after:

emtellipro-db-client \
    process \
    --database 'postgresql://username:password@10.10.100.10:5432/ct_report_output'  \
    --server $EMTELLIPRO_SERVER \
    --access-key $EMTELLIPRO_ACCESS_KEY \
    --secret-key $EMTELLIPRO_SHARED_SECRET \
    --sql-query "SELECT g0008e0050 AS id,g0008e0050 AS source_document_id,'Radiology' AS original_category,g0008e0060 AS original_subcategory,g0010e0020 AS subject_id,g0008e1030 AS description,g0008e0080 AS institution,DATE(g0008e0020) AS chartdate,report AS text FROM reports" 'mysql+pymysql://username:password@10.100.10.55:3306/reports' \
    --store-reports \
    --category Radiology \
    --subcategory CT \
    --features snomed-ontology,entity-polarity,entity-uncertainty,text \
    --job-id ct_reports

Here’s an explanation of all the options we use above:

process - telling the Python Database Client that we want to process the reports
--database - this is the database into which we’re going to be storing our output; we’ve used the URL that we constructed above
--server - the address of the API server that you have been assigned by Emtelligent - ensure you are using the correct address assigned to your institution
--access-key - this should be the NLP API access key assigned to you by Emtelligent
--secret-key - this should be the NLP API secret key assigned to you by Emtelligent
--sql-query - this is the SQL query and URL that we built for the MySQL RDBMS that we had our reports in, above
--store-reports - this will store our original reports into our ct_report_output database, to allow us to have all the data in one place
--category - as discussed above, we’re going to use Radiology here as these are Radiology reports
--subcategory - as discussed above, as these are CT scan reports, we’re going to use CT as our subcategory
--features - these are our processing options that will give us the features we need and help limit our output database size
--job-id - this is just a unique identifier for this run of the client, to enable storage of multiple batches in the same database, and track which run was which

Once the processing run is complete, the client will store the output in the database and we’re ready to start searching for the data that we want.

Searching the Output

Once the data has been stored in our database, it’s ready to search. In this section we show how you can write a custom SQL query, directly access the database, and dump the data out as a .csv file for offline analysis

SQL Query and Export

Before we can write a SQL query, we need to figure out exactly what fields we want to get out of the database. While it could be something as simple as just a list of report IDs that have the data we want, we’ll go for a bit more verbose output here, with a number of fields to help us sort/search through the data once we’ve exported it. Here’s a list of the fields we’ll want to export, along with the table name, in standard SQL tablename.fieldname notation:

Accession number - documentmetadata.source_document_id
Identified term - foundentityspan.text (we’ll call this found_text to avoid ambiguity with the report field itself)
Patient ID - documentmetadata.subject_id
Report section - foundentity.section_name
Sentence containing the found term - here we’ll use a function to extract the sentence from the whole report
SNOMED Concept ID - foundentityconcept.concept_id
SNOMED fully specified name - concept.description
Study description - documentmetadata.description
Uncertainty status - foundentity.uncertainty

And based on the criteria that we want to use for our search, we are going to use the following search constraints:

e.type_='found' - NLP API has two broad entity types -found and assumed; assumed entities are a special case for some emelliPro relations such as experiencer-relations, and we just want found entities for what the engine actually found in the text
dm.description ILIKE '%CT%HEAD%' - we’re restricting our search to reports that have a description with the word HEAD in them, to limit our search just to CT HEAD reports
fe.section_name IN ('FINDINGS','IMPRESSION') - here, we’re restricting our search to mentions of our search concept just to the FINDINGS or IMPRESSION section of the report
fec.concept_ontology = 'snomed' - we just want concepts from the SNOMED ontology
fec.concept_id IN ('1386000','50960005','131148009','274100004','21454007','82999001','451035002')
- these are the concepts that we want to search for
fe.polarity = 'asserted' - we are looking for reports where this concept is ‘ruled in’, or was asserted by the dictating radiologist

By virtue of necessity, and to follow good database design, the NLP API database schema is highly normalized. This means that a number of joins are required to get the data out of database in the format that we want.

So - here is the query that we’re going to want to use:

1 SELECT DISTINCT
2     dm.source_document_id,
3     dm.subject_id,
4     dm.original_category,
5     dm.original_subcategory,
6     dm.description,
7     dm.chartdate,
8     fec.concept_id,
9     fec.concept_ontology,
10     c.description,
11     fes.text AS found_text,
12     fe.section_name,
13     SUBSTRING(d.text, sls.start, (sls.end-sls.start+1)) AS sentence,
14     fe.uncertainty,
15     fet.type_name
16 FROM foundentityspan fes
17 JOIN foundentity fe ON fe.id=fes.found_entity_id
18 JOIN foundentitylocation fel ON fe.id=fel.found_entity_id
19 JOIN location l ON fel.location_id=l.id AND (l.type_='sentence')
20 JOIN sentencelocationspan sls ON sls.sentence_location_id=l.location_id
21 JOIN foundentityconcept fec ON fes.found_entity_id=fec.found_entity_id
22 JOIN concept c ON fec.concept_id=c.concept_id
23 JOIN foundentitytype fet ON fet.found_entity_id=fe.id AND (fet.ontology LIKE 'snomed')
24 JOIN entity e ON fe.id=e.entity_id
25 JOIN document d ON d.id=e.document_id
26 JOIN documentmetadata dm ON d.id = dm.document_id
27 WHERE
28     e.type_='found'
29     AND dm.description ILIKE '%CT%HEAD%'
30     AND fe.section_name IN ('FINDINGS','IMPRESSION')
31     AND fec.concept_ontology = 'snomed'
32     AND fec.concept_id IN ('1386000','50960005','131148009','274100004','21454007','82999001','451035002')
33     AND fe.polarity = 'asserted';

If we run this query, we should see a table that looks something like:

 source_document_id | subject_id | original_category | original_subcategory |        description        |      chartdate      | concept_id | concept_ontology |                   description                   |       found_text        | section_name |                                                      sentence                                                       | uncertainty |        type_name
--------------------+------------+-------------------+----------------------+---------------------------+---------------------+------------+------------------+-------------------------------------------------+-------------------------+--------------+---------------------------------------------------------------------------------------------------------------------+-------------+-------------------------
 1111111            | 1111111    | Radiology         | CT                   | CT HEAD W/O CONTRAST      | 2004-08-12 00:00:00 | 50960005   | snomed           | Hemorrhage (morphologic abnormality)            | bleeding                | FINDINGS     |  Slightly more hyperintense material at the more dependent aspect                                                  +| certain     | morphologic abnormality
                    |            |                   |                      |                           |                     |            |                  |                                                 |                         |              |  of the right subdural collection (2:9) are compatible with more recent                                            +|             |
                    |            |                   |                      |                           |                     |            |                  |                                                 |                         |              |  bleeding.

And if we wanted to export this to a .csv file directly from our query, we could encapsulate our query in a COPY (<query>) TO ... so that it would look something like this:

1 COPY(SELECT DISTINCT
2     dm.source_document_id,
3     dm.subject_id,
4 ....
5     AND fec.concept_id IN ('1386000','50960005','131148009','274100004','21454007','82999001','451035002')
6     AND fe.polarity = 'asserted')
7 TO '/tmp/hemorrhage_ct_head_export.csv'
8 WITH (FORMAT CSV, HEADER TRUE, ESCAPE '\', FORCE_QUOTE *);

Above I’ve used this on linux, so I’m exporting it to a file in /tmp - you may need to use a different format/directory if you’re running this on another OS.

And that’s pretty much it - we should get the results in a .csv file and we can then import it somewhere else to do something with it.

Summary

This document has described the considerations required in performing a processing run to identify reports containing a list of clinical concepts, using the NLP API Python Database Client, and either a direct SQL query, or the NLP API Search App. It has shown how to retrieve reports from one RDBMS (MariaDB in this case), process these reports, and export the output from the NLP API into another RDBMS (PostgreSQL). Hopefully it will get you on your way to processing data with the NLP API and finding the information that will help you help your patients.