# How to remove low abundance and less prevalent data from my dataset?

I have a relative abundance table in .tsv format where samples are in column and rows contain the features (pathways). Something like this reproducible example:

# Pathway   ERR2601 ERR26014    ERR260145_Abu   ERR260  ERR2601 ERR2601 ERR2607 ERR2600 ERR26
1CMET2-PWY: N10-formyl-tetrahydrofolate biosynthesis    0.00826631  0.00946472  0.00962571  0.00411153  0.00811879  0.00844421  0.00915602  0.00789428  0.0091798
3-HYDROXYPHENYLACETATE-DEGRADATION-PWY: 4-hydroxyphenylacetate degradation  0   0   0   0.000695608 0   0   0   0   0
7ALPHADEHYDROX-PWY: cholate degradation (bacteria, anaerobic)   0   0   0   0   0   0   0   0   0
AEROBACTINSYN-PWY: aerobactin biosynthesis  0   0   0   0   0   0   0   0   0
ALLANTOINDEG-PWY: superpathway of allantoin degradation in yeast    0.0000331927    0   0.0000335389    0   0   0   0   0.0000189167    0.0000908934
ANAEROFRUCAT-PWY: homolactic fermentation   0.00682776  0.00681683  0.00675732  0.00495716  0.00691969  0.0053589   0.00514634  0.00779249  0.00426403
ANAGLYCOLYSIS-PWY: glycolysis III (from glucose)    0.00866693  0.0100437   0.00829714  0.00524566  0.00960262  0.0075093   0.00716347  0.00916205  0.00866289
ARG+POLYAMINE-SYN: superpathway of arginine and polyamine biosynthesis  0.00106395  0.000544088 0.000774645 0.00370237  0.00102368  0.00133848  0.00148691  0.00114062  0.00188739
ARGDEG-PWY: superpathway of L-arginine, putrescine, and 4-aminobutanoate degradation    0   0   0   0.00164625  0.0000278133    0   0   0   0
ARGININE-SYN4-PWY: L-ornithine de novo biosynthesis 0.00166327  0.00383993  0.000105528 0.00230283  0.00404469  0.00115975  0.000460711 0.00101816  0.00367102
ARGORNPROST-PWY: arginine, ornithine and proline interconversion    0.000132331 0.000244069 0.0000796126    0.000479006 0.000237704 0.000290783 0   0.00066458  0.0000581229
ARGSYN-PWY: L-arginine biosynthesis I (via L-ornithine) 0.0124159   0.00955758  0.0118644   0.00533077  0.00774959  0.0098352   0.0127232   0.00996349  0.0108839


Now, I want to keep features (i.e. pathways) which are present with abundance >0.0001 and present in at least 10% of samples. What bash command can I use in order to do that?

• Your "reproducible example" is not reproducible. It should be a tab or comma separated plain text content pasted directly in the post and formatted as code (or uploaded on a GitHub gist). A screenshot of an excel spreadsheet is quite far from reproducibility. Nov 10 '20 at 18:31
• Thanks Ram RS for your response. I shared a google drive link. I didn't know it will be shown as an image. However, I have edited the post and entered the table as code. But, still I don't know why it is not showing as a .csv table. Please tell me whether you can help me. Thanks for your time. Nov 10 '20 at 20:10
• Your data is a little difficult to understand. Can you describe it better please? Please explain what each row and each column represents. I see duplicate column names (ERR2601) as well as inconsistent prefixes (such as the _Abu prefix on one which seems to indicate some sort of difference from the rest of the columns). If you're looking to pick all rows where at least 10% of columns 2-10 are >0.0001, you should be able to do that in R by counting number of columns that are >0.0001. Nov 10 '20 at 20:55
• Bash is the wrong tool for this. Nov 10 '20 at 22:37
• I would look at Python pandas or an R dataframe. The best bash solution would require awk but you'd need to know how to program with it.
– M__
Nov 11 '20 at 8:58

Here is a bash/awk solution:

awk -F'\t' 'NR==1{print}; NR>1{N=0;for(i=2; i<(NF+1); i++){if(\$i >= 0.0001) N++}; if(N>0.1*NF) print}' input.tsv


We make sure the input field separator is tab, then we loop on the number of fields (NF) and add to the tally the N of fields above threshold. At the end we simply print the entire line if N is high enough.

EDITED: for the header, we print the first line (NR==1) as is, then apply the expression when Number of Records > 1.

• Thanks a lot @Alexlok. It's working. But, it is also removing the header (i.e. the first line of the table i provided). Is there a way to retain it in the output? Nov 21 '20 at 22:22
• Indeed, my tail command was excluding the header, I edited to apply a different awk rule for the header or the rest of the text. Nov 22 '20 at 19:23
• Thanks again @Alexlok. It's working really good!!! Nov 25 '20 at 19:05

Assuming you have this stored in a matrix called m, where the pathway labels are row names:

IDX = apply(m, 1, function(x) sum(x > 0.0001)/length(x) >= 0.1)
m_subset = m[which(IDX),]

• OP isn't using R. OP also has duplicate colnames. Nov 10 '20 at 22:22
• OP needs to use the right tool for the job, it doesn't matter if they prefer bash. Nov 10 '20 at 22:37
• I agree with this. You'd need to be an @terdon standard coder to be doing this routinely and he'd likely prefer to use awk, in this example. Few coders are that standard. The OP could use awk if they wanted a bash solution.
– M__
Nov 11 '20 at 8:54