Build a CTIS Trial Landscape

Analyze rare-disease CTIS exports with Python and create an HTML dashboard.

Introduction

30 Second Summary

Public trial searches can leave important details scattered across long result lists. For a rare condition, that makes it difficult to see where studies are happening or who is behind them.

In this project, you will turn public trial results from the Clinical Trials Information System (CTIS) into a reusable local analysis with Python. The finished workflow transforms a live CSV export into a cleaned dataset plus a standalone HTML dashboard.

What You'll Build

Your finished dashboard will show the trial landscape for your chosen condition through summary cards, ranked breakdowns, plus a trial snapshot table.

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

  • A safe practice run where you can reveal four fictional trial rows plus the file structure behind them.
  • A reusable analysis command that adapts to the headers in a fresh public export.
  • A cleaned trial landscape you can open in Microsoft Edge to compare phases, statuses, countries, plus sponsors.
  • Secret Mission: Add a recruitment-status watchlist that filters the cleaned dataset using a keyword from your dashboard.

Are there any prerequisites?

This project assumes basic Python familiarity plus Visual Studio Code on a Windows computer. You also need internet access plus a rare disease or medical condition to investigate.

Before We Start

This is your moment to choose the rare disease or medical condition that anchors the project. You will also define the decision or question that your trial landscape should support before the hands-on work begins.

Verify Your Windows Workspace

A live trial landscape depends on a local workspace that can run Python. Your network must also reach the public Clinical Trials Information System (CTIS).

This step checks both dependencies before any files arrive. If the portal is blocked, you will keep moving with synthetic data until you can retry from a permitted network.

In this step, get ready to:
  • Prepare the Documents\ctis-pivot project workspace.
  • Confirm that the integrated terminal runs the pinned Python version.
  • Check access to the public CTIS portal.
Prepare the project workspace

A dedicated workspace keeps every project artifact under one path. You will create Documents\ctis-pivot with File Explorer.

  • Press the Windows logo key+E to open File Explorer.
  • Select Documents in the left navigation pane.
  • Select New in the toolbar.
  • Select Folder.
  • Type ctis-pivot as the folder name.
  • Press Enter to create the folder.

You will see ctis-pivot inside Documents.

An opened folder becomes a workspace in Visual Studio Code. Its Explorer sidebar keeps your upcoming files visible.

  • Press the Windows key to open Windows Search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.
  • Select File from the top menu.
  • Select Open Folder....
  • Select the Documents folder.
  • Select the ctis-pivot folder.
  • Click Select Folder.

Good start. Your ctis-pivot folder now appears at the top of the Explorer sidebar.

Verify the Python runtime

The analyzer relies on Python's standard library. A version check proves that the runtime is available from the same workspace where you will run every script.

  • Select Terminal from the Visual Studio Code top menu.
  • Select New Terminal.

You will see an integrated terminal at the bottom of Visual Studio Code. It starts inside Documents\ctis-pivot.

Before you run the check, what Python version do you expect the terminal to report?

  • Check the active Python runtime by running this command:
python --version

What does this command do?

The command asks Python to print its version number. It exits without running a script.

✔️ I see Python 3.14.7

The terminal prints Python 3.14.7. That confirms your workspace can run the pinned runtime.

That is one dependency cleared. Your future scripts can use the expected standard-library features.

ⓧ I see an older version

This project uses Python 3.14.7. Upgrade the runtime before you continue.

  • Open the official Python install manager instructions for Windows.
  • Follow the official instructions to install Python 3.14.7.
  • Restart Visual Studio Code after the installation finishes.
  • Return to the Documents\ctis-pivot workspace.
  • Select Terminal from the top menu.
  • Select New Terminal.
  • Repeat the version check by running this command:
python --version

What should I see?

The command prints the version used by the new terminal. The output should now show Python 3.14.7.

ⓧ Command not found

Windows cannot currently launch Python through the python command. The official Python install manager provides the recommended installation path.

  • Open the official Python install manager instructions for Windows.
  • Follow the official instructions to install Python 3.14.7.
  • Restart Visual Studio Code after the installation finishes.
  • Return to the Documents\ctis-pivot workspace.
  • Select Terminal from the top menu.
  • Select New Terminal.
  • Check the installed runtime by running this command:
python --version

What should I see?

The fresh terminal should print Python 3.14.7. This confirms that Windows can now launch the runtime.

Python check still failing?

  • Confirm that the Python installation completed before restarting Visual Studio Code.
  • Open a fresh integrated terminal after Visual Studio Code restarts.

If the runtime still does not appear, help me troubleshoot the Python version check on Windows.

Check the CTIS public portal

The CTIS public website provides searchable trial information without sign-in. Opening the portal in Microsoft Edge proves that your current network can reach the live data source.

  • Press the Windows key to open Windows Search.
  • Type Microsoft Edge into the search field.
  • Press Enter to open Microsoft Edge.
  • Press Ctrl+L to select the URL in the address bar.
  • Enter https://euctis-sat-ct2.ema.europa.eu/ into the address bar.

Before you load the portal, do you expect the public search to open without a sign-in barrier?

  • Press Enter to load the CTIS portal.

✔️ The public portal loads

You will see the CTIS public trial-search website. No sign-in prompt blocks access to the public search.

Your live data source is within reach. The export workflow can use the portal later in the project.

ⓧ My network blocks the portal

A blocked work network does not stop your practice workflow. The synthetic-data path keeps the next step available.

  • Continue with the synthetic dataset in the next step.
  • Retry the live CTIS portal from another permitted network before Step 3.

Workspace checkpoint

The Documents\ctis-pivot folder is created. It is open as your Visual Studio Code workspace.

The integrated terminal has confirmed Python 3.14.7.

The CTIS portal is reachable without sign-in. If your network blocks it, your synthetic-data path is ready.

Your Windows workspace is ready for trial data. Next, you will inspect a synthetic CTIS export before depending on the live portal.

Inspect a Synthetic Trial Export

Your Windows workspace is ready in Visual Studio Code. Python runs in the integrated terminal.

A synthetic CSV gives you a controlled version of the trial workflow. It exposes the file structure before a live Clinical Trials Information System (CTIS) export or its current schema enters the workflow.

In this step, get ready to:
  • Create a fictional export with four practice trial rows.
  • Build an inspector that identifies how the CSV is structured.
  • Run the inspector to reveal seven numbered headers.
Create the synthetic trial export

The practice file contains fictional records. No row represents a real trial or sponsor.

Several country cells contain multiple values inside one field. Those values give the later analysis a realistic data-quality challenge.

  • Select the ctis-pivot folder in the Visual Studio Code Explorer sidebar.
  • Click the New File icon in the Explorer toolbar.
  • Type sample_ctis_export.csv in the file name box.
  • Press Enter to create the file.
  • Fill sample_ctis_export.csv with the fictional practice records by pasting this data:
Trial number,Title,Sponsor,Trial phase,Overall status,Countries,Condition
SYNTH-001,First synthetic trial,Northstar Biotech,Phase II,Recruiting,"DE; FR; NL",Rare condition alpha
SYNTH-002,Second synthetic trial,Northstar Biotech,Phase I,Completed,DE,Rare condition alpha
SYNTH-003,Third synthetic trial,Harbor Research,Phase II,Recruiting,"FR; ES",Rare condition alpha
SYNTH-004,Fourth synthetic trial,Atlas Therapeutics,Phase III,Not yet recruiting,"NL; BE; DE",Rare condition alpha

What is in this practice file?

  • The first line defines seven headers.
  • The next four lines define fictional trial rows.
  • The Countries column includes cells such as DE; FR; NL.
  • Quotation marks keep each combined country value inside one CSV field.
  • Save sample_ctis_export.csv.
  • Confirm sample_ctis_export.csv appears inside the ctis-pivot folder in the Explorer sidebar.

Practice file looking different?

  • Check that the file name ends with .csv.
  • Confirm the header line starts with Trial number.
  • Compare each synthetic row with the supplied data.
  • Help me check my synthetic CSV file.

✔️ Awesome, I've got everything!

Great. Double-check that you saved sample_ctis_export.csv.

ⓧ I'd like to double check the full code

Trial number,Title,Sponsor,Trial phase,Overall status,Countries,Condition
SYNTH-001,First synthetic trial,Northstar Biotech,Phase II,Recruiting,"DE; FR; NL",Rare condition alpha
SYNTH-002,Second synthetic trial,Northstar Biotech,Phase I,Completed,DE,Rare condition alpha
SYNTH-003,Third synthetic trial,Harbor Research,Phase II,Recruiting,"FR; ES",Rare condition alpha
SYNTH-004,Fourth synthetic trial,Atlas Therapeutics,Phase III,Not yet recruiting,"NL; BE; DE",Rare condition alpha
Build the adaptive CSV inspector

A CSV dialect describes structural choices such as the delimiter. The loader tests several common encodings.

Python infers the delimiter from a file sample. This dynamic detection keeps the inspector useful when a later export has different file settings.

Why Python for this workflow?

Microsoft Excel can inspect one download manually. A Python script repeats the same structural checks after every export refresh.

  • Select the ctis-pivot folder in the Explorer sidebar.
  • Click the New File icon in the Explorer toolbar.
  • Type inspect_ctis.py in the file name box.
  • Press Enter to create the file.
  • Add the inspector imports to inspect_ctis.py by pasting this code:
import csv
import sys
from pathlib import Path

What do these imports provide?

  • The csv module reads rows according to the detected delimiter.
  • The sys module provides the command-line arguments.
  • The Path class represents the CSV file path.
  • Save inspect_ctis.py.
  • Confirm inspect_ctis.py appears beside sample_ctis_export.csv in the Explorer sidebar.

Imports showing a problem?

  • Confirm all three import lines sit at the top of inspect_ctis.py.
  • Check that the file name ends with .py.
  • Help me diagnose these Python imports.
  • Add the reusable CSV loader below the import group by pasting this code:
def load_csv(path):
    if not path.exists():
        raise FileNotFoundError(f"CSV file not found: {path}")

    errors = []
    for encoding in ("utf-8-sig", "utf-16", "cp1252"):
        try:
            with path.open("r", encoding=encoding, newline="") as handle:
                sample = handle.read(4096)
                handle.seek(0)

                if not sample.strip():
                    raise ValueError("The CSV file is empty.")

                try:
                    dialect = csv.Sniffer().sniff(sample, delimiters=",;\t")
                except csv.Error:
                    dialect = csv.excel

                reader = csv.DictReader(handle, dialect=dialect)
                rows = list(reader)
                headers = reader.fieldnames or []

            if headers:
                return headers, rows, encoding, dialect.delimiter
        except (UnicodeError, csv.Error, ValueError) as error:
            errors.append(f"{encoding}: {error}")

    details = "; ".join(errors)
    raise RuntimeError(f"Could not read the CSV file. Attempts: {details}")

What does the loader do?

  • The load_csv(path) function checks that the requested file exists.
  • The encoding loop tries utf-8-sig. It can also try utf-16 or cp1252.
  • The sniffer studies a sample to identify the delimiter.
  • The dictionary reader preserves the exact header labels from the file.
  • The return value provides the headers with the rows. It also provides the successful encoding with the delimiter.
  • Save inspect_ctis.py.
  • Check that Python can parse the current loader by running the project command in the integrated terminal from earlier:
python inspect_ctis.py sample_ctis_export.csv

What does this check prove?

The terminal returns to the prompt without a traceback. Python has parsed the import group with the complete loader definition.

Seeing a Python traceback?

  • Compare the indentation inside load_csv(path) with the supplied code.
  • Check that every opening parenthesis has a matching closing parenthesis.
  • Help me find the syntax problem in my loader.

The main() function connects the loader to the CSV path entered in the terminal. It formats the detected details as a numbered report.

  • Add the command-line entry point below load_csv(path) by pasting this code:
def main():
    if len(sys.argv) != 2:
        raise SystemExit("Usage: python inspect_ctis.py <csv-file>")

    path = Path(sys.argv[1])
    headers, rows, encoding, delimiter = load_csv(path)

    print(f"File: {path}")
    print(f"Encoding: {encoding}")
    print(f"Delimiter: {delimiter!r}")
    print(f"Data rows: {len(rows)}")
    print("Headers:")
    for number, header in enumerate(headers, start=1):
        print(f"  {number}. {header}")


if __name__ == "__main__":
    main()

How does the report work?

  • The argument check requires one CSV file path.
  • The Path object passes that file to load_csv(path).
  • The report prints the detected file details before the headers.
  • The loop numbers each original header from one.
  • The final guard runs main() when you execute the script directly.
  • Save inspect_ctis.py.
  • Confirm the final line in inspect_ctis.py calls main().

✔️ Awesome, I've got everything!

Good. Double-check that you saved inspect_ctis.py.

ⓧ I'd like to double check the full code

import csv
import sys
from pathlib import Path


def load_csv(path):
    if not path.exists():
        raise FileNotFoundError(f"CSV file not found: {path}")

    errors = []
    for encoding in ("utf-8-sig", "utf-16", "cp1252"):
        try:
            with path.open("r", encoding=encoding, newline="") as handle:
                sample = handle.read(4096)
                handle.seek(0)

                if not sample.strip():
                    raise ValueError("The CSV file is empty.")

                try:
                    dialect = csv.Sniffer().sniff(sample, delimiters=",;\t")
                except csv.Error:
                    dialect = csv.excel

                reader = csv.DictReader(handle, dialect=dialect)
                rows = list(reader)
                headers = reader.fieldnames or []

            if headers:
                return headers, rows, encoding, dialect.delimiter
        except (UnicodeError, csv.Error, ValueError) as error:
            errors.append(f"{encoding}: {error}")

    details = "; ".join(errors)
    raise RuntimeError(f"Could not read the CSV file. Attempts: {details}")


def main():
    if len(sys.argv) != 2:
        raise SystemExit("Usage: python inspect_ctis.py <csv-file>")

    path = Path(sys.argv[1])
    headers, rows, encoding, delimiter = load_csv(path)

    print(f"File: {path}")
    print(f"Encoding: {encoding}")
    print(f"Delimiter: {delimiter!r}")
    print(f"Data rows: {len(rows)}")
    print("Headers:")
    for number, header in enumerate(headers, start=1):
        print(f"  {number}. {header}")


if __name__ == "__main__":
    main()
Connect the practice file to the inspector

The completed command now loads the synthetic file. Its report makes the row count with the original headers visible before any analysis begins.

Before you run it, how many data rows and numbered headers do you expect the inspector to find?

  • Inspect the synthetic export by running this command in the integrated terminal:
python inspect_ctis.py sample_ctis_export.csv

What should you see?

  • The report names sample_ctis_export.csv as the file.
  • The report identifies the encoding with the comma delimiter.
  • The line Data rows: 4 confirms that all four practice trials loaded.
  • The numbered header list starts with 1. Trial number.
  • The numbered header list ends with 7. Condition.

That is your first working data check. The inspector can now reveal a CSV structure without assuming its headers.

Inspection result not matching?

  • Confirm the terminal is using the Documents\ctis-pivot workspace from earlier.
  • Compare the row count with the four synthetic records in sample_ctis_export.csv.
  • Check that the header line uses commas between all seven names.
  • Help me diagnose my CSV inspection result.

Your practice pipeline can identify an unfamiliar CSV before analysis starts. Next, you will apply the same inspector to a live CTIS export for your chosen condition.

Export and Inspect Live CTIS Results

Your synthetic export proved that the inspector can reveal a CSV file's structure. The live portal now supplies the real trial records for your chosen condition.

The Clinical Trials Information System (CTIS) can change the labels or order of fields in its current export. You will inspect the downloaded headers before the analyzer maps them.

In this step, get ready to:
  • Search the CTIS public portal for your selected condition.
  • Download the selected analysis fields as ctis_export.csv.
  • Verify the live row count and numbered headers with the existing inspector.
Search CTIS and choose analysis fields

Each CTIS result can show extra analysis fields through display options. The downloaded file carries those selected fields into your local workflow.

  • Switch back to the CTIS public portal tab in Microsoft Edge from earlier.
  • Enter the condition you selected in Step 0 into the public search interface.
  • Start the search with the interface's search control.
  • Review the returned trial records for relevance to your condition.

You'll see the current public trials that match your search terms.

  • Select display options for the results.
  • Include recruitment status when available.
  • Include trial phase when available.
  • Include sponsor information when offered.
  • Include any other field that supports your landscape question.

The results now show the available phase information. They also show the available status and sponsor information you selected.

Why choose fields before downloading?

The CSV export includes fields selected through display options. Your choices determine which columns are available to inspect.

The inspector learns the current header labels from the file itself. This keeps your workflow compatible with the live export.

Save the live export as ctis_export.csv

The export freezes your current search result set into a local file. A stable filename lets every later command target the same path.

  • Select Download results for the current search results.
  • Use Microsoft Edge's download controls to show the new .csv file in File Explorer.
  • Drag the downloaded .csv file into the Documents\ctis-pivot folder.
  • Select the moved .csv file.
  • Press F2 to edit its name.
  • Enter ctis_export.csv as the new filename.
  • Press Enter to save the filename.

File Explorer now shows ctis_export.csv inside Documents\ctis-pivot.

  • Switch back to Visual Studio Code from earlier.
  • Confirm ctis_export.csv appears beside sample_ctis_export.csv in the Explorer sidebar.

That is the live data bridge complete: your CTIS export now has the stable name the analyzer expects.

Can't find the downloaded export?

  • Use Microsoft Edge's download controls to show the downloaded file in File Explorer.
  • Confirm the downloaded filename ends in .csv.
  • Move the file into Documents\ctis-pivot before renaming it.

Help me locate and rename my CTIS export.

Inspect the live export

The live file's exact headers form its current schema. Numbered output lets you map the fields CTIS supplied without assuming undocumented column names.

Before you run the inspector, do you expect the live export to keep the same seven headers as the synthetic file?

  • Inspect ctis_export.csv with the existing script by running this command:
python inspect_ctis.py ctis_export.csv

How to read the inspection

  • The File: line ties the report to ctis_export.csv.
  • The Encoding: line shows which common text encoding succeeded.
  • The Delimiter: line shows how the current export separates columns.
  • The Data rows: line states the number of live trial records.
  • The Headers: section lists the current column names by number.
  • Find Data rows: in the terminal output.
  • Confirm the reported number is at least 1.
  • Read the numbered list beneath Headers:.
  • Confirm an available phase field is represented.
  • Confirm an available status field is represented.
  • Confirm sponsor information is represented when the portal offered it.
  • Confirm every other field needed for your landscape question is represented.

You have captured the live schema successfully: the terminal now shows at least one data row plus the exact headers your analyzer must use.

Rows or analysis fields missing?

  • Return to the CTIS portal tab from earlier when the row count is zero.
  • Revise the search until the portal returns at least one trial.
  • Select display options when an analysis field is absent.
  • Include the missing field when it is available.
  • Download the results again.
  • Replace ctis_export.csv in Documents\ctis-pivot with the new export.
  • Run the inspection command again.

Help me diagnose my live CTIS export.

Your live CTIS export is ready for analysis. Next, you will test what happens when multi-country cells are counted before normalization.

Expose Misleading Raw Counts

Your inspected Clinical Trials Information System (CTIS) export has real rows with current headers. You now know exactly which fields the live file contains.

A raw frequency count treats each complete country cell as one category. A cell containing several country codes becomes one misleading label.

In this step, get ready to:
  • Build a naive analyzer that counts unmodified country-cell values.
  • Run the analyzer against the synthetic export.
  • Expose the misleading categories created by multi-country cells.
Build the naive country counter

The analyzer reuses load_csv from your existing inspector. Python's Counter records how often each untouched cell value appears.

  • In the Visual Studio Code Explorer sidebar, click the New File icon beside ctis-pivot.
  • Enter naive_ctis_analyzer.py as the file name.

The empty naive_ctis_analyzer.py file opens beside your existing project files.

  • Add the imports plus the numbered header selector by pasting this code into naive_ctis_analyzer.py:
import sys
from collections import Counter
from pathlib import Path

from inspect_ctis import load_csv


def choose_header(headers):
    print("Available headers:")
    for number, header in enumerate(headers, start=1):
        print(f"  {number}. {header}")

    while True:
        answer = input("Enter the number of the country column: ").strip()
        if answer.isdigit():
            index = int(answer) - 1
            if 0 <= index < len(headers):
                return headers[index]
        print("Enter one of the displayed header numbers.")

What Does This Code Do?

  • The imports provide command-line arguments, frequency counting, file paths, plus your existing CSV loader.
  • The choose_header function displays every available header with a number.
  • The input loop accepts a number only when it points to a displayed header.
  • The function returns the exact header text from the CSV file.
  • Place your cursor on a new line below print("Enter one of the displayed header numbers.").
  • Add the program entry point plus the raw counting logic by pasting this code:


def main():
    if len(sys.argv) != 2:
        raise SystemExit("Usage: python naive_ctis_analyzer.py <csv-file>")

    path = Path(sys.argv[1])
    headers, rows, _, _ = load_csv(path)
    country_header = choose_header(headers)

    raw_counts = Counter(
        (row.get(country_header, "") or "").strip() or "(blank)"
        for row in rows
    )

    print("\nNaive country counts:")
    for value, count in raw_counts.most_common():
        print(f"  {value}: {count}")

    print("\nNotice that multi-country cells are counted as combined labels.")


if __name__ == "__main__":
    main()

How Does the Raw Count Work?

  • The main function requires one CSV file path from the command line.
  • The shared load_csv function returns the file's exact headers plus its rows.
  • The selected country value passes into Counter without being split.
  • The most_common method prints each complete cell value with its frequency.
  • Save naive_ctis_analyzer.py.
  • Confirm naive_ctis_analyzer.py appears beside inspect_ctis.py in the Explorer sidebar.

Can't Save the Analyzer?

  • Check that naive_ctis_analyzer.py is inside the open ctis-pivot folder.
  • Check that the file name ends with .py.

If the file still looks different from the reference, help me compare my naive analyzer with the required structure.

✔️ Awesome, I've got everything!

Great. Double-check that you saved naive_ctis_analyzer.py inside ctis-pivot.

ⓧ I'd like to double check the full code

import sys
from collections import Counter
from pathlib import Path

from inspect_ctis import load_csv


def choose_header(headers):
    print("Available headers:")
    for number, header in enumerate(headers, start=1):
        print(f"  {number}. {header}")

    while True:
        answer = input("Enter the number of the country column: ").strip()
        if answer.isdigit():
            index = int(answer) - 1
            if 0 <= index < len(headers):
                return headers[index]
        print("Enter one of the displayed header numbers.")


def main():
    if len(sys.argv) != 2:
        raise SystemExit("Usage: python naive_ctis_analyzer.py <csv-file>")

    path = Path(sys.argv[1])
    headers, rows, _, _ = load_csv(path)
    country_header = choose_header(headers)

    raw_counts = Counter(
        (row.get(country_header, "") or "").strip() or "(blank)"
        for row in rows
    )

    print("\nNaive country counts:")
    for value, count in raw_counts.most_common():
        print(f"  {value}: {count}")

    print("\nNotice that multi-country cells are counted as combined labels.")


if __name__ == "__main__":
    main()

What Should Match?

Use this full-file reference to compare the imports, both functions, plus the final main call with your saved file.

Run the misleading count

The synthetic export gives this counting method a controlled test. The analyzer asks for the country header after loading the file.

Before you run this, do you expect the result to represent individual countries or complete cell values?

  • Run the naive analyzer against the synthetic export by entering this command in the integrated terminal:
python naive_ctis_analyzer.py sample_ctis_export.csv

What Does This Command Do?

The command runs naive_ctis_analyzer.py with sample_ctis_export.csv as its input file. The program then displays seven numbered headers.

  • Type 6 at the country-column prompt.
  • Press Enter to generate the raw frequency count.

The terminal reports four data labels. Three of those labels preserve several country codes from a single CSV cell.

Germany appears across three synthetic records. The raw output hides two of those appearances inside combined labels.

You have caught the flaw at exactly the right moment. The script runs successfully while its untouched cell values create misleading categories.

Not Seeing the Combined Labels?

  • Confirm that you entered 6 for the country column in the synthetic file.
  • Confirm that the command uses sample_ctis_export.csv as the input file.
  • Compare the raw_counts expression with the full-file reference above.

If the command still produces different results, help me diagnose my naive CTIS country count.

Your naive analyzer has exposed the data-quality problem with evidence from the synthetic export. Next, you will normalize those multi-country values into reliable counts.

Build the Cleaned Dataset and Dashboard

Your naive analyzer proved that each multi-country cell becomes one misleading label. The live Clinical Trials Information System (CTIS) export also reflects the fields selected in the public portal.

You will map those live columns into a stable schema. Your Python analyzer will produce a cleaned CSV plus a standalone HTML dashboard.

In this step, get ready to:
  • Map the current export headers to stable internal field names.
  • Normalize country values before calculating frequencies.
  • Generate a cleaned dataset plus a local trial-landscape dashboard.
Map and normalize the live schema

Dynamic header mapping lets the analyzer work with the exact column labels in your current export. Normalization turns different multi-country separators into one predictable format.

  • In the Visual Studio Code file sidebar from earlier, select the ctis-pivot folder.
  • Use the new-file control in the sidebar toolbar.
  • Enter ctis_analyzer.py as the file name.
  • Confirm the file name to create it.
  • Add the standard-library imports plus the canonical field definitions by pasting this code into ctis_analyzer.py:
import csv
import sys
from collections import Counter
from html import escape
from pathlib import Path

from inspect_ctis import load_csv


FIELD_PROMPTS = [
    ("trial_number", "trial number"),
    ("title", "trial title"),
    ("sponsor", "sponsor or co-sponsor"),
    ("trial_phase", "trial phase"),
    ("overall_status", "overall trial or recruitment status"),
    ("countries", "countries or Member States concerned"),
    ("condition", "condition"),
]

CANONICAL_HEADERS = [field for field, _ in FIELD_PROMPTS]

What does this setup do?

  • The imports provide CSV writing, command-line arguments, frequency counting, HTML escaping, file paths, plus the existing load_csv() loader.
  • FIELD_PROMPTS pairs each stable field name with a readable mapping prompt.
  • CANONICAL_HEADERS preserves one consistent output order for every cleaned export.
  • Save ctis_analyzer.py.
  • Confirm the file remains listed beside inspect_ctis.py in the sidebar.
  • Add the numbered header display plus the interactive mapping function below CANONICAL_HEADERS by pasting this code:
def show_headers(headers):
    print("Available headers:")
    for number, header in enumerate(headers, start=1):
        print(f"  {number}. {header}")


def choose_columns(headers):
    show_headers(headers)
    mapping = {}

    for canonical_name, label in FIELD_PROMPTS:
        while True:
            answer = input(f"Header number for {label}: ").strip()
            if answer.isdigit():
                index = int(answer) - 1
                if 0 <= index < len(headers):
                    mapping[canonical_name] = headers[index]
                    break
            print("Enter one of the displayed header numbers.")

    return mapping

How does header mapping work?

  • show_headers() prints the live export labels with numbers.
  • choose_columns() asks for one source header per canonical field.
  • The bounds check accepts only a number displayed by the script.
  • The returned mapping connects current CTIS labels to stable internal names.
  • Save ctis_analyzer.py.
  • Confirm the two new function names appear below CANONICAL_HEADERS.
  • Add the text cleanup plus country normalization functions below choose_columns() by pasting this code:
def clean_text(value):
    return " ".join((value or "").split())


def split_values(value):
    normalized = value or ""
    for separator in ("\r\n", "\r", "\n", ";", "|"):
        normalized = normalized.replace(separator, "\n")
    return [clean_text(part) for part in normalized.split("\n") if clean_text(part)]


def canonicalize(rows, mapping):
    cleaned_rows = []

    for row in rows:
        cleaned = {}
        for canonical_name in CANONICAL_HEADERS:
            source_header = mapping[canonical_name]
            cleaned[canonical_name] = clean_text(row.get(source_header, ""))

        countries = split_values(cleaned["countries"])
        cleaned["countries"] = " | ".join(countries)
        cleaned_rows.append(cleaned)

    return cleaned_rows

How does normalization fix the counts?

  • clean_text() removes surrounding whitespace plus repeated internal spacing.
  • split_values() recognizes line breaks, semicolons, plus vertical bars as value boundaries.
  • canonicalize() copies each selected source column into its stable destination field.
  • Country values are rejoined with | so later counting can split them consistently.
  • Save ctis_analyzer.py.
  • Confirm canonicalize() ends by returning cleaned_rows.
  • Add the cleaned CSV writer plus the frequency helpers below canonicalize() by pasting this code:
def write_cleaned_csv(path, rows):
    with path.open("w", encoding="utf-8-sig", newline="") as handle:
        writer = csv.DictWriter(handle, fieldnames=CANONICAL_HEADERS)
        writer.writeheader()
        writer.writerows(rows)


def display_value(value):
    return value if value else "(blank)"


def count_single_values(rows, key):
    return Counter(display_value(row[key]) for row in rows)


def count_countries(rows):
    counts = Counter()
    for row in rows:
        countries = split_values(row["countries"])
        if countries:
            counts.update(countries)
        else:
            counts["(blank)"] += 1
    return counts

What do these helpers produce?

  • write_cleaned_csv() writes the stable header row before writing the cleaned trial rows.
  • display_value() gives empty cells a visible label in the dashboard.
  • count_single_values() handles fields that contain one category per trial.
  • count_countries() counts each normalized country separately.
  • Save ctis_analyzer.py.
  • Confirm count_countries() calls split_values() before updating its counter.

Seeing an editor warning?

Check that every function starts at the far-left edge of the file. Confirm that each indented function body uses the same spacing.

Compare the spelling of FIELD_PROMPTS plus CANONICAL_HEADERS with the earlier definitions. Help me check the mapping and normalization code.

Build the dashboard report

Frequency tables turn the cleaned rows into a landscape that you can scan. HTML escaping keeps source text from being interpreted as page markup.

  • Add the reusable frequency-table renderer below count_countries() by pasting this code:
def render_count_table(title, counter, limit=15):
    body = "".join(
        f"<tr><td>{escape(str(label))}</td><td>{count}</td></tr>"
        for label, count in counter.most_common(limit)
    )
    if not body:
        body = '<tr><td colspan="2">No values available</td></tr>'

    return f"""
    <section class="panel">
      <h2>{escape(title)}</h2>
      <table>
        <thead><tr><th>Value</th><th>Trials</th></tr></thead>
        <tbody>{body}</tbody>
      </table>
    </section>
    """

How is each ranking rendered?

  • most_common() ranks labels from the highest count to the lowest count.
  • escape() converts HTML-sensitive label characters into safe text.
  • The fallback row keeps an empty category table readable.
  • The returned section gives every ranking the same two-column structure.
  • Save ctis_analyzer.py.
  • Confirm the renderer limits each ranking to 15 rows by default.
  • Add the trial snapshot renderer below render_count_table() by pasting this code:
def render_trial_table(rows, limit=25):
    body = "".join(
        "<tr>"
        f"<td>{escape(row['trial_number'])}</td>"
        f"<td>{escape(row['title'])}</td>"
        f"<td>{escape(row['sponsor'])}</td>"
        f"<td>{escape(row['trial_phase'])}</td>"
        f"<td>{escape(row['overall_status'])}</td>"
        f"<td>{escape(row['countries'])}</td>"
        "</tr>"
        for row in rows[:limit]
    )
    if not body:
        body = '<tr><td colspan="6">No trials available</td></tr>'

    return f"""
    <section class="panel wide">
      <h2>Trial snapshot</h2>
      <table>
        <thead>
          <tr>
            <th>Trial number</th><th>Title</th><th>Sponsor</th>
            <th>Phase</th><th>Status</th><th>Countries</th>
          </tr>
        </thead>
        <tbody>{body}</tbody>
      </table>
    </section>
    """

What does the snapshot show?

  • The table displays up to 25 cleaned trial rows.
  • Each source value passes through escape() before entering the page.
  • The selected columns make each landscape category traceable to individual trials.
  • Save ctis_analyzer.py.
  • Confirm the snapshot includes trial number, title, sponsor, phase, status, plus countries.

The remaining code assembles the summary cards, styling, output paths, plus command flow. Use the complete reference to make every section of ctis_analyzer.py match before the live run.

✔️ Awesome, I've got everything!

Great. Double-check that you saved ctis_analyzer.py.

ⓧ I'd like to double check the full code

import csv
import sys
from collections import Counter
from html import escape
from pathlib import Path

from inspect_ctis import load_csv


FIELD_PROMPTS = [
    ("trial_number", "trial number"),
    ("title", "trial title"),
    ("sponsor", "sponsor or co-sponsor"),
    ("trial_phase", "trial phase"),
    ("overall_status", "overall trial or recruitment status"),
    ("countries", "countries or Member States concerned"),
    ("condition", "condition"),
]

CANONICAL_HEADERS = [field for field, _ in FIELD_PROMPTS]


def show_headers(headers):
    print("Available headers:")
    for number, header in enumerate(headers, start=1):
        print(f"  {number}. {header}")


def choose_columns(headers):
    show_headers(headers)
    mapping = {}

    for canonical_name, label in FIELD_PROMPTS:
        while True:
            answer = input(f"Header number for {label}: ").strip()
            if answer.isdigit():
                index = int(answer) - 1
                if 0 <= index < len(headers):
                    mapping[canonical_name] = headers[index]
                    break
            print("Enter one of the displayed header numbers.")

    return mapping


def clean_text(value):
    return " ".join((value or "").split())


def split_values(value):
    normalized = value or ""
    for separator in ("\r\n", "\r", "\n", ";", "|"):
        normalized = normalized.replace(separator, "\n")
    return [clean_text(part) for part in normalized.split("\n") if clean_text(part)]


def canonicalize(rows, mapping):
    cleaned_rows = []

    for row in rows:
        cleaned = {}
        for canonical_name in CANONICAL_HEADERS:
            source_header = mapping[canonical_name]
            cleaned[canonical_name] = clean_text(row.get(source_header, ""))

        countries = split_values(cleaned["countries"])
        cleaned["countries"] = " | ".join(countries)
        cleaned_rows.append(cleaned)

    return cleaned_rows


def write_cleaned_csv(path, rows):
    with path.open("w", encoding="utf-8-sig", newline="") as handle:
        writer = csv.DictWriter(handle, fieldnames=CANONICAL_HEADERS)
        writer.writeheader()
        writer.writerows(rows)


def display_value(value):
    return value if value else "(blank)"


def count_single_values(rows, key):
    return Counter(display_value(row[key]) for row in rows)


def count_countries(rows):
    counts = Counter()
    for row in rows:
        countries = split_values(row["countries"])
        if countries:
            counts.update(countries)
        else:
            counts["(blank)"] += 1
    return counts


def render_count_table(title, counter, limit=15):
    body = "".join(
        f"<tr><td>{escape(str(label))}</td><td>{count}</td></tr>"
        for label, count in counter.most_common(limit)
    )
    if not body:
        body = '<tr><td colspan="2">No values available</td></tr>'

    return f"""
    <section class="panel">
      <h2>{escape(title)}</h2>
      <table>
        <thead><tr><th>Value</th><th>Trials</th></tr></thead>
        <tbody>{body}</tbody>
      </table>
    </section>
    """


def render_trial_table(rows, limit=25):
    body = "".join(
        "<tr>"
        f"<td>{escape(row['trial_number'])}</td>"
        f"<td>{escape(row['title'])}</td>"
        f"<td>{escape(row['sponsor'])}</td>"
        f"<td>{escape(row['trial_phase'])}</td>"
        f"<td>{escape(row['overall_status'])}</td>"
        f"<td>{escape(row['countries'])}</td>"
        "</tr>"
        for row in rows[:limit]
    )
    if not body:
        body = '<tr><td colspan="6">No trials available</td></tr>'

    return f"""
    <section class="panel wide">
      <h2>Trial snapshot</h2>
      <table>
        <thead>
          <tr>
            <th>Trial number</th><th>Title</th><th>Sponsor</th>
            <th>Phase</th><th>Status</th><th>Countries</th>
          </tr>
        </thead>
        <tbody>{body}</tbody>
      </table>
    </section>
    """


def build_dashboard(rows, source_name):
    phase_counts = count_single_values(rows, "trial_phase")
    status_counts = count_single_values(rows, "overall_status")
    sponsor_counts = count_single_values(rows, "sponsor")
    country_counts = count_countries(rows)

    unique_sponsors = len([name for name in sponsor_counts if name != "(blank)"])
    unique_countries = len([name for name in country_counts if name != "(blank)"])

    panels = "".join(
        [
            render_count_table("Trial phase", phase_counts),
            render_count_table("Overall or recruitment status", status_counts),
            render_count_table("Countries", country_counts),
            render_count_table("Sponsors", sponsor_counts),
            render_trial_table(rows),
        ]
    )

    return f"""<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>CTIS Trial Landscape</title>
  <style>
    :root {{ color-scheme: light; font-family: Arial, sans-serif; }}
    body {{ margin: 0; background: #f4f7fb; color: #172033; }}
    header {{ background: #173b67; color: white; padding: 28px; }}
    header p {{ margin-bottom: 0; }}
    main {{ max-width: 1180px; margin: 0 auto; padding: 24px; }}
    .cards {{ display: grid; grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); gap: 16px; margin-bottom: 20px; }}
    .card, .panel {{ background: white; border-radius: 12px; padding: 18px; box-shadow: 0 4px 14px rgba(23, 59, 103, 0.08); }}
    .card strong {{ display: block; font-size: 2rem; color: #173b67; }}
    .grid {{ display: grid; grid-template-columns: repeat(2, minmax(0, 1fr)); gap: 20px; }}
    .wide {{ grid-column: 1 / -1; overflow-x: auto; }}
    table {{ width: 100%; border-collapse: collapse; }}
    th, td {{ text-align: left; border-bottom: 1px solid #dfe6ef; padding: 9px; vertical-align: top; }}
    th {{ color: #173b67; }}
    .note {{ margin-top: 20px; color: #4d5d73; }}
    @media (max-width: 760px) {{ .grid {{ grid-template-columns: 1fr; }} }}
  </style>
</head>
<body>
  <header>
    <h1>CTIS Trial Landscape</h1>
    <p>Source file: {escape(source_name)}</p>
  </header>
  <main>
    <section class="cards">
      <div class="card"><strong>{len(rows)}</strong>Total trial rows</div>
      <div class="card"><strong>{unique_sponsors}</strong>Named sponsors</div>
      <div class="card"><strong>{unique_countries}</strong>Countries</div>
      <div class="card"><strong>{len(status_counts)}</strong>Status labels</div>
    </section>
    <section class="grid">{panels}</section>
    <p class="note">This report summarizes public registry data. It does not assess treatment efficacy, safety, or trial quality.</p>
  </main>
</body>
</html>
"""


def main():
    if len(sys.argv) != 2:
        raise SystemExit("Usage: python ctis_analyzer.py <csv-file>")

    input_path = Path(sys.argv[1])
    headers, rows, encoding, delimiter = load_csv(input_path)

    print(f"Loaded {len(rows)} rows using {encoding} and delimiter {delimiter!r}.")
    mapping = choose_columns(headers)
    cleaned_rows = canonicalize(rows, mapping)

    cleaned_path = input_path.with_name(f"{input_path.stem}_cleaned.csv")
    dashboard_path = input_path.with_name(f"{input_path.stem}_dashboard.html")

    write_cleaned_csv(cleaned_path, cleaned_rows)
    dashboard = build_dashboard(cleaned_rows, input_path.name)
    dashboard_path.write_text(dashboard, encoding="utf-8")

    print(f"Created: {cleaned_path}")
    print(f"Created: {dashboard_path}")


if __name__ == "__main__":
    main()

How does the finished report work?

build_dashboard() calculates phase, status, sponsor, plus normalized country frequencies. It combines those rankings with the trial snapshot.

main() derives both output names from the source filename. One run can therefore process a refreshed export without changing the script.

Does your file differ from the reference?

Compare one function at a time from top to bottom. Pay close attention to indentation inside the multiline HTML string.

Confirm the final two lines call main() only when the file runs as a script. Help me compare the analyzer with the complete code.

Run and verify the live analysis

The analyzer displays the live headers before asking for seven choices. Each choice links one current CTIS column to a canonical output field.

  • Use the numbered header list from your live inspection to identify the trial-number column.
  • Identify the trial-title column.
  • Identify the sponsor or co-sponsor column.
  • Identify the trial-phase column.
  • Identify the overall trial or recruitment-status column.
  • Identify the countries or Member States concerned column.
  • Identify the condition column.

Before you run the analyzer, which two output files do you expect it to create from ctis_export.csv?

  • Run the live analysis in the integrated terminal from earlier by entering this command:
python ctis_analyzer.py ctis_export.csv

What does this command do?

Python runs ctis_analyzer.py with ctis_export.csv as its source file. The script reuses load_csv() to detect the live encoding plus delimiter.

  • Enter the displayed number for the trial-number header.
  • Enter the displayed number for the trial-title header.
  • Enter the displayed number for the sponsor or co-sponsor header.
  • Enter the displayed number for the trial-phase header.
  • Enter the displayed number for the overall trial or recruitment-status header.
  • Enter the displayed number for the countries or Member States concerned header.
  • Enter the displayed number for the condition header.

The terminal reports the loaded row count. It then confirms the creation of ctis_export_cleaned.csv plus ctis_export_dashboard.html.

  • Select ctis_export_cleaned.csv in the Visual Studio Code file sidebar.
  • Check the first row of the cleaned file.

You will see the canonical headers trial_number,title,sponsor,trial_phase,overall_status,countries,condition. Multi-country cells now use | between separate values.

Before you open the dashboard, do you expect a cell such as DE; FR; NL to remain one combined country category?

  • Return to Documents\ctis-pivot in File Explorer.
  • Switch back to the Microsoft Edge window from earlier.
  • Drag ctis_export_dashboard.html from File Explorer into Microsoft Edge.

You will see summary cards for trial rows, named sponsors, countries, plus status labels. Separate tables rank phase, status, country, plus sponsor values.

The trial snapshot shows the mapped records beneath those rankings. Each country now contributes its own count instead of remaining inside a combined label.

Dashboard not opening correctly?

Confirm that the terminal created both output files inside Documents\ctis-pivot. If the command rejected a choice, enter one of the header numbers displayed above the prompt.

If a table contains the wrong values, rerun the command. Choose the source header that matches each prompt. Help me diagnose my CTIS dashboard output.

You have turned a changing public export into a reusable trial-landscape workflow. Your cleaned data plus local dashboard now make the normalization fix visible.

Secret mission

Add a Recruitment-Status Watchlist

Filter your cleaned CTIS dataset with a status keyword from the dashboard. You will create a reusable watchlist that preserves every matching trial in a separate CSV file.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep, pause, or delete your local workspace. The project has no cloud resources or ongoing costs because its Python workflow runs locally.

Resources you used:

  • A local workspace at Documents\ctis-pivot containing inspect_ctis.py, naive_ctis_analyzer.py, ctis_analyzer.py, and build_watchlist.py.
  • Input CSV data from the synthetic sample plus the live Clinical Trials Information System (CTIS) export.
  • Generated CSV outputs in ctis_export_cleaned.csv and ctis_export_cleaned_watchlist.csv.
  • A standalone dashboard in ctis_export_dashboard.html.

Keep everything running

No action is needed. Choose this if you want to reuse the analyzer with refreshed trial exports.

  • Retain Documents\ctis-pivot in your Documents folder.
  • Use ctis_export_cleaned.csv as the canonical dataset for future status filters.
  • Replace ctis_export.csv with a fresh download when you want to rerun the landscape.
  • Open ctis_export_dashboard.html in Microsoft Edge when you want to present the landscape.

Pause - I'll come back to this later

Pausing closes the tools that use memory while preserving every file. The completed scripts leave no background server running.

  • Close the ctis_export_dashboard.html tab in Microsoft Edge.
  • Close Visual Studio Code.
  • Leave Documents\ctis-pivot in your Documents folder.

Your scripts, datasets, dashboard, and watchlist remain available for your next session.

Delete - I don't want to use this again

Remove all project resources when you want a completely fresh start.

Deletion is permanent. A separate copy protects any result you still need.

  • Copy any result you want to keep to a folder outside Documents\ctis-pivot.
  • Return to the Visual Studio Code terminal from earlier.
  • Remove the workspace and list the remaining Documents folders by running:
Set-Location "$HOME\Documents"
Remove-Item -LiteralPath "ctis-pivot" -Recurse -Force
Get-ChildItem

What does this cleanup do?

  • The first line moves the terminal to Documents so the workspace is no longer the active directory.
  • The second line permanently removes ctis-pivot with everything inside it.
  • The final line prints the remaining Documents entries for a visible check.

Your local cleanup is complete. The missing ctis-pivot entry confirms that every resource listed above is gone.

Still see the workspace?

  • Close any file from Documents\ctis-pivot that is still open in another app.
  • Return to the terminal from earlier.
  • Run the cleanup block again.

Help me diagnose why my Windows ctis-pivot folder is not being deleted.

Nice Work!

Nice Work!

You did it! You transformed a live Clinical Trials Information System (CTIS) export for your chosen condition into a reusable Python analysis workflow. Your cleaned CSV now feeds a standalone HTML dashboard that makes the trial landscape visible at a glance.

You've learned how to:

  • Inspect a live CTIS export with a schema-aware loader that reveals its exact file structure before analysis.
  • Expose misleading raw multi-country counts through a naive analysis. Fix the result through multi-value normalization.
  • Map live numbered headers into a canonical cleaned dataset in ctis_export_cleaned.csv. Generate a standalone trial-landscape dashboard in ctis_export_dashboard.html.
  • Secret Mission: Build a recruitment-status watchlist from ctis_export_cleaned.csv. Match each overall_status value case-insensitively.

Ready to quiz yourself?