1

I'm trying to create a relative abundance table but am having some problems. I managed to do it for 1 column, but am struggling to transform the entire table.

  • The tables are always .tsv format and thus tab separated.
  • Row 1 contains the names.
  • Rows 2 to x contain the data.
  • Column 1 contains the row names.
  • The last column (n) contains the Taxonomy and is thus not numerical.
  • Columns 2 to (n-1) contain the actual numerical data that needs to be transformed into relative abundance (i.e. value divided by sum of that column).

A simple table looks like the one below. Of course, the taxonomy field is usually a lot longer, but I shortened it here:

#OTU ID SampleA SampleB SampleC Taxonomy
cddjfhj63vhjdsmr4j4ot   17  14  0   d__Bacteria; p__Proteobacteria;
FPLS01016108.2.1307 15  20  14  d__Bacteria; p__Proteobacteria;
JX225178.1.1277 0   0   0   d__Bacteria; p__Bacteroidota;
FJ439832.1.1432 12  0   0   d__Bacteria; p__Bacteroidota;

Basically, the output should be this:

#OTU ID SampleA SampleB SampleC Taxonomy
cddjfhj63vhjdsmr4j4ot   0.386363636 0.411764706 0   d__Bacteria; p__Proteobacteria;
FPLS01016108.2.1307 0.340909091 0.588235294 1   d__Bacteria; p__Proteobacteria;
JX225178.1.1277 0   0   0   d__Bacteria; p__Bacteroidota; c__Bacteroidia;
FJ439832.1.1432 0.272727273 0   0   d__Bacteria; p__Bacteroidota;

I found the code below, but it only works for one column and if the table doesn't contain the top row with the names:

awk 'FNR==NR{s+=$2;next;} {printf "%s\t%s\n",$1,$2/s}' table.tsv table.tsv > table2.tsv

Any idea how I can process my table in Linux with some 'simple' commands or even with Python, so the relative abundance table is generated?

Thanks!

1 Answer 1

0
awk 'BEGIN{ FS=OFS="\t"; CONVFMT="%.9f" }
   NR==FNR{ for(i=2; i<=4; i++) sum[i]+=$i; next }
   FNR>1  { $2/=sum[2]; $3/=sum[3]; $4/=sum[4] }1' infile infile

Generally, you would need to pass the start/end columns:

awk -v strt=2 -v end=4 '
BEGIN  { FS=OFS="\t"; CONVFMT="%.9f" }
NR==FNR{ for(i=strt; i<=end; i++) sum[i]+=$i; next }
FNR>1  { for(i=strt; i<=end; i++) $i/=sum[i] }1' infile infile

Read more about CONVFMT; Here since you needed 9 precision significant digits after the floating point I used %.9f (default is %.6g).

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .