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:

tablecolumns
smiles.parquetcid, smiles
titles.parquetcid, title
iupac_names.parquetcid, iupac_name
inchi_keys.parquetcid, inchi, inchikey (multiple rows per cid: one per protonation state)
mass.parquetcid, molecular_formula, monoisotopic_mass, exact_mass
parent.parquetcid, parent_cid
cid_sid.parquetcid, sid, link_type
synonyms.parquetcid, 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.