0

I have the following file:

gene.14977.0.1.p2       NbD023586.1.mrna1       100.0   132     0       0       1       132     1       132     4.9e-72     268.9
gene.14977.0.1.p2       NbD032405.1.mrna1       95.5    132     6       0       1       132     1       132     1.5e-68     257.3
gene.14983.1.1.p1       NbE05064429.1.mrna1     61.4    202     13      3       8       145     530     730     8.6e-51     198.7
gene.14983.1.1.p1       NbD024082.1.mrna1       59.3    209     13      3       8       145     530     737     5.6e-50     196.1
gene.14983.1.1.p1       NbD018021.1.mrna1       59.5    205     18      3       5       145     523     726     3.0e-48     190.3
gene.14986.0.0.p1       NbD007981.1.mrna1       100.0   422     0       0       9       430     1       422     1.8e-256    883.2
gene.14986.0.0.p1       NbD032402.1.mrna1       96.5    430     14      1       1       430     1       429     2.7e-252    869.4
gene.14986.0.0.p1       NbD023991.1.mrna1       85.3    428     61      1       1       428     1       426     2.1e-225    780.0

For each gene value (in first column, e.g., gene.14977.0.1.p2), I would like to keep only the line with the smallest 11th column value (e.g., 4.9e-72).  Expected output is:

gene.14977.0.1.p2       NbD023586.1.mrna1       100.0   132     0       0       1       132     1       132     4.9e-72     268.9
gene.14983.1.1.p1       NbE05064429.1.mrna1     61.4    202     13      3       8       145     530     730     8.6e-51     198.7
gene.14986.0.0.p1       NbD007981.1.mrna1       100.0   422     0       0       9       430     1       422     1.8e-256    883.2

How is it possible to do it?

3
  • The question isn't clear: you should tell that you don't want two outputs with the same gene. Not everyone here is a biologist. Else, some people will think the expected values are 1.8e-256, 2.7e-252 and 2.1e-225 or will not understand why it's not. Oct 21 '20 at 10:48
  • 1
    ITYM "which have the smallest 11th column for each unique first column value", right?
    – Ed Morton
    Oct 21 '20 at 11:54
  • If all the lines with the same gene value (e.g., gene.14977.0.1.p2) are guaranteed to be together (consecutive) in the input file, you should say so. If the file is sorted by the 11th column as a secondary sort key (as appears to be the case in your example), you should say that, too, as it could make the answer simpler. Oct 21 '20 at 19:09
3

If you want to extract the lines that has the smallest value in the 11th column grouped by gene, then use the gene identifier as the key in an associative array in awk to keep track of the smallest value for that gene:

$ awk '{ gene = $1 } min[gene] == "" || min[gene] > $11 { min[gene] = $11; line[gene] = $0 } END { for (gene in line) print line[gene] }' file
gene.14983.1.1.p1       NbE05064429.1.mrna1     61.4    202     13      3       8       145     530     730     8.6e-51 198.7
gene.14977.0.1.p2       NbD023586.1.mrna1       100.0   132     0       0       1       132     1       132     4.9e-72 268.9
gene.14986.0.0.p1       NbD007981.1.mrna1       100.0   422     0       0       9       430     1       422     1.8e-256        883.2

Here, I'm also keeping track of the actual line that the minimal values comes from, for each gene, and at the end those lines are printed.

The awk program, nicely formatted:

{
        # Pick out the gene name
        gene = $1
}

min[gene] == "" || min[gene] > $11 {
        # This gene hase either not been seen before,
        # or its value in column 11 is smaller than
        # what has been seen before (for this gene).
        min[gene] = $11    # save small value from column 11
        line[gene] = $0    # save whole line
}

END {
        # Print all remembered lines.
        for (gene in line)
                print line[gene]
}

A minimized one-liner version, for people that like unmaintainable code:

awk 'm[$1]==""||m[$1]>$11{m[$1]=$11;t[$1]=$0}END{for(g in t)print t[g]}' file

You can also get the same result (possibly in a different order) using GNU sort (which can do "human numeric sort" of numbers in scientific notation):

$ sort -k1,1 -k11,11g file | sort -u -k1,1
gene.14977.0.1.p2       NbD023586.1.mrna1       100.0   132     0       0       1       132     1       132     4.9e-72 268.9
gene.14983.1.1.p1       NbE05064429.1.mrna1     61.4    202     13      3       8       145     530     730     8.6e-51 198.7
gene.14986.0.0.p1       NbD007981.1.mrna1       100.0   422     0       0       9       430     1       422     1.8e-256        883.2

Here, the first sort arranges the data so that the lines are sorted by genes (the 1st column), and that the lines for each gene is are sorted numerically by the 11th column. The second sort then picks out a single line for each gene (due to the -u option, requesting single entries for each sort key). This single line would be the first line for each gene, which will be the line with the smallest value in the 11th column (due to the first sort).

Instead of the second sort, you could use a short awk program, which would be a bit faster for huge sets of data:

sort -k1,1 -k11,11g file | awk '!seen[$1]++'
1
  • ^1 for the sort | awk answer. I think you'd need to pass the GNU-only -s to the 2nd sort for the sort | sort answer to guarantee it'd print every line with first value seen as opposed to some other line for that value.
    – Ed Morton
    Oct 21 '20 at 11:58

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.