PubChem: compound identifier, name and mass registry: sample notebook
A worked notebook for PubChem: compound identifier, name and mass registry, with 4 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
- National Center for Biotechnology Information (NCBI)
- License
- No restriction asserted by NCBI; not warranted free of third-party depositor claims (see NCBI site policy)
- Format
- Parquet (in sampled files)
- Stored at
- Amazon S3, s3://scigantic-pubchem (us-east-1)
- Access
- Free and public. No account with the data provider is needed.
The notebook
PubChem: compound identifier, name and mass registry
PubChem is NCBI's open chemistry database: 124.6 million compounds. This archive is a narrow, weekly-refreshed mirror of just the identifier/name/mass registry, not the full PubChem Compound database -- eight tables, mounted read-only at ~/archive/parquet/, each exported by PubChem in ascending CID order:
| table | columns |
| smiles.parquet | cid, smiles |
| titles.parquet | cid, title |
| iupac_names.parquet | cid, iupac_name |
| inchi_keys.parquet | cid, inchi, inchikey (multiple rows per cid: one per protonation state) |
| mass.parquet | cid, molecular_formula, monoisotopic_mass, exact_mass |
| parent.parquet | cid, parent_cid |
| cid_sid.parquet | cid, sid, link_type |
| synonyms.parquet | cid, synonym (multiple rows per cid) |
Because every table is CID-sorted, a query that filters on cid prunes straight to the right row group -- the point lookup this archive exists for. BioAssay results, gene/protein data, 3D conformers, and similarity/substructure search are NOT in this mirror; those stay live via the scigantic_pubchem package's PUG REST calls, shown below.
import os
from pathlib import Path
import duckdb
ARCHIVE = Path(os.environ.get("SCIGANTIC_MOUNT_PATH", Path.home() / "archive"))
PARQUET = ARCHIVE / "parquet"
cid = 2244 # aspirin
row = duckdb.connect().execute(f"""
SELECT s.smiles, t.title, i.iupac_name, m.molecular_formula, m.monoisotopic_mass
FROM read_parquet('{PARQUET / "smiles.parquet"}') s
LEFT JOIN read_parquet('{PARQUET / "titles.parquet"}') t USING (cid)
LEFT JOIN read_parquet('{PARQUET / "iupac_names.parquet"}') i USING (cid)
LEFT JOIN read_parquet('{PARQUET / "mass.parquet"}') m USING (cid)
WHERE s.cid = ?
""", [cid]).fetchone()
row
All known names for a CID
synonyms.parquet has every name PubChem's filtered synonym list carries for a CID, one row each:
duckdb.connect().execute(f"""
SELECT synonym FROM read_parquet('{PARQUET / "synonyms.parquet"}') WHERE cid = ?
""", [cid]).df()
Or, skip the SQL
scigantic_pubchem is already installed in this image (also on PyPI for use outside Scigantic). Its identifiers()/synonyms() do the same joins as above, but as a live remote query -- inside Scigantic, reading the mounted files directly (like the cells above) is faster since it skips a redundant network round trip. Reach for the package instead when you're not running inside Scigantic, or when you need resolve()/xrefs(): name/InChIKey/SMILES -> CID resolution, which this mirror can't answer at all since it's CID-sorted and gets no pruning benefit from those columns.
import scigantic_pubchem as pubchem
pubchem.identifiers(cid)
Cross-referencing into BindingDB
BindingDB's own measurements.parquet already carries a pubchem_cid column, so this is a live filter against the scigantic-bindingdb mirror, not a precomputed join:
pubchem.bindingdb_measurements(cid)
Run it
Open this notebook to run it, see the dataset page for the full description, or browse the full catalog.