ZINC15:examples
ZINC15:examples:results-figures ZINC15:Actions ZINC15:examples:genes ZINC15:examples:biogenic ZINC15:examples:bioactive ZINC15:examples:screening
Examples organized by level of difficulty
See ZINC15:Levels for a description of our classification of questions by how easy it is to answer them.
Public access
- ZINC15:examples:level1 - questions that are easy to answer using the [zinc15.docking.org zinc15.docking.org] web page.
- ZINC15:examples:level2 - questions that require you compose your own query in the URL. This is usually because we have not yet gotten around to writing a graphical interface for this class of questions.
- ZINC15:examples:level3 - these are questions that are difficult or not possible to answer using the ZINC 15 website as it currently exists, but can be answered using a bit of python. We show you how. Using a bit of python can dramatically extend the power of the ZINC website.
Private access required
- ZINC15:examples:level4 - these (and all higher levels) require privileged access to ZINC which is not available to the general public. However, by showing you what these questions are, and how you can solve them with access, we alert you to the possibilities of ZINC. Ask us if you require this level of access to ZINC.
- ZINC15:examples:level5 - higher level access. not just privileged ipynb, but also command line ZINC shell.
- ZINC15:examples:level6 - SQL
- ZINC15:examples:level7 - SQL and python
- ZINC15:examples:level8 - questions we've been asked that we are unable to answer as this time.
zinc15.docking.org/substances.txt:smiles,zinc_id,tanimoto_similarity? catalog.purchasable%20gt%209&ecfp4.data%20similarto%20CC(=O)Oc1ccccc1C(=O)O%20within%20.5?count=all
How many primary amines are available for sale in preparative quantities?
URL="zinc15.docking.org:8015/substances.txt?substance.structure:contains=[ND1]&catalog.bb=True&substance.purchasability:gt=9&count=all" wget -o log -O amines.smi "$URL" wc -l amines.smi
answer: xxxx
How many ChEMBL targets have at least one purchasable compound for sale?
zinc15.docking.org:8015/activities:target.uniprot?catalog.purchasable .....
How many ligands for class F GPCRs are for sale?
zinc15.docking.org:8015/substances.txt:smiles,sub_id,gene.name?subclass=name=GPCR-F&substance.purchasability:ge=10&count=all
What is the nearest metabolite or drug to my compound?
zinc15.docking.org/substances.txt?ecfp4.data:tanimoto,0.5=<url-encoded-smiles>
thus for ZINC27,
zinc15.docking.org/substances.txt?ecfp4.data:tanimoto,0.5=N[C@@H](CCc1ccc(N(CCCl)CCCl)cc1)C(=O)O
Which FDA approved drugs are also metabolites?
zinc15.docking.org/substances?substance.features:contains=211,202&count=all
“Which compounds are in Drugbank but not in ChEMBL Drugstore?
Which genes have a ligand reported that binds at 10nM concentration or better?
zinc15.docking.org:8015/activities.txt:gene.name?activities.affinitynm:ge=8&distinct=gene.name&count=all
Which targets do hydroxamic acids bind to?
zinc15.docking.org/targets.txt:target.uniprot?structure.contains=C(=O)[ND2][OD1]&distinct=target.uniprot&count=all
Which vendors sell the most biogenic compounds (natural products), and what are those compounds?
zinc15.docking.org/catitms.txt:catalog.short_name,catitm.supplier_code,substance.zinc_id,substance.smiles?catalog.np=1&count=all
How many primary amines as for sale in preparative quantitites?
zinc15.docking.org/substances.txt?substance.structure:contains=[ND1]&catalog.bb=True&count=all
How to calculate results from the ZINC15 2015 paper:
Table 2 - Genes, Uniprot codes and annotated compounds by affinity bin
- * select organism_fk, count(gene_id) from gene group by organism_fk;
zinc15.docking.org/genes.txt:count(*)?group-by=organism_fk
- + select g.organism_fk, count(a.anno_id) from annotation a, gene g where a.gene_fk = g.gene_id group by g.organism_fk;
zinc15.docking.org/targets.txt:count(*)?group-by=gene.organism_fk
- $ select count(distinct(n.sub_id_fk)),g.organism_fk from gene g, annotation a, note n where g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 9 group by g.organism_fk;
zinc15.docking.org/ not sure we can do this!
Table 3 - Gene Target Classes
- * select count(g.gene_id),tc.major_class from target_class tc, gene g where g.target_class_fk = tc.target_class_id group by tc.major_class order by tc.major_class
- + select count(distinct(s.sub_id)),tc.major_class from gene g, substance s, note n, annotation a, target_class tc where g.gene_id = a.gene_fk and g.target_class_fk = tc.target_class_id and n.anno_id_fk = a.anno_id and s.sub_id = n.sub_id_fk and s.purchasability >20, 20, 10 group by tc.major_class
- $ select count(distinct(g.gene_id)), tc.major_class from target_class tc, gene g, substance s, note n, annotation a where s.sub_id = n.sub_id_fk and a.anno_id = n.anno_id_fk and a.gene_fk = g.gene_id and g.target_class_fk = tc.target_class_id and s.purchasability > 9 group by tc.major_class;
Table 4 - Chemical Diversity by Gene
4) histogram of clusters per gene.
select c.gene_fk, g.name, count(c.cluster_id) from cluster c, gene g where g.gene_id = c.gene_fk and c.ordinal = 1 group by c.gene_fk,g.name order by count(c.cluster_id) desc;
for any one pick the smiles:
select smiles,sub_id from substance where sub_id in (select rep_sub_id_fk from cluster where gene_fk = 57387 and ordinal=1);
another
select c.gene_fk, g.name, count(c.cluster_id) from cluster c, gene g where g.gene_id = c.gene_fk and c.ordinal = 1 group by c.gene_fk,g.name order by count(c.cluster_id) desc;
another
select gene_fk, count(cluster_id) from cluster where ordinal = 1 group by gene_fk order by count(cluster_id) desc ;
Table 5 - Genes per compound as a function of affinity cutoff (polypharmacology)
this is almost for sure wrong:
select sub_id count(sub_id_fk) from note as n join annotation as a on n.anno_id_fk = a.anno_id join gene as g on g.gene_id = a.gene_fk group by n.sub_id_fk having min(n.affinitynM) >= 9;
NEW
Non purch:
select count(sub_id_fk) as num_compounds, cgi as having_num_genes from (select n.sub_id_fk as sub_id_fk , count(distinct(g.gene_id)) as cgi from gene g, annotation a, note n where g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 9 group by n.sub_id_fk) as t group by cgi order by cgi;
Heavy hitters:
select n.sub_id_fk as sub_id_fk , count(distinct(g.gene_id)) as cgi from gene g, annotation a, note n where g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 9 group by n.sub_id_fk order by cgi desc limit 10;
Purch:
select count(sub_id_fk) as num_compounds, cgi as having_num_genes from (select n.sub_id_fk as sub_id_fk , count(distinct(g.gene_id)) as cgi from gene g, annotation a, note n, catalog_item ci, catalog c where c.cat_id=ci.cat_id_fk and ci.sub_id_fk = n.sub_id_fk and g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 8 and c.purchasable>9 group by n.sub_id_fk) as t group by cgi order by cgi;
Heavy hitters (purch):
select n.sub_id_fk as sub_id_fk , count(distinct(g.gene_id)) as cgi from gene g, annotation a, note n, catalog_item ci, catalog c where c.cat_id=ci.cat_id_fk and ci.sub_id_fk = n.sub_id_fk and g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 8 and c.purchasable>9 group by n.sub_id_fk
Table 6
1 compounds:
http://zinc15.docking.org/substances.txt:smiles,zinc_id?structure-contains=CS(=O)(=O)[ND1]&substance.purchasability=for-sale
genes: 2
http://zinc15.docking.org/substances.txt:smiles,zinc_id?substance.purchasability=for-sale&structure-contains=C(=O)[ND2][OD1]
3
http://zinc15.docking.org/substances.txt:smiles,zinc_id?purchasability=for-sale&structure-contains=cC(=O)[OD1]
4
zinc15.docking.org/v1/substances.txt:smiles,zinc_id?purchasability=for-sale&structure-contains=CC(=O)[OD1]
5.
zinc15.docking.org/substances.txt:smiles,zinc_id?purchasability=for-sale&structure-contains=[SH]&count=all
6. Primary amines.
Table 7 - What genes do warheads hit?
compounds: 1
zinc15.docking.org/substances.txt:smiles,zinc_id?structure-contains=CS(=O)(=O)[ND1]&purchasability=for-sale
2
zinc15.docking.org/substances.txt:smiles,zinc_id?purchasability=for-sale&structure-contains=C(=O)[ND2][OD1]
3
zinc15.docking.org/substances.txt:smiles,zinc_id?purchasability=for-sale&structure-contains=cC(=O)
4
http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.purchasability%20gt%209&structure%20contains%20CC(=O)[OD1]
5.
Table 8A
1. http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.features%20contains%20201, 2. http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.features%20contains%20202, 3. http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.features%20contains%20203,
&substance.purchasability >20 =20, =10, >1.
NEW TABLE 8
chemical warheads and the genes they hit as a function of affinity.
http://zincapi.ucsf.bkslab.org/v1/activities/txt:target.gene.name,substance.smiles,substance.zinc_id,substance.purchasability/activity.affinitynm ge 7&substance.structure%20contains%20cC(=O)[OD1]?count=all
Table 8B
http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.features%20contains%20211, http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.features%20contains%20212, http://zincapi.ucsf.bkslab.org/v1/substances/txt:smiles,zinc_id/substance.features%20contains%20213,
&substance.purchasability >20 =20, =10, >1.
Figure 1
Drawn. probably to delete.
Figure 2
Non-purch final:
psql -h samekh -U test zinc15 -c "select count(distinct(n.sub_id_fk)) as num_compds, g.name as gene_name from gene g, annotation a, note n where g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 5 and g.organism_fk = 1 group by g.name " > 1-10um.txt
Purch final:
psql -h samekh -U test zinc15 -c "select count(distinct(n.sub_id_fk)) as num_compds, g.name as gene_name from gene g, annotation a, note n, catalog_item ci, catalog c where c.cat_id=ci.cat_id_fk and ci.sub_id_fk = n.sub_id_fk and g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 5 and g.organism_fk = 2 and c.purchasable > 9 group by g.name" > 2-10um-purch.txt
Unknown
http://api.docking.org/v1/activities/txt:target.gene.name,sub_id_fk,affinitynm,substance.best_purchasbility
New non-purch
select count(distinct(n.sub_id_fk)) as num_compds, g.name as gene_name from gene g, annotation a, note n where g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 6 and g.organism_fk = 1 group by g.name; (by organism x 4)
Purch:
select count(distinct(n.sub_id_fk)) as num_compds, g.name as gene_name from gene g, annotation a, note n, catalog_item ci, catalog c where c.cat_id=ci.cat_id_fk and ci.sub_id_fk = n.sub_id_fk and g.gene_id = a.gene_fk and a.anno_id = n.anno_id_fk and n.affinitynm >= 8 and g.organism_fk = 2 and c.purchasable > 9 group by g.name limit 10;
Figure 3
Actions
Translate
zinc15.docking.org/actions/translate
Nearest Metabolite
zinc15.docking.org/actions/translate
Build dockable library
zinc15.docking.org/actions/build-library
One-step reactions
zinc15.docking.org/actions/reaction