Datamash Examples - GNU Project - Free Software Foundation

GNU datamash - Examples

GNU datamash is command-line program which performs simple calculation (e.g. count, sum, min, max, mean, stdev, string coalescing) on input files.

# sum the values in the first column
$ seq 10 | datamash sum 1
55

Datamash has a rich set of statistical functions, to quickly assess information in textual input files. An example of calculating basic statistic (mean, 1st quartile, median, 3rd quartile, IQR, sample-standard-deviation, and p-value of Jarque-Bera test for normal distribution:

$ datamash -H mean 1 q1 1 median 1 q3 1 iqr 1 sstdev 1 jarque 1 < FILE.TXT
mean(x) q1(x) median(x) q3(x) iqr(x) sstdev(x) jarque(x)
45.32 23 37 61.5 38.5 30.4487 8.0113e-09

Topics

Example files
Example: Test Scores
Example: Header Lines
Example: Genes
Example: Datamash with other unix programs
Example: Grouping Multiple Fields
Example: Descriptive Statistics
Example: Transpose File (swap rows, columns)
Example: Reverse fields

Example Files

The input files used in the following examples are distributed with datamash's source code, and can also be downloaded directly: scores.txt, scores_h.txt, genes.txt, genes_h.txt (The "_h" files have an additional header line as the first line of the file).

When datamash is locally installed, example files are typically in /usr/share/datamash/examples or /usr/local/share/datamash/examples.

Example: Test Scores

The file scores.txt contains tests scores of college students of different majors (Arts, Business, Health and Medicine, Life Sciences, Engineering, Social Sciences).

The files has three columns: Name, Major, Score:

$ cat scores.txt
Shawn Arts 65
Marques Arts 58
Fernando Arts 78
Paul Arts 63
Walter Arts 75
...

Using datamash, find the lowest (min) and highest (max) score for each College Major (Major is in column 2, the score values are in column 3):

$ datamash -g 2 min 3 max 3 < scores.txt
Arts 46 88
Business 79 94
Health-Medicine 72 100
Social-Sciences 27 90
Life-Sciences 14 91
Engineering 39 99

Similarly, find the number of students, mean score and sample-standard-deviation for each College major:

$ datamash -g 2 mean 3 sstdev 3 < scores.txt
Arts 68.9474 10.4215
Business 87.3636 5.18214
Health-Medicine 90.6154 9.22441
Social-Sciences 60.2667 17.2273
Life-Sciences 55.3333 20.606
Engineering 66.5385 19.8814

Example: Header Lines

A header line is an optional first line in the input or output files, which labels each column. Datamash can generate header line in the output file, even if the input file doesn't have a header line (scores.txt does not have a header line, the first line in the file contains data).

Use --header-out to add a header line to the output (when the input does not contain a header line):

$ datamash --header-out -g 2 count 3 mean 3 pstdev 3 < scores.txt
GroupBy(field-2) mean(field-3) sstdev(field-3)
Arts 68.9474 10.4215
Business 87.3636 5.18214
Health-Medicine 90.6154 9.22441
Social-Sciences 60.2667 17.2273
Life-Sciences 55.3333 20.606
Engineering 66.5385 19.8814

When the input file has a header line, datamash will use the names from each column in the output header line. scores_h.txt contains the same information as scores.txt, with an additional header line:

$ cat scores_h.txt
Name Major Score
Shawn Arts 65
Marques Arts 58
Fernando Arts 78
Paul Arts 63
Walter Arts 75
...

Use -H (equivalent to --header-in --header-out) to use input headers and print output headers:

$ datamash -H -g 2 count 3 mean 3 pstdev 3 < scores_h.txt
GroupBy(Major) mean(Score) sstdev(Score)
Arts 68.9474 10.4215
Business 87.3636 5.18214
Health-Medicine 90.6154 9.22441
Social-Sciences 60.2667 17.2273
Life-Sciences 55.3333 20.606
Engineering 66.5385 19.8814

Example: Genes

note: The following examples assume basic bioinformatics knowledge.

The genes.txt file contains a small subset of the Human Genome genes. The columns of genes.txt are:

bin
name - isoform/transcript identifier
chromosome
strand
txStart - transcription start site
txEnda - transcription end site
cdsStart - coding start site
cdsEnd - coding end site
exonCount - number of exons
exonStarts
exonEnds
score
GeneName - gene identifier
cdsStartStat
cdsEndStat
exonFrames

The list is compiled by the UCSC Genome Browser Project, part of of Center for Biomolecular Science and Engineering (CBSE) at the University of California Santa Cruz (UCSC).

Find Number of isoforms per gene

The gene identifiers are in column 13, the transcript identifiers are in column 2. To count how many isoforms each gene has, use datamash to group by column 13, and for each group, count the values in column 2 (use -s to automatically sort the input file):

$ datamash -s -g 13 count 2 < genes.txt
ABCC1 1
ABCC10 2
ABCC11 3
ABCC12 1
ABCC13 2
...

Using the collapse operation, datamash can print all the isoforms for each gene:

$ datamash -s -g 13 count 2 collapse 2 < genes.txt
ABCC1 1 NM_004996
ABCC10 2 NM_001198934,NM_033450
ABCC11 3 NM_032583,NM_033151,NM_145186
ABCC12 1 NM_033226
ABCC13 2 NR_003087,NR_003088
...

When using a file with a header line, add -H:

$ datamash -H -s -g 13 count 2 collapse 2 < genes_h.txt
GroupBy(name2) count(name) collapse(name)
ABCC1 1 NM_004996
ABCC10 2 NM_001198934,NM_033450
ABCC11 3 NM_033151,NM_145186,NM_032583
ABCC12 1 NM_033226
ABCC13 2 NR_003088,NR_003087
...

Datamash with other unix programs

Datamash is designed as a unix filter program, reading from standard input and writing to standard output. It works well with the common unix programs (such as awk) for additional functionality:

Find genes with more than 5 isoforms:

 $ cat genes.txt | datamash -s -g 13 count 2 collapse 2 | awk '2ドル>5'
AC159540.1 6 NR_040097,NR_103732,NR_103733,NR_040097,NR_103732,NR_103733
ACSF3 6 NM_001127214,NM_001243279,NM_001284316,NM_174917,NR_045667,NR_104293
ADAM29 7 NM_001130703,NM_001130704,NM_001130705,NM_001278125,NM_001278126,NM_001278127,NM_014269
AIPL1 8 NM_001033054,NM_001033055,NM_001285399,NM_001285400,NM_001285401,NM_001285402,NM_001285403,NM_014336
ANXA8 6 NM_001040084,NM_001271702,NM_001271703,NM_001040084,NM_001271702,NM_001271703
...

Which genes are transcribes from both strands?
(that is, they have isoforms with both positive and negative strands. strand column is number 4)

$ cat genes.txt | datamash -s -g 13 countunique 4 | awk '2ドル>1'
AC159540.1 2
AMY1C 2
ANXA8 2
BMS1P17 2
BMS1P18 2
...

Which genes are transcribes from multiple chromosomes?
(that is, they have isoforms from multiple chromosomes. Chromosome column is number 3):

$ cat genes.txt | datamash -s -g 13 countunique 3 unique 3 | awk '2ドル>1'
AKAP17A 2 chrX,chrY
ASMT 2 chrX,chrY
ASMTL 2 chrX,chrY
ASMTL-AS1 2 chrX,chrY
BMS1P17 2 chr14,chr22
...

Examine Exon-count variability
(for each gene, list the minimum, maximum, mean and stddev of the exon-count of its isoforms. Exon-Count column is number 9):

$ cat genes.txt | datamash -s -g 13 count 9 min 9 max 9 mean 9 pstdev 9 | awk '2ドル>1'
ABCC10 2 20 22 21 1
ABCC11 3 29 30 29.3 0.471405
ABCC13 2 5 6 5.5 0.5
ABCC3 2 12 31 21.5 9.5
AC159540.1 6 4 5 4.1 0.372678
...

Example: Grouping multiple fields

Chromosome name is in column 3. How many transcripts are in each chromosome?

$ datamash -s -g 3 count 2 < genes.txt
chr1 365
chr10 164
chr11 189
chr12 187
chr13 66
...

Strand information is in column 4. How many transcripts are in each chromsome AND strand?
To find out, group by two columns: Column 3 (chromosome name) and Column 4 (strand):

$ datamash -s -g 3,4 count 2 < genes.txt
chr1 - 183
chr1 + 182
chr10 - 52
chr10 + 112
chr11 - 105
chr11 + 84
chr12 - 117
chr12 + 70
...

Example: Descriptive Statistics

Calculating the Five-Number Summary: of all values in the first column of the input file:

$ datamash min 1 q1 1 median 1 q3 1 max 1 < FILE.TXT
78 93 100 107 120

The same command, with header lines for better clarity:

$ datamash -H min 1 q1 1 median 1 q3 1 max 1 < FILE.TXT
min(x) q1(x) median(x) q3(x) max(x)
78 93 100 107 120

Finding out the count,mean and sample standard-deviation:

$ datamash -H count 1 mean 1 sstdev 1 < FILE.TXT
count(x) mean(x) sstdev(x)
100 100.06 9.5767184

Example: Transpose File (swap rows, columns)

Use transpose to swap rows and columns in a file:

$ cat input.txt
Sample Year Count
id-123 2014 1002
id-99 2013 990
id-42 2014 2030
id-13 2014 599
$ datamash transpose < input.txt
Sample id-123 id-99 id-42 id-13
Year 2014 2013 2014 2014
Count 1002 990 2030 599

By default, transpose verifies the input has the same number of fields in each line, and fails with an error otherwise:

$ cat input1.txt
Sample Year Count
id-123 2014 1002
id-99 2013
id-42 2014 2030
id-13 2014 599
$ datamash transpose < input1.txt
datamash: transpose input error: line 3 has 2 fields (previous lines had 3);
see --help to disable strict mode

Use --no-strict to allow missing values:

$ datamash --no-strict transpose < input1.txt
Sample id-123 id-99 id-42 id-13
Year 2014 2013 2014 2014
Count 1002 N/A 2030 599
# Use --filler to set the missing-field-filler value:
$ datamash --no-strict --filler XYZ transpose < input1.txt
Sample id-123 id-99 id-42 id-13
Year 2014 2013 2014 2014
Count 1002 XYZ 2030 599

Example: Reverse Fields

Use reverse to reverse the fields order in a file:

$ cat input.txt
Sample Year Count
id-123 2014 1002
id-99 2013 990
id-42 2014 2030
id-13 2014 599
$ datamash reverse < input.txt
Count Year Sample
1002 2014 id-123
990 2013 id-99
2030 2014 id-42
599 2014 id-13

By default, reverse verifies the input has the same number of fields in each line, and fails with an error otherwise. Use --no-strict to disable this behaviour (See transpose section above for an example).