Build a FHIR-to-SQL Health Data Pipeline

Extract clinical FHIR data into SQLite and run SQL cohort analyses.

Introduction

30 Second Summary

Every time you visit a doctor, your vitals, diagnoses, and lab results get stored in a healthcare system. Health data teams need to pull this scattered clinical information together to answer questions like "do patients with hypertension tend to have higher BMI?" The standard format for exchanging this data is called FHIR.

In this project, you will build a Python pipeline that connects to a live FHIR server, extracts patient records and clinical observations, transforms the deeply nested healthcare data into clean database tables, and runs SQL queries to compare vital signs across patient groups.

What You'll Build

You run a Jupyter notebook cell and watch it pull real clinical records from a live FHIR server, flatten the messy nested JSON into tidy relational tables, and produce a bar chart comparing vital signs across patient cohorts.

By the end of this project, you'll have:

  • A working FHIR connection that pulls patient demographics and clinical observations from a live healthcare server using Python.
  • A local SQLite database with clean relational tables transformed from deeply nested FHIR JSON, with proper foreign keys linking patients to their observations.
  • SQL cohort queries and matplotlib visualizations that answer clinical questions like "Do different age groups have different average vital signs?"
  • Secret Mission: A time-series analysis tracking how a single patient's blood pressure changes over multiple visits.

Are there any prerequisites?

This project assumes you have Python fundamentals (variables, functions, loops), basic pandas knowledge (DataFrames, columns), and basic SQL knowledge (SELECT, JOIN, GROUP BY). You do not need any prior healthcare or FHIR experience.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. Taking a moment to connect the project goals to the real world makes every step that follows more intentional.

Set Up and Connect to the FHIR Server

Before building a data pipeline, you need two things in place. A Python library that speaks the FHIR protocol, and confirmation that your target server is actually reachable.

The fhirpy library provides a clean, Pythonic interface for querying FHIR servers. In this step, you'll install it, create your pipeline notebook, and confirm that the HAPI FHIR public test server is responding to requests.

In this step, get ready to:
  • Install the fhirpy Python library.
  • Create a Jupyter notebook for the pipeline.
  • Connect to the HAPI FHIR test server and verify it's reachable.
Install fhirpy and create the notebook

The fhirpy library handles pagination, search parameter encoding, and resource type filtering when communicating with FHIR servers. You need it installed before you can import anything.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Install fhirpy by running this command:
pip install fhirpy==2.0.15

You should see output ending with a line like Successfully installed fhirpy-2.0.15 (along with any dependencies).

Seeing 'command not found' or a permissions error?

If pip is not recognized, try pip3 instead. Some systems require pip3 to target Python 3.

If you see a permissions error on macOS or Linux, add --user to the end of the command to install into your user directory.

Help me troubleshoot my pip install error.

  • Launch Jupyter Notebook by running:
jupyter notebook

What happens when you run this?

This command starts a local Jupyter server and opens your default browser to the Jupyter file browser. Your terminal will show log output and stay busy while Jupyter runs. Leave it open.

  • In the Jupyter file browser that opened in your browser, click New in the top right corner.
  • Select Python 3 from the dropdown to create a new notebook.
  • Click Untitled at the top of the page and rename the notebook to fhir_pipeline. Jupyter adds the .ipynb extension automatically.

You should see your new fhir_pipeline.ipynb notebook open with an empty cell ready for input.

  • In the first cell, type the following setup comment and press Shift+Enter to run it:
# Cell 1: Setup
# pip install fhirpy==2.0.15

Why is this just a comment?

You already installed fhirpy from your terminal. This comment records the dependency so anyone reading your notebook knows what needs to be installed to run it.

Connect to the FHIR server and verify

With fhirpy installed, you can now connect to the HAPI FHIR public test server. This server hosts synthetic clinical data and requires no authentication.

  • Click into the new empty cell below Cell 1. Type the following connection code:
# Cell 2: Connect to FHIR Server
from fhirpy import SyncFHIRClient

client = SyncFHIRClient(url="http://hapi.fhir.org/baseR4")

patient_count = client.resources("Patient").count()
print(f"Server has {patient_count} Patient resources available")

What does this code do?

  • SyncFHIRClient creates a synchronous connection to the FHIR server at the URL you provide. No async event loops needed in a notebook.
  • client.resources("Patient") tells fhirpy you want to work with Patient resources on that server.
  • .count() sends a request to the server asking how many Patient resources exist. This is a lightweight way to confirm the connection works without downloading actual data.

Before you run this cell, what do you think will happen? Will the server respond with a number, or will you see an error?

  • Press Shift+Enter to run Cell 2.

You should see output like Server has 1234 Patient resources available (the exact number will vary since the server's data changes over time).

Seeing a connection error?

If you see ConnectionError or a timeout, the HAPI FHIR test server may be temporarily down. It is a free public server that occasionally goes offline for maintenance. Wait a minute and try running the cell again.

If you see ModuleNotFoundError: No module named 'fhirpy', the installation did not reach the Python environment your notebook is using. Run !pip install fhirpy==2.0.15 directly in a notebook cell, then restart your kernel from Kernel > Restart and re-run Cell 2.

Help me troubleshoot my FHIR server connection error.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook with Cmd+S (macOS) or Ctrl+S (Windows).

ⓧ I'd like to double check the full code

Here is everything your notebook should contain so far:

# Cell 1: Setup
# pip install fhirpy==2.0.15

# Cell 2: Connect to FHIR Server
from fhirpy import SyncFHIRClient

client = SyncFHIRClient(url="http://hapi.fhir.org/baseR4")

patient_count = client.resources("Patient").count()
print(f"Server has {patient_count} Patient resources available")

Your environment is ready and the FHIR server is confirmed reachable. Next up, you'll pull real patient and observation data from the server and explore the deeply nested JSON structure that FHIR returns.

Extract Patient and Observation Data

Your connection to the FHIR server is live. Now you need the raw clinical data that your pipeline will eventually transform and load into a database.

In this step, you will pull Patient and Observation resources from the server using fhirpy's search API. The data comes back as deeply nested JSON. Inspecting that nested structure is the key to understanding what you will need to flatten later.

In this step, get ready to:
  • Fetch Patient resources from the FHIR server.
  • Extract Observation resources for each patient filtered by vital signs.
  • Inspect the nested FHIR JSON to understand the data structure.
Fetch Patient resources

The HAPI FHIR server holds thousands of synthetic patient records. You only need a sample to build and test your pipeline.

  • Create a new cell in your notebook (below the connection cell) and fetch 50 patients by running this code:
# Cell 3: Extract Patient Data
patients_raw = client.resources("Patient").limit(50).fetch()
print(f"Fetched {len(patients_raw)} patients")
print("Sample patient structure:")
print(patients_raw[0].serialize())

What does this code do?

This cell uses two fhirpy methods you saw in Step 1.

  • .limit(50) caps the query at 50 resources so you get a manageable sample.
  • .fetch() executes the query and returns a list of resource objects.
  • .serialize() converts the first patient resource into a plain Python dictionary so you can see its full nested structure.

Before you run the cell, what do you think a single Patient resource looks like? Do you expect a flat record or something more complex?

  • Run the cell.

You should see a count line followed by a large nested dictionary. Look for fields like name (an array of objects), gender, birthDate, and address.

Why is the name field an array?

FHIR resources are designed to handle real-world complexity. A patient can have multiple names (maiden name, married name, nickname). Each name object contains separate family and given fields rather than a single string.

This nesting is what makes FHIR data tricky to work with directly. You cannot just dump it into a spreadsheet. Flattening this structure into relational columns is the core challenge you will solve in the next step.

Seeing an empty output or an error?

  • If you see an IndexError, the server returned zero patients. Try running the cell again. The HAPI test server is public and can be temporarily slow.
  • If you see a connection error, confirm the server URL in Cell 2 is exactly http://hapi.fhir.org/baseR4 with no trailing slash.

Why can't I connect to the FHIR server?

Extract Observations for each patient

Patients alone are just demographics. The clinical value lives in their Observations: blood pressure readings, BMI measurements, heart rate, and more.

  • Create a new cell below Cell 3 and pull vital-sign observations for every patient by running this code:
# Cell 4: Extract Observations for Each Patient
all_observations_raw = []

for patient in patients_raw:
    patient_id = patient["id"]
    observations = client.resources("Observation").search(
        subject=f"Patient/{patient_id}",
        category="vital-signs"
    ).limit(20).fetch()
    all_observations_raw.extend(observations)

print(f"Fetched {len(all_observations_raw)} observations total")
if all_observations_raw:
    print("Sample observation structure:")
    print(all_observations_raw[0].serialize())

What does this code do?

  • The loop iterates through each patient and uses .search(subject=f"Patient/{patient_id}", category="vital-signs") to fetch only observations belonging to that patient in the vital-signs category.
  • subject and category are FHIR search parameters. They filter results on the server side so you only download relevant data.
  • .extend() appends each patient's observations to the master list, building a complete collection across all patients.

Do you think every patient will have vital-sign observations? What do you expect the total count to be?

  • Run the cell.

You should see a total observation count (the number varies because the HAPI test server uses synthetic data that gets refreshed). If observations were found, you will also see a sample observation dictionary printed below the count.

Some patients may have zero observations

The HAPI test server is regularly purged and reloaded with synthetic data. Some patients have rich observation histories while others have none. Your total count might be anywhere from a handful to several hundred. This is normal for real-world clinical data.

Seeing 0 observations total?

  • This can happen if the test server was recently purged. Try running Cell 3 again to fetch a fresh batch of patients, then re-run Cell 4.
  • Confirm the search parameter is category="vital-signs" (not categories or category='vitals').

Help me debug why I'm getting zero observations from the FHIR server.

Examine the Observation structure

Look at the sample observation dictionary that Cell 4 printed. This is the nested structure you will need to flatten into table columns in the next step.

What are the key fields in a FHIR Observation?

Each Observation resource contains several nested paths that map to clinical data points:

  • code.coding[0].display holds the measurement name (e.g. "Body mass index" or "Blood pressure").
  • valueQuantity.value and valueQuantity.unit hold the numeric reading and its unit (e.g. 28.5 kg/m2).
  • effectiveDateTime records when the measurement was taken.
  • subject.reference links the observation back to its patient with a string like "Patient/123".

Notice how the value is buried three levels deep (valueQuantity then value). This is typical of FHIR resources and is exactly why a flattening step is essential before analysis.

  • Scroll through the printed observation dictionary in your notebook output and try to locate each of these four paths: code.coding[0].display, valueQuantity.value, effectiveDateTime, and subject.reference.

What if my observation doesn't have valueQuantity?

Some observations (like blood pressure) store their values inside a component array instead of valueQuantity. For this project, you will extract only simple observations that use valueQuantity. The flattening function you write next will skip any observation missing that field.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all four cells.

ⓧ I'd like to double check the full code

Your notebook should now have four cells. Scroll up and confirm each is present in this order:

  • Cell 1: the pip install fhirpy==2.0.15 comment.
  • Cell 2: imports SyncFHIRClient, creates client, and prints the patient count.
  • Cell 3: fetches 50 patients into patients_raw and prints the first patient's serialized structure.
  • Cell 4: loops through patients_raw, searches for vital-signs observations per patient, collects them all into all_observations_raw, and prints the total count plus a sample observation.

You now have raw Patient and Observation data sitting in memory. The structures are deeply nested and full of optional fields. Next, you will flatten this mess into clean relational tables and load them into a SQLite database where you can query them with SQL.

Transform and Load into SQLite

You now have raw clinical data sitting in memory. The problem is that FHIR resources are deeply nested JSON structures with arrays inside arrays, optional fields, and inconsistent coding systems across different resources.

Flattening this nested JSON into clean relational tables with proper foreign keys is the core skill that makes FHIR data actually queryable. In this step, you will write functions that safely extract the fields you need, build DataFrames, and load everything into a local SQLite database.

In this step, get ready to:
  • Write a flatten function that safely extracts patient demographics from nested FHIR JSON.
  • Write a second flatten function for clinical observations with proper patient linking.
  • Load both DataFrames into a SQLite database and verify the row counts.
Write the flatten_patient() function

Each Patient resource stores names inside an array of objects, and any field could be missing entirely. You need a function that navigates this nested structure defensively using .get() so your pipeline does not crash on incomplete records.

  • Create a new cell in your notebook (Cell 5) and add the following code to define the flatten function and build your patients DataFrame:
# Cell 5: Transform Patient Data
import pandas as pd

def flatten_patient(resource):
    data = resource.serialize()
    patient_id = data.get("id", "")
    gender = data.get("gender", "unknown")
    birth_date = data.get("birthDate", None)

    family_name = ""
    given_name = ""
    names = data.get("name", [])
    if names:
        family_name = names[0].get("family", "")
        given_list = names[0].get("given", [])
        if given_list:
            given_name = given_list[0]

    return {
        "patient_id": patient_id,
        "family_name": family_name,
        "given_name": given_name,
        "gender": gender,
        "birth_date": birth_date,
    }

patients_flat = [flatten_patient(p) for p in patients_raw]
df_patients = pd.DataFrame(patients_flat)
print(f"Patients DataFrame: {len(df_patients)} rows")
print(df_patients.head())

What does this code do?

The flatten_patient() function calls .serialize() to convert a FHIR resource object into a plain Python dictionary. From there, it uses .get() with default values so missing fields return an empty string instead of crashing with a KeyError.

The nested name extraction is the trickiest part. FHIR stores patient names as an array (a patient can have multiple names). You drill into names[0] for the first entry, then extract family and given[0] separately.

  • Run Cell 5.
  • You should see output like Patients DataFrame: 50 rows followed by a table showing columns for patient_id, family_name, given_name, gender, and birth_date.

Seeing a NameError for patients_raw?

Make sure you ran Cells 1 through 4 first. The patients_raw variable is created in Cell 3. If your kernel restarted, re-run all previous cells in order.

If some rows show empty names or unknown gender, that is expected. The HAPI test server has incomplete records, and the .get() defaults handle these gracefully.

help me debug my flatten_patient function

Write the flatten_observation() function

Observations are even more deeply nested than Patient resources. The measurement value lives inside a valueQuantity object, the measurement type is buried in a code.coding array, and the patient link is a reference string you need to parse.

  • Create a new cell (Cell 6) and add the following code to flatten your observation data:
# Cell 6: Transform Observation Data
def flatten_observation(resource):
    data = resource.serialize()

    subject_ref = data.get("subject", {}).get("reference", "")
    patient_id = subject_ref.replace("Patient/", "") if subject_ref else ""

    code_display = ""
    code_info = data.get("code", {})
    codings = code_info.get("coding", [])
    if codings:
        code_display = codings[0].get("display", "")

    value = None
    unit = ""
    value_quantity = data.get("valueQuantity", {})
    if value_quantity:
        value = value_quantity.get("value")
        unit = value_quantity.get("unit", "")

    effective_date = data.get("effectiveDateTime", None)

    return {
        "patient_id": patient_id,
        "code_display": code_display,
        "value": value,
        "unit": unit,
        "effective_date": effective_date,
    }

observations_flat = [flatten_observation(o) for o in all_observations_raw]
df_observations = pd.DataFrame(observations_flat)
print(f"Observations DataFrame: {len(df_observations)} rows")
print(df_observations.head())

What does this code do?

The subject.reference field stores a string like Patient/12345. The function strips the prefix to get the raw patient ID, which becomes the foreign key linking observations back to patients.

The valueQuantity check handles a common FHIR quirk. Some observations (like blood pressure panels) store values in a component array instead. Those will get None for value here, which you will filter out during analysis.

  • Run Cell 6.
  • You should see output like Observations DataFrame: N rows (where N depends on how much data the HAPI server returned) followed by a table with columns patient_id, code_display, value, unit, and effective_date.

Seeing 0 rows in your observations DataFrame?

The HAPI test server is regularly purged and reloaded. If no observations were returned in Cell 4, re-run Cell 4 first and check its output count. If it still shows zero, try running Cell 3 again to fetch a fresh batch of patients.

help me debug why my observations DataFrame is empty

Load both DataFrames into SQLite

With your data cleanly flattened into two DataFrames, you can now load them into a relational database. Python's built-in sqlite3 module creates a local database file that requires zero setup.

  • Create a new cell (Cell 7) and add the following code to load your data into SQLite and verify the row counts:
# Cell 7: Load into SQLite
import sqlite3

conn = sqlite3.connect("fhir_clinical.db")

df_patients.to_sql("patients", conn, if_exists="replace", index=False)
df_observations.to_sql("observations", conn, if_exists="replace", index=False)

patient_count_sql = pd.read_sql("SELECT COUNT(*) as count FROM patients;", conn)
obs_count_sql = pd.read_sql("SELECT COUNT(*) as count FROM observations;", conn)
print(f"Loaded {patient_count_sql['count'][0]} patients into SQLite")
print(f"Loaded {obs_count_sql['count'][0]} observations into SQLite")

What does this code do?

The sqlite3.connect("fhir_clinical.db") call creates a new database file in your current directory (or opens it if it already exists). No server, no configuration needed.

The df.to_sql() method from pandas writes each DataFrame directly into a SQL table. The if_exists="replace" argument means re-running this cell overwrites the old data cleanly. The SELECT COUNT(*) queries confirm data actually landed in the tables.

Before you run this cell, what do you think the output will show? Will both counts be non-zero?

  • Run Cell 7.
  • You should see two lines printed. One confirming the number of patients loaded (matching your DataFrame row count from Cell 5). One confirming the number of observations loaded (matching your DataFrame row count from Cell 6). Both numbers should be greater than zero.

Seeing an OperationalError?

If you see a database-related error, make sure you have write permissions in the directory where your notebook is running. The fhir_clinical.db file is created in the same folder as your notebook.

If the counts show zero, go back and confirm your DataFrames (df_patients and df_observations) have data by running len(df_patients) and len(df_observations) in a quick cell.

help me fix my SQLite loading step

Why SQLite instead of PostgreSQL?

SQLite is built into Python's standard library, so there is nothing to install or configure. It teaches the same relational concepts and SQL query skills that transfer directly to production databases like PostgreSQL.

For a data pipeline prototype in a notebook, zero-setup wins. You can always swap the connection string later when moving to production.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all cells through Cell 7.

ⓧ I'd like to double check the full code

Here are the three cells you added in this step. Scroll up and confirm each one is present in your notebook, in order.

  • Cell 5: Transform Patient Data. Includes the import pandas as pd line, the full flatten_patient() function definition, the list comprehension, DataFrame creation, and two print statements.
  • Cell 6: Transform Observation Data. Includes the full flatten_observation() function definition, the list comprehension, DataFrame creation, and two print statements.
  • Cell 7: Load into SQLite. Includes import sqlite3, the connection, two .to_sql() calls, and the COUNT verification queries.

Your raw FHIR data is now sitting in a proper relational database with two linked tables. Next up, you will write SQL queries that join these tables to answer real clinical questions and visualize the results.

Run SQL Cohort Queries and Visualize

Your clinical data is now sitting in a proper relational database with patients linked to their observations. The hard transformation work is done.

Now for the payoff: answering clinical questions with SQL. You will join the patients and observations tables to compare vital sign averages across patient groups, then visualize the results with matplotlib bar charts.

In this step, get ready to:
  • Run a SQL query that calculates average vital signs grouped by patient gender.
  • Run a second query that groups patients into age brackets and compares vital signs across groups.
  • Visualize both results as matplotlib bar charts.
Query vital signs by gender

The simplest cohort analysis starts with a demographic split everyone can understand: do vital sign averages differ between genders?

  • Create a new cell in your notebook (Cell 8) and write the following SQL query:
# Cell 8: SQL Cohort Query - Vital Signs by Gender
query_by_gender = """
SELECT
    p.gender,
    o.code_display,
    ROUND(AVG(o.value), 2) as avg_value,
    COUNT(*) as num_readings
FROM observations o
JOIN patients p ON o.patient_id = p.patient_id
WHERE o.value IS NOT NULL
GROUP BY p.gender, o.code_display
HAVING num_readings >= 3
ORDER BY o.code_display, p.gender;
"""

df_gender = pd.read_sql(query_by_gender, conn)
print("Vital signs by gender:")
print(df_gender)

What does this query do?

This SQL joins the two tables on patient_id, then groups by gender and vital sign type. For each group it calculates the average value and counts the readings.

The HAVING num_readings >= 3 clause filters out groups with too few data points. This prevents a single stray reading from skewing your results.

  • Run the cell.
  • You should see a table printed with columns for gender, code_display, avg_value, and num_readings.

Seeing an empty DataFrame?

If df_gender is empty, the HAVING clause may be filtering out all groups because no group has 3 or more readings. This happens if the test server had sparse data for your particular patients. Try lowering HAVING num_readings >= 3 to HAVING num_readings >= 1 temporarily to confirm data exists.

Also confirm that conn is still open. If you restarted your kernel since Cell 7, you need to re-run Cell 7 first.

Why is my gender query returning no results?

Query vital signs by age group

Gender is a broad split. Age groups give you a more clinically meaningful breakdown because vital signs naturally shift across a patient's lifespan.

  • Create a new cell (Cell 9) and write a query that buckets patients into age groups:
# Cell 9: SQL Query - Vital Signs by Age Group
query_by_age = """
SELECT
    CASE
        WHEN (strftime('%Y', 'now') - strftime('%Y', p.birth_date)) < 30 THEN 'Under 30'
        WHEN (strftime('%Y', 'now') - strftime('%Y', p.birth_date)) BETWEEN 30 AND 50 THEN '30-50'
        WHEN (strftime('%Y', 'now') - strftime('%Y', p.birth_date)) BETWEEN 51 AND 70 THEN '51-70'
        ELSE 'Over 70'
    END as age_group,
    o.code_display,
    ROUND(AVG(o.value), 2) as avg_value,
    COUNT(*) as num_readings
FROM observations o
JOIN patients p ON o.patient_id = p.patient_id
WHERE o.value IS NOT NULL AND p.birth_date IS NOT NULL
GROUP BY age_group, o.code_display
HAVING num_readings >= 3
ORDER BY o.code_display, age_group;
"""

df_age = pd.read_sql(query_by_age, conn)
print("Vital signs by age group:")
print(df_age)

How does the age bucketing work?

SQLite does not have a built-in age function. Instead, strftime('%Y', 'now') gets the current year and strftime('%Y', p.birth_date) gets the birth year. Subtracting them gives an approximate age.

The CASE expression then sorts each patient into one of four buckets: Under 30, 30-50, 51-70, or Over 70. The WHERE p.birth_date IS NOT NULL filter ensures patients without a birth date do not break the calculation.

  • Run the cell.
  • You should see a table with an age_group column containing values like Under 30, 30-50, etc., alongside average vital sign values.

Getting a SQL error about strftime?

Double-check the single quotes around %Y and now in the query. SQLite requires these to be string literals wrapped in single quotes inside the triple-quoted Python string.

Help me fix my strftime SQL query for age groups.

Visualize the results with matplotlib

Tables of numbers answer the question, but a chart makes the answer jump off the screen. You will plot both query results side-by-side as bar charts.

  • Create a new cell (Cell 10) and add the visualization code:
# Cell 10: Visualize Results
import matplotlib.pyplot as plt

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# Plot 1: By Gender
if not df_gender.empty:
    top_vital = df_gender["code_display"].value_counts().idxmax()
    gender_subset = df_gender[df_gender["code_display"] == top_vital]
    axes[0].bar(gender_subset["gender"], gender_subset["avg_value"])
    axes[0].set_xlabel("Gender")
    axes[0].set_ylabel(f"Average Value")
    axes[0].set_title(f"Average {top_vital} by Gender")

# Plot 2: By Age Group
if not df_age.empty:
    top_vital_age = df_age["code_display"].value_counts().idxmax()
    age_subset = df_age[df_age["code_display"] == top_vital_age]
    axes[1].bar(age_subset["age_group"], age_subset["avg_value"])
    axes[1].set_xlabel("Age Group")
    axes[1].set_ylabel(f"Average Value")
    axes[1].set_title(f"Average {top_vital_age} by Age Group")

plt.tight_layout()
plt.show()

What does this visualization code do?

The code creates a figure with two subplots side-by-side. For each chart, it picks the vital sign type with the most data points (value_counts().idxmax()) so the bars have meaningful averages to compare.

The if not df_gender.empty guards prevent errors if either query returned no data. This defensive pattern is common in health data work where missing data is the norm.

Before you run this cell, what do you expect to see? Think about which vital sign will have the most readings and whether averages might differ across groups.

  • Run the cell.
  • You should see two bar charts displayed inline. The left chart shows average vital sign values split by gender. The right chart shows the same for age groups.

Charts appear but bars are missing?

If one or both charts show empty axes with no bars, the corresponding DataFrame (df_gender or df_age) was empty. Scroll up and check that those cells printed non-empty tables. The if not df_gender.empty guard silently skips plotting when there is no data.

Why are my matplotlib charts showing empty axes?

  • Create one final cell (Cell 11) to close the database connection:
# Cell 11: Cleanup
conn.close()
  • Run the cell to release the database lock on fhir_clinical.db.

✔️ Awesome, I've got everything!

Great. Confirm you have saved your notebook with all cells through Cell 11.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these cells are present in order:

  • Cell 8: SQL Cohort Query - Vital Signs by Gender (defines query_by_gender and prints df_gender).
  • Cell 9: SQL Query - Vital Signs by Age Group (defines query_by_age with CASE expression and prints df_age).
  • Cell 10: Visualize Results (imports matplotlib.pyplot, creates two bar charts, calls plt.show()).
  • Cell 11: Cleanup (calls conn.close()).

You have gone from raw FHIR JSON all the way to a clinical visualization. Your pipeline extracts, transforms, loads, queries, and visualizes in a single notebook. Next, take on the Secret Mission to track a single patient's vitals over time.

Secret mission

Time-Series Patient Vital Signs

Your cohort queries answer questions about groups of patients. But real clinical analytics often requires tracking how a single patient's measurements change over time. Can you build a time-series analysis that plots one patient's vital signs across multiple visits?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with no cloud services or paid APIs, so there are no ongoing costs.

Resources you used:

  • fhir_clinical.db SQLite database file stored locally on your machine.
  • fhir_pipeline.ipynb Jupyter notebook containing your full pipeline code.
  • fhirpy Python package installed via pip.

Keep everything running

No action needed. Choose this if you want to keep experimenting with your clinical database or extend the notebook with new queries.

  • Your fhir_clinical.db file stays on disk for future SQL experiments.
  • Your fhir_pipeline.ipynb notebook is ready to rerun or extend at any time.
  • The fhirpy package remains installed in your Python environment for future FHIR projects.

Pause - I'll come back to this later

There are no running processes or active connections to shut down. Your files are already at rest on disk.

  • Close the Jupyter notebook tab in your browser.
  • Stop the Jupyter server in your terminal by pressing Ctrl+C if it is still running.
  • Your fhir_clinical.db and fhir_pipeline.ipynb files will be waiting for you when you return.

Delete - I don't want to use this again

Remove all project files and uninstall the fhirpy package to start fresh.

  • Delete the SQLite database and notebook by running this command from the folder where they are saved:

macOS

rm fhir_clinical.db fhir_pipeline.ipynb

Windows

Remove-Item fhir_clinical.db, fhir_pipeline.ipynb
  • Uninstall the fhirpy package by running:
pip uninstall fhirpy
  • Confirm the removal by entering y when prompted.

Nice Work!

Nice Work!

Nice work! You just built a complete FHIR-to-SQL health data pipeline that extracts clinical data from a live server, transforms it into relational tables, and answers real cohort analysis questions.

You've learned how to:

  • Connect to a live FHIR R4 server and extract clinical resources (Patient demographics and vital-sign Observations) using the fhirpy Python client.
  • Transform deeply nested FHIR JSON into clean relational tables with proper foreign keys and load them into a SQLite database using pandas.
  • Run SQL cohort queries joining patients to their observations, group by demographics, and visualize clinical comparisons with matplotlib bar charts.
  • Secret Mission: Built a time-series analysis tracking a patient's vital signs over multiple visits, plotted as a line chart over time.

Ready to quiz yourself?