-1

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
1

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
0

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
0

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
0

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:"
head $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)

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.