1

The data is in this format;-

South,London,5
South,Greenwich,7
South,Guildford,2
South,Maidstone,3
South,Oxted,4
North,Manchester,10
North,Leeds,7
North,Grimsby,9
North,Halifax,11
Midlands,Birmingham,16
Midlands,Coventry,10
Midlands,Walsall,15
Midlands,Rutland,1

I need an AWK program that sifts through and spits out the records to group by the second column but only printing the top three values thus;-

South,Greenwich,7
South,London,5
South,Oxted,4
North,Halifax,11
North,Manchester,10
North,Grimsby,9
Midlands,Birmingham,16
Midlands,Walsall,15
Midlands,Coventry,10

The actual file has far more regions/towns - any help?

4
  • 6
    I don't see where you are "grouping" here. It looks like simple sorting by the 3rd column. Do you need to show more sample input and output to state your intention clearly? With this input and output, sort -t, -k3,3nr file | head -3 will do it. Jun 17 '19 at 15:54
  • Original data file is a lot bigger .... such as North,Manchester,4,North,York,10,East,Norwich,2,East,Cambridge,4,West,Bristol,45,West,Yeovil,22 etc
    – Bill
    Jun 17 '19 at 18:00
  • I don’t see grouping and I don’t see anything done with the second column, only the third.
    – Wildcard
    Jun 18 '19 at 6:42
  • 1
    From the output I see that you mean top three of each category (the first field). This information should be in the question, not only the output and we have to find out what you mean...
    – pLumo
    Jun 18 '19 at 7:14
4

You can first sort it using sort command and then use awk to filter the top 3 for each category in $1:

sort -t, -k1,1 -k3,3nr file | awk -F, 'n[$1]++<3'

Output:

Midlands,Birmingham,16
Midlands,Walsall,15
Midlands,Coventry,10
North,Halifax,11
North,Manchester,10
North,Grimsby,9
South,Greenwich,7
South,London,5
South,Oxted,4

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.