Skip to content

Advanced Terminal Tools

This page covers more advanced terminal tools that go beyond the core course material. These tools are available on the GDC server and are particularly useful for students who want to go further with terminal-based data analysis.

Spreadsheet Manipulations

In biology and other scientific fields, data is often stored in spreadsheets. While many people use Microsoft Excel for this, relying on spreadsheet editors can be problematic. Spreadsheets are not well suited to large datasets, can be slow and prone to errors from manual manipulation, and do not support reproducibility. The terminal offers a more reliable and transparent alternative.

In this session, we look at the basics of working with spreadsheet data using csvtk, a toolkit for working with CSV files (Comma-Separated Values) from the terminal. csvtk can convert Excel files to CSV, extract specific data, merge tables, create plots, and run basic statistical analyses.

(1) Download a dummy spreadsheet file.

cd ${HOME}
curl -O https://www.gdc-docs.ethz.ch/GeneticDiversityAnalysis/GDA/data/TestFile.xlsx

You can inspect the file, but it will not be human-readable in its current Excel format:

head TestFile.xlsx

(2) Convert the Excel file to CSV format using csvtk.

csvtk xlsx2csv -a TestFile.xlsx

This shows all the sheets in the Excel file. You might see something like:

index   sheet
1       weight
2       size
3       count

(3) Extract each sheet as a separate CSV file.

csvtk xlsx2csv -i 1 -o TestFile_GW.csv TestFile.xlsx
csvtk xlsx2csv -i 2 -o TestFile_S.csv  TestFile.xlsx
csvtk xlsx2csv -i 3 -o TestFile_C.csv  TestFile.xlsx

(4) Combine the sheets into a single file.

csvtk join TestFile_GW.csv TestFile_S.csv TestFile_C.csv > TestFile_GWSC.csv

(5) Display the data in a clear, well-formatted view.

csvtk pretty -S 3line -m Sample TestFile_GWSC.csv

(6) Create a box plot showing body size differences by sex.

csvtk plot box TestFile_GWSC.csv -g Sex -f Size --height 3 --width 5 --horiz --title "Body Size Difference" --xlab "Size" --ylab "Sex" > box_size.png
# Get a local copy of the plot:
# scp guest??@gdc-vserver.ethz.ch:/home/guest??/box_size.png .

(7) Create a scatter plot showing the relationship between weight and size.

csvtk plot line TestFile_GWSC.csv -x "Weight" -y "Size" -g Sex --title "Scatter" --scatter > scatter_weight.png

csvtk can also calculate correlations between variables.

csvtk corr -f 3,4 TestFile_GWSC.csv  # correlation between weight and size
csvtk corr -f 4,5 TestFile_GWSC.csv  # correlation between weight and count
csvtk corr -f 3,5 TestFile_GWSC.csv  # correlation between size and count

Using terminal tools like csvtk helps you handle spreadsheet data safely, efficiently, and in a way that is easy to reproduce and share with others.

Using Primer3 for Finding PCR Primers

Rather than searching for an existing primer sequence, Primer3 allows you to design new primer sequences from a template. This is a crucial step in many molecular biology workflows.

(1) Download the Primer3 settings template.

curl -O https://www.gdc-docs.ethz.ch/GeneticDiversityAnalysis/GDA/data/PCR.settings.template
cat PCR.settings.template

(2) Create a file with your query sequence formatted for Primer3.

echo -n "SEQUENCE_TEMPLATE="      > add.tmp           # Add ID tag
bash SingleFasta.sh S000444351.fa > S000444351.fasta  # Remove end-of-lines
grep -v ">" S000444351.fasta      >> add.tmp          # Extract sequence without FASTA header
echo "=" >> add.tmp                                   # Ensure the file ends with an equal sign

(3) Add your query sequence to the Primer3 settings.

cat PCR.settings.template add.tmp > PCR.settings

(4) Run Primer3 to find PCR primers.

primer3_core --default_version=1 --format_output --output primers.txt < PCR.settings

(5) Check the output file for primer sequences.

less primers.txt
# Use [q] to quit the less viewer

Using infoseq for Sequence Information

The infoseq tool from the EMBOSS package provides detailed information about sequence data, including length, GC content, and composition.

(1) Check that infoseq is available and display basic usage information.

which infoseq
infoseq --help

(2) Report the length of each sequence in the file.

infoseq -only -length -nocolumns RDP_16S_Archaea_Subset.fasta > L.tmp
less L.tmp
# Note: "-only -length" restricts the output to sequence lengths

(3) Find the length range.

sort -n L.tmp | head # lower range
sort -n L.tmp | tail # upper range
csvtk plot box L.tmp > box_size.png

❖ Challenge: Find the range of percent GC content for the RDP sequences.

Insights 
## Calculate percent GC content
infoseq -only -pgc -nocolumns RDP_16S_Archaea_Subset.fasta | sort -n > pGC.tmp
# -pgc: calculate percent GC content (see infoseq --help for details)
# -n: sort numerically

## Find max and min percent GC
head -n 2 pGC.tmp  # smallest percent GC content
tail -n 1 pGC.tmp  # largest percent GC content
csvtk plot box pGC.tmp > box_pgc.png

Helpful Resources