Tutorial
Analyzing an OTU table
Data
Here is a mock OTU table from 16S rRNA sequencing results. Columns are sample IDs in the format "GROUP.ID".
$ cat otu_table.csv
Taxonomy,A.1,A.2,A.3,B.1,B.2,B.3,C.1,C.2
Proteobacteria,.13,.29,.13,.16,.13,.22,.30,.23
Firmicutes,.42,.06,.49,.41,.55,.41,.32,.38
Bacteroidetes,.19,.62,.12,.33,.16,.29,.34,.35
Deferribacteres,.17,.00,.24,.01,.01,.01,.01,.01
What a mess! Let's make it prettier!
$ csvtk pretty otu_table.csv
Taxonomy A.1 A.2 A.3 B.1 B.2 B.3 C.1 C.2
Proteobacteria .13 .29 .13 .16 .13 .22 .30 .23
Firmicutes .42 .06 .49 .41 .55 .41 .32 .38
Bacteroidetes .19 .62 .12 .33 .16 .29 .34 .35
Deferribacteres .17 .00 .24 .01 .01 .01 .01 .01
Steps
-
Counting
$ csvtk stat otu_table.csv file num_cols num_rows otu_table.csv 9 4 -
Column names
$ csvtk headers otu_table.csv # otu_table.csv 1 Taxonomy 2 A.1 3 A.2 4 A.3 5 B.1 6 B.2 7 B.3 8 C.1 9 C.2 -
Convert to a tab-delimited table
$ csvtk csv2tab otu_table.csv Taxonomy A.1 A.2 A.3 B.1 B.2 B.3 C.1 C.2 Proteobacteria .13 .29 .13 .16 .13 .22 .30 .23 Firmicutes .42 .06 .49 .41 .55 .41 .32 .38 Bacteroidetes .19 .62 .12 .33 .16 .29 .34 .35 Deferribacteres .17 .00 .24 .01 .01 .01 .01 .01 -
Extract data for groups A and B and save it to a file with
-o otu_table.gAB.csv$ csvtk cut -F -f "Taxonomy,A.*,B.*" otu_table.csv -o otu_table.gAB.csv $ csvtk pretty otu_table.gAB.csv Taxonomy A.1 A.2 A.3 B.1 B.2 B.3 Proteobacteria .13 .29 .13 .16 .13 .22 Firmicutes .42 .06 .49 .41 .55 .41 Bacteroidetes .19 .62 .12 .33 .16 .29 Deferribacteres .17 .00 .24 .01 .01 .01 -
Search some rows by fields. Matched parts will be highlighted in red
$ csvtk grep -f Taxonomy -r -p "tes" otu_table.gAB.csv -TResult:
-
Transpose
$ csvtk transpose otu_table.gAB.csv -o otu_table.gAB.t.csv $ csvtk pretty otu_table.gAB.t.csv Taxonomy Proteobacteria Firmicutes Bacteroidetes Deferribacteres A.1 .13 .42 .19 .17 A.2 .29 .06 .62 .00 A.3 .13 .49 .12 .24 B.1 .16 .41 .33 .01 B.2 .13 .55 .16 .01 B.3 .22 .41 .29 .01 -
Rename the first column
$ csvtk rename -f 1 -n "sample" otu_table.gAB.t.csv -o otu_table.gAB.t.r.csv $ csvtk pretty otu_table.gAB.t.r.csv sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres A.1 .13 .42 .19 .17 A.2 .29 .06 .62 .00 A.3 .13 .49 .12 .24 B.1 .16 .41 .33 .01 B.2 .13 .55 .16 .01 B.3 .22 .41 .29 .01 -
Add a group column
$ csvtk mutate -p "(.+?)\." -n group otu_table.gAB.t.r.csv -o otu_table2.csv $ csvtk pretty otu_table2.csv sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres group A.1 .13 .42 .19 .17 A A.2 .29 .06 .62 .00 A A.3 .13 .49 .12 .24 A B.1 .16 .41 .33 .01 B B.2 .13 .55 .16 .01 B B.3 .22 .41 .29 .01 B -
Rename groups:
$ csvtk replace -f group -p "A" -r "Ctrl" otu_table2.csv \ | csvtk replace -f group -p "B" -r "Treatment" \ > otu_table3.csv $ csvtk pretty -s " " otu_table3.csv sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres group A.1 .13 .42 .19 .17 Ctrl A.2 .29 .06 .62 .00 Ctrl A.3 .13 .49 .12 .24 Ctrl B.1 .16 .41 .33 .01 Treatment B.2 .13 .55 .16 .01 Treatment B.3 .22 .41 .29 .01 Treatment -
Sort by abundance of Proteobacteria in descending order.
$ csvtk sort -k Proteobacteria:nr otu_table3.csv \ | csvtk pretty -s " " sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres group A.2 .29 .06 .62 .00 Ctrl B.3 .22 .41 .29 .01 Treatment B.1 .16 .41 .33 .01 Treatment B.2 .13 .55 .16 .01 Treatment A.3 .13 .49 .12 .24 Ctrl A.1 .13 .42 .19 .17 Ctrl -
Sort by abundance of Proteobacteria in descending order and Firmicutes in ascending order
$ csvtk sort -k Proteobacteria:nr -k Firmicutes:n otu_table3.csv \ | csvtk pretty -s " " sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres group A.2 .29 .06 .62 .00 Ctrl B.3 .22 .41 .29 .01 Treatment B.1 .16 .41 .33 .01 Treatment A.1 .13 .42 .19 .17 Ctrl A.3 .13 .49 .12 .24 Ctrl B.2 .13 .55 .16 .01 Treatment -
Filter samples with abundance greater than 0 in all taxa (all columns except sample and group; you can also use
-f "2-5>0").$ cat otu_table3.csv \ | csvtk filter -f "2-5>0" \ | csvtk pretty -s " " sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres group A.1 .13 .42 .19 .17 Ctrl A.3 .13 .49 .12 .24 Ctrl B.1 .16 .41 .33 .01 Treatment B.2 .13 .55 .16 .01 Treatment B.3 .22 .41 .29 .01 Treatment -
Most of the time, we may want to remove samples with abundance of 0 in all taxa.
$ cat otu_table3.csv \ | csvtk filter -f "2-5>0" --any \ | csvtk pretty -s " " sample Proteobacteria Firmicutes Bacteroidetes Deferribacteres group A.1 .13 .42 .19 .17 Ctrl A.2 .29 .06 .62 .00 Ctrl A.3 .13 .49 .12 .24 Ctrl B.1 .16 .41 .33 .01 Treatment B.2 .13 .55 .16 .01 Treatment B.3 .22 .41 .29 .01 Treatment