2

I want to distinct print the lines where diff is minimum. (diff=$2-$3)

The input file is:

c1,5,2         <-- diff=3
c1,5,3         <-- diff=2
c1,5,1         <-- diff=4
c2,8,3         <-- diff=5
c2,8,4         <-- diff=4

The expected output is:

c1,5,3  
c2,8,4  

How can I do this with Awk (e.g. with a one-liner)?


In other words, for each first field value (c1, c2), I want to print a single line with that first field value, chosen for the minimal diff between the second and third fields.

  • 1
    @RomanPerekhrest, it's not unclear at all. The two lines you cite have different first fields. Read the question title again. I've made an edit to make it clearer, though. – Wildcard Jul 10 '17 at 20:56
2

Enjoy awk solution:

awk -F, '{ diff=$2-$3; if(a[$1]>diff || !a[$1]) { a[$1]=diff; b[$1]=$2 FS $3 } }
         END{ for(i in a) print i,b[i] }' OFS=',' yourfile

The output:

c1,5,3
c2,8,4
0

Two-pass Awk command, fairly dirty:

awk -F, 'FNR == NR {diff[NR] = $2-$3;
         if (!($1 in minline) || diff[NR] < diff[minline[$1]]) {
           minline[$1]=NR}; next}
         FNR == 1 {for (a in minline) {p[minline[a]]}}
         FNR in p' file.csv file.csv

For interest, here is how to do this using SQL (done in Postgres, to be specific):

vagrant=# \d quick 
   Table "pg_temp_2.quick"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | text    | 
 f2     | integer | 
 f3     | integer | 

vagrant=# select * from quick;
 f1 | f2 | f3 
----+----+----
 c1 |  5 |  2
 c1 |  5 |  3
 c1 |  5 |  1
 c2 |  8 |  3
 c2 |  8 |  4
(5 rows)

vagrant=# select f1, f2, f3 from (select *, rank() over (partition by f1 order by f2 - f3) from quick) as x where rank = 1;
 f1 | f2 | f3 
----+----+----
 c1 |  5 |  3
 c2 |  8 |  4
(2 rows)

vagrant=# 

  • Both proposals fit the purpose; thank you both! – Petros Jul 11 '17 at 8:55

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.