Untargeted or semi-targeted metabolomic datasets are sometimes labelled with common compound names, e.g. lactate, or ATP.
Converting these common names to database IDs suitable for enrichment analysis e.g., KEGG IDs for use with KEGG pathways, can be done by hand; but that is tedious and subjective. I could not find an end-to-end solution to automate this conversion.
A command line script which automatically converts a compound's common names into as many external IDs as possible.
For example, for an input.txt:
1-methyladenosine
xanthosine
It will output an output.tsv:
original_name source_name source_id
1-methyladenosine Hangzhou APIChem Technology AC-32347
1-methyladenosine 27441 190000038145
[...]
xanthosine 27041 V11222
xanthosine NextBio 64959
Such that KEGG IDs etc. can be obtained:
$ grep KEGG output.tsv
1-methyladenosine KEGG C02494
xanthosine KEGG C01762Click here to expand the command line script
There are two dependencies, both available from PyPI: pubchempy and tqdm.
This script was generated in part by Google's Gemini Pro 2.5 (online chat interface) on 15 October 2025.
# pubchem_substance_mapper.py
#
# Description:
# This script reads a list of compound common names from an input text file,
# queries the PubChem database to find associated substance source names and IDs,
# and writes the results to a three-column TSV file.
#
# Requirements:
# pip install pubchempy tqdm
#
# Usage:
# python3 pubchem_substance_mapper.py input.txt output.tsv
# python3 pubchem_substance_mapper.py input.txt output.tsv --delay 0.2
# python3 pubchem_substance_mapper.py input.txt output.tsv --no-progress-bar
import argparse, csv, sys, time
from typing import List, Tuple, Dict
import urllib.error
import pubchempy as pcp
from tqdm import tqdm
def main():
"""
Main function to parse arguments and orchestrate the mapping process.
"""
parser = argparse.ArgumentParser(
description="Map compound common names to PubChem Substance IDs.",
formatter_class=argparse.ArgumentDefaultsHelpFormatter
)
parser.add_argument("input_file", help="Path to input plaintext file.")
parser.add_argument("output_file", help="Path for the output TSV file.")
parser.add_argument(
"--delay",
type=float,
default=0.5,
help="Seconds to wait between each API call to PubChem."
)
parser.add_argument(
"--no-progress-bar",
action="store_true",
help="Disable the interactive progress bar."
)
args = parser.parse_args()
# Step 1: Read names from the input file
compound_names = read_compound_names(args.input_file)
print(f"Read {len(compound_names)} compound names from '{args.input_file}'.")
# Step 2: Process names and query the API
results, stats = process_and_query_names(
compound_names, args.delay, args.no_progress_bar
)
# Step 3: Write results to the output file
write_tsv(args.output_file, results)
# Step 4: Report final statistics
print(f"\n--- Processing Complete ---")
print(f"Results saved to '{args.output_file}'.")
print("\nStatistics:")
print(f" - Names with substances found: {stats['names_with_substances']} / {len(compound_names)}")
print(f" - Names without substances found: {len(compound_names) - stats['names_with_substances']} / {len(compound_names)}")
print(f" - Total substances written: {stats['total_substances_found']}")
def read_compound_names(filepath: str) -> List[str]:
"""Reads compound names from a text file, one per line."""
with open(filepath, 'r', encoding='utf-8') as f:
return [line.strip() for line in f if line.strip()]
def process_and_query_names(names: List[str], delay: float, no_progress_bar: bool) -> Tuple[List[List[str]], Dict[str, int]]:
"""
Iterates through compound names, queries PubChem, and collects results and statistics.
"""
results_data = []
stats = {
"names_with_substances": 0,
"total_substances_found": 0,
}
iterable = names
if not no_progress_bar:
iterable = tqdm(names, desc="Processing compounds", unit=" name", file=sys.stdout)
for name in iterable:
if not no_progress_bar:
iterable.set_postfix_str(f"Querying: {name[:25]:<25}", refresh=True)
substances = get_substances_from_name(name, delay)
if substances:
stats["names_with_substances"] += 1
stats["total_substances_found"] += len(substances)
for substance in substances:
source_name = substance.source_name if substance.source_name else 'N/A'
source_id = substance.source_id if substance.source_id else 'N/A'
results_data.append([name, source_name, source_id])
else:
# Write the original name even if no substances are found
results_data.append([name, '', ''])
time.sleep(delay)
return results_data, stats
def write_tsv(filepath: str, data: List[List[str]]):
"""Writes the collected data to a TSV file."""
with open(filepath, 'w', newline='', encoding='utf-8') as f:
writer = csv.writer(f, delimiter='\t')
writer.writerow(['original_name', 'source_name', 'source_id'])
writer.writerows(data)
def get_substances_from_name(name: str, delay: float) -> List[pcp.Substance]:
"""
Retrieves a list of Substance objects for a given compound name.
Handles PUGREST.Timeout errors by splitting large SID sets into smaller chunks.
"""
cids = pcp.get_cids(name, 'name')
if not cids:
return []
compounds = pcp.get_compounds(cids)
sids_set = {sid for compound in compounds if compound.sids for sid in compound.sids}
if not sids_set:
return []
# Hand off to the recursive function that can handle splitting
return _get_substances_with_splitting(list(sids_set), delay)
def _get_substances_with_splitting(sids: List[int], delay: float, split_level: int = 0) -> List[pcp.Substance]:
"""
Recursively queries for substances, splitting the SID list on timeout.
"""
MAX_SPLITS = 5
if split_level >= MAX_SPLITS:
print(f"\nFailed to retrieve substances after splitting the query {MAX_SPLITS} times. Exiting.", file=sys.stderr)
sys.exit(1)
# An empty list of SIDs will cause an error, so we return early.
if not sids:
return []
try:
# Make the actual API call
substances = pcp.get_substances(sids)
return substances
except pcp.TimeoutError as e:
if 'PUGREST.Timeout' in str(e):
# If timeout, split the list and recurse on each half
print(f"\nPubChem timeout. Splitting SID set of size {len(sids)} (Split level {split_level + 1}/{MAX_SPLITS})...", file=sys.stderr)
mid_point = len(sids) // 2
first_half = sids[:mid_point]
second_half = sids[mid_point:]
# Process first half
results1 = _get_substances_with_splitting(first_half, delay, split_level + 1)
# Delay between the split queries
time.sleep(delay)
# Process second half
results2 = _get_substances_with_splitting(second_half, delay, split_level + 1)
return results1 + results2
else:
# Re-raise any other HTTP error immediately
raise
if __name__ == "__main__":
main()Even though some databases have their own common name--to-ID mapping services, e.g. KEGG has a text-based search; I found that PubChem's database of compound aliases and external IDs worked much better.
The core logic of the script is this:
- For each common name, find all the matching PubChem compounds
- For all the matching PubChem compounds, find all its corresponding PubChem substances
- For each PubChem substance, print the source name (e.g. KEGG) and source ID (e.g. C02494)
An important QOL is the handling of server-side PubChem HTTP 504 error (Gateway Timeout) due to queries for PubChem substances with large lists of PubChem substance IDs (> 1000-ish). This is handled by recursively splitting the substance ID list into half, up to five times. So, it should work fine for any common name with fewer than about 32,000 substances.
The script does not explicitly record the PubChem compound ID (CID) itself. It is recorded implicitly, though, because many external databases mirror the CID.