get min and max from subarray in multidimensional array in Awk

user_4579196 0
Viewed 68

I have million rows of measurement for cities for different days. There are multiple measurements so I need to get min and max for each city and day combinations. Here's sample data:

London  Wednesday   19
Melbourne   Tuesday 128
London  Wednesday   9
London  Tuesday 9
Melbourne   Tuesday 99
London  Wednesday   18
London  Tuesday 2
Melbourne   Wednesday   89
Melbourne   Wednesday   9
Melbourne   Tuesday 23
London  Tuesday 13
Melbourne   Wednesday   11

I tried this

{
  arr[$1][$2][$3]++
}
END{
  for (city in arr){
    printf"%s\t",city
    for (day in arr[city]){
      n=asorti(arr[city][day],sorted)
      printf"%s\t%s\t%s\t",day,sorted[1],sorted[n];
    }
  printf"\n"
  }
}

But I'm getting alphabetical order not numerical order:

Melbourne Tuesday   128 99  Wednesday   11  9
London    Tuesday   13  9   Wednesday   18  9

What I need is:

Melbourne Tuesday 23 128 Wednesday 9 89
London    Tuesday 2  13  Wednesday 9 19

I tried using BEGIN{ PROCINFO["sorted_in"] = "@ind_num_asc"} but didn't help.

awk arrays
2 Answers

There is no need to do sorting.

Using gnu-awk, you may use this:

awk 'max[$1][$2] < $3 {max[$1][$2] = $3} !min[$1][$2] || min[$1][$2] > $3 {min[$1][$2] = $3} END {for (i in max) {printf "%s", i; for (j in max[i]) printf " %s %d %d", j, min[i][j], max[i][j]; print ""}}' file | column -t

Melbourne  Tuesday  23  128  Wednesday  9  89
London     Tuesday  2   13   Wednesday  9  19

A readable version:

awk '
max[$1][$2] < $3 {
   max[$1][$2] = $3
}
!min[$1][$2] || min[$1][$2] > $3 {
   min[$1][$2] = $3
}
END {
   for (i in max) {
      printf "%s", i
      for (j in max[i])
         printf " %s %d %d", j, min[i][j], max[i][j]
      print ""
   }
}' file | column -t
user_5482250

With respect to I'm getting alphabetical order not numerical order - right, because array indices are always strings, even if they look like numbers, and so asorti() does string/alphabetic sorting by default. If you want it to do numeric sorting instead then you have to tell it that by adding an extra argument asorti(arr[city][day],sorted,"@ind_num_asc"), see https://www.gnu.org/software/gawk/manual/gawk.html#Array-Sorting-Functions.

I wouldn't bother with asorti() for this (or for most things), though, just use sorted_in:

$ cat tst.awk
{ vals[$1][$2][$3] }
END {
    PROCINFO["sorted_in"] = "@ind_num_asc"
    for ( city in vals ) {
        printf "%s", city
        for ( day in vals[city] ) {
            printf "%s%s", OFS, day
            cnt = 0
            for ( val in vals[city][day] ) {
                if ( ++cnt == 1 ) {
                    min = val
                }
                max = val
            }
            printf "%s%s%s%s", OFS, min, OFS, max
        }
        print ""
    }
}


$ awk -f tst.awk file | column -t
London     Tuesday  2   13   Wednesday  9  19
Melbourne  Tuesday  23  128  Wednesday  9  89

The above used GNU awk for arrays of arrays and sorted_in.

If this question wasn't specifically about sorting arrays then I'd have done it by piping sort -k1,2 -k3,3n file into an awk script that just printed the 1st and last $3 for every $1/$2 combination.

user_17450010
Related