Synthetic FHIR Healthcare Records

Application Areas

Healthcare

Datatypes

Numeric

Text

This dataset, “Synthea synthetic patient generator data in OMOP Common Data Model”, has a full page on AWS’s Open Data Registry here, since data generated by this synthetic generator is subsequently used in AWS’s Population Health on AWS using Amazon HealthLake tutorial. Running that tutorial in its entirety would cost over $500, however, so, for your final project you can extract just the generated data from the tutorial. It is this extracted version of the data that we use in the Midterm Vignette below.

Midterm Vignette

Given the prominence of healthcare providers and health insurance organizations among the firms hiring data scientists (see the top of the main datasets page), the goal for this dataset is to provide an “inside look” at the types of events and information formats that might stream into a healthcare organization’s OLTP database.

For Midterm purposes, you can imagine being hired as a data scientist at a hospital (congratulations!) and being asked to use your DSAN 6000 skills to meet the following needs:

The hospital is getting lots of complaints about Medication Requests not being processed, so it would like to take the Medication Request events streaming into their existing OLTP database and acquire a sense for (e.g.) how many non-processed requests there are per week, and why this might be happening (for example, as in this famous study of judicial rulings before and after lunch breaks, it may be due to doctor fatigue)
On top of your own analyic findings regarding the Medication Requests issue, the hospital would also like to have an Administrative Dashboard wherein the data you used to derive your conclusions (in the previous step) is made continuously available to hospital administrators. Among other benefits, this would ensure that the effects of changes implemented by the hospital to address the issue can be monitored over time.

As part of this task, you have been given access to the hospital’s OLTP database (generated by Synthea, and detailed below), and you have been told to “take it from there”: your job, on Questions 1-6 on the Midterm, is to plan out what each step in an OLTP $\leadsto$ OLAP $\leadsto$ analytic insights pipeline for this task might look like.

Question 1 Starter: OLTP Structure

An example of the OLTP records that might show up in the hospital’s DB over a given period of time is as follows. You can imagine this as a sample of the most-recently-added rows in an events table (think of the streaming-events OLTP demo from the Week 1 slides!)

Code

import pandas as pd
from great_tables import GT

event_df = pd.read_csv('med_requests.csv')

def move_to_front(df, colname):
  col_data = df.pop(colname)
  df.insert(0, colname, col_data)
  return df
# Move important cols to front
event_df = move_to_front(event_df, 'subject.reference')
event_df = move_to_front(event_df, 'encounter.reference')
event_df = move_to_front(event_df, 'authoredOn')
event_df = move_to_front(event_df, 'resourceType')
GT(event_df)

resourceType	authoredOn	encounter.reference	subject.reference	id	status	intent	meta.profile	medicationCodeableConcept.coding	medicationCodeableConcept.text	requester.reference	requester.display	reasonReference	dosageInstruction	medicationReference.reference
MedicationRequest	2022-07-03T08:53:13-07:00	Encounter/44e48251-0d05-1f97-2669-1030589ec71d	Patient/fcca54e8-5abe-cd71-30a0-464f15368bea	68b82965-d48a-9a65-3413-348749c6655e	stopped	order	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-medicationrequest']	[{'system': 'http://www.nlm.nih.gov/research/umls/rxnorm', 'code': '834061', 'display': 'Penicillin V Potassium 250 MG Oral Tablet'}]	Penicillin V Potassium 250 MG Oral Tablet	Practitioner?identifier=http://hl7.org/fhir/sid/us-npi\|9999998479	Dr. Richard Stroman	[{'reference': 'Condition/ca945918-6755-ec6a-8f46-ffc844a90a27'}, {'reference': 'Condition/a61f19e0-a659-b662-25d2-5e973595ec2f'}]	None	None
MedicationRequest	2022-07-02T20:58:26-07:00	Encounter/46127bb5-1eab-c8e3-f627-7a3cae268f3b	Patient/50aac3ff-d836-adfd-620d-662fa08fdf55	145b9a89-bec1-de9e-f0cd-c05b689b13cf	active	order	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-medicationrequest']	[{'system': 'http://www.nlm.nih.gov/research/umls/rxnorm', 'code': '857005', 'display': 'Acetaminophen 325 MG / HYDROcodone Bitartrate 7.5 MG Oral Tablet'}]	Acetaminophen 325 MG / HYDROcodone Bitartrate 7.5 MG Oral Tablet	Practitioner?identifier=http://hl7.org/fhir/sid/us-npi\|9999998479	Dr. Richard Stroman	None	[{'sequence': 1, 'timing': {'repeat': {'frequency': 1, 'period': 6, 'periodUnit': 'h'}}, 'asNeededBoolean': False, 'doseAndRate': [{'type': {'coding': [{'system': 'http://terminology.hl7.org/CodeSystem/dose-rate-type', 'code': 'ordered', 'display': 'Ordered'}]}, 'doseQuantity': {'value': 1}}]}]	None
MedicationRequest	2022-07-02T20:58:26-07:00	Encounter/46127bb5-1eab-c8e3-f627-7a3cae268f3b	Patient/50aac3ff-d836-adfd-620d-662fa08fdf55	e17765e9-66b5-d2d4-1407-9e6994ecb15f	active	order	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-medicationrequest']	[{'system': 'http://www.nlm.nih.gov/research/umls/rxnorm', 'code': '310965', 'display': 'Ibuprofen 200 MG Oral Tablet'}]	Ibuprofen 200 MG Oral Tablet	Practitioner?identifier=http://hl7.org/fhir/sid/us-npi\|9999998479	Dr. Richard Stroman	None	[{'sequence': 1, 'text': 'Take as needed.', 'asNeededBoolean': True}]	None
MedicationRequest	2022-07-01T09:18:17-07:00	Encounter/3930d700-a1ad-6b5d-d342-c002fede75f5	Patient/a7b903a5-5c6f-3f5e-4035-62d94b0b52b3	a64a2543-6777-f012-d09e-7a215ccda2e5	active	order	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-medicationrequest']	[{'system': 'http://www.nlm.nih.gov/research/umls/rxnorm', 'code': '2123111', 'display': 'NDA020503 200 ACTUAT Albuterol 0.09 MG/ACTUAT Metered Dose Inhaler'}]	NDA020503 200 ACTUAT Albuterol 0.09 MG/ACTUAT Metered Dose Inhaler	Practitioner?identifier=http://hl7.org/fhir/sid/us-npi\|9999998909	Dr. Lola Tamayo	[{'reference': 'Condition/0c1d6ee6-d786-825f-8522-87b2c2804835'}]	[{'sequence': 1, 'text': 'Take as needed.', 'asNeededBoolean': True}]	None
MedicationRequest	2022-07-01T09:18:17-07:00	Encounter/3930d700-a1ad-6b5d-d342-c002fede75f5	Patient/a7b903a5-5c6f-3f5e-4035-62d94b0b52b3	98245498-7a3d-21e7-9cb4-c0bc40de7003	active	order	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-medicationrequest']	[{'system': 'http://www.nlm.nih.gov/research/umls/rxnorm', 'code': '895994', 'display': '120 ACTUAT Fluticasone propionate 0.044 MG/ACTUAT Metered Dose Inhaler'}]	120 ACTUAT Fluticasone propionate 0.044 MG/ACTUAT Metered Dose Inhaler	Practitioner?identifier=http://hl7.org/fhir/sid/us-npi\|9999998909	Dr. Lola Tamayo	[{'reference': 'Condition/0c1d6ee6-d786-825f-8522-87b2c2804835'}]	[{'sequence': 1, 'text': 'Take as needed.', 'asNeededBoolean': True}]	None

Let’s “zoom in” on the first event in this table:

Code

event_df.iloc[0]

resourceType                                                        MedicationRequest
authoredOn                                                  2022-07-03T08:53:13-07:00
encounter.reference                    Encounter/44e48251-0d05-1f97-2669-1030589ec71d
subject.reference                        Patient/fcca54e8-5abe-cd71-30a0-464f15368bea
id                                               68b82965-d48a-9a65-3413-348749c6655e
status                                                                        stopped
intent                                                                          order
meta.profile                        ['http://hl7.org/fhir/us/core/StructureDefinit...
medicationCodeableConcept.coding    [{'system': 'http://www.nlm.nih.gov/research/u...
medicationCodeableConcept.text              Penicillin V Potassium 250 MG Oral Tablet
requester.reference                 Practitioner?identifier=http://hl7.org/fhir/si...
requester.display                                                 Dr. Richard Stroman
reasonReference                     [{'reference': 'Condition/ca945918-6755-ec6a-8...
dosageInstruction                                                                 NaN
medicationReference.reference                                                     NaN
Name: 0, dtype: object

From this output we can see that it represents a MedicationRequest event which was authored (and thus appeared in the table) on July 7, 2022.

Since this kind of event, a MedicationRequest, represents one possible “sub-event” within a broader event called an Encounter, somewhere earlier on in the OLTP DB there would be an Encounter event recorded in the system – this is how the MedicationRequest reference is able to refer back to this earlier event via the encounter.reference field. For the first row, this encounter.reference value is:

Code

event_df.iloc[0]['encounter.reference']

'Encounter/44e48251-0d05-1f97-2669-1030589ec71d'

And indeed, if you filter the full Synthea-generated data, you will find an earlier record with a resourceType value of Encounter and an id value matching the above UUID. It looks as follows:

Code

encounter_df = pd.read_csv('encounters.csv')
GT(encounter_df)

resourceType	id	identifier	status	type	participant	location	meta.profile	class.system	class.code	subject.reference	subject.display	period.start	period.end	serviceProvider.reference	serviceProvider.display	reasonCode	hospitalization.dischargeDisposition.coding	hospitalization.dischargeDisposition.text
Encounter	44e48251-0d05-1f97-2669-1030589ec71d	[{'use': 'official', 'system': 'https://github.com/synthetichealth/synthea', 'value': '44e48251-0d05-1f97-2669-1030589ec71d'}]	finished	[{'coding': [{'system': 'http://snomed.info/sct', 'code': '185345009', 'display': 'Encounter for symptom'}], 'text': 'Encounter for symptom'}]	[{'type': [{'coding': [{'system': 'http://terminology.hl7.org/CodeSystem/v3-ParticipationType', 'code': 'PPRF', 'display': 'primary performer'}], 'text': 'primary performer'}], 'period': {'start': '2022-07-03T08:53:13-07:00', 'end': '2022-07-03T09:08:13-07:00'}, 'individual': {'reference': 'Practitioner?identifier=http://hl7.org/fhir/sid/us-npi\|9999998479', 'display': 'Dr. Richard Stroman'}}]	[{'location': {'reference': 'Location?identifier=https://github.com/synthetichealth/synthea\|384fe7cb-a286-37a5-bc55-ba430168611b', 'display': 'TORRANCE MEMORIAL MEDICAL CENTER'}}]	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-encounter']	http://terminology.hl7.org/CodeSystem/v3-ActCode	AMB	Patient/fcca54e8-5abe-cd71-30a0-464f15368bea	Lucia Vaca	2022-07-03T08:53:13-07:00	2022-07-03T09:08:13-07:00	Organization?identifier=https://github.com/synthetichealth/synthea\|2be6e135-2d16-3e7c-9239-9819ed165e48	TORRANCE MEMORIAL MEDICAL CENTER	[{'coding': [{'system': 'http://snomed.info/sct', 'code': '43878008', 'display': 'Streptococcal sore throat (disorder)'}]}]	None	None

Finally, notice how both the MedicationRequest event and the Encounter event must refer back to an even earlier event, the event of a new patient being created (their information being gathered and sent to the OLTP DB). We can look at the subject.reference field for both to make sure they match:

Code

print(event_df.iloc[0]['subject.reference'])
print(encounter_df.iloc[0]['subject.reference'])

Patient/fcca54e8-5abe-cd71-30a0-464f15368bea
Patient/fcca54e8-5abe-cd71-30a0-464f15368bea

And, once again, we can look even earlier in the Synthea-generated data to locate an entry with a resourceType value of Patient and an id value matching this UUID:

Code

patient_df = pd.read_csv('patients.csv')
GT(patient_df)

resourceType	id	extension	identifier	name	telecom	gender	birthDate	address	multipleBirthBoolean	communication	meta.profile	text.status	text.div	maritalStatus.coding	maritalStatus.text	multipleBirthInteger	deceasedDateTime
Patient	fcca54e8-5abe-cd71-30a0-464f15368bea	[{'url': 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-race', 'extension': [{'url': 'ombCategory', 'valueCoding': {'system': 'urn:oid:2.16.840.1.113883.6.238', 'code': '2106-3', 'display': 'White'}}, {'url': 'text', 'valueString': 'White'}]}, {'url': 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity', 'extension': [{'url': 'ombCategory', 'valueCoding': {'system': 'urn:oid:2.16.840.1.113883.6.238', 'code': '2135-2', 'display': 'Hispanic or Latino'}}, {'url': 'text', 'valueString': 'Hispanic or Latino'}]}, {'url': 'http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName', 'valueString': 'Rebeca Sandoval'}, {'url': 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex', 'valueCode': 'F'}, {'url': 'http://hl7.org/fhir/StructureDefinition/patient-birthPlace', 'valueAddress': {'city': 'Wesley', 'state': 'Saint Andrew Parish', 'country': 'DM'}}, {'url': 'http://synthetichealth.github.io/synthea/disability-adjusted-life-years', 'valueDecimal': 0}, {'url': 'http://synthetichealth.github.io/synthea/quality-adjusted-life-years', 'valueDecimal': 8}]	[{'system': 'https://github.com/synthetichealth/synthea', 'value': 'fcca54e8-5abe-cd71-30a0-464f15368bea'}, {'type': {'coding': [{'system': 'http://terminology.hl7.org/CodeSystem/v2-0203', 'code': 'MR', 'display': 'Medical Record Number'}], 'text': 'Medical Record Number'}, 'system': 'http://hospital.smarthealthit.org', 'value': 'fcca54e8-5abe-cd71-30a0-464f15368bea'}, {'type': {'coding': [{'system': 'http://terminology.hl7.org/CodeSystem/v2-0203', 'code': 'SS', 'display': 'Social Security Number'}], 'text': 'Social Security Number'}, 'system': 'http://hl7.org/fhir/sid/us-ssn', 'value': '999-93-3994'}]	[{'use': 'official', 'family': 'Vaca', 'given': ['Lucia']}]	[{'system': 'phone', 'value': '555-537-8228', 'use': 'home'}]	female	2013-04-07	[{'extension': [{'url': 'http://hl7.org/fhir/StructureDefinition/geolocation', 'extension': [{'url': 'latitude', 'valueDecimal': 33.797187462902954}, {'url': 'longitude', 'valueDecimal': -118.28572853994088}]}], 'line': ['1085 Grant Boulevard'], 'city': 'Los Angeles', 'state': 'CA', 'postalCode': '91330', 'country': 'US'}]	False	[{'language': {'coding': [{'system': 'urn:ietf:bcp:47', 'code': 'es', 'display': 'Spanish'}], 'text': 'Spanish'}}]	['http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient']	generated	<div xmlns="http://www.w3.org/1999/xhtml">Generated by <a href="https://github.com/synthetichealth/synthea">Synthea</a>.Version identifier: v3.0.0 . Person seed: -5085859045495963725 Population seed: 0</div>	[{'system': 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus', 'code': 'S', 'display': 'Never Married'}]	Never Married	None	None

Note the complexities that may be involved in turning this OLTP-format stream of events into an analytics-friendly OLAP infrastructure meeting the hospital’s needs (as listed above). For example:

A simple issue could just be de-duplication: One doctor might think that the person they’re treating is a new patient, for example, but the person’s information might actually have been entered earlier by a different doctor (and, the information might in fact be slightly different! I accidentally tell doctors “Jeff” instead of my technical legal name “Jeffrey”, for example, and it is often entered by doctors as “Jeffery”, “Geoff”, “Geoffrey”, etc. 😵‍💫)
A more complex issue that we’ve discussed in earlier weeks could be data provenance (also referred to as “data lineage” sometimes): perhaps the failing medication requests are being entered by a doctor’s assistant, and the system thus detects that the assistant is not authorized to make medication requests, so that they are subsequently denied.
How might you handle a case where someone’s custodian or legal guardian is the one actually present at the appointment, such that the medication request was made during an encounter with person $X$ (the guardian) but the medication itself is for a patient $Y$?