4

I have the following file: 

6180,6180,0,1,,1,0,1,1,0,0,0,0,0,0,0,0,4326,4326,,0.440000,
6553,6553,0,1,,1,0,1,1,0,0,0,0,1,0,1,0,4326,4326,,9.000000,
1297,1297,0,0,,0,0,1,0,0,0,0,0,1,0,1,0,1707,1707,,7.000000,
6598,6598,0,1,,1,0,1,1,0,0,0,1,0,0,0,0,1390,1390,,0.730000,
4673,4673,0,1,,1,0,1,1,0,0,0,0,0,0,0,0,1707,1707,,0.000000,

I need an awk command that print out the maximum value of $21 for $18.

the desired output will look like:

6553,6553,0,1,,1,0,1,1,0,0,0,0,1,0,1,0,4326,4326,,9.000000,
1297,1297,0,0,,0,0,1,0,0,0,0,0,1,0,1,0,1707,1707,,7.000000,
6598,6598,0,1,,1,0,1,1,0,0,0,1,0,0,0,0,1390,1390,,0.730000,

I got this result, but using the sort command, as below: 

sort -t, -k18,18n -k21,21nr | awk -F"," '!a[$18]++'

while I am looking to do it with single awk command.

Please advice,

Improve this question
1
  • I rolled back your edit because it changed what the question was asking for, making all answers obsolete. If you have a new question, please post it separately.
    – terdon ♦
    Jul 29, 2015 at 12:15

3 Answers 3

Reset to default
8

I don't see why you would want to do it in a single awk command, what you have seems perfectly fine. Anyway, here's one way:

$ awk -F, '(max[$18]<$21 || max[$18]==""){max[$18]=$21;line[$18]=$0}
            END{for(key in line){print line[key]}}' file
6598,6598,0,1,,1,0,1,1,0,0,0,1,0,0,0,0,1390,1390,,0.730000,
1297,1297,0,0,,0,0,1,0,0,0,0,0,1,0,1,0,1707,1707,,7.000000,
6553,6553,0,1,,1,0,1,1,0,0,0,0,1,0,1,0,4326,4326,,9.000000,

The idea is very simple. We have two arrays, max has $18 as a key and $21 as a value. For every line, if the saved value for $18 is smaller than $21 or if there is no value stored for $18, then we store the current line ($0) as the value for $18 in array line. Finally, in the END{} block, we print array line.

Note that the script above treats $18 as a string. Therefore, 001 and 1 will be considered different strings.

Improve this answer
7
  • Note this won't work for negative numbers.
    – 123
    Jul 29, 2015 at 14:20
  • @User112638726 how so? If the greatest number is negative, that will be kept. Do you mean that it doesn't compare absolute numbers? That it doesn't consider -10 to be greater than 5? Why should it?
    – terdon ♦
    Jul 29, 2015 at 14:37
  • Max[$18] begins at null or 0, so if the $21 is negative the block won't be run.
    – 123
    Jul 29, 2015 at 14:52
  • @User112638726 ah, very good point! I hadn't considered that. Thanks, fixed now.
    – terdon ♦
    Jul 29, 2015 at 14:55
  • 1
    @User112638726 Damn. Good point, again :). I assume you meant max[$18]=="" not max[$18]!="".
    – terdon ♦
    Jul 29, 2015 at 15:02
4

Using uniq instead awk can be quicker a little:

sort -t, -k18,18nr -k21,21nr | uniq -s39 -w4
Improve this answer
16
  • I just tested using a 287M file (the content of the OP's example repeated a few thousand times) and my awk approach took 23 seconds while the sort/uniq took 30. Here, both sort and uniq need to go over the entire file. Why would you expect it to be faster? Also, limiting the number of characters compared is clever but assumes that the number of characters per line is fixed, right? It will fail if one of the fields isn't empty or has a value with >1 digit.
    – terdon ♦
    Jul 29, 2015 at 11:40
  • @terdon - is your /tmp a tmpfs?
    – mikeserv
    Jul 29, 2015 at 12:01
  • 1
    @terdon You make incorrect example by «content of the OP's example repeated a few thousand times» so you have 3 members in array only. For real huge amount of different data awk even can reach full of memory error.
    – Costas
    Jul 29, 2015 at 12:46
  • 2
    @terdon I have write «can be quicker» because it strongly depends of data. If you'd like I'l add «in some cases». As noted by @mikeserv above awk read data twice: fist when put data into 2 arrays, then read array in end loop. So if resulting array is small — awk will be quicker.
    – Costas
    Jul 29, 2015 at 13:09
  • 1
    @Costas first I want to clarify that I'm just trying to learn here. I'm not saying you're wrong, as far as I can tell, you know far more about this sort of thing than I. And, as it turns out, I'm quite right and you do know more than I do! I tried again with a file where every $18 was different and awk took 5.7 seconds while sort/uniq took 2.8 to produce the same file!
    – terdon ♦
    Jul 29, 2015 at 13:26
1

You can try following awk:

awk -F"," '{ if (max[$18] < $21) { max[$18] = $21; x[$18] = NR; } z[NR] = $0; } END { for (i in x) print z[x[i]]; }' file

It uses 3 arrays max and x with keys of column $18 and z with keys row numbers . In max we hold max values, in x we are holding number of row containing max value, and in z every row in file. In the END block for every key in array x we print value of z[x[i]].
This solution is not good for large files since it reads entire file into memory.

Improve this answer
0

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.