2

I have a table that has 5 columns and multiple rows

The last column has the sum of all values for each row

like that:

A       B   C   D   E
gene1   1   3   5   9
gene2   0   0   4   4
gene3   1   0   1   2
gene4   5   5   0   10
gene5   2   0   0   2

What I would like to do is to extract gene2 and gene5 rows into a separate file and also to have another file that would contain the other genes (but without the extracted ones), gene1,gene3 and gene4

I am thinking of using the awk command but I cannot understand how can I link rows and numbers?

  • This is somewhat confusing to me. What determines that gene2 and 5 are extracted? Or is that just a static requirement? You mention that if the last column is equal to the sum of the other columns it qualifies but that applies to every row in your example. – jesse_b Dec 6 '19 at 21:46
  • You are right, maybe the idea is that if the only value that is not 0 is equal to a sum - that would mean the row needs to be extracted.. – Mark Dec 6 '19 at 21:57
2

Using the awk:

awk '{
    if ( ($2 == $5 && $3 + $4 == 0) || ($3 == $5 && $2 + $4 == 0) || ($4 == $5 && $2 + $3 == 0) ) {
        print $0 > "match-file"
    } else if ($2 + $3 + $4 == $5) { 
        print $0 > "nomatch-file"
    }
}' input
  • Thank you so much. Did not know that print$0 prints the line, there's so much to learn ) – Mark Dec 6 '19 at 22:49
1

A ( very similar) alternative:

awk '{
    if ( $2 == $5 || $3 == $5|| $4 == $5 ) {
        print $0 > "match-file"
    } else if ($2 + $3 + $4 == $5) { 
        print $0 > "nomatch-file"
    }
}' input
  • I considered this but this wouldn't validate whether the sum of the columns is not equal to the last column in either case. I'm not sure if that is a consideration that OP needs to worry about or not though. – jesse_b Dec 6 '19 at 23:11
  • I do not understand in what cases can the version that I propose fail to outcome the correct results. If the value of columns 2, 3 or 4 match the value of column 5 the other two columns value are zero. – Paulo Tomé Dec 6 '19 at 23:22
  • Say there was a column like: gene4 10 5 2 10. Your code does nothing to validate that the other columns are zero – jesse_b Dec 6 '19 at 23:25
  • In the question is stated that: "The last column has the sum of all values for each row". – Paulo Tomé Dec 6 '19 at 23:27
  • Yeah like I said it may not be a concern for op but I'm not sure how this data was generated and if we can be sure that is true for the entire file. – jesse_b Dec 6 '19 at 23:29
0
Using GNU awk:

$ awk 'NR>1{
    t = gensub(/ /, "  ", "g")
    f = gsub(/ 0 /, "", t) > 1 ? "File1" : "File2"
    print $0 > f
}' file

Using all Posix sed constructs we can do this as follows:

$ sed -ne '
   1b
   s/[[:blank:]]0[[:blank:]]/&/2w File1
   t
   s/^//w File2
' file

Run sed in the -n mode which means no default printing the pattern space. Skip the header line, assumed to be the first one.

If in a line other than the header, we are able to see atleast two isolated zeros => last column must match one column from amongst the 2 , 3, or 4 columns. This is because the last column is the sum of columns 2,3, and 4.

Assumes that there are no leading or trailing whitespaces.

Results are dumped in File1 for matching records. And File2 for the rest. Header is not present in either of the outputs.

0

Seems that you are just looking for 2 (leading) zeroes in the line, in which case try

awk '{if (NR==1){print > "match"; print > "nomatch"}
    else
    {if ($0 ~ / 0.* 0/) {print > "match"} else {print > "nomatch"}}}' file1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.