alethiotx.artemis.chembl

 85def molecules(version: str = '36', top_n_activities: int = 1):
 86    """
 87    Query ChEMBL database for bioactive drug molecules with clinical trial data and therapeutic indications.
 88    
 89    This function retrieves comprehensive drug-target-indication relationships from ChEMBL, automatically
 90    normalizing all molecular forms (salts, formulations, etc.) to their parent compounds. It integrates
 91    clinical trial phases, MeSH disease classifications, drug mechanisms, and target information to create
 92    a unified dataset for drug target prioritization and discovery.
 93    
 94    **Data Processing Workflow:**
 95    
 96    1. **Parent Normalization**: All child molecules (salts, prodrugs, formulations) are mapped to their
 97       parent compound using ChEMBL's molecule hierarchy
 98    2. **Indication Aggregation**: Drug indications from both parent and all child molecules are combined
 99    3. **Target Assignment**: Molecular targets are identified using a three-tier hierarchy:
100       
101    - Primary: Parent molecule's ``DRUG_MECHANISM`` table entries (known mechanisms)
102    - Secondary: Child molecule mechanisms (inherited when parent lacks mechanisms)
103    - Tertiary: Top N most-studied targets from ``ACTIVITIES`` table (bioassay data)
104    
105    4. **Clinical Trial Mapping**: Links molecules to ClinicalTrials.gov identifiers with phase information
106    5. **Year Inference**: Estimates trial registration year from NCT identifiers
107    
108    **Key Features:**
109    
110    - Only includes molecules with clinical trial references (ClinicalTrials.gov)
111    - Filters to human targets only (``Homo sapiens``)
112    - One molecule-indication-target per row (exploded format for multi-trial drugs)
113    - Mechanisms apply to all indications of a molecule (not indication-specific)
114    
115    :param version: ChEMBL database version to query. Version 36 covers data through 2024.
116                    See https://www.ebi.ac.uk/chembl/ for available versions.
117    :type version: str, optional
118    :param top_n_activities: For molecules without documented mechanisms (no ``DRUG_MECHANISM`` entries),
119                             include the top N most-studied targets from bioassay data. Set to 0 to
120                             exclude activity-based targets entirely. Defaults to 1 (most-studied target only).
121    :type top_n_activities: int, optional
122    
123    :return: DataFrame with one row per parent-molecule-indication-target combination. 
124    
125    **Columns:**
126    
127    **Molecule Information:**
128    
129    - ``chembl_id`` (str): ChEMBL identifier for the parent molecule (e.g., 'CHEMBL25')
130    - ``pref_name`` (str): Preferred drug name (e.g., 'ASPIRIN')
131    - ``parent_molregno`` (int): Internal ChEMBL registry number for parent molecule
132    
133    **Indication Information:**
134    
135    - ``mesh_heading`` (str): MeSH disease term (e.g., 'Lung Neoplasms')
136    - ``mesh_id`` (str): MeSH unique identifier (e.g., 'D008175')
137    - ``phase`` (int): Maximum clinical trial phase for this indication (0-4, where 4=approved)
138    - ``reference_type`` (str): Always 'ClinicalTrials' (pre-filtered)
139    - ``clinical_trial_id`` (str): ClinicalTrials.gov NCT identifier (e.g., 'NCT00123456')
140    - ``trial_year`` (int, nullable): Inferred trial registration year via :func:`infer_nct_year`
141    
142    **Target Information:**
143    
144    - ``target_chembl_id`` (str): ChEMBL target identifier (e.g., 'CHEMBL240')
145    - ``target_organism`` (str): Always 'Homo sapiens' (pre-filtered)
146    - ``target_type`` (str): Target classification (e.g., 'SINGLE PROTEIN', 'PROTEIN COMPLEX')
147    - ``target_uniprot_id`` (str, nullable): UniProt accession (e.g., 'P35354')
148    - ``target_gene_name`` (str, nullable): HGNC gene symbol (e.g., 'EGFR')
149    - ``mechanism_of_action`` (str, nullable): Mechanism description (NULL for activity-derived targets)
150    - ``action_type`` (str, nullable): Drug action type (e.g., 'INHIBITOR', 'AGONIST'; NULL for activities)
151    - ``target_source`` (str): Data provenance - one of:
152        
153        - ``DRUG_MECHANISM``: From parent's mechanism table (highest confidence)
154        - ``DRUG_MECHANISM_CHILD``: Inherited from child molecule's mechanism
155        - ``ACTIVITIES``: Derived from bioassay activity data (lower confidence)
156    
157    :rtype: pandas.DataFrame
158    
159    **Examples**
160    
161    Basic usage - retrieve all molecules from ChEMBL v36::
162    
163    >>> from alethiotx.artemis.chembl import molecules
164    >>> df = molecules(version='36', top_n_activities=1)
165    >>> print(f"{len(df)} records, {df['chembl_id'].nunique()} unique molecules")
166    >>> print(df[['chembl_id', 'pref_name', 'mesh_heading', 'target_gene_name']].head())
167    
168    Filter to specific disease and approved drugs only::
169    
170    >>> df = molecules(version='36')
171    >>> lung_cancer = df[df['mesh_heading'] == 'Lung Neoplasms']
172    >>> approved = lung_cancer[lung_cancer['phase'] == 4]
173    >>> print(approved[['pref_name', 'target_gene_name']].drop_duplicates())
174    
175    Exclude activity-based targets (mechanism data only)::
176    
177    >>> df = molecules(version='36', top_n_activities=0)
178    >>> print(f"Mechanisms only: {df['target_source'].value_counts()}")
179    
180    Analyze recent trials (last 6 years)::
181    
182    >>> from datetime import datetime
183    >>> df = molecules(version='36')
184    >>> current_year = datetime.now().year
185    >>> recent = df[df['trial_year'] >= current_year - 6]
186    >>> print(f"Recent trials: {len(recent)} records")
187    
188    .. note::
189        **Parent-Child Normalization**: All molecular forms (salts like 'aspirin sodium',
190        formulations like 'aspirin tablet') are normalized to their parent compound ('aspirin').
191        This ensures consistent target mapping and prevents double-counting.
192    
193    .. note::
194        **Mechanism-Indication Independence**: A molecule's targets are the same across all its
195        indications. For example, if aspirin targets COX1/COX2, these targets apply whether the
196        indication is 'Pain' or 'Cardiovascular Disease'. This reflects biological reality - a
197        drug's mechanism doesn't change based on what it's prescribed for.
198    
199    .. note::
200        **Clinical Trial ID Explosion**: When a molecule has multiple comma-separated trial IDs
201        (e.g., 'NCT001,NCT002'), they are exploded into separate rows. This enables per-trial
202        analysis and proper trial counting.
203    
204    .. warning::
205        **Data Volume**: ChEMBL v36 contains hundreds of thousands of molecule-target relationships.
206        Full queries may take several minutes and return large DataFrames (>100K rows). Consider
207        filtering by version, phase, or disease after loading to reduce memory usage.
208    
209    .. warning::
210        **Activity-Based Targets**: Targets from the ``ACTIVITIES`` table (``target_source='ACTIVITIES'``)
211        have lower confidence than mechanism-based targets. They represent bioassay activity but may
212        not reflect clinical mechanisms. Set ``top_n_activities=0`` to exclude these if you need
213        high-confidence mechanisms only.
214    
215    .. warning::
216        **Requires chembl-downloader**: This function requires the ``chembl-downloader`` package
217        to be installed. Install via: ``pip install chembl-downloader``
218    
219    .. seealso::
220        - :func:`infer_nct_year`: Used internally to estimate trial registration years
221        - ChEMBL Documentation: https://chembl.gitbook.io/chembl-interface-documentation/
222        - ClinicalTrials.gov: https://clinicaltrials.gov/
223
224    """
225    
226    print("Step 1: Getting all parent molecules with their children's indications...")
227    # Get all molecules (parent and children) with clinical trial indications
228    # Map everything to parent_molregno
229    sql_all_indications = """
230    SELECT DISTINCT
231        COALESCE(MH.parent_molregno, MD.molregno) AS parent_molregno,
232        MD_parent.chembl_id AS parent_chembl_id,
233        MD_parent.pref_name AS parent_pref_name,
234        DI.mesh_heading,
235        DI.mesh_id,
236        DI.max_phase_for_ind AS phase,
237        IREF.ref_type AS reference_type,
238        IREF.ref_id AS clinical_trial_id
239    FROM MOLECULE_DICTIONARY MD
240    INNER JOIN DRUG_INDICATION DI ON MD.molregno = DI.molregno
241    INNER JOIN INDICATION_REFS IREF ON DI.drugind_id = IREF.drugind_id
242    LEFT JOIN MOLECULE_HIERARCHY MH ON MD.molregno = MH.molregno
243    INNER JOIN MOLECULE_DICTIONARY MD_parent ON COALESCE(MH.parent_molregno, MD.molregno) = MD_parent.molregno
244    WHERE IREF.ref_id IS NOT NULL 
245        AND IREF.ref_type = 'ClinicalTrials'
246    """
247    df_indications = chembl_downloader.query(sql_all_indications, version=version)
248    print(f"  Found {len(df_indications)} indication records for {df_indications['parent_molregno'].nunique()} parent molecules")
249    
250    print("\nStep 2: Getting mechanisms for parent molecules...")
251    # Get mechanisms from parent molecules directly
252    sql_parent_mechanisms = """
253    SELECT DISTINCT
254        MD.molregno AS parent_molregno,
255        TD.chembl_id AS target_chembl_id,
256        TD.organism AS target_organism,
257        TD.target_type,
258        CS_TARGET.accession AS target_uniprot_id,
259        COMP_SYN.component_synonym AS target_gene_name,
260        DM.mechanism_of_action,
261        DM.action_type,
262        'DRUG_MECHANISM' AS target_source
263    FROM MOLECULE_DICTIONARY MD
264    INNER JOIN DRUG_MECHANISM DM ON MD.molregno = DM.molregno
265    INNER JOIN TARGET_DICTIONARY TD ON DM.tid = TD.tid
266    LEFT JOIN TARGET_COMPONENTS TC ON TD.tid = TC.tid
267    LEFT JOIN COMPONENT_SEQUENCES CS_TARGET ON TC.component_id = CS_TARGET.component_id
268    LEFT JOIN COMPONENT_SYNONYMS COMP_SYN ON CS_TARGET.component_id = COMP_SYN.component_id AND COMP_SYN.syn_type = 'GENE_SYMBOL'
269    WHERE TD.organism = 'Homo sapiens'
270    """
271    df_parent_mech = chembl_downloader.query(sql_parent_mechanisms, version=version)
272    print(f"  Found {len(df_parent_mech)} mechanism records for {df_parent_mech['parent_molregno'].nunique()} parent molecules")
273    
274    print("\nStep 3: Getting mechanisms from children molecules for parents without mechanisms...")
275    # Get mechanisms from children for parents that lack mechanisms
276    sql_child_mechanisms = """
277    SELECT DISTINCT
278        MH.parent_molregno,
279        TD.chembl_id AS target_chembl_id,
280        TD.organism AS target_organism,
281        TD.target_type,
282        CS_TARGET.accession AS target_uniprot_id,
283        COMP_SYN.component_synonym AS target_gene_name,
284        DM.mechanism_of_action,
285        DM.action_type,
286        'DRUG_MECHANISM_CHILD' AS target_source
287    FROM MOLECULE_HIERARCHY MH
288    INNER JOIN DRUG_MECHANISM DM ON MH.molregno = DM.molregno
289    INNER JOIN TARGET_DICTIONARY TD ON DM.tid = TD.tid
290    LEFT JOIN TARGET_COMPONENTS TC ON TD.tid = TC.tid
291    LEFT JOIN COMPONENT_SEQUENCES CS_TARGET ON TC.component_id = CS_TARGET.component_id
292    LEFT JOIN COMPONENT_SYNONYMS COMP_SYN ON CS_TARGET.component_id = COMP_SYN.component_id AND COMP_SYN.syn_type = 'GENE_SYMBOL'
293    WHERE TD.organism = 'Homo sapiens'
294        AND NOT EXISTS (
295            SELECT 1 FROM DRUG_MECHANISM DM2 WHERE DM2.molregno = MH.parent_molregno
296        )
297    """
298    df_child_mech = chembl_downloader.query(sql_child_mechanisms, version=version)
299    print(f"  Found {len(df_child_mech)} child mechanism records for {df_child_mech['parent_molregno'].nunique()} parent molecules")
300    
301    # Combine mechanisms
302    df_mechanisms = pd.concat([df_parent_mech, df_child_mech], ignore_index=True)
303    
304    print("\nStep 4: Getting activities for parents without any mechanisms...")
305    # Find parents without mechanisms
306    parents_with_mech = set(df_mechanisms['parent_molregno'].unique())
307    all_parents = set(df_indications['parent_molregno'].unique())
308    parents_without_mech = all_parents - parents_with_mech
309    
310    print(f"  Parents needing activities: {len(parents_without_mech)}")
311    
312    if len(parents_without_mech) > 0:
313        molregno_list = ','.join(map(str, parents_without_mech))
314        
315        sql_activities = f"""
316        WITH RankedActivities AS (
317            SELECT 
318                COALESCE(MH.parent_molregno, ACT.molregno) AS parent_molregno,
319                TD.chembl_id AS target_chembl_id,
320                TD.organism AS target_organism,
321                TD.target_type,
322                CS_TARGET.accession AS target_uniprot_id,
323                COMP_SYN.component_synonym AS target_gene_name,
324                COUNT(ACT.activity_id) as activity_count,
325                ROW_NUMBER() OVER (PARTITION BY COALESCE(MH.parent_molregno, ACT.molregno) ORDER BY COUNT(ACT.activity_id) DESC) as rn
326            FROM ACTIVITIES ACT
327            LEFT JOIN MOLECULE_HIERARCHY MH ON ACT.molregno = MH.molregno
328            INNER JOIN ASSAYS A ON ACT.assay_id = A.assay_id
329            INNER JOIN TARGET_DICTIONARY TD ON A.tid = TD.tid
330            LEFT JOIN TARGET_COMPONENTS TC ON TD.tid = TC.tid
331            LEFT JOIN COMPONENT_SEQUENCES CS_TARGET ON TC.component_id = CS_TARGET.component_id
332            LEFT JOIN COMPONENT_SYNONYMS COMP_SYN ON CS_TARGET.component_id = COMP_SYN.component_id AND COMP_SYN.syn_type = 'GENE_SYMBOL'
333            WHERE COALESCE(MH.parent_molregno, ACT.molregno) IN ({molregno_list})
334                AND TD.organism = 'Homo sapiens'
335            GROUP BY COALESCE(MH.parent_molregno, ACT.molregno), TD.chembl_id, TD.organism, TD.target_type, CS_TARGET.accession, COMP_SYN.component_synonym
336        )
337        SELECT 
338            parent_molregno,
339            target_chembl_id,
340            target_organism,
341            target_type,
342            target_uniprot_id,
343            target_gene_name,
344            NULL AS mechanism_of_action,
345            NULL AS action_type,
346            'ACTIVITIES' AS target_source
347        FROM RankedActivities
348        WHERE rn <= {top_n_activities}
349        """
350        
351        df_activities = chembl_downloader.query(sql_activities, version=version)
352        print(f"  Found {len(df_activities)} activity-based targets for {df_activities['parent_molregno'].nunique()} parent molecules")
353        
354        # Add to mechanisms
355        df_mechanisms = pd.concat([df_mechanisms, df_activities], ignore_index=True)
356    
357    print("\nStep 5: Combining indications with mechanisms...")
358    # Cross join indications with mechanisms at parent level (each indication gets all targets)
359    df_combined = df_indications.merge(
360        df_mechanisms,
361        on='parent_molregno',
362        how='left'
363    )
364    
365    # Rename columns for final output
366    df_combined = df_combined.rename(columns={
367        'parent_chembl_id': 'chembl_id',
368        'parent_pref_name': 'pref_name'
369    })
370    
371    print(f"  Total combined records: {len(df_combined)}")
372    print(f"  Total unique parent molecules: {df_combined['chembl_id'].nunique()}")
373    
374    # Explode clinical_trial_id column if it contains multiple comma-separated IDs
375    if 'clinical_trial_id' in df_combined.columns and df_combined['clinical_trial_id'].notna().any():
376        df_combined['clinical_trial_id'] = df_combined['clinical_trial_id'].apply(
377            lambda x: x.split(',') if isinstance(x, str) and ',' in x else [x] if x else []
378        )
379        df_combined = df_combined.explode('clinical_trial_id')
380        df_combined['clinical_trial_id'] = df_combined['clinical_trial_id'].apply(lambda x: x.strip() if isinstance(x, str) else x)
381        
382        # Add inferred year column as integer
383        df_combined['trial_year'] = df_combined['clinical_trial_id'].apply(infer_nct_year)
384        df_combined['trial_year'] = df_combined['trial_year'].astype('Int64')  # Use nullable integer type
385
386    return df_combined

 6def infer_nct_year(nct_id):
 7    """
 8    Infer the approximate registration year from a ClinicalTrials.gov NCT identifier.
 9    
10    NCT IDs follow the format ``NCT########``, where the 8-digit numeric portion is assigned
11    sequentially and increases over time. This function uses empirically-observed NCT ID
12    allocation patterns to estimate when a trial was registered, which is useful for temporal
13    filtering and analysis when exact registration dates are not available.
14
15    **NCT ID Allocation Ranges:**
16    
17    - ``NCT00000000`` - ``NCT00999999``: ~1999-2004
18    - ``NCT01000000`` - ``NCT01999999``: ~2005-2011
19    - ``NCT02000000`` - ``NCT02999999``: ~2012-2015
20    - ``NCT03000000`` - ``NCT03999999``: ~2016-2018
21    - ``NCT04000000`` - ``NCT04999999``: ~2019-2021
22    - ``NCT05000000`` - ``NCT05999999``: ~2022-2023
23    - ``NCT06000000``+: ~2024+
24
25    :param nct_id: A ClinicalTrials.gov identifier (e.g., ``NCT00500000``)
26    :type nct_id: str
27    :return: Estimated year of trial registration as an integer, or ``None`` if the NCT ID is invalid/malformed
28    :rtype: int or None
29
30    **Examples**
31    
32    >>> infer_nct_year("NCT00500000")
33    2002
34    >>> infer_nct_year("NCT03000000")
35    2016
36    >>> infer_nct_year("NCT06123456")
37    2024
38    >>> infer_nct_year("invalid")
39    None
40    >>> infer_nct_year("NCT123")  # Too short
41    None
42    
43    **Use Cases:**
44    
45    - Filtering clinical trials data by approximate time period
46    - Temporal analysis of drug development trends
47    - Quick year estimation when full trial metadata is unavailable
48    
49    .. note::
50        This function provides an **approximation** based on historical NCT ID allocation
51        patterns. Individual trials may vary by ±1-2 years from the estimated value.
52        For precise temporal analysis, obtain the actual registration date from the
53        ClinicalTrials.gov API or database.
54    
55    .. warning::
56        Returns ``None`` for invalid inputs including non-string types, IDs without the
57        "NCT" prefix, or IDs that don't contain exactly 8 digits after "NCT".
58    """
59    if not isinstance(nct_id, str) or not nct_id.startswith('NCT'):
60        return None
61    
62    # Extract the numeric part
63    match = re.search(r'NCT(\d{8})', nct_id)
64    if not match:
65        return None
66    
67    num = int(match.group(1))
68    
69    # Approximate year ranges based on NCT ID ranges
70    if num < 1000000:
71        return 1999 + (num // 200000)  # ~1999-2004
72    elif num < 2000000:
73        return 2005 + ((num - 1000000) // 140000)  # ~2005-2011
74    elif num < 3000000:
75        return 2012 + ((num - 2000000) // 250000)  # ~2012-2015
76    elif num < 4000000:
77        return 2016 + ((num - 3000000) // 330000)  # ~2016-2018
78    elif num < 5000000:
79        return 2019 + ((num - 4000000) // 350000)  # ~2019-2021
80    elif num < 6000000:
81        return 2022 + ((num - 5000000) // 500000)  # ~2022-2023
82    else:
83        return 2024 + ((num - 6000000) // 500000)  # ~2024+