6

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?

1
  • 1
    Anna, you’ll get (more) useful answers if you provide sample data that represents your actual data.
    – Jeff Schaller
    Feb 1, 2018 at 21:19

4 Answers 4

7

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
0
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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .