# Conditions: pharma conditions

The pharma conditions commands, with each dataset's columns, units and source credit.

A disease or condition as the National Library of Medicine's MeSH vocabulary (Medical Subject Headings) names it, with the trials, sponsors, sites, drugs, safety events, publications and prevalence recorded for it.

Id: A MeSH descriptor id, the id the web pages use: D followed by six to nine digits (D001943). Every condition dataset takes this same key. Matching is exact, case included.

## Commands

| Command | Reads |
| --- | --- |
| pharma conditions list | Conditions |
| pharma conditions get <id> | One condition and the datasets reachable from it |
| pharma conditions get <id> company-programs | Company programs |
| pharma conditions get <id> condition-adverse-events | Condition adverse events |
| pharma conditions get <id> condition-burden | Condition burden |
| pharma conditions get <id> condition-delivery | Condition delivery |
| pharma conditions get <id> condition-drugs | Condition drugs |
| pharma conditions get <id> condition-prevalence | Condition prevalence |
| pharma conditions get <id> condition-publications | Condition publications |
| pharma conditions get <id> condition-sites | Condition sites |
| pharma conditions get <id> condition-trials | Condition trials |
| pharma conditions get <id> pdufa-dates | FDA decision dates (PDUFA) and catalysts |

Every command takes the paging, sorting and output flags described on the CLI page; the CLI prints the source credit with every result.

- [Pharma Observatory CLI](https://pharmaobservatory.com/cli)

## Conditions

**Command**

```sh
pharma conditions list
```

The conditions object type's primary dataset: one row per disease or condition in the MeSH vocabulary (Medical Subject Headings), with its category and trial counts. The trials, sponsors, sites, drugs, safety events, publications, prevalence and burden figures are their own datasets, reached by condition_id.

One row per: condition. Key columns: `condition_id`.

Source: US National Library of Medicine, Medical Subject Headings (MeSH) (Public domain); ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| condition_name | text |  | The condition's MeSH heading. |
| primary_category | text |  | The condition's primary MeSH disease category. |
| categories | text[] |  | The MeSH tree branches the condition sits under (C and F03 codes). |
| is_neoplasm | boolean |  | True when the condition sits under the MeSH neoplasms branch (C04). |
| tree_numbers | text[] |  | The condition's MeSH tree numbers. |
| n_trials | bigint | trials | Trials tagged with the condition or with a condition beneath it in the MeSH tree. |
| n_direct_trials | bigint | trials | Trials tagged with the condition itself. |

## Company programs

**Command**

```sh
pharma conditions get <id> company-programs
```

Development programs by sponsor, with the condition's global burden as a gated column.

One row per: lead sponsor, drug and indication (a development program). Key columns: `sponsor_org_id`, `drug_id`, `condition_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY); IHME Global Burden of Disease 2021 (CC BY-NC 4.0)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| sponsor_org_id | text |  | Legal-entity level: the lead sponsor's organization record. |
| sponsor_name | text |  | The sponsor's organization name. |
| sponsor_company_id | text |  | Company level: the sponsor's company (null when unresolved). |
| sponsor_company_name | text |  | Display name of the sponsor's company. |
| sponsor_group_id | text |  | Group level: the sponsor's current ultimate parent; a root's group is itself. |
| sponsor_group_name | text |  | Display name of the sponsor's group. |
| drug_id | text |  | The drug key. |
| drug_name | text |  | The drug's name. |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| condition_name | text |  | The condition's name. |
| max_phase | integer |  | Highest phase reached: 0 early phase 1 through 6 for phase 4 (marketed); -1 when unstated. |
| n_trials | bigint | trials | Lead-sponsored trials in the program. |
| n_active | bigint | trials | Of those, trials in an active status. |
| last_year | integer | calendar year | Latest trial start year. |
| withdrawn | boolean |  | True when the drug has been withdrawn. |
| lifecycle | text |  | approved, active or abandoned. |
| dalys_millions | double | millions of DALYs | Disability-adjusted life years (DALYs, millions) of the condition's global burden, from the Global Burden of Disease (GBD) 2021 study of the Institute for Health Metrics and Evaluation (IHME). Null for a caller without the licence for this column; see withheld_columns. |

## Condition adverse events

**Command**

```sh
pharma conditions get <id> condition-adverse-events
```

The serious adverse event terms reported most across a condition's trials, one row per condition and term.

One row per: condition and serious adverse event term. Key columns: `condition_id`, `adverse_event_term`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| adverse_event_term | text |  | The adverse event term as the trials report it. |
| subjects_affected | numeric | subjects | Subjects affected, summed across the condition's trials. |
| n_trials | bigint | trials | Trials for this condition that report the term. |

## Condition burden

**Command**

```sh
pharma conditions get <id> condition-burden
```

Global disease burden against trial activity, one row per condition with a burden figure, from the Global Burden of Disease study (GBD).

One row per: condition with a burden figure and at least one trial. Key columns: `condition_id`.

Source: IHME Global Burden of Disease 2021 (CC BY-NC 4.0); ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| condition_name | text |  | The condition's MeSH heading. |
| cause_name | text |  | The Global Burden of Disease cause matched to the condition. |
| dalys_millions | double | millions of DALYs | Global disability-adjusted life years for the matched cause, in millions. |
| deaths_millions | double | millions of deaths | Global deaths for the matched cause, in millions. |
| druggability | integer |  | Druggability class of the matched cause: 1 highly druggable, 2 druggable, 3 challenging, 4 very challenging. |
| n_trials | bigint | trials | Trials for the condition, counted under each condition they are tagged with and each condition above it in the MeSH tree. |
| trials_per_million_dalys | numeric | trials per million DALYs | Trials per million DALYs: n_trials divided by dalys_millions. |

## Condition delivery

**Command**

```sh
pharma conditions get <id> condition-delivery
```

One row per condition, with how its trials delivered against the plan first recorded for them (enrollment and completion).

One row per: condition. Key columns: `condition_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| condition_name | text |  | The condition's MeSH heading. |
| n_trials | bigint | trials | Trials in the roll-up, counted under each condition they are tagged with and each condition above it in the MeSH tree. |
| n_trials_with_observed_plan | bigint | trials | Trials whose first recorded plan is known. |
| n_trials_with_enrollment_delta | bigint | trials | Trials graded on enrollment. |
| n_trials_with_completion_delta | bigint | trials | Trials graded on completion date. |
| n_enrollment_met | bigint | trials | Graded trials that met their planned enrollment. |
| n_enrollment_missed | bigint | trials | Graded trials that missed their planned enrollment. |
| enrollment_hit_rate | numeric | share | Share of graded trials that met planned enrollment (0 to 1). |
| median_enrollment_attainment_pct | double | percent | Median enrollment attained as a percent of the plan. |
| n_on_time | bigint | trials | Graded trials that completed on or before the planned date. |
| n_slipped | bigint | trials | Graded trials that completed after the planned date. |
| on_time_rate | numeric | share | Share of graded trials that completed on time (0 to 1). |
| median_completion_slip_days | double | days | Median days between planned and actual primary completion, among graded trials; negative when earlier than planned. |

## Condition drugs

**Command**

```sh
pharma conditions get <id> condition-drugs
```

The drugs trialed most for a condition, one row per condition and resolved molecule.

One row per: condition and resolved molecule. Key columns: `condition_id`, `molecule_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| drug_id | text |  | The drug key (a canonical drug id; the same value as molecule_id on this dataset). |
| molecule_id | text |  | The molecule key the drug resolves to. |
| drug_name | text |  | The molecule's display name. |
| n_trials | bigint | trials | Trials for this condition that test the molecule. |

## Condition prevalence

**Command**

```sh
pharma conditions get <id> condition-prevalence
```

Prevalence and incidence figures for a condition, one row per figure and condition mapping, with the source, date and the population each figure is stated against.

One row per: prevalence figure and condition mapping. Key columns: `disease_prevalence_id`.

Source: Orphanet, Orphadata Science (INSERM) (CC BY 4.0)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| disease_prevalence_id | text |  | The figure's key. |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| condition_name | text |  | The condition's MeSH heading. |
| condition_mapping_relation | text |  | How the figure's disease maps to the condition: exact, narrower, broader, undetermined or unspecified. |
| disease_id | text |  | The rare disease key of the figure's disease (orpha:<code>). |
| disease_name | text |  | The disease's name in the source. |
| orpha_code | text |  | The Orphanet disease number. |
| prevalence_type | text |  | What the figure measures: point_prevalence, lifetime_prevalence, annual_incidence, prevalence_at_birth or cases_families. |
| prevalence_class | text |  | The source's prevalence band, as filed. |
| prevalence_lower_bound | numeric |  | Lower bound of the prevalence band as a ratio of the population (0.0001 is 1 in 10,000). |
| prevalence_upper_bound | numeric |  | Upper bound of the prevalence band as a ratio of the population (0.0005 is 5 in 10,000). |
| population_denominator | numeric |  | The population size the source states the band against (for example 10000 for a band per 10,000 persons). |
| population_unit | text |  | The unit of the population denominator. |
| qualification | text |  | The source's qualification of the figure (class_only, value_and_class and so on). |
| validation_status | text |  | The source's validation status for the figure (validated or not_yet_validated). |
| geography | text |  | The geography the figure describes, as the source states it. |
| jurisdiction_code | text |  | US, EU or GB where the geography is that jurisdiction; null otherwise. |
| observation_date | date |  | The date the source assigns to the observation. |
| source_snapshot_date | date |  | The date of the source release the figure was read from. |
| source | text |  | The publication reference the source cites for the figure. |
| source_url | text |  | The source release URL. |
| license_name | text |  | The figure's licence. |
| attribution | text |  | The credit line the licence requires. |

## Condition publications

**Command**

```sh
pharma conditions get <id> condition-publications
```

Publications tagged with a condition's MeSH (Medical Subject Headings) heading, one row per condition and PubMed id.

One row per: condition and publication. Key columns: `condition_id`, `pmid`.

Source: US National Library of Medicine, PubMed (Public domain)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| pmid | text |  | The PubMed id. |
| title | text |  | The article title. |
| pub_year | integer | year | Publication year. |
| journal_title | text |  | The journal title. |
| evidence_type | text |  | The article's PubMed publication type, one per article, picked by study design (Randomized Controlled Trial, Journal Article and so on). |
| is_major_topic | boolean |  | True when the condition is a major topic of the article. |
| n_linked_trials | bigint | trials | Registry trials linked to the article. |

## Condition sites

**Command**

```sh
pharma conditions get <id> condition-sites
```

The trial sites listed most often for a condition's trials, one row per condition and site.

One row per: condition and site. Key columns: `condition_id`, `site_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| site_id | text |  | The site key (a normalized facility, city and country). |
| site_name | text |  | The facility name as the registry lists it. |
| city | text |  | City as listed. |
| country | text |  | Country as listed. |
| n_trials | bigint | trials | Trials for this condition listed at the site. |

## Condition trials

**Command**

```sh
pharma conditions get <id> condition-trials
```

One row per condition and trial, with the trial's phase, status and dates and its lead sponsor, so group-by sponsor, phase or start year answers the condition landscape questions.

One row per: condition and trial. Key columns: `condition_id`, `trial_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| condition_id | text |  | The condition key, a MeSH (Medical Subject Headings) descriptor id (D followed by six to nine digits). |
| condition_name | text |  | The condition's MeSH heading. |
| trial_id | text |  | The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number). |
| is_direct | boolean |  | True when the trial is tagged with this condition itself; false when it is tagged with a condition beneath it in the MeSH tree. |
| trial_title | text |  | The trial's brief title. |
| study_type | text |  | The registry's study type (INTERVENTIONAL, OBSERVATIONAL or EXPANDED_ACCESS). |
| phase | text |  | The registry's phase code (PHASE1, PHASE2, PHASE2/PHASE3, NA and so on). Null when the registry gives no phase (observational and expanded-access trials); phase_label carries the display label for those. |
| phase_label | text |  | The trial phase as a display label (Phase 2, Phase 3, Observational, Not applicable, Expanded access, Unknown). The same trial's phase column holds the registry code. |
| overall_status | text |  | The registry's overall recruitment status (COMPLETED, RECRUITING, TERMINATED and so on). |
| start_date | date |  | Trial start date as registered. |
| start_year | integer | year | Calendar year of the start date. |
| primary_completion_date | date |  | Primary completion date as registered. |
| last_update_posted_date | date |  | The date the registry last posted an update to the trial record. |
| enrollment | integer | participants | Enrollment as registered. |
| were_results_reported | boolean |  | Whether results are posted on the registry. |
| sponsor_name | text |  | The lead sponsor's name as filed. |
| sponsor_org_id | text |  | Legal-entity level: the lead sponsor's organization record. |
| sponsor_company_id | text |  | Company level: the lead sponsor's company (null when the sponsor did not resolve to a company). |
| sponsor_company_name | text |  | The lead sponsor's company name. |
| sponsor_group_id | text |  | Group level: the current ultimate parent of the lead sponsor's company; dated links are in company_members. |
| sponsor_group_name | text |  | Display name of the lead sponsor's group. |
| sponsor_agency_class | text |  | The funder class the registry declared for the sponsor (INDUSTRY, NIH, FED, OTHER_GOV, NETWORK, OTHER, INDIV, AMBIG, UNKNOWN). UNKNOWN is the registry's own code for a class it does not state. Null when the trial has no lead sponsor row. |

## FDA decision dates (PDUFA) and catalysts

**Command**

```sh
pharma conditions get <id> pdufa-dates
```

Decision dates and catalysts of the US Food and Drug Administration (FDA), entered from a company, a legal entity, a drug or a condition.

One row per: calendar entry. Key columns: `pdufa_date_id`.

Source: Benzinga FDA / PDUFA calendar (Licensed data; not for redistribution); US SEC EDGAR

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| pdufa_date_id | text |  | The calendar entry id. |
| source | text |  | Which lane the row came from: benzinga, sec_extraction or both. |
| restriction_tier | text |  | none for a row from SEC filings; licensable for a row from the Benzinga calendar (the filings are those made to the US Securities and Exchange Commission). |
| decision_date | date |  | The decision date of the US Food and Drug Administration (FDA); a PDUFA date is the goal date under the Prescription Drug User Fee Act. |
| date_start | date |  | Start of the date interval the source states. |
| date_end | date |  | End of the date interval the source states. |
| date_precision | text |  | Precision of the date (day, month, quarter). |
| date_phrase | text |  | The date as the source words it. |
| event_type | text |  | Event type. |
| event_category | text |  | Event category (pdufa, adcomm and so on). |
| drug_name | text |  | The drug name as filed. |
| drug_id | text |  | The drug key. |
| molecule_id | text |  | The molecule key. |
| sponsor_name | text |  | The sponsor name as filed. |
| sponsor_org_id | text |  | Legal-entity level: the organization record the sponsor resolved to (null when unresolved). |
| sponsor_company_id | text |  | Company level: the company the sponsor belongs to (null when unresolved). |
| sponsor_company_name | text |  | Display name of the sponsor's company. |
| sponsor_group_id | text |  | Group level: the sponsor's current ultimate parent company; a root company's group is itself. |
| sponsor_group_name | text |  | Display name of the sponsor's group. |
| ticker | text |  | Exchange ticker. |
| condition_id | text |  | The condition key. |
| condition_name | text |  | The condition's name. |
| indication_text | text |  | The indication as the source words it. |
| source_type | text |  | Kind of source document. |
| source_link | text |  | Link to the source document. |