ChEMBL 37: curated bioactivity database of drug-like molecules: sample notebook
A worked notebook for ChEMBL 37: curated bioactivity database of drug-like molecules, with 3 code cells and the output each one printed. On Scigantic it opens in a hosted JupyterLab with the dataset already mounted, so nothing is downloaded first.
- Provider
- EMBL-EBI (ChEMBL group)
- License
- CC BY-SA 3.0
- Format
- FASTA, HDF5 (in sampled files)
- Stored at
- Amazon S3, s3://scigantic-chembl (us-east-1)
- Access
- Free and public. No account with the data provider is needed.
- DOI
- 10.6019/CHEMBL.database.37
The notebook
ChEMBL 37
One row in activities is one measurement of one compound in one assay. There are 24,527,044 of them, over 2,921,148 compounds and 18,552 targets, curated from the medicinal chemistry literature by EMBL-EBI.
The release is mounted read-only at ~/archive/chembl_37. Three copies of the same data live there:
- derived/activities_enriched.parquet — the potency join done once, four correctness filters already applied. Start here. The query below takes under a second.
- parquet/ — 72 raw tables, one file each. Use this for anything the join above excludes: non-numeric outcomes, censored rows, or any of the other tables.
- sqlite/chembl_37.db — the database exactly as EBI ships it, with all its indexes. Good for looking up one compound by ChEMBL ID; a 5-way join does not finish against it.
bulk/ has the SDF, a flat SMILES table, and Morgan fingerprints if you would rather not write SQL at all.
import os
from pathlib import Path
import duckdb
ARCHIVE = Path(os.environ.get("SCIGANTIC_MOUNT_PATH", Path.home() / "archive"))
ENRICHED = ARCHIVE / "chembl_37" / "derived" / "activities_enriched.parquet"
df = duckdb.connect().execute(f"""
SELECT compound_chembl_id, compound_pref_name, standard_type,
standard_value, standard_units, pchembl_value,
canonical_smiles, doc_year
FROM read_parquet('{ENRICHED}')
WHERE target_chembl_id = 'CHEMBL203' -- EGFR. Swap in any target.
AND target_type = 'SINGLE PROTEIN'
AND confidence_score >= 8
ORDER BY pchembl_value DESC
""").df()
print(f"{len(df):,} activities, {df.compound_chembl_id.nunique():,} distinct compounds")
df.head(10)
Output:
18,998 activities, 11,202 distinct compounds compound_chembl_id compound_pref_name standard_type standard_value \ 0 CHEMBL4650319 MOBOCERTINIB IC50 0.010 1 CHEMBL5270693 None IC50 0.010 2 CHEMBL176582 None IC50 0.010 3 CHEMBL5790648 None IC50 0.016 4 CHEMBL5746224 None IC50 0.017 5 CHEMBL4650319 MOBOCERTINIB IC50 0.020 6 CHEMBL5270693 None IC50 0.020 7 CHEMBL180022 NERATINIB IC50 0.020 8 CHEMBL4537790 None IC50 0.020 9 CHEMBL3353410 OSIMERTINIB IC50 0.020 standard_units pchembl_value \ 0 nM 11.00 ...
Four of those six filters are already applied inside activities_enriched.parquet: pchembl_value IS NOT NULL, standard_relation = '=', no data_validity_comment, not a potential_duplicate. Applied across the whole database, together with the two filters still in the query above, they keep 3,328,132 of the 24.5M activities, so most of what's missing was genuinely not comparable.
- pchembl_value is -log10 of the value in molar. It is populated on 20% of rows, only where the endpoint is an exact IC50, EC50, Ki, Kd or similar.
- standard_value, not value — the standard_* columns are unit-harmonised (concentrations in nM). The raw columns are whatever the paper said.
- chembl_id is stable, molregno is not. The join is already done for you here; if you drop to parquet/ for something this file excludes, join on the integer keys inside one release, but never save them.
- Ask the assistant panel on the right for anything else. It has the full schema and the traps for this release.
Table and column reference: ~/archive/chembl_37/schema_documentation.txt. Every table's row count: ~/archive/chembl_37/parquet/_index.txt.
Or, skip the SQL
scigantic_chembl is already installed in this image (also on PyPI for use outside Scigantic). It does the join above in one call, byte-identical results to the cell above, 18,998 rows either way.
import scigantic_chembl as chembl
df = chembl.activities(target_chembl_id="CHEMBL203")
print(f"{len(df):,} activities, {df['compound_chembl_id'].nunique():,} distinct compounds")
Output:
18,998 activities, 11,202 distinct compounds
It also does two things the files alone don't give you: every compound with a measurement (1.68M of them) has a precomputed fingerprint, so structural similarity search and exact substructure search are function calls, not a fingerprinting pipeline you write yourself.
gefitinib = "COC1=C(C=C2C(=C1)N=CN=C2NC3=CC(=C(C=C3)F)Cl)OCCCN4CCOCC4"
chembl.similar_compounds(gefitinib, top_k=5)
Output:
chembl_id tanimoto 0 CHEMBL939 1.000000 1 CHEMBL14699 0.919355 2 CHEMBL4165375 0.916667 3 CHEMBL299672 0.857143 4 CHEMBL4448162 0.857143
Run it
Open this notebook to run it, see the dataset page for the full description, or browse the full catalog.