# Extract row with maximum value from multiply represented second variable

I have a problem that I cant seem to solve.

I have a massive tab-delimited text file similar to:

``````chr 10 20 20 a
chr 30 40 10 b
chr 30 40 15 b
chr 30 40 11 b
``````

What I need is to: 1) extract all unique rows 2) Where column 5 is represented multiple times (eg b), the row with the maximum value in column 4.

So the rows I need in the above example are:

```chr 10 20 20 a (this is a unique row)
chr 30 40 15 b (this is the row with maximum value in column 4 when column 5 is represented multiple times.
```

Is there is a simple way to do this?

• actually, all the input rows in their whole representation are unique. So, elaborate your uniqueness. – RomanPerekhrest Dec 19 '17 at 13:20
• @RomanPerekhrest, well, they did mention having multiples of the value in column 5, so we might at least consider asking if that was the intended meaning? – ilkkachu Dec 19 '17 at 14:16
• Why do you want the "a" row when it only appears once? This seems to contradict your 2nd criterion. – glenn jackman Dec 19 '17 at 16:12
• @glennjackman Its information that I need (unique lines). I will remove all unique lines first and then print out the max of the multiple represented group. – joshiricky Dec 19 '17 at 18:27

So, group the lines by the fifth column, and for each group, print the line where the fourth column is greatest?

Assuming you have no negative numbers:

``````\$ awk '\$4 > val[\$5] {val[\$5] = \$4; line[\$5] = \$0}
END {for (x in line) print line[x] }' < foo.txt
chr 10 20 20 a
chr 30 40 15 b
``````
• This worked perfectly!!!!! Awesome, thank you soooo much :) – joshiricky Dec 19 '17 at 18:46

example.txt

``````chr 10 20 20 a
chr 30 40 10 b
chr 30 40 15 b
chr 30 40 11 b
``````

code

`````` awk '\$5== "a" {print \$0}' l.txt | sort -k4 -nr | sed -n '1p' ; awk '\$5=="b" {print \$0}' l.txt | sort -k4  -nr | sed -n '1p'
``````

Output

``````chr 10 20 20 a
chr 30 40 15 b
``````

I would write:

``````awk '
NR == FNR {count[\$5]++; if (\$4 > max[\$5]) max[\$5] = \$4; next}
count[\$5] > 1 && \$4 == max[\$5] && !seen[\$0]++
' file file
``````
``````chr 30 40 15 b
``````

Process the file twice:

• the first time, count how often the 5th field occurs, and find the maximum 4th field for each 5th field value
• the second time, process your criteria:
• only records where the 5th field appears more than once, and
• only records with max 4th field, and
• only unique records

If you want to see lines where the count is one, then we can simply write

``````awk 'NR == FNR {if (\$4 > max[\$5]) max[\$5]=\$4; next} \$4==max[\$5] && !seen[\$0]++' file file
``````
``````chr 10 20 20 a
chr 30 40 15 b
``````

This snippet:

``````# Utility functions: print-as-echo, print-line-with-visual-space.
pe() { for _i;do printf "%s" "\$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "\$*"; }
pl " Input data file \$FILE:"

pl " Expected output:"
cat \$E

pl " Results:"
datamash -t" " --group=5 max 4 --full <\$FILE |
cut -d" " -f1-5
``````

produces:

``````-----
Input data file data1:
chr 10 20 20 a
chr 30 40 10 b
chr 30 40 15 b
chr 30 40 11 b

-----
Expected output:
chr 10 20 20 a
chr 30 40 15 b

-----
Results:
chr 10 20 20 a
chr 30 40 15 b
``````

For a system like:

``````OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.9 (jessie)
bash GNU bash 4.3.30
datamash (GNU datamash) 1.2
cut (GNU coreutils) 8.23
``````

Some details for datamash:

``````datamash        command-line calculations (man)
Path    : /usr/local/bin/datamash
Version : 1.2
Type    : ELF 64-bit LSB executable, x86-64, version 1 (SYS ...)
Help    : probably available with -h,--help
Home    : https://savannah.gnu.org/projects/datamash/ (pm)
Home    : http://www.gnu.org/software/datamash (doc)
``````