GeinQSAR Studio
Home › The molecule spreadsheet

The molecule spreadsheet

The central view of a dataset: one row per compound with its structure, identity, set and values, and the tools to sort, filter, search, edit and select.

Every dataset opens as a spreadsheet. It draws the structure of every compound, holds its name, identifier, set and flag, and has one column for each activity, imported property, descriptor and formula. Only the rows on screen are drawn, so a table of a hundred thousand compounds scrolls as smoothly as one of thirty.

The steroid sample as a spreadsheet. The bar above the table holds the Search, SMARTS filter and Similar to (SMILES) boxes, the clear button, and the Structures and Filters toggles. The columns are Structure, ID, Name, Set (all Training here), Flag, the two activities pK_CBG and pK_TBG, and the imported text column structure_source. The status bar reads 31 rows · 10 columns.
The steroid sample as a spreadsheet. The bar above the table holds the Search, SMARTS filter and Similar to (SMILES) boxes, the clear button, and the Structures and Filters toggles. The columns are Structure, ID, Name, Set (all Training here), Flag, the two activities pK_CBG and pK_TBG, and the imported text column structure_source. The status bar reads 31 rows · 10 columns.

The columns

ColumnContentEditing
StructureThe 2D depiction, with wedges for stereocentres. A red cross marks a row without a valid structure.Double-click to edit the structure in the sketcher.
ID, NameThe identity of the compound.In place.
SMILESThe canonical SMILES. Hidden by default; show it with Data > Columns.Follows the structure.
SetTraining, Test, External or Excluded as a coloured chip (blue, vermilion, green, grey, from a palette that colour-blind readers can tell apart); empty when unassigned.Through the set commands, the Inspector or the context menu.
FlagA free mark, for outliers, compounds to re-measure or to discuss.Click the box, or Toggle flag.
ActivityThe measured response, with its unit in the tooltip of the header.In place.
ImportedEvery other column of the file.In place.
DescriptorsComputed columns, grouped by family in the Project panel.Read only; recompute after a structure changes.
FormulaColumns computed from other columns (see Formula columns).Through the formula editor.
Match, SimilarityAdded by the SMARTS filter and the similarity search.Read only.

To edit a cell, double-click it, press Enter or F2, or simply start typing on the selected cell. An edit recomputes the formula columns at once. There is no undo in the spreadsheet, so save the project or export the dataset before a large change.

Order, width and freezing

The order of the columns is their order everywhere: in the column manager, in the Project panel and in an exported file. Drag a header to move a column. Drag the right edge of a header to change the width, double-click the edge to fit it to the content, or use Data > Auto-fit columns for all of them. The identity columns at the left (Structure, ID and Name) stay in view while you scroll sideways; Freeze up to here in the header menu moves that boundary, and Unfreeze columns removes it.

Structures in the toolbar (or View > Show structures) hides the drawings and shrinks the rows, so that about three times as many fit on the screen. Use it to scan values; the structures return with the next click.

The same table with Structures switched off. The rows are a single line high, twelve compounds are visible instead of four, and the numeric columns can be read down the page.
The same table with Structures switched off. The rows are a single line high, twelve compounds are visible instead of four, and the numeric columns can be read down the page.

Sorting

Click a header to sort by it: the first click sorts ascending, the second descending, the third removes the sort. Hold Shift while clicking another header to add it as a second key, for example the set first and the activity within each set. Text is sorted in natural order, so that C2 comes before C10. The header menu offers Sort ascending, Sort descending and Clear sort. Sorting never changes the stored order of the rows.

Selecting rows

Click a row to select it, Ctrl+click to add or remove one, Shift+click to select a range. Edit > Select all (Ctrl+A) selects every row the table shows, which with a filter active means the rows that pass it; Invert selection (Ctrl+Shift+A) works on the same rows, and Ctrl+D clears the selection. A click on a set in the Project panel selects its rows.

The selection belongs to the dataset, so it is shared with the 3D workspace and the Inspector: select compounds in the table and the 3D view shows the same ones. The program never selects rows by itself; an import, a paste or a calculation leaves the selection as it was, and deleting rows leaves nothing selected.

Search and filters

ToolWhat it doesWhen to use it
Search (Ctrl+F)Keeps the rows in which any visible column contains the text, as you type.To find a compound by name or ID.
SMARTS filterKeeps the compounds that contain a substructure. Applied with Enter or when the box loses focus. Adds a Match column.To look at a chemotype: all carboxylic acids, all compounds with a basic amine.
Similar to (SMILES)Adds a Similarity column, the Tanimoto coefficient on ECFP4 fingerprints of 1024 bits, and sorts the table with the most similar compound first. Applied with Enter.To find the near neighbours of a query, for example the analogues of a new hit.
FiltersShows a filter row under the headers, one box per column.To restrict by value: pIC50 above 7, MW between 300 and 500, no missing logP.
Clear button (the cross)Clears the search, the SMARTS filter, the similarity query and every column filter at once. Chemistry > Clear structure filters does the same.

The filter row

Each box of the filter row takes an expression for its column. All boxes apply together.

ExpressionKeepsExample
textValues that contain it (any case)sterone
=text, !=textValues equal, or not equal, to it=S01
!textValues that do not contain it!acetate
^text, text$Values that start or end with it^11-, one$
* and ?A pattern over the whole valueS1?
>, >=, <, <=, =, != with a numberNumbers compared with it>6.5
a..bNumbers from a to b inclusive5..7
empty, !emptyMissing, or present, values!empty
true, false (also yes/no, 1/0)Ticked or unticked boxes, for Flag and Matchtrue
The filter row shown under the headers, with an empty Filter box for each column. The four of the six compounds of the test table are in view, two of them highlighted; the Inspector summarises all four selected compounds: four with a structure, two training, one test, one external.
The filter row shown under the headers, with an empty Filter box for each column. The four of the six compounds of the test table are in view, two of them highlighted; the Inspector summarises all four selected compounds: four with a structure, two training, one test, one external.

Substructure filter

Type a SMARTS pattern and press Enter. The search runs in the background on every structure; the table then shows only the matches and the status bar says how many there are, for example "4 compounds match c1ccccc1". The Match column holds the result for every row, so it can be sorted, exported or used in a formula. An invalid pattern is reported in a notification and in the tooltip of the box. Emptying the box removes the filter.

The SMARTS filter c1ccccc1 applied to the test table: benzene, aspirin, phenol and toluene remain. The status bar at the bottom reports 4 compounds match c1ccccc1 and 6 rows (4 shown).
The SMARTS filter c1ccccc1 applied to the test table: benzene, aspirin, phenol and toluene remain. The status bar at the bottom reports 4 compounds match c1ccccc1 and 6 rows (4 shown).

Useful patterns: C(=O)[OH] carboxylic acids, [NX3;H2,H1;!$(NC=O)] primary and secondary amines that are not amides, c1ccc2[nH]ccc2c1 indoles, [F,Cl,Br,I] any halogen, [OX2H] hydroxyl groups.

Similarity search

Type or paste a SMILES into Similar to (SMILES) and press Enter. The Similarity column is filled with values from 0 to 1, the table is sorted by it with the most similar compound first, and the Inspector shows the distribution. A Tanimoto coefficient on ECFP4 of about 0.4 or more usually indicates a related compound; identical structures score 1. The search does not filter; combine it with a filter on the Similarity column (>=0.4) to keep the neighbours only.

The steroids sorted by similarity to aldosterone. Aldosterone itself is first, corticosterone second; the status bar names the query, and the Inspector, with six rows selected, ends with the statistics of the Similarity column.
The steroids sorted by similarity to aldosterone. Aldosterone itself is first, corticosterone second; the status bar names the query, and the Inspector, with six rows selected, ends with the statistics of the Similarity column.

The context menu

Right-click a row for the commands on the rows, right-click a header for the commands on the column. A right click on a row that is not selected selects it first, so the menu always acts on what you see highlighted.

The context menu of a row. The row commands come first (Copy, Copy with headers, Paste SMILES, Edit structure, Show in 3D, Assign to set, Toggle flag, Delete rows), then the column commands for the column under the pointer, then the table commands. Statistics is greyed because it needs a numeric column. Row 4 shows the red cross of a structure that could not be read.
The context menu of a row. The row commands come first (Copy, Copy with headers, Paste SMILES, Edit structure, Show in 3D, Assign to set, Toggle flag, Delete rows), then the column commands for the column under the pointer, then the table commands. Statistics is greyed because it needs a numeric column. Row 4 shows the red cross of a structure that could not be read.
EntryWhat it does
Copy, Copy with headersThe selected rows as tab-separated text (Ctrl+C, Ctrl+Shift+C), ready to paste into another program or a document.
Paste SMILESAppends the SMILES list on the clipboard (Ctrl+V): one structure per line, an optional name after a space, tab, comma or semicolon. Lines that do not parse are reported and not added.
Edit structureOpens the sketcher on the row.
Show in 3DOpens the 3D workspace of the dataset with the selection.
Assign to setTraining, Test, External, Excluded or Unassigned.
Toggle flagFlags the rows, or unflags them if all were flagged.
Delete rowsRemoves the rows (Delete).
Sort ascending, Sort descending, Clear sortSort by the column.
Freeze up to here, Unfreeze columnsKeep the columns up to this one in view while scrolling sideways.
Auto-fit width, Hide columnFit the column to its content, or hide it (it stays in the dataset and in the column manager).
StatisticsThe statistics dialog for a numeric column.
Edit formulaFor a formula column, its editor.
Remove columnDeletes the column from the dataset. Identity columns cannot be removed.
Select all, ColumnsSelect every shown row; open the column manager.
Filter row, Summary rowShow the filter row, or a summary row with the mean of every activity and descriptor column.
Clear filtersShown while a filter is active.

The column manager

Data > Columns (or Columns on the Data tab) lists every column of the dataset, hidden ones included, in table order. It is the place to tidy a table before a figure or an export.

The column manager. Each row has the visibility box, the title, the group (Identity, Activity, Imported, Search), the units, the number of decimals for numeric columns, and the buttons to move the column up or down or remove it. SMILES is unticked, so it is hidden in the table; the identity columns have no remove button.
The column manager. Each row has the visibility box, the title, the group (Identity, Activity, Imported, Search), the units, the number of decimals for numeric columns, and the buttons to move the column up or down or remove it. SMILES is unticked, so it is hidden in the table; the identity columns have no remove button.
ControlEffect
Find a columnNarrows the list to titles or groups that contain the text; with hundreds of descriptors, type a family name.
Show all, Hide allShow every column, or hide all except Structure, ID and Name.
BoxShow the column in the spreadsheet.
Title, UnitsRename the column or set its unit. Formulas that read the column keep working.
DecimalsFrom 0 to 9; changes the display and the exported text, not the stored value.
ArrowsMove the column up or down, which moves it left or right in the table.
GearFor a formula column, edit the formula.
BinRemove the column. Formula columns that read it then show empty cells.

Drawing and editing structures

Chemistry > Edit structure, a double click on a structure cell, or Edit structure in the context menu opens the sketcher on that compound; Chemistry > Draw a new compound opens it empty. The dialog has a Name box, the toolbar, the drawing area and, underneath, the SMILES of the drawing with a Copy SMILES button. Apply (or Add compound for a new one) writes the structure, its canonical SMILES and a new depiction into the row.

The top of the Edit structure dialog. The first row holds the tools (Sel, Atom, Bond, Chain, Ring, Tpl, Wedge, Hash, Wavy, +/-, R, Del), the second the elements with More for the periodic table, the third the bond orders, the ring sizes R3 to R7, Ph and the template list, and the fourth undo, redo, Clean, Copy, Paste, Fit and the select and pan modes.
The top of the Edit structure dialog. The first row holds the tools (Sel, Atom, Bond, Chain, Ring, Tpl, Wedge, Hash, Wavy, +/-, R, Del), the second the elements with More for the periodic table, the third the bond orders, the ring sizes R3 to R7, Ph and the template list, and the fourth undo, redo, Clean, Copy, Paste, Fit and the select and pan modes.
ControlUse
SelSelect and move atoms.
AtomPlace atoms of the current element.
BondDraw bonds of the current order.
ChainDrag out a carbon chain.
Ring, R3 to R7, PhPlace, attach or fuse a ring of that size; Ph is a benzene ring.
Tpl and the listPlace a template: benzene, pyridine, pyrimidine, the cycloalkanes from three to eight members, cyclopentadiene, naphthalene, indole, quinoline, purine, piperidine, piperazine, morpholine, the steroid skeleton or adamantane.
Wedge, Hash, WavyStereo bonds: solid wedge (narrow end at the stereocentre), hashed wedge, and the wavy bond for an unspecified configuration.
+/-Charge: a click adds one, a right click subtracts one.
RAn R-group attachment point.
DelErase atoms and bonds.
Elements, MoreC, N, O, S, P, F, Cl, Br, I, H, and the full periodic table.
1, 2, 3Bond order.
CleanTidy the layout (Ctrl+L).
Copy, PasteCopy the SMILES; paste a SMILES or a MOL block.
FitFit the drawing to the area (Ctrl+0).
Pointer and handThe left mouse button selects, or pans the drawing.

With the pointer over an atom, the keys C N O S P F I H, L (Cl) and B (Br) set its element and + and - its charge; over a bond, 1 2 3 set its order. Ctrl+Z and Ctrl+Y undo and redo inside the sketcher.

Descriptor columns are not recomputed when a structure changes, because a block of thousands of columns would be recomputed for one edit. Run the descriptor command again when you have finished editing. Formula columns follow at once.

Sets and flags

The Set of a compound decides its role in modelling. Training compounds fit the model, Test compounds validate it, External compounds are kept out of model building and splitting altogether and serve as an external validation set, and Excluded compounds are ignored. To assign the selected rows, use Data > Assign to training set, Assign to test set, Assign to external set, Exclude from modelling or Clear set membership, the Training and Test buttons of the Home tab, the context menu, or the Inspector. A designed split of the whole dataset is made with Data > Training/test split (see Preparing the data).

The flag has no effect on modelling. Use it to mark compounds for yourself, then filter the Flag column on true to see them together.

An empty dataset

File > New dataset opens an empty spreadsheet whose toolbar offers the three ways to fill it: Import, Paste SMILES and Draw. A filter that matches nothing says "No compound matches the filter" instead of "No compounds", so an empty table is never mistaken for an empty dataset.

A new, empty dataset. Under the search bar are the Import, Paste SMILES and Draw buttons; the table shows its five standard columns and the words No compounds, and the Inspector reports 0 compounds.
A new, empty dataset. Under the search bar are the Import, Paste SMILES and Draw buttons; the table shows its five standard columns and the words No compounds, and the Inspector reports 0 compounds.