sort according to a column value and take first n lines between the same headers in a file

user_16351260 0
Viewed 43

I have an input like this;

header  score1  score2
item    1.3 100
item    2.3 170
item    4.0 35
header  score1  score2
item    2.9 45
item    1.7 55
header  score1  score2
item    0.5 60
header  score1  score2
header  score1  score2
item    1.4 75
item    2.5 120
item    3.7 200
header  score1  score2

I want to consider the lines between two lines including 'header' individually. Sort the lines according to the value in the second column in descending order and take the first two lines with the highest values. Also, add the header at the top. It is known that the list starts with "header score1 score2" .

So desired output is this;

header  score1  score2
item    4.0 35
item    2.3 170
header  score1  score2
item    2.9 45
item    1.7 55
header  score1  score2
item    0.5 60
header  score1  score2
header  score1  score2
item    3.7 200
item    2.5 120
header  score1  score2

I am a relatively new awk user so my best methodology for now is separating the steps in words and then doing the code research and application stepwise. Building a code block is something I cannot apply for now.

So first I have to separately consider every interval between the lines starting with "header"

1.

awk '/header/ {p=1;print;next}  /^header/ && p {p=0;print} p' input.txt

This inputs the same file as expected. What I understand from this is when there is 'header it is printing it and continues to print below lines until another 'header'

the sorting and taking the first 2 I am doing with this code:

2.

sort  -k2 -nr | head -2     # this should be without the header

I am guessing that I have to insert the second code inside the first one somehow, so I would appreciate any help about this. Thank you

awk
4 Answers
awk '
   /header/ {
        # Close pipe to sort when we get header.
        close("sort -r|head -2")
        # Copy header line to standard output.
        print
        next
   }
   {    # other lines to sort.
        print | "sort -r|head -2"
   }' input_file|column -t


header  score1  score2
item    4.0     35
item    2.3     170
header  score1  score2
item    2.9     45
item    1.7     55
header  score1  score2
item    0.5     60
header  score1  score2
header  score1  score2
item    3.7     200
item    2.5     120
header  score1  score2
user_41778280

Using the DSU (Decorate/Sort/Undecorate) idiom with any awk+sort+cut:

$ cat tst.sh
#!/usr/bin/env bash

awk -v OFS='\t' '
    $1 == "header" {
        blockNr++
    }
    { print blockNr, ($1 == "header" ? 0 : 1), $0 }
' "$@" |
sort -k1,1n -k2,2n -k4,4rn |
cut -f3- |
awk '
    $1 == "header" {
        cnt = 0
    }
    cnt++ < 3
'


$ ./tst.sh file
header  score1  score2
item    4.0 35
item    2.3 170
header  score1  score2
item    2.9 45
item    1.7 55
header  score1  score2
item    0.5 60
header  score1  score2
header  score1  score2
item    3.7 200
item    2.5 120
header  score1  score2

See How to sort data based on the value of a column for part (multiple lines) of a file? for more info on how DSU works.

user_17450010

another awk with some support and no error handling.

$ awk 'BEGIN    {cmd="sort -k2nr | head -2"} 
       /header/ {close(cmd); print; next} 
                {print | cmd}' file

header  score1  score2
item    4.0 35
item    2.3 170
header  score1  score2
item    2.9 45
item    1.7 55
header  score1  score2
item    0.5 60
header  score1  score2
header  score1  score2
item    3.7 200
item    2.5 120
header  score1  score2

uses awk for partitioning the data to sections at the headers and delegating the sort and take 2 functions to other commands.

user_14358690

took me long enough — the concept is to create a synthetic array index key for sorting purposes that

  • incorporates the rank ordering from multiple columns,
  • can deal with floating point, have them mix-and match with integers, and
  • maintains numeric rank ordering in ASCII
  • be wide enough that it'll only start to overflow in very large or small numbers, in the absence of big-integer library
  • minimizing number of compare ops needed
* a single unified sort key compare
    
     instead of 

* having to go into each sub-key field when dealing with tie-breakers

CODE

BEGIN {
 1      OFS = "\t"
 1      PROCINFO["sorted_in"] = "@ind_str_desc"

 1        __ = "[+-:]+"
 1      gsub("^|$", "[[:blank:]]", __)

 1      ____ = (((_*=_+=_^=_<_)^_)^--_)
 1         _ = _<_ 
}
# Rule(s)
 4  ($_)!~__ { # 2
 2      print
 2      while (($_) !~ __) {
 2          split("", ___)
 2          $_ = ""
 2          getline

    }
}
 4  {
 9      do {
 9          ___[sprintf("x%.12X%.12X",-(____/$(NF-!_)),-(____/$(NF)))] = $_
 9          getline
        } while (($_) ~ __)
 4      ______ = (! _) + (! _)
 9      for (_______ in ___) {
 9          if (-______ < +______--) { # 7

 7              print _______, ___[_______]
        }
    }
 4      split("", ___)
 4      print
 4      $_ = ""
}

OUTPUT

header  score1  score2
xFFFFFFFFFFC00000FFFFFFFFFFF8AF8B   item    4.0 35
xFFFFFFFFFF90B217FFFFFFFFFFFE7E7F   item    2.3 170

header  score1  score2
xFFFFFFFFFFA7B962FFFFFFFFFFFA4FA5   item    2.9 45
xFFFFFFFFFF69696AFFFFFFFFFFFB5870   item    1.7 55

header  score1  score2
xFFFFFFFFFE000000FFFFFFFFFFFBBBBC   item    0.5 60

header  score1  score2
header  score1  score2

xFFFFFFFFFFBACF92FFFFFFFFFFFEB852   item    3.7 200
xFFFFFFFFFF99999AFFFFFFFFFFFDDDDE   item    2.5 120

header  score1  score2
user_146721140
Related