Bioinformatics Programming
Nested SQL queries and aggregate functions for complex information retrieval from a database
In one of our previous articles, we have mentioned a few basic SQL queries to search in a database. In this article, we have described a few nested SQL queries and aggregate functions for information retrieval from a biological database.
1. To retrieve a limited number of records from a database.
>SELECT TOP number * FROM table_name;
For example, you have a table named sequences in your database having different columns such as OrgName, Sequence, OrgNumber, and so on. Since a single species may consist of multiple sequences and you want to retrieve only the top 3 sequences of that species, then use the following query.
>SELECT TOP 3 * FROM sequences;
In MySQL, the LIMIT clause does the same thing,
>SELECT column_names * FROM table_name
WHERE condition LIMIT number;
For example,
>SELECT * FROM sequences
WHERE OrgName=’Arabidopsis thaliana’
LIMIT 3;
2. To retrieve the minimum and maximum values from the selected column
To find the minimum value
>SELECT MIN(column_name) FROM table_name
WHERE condition;
For example,
>SELECT MIN(strains) AS MinimumStrains
FROM sequences;
To find the maximum value
>SELECT MAX(column_name) FROM table_name
WHERE condition;
For example,
>SELECT MAX(strains) AS MinimumStrains
FROM sequences
WHERE OrgName=’Arabidopsis thaliana’;
3. Aggregate functions in SQL
To find the number of rows matching a condition
>SELECT COUNT(column_name)
FROM table_name
WHERE condition;
For example,
>SELECT COUNT(OrgNumber)
FROM sequences;
To find the average value of a numeric column in a table
>SELECT AVG(column_name)
FROM table_name
WHERE condition;
For example,
>SELECT AVG(TotSeqs)
FROM sequences;
To find the sum of a numeric column
>SELECT SUM(column_name)
FROM table_name
WHERE condition;
For example,
>SELECT SUM(TotSeqs)
FROM sequences;
4. To search for a specific pattern in a column
>SELECT column1, column2, …
FROM table_name
WHERE column_name LIKE pattern;
There are several other wildcards that can be used with LIKE operator such as ‘*’, ‘[]’, ‘^’, etc. They can be used in combination as well. There are two wildcards mostly used with LIKE operator: ‘%’ and ‘_’.
% represents zero, one, or multiple characters, whereas, _ represents a single character. These wildcards are used before and/or after the specific characters or a pattern you are looking for.
For example, if you wish to search for sequences of a species using a few characters, then use the following query.
>SELECT Sequence, OrgName
FROM sequences
WHERE OrgName LIKE ‘%dopsis%’;
>SELECT Sequence, OrgName
FROM sequences
WHERE OrgName LIKE ‘%thaliana’;
>SELECT Sequence, OrgName
FROM sequences
WHERE OrgName LIKE ‘_ _abidop%’;
5. To specify multiple values in a WHERE clause
>SELECT column1, column2, …
FROM table_name
WHERE column_name IN (value1, value2, …);
For example,
>SELECT * FROM sequences
WHERE OrgName IN (‘Arabidopsis thaliana’, ‘Agrocybe aegerita’, ‘Homo sapiens’);
Or instead of entering values, SELECT statement can also be used as shown below,
>SELECT * FROM sequences
WHERE OrgName IN (SELECT statement);
If you wish to select records that are not present in the given values, then use the following query.
>SELECT * FROM sequences
WHERE OrgName NOT IN (‘Arabidopsis thaliana’, ‘Agrocybe aegerita’, ‘Homo sapiens’);
6. To select records within a given range in a table
>SELECT column1, column2, …
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
For example,
>SELECT * FROM sequences
WHERE OrgNumber BETWEEN 102 AND 130;
Other SQL queries for complex information retrieval from a database will be described in upcoming articles.
Bioinformatics Programming
DockingAnalyzer.py: A New Python script to Identify Ligand Binding in Protein Pockets.
High-throughput virtual screening (HTVS) is a pivotal technique in drug discovery that screens extensive libraries of compounds to find potential drug candidates. One of the essential tasks in HTVS is to ensure that ligands are binding within the protein’s binding pocket. This task can be particularly challenging when dealing with thousands of docking results. To address this challenge, we present a Python script that automates the analysis of molecular docking results generated by AutoDock Vina [1] using PyMOL [2]. This script calculates the center of mass (COM) for each docked pose, compares it with a reference ligand’s COM, and identifies poses that bind within a specified threshold distance. This process is crucial in mass docking scenarios where confirming ligand binding within the pocket is necessary. (more…)
Bioinformatics Programming
How to copy and rename files simultaneously in same directory in Ubuntu (Linux)?
Copying and renaming files from one destination to another is easily accomplished by the cp command in Linux. However, it becomes more complex when we want to copy and rename all files in the same directory while assigning a serial number to each copy. In this article, we provide a shell script to copy and rename files present in a directory, adding a unique serial number to each copy.
Bioinformatics Programming
Free_Energy_Landscape-MD: Python package to create Free Energy Landscape using PCA from GROMACS.
In molecular dynamics (MD) simulations, a free energy landscape (FEL) serves as a crucial tool for understanding the behavior of molecules and biomolecules over time. It is difficult to understand and plot a meaningful FEL and then extract the time frames at which the plot shows minima. In this article, we introduce a new Python package (Free_Energy_Landscape-MD) to generate an FEL based on principal component analysis (PCA) from MD simulation done by GROMACS [1].
Virtual screening (VS) is a crucial aspect of bioinformatics. As you may already know, there are various tools available for this purpose, including both paid and freely accessible options such as Autodock Vina. Conducting virtual screening with Autodock Vina requires less effort than analyzing its results. However, the analysis process can be challenging due to the large number of output files generated. To address this, we offer a comprehensive Python package designed to automate the analysis of virtual screening results.
Bioinformatics Programming
vs_interaction_analysis.py: Python script to perform post-virtual screening analysis
Analyzing the results of virtual screening (VS) performed with Autodock Vina [1] can be challenging when done manually. In earlier instances, we supplied two scripts, namely vs_analysis.py [2,3] and vs_analysis_compounds.py [4]. This time, we have developed a new Python script to simplify the analysis of VS results.
In this article. we are creating a pie chart of the docking score of five different compounds docked with the same protein. (more…)
With the new year, we are going to start with a very simple yet complicated topic (for beginners) in bioinformatics. In this tutorial, we provide a simple code to plot swarm boxplot using matplotlib and seaborn. (more…)
Previously, we provided a similar script to download ligand SMILES from PDB ligand IDs. In this article, we are downloading PDB ligand structures from their corresponding IDs. (more…)
Fetching SMILE strings for a given number of SDF files of chemical compounds is not such a trivial task. We can quickly obtain them using RDKit or OpenBabel. But what if you don’t have SDF files of ligands in the first place? All you have is Ligand IDs from PDB. If they are a few then you can think of downloading SDF files manually but still, it seems time-consuming, especially when you have multiple compounds to work with. Therefore, we provide a Python script that will read all Ligand IDs and fetch their SDF files, and will finally convert them into SMILE strings. (more…)
Bioinformatics Programming
How to get secondary structure of multiple PDB files using DSSP in Python?
In this article, we will obtain the secondary structure of multiple PDB files present in a directory using DSSP [1]. You need to have DSSP installed on your system. (more…)
Bioinformatics Programming
vs_analysis_compound.py: Python script to search for binding affinities based on compound names.
Previously, we have provided the vs_analysis.py script to analyze virtual screening (VS) results obtained from Autodock Vina. In this article, we have provided another script to search for binding affinity associated with a compound. (more…)
In this article, we provide a simple Python script to download files from an FTP server using Python. (more…)
VMD allows converting PDB to PSF format but sometimes it gives multiple errors. Therefore, in this article, we are going to convert PDB into PSF format using a different method. (more…)
As mentioned in some of our previous articles, RDKit provides a wide range of functions. In this article, we are using RDKit [1] to draw a molecular structure using SMILES. (more…)
Data preprocessing is a foremost and essential step in clustering based on machine learning methods. It removes noise and provides better results. In this article, we are going to discuss the steps involved in data preprocessing using MATLAB [1]. (more…)
RDKit [1] allows performing multiple functions on chemical compounds. One is the quantitative estimation of drug-likeness also known as QED properties. These properties include molecular weight (MW), octanol-water partition coefficient (ALOGP), number of hydrogen bond donors (HBD), number of hydrogen bond acceptors (HBA), polar surface area (PSA), number of rotatable bonds (ROTB), number of aromatic rings (AROM), structural alerts (ALERTS). (more…)
You can obtain SMILES of multiple compounds or ligands in an SDF file in one go. Here, we provide a simple Python script to do that. (more…)
Bioinformatics Programming
tanimoto_similarities_one_vs_all.py – Python script to calculate Tanimoto Similarities of multiple compounds
We previously provided a Python script to calculate the Tanimoto similarities of multiple compounds against each other. In this article, we are providing another Python script to calculate the Tanimoto similarities of one compound with multiple compounds. (more…)
Bioinformatics Programming
tanimoto_similarities.py: A Python script to calculate Tanimoto similarities of multiple compounds using RDKit.
RDKit [1] is a very nice cheminformatics software. It allows us to perform a wide range of operations on chemical compounds/ ligands. We have provided a Python script to perform fingerprinting using Tanimoto similarity on multiple compounds using RDKit. (more…)
In this article, we are providing a few commands that are used to commit changes to GitHub repositories using VS code terminal.
You must be logged in to post a comment Login