<p>Here is a bash/awk solution:</p>
<pre><code>awk -F'\t' 'NR==1{print}; NR&gt;1{N=0;for(i=2; i&lt;(NF+1); i++){if($i &gt;= 0.0001) N++}; if(N&gt;0.1*NF) print}' input.tsv
</code></pre>
<p>We make sure the input field separator is tab, then we loop on the number of fields (NF) and add to the tally the <code>N</code> of fields above threshold. At the end we simply <code>print</code> the entire line if <code>N</code> is high enough.</p>
<p>EDITED: for the header, we print the first line (<code>NR==1</code>) as is, then apply the expression when Number of Records &gt; 1.</p>

CC BY-SA 4.0
2020-11-21T22:22:29.743Z
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?
DEEP CHANDA
CC BY-SA 4.0
2020-11-22T19:23:40.083Z
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.
Alexlok
CC BY-SA 4.0
2020-11-25T19:05:11.417Z
Thanks again @Alexlok. It's working really good!!!
DEEP CHANDA
CC BY-SA 4.0
2020-11-17T23:58:58.727Z
2020-11-22T19:22:54.103Z
2020-11-22T19:22:54.103Z
Alexlok
<p>Assuming you have this stored in a matrix called <code>m</code>, where the pathway labels are row names:</p>
<pre><code>IDX = apply(m, 1, function(x) sum(x &gt; 0.0001)/length(x) &gt;= 0.1)
m_subset = m[which(IDX),]
</code></pre>

CC BY-SA 4.0
2020-11-10T22:22:07.757Z
OP isn't using R. OP also has duplicate colnames.
Ram RS
CC BY-SA 4.0
2020-11-10T22:37:22.323Z
OP needs to use the right tool for the job, it doesn't matter if they prefer bash.
Devon Ryan
CC BY-SA 4.0
2020-11-11T08:54:56.640Z
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__
CC BY-SA 4.0
2020-11-10T22:07:09.523Z
2020-11-10T22:07:09.523Z
Devon Ryan
<p>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:</p>
<pre><code># 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
</code></pre>
<p>Now, I want to keep features (i.e. pathways) which are present with abundance &gt;0.0001 and present in at least 10% of samples. What bash command can I use in order to do that?</p>

CC BY-SA 4.0
2020-11-10T18:31:50.190Z
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.
Ram RS
CC BY-SA 4.0
2020-11-10T20:10:40.087Z
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.
DEEP CHANDA
CC BY-SA 4.0
2020-11-10T20:55:05.383Z
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.
Ram RS
CC BY-SA 4.0
2020-11-10T21:20:04.567Z
Column names are just subject IDs and rownames are the pathway names. So, please don't think about them. Each column denotes the relative abundance of respective pathways. If you add the values of a column, it will give you 1.00. Now, here, as example, I have 9 columns, right? Now, 10% of 9 is 1 (apprx.). So, I want to get an output file containing rows with at least one value > 0.0001.  That means, rows with 1 or more values > 0.0001 will be kept and rows which does not have at least one value >0.0001 will be removed. As, there is not much complex calculation, I would prefer to do in bash.
DEEP CHANDA
CC BY-SA 4.0
2020-11-10T22:37:49.600Z
Bash is the wrong tool for this.
Devon Ryan
CC BY-SA 4.0
2020-11-11T08:58:36.827Z
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__
<bash>
How to remove low abundance and less prevalent data from my dataset?
# How to remove low abundance and less prevalent data from my dataset?

(Asked by: username_0 on 2020-11-10)

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?

**Tags:** bash
> **Comments:**
> **username_1**: 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.  
> **username_0**: 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.  
> **username_1**: 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.  
> **username_0**: Column names are just subject IDs and rownames are the pathway names. So, please don't think about them. Each column denotes the relative abundance of respective pathways. If you add the values of a column, it will give you 1.00. Now, here, as example, I have 9 columns, right? Now, 10% of 9 is 1 (apprx.). So, I want to get an output file containing rows with at least one value > 0.0001.  That means, rows with 1 or more values > 0.0001 will be kept and rows which does not have at least one value >0.0001 will be removed. As, there is not much complex calculation, I would prefer to do in bash.  
> **username_2**: Bash is the wrong tool for this.  
> **username_3**: 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.  

---

## 2 answers

### Answer by username_4 on 2020-11-17. Score: 1 | Accepted answer

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.

> **Comments:**
> **username_0**: 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?  
> **username_4**: 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.  
> **username_0**: Thanks again @Alexlok. It's working really good!!!  

---

### Answer by username_2 on 2020-11-10. Score: 2

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),]

> **Comments:**
> **username_1**: OP isn't using R. OP also has duplicate colnames.  
> **username_2**: OP needs to use the right tool for the job, it doesn't matter if they prefer bash.  
> **username_3**: 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.  

---

