title: How to categorize and manipulate data in a row based on column names
body: <p>I have a table, the start is below:</p>
  
  <pre><code>                    Control_H1455_121005_4     Case_X1456_121005_1     Case_V1457_121005_7      Control_K1461_121005_2
  ENSG00000419.8                     0                    0                    14                    3
  ENSG00000457.8                     2                    0                    1                     0
  ENSG00000460.11                    18                   3                    16                    6
  ENSG00000938.7                     0                    0                    0                     0      
  </code></pre>
  
  <p>UPDATED:</p>
  
  <ol>
  <li><p>First I want to change the colnames. Only the part before the 2nd underscore is important, so e.g in Control_H1455_121005_4, I want to shorten it to just Control_H1455. Here's my code so far:</p>
  
  <pre><code>gsub("^.*?_","_","Case_H1455_121005_4")
  </code></pre>
  
  <p>But this returns <code>"_H1455_121005_4"</code>. The part I want to keep is actually <code>Case_H1455</code>. So I just want to keep all the characters up until the <strong>2nd underscore</strong>.</p>
  
  <p>UPDATE: for (2), I have the following code:</p>
  
  <pre><code>#separating data into Control &amp; Case groups
  data_con=data[which(substring(names(data),2,2) %in% c("o"))]
  data_case=data[which(substring(names(data),2,2) %in% c("a"))]
  
  #delete rows if both case and control groups have &gt;= 90% cols that contain 0
  #data &lt;- data[(rowSums(data_case==0)/ncol(data_case) &lt; 0.9 &amp; rowSums(data_con==0)/ncol(data_con) &lt; 0.9) , ]
  </code></pre>
  
  <p>It seems to be working.</p></li>
  <li><p>I want to filter through the each row and divide the data in that row in 2 groups: Control and Case. Then, I want to delete a row if and only if it satisfies the following condition: that >= 90% of the cols contains 0 in both Case <strong>AND</strong> Control group. So in this sample table, in order to delete a row, the groups Control and Case have to each contain >=90% of cols that contain 0. So here it'd be the last row. If the Case group contains >=90% of cols that have 0 but the Control group has &lt;90% cols with 0 (or vice versa), then that row should be kept. To do this, I currently have the following code:</p>
  
  <pre><code>data &lt;- data[rowSums(data==0)/ncol(data) &lt; 0.9, ]
  </code></pre>
  
  <p>but this doesn't separate each row by Case and Control and look at each group individually. </p></li>
  </ol>
  
  <p>Please keep in mind also that there are many more cols and rows in the actually data and Control and Case appear randomly from col to col. </p>
  
  <p>So any help on number (1) would be greatly appreciated!</p>