4

I have a text file as (for example):

apple   V$NFY_Q6_rc V=0.741
apple   V$HOXA7_01_rc   V=0.454
apple   V$ALPHACP1_01_rc    V=0.695
orange  V$SP4_Q5    V=0.747
grapes  V$SP1_Q2_01 V=0.677
grapes  V$SP1_Q6_01_rc  V=0.685
grapes  V$SP1_Q6_rc V=0.884

I want to extract the rows that have the highest V value (in column 3) for each fruit. My output should look like:

apple   V$NFY_Q6_rc V=0.741
orange  V$SP4_Q5    V=0.747
grapes  V$SP1_Q6_rc V=0.884

I have several files like this in different subdirectories.

After subsetting, I would use the following code to get the number of occurences of a particular string in column 2.

        perl -lanE '$str=$F[1];  $f="/home/$str/list/$str.txt"; $c=`grep -c "$str" "$f"`;chomp($c);$x=0;$x++ if $c;say "$str\t$x\t$c"' file2

this would result in the following output. Let's assume I am searching for the string "SP4" in column 2 of this file:

         X       X in file?  number of times it occurs
        NFA            0                            0
        SP4            1                            2
        NFATC1         0                            0

I want the V value (from above to be included along with this table)

          X       X in file?  number of times it occurs      V value
        NFA            0                            0
        SP4            1                            2         0.747
        NFATC1         0                            0

P.S.: For further details on the perl program in between, please refer to this link:

         http://stackoverflow.com/questions/23109490/search-for-occurrence-of-a-string-in-another-file-in-a-particular-column
4

If order is not important, two passes of sort should do the trick. In the first pass, sort by field 1 followed by reverse-sorting by the numeric portion of field 3 (starting position 4). The -b modifier causes leading blanks to be ignored. Pipe this to a second sort that returns one record for every unique value in field 1, but this time specify the stable sort(-s) modifier to ensure that the record with the highest value in field 3 (which had bubbled up to the top for each value in field 1 courtesy the previous sort) is returned

sort -k1,1 -k3.4b,3nr file.txt | sort -k1,1 -s -u
apple   V$NFY_Q6_rc V=0.741
grapes  V$SP1_Q6_rc V=0.884
orange  V$SP4_Q5    V=0.747
  • Thanks a ton! It works. Could you please explain the code? – biobudhan Apr 29 '14 at 8:45
  • @biobudhan, added some explanation, see if it makes sense. Also note that I have eliminated the dependence on version sorting, plain numeric sorting should work for your case! – iruvar Apr 29 '14 at 13:19
  • Thank you so much for the explanation as well. It worked like a charm!! :-) – biobudhan Apr 29 '14 at 15:42
  • I have one more question added to this. After subsetting this file as above, I would count the number of instances of a certain string in this file and put it in a table (I have already written a code for this). Now, I would like to add the value of V to this. I have updated my question according to this now. please help!! – biobudhan Apr 29 '14 at 17:05
  • @biobudhan, Please post that as a new question. Sounds like you need perl help, I am by way of being a Pythonista ;-) – iruvar Apr 29 '14 at 17:36
2

With awk:

awk -F'[ =]' '$NF>a[$1]{a[$1]=$NF;b[$1]=$0}END{for (i in b) print b[i]}' filename.txt

This works by creating an array a of the V values, indexed by the first column. For each line, the V value is compared against the current value of the array for that index, and if it is greater, then the value in the array is updated and the whole line is stored in a second array b. After the whole file has been processed, everything in b is printed.

Example output:

orange  V$SP4_Q5    V=0.747
apple   V$NFY_Q6_rc V=0.741
grapes  V$SP1_Q6_rc V=0.884

If the ordering is important, you can pipe to sort (eg, sort by the V number):

awk -F'[ =]' '$NF>a[$1]{a[$1]=$NF;b[$1]=$0}END{for (i in b) print b[i]}' filename.txt | sort -t= -nk2

Gives:

apple   V$NFY_Q6_rc V=0.741
orange  V$SP4_Q5    V=0.747
grapes  V$SP1_Q6_rc V=0.884

Working example: http://ideone.com/WPvRzh

  • I assume we need to give the name of the input file instead of filename.txt, right? If that is the case, my output is not a subset, the entire file gets displayed again. – biobudhan Apr 28 '14 at 10:02
  • Yes, "filename.txt" was a placeholder for the relevant input file. What version of awk are you using? What happens if you add 0; directly before END? – Josh Jolly Apr 28 '14 at 10:51
  • Adding a 0; too results in the same output. My awk version is: awk -W version mawk 1.3.3 Nov 1996, Copyright (C) Michael D. Brennan – biobudhan Apr 28 '14 at 11:45
0

Here is a solution in perl:

$ perl -F'\s+|=' -anle '
    $h{$F[0]} = [$F[-1],$_] if $F[-1] > $h{$F[0]}->[0];
    END {print $h{$_}->[1] for keys %h}' file
grapes  V$SP1_Q6_rc V=0.884
apple   V$NFY_Q6_rc V=0.741
orange  V$SP4_Q5    V=0.747

Update

Assume that you have two result after running two one-liner.

file1:

apple   V$NFY_Q6_rc V=0.741
orange  V$SP4_Q5    V=0.747
grapes  V$SP1_Q6_rc V=0.884

file2:

X       X in file?  number of times it occurs
NFA            0                            0
SP4            1                            2
NFATC1         0                            0

You can try this:

$ awk -F'[ =]+' 'FNR==NR{a[$2]=$NF;next}
            FNR==1{print $0"\tV value";next}
            {for(i in a){
                if(index(i,$1)){
                    print $0"\t"a[i];
                    next;
                }
            }
            print;
        }' file1 file2
X       X in file?  number of times it occurs   V value
NFA            0                            0
SP4            1                            2   0.747
NFATC1         0                            0
  • Thank you very much. The first perl code works like a charm. The second one however, creates only a column called V value, the value doesn't get placed. Please help – biobudhan May 2 '14 at 11:37
  • @biobudhan: Can you post what have you done? With example in my answer, this works, do you try it? – cuonglm May 2 '14 at 11:40
  • I pasted the same code above only changing the filenames. – biobudhan May 2 '14 at 12:15
  • What is your two filenames content? Can you post it? – cuonglm May 2 '14 at 13:11
  • file1: apple V$NFY_Q6_rc V=0.741 orange V$SP4_Q5 V=0.747 grapes V$SP1_Q6_rc V=0.884 file2: X X in file? number of times it occurs NFA 0 0 SP4 1 2 NFATC1 0 0 I have both saved in different directories. So i included the path as well in the place of file1 and file2. – biobudhan May 2 '14 at 13:46

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.