Scraping the Clinical Trials Information System
Scraping the Clincal Trials Information System
The Clinical Trials Information System (CTIS) serves as the mandatory centralized repository for clinical trial data in the European Union. According to the EU Clinical Trials Regulation (EU CTR) No. 536/2014, sponsors (people or organizations responsible for planning, funding, and overseeing trials) conducting trials within the EU or European Economic Area (EEA) must upload comprehensive trial information to CTIS. This includes details about the trial’s objectives, design, participant criteria, trial sites involved, safety events and eventual outcomes. This mandatory reporting ensures that CTIS contains a rich and reliable dataset covering a wide range of therapeutic areas and study phases, making it a valuable resource for scientific, clinical, and public health stakeholders.
CTIS itself already makes this data publically available through a web interface (Link). However, there are limits to what you can do with this data in terms of analysis other than pure reading. This is where web scraping comes into play.
A web scraper that systematically extracts and organizes this data can be instrumental in making insights more accessible and actionable, allowing you to track research trends, assess evidence across multiple studies, and support timely, data-informed decisions.
First step: Finding a way to extract the website's data
Web scraping can be done in various ways, depending on the structure of the website, the data format, and the complexity of the scraping task. I usually use one of the ways listed below:
- HTML Parsing: Using a combination of Python libraries like requests and BeautifulSoup, this method involves requesting and parsing the HTML of a web page to locate and extract the desired data. This is especially useful for websites with predictable HTML structures, allowing for easy extraction of data from tags, classes, or IDs. However, this method often fails for complex websites with a lot of JavaScript that renders HTML on the go or websites with extended anti bot protection.
- Browser Automation: With tools like Selenium and Puppeteer you can automate a web browser to navigate through pages, interact with elements, and extract data. This method is helpful for scraping data from dynamic websites (often using JavaScript for content) that load content asynchronously or require user interaction (i.e. logins).
- Using APIs: With a little detective work, the API endpoints that websites use to retreive their data from can often be identified. We can make use of these endpoints to extract data ourselves. Compared to HTML parsing and browser automation, this method is more robust, as changes to the website have no impact on our code as long as the API is not changed.
As using a website's API is usually the most reliable way of scraping a websites data, I started my browser's developer console and observed the network traffic while clicking through the trial search. To identify API endpoints we can possibly use to extract data, it makes sense to search the Network Monitor list for network requests that have a JSON response. Doing so I managed to find two endpoints that provide us with the data we need:
1. Overview of all trials
Sending a POST request to https://euclinicaltrials.eu/ctis-public-api/search returns an overview of all trials currently entered in CTIS. Using the following payload the response can be filtered to only include a subset of trials:
{
"pagination": {
"page": 1,
"size": 20
},
"sort": {
"property": "decisionDate",
"direction": "DESC"
},
"searchCriteria": {
"containAll": null,
"containAny": null,
"containNot": null,
"title": null,
"number": null,
"status": null,
"medicalCondition": null,
"sponsor": null,
"endPoint": null,
"productName": null,
"productRole": null,
"populationType": null,
"orphanDesignation": null,
"msc": null,
"ageGroupCode": null,
"therapeuticAreaCode": null,
"trialPhaseCode": null,
"sponsorTypeCode": null,
"gender": null,
"protocolCode": null,
"rareDisease": null,
"pip": null,
"haveOrphanDesignation": null,
"hasStudyResults": null,
"hasClinicalStudyReport": null,
"isLowIntervention": null,
"hasSeriousBreach": null,
"hasUnexpectedEvent": null,
"hasUrgentSafetyMeasure": null,
"isTransitioned": null,
"eudraCtCode": null,
"trialRegion": null,
"vulnerablePopulation": null,
"mscStatus": null
}
}
After successfully sending the POST request you'll receive a response looking like this:
{
"showWarning": false,
"pagination": {
"totalRecords": 6929,
"currentPage": 1,
"totalPages": 347,
"nextPage": true,
"prevPage": false
},
"data": [
{
"ctNumber": "2024-511897-64-00",
"ctStatus": 2,
"ctTitle": "CA-CIBLE : Impact of increasing the ionized serum calcium target post-filter on the effectiveness of regional citrate anticoagulation during continuous extra-renal purification in intensive care: multicenter randomized controlled non-inferiority study.",
"shortTitle": "APHP220257",
"conditions": "Adult patients hospitalized in intensive care with an indication of EER with ARC during the stay.",
"trialCountries": [
"France:2"
],
"decisionDateOverall": "25/10/2024",
"decisionDate": "FR: 25/10/2024",
"therapeuticAreas": [
"Analytical, Diagnostic and Therapeutic Techniques and Equipment [E] - Therapeutics [E02]"
],
"sponsor": "Assistance Publique Hopitaux De Paris",
"sponsorType": "Hospital/Clinic/Other health care facility",
"trialPhase": "Phase III and phase IV (Integrated)",
"endPoint": "The lifespan of each hemofilter measured in hours. The cause of the cessation of the EER will be informed among 6 previously defined methods: • Filter thrombosis • Catheter dysfunction: inlet pressure too negative (less than -150mmHg) or outlet pressure too positive (greater than +150mmHg) • Transport: restitution for patient mobilization • Futility: no indication to continue the EER • End of session: EER for more than 72 hours • Other, Post-filter Ca2+ (in mmol/l) measured during the EER session, Citrate infusion rates (PPS) in mmol/l, The incidence of suspected citrate accumulation syndrome defined by a hypocalcemia associated with a total calcium/ionized calcium ratio greater than 2.5, The occurrence of the following 5 metabolic complications: • Hypocalcemia [Ca2+ < 0.95mmol/l] • Metabolic acidosis [PH < 7.3 and HCO3- < 20mmol/l] • Metabolic alkalosis [PH > 7.5 and HCO3- > 30mmol/l] • Hypernatraemia [Na+ > 145mmol/l] • Hypomagnesemia [Mg2+ < 0.70mmol/l], The costs (in euros) induced by each hemofilter change and the quantities citrate infused, The impossibility of restoring the EER circuit at the end of the EER session (equivalent 200mL of blood)",
"product": "Regiocit solution pour hémofiltration",
"ageRangeSecondary": [
""
],
"ageGroup": "65+ years, 18-64 years",
"gender": "Female, Male",
"trialRegion": 1,
"totalNumberEnrolled": "412",
"primaryEndPoint": "The primary endpoint is the incidence of filter thrombosis (in %).",
"resultsFirstReceived": "No",
"lastUpdated": "25/10/2024",
"lastPublicationUpdate": "26/10/2024"
},
{
"ctNumber": "2024-518143-38-00",
[...]
This data already gives us a good overview of the trials entered in CTIS. However, to get a more detailed look at trials, we need to use the second endpoint.
2. Trial details
The second API endpoint identified returns details for a single trial when sending a GET request (https://euclinicaltrials.eu/ctis-public-api/retrieve/2024-518143-38-00). The trial can be specified by modifying the EUCT Code (a unique trial identifier) at the end of the url. A response will look like this:
{
"ctNumber": "2024-518143-38-00",
"ctStatus": "Authorised",
"decisionDate": "2024-10-25T18:25:35.308",
"publishDate": "2024-10-26T03:05:51.645609397",
"ctPublicStatusCode": 2,
"authorizedApplication": {
"authorizedPartI": {
"id": 59071,
"rowSubjectCount": 0,
"products": [
{
"id": 240084,
"part1MpRoleTypeCode": "3",
"productDictionaryInfo": {
"euMpNumber": "SUB13910MIG",
"marketingAuthNumber": "-",
"prodAuthStatus": 2,
"prodName": "FOLINIC ACID",
"pharmForm": "SOLUTION FOR INJECTION/INFUSION",
"activeSubstanceName": "FOLINIC ACID",
"productSubstances": [
{
"substancePk": "152102",
"actSubstOrigin": "Chemical",
"actSubstName": "FOLINIC ACID",
"substanceEvCode": "SUB13910MIG"
}
],
"atcCode": "-",
"atcName": "-",
"atcTermLevel": "-",
"activeSubstanceOtherDescriptiveName": ""
},
"isPaediatricFormulation": false,
"mpRoleInTrial": "1",
"orphanDrugEdit": false,
"doseUom": "mg/m2 milligram(s)/sq. meter",
"maxDailyDoseAmount": "400",
"doseUomTotal": "mg/m2 milligram(s)/sq. meter",
"maxTotalDoseAmount": "800",
"productChangedRelationMA": false,
"maxTreatmentPeriod": 12,
"timeUnitCode": "3",
"otherMedicinalProduct": "metabolite",
"scientificProductEvCode": "SUB13910MIG",
"devices": [],
"characteristics": [
"11"
],
"routes": [
"INTRAVENOUS INFUSION"
],
"allSubstancesChemicals": false,
"productName": "FOLINIC ACID",
"jsonActiveSubstanceNames": "folinic acid"
},
{
"id": 240082,
"part1MpRoleTypeCode": "1",
"productDictionaryInfo": {
"productPk": "11440371",
"productPharmForm": "CONCENTRATE FOR DISPERSION FOR INFUSION",
"euMpNumber": "PRD6811022",
"marketingAuthNumber": "EU/1/16/1130/001",
"prodAuthStatus": 2,
"prodName": "Onivyde pegylated liposomal 4.3 mg/ml concentrate for dispersion for infusion",
"pharmForm": "CONCENTRATE FOR DISPERSION FOR INFUSION",
"activeSubstanceName": "IRINOTECAN",
"authorisationCountryCode": "EU",
"mrpNumber": "EMEA/H/C/004125",
"nameOrg": "LES LABORATOIRES SERVIER (SURESNES)",
"productSubstances": [
{
"productPk": "11440371",
"substancePk": "352073",
"nameOrg": "LES LABORATOIRES SERVIER (SURESNES)",
"substanceOrigin": "Chemical",
"actSubstOrigin": "Chemical",
"actSubstName": "IRINOTECAN",
"substanceEvCode": "SUB08295MIG"
}
],
"atcCode": "L01XX19",
"atcName": "IRINOTECAN",
"atcTermLevel": "5",
"activeSubstanceOtherDescriptiveName": ""
},
"isPaediatricFormulation": false,
"mpRoleInTrial": "1",
"orphanDrugEdit": false,
"doseUom": "mg/m2 milligram(s)/sq. meter",
"maxDailyDoseAmount": "70",
[...]
I have shortened this response for better readability.
Extracting data
Now that we have found a way to get an overview of all the trials in CTIS and their corresponding details, we can use the overview response to get all the EUCT identifiers and then use these identifiers to extract details via the second endpoint.
Fetching EUCT identifiers
I designed the get_trial_overview function to collect trial overview data including the EUCT identifiers from the overview endpoint. It handles pagination to ensure it retrieves every available record. It then leverages Python’s generator capabilities to yield each TrialOverview instance one at a time, enabling memory-efficient processing even with large datasets. Each record is wrapped in a TrialOverview dataclass, offering structured access to specific data fields from the API response.
def get_trial_overview() -> Iterator[TrialOverview]:
next_page_available = True
page = 1
payload = OVERVIEW_PAYLOAD
while next_page_available:
payload["pagination"]["page"] = page
r = requests.post(
OVERVIEW_URL, headers=OVERVIEW_HEADERS, data=json.dumps(payload)
)
json_data = validate_response(r)
for trial in json_data["data"]:
trial_overview = from_dict(
data=trial,
data_class=TrialOverview,
config=Config(check_types=False, strict=True),
)
yield trial_overview
page += 1
next_page_available = json_data["pagination"]["nextPage"]
Fetching trial details
The get_full_trial function is designed to retrieve comprehensive details of a specific trial by making a request to the details endpoint mentioned above. It uses the unique EUCT identifier (ct_number), to send trial specific requests, then parses the received JSON data into a structured FullTrial dataclass.
def get_full_trial(ct_number: str) -> FullTrial:
full_trial_url = f"https://euclinicaltrials.eu/ctis-public-api/retrieve/{ct_number}"
r = requests.get(full_trial_url)
json_data = validate_response(r)
full_trial = from_dict(
FullTrial,
json_data,
config=Config(check_types=False, strict=False),
)
return full_trial
Putting the pieces together
By combining both functions we can iterate over each trial returned by the get_trial_overview and extract the corresponding full trial details using get_full_trial:
for trial_overview in get_trial_overview():
full_trial = get_full_trial(trial_overview.ctNumber)
Parsing data
In order to be able to process the data efficiently, I have created a set of nested dataclasses that reflect the structure of the JSON responses retrieved from the api endpoints. Using dataclasses offers several advantages over the use of dictionaries, for example:
- Type Safety and Structure: Dataclasses enforce specific types and structure, reducing errors and making data access more predictable than with dynamic dictionaries.
- Readability and Self-Documentation: With attribute definitions directly in the class, dataclasses provide a clear, self-documenting schema, which is easier to understand and maintain than nested or ambiguous dictionary keys.
- Simplified Refactoring: Changes to the data model only require updating the dataclass, automatically applying updates across the codebase without needing to change every dictionary instance.
- IDE and Code Completion Support: Dataclasses benefit from IDE features like type hints and code completion, enabling quicker development with fewer errors compared to using generic dictionaries.
Here's an example of the FullTrial dataclass the get_full_trial function uses to parse JSON responses:
@dataclass
class FullTrial:
ctNumber: str
ctStatus: str
startDateEU: Optional[str]
endDateEU: Optional[str]
decisionDate: str
publishDate: str
ctPublicStatusCode: int
authorizedApplication: AuthorizedApplication
events: Events
trialRegion: Optional[str]
trialRegionCode: Optional[int]
results: dict = field(default_factory=dict)
documents: List[Document] = field(default_factory=list)
correctiveMeasures: List = field(default_factory=list)
As you can see several fields of this dataclass contain other dataclasses, indicating a nested structure. I created a total of 94 dataclasses to fully map the JSON responses.
Persisting data
In order to persist the extracted data, I have created various functions to transfer the data from the dataclasses to an SQL database. I decided to use Python's SQLAlchemy for this as this library offered me several advantages:
- SQLAlchemy’s Object-Relational Mapping (ORM) allowed me to define database tables as Python classes, making it easy to work with database records as objects instead of manually writing SQL queries.
- SQLAlchemy supports multiple databases (e.g., SQLite, PostgreSQL, MySQL), so it’s easy to switch database backends without changing much code, which is why is was able to use a SQlite database for development and switch to PostgreSQL for production.
- With session management and batch operations, SQLAlchemy provides efficient ways to insert, update, or delete large datasets, which is ideal for handling potentially large volumes of scraped data.
For the first step i had to define several database classes:
class Base(DeclarativeBase):
pass
class Trial(Base):
__tablename__ = "trial"
id: Mapped[int] = mapped_column(Integer, primary_key=True, nullable=False)
title: Mapped[Optional[str]] = mapped_column(String, index=True)
short_title: Mapped[Optional[str]]
ct_number: Mapped[str] = mapped_column(
String, nullable=False, unique=True, index=True
)
is_transitioned: Mapped[Optional[bool]]
eudract_number: Mapped[Optional[str]]
nct_number: Mapped[Optional[str]]
status: Mapped[Optional[str]]
public_status_code: Mapped[Optional[str]]
phase: Mapped[str]
age_group: Mapped[Optional[str]]
gender: Mapped[Optional[str]]
trial_region: Mapped[Optional[str]]
estimated_recruitment_start_date: Mapped[Optional[date]]
decision_date: Mapped[Optional[date]]
estimated_end_date: Mapped[Optional[date]]
start_date_eu: Mapped[Optional[date]]
end_date_eu: Mapped[Optional[date]]
estimated_recruitment: Mapped[Optional[int]]
last_updated_in_ctis: Mapped[date]
ctis_url: Mapped[str]
# n:1
serious_breaches: Mapped[List["SeriousBreach"]] = relationship(
back_populates="trial", cascade="all, delete"
)
# m:n
sponsors: Mapped[List["Sponsor"]] = relationship(
secondary=trialsponsor, back_populates="trials", cascade="all, delete"
)
third_parties: Mapped[List["ThirdParty"]] = relationship(
secondary=trialthirdparty, back_populates="trials", cascade="all, delete"
)
conditions: Mapped[List["Condition"]] = relationship(
secondary=trialcondition, back_populates="trials", cascade="all, delete"
)
sites: Mapped[List["Site"]] = relationship(
secondary=trialsite, back_populates="trials", cascade="all, delete"
)
products: Mapped[List["Product"]] = relationship(
secondary=trialproduct, back_populates="trials", cascade="all, delete"
)
therapeutic_areas: Mapped[List["TherapeuticArea"]] = relationship(
secondary=trialtherapeuticarea,
back_populates="trials",
cascade="all, delete",
)
[...]
After creating all defined tables (Base.metadata.create_all(engine)) I inserted data using SQLAlchemy's ORM. The function below takes the dataclasses returned by get_trial_overview and get_full_trial as well as a database session and inserts the provided data into the respective database tables.
def insert_trial_data(session: Session, trial_overview: TrialOverview, full_trial: FullTrial) -> None:
[...]
trial = Trial(
title=trial_title,
short_title=trial_short_title,
ct_number=trial_ct_number,
is_transitioned=is_trial_transitioned,
eudract_number=trial_eudract,
nct_number=trial_nct,
status=trial_status,
public_status_code=trial_public_status_code,
phase=trial_phase,
age_group=trial_age_group,
gender=trial_gender,
trial_region=trial_region,
estimated_recruitment_start_date=trial_recruitment_start_date,
decision_date=trial_decision_date,
estimated_end_date=trial_estimated_end_date,
start_date_eu=trial_start_date_eu,
end_date_eu=trial_end_date_eu,
estimated_recruitment=trial_estimated_recruitment,
last_updated_in_ctis=trial_last_update,
ctis_url=f"https://euclinicaltrials.eu/search-for-clinical-trials/?lang=en&EUCT={trial_overview.ctNumber}",
)
session.add(trial)
[...]
sponsors = full_trial.authorizedApplication.authorizedPartI.sponsors
for sponsor in sponsors:
sponsor_row = get_or_create(
session,
Sponsor,
defaults={
"name": sponsor.organisation.name,
"type": sponsor.organisation.type,
},
is_primary=sponsor.primary,
org_id=sponsor.organisation.businessKey,
)
if sponsor_row not in trial.sponsors:
trial.sponsors.append(sponsor_row)
[...]
In order to be able to work better with the database afterwards, I have tried to simplify the database design in comparison to the JSON schema we got from the API response. For this reason some datapoints you find in the JSON response might be missing in the database.
Wrapping Up
This project demonstrates how Python can be a powerful tool for web scraping, data extraction, and organization, especially when working with structured APIs. By automating the retrieval of clinical trial data from CTIS, I created a robust workflow that processes and stores a complex dataset.
Through this journey, I sharpened my skills in navigating API structures, designing efficient data parsers, and applying SQLAlchemy to manage the dataset seamlessly. It’s a practical approach to handling information that might otherwise be challenging to access and analyze through a standard web interface.
If you’re considering building a similar scraper, my advice is to begin with a clear understanding of the API or website structure you’re targeting, test your code extensively, and use efficient database tools to handle the data volume.
Feel free to explore the code in my GitHub repository and try implementing your own improvements!