0

I have a large data file that contains four columns, the fourth is the total values for the row. I need to be able to find the top three values keyed on the first three columns. The following AWK will produce output but stops when it finds duplicate totals in the data file's fourth column. Can someone point me in the direction of how to add a counter loop in this AWK so that it produces the correct output please?

BEGIN { FS="," }

{
  site=$1
  region=$2
  town=$3
  totcnt=$4+0

  key=site","region","town

  if (totcnt >=max) {
    max=totcnt
    mytot[key]=max
  }
}

END { for (i in mytot) {print i "," mytot[i]} }

Many thanks

7
  • 2
    please provide some input.
    – pLumo
    Jul 30 '19 at 8:51
  • 3
    and some expected output. and what do you mean by "find the top three values keyed on the first three columns."?
    – cas
    Jul 30 '19 at 9:00
  • also, when you say "stops when it finds duplicate totals...", what is actually happening is that any duplicate entries are overwritten when there's a higher totcnt.
    – cas
    Jul 30 '19 at 9:07
  • 1. To start with, you're going to need either three arrays (for the top three values) or a single multi-dimensional array - you can't store three values in a single variable. 2. And if you're not using GNU awk (which has reasonable support for multi-dimensional arrays), you're probably better off doing this in perl. And even if it is GNU awk, it would still be easier in perl. 3. Also, please stop saying "The AWK" - "The awk script" or "the script" are grammatically correct, but "The AWK" is not.
    – cas
    Jul 30 '19 at 10:30
  • 2
    Your question and your code do two different things. Your code only cares about the fourth column. You could clean up a lot of your code by using $0. Also, your code isn't stopping when it finds duplicates, it is overwriting the the array entry. If this is homework, good luck. If this is something else, you are using the wrong tool: sort -rnk4,4 -t, input.txt | head -3 Jul 30 '19 at 12:01
1

The following perl script uses a data structure called a Hash-of-Arrays (or HoA). The hash (%sites) has a key derived from the first three fields (site,region,town) and each element of the hash is an array containing ALL of the totals for that particular key.

It reads in each line to build up the HoA - stripping any leading or trailing whitespace and ignoring any blank lines. It also ignores comments (anything beginning with a # character) because it's trivially easy to do and I think it's useful to be able to comment out data in text data files.

After the script has finished reading in the entire input file, it reverse sorts the arrays belonging to each key, and then prints out a summary line containing only the top 3 values for each.

#!/usr/bin/perl

my %sites=();

while(<>) {
  chomp;
  s/#.*//;        # strip comments #
  s/^\s*|\s*$//g; # strip leading and trailing spaces
  next if (/^$/); # skip blank lines

  my($site,$region,$town,$total) = split /,/;
  my $key = "$site,$region,$town";

  push @{ $sites{$key} }, $total;
}

foreach my $k (sort keys %sites) {
  @{ $sites{$k} } = reverse sort @{ $sites{$k} };
  print $k . ": " . join(", ",@{ $sites{$k}}[0..2] ), "\n";
};

sample output:

$ ./jon.pl input.txt
site1,North,Bristol: 996776, 9776, 6776
site2,South,Guildford: 99392, 392, 2392
site2,South,London: 99381, 381, 2381
site3,Central,Birmingham: 992628, 5628, 2628
site3,Wales,Cardiff: 99834, 9834, 834
site3,Wales,Swansea: 991796, 3796, 21796
site5,South,Guildford: 99338, 338, 2338
site5,South,London: 99266, 3266, 266
site5,South,Windsor: 99359, 359, 2359
site5,West,Bristol: 997700, 9700, 7700

input.txt contains the sample data you provided, copied several times, with the totals edited in the copies.

The output can easily be modified to be prettier, or to print one line for each total (hint, loop over array elements [0..2] rather than join() them).


BTW, it is possible to implement something like this in awk, but it is much easier to do in perl.

3
  • it's not about what you're allowed to say, or what might offend somebody. it's about what's correct. a script is not an awk, it's a script. awk is a programming language. a script is a script, even if it happens to be written in awk. Calling an awk script "the awk" is like calling Hamlet "the early modern English" instead of "a play".
    – cas
    Jul 30 '19 at 13:30
  • anyway, did the perl script above do something like what you wanted? did you understand what it's doing and how it works?
    – cas
    Jul 30 '19 at 13:34
  • Hi Cas - I'm afraid perl is totally alien to me, I do moslty understand the constructs in AWK and when I can get my head around the arrays business I might be able to tweak what I originally wrote to actually do what is intended
    – Jon
    Jul 30 '19 at 13:57
0

To keep the three largest values, you can use two arrays of three elements each, one for the actual values and another for the associated keys. These should be kept sorted.

BEGIN { FS = "," }

{
    key = sprintf("%s,%s,%s", $1, $2, $3)
    value = $4

    for (i = 1; i <= 3; ++i)
        if (values[i] == "" || values[i] < value) {
            tmp = values[i]
            values[i] = value
            value = tmp

            tmp = keys[i]
            keys[i] = key
            key = tmp
        } else break
}

END {
    for (i = 1; i <= 3; ++i)
        printf("%s,%d\n", keys[i], values[i])
}

This uses two arrays, values and keys. For each read value (value = $4), the value is checked against each value in values to see whether it is bigger than any of them. If it is, the "current value" (value) is swapped with the value in values[i] and that value is "bubbled" down the array. The keys array is kept in sync with values array.

Testing:

$ cat file
A1,B2,C3,35
A4,B5,C6,607
A7,B8,C9,159
A10,B11,C12,100
A13,B14,C15,116
A16,B17,C18,688
A19,B20,C21,346
A22,B23,C24,277
A25,B26,C27,931
A28,B29,C30,552
A31,B32,C33,605
A34,B35,C36,109
$ awk -f script.awk file
A25,B26,C27,931
A16,B17,C18,688
A4,B5,C6,607
1
  • Thanks very much Kusalananda - you are a life saver.
    – Jon
    Jul 31 '19 at 7:41

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.