Connect Scripting via Jupyter Notebook

This short tutorial is created for Demo Project. In case of using different schema, values needs to be adjust.

Connecting to Connect session

from connect_api import DataApi
# DataApi("apiKey", ["serverSchemaId"]) or DataApi("apiKey", ["serverSchemaId1", "serverSchemaId2"]) for 2 schemas
api = DataApi("6i9MYZxHxJMvHn4qyJ2vAockLzqllbNk", ["6C6F63616C6462"])

Display all view names

Display all views associated to a project.

[view.name for view in api.getViews()]

['Pubchem grid view',
 'Wombat activities (multi-entity table)',
 'Compact fields',
 'Wombat (calc fields)',
 'Wombat (charts overview)',
 'Wombat (compound view) compounds',
 'Wombat (widgets overview)',
 'Pubchem conditional formatting',
 'Pubchem single record form',
 'Pubchem tabular form']

Connect to a View

view = api.getViewByName("Pubchem grid view")

Open view session

View session is an object which enables the user to query data and get other information related to the view.

First we connect to the pubchem view session.

pubchem_vs = api.openViewSession(api.getViewByName('Pubchem grid view'))

Next, let's connect to the wombat view session

wombat_vs = api.openViewSession(api.getViewByName('Wombat activities'))

Perform query and get data

Querying data is based on the jchem query language. Connect API uses the QueryTerm object to perform analogous queries.

from connect_api import QueryTerm

Get data tree associated to the pubchem view

dt = pubchem_vs.getDataTree()

Get id field and molweight fields

cdId = dt.getFieldByName("CdId")
mw = dt.getFieldByName("Mol Weight")
dbname = dt.getFieldByName("DB name")

Query all items where ID field (CdId) has larger value than 1003.

pubchem_vs.query([
  QueryTerm(cdId.fieldId, ["1003"], ">")
], {})

[JUST INPUT TO THE QUERY] Print ID of an ID field.

cdId.fieldId

'18BCBE8A4D74E975267DFA2C991E16B8'

Now retrieve all data which satisfies the query and display first 10 results

allData = pubchem_vs.getData([cdId.fieldId, mw.fieldId, dbname.fieldId])
allData[:10]

[[1004, 169.073, 'BioCyc'],
 [1005, 202.55, 'BioCyc'],
 [1006, 163.184, 'BioCyc'],
 [1007, 148.158, 'BioCyc'],
 [1008, 260.135, 'BioCyc'],
 [1009, 473.446, 'BioCyc'],
 [1010, 98.95, 'BioCyc'],
 [1011, 181.44, 'BioCyc'],
 [1012, 290.447, 'BioCyc'],
 [1013, 169.136, 'BioCyc']]

Make a scatter plot of the data. Here I use seaborn library for that purpose

Analyze data

import seaborn as sns
import pandas as pd
ids = [inner_list[0] for inner_list in allData]
molweights = [inner_list[1] for inner_list in allData]
dbnames = [inner_list[2] for inner_list in allData]
df = pd.DataFrame()
df['CdId']=ids
df['mw']=molweights
df['dbname']=dbnames
print(f"The dataframe has {df.shape[0]} rows and {df.shape[1]} columns.")
_=sns.scatterplot(x='CdId', y='mw', data=df)

The dataframe has 997 rows and 3 columns.

src/images/plexus-connect/Jupyter/output_25_1.png

Alternatively this can be done simply by using pandas (both plotting in pandas and seaborn are using an underscoring matplotlib library. However the implementation and settings are different, therefore different default aesthetics.)

_ = df.plot(kind='scatter', x='CdId', y='mw')

src/images/plexus-connect/Jupyter/output_27_0.png

if I want to look at the distribution of molecular weights, then this can be achieved again by using pandas library's methods.

_ = df.mw.hist()

src/images/plexus-connect/Jupyter/output_29_0.png

Let's look at the original databases and their relations to molecular weigths. First, we check how many molecules belong to what database.

df.dbname.value_counts()

KEGG                      250
MOLI                      250
NIST Chemistry WebBook    250
BioCyc                    247
Name: dbname, dtype: int64

Then we display this using a scatterplot

sns.scatterplot(data=df, x='mw', y='CdId', hue=df.dbname.tolist())

<AxesSubplot:xlabel='mw', ylabel='CdId'>

src/images/plexus-connect/Jupyter/output_33_1.png

We can see here, that molecules from all four constituing databases have similar molecular weights. However, to really check that, we need different vizualization. Violin plot might come in handy

sns.violinplot(data=df, y='mw', x=df.dbname.tolist())

<AxesSubplot:ylabel='mw'>

src/images/plexus-connect/Jupyter/output_35_1.png

Now, we could say that the mass composition of molecules from the databases is indeed very similar with one possible exception being the MOLI database.

Perform query in wombat

Let's begin by displaying all fields.

dt2 = wombat_vs.getDataTree()
print(f"Field names: {[field.name for field in dt2.root.fields]}")

Field names: ['Num assay vals', 'Formula', 'ID', 'MOL.REF', 'VALUE.MIN', 'BIND.NONSPECIFIC', 'EST.LOGKOW', 'BIND.ENDOGENLIGAND', 'SMDL.ID', 'SMDL.IDX', 'BIO.TISSUETYPE', 'OTHER', 'SMDL.SID', 'VALUE', 'ID', 'BIO.CELL', 'BIND.RADIOLIGAND', 'BIO.SPECIES', 'SWISSP.ID', 'EXP.LOGKOW', 'MOL.GNAME', 'TARGET.TYPE', 'REC.NAME', 'MOL.SMI', 'Date', 'Wombat structures ID', 'VALUE.MAX', 'EST.LOGWSOL', 'Structure', 'Atoms', 'CdId', 'WOMBAT_ACT_LIST_ID', 'TYPE', 'BIO.TISSUESOURCE', 'EXP.LOGWSOL', 'BIO.STIMULUS', 'SWISSP.SPECIES', 'REC.TYPE', 'TARGET.NAME', 'BIO.EFFECT', 'Assay values', 'Rings', 'MOL.NAME', 'Mol Weight', 'logP / MW', 'REC.FAMILY']

cdId_2 = dt2.getFieldByName("CdId")
# here I intend to retrieve all data. Therefore, I am using a larger than negative ID
qt = QueryTerm(cdId_2.fieldId, ["-1"], ">")

wombat_vs.query([qt], {})
#wombat_vs.query([qt], {})

And why not also retrieve all fields?

wombat_vs.getData([field.fieldId for field in dt2.root.fields])[:2]

output

```python [[3, 'C18H19ClN2O2', 470, 'Bioorg. Med. Chem. Lett. 12(2)-2002 243-248', 8.720000267028809, None, 4.980000019073486, None, 470, 'SMDL-00000470', None, None, 23, '8.7200', '1', 'L929 cell', '[125I]R-91150', 'human', 'P28223', None, 'R-95292', 'receptor', 'serotonergic receptor', 'CN(C)CC1CC2N(O1)c3cc(Cl)ccc3Oc4ccccc24', None, 1, 8.720000267028809, -5.9670000076293945, '$RDFILE 1\n$DATM 2/25/2004 7:34:48\n$MFMT $MIREG 470\n\n -ISIS- 02250407342D\n\n 23 26 0 0 0 0 0 0 0 0999 V2000\n 4.5542 -1.9042 0.0000 N 0 0 3 0 0 0 0 0 0 0 0 0\n 5.3042 -1.9125 0.0000 C 0 0 3 0 0 0 0 0 0 0 0 0\n 4.0792 -2.4917 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.3250 -1.1917 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0\n 5.7625 -2.4917 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.9250 -3.5500 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0\n 4.2500 -3.2167 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 5.5917 -3.2250 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.9375 -0.7500 0.0000 C 0 0 3 0 0 0 0 0 0 0 0 0\n 5.5417 -1.1917 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 3.3667 -2.2667 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 3.7042 -3.7292 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.9417 0.0083 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 2.8250 -2.7792 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.2875 0.3875 0.0000 N 0 0 3 0 0 0 0 0 0 0 0 0\n 2.9917 -3.5125 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 2.1000 -2.5542 0.0000 Cl 0 0 0 0 0 0 0 0 0 0 0 0\n 6.4750 -2.2750 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 6.1417 -3.7375 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 3.6292 0.0083 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.2917 1.1458 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 7.0250 -2.7792 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 6.8625 -3.5167 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 2 1 1 0 0 0 0\n 3 1 1 0 0 0 0\n 4 1 1 0 0 0 0\n 5 2 1 0 0 0 0\n 6 7 1 0 0 0 0\n 7 3 2 0 0 0 0\n 8 5 2 0 0 0 0\n 9 4 1 0 0 0 0\n 10 2 1 0 0 0 0\n 11 3 1 0 0 0 0\n 12 7 1 0 0 0 0\n 13 9 1 0 0 0 0\n 14 11 2 0 0 0 0\n 15 13 1 0 0 0 0\n 16 14 1 0 0 0 0\n 17 14 1 0 0 0 0\n 18 5 1 0 0 0 0\n 19 8 1 0 0 0 0\n 20 15 1 0 0 0 0\n 21 15 1 0 0 0 0\n 22 18 2 0 0 0 0\n 23 22 1 0 0 0 0\n 10 9 1 0 0 0 0\n 8 6 1 0 0 0 0\n 12 16 2 0 0 0 0\n 19 23 2 0 0 0 0\nM END\n', 42, 1, 1, 'IC50', 'cloned', None, None, 'human', None, '5-HT2A', 'antagonist', 'IC50 [5-HT2A] 8.7200\nIC50 [5-HT2C] 8.4200\nIC50 [H1] 6.9900\n', 4, '1', 330.81, 66.42771058895421, 'GPCR'], [3, 'C18H19ClN2O2', 470, 'Bioorg. Med. Chem. Lett. 12(2)-2002 243-248', 8.420000076293945, None, 4.980000019073486, None, 470, 'SMDL-00000470', None, None, 23, '8.4200', '2', 'CHO cell', '[3H]mesulergine', 'human', 'P28335', None, 'R-95292', 'receptor', 'serotonergic receptor', 'CN(C)CC1CC2N(O1)c3cc(Cl)ccc3Oc4ccccc24', None, 1, 8.420000076293945, -5.9670000076293945, '$RDFILE 1\n$DATM 2/25/2004 7:34:48\n$MFMT $MIREG 470\n\n -ISIS- 02250407342D\n\n 23 26 0 0 0 0 0 0 0 0999 V2000\n 4.5542 -1.9042 0.0000 N 0 0 3 0 0 0 0 0 0 0 0 0\n 5.3042 -1.9125 0.0000 C 0 0 3 0 0 0 0 0 0 0 0 0\n 4.0792 -2.4917 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.3250 -1.1917 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0\n 5.7625 -2.4917 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.9250 -3.5500 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0\n 4.2500 -3.2167 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 5.5917 -3.2250 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.9375 -0.7500 0.0000 C 0 0 3 0 0 0 0 0 0 0 0 0\n 5.5417 -1.1917 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 3.3667 -2.2667 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 3.7042 -3.7292 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.9417 0.0083 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 2.8250 -2.7792 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.2875 0.3875 0.0000 N 0 0 3 0 0 0 0 0 0 0 0 0\n 2.9917 -3.5125 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 2.1000 -2.5542 0.0000 Cl 0 0 0 0 0 0 0 0 0 0 0 0\n 6.4750 -2.2750 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 6.1417 -3.7375 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 3.6292 0.0083 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 4.2917 1.1458 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 7.0250 -2.7792 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 6.8625 -3.5167 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0\n 2 1 1 0 0 0 0\n 3 1 1 0 0 0 0\n 4 1 1 0 0 0 0\n 5 2 1 0 0 0 0\n 6 7 1 0 0 0 0\n 7 3 2 0 0 0 0\n 8 5 2 0 0 0 0\n 9 4 1 0 0 0 0\n 10 2 1 0 0 0 0\n 11 3 1 0 0 0 0\n 12 7 1 0 0 0 0\n 13 9 1 0 0 0 0\n 14 11 2 0 0 0 0\n 15 13 1 0 0 0 0\n 16 14 1 0 0 0 0\n 17 14 1 0 0 0 0\n 18 5 1 0 0 0 0\n 19 8 1 0 0 0 0\n 20 15 1 0 0 0 0\n 21 15 1 0 0 0 0\n 22 18 2 0 0 0 0\n 23 22 1 0 0 0 0\n 10 9 1 0 0 0 0\n 8 6 1 0 0 0 0\n 12 16 2 0 0 0 0\n 19 23 2 0 0 0 0\nM END\n', 42, 1, 2, 'IC50', 'cloned', None, None, 'human', None, '5-HT2C', 'antagonist', 'IC50 [5-HT2A] 8.7200\nIC50 [5-HT2C] 8.4200\nIC50 [H1] 6.9900\n', 4, '1', 330.81, 66.42771058895421, 'GPCR']] ```

Now, I would like to take all the data and turn it into pandas dataframe object. (This may take a little while).

wombat_df = pd.DataFrame()
for i, field_name in enumerate([field.name for field in dt2.root.fields]):
    wombat_df[field_name] =  [inner_list[i] for inner_list in wombat_vs.getData([field.fieldId for field in dt2.root.fields])]

Let's keep using 'CdId' as an index column instead of creating new one. Also, let's display first five rows:

wombat_df.set_index('CdId', inplace=True)
wombat_df.head(5)

	Num assay vals	Formula	ID	MOL.REF	VALUE.MIN	BIND.NONSPECIFIC	EST.LOGKOW	BIND.ENDOGENLIGAND	SMDL.ID	SMDL.IDX	...	SWISSP.SPECIES	REC.TYPE	TARGET.NAME	BIO.EFFECT	Assay values	Rings	MOL.NAME	Mol Weight	logP / MW	REC.FAMILY
CdId
1	3	C18H19ClN2O2	1	Bioorg. Med. Chem. Lett. 12(2)-2002 243-248	8.72	None	4.98	None	470	SMDL-00000470	...	human	None	5-HT2A	antagonist	IC50 [5-HT2A] 8.7200\nIC50 [5-HT2C] 8.4200\nIC...	4	1	330.810	66.427711	GPCR
1	3	C18H19ClN2O2	2	Bioorg. Med. Chem. Lett. 12(2)-2002 243-248	8.42	None	4.98	None	470	SMDL-00000470	...	human	None	5-HT2C	antagonist	IC50 [5-HT2A] 8.7200\nIC50 [5-HT2C] 8.4200\nIC...	4	1	330.810	66.427711	GPCR
1	3	C18H19ClN2O2	3	Bioorg. Med. Chem. Lett. 12(2)-2002 243-248	6.99	None	4.98	None	470	SMDL-00000470	...	human	None	H1	antagonist	IC50 [5-HT2A] 8.7200\nIC50 [5-HT2C] 8.4200\nIC...	4	1	330.810	66.427711	GPCR
2	3	C18H20N2	1	Bioorg. Med. Chem. Lett. 12(2)-2002 243-248	7.98	None	3.35	None	471	SMDL-00000471	...	human	None	5-HT2A	antagonist	IC50 [5-HT2A] 7.9800\nIC50 [5-HT2C] 8.1300\nIC...	4	3	264.372	78.917017	GPCR
2	3	C18H20N2	2	Bioorg. Med. Chem. Lett. 12(2)-2002 243-248	8.13	None	3.35	None	471	SMDL-00000471	...	human	None	5-HT2C	antagonist	IC50 [5-HT2A] 7.9800\nIC50 [5-HT2C] 8.1300\nIC...	4	3	264.372	78.917017	GPCR

5 rows × 44 columns

Congratulation !