2

I have a large flat file which is tab separated and look like this:

APP     SRC     HITS
App1    SRC1    202
App1    SRC1    101
App1    SRC13   90
App1    SRC2    3169
App1    SRC4    1
App1    SRC2    158
App1    SRC2    151
App3    SRC3    1
App3    SRC3    6
App3    SRC3    11
App3    SRC3    16
App3    SRC3    339
App2    SRC3    2
App2    SRC3    10023

I am very new scripting so not sure where to begin and i cant seem to find a solution where I need to group the results from APP and SRC columns (i.e. APP and SRC columns become one key).

I want to print the MIN and MAX hits only for each APP and the SRC it belongs to. e.g.

The output should read something like this. Is there a way I can achieve this by passing the file through a script?

APP  SRC   MIN   MAX 
App1 SRC1  101   202
App1 SRC2  151   3169
App3 SRC3  1     339
App2 SRC3  2     10023

Many thanks in advance!

1
  • Hello, both answers below that show output from your sample data return six (6) rows. Your OP only shows four (4) rows. Please update your OP if the sample output you've posted is incorrect, thx! Commented Jul 18, 2022 at 23:16

3 Answers 3

3

With awk:

awk '
BEGIN{ FS=OFS="\t"; print "APP", "SRC", "MIN", "MAX" }

 NR==1{ next }
 { key=($1 OFS $2) }
 !(key in min) { min[key]=max[key]= $3 }
 min[key]>$3 { min[key]=$3 }
 max[key]<$3 { max[key]=$3 }

END{ for (key in min) print key, min[key], max[key] }' infile

Ouptut:

APP     SRC     MIN     MAX
App2    SRC3    2       10023
App1    SRC1    101     202
App1    SRC2    151     3169
App1    SRC4    1       1
App1    SRC13   90      90
App3    SRC3    1       339
0
1

Assuming \t can't appear in columns names use this with any awk :

#!/bin/awk -f
BEGIN {SUBSEP=OFS="\t"; print "APP", "SRC", "MIN", "MAX";}
NR > 1 && (!(($1,$2) in min) || min[$1,$2] > $3) { min[$1,$2] = $3 }
NR > 1 && (!(($1,$2) in max) || max[$1,$2] < $3) { max[$1,$2] = $3 }
END { 
    for(i in min) printf "%s%s%s%s%s\n", i, OFS, min[i], OFS, max[i]
}
2
  • Thank you both @DanieleGrassini. Your solution works and I very much grateful for your answer.
    – DAP P
    Commented Jul 13, 2022 at 8:38
  • @DAPP see the edits Commented Jul 13, 2022 at 22:41
0

Using Raku (formerly known as Perl_6)

raku -e 'my @a = lines.skip.map(*.words); my @b = @a.map(*.[0..1].join("\t"));  \
         my %c;  %c.=append([Z=>] @b, @a.map(*.[2])); put "APP\t\tSRC\t\tMIN\tMAX";  \
         for %c.sort {say (.key, .value.map(*.Int).min, .value.map(*.Int).max).join: "\t"};' 

Here's an approach using Raku's built-in hash functionalities. Basically the first two columns are combined to create a key, and a hash %c is created with the third column as one-or-more values. Raku's append function adds values to the appropriate key:

~$ raku -e 'my @a = lines.skip>>.words; my @b = @a.map(*.[0..1].join("\t")); my %c;  %c.=append([Z=>] @b, @a.map(*.[2])); for %c.sort { .say };'  file
App1    SRC1 => [202 101]
App1    SRC13 => 90
App1    SRC2 => [3169 158 151]
App1    SRC4 => 1
App2    SRC3 => [2 10023]
App3    SRC3 => [1 6 11 16 339]

Raku provides min, max, and minmax routines in the base language. Note in the first code example: it is EXTREMELY IMPORTANT to coerce values to Int prior to output, otherwise min/max won't work correctly. Additionally, Raku has an sprintf function if rigorous formatting beyond \t separation is required (caveat: above output has extra \ts in the header to align).


Sample Input:

APP     SRC     HITS
App1    SRC1    202
App1    SRC1    101
App1    SRC13   90
App1    SRC2    3169
App1    SRC4    1
App1    SRC2    158
App1    SRC2    151
App3    SRC3    1
App3    SRC3    6
App3    SRC3    11
App3    SRC3    16
App3    SRC3    339
App2    SRC3    2
App2    SRC3    10023

Sample Output:

APP     SRC     MIN MAX
App1    SRC1    101 202
App1    SRC13   90  90
App1    SRC2    151 3169
App1    SRC4    1   1
App2    SRC3    2   10023
App3    SRC3    1   339

https://docs.raku.org/routine/=%3E
https://raku.org

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .