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
    api = DataApi("username", "password")

    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 !