4

I have a file looks like this :

1   7.8e-12  
1   7.8e-12  
1   1.0e-11   
2   9.3e-13    
2   3.5e-12 
2   3.5e-10
2   3.1e-9         
3   3.0e-11    
3   3.0e-11     
3   1.7e-08   

For every value in column one, I want to select "all rows" having minimum value in column 2 and group by column one. So the desired output is:

 1   7.8e-12  
 1   7.8e-12
 2   9.3e-13
 3   3.0e-11    
 3   3.0e-11 

Any idea how to do this?

  • @ Theophrastus, yes I knew about this but I want to keep the duplicate rows! – Anna1364 Feb 1 '18 at 20:23
  • 1
    Anna, you’ll get (more) useful answers if you provide sample data that represents your actual data. – Jeff Schaller Feb 1 '18 at 21:19
  • 1
    1.7e-11 is smaller than 5.1e-11 – glenn jackman Feb 1 '18 at 21:27
6

One approach would be to sort in ascending order, then note the first col2 value for each col1 and print if the current col2 value is equal to it:

sort -k1,1n -k2,2g file | awk '!a[$1] {a[$1] = $2} $2 == a[$1]'
1   7.8e-12
1   7.8e-12
2   9.3e-13
3   3.0e-11
3   3.0e-11
  • Thanks @steeldriver, this is doing the job for the example here, but my real numbers in my file are 7.8e-12,7.8e-12,1.0e-11,1.0e-11,3.9e-11 and this code does not select the smallest one! It selected rows with 1.0e-11, but 7.8e-12 is indeed smaller!. do you know what might be wrong? – Anna1364 Feb 1 '18 at 21:17
  • 1
    @Anna1364 AFAIK sort -n does not recognize exponential (scientific) notation - if you have the GNU vairant, you can try sort -g (--general-numeric-sort) instead – steeldriver Feb 1 '18 at 21:21
  • thanks a lot @steeldriver, now it works perfectly. Sorry for the confusion! – Anna1364 Feb 1 '18 at 21:43
5

This should handle numbers in scientific notation:

awk '
    NR == FNR {
        if (!($1 in min) || $2 < min[$1])
            min[$1] = $2
        next
    }
    $2 == min[$1]
' file file

We process the file twice: once to find the min value for each key, then to output lines with that min value.

1

With GNU tools and complex as you need duplicated values!

grep -F "$(datamash -W -g1 min 2 <infile | \
sed 's/\([^\.][1-9]\)e/\1.0e/')" <(sed 's/ \+/\t/' infile)

if only minimum values were concerned, below was just enough with datamash.

datamash -W -g1 min 2 <infile
1

Just to round out the text-processing based answers, here's a way to do this with PostgreSQL:

First, pre-process the file to convert it to CSV for easier import:

awk -v OFS=, '$1=$1' file.txt > file.csv

Then create a temp table in PostgreSQL like so:

create temp table x (id int, bignum float);

Copy the CSV into it:

\copy x from file.csv with (format csv)

And query the temp table for the results you want:

select id, bignum
from (
  select
    *,
    rank() over (partition by id order by bignum)
      as rank
  from x
) as sqlrequiresthisalias
where rank = 1;

Results:

 id | bignum  
----+---------
  1 | 7.8e-12
  1 | 7.8e-12
  2 | 9.3e-13
  3 |   3e-11
  3 |   3e-11
(5 rows)
0

GNU awk solution:

awk 'BEGIN{ PROCINFO["sorted_in"] = "@val_num_asc" }
     { a[$1][++c] = $2 }
     END{ 
         for (i in a) { 
             prev = 0;
             for (j in a[i]) { 
                 v = a[i][j]; if (prev && v != prev) continue; 
                 print i, v; prev = v 
             }
         }
     }' file

The output:

1 7.8e-12
1 7.8e-12
2 9.3e-13
3 3.0e-11
3 3.0e-11

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.